public void updateServiceType( CFSecurityAuthorization Authorization, CFSecurityServiceTypeBuff Buff) { final String S_ProcName = "updateServiceType"; ResultSet resultSet = null; try { int ServiceTypeId = Buff.getRequiredServiceTypeId(); String Description = Buff.getRequiredDescription(); int Revision = Buff.getRequiredRevision(); Connection cnx = schema.getCnx(); String sql = "exec sp_update_svctype ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?"; if (stmtUpdateByPKey == null) { stmtUpdateByPKey = cnx.prepareStatement(sql); } int argIdx = 1; stmtUpdateByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtUpdateByPKey.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtUpdateByPKey.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtUpdateByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtUpdateByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtUpdateByPKey.setString(argIdx++, "SVCT"); stmtUpdateByPKey.setInt(argIdx++, ServiceTypeId); stmtUpdateByPKey.setString(argIdx++, Description); stmtUpdateByPKey.setInt(argIdx++, Revision); resultSet = stmtUpdateByPKey.executeQuery(); if (resultSet.next()) { CFSecurityServiceTypeBuff updatedBuff = unpackServiceTypeResultSetToBuff(resultSet); if (resultSet.next()) { resultSet.last(); throw CFLib.getDefaultExceptionFactory() .newRuntimeException( getClass(), S_ProcName, "Did not expect multi-record response, " + resultSet.getRow() + " rows selected"); } Buff.setRequiredDescription(updatedBuff.getRequiredDescription()); Buff.setRequiredRevision(updatedBuff.getRequiredRevision()); } else { throw CFLib.getDefaultExceptionFactory() .newRuntimeException( getClass(), S_ProcName, "Expected a single-record response, " + resultSet.getRow() + " rows selected"); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } } }
public void testBoolean() throws Throwable { // String crtab = "create table #testBigInt (a bigint)"; String crtab = "create table #testBit (a BIT NULL)"; dropTable("#testBit"); Statement stmt = con.createStatement(); stmt.executeUpdate(crtab); stmt.executeUpdate("insert into #testBit values (NULL)"); stmt.executeUpdate("insert into #testBit values (0)"); stmt.executeUpdate("insert into #testBit values (1)"); ResultSet rs = stmt.executeQuery("select * from #testBit where a is NULL"); rs.next(); rs.getBoolean(1); rs = stmt.executeQuery("select * from #testBit where a = 0"); rs.next(); rs.getBoolean(1); rs = stmt.executeQuery("select * from #testBit where a = 1"); rs.next(); rs.getBoolean(1); stmt.close(); PreparedStatement pstmt = con.prepareStatement("insert into #testBit values (?)"); pstmt.setBoolean(1, true); assertTrue(!pstmt.execute()); assertTrue(pstmt.getUpdateCount() == 1); pstmt.setBoolean(1, false); assertTrue(!pstmt.execute()); assertTrue(pstmt.getUpdateCount() == 1); pstmt.setNull(1, java.sql.Types.BIT); assertTrue(!pstmt.execute()); assertTrue(pstmt.getUpdateCount() == 1); pstmt.close(); }
private String getInfo(ResultSet rs) throws SQLException { // position to first record boolean moreRecords = rs.next(); // If there are no records, display a message if (!moreRecords) { return null; } Vector columnHeads = new Vector(); Vector rows = new Vector(); try { // get column heads ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 1; i <= rsmd.getColumnCount(); ++i) columnHeads.addElement(rsmd.getColumnName(i)); // get row data do { rows.addElement(getNextRow(rs, rsmd)); } while (rs.next()); String info; String aux = rows.get(0).toString(); info = aux.substring(1, aux.length() - 1); return info; } catch (SQLException sqlex) { sqlex.printStackTrace(); return null; } }
private void initFromDatabase() throws SQLException, BlockStoreException { Statement s = conn.get().createStatement(); ResultSet rs; rs = s.executeQuery("SELECT value FROM settings WHERE name = '" + CHAIN_HEAD_SETTING + "'"); if (!rs.next()) { throw new BlockStoreException("corrupt Postgres block store - no chain head pointer"); } Sha256Hash hash = new Sha256Hash(rs.getBytes(1)); rs.close(); this.chainHeadBlock = get(hash); this.chainHeadHash = hash; if (this.chainHeadBlock == null) { throw new BlockStoreException("corrupt Postgres block store - head block not found"); } rs = s.executeQuery( "SELECT value FROM settings WHERE name = '" + VERIFIED_CHAIN_HEAD_SETTING + "'"); if (!rs.next()) { throw new BlockStoreException( "corrupt Postgres block store - no verified chain head pointer"); } hash = new Sha256Hash(rs.getBytes(1)); rs.close(); s.close(); this.verifiedChainHeadBlock = get(hash); this.verifiedChainHeadHash = hash; if (this.verifiedChainHeadBlock == null) { throw new BlockStoreException("corrupt Postgres block store - verified head block not found"); } }
@BeforeClass public static void setUpBeforeClass() throws Exception { con.addCustomer( "Buddy", "Bear", "1520 Garnet Ave", "", "San Diego", "CA", "92109", "4766666656"); con.addPublication("Runner Magazine", "Sports", 9.80, "Monthly", 5); ResultSet r = con.searchCustomer(0, "Buddy", ""); try { while (r.next()) { testCustID = r.getInt("CustomerID"); } r.close(); } catch (Exception e) { e.printStackTrace(); } ResultSet rs = con.searchPublication(0, "", "Runner Magazine"); try { while (rs.next()) { testPubID = rs.getInt("PublicationID"); } } catch (Exception e) { e.printStackTrace(); } subscriptions newSub = new subscriptions(testCustID, testPubID); }
public void updateOptionPrice( String modelName, String optionSetName, String optionName, float newPrice) { try { int autoid = 0; String sql = "select id from automobile where name ='" + modelName + "';"; ResultSet rs = statement.executeQuery(sql); while (rs.next()) { autoid = rs.getInt("id"); // get auto_id } int opsid = 0; sql = "select id from optionset where name ='" + optionSetName + "' and auto_id= " + autoid + ";"; rs = statement.executeQuery(sql); while (rs.next()) { opsid = rs.getInt("id"); // get option_id } sql = "update options set price = " + newPrice + " where name= '" + optionName + "' and option_id= " + opsid + ";"; statement.executeUpdate(sql); // update it with name and option_id } catch (SQLException e) { e.printStackTrace(); } }
@Test public void testReadDomainsAndGuids() throws Exception { CustomerDAO dao = new CustomerDAO(); IConfiguration configuration = EasyMock.createStrictMock(IConfiguration.class); dao.setConfiguration(configuration); ResultSet resultSet = EasyMock.createStrictMock(ResultSet.class); Set<String> domains = new HashSet<String>(); Set<GlobalIdentifier> guids = new HashSet<GlobalIdentifier>(); int custId = 34; int cloudService = 2; int replicationZone = 453; int cloudService2 = 1; int replicationZone2 = 13; int cloudService3 = 3; // first exec of loop EasyMock.expect(resultSet.getString(10)).andReturn("domain123"); EasyMock.expect(resultSet.getString(11)).andReturn("guid123"); EasyMock.expect(resultSet.getInt(12)).andReturn(cloudService); EasyMock.expect(resultSet.getInt(13)).andReturn(replicationZone); EasyMock.expect(resultSet.next()).andReturn(true); EasyMock.expect(resultSet.getInt(1)).andReturn(custId); // second exec of loop EasyMock.expect(resultSet.getString(10)).andReturn("domain456"); EasyMock.expect(resultSet.getString(11)).andReturn("guid456"); EasyMock.expect(resultSet.getInt(12)).andReturn(cloudService2); EasyMock.expect(resultSet.getInt(13)).andReturn(replicationZone2); EasyMock.expect(resultSet.next()).andReturn(true); EasyMock.expect(resultSet.getInt(1)).andReturn(custId); // third exec of loop (guid not valid with no cloud service) EasyMock.expect(resultSet.getString(10)).andReturn("domain456"); EasyMock.expect(resultSet.getString(11)).andReturn("guid789"); EasyMock.expect(resultSet.getInt(12)).andReturn(cloudService3); EasyMock.expect(resultSet.next()).andReturn(true); EasyMock.expect(resultSet.getInt(1)).andReturn(custId + 1); // ends loop with mismatched custid EasyMock.replay(resultSet); assertTrue( "Should have another item even.", dao.readDomainsAndGuids(resultSet, custId, domains, guids)); EasyMock.verify(resultSet); assertEquals("Should have 2 domains.", 2, domains.size()); assertTrue("Domain123 not found.", domains.contains("domain123")); assertTrue("Domain456 not found.", domains.contains("domain456")); assertEquals("Should have 2 guids.", 2, guids.size()); for (GlobalIdentifier guid : guids) { if (guid.getGuid().equals("guid123")) { assertEquals("Wrong cloud service in guid123", CloudService.GOOGLE, guid.getService()); assertEquals("Wrong replication zone.", replicationZone, guid.getReplicationZone()); } else { assertEquals("Wrong cloud service in guid456", CloudService.OFFICE365, guid.getService()); assertEquals("Wrong replication zone.", replicationZone2, guid.getReplicationZone()); } } }
/** * Returns the normalized Authority value for an author based on the name passed in. If no * authority exists, null will be returned. * * @param author the author to get the authority information for * @return the normalized authority information or null if no authority exists. */ public static String getNormalizedAuthorAuthorityFromDatabase(String author) { if (!connectToDatabase()) { return null; } else { try { getPreferredAuthorByOriginalNameStmt.setString(1, author); // First check without normalization ResultSet originalNameResults = getPreferredAuthorByOriginalNameStmt.executeQuery(); if (originalNameResults.next()) { String authority = originalNameResults.getString("normalizedName"); // Found a match originalNameResults.close(); return authority; } else { // No match, check alternate names for the author String normalizedAuthor = AuthorNormalizer.getNormalizedName(author); getPreferredAuthorByAlternateNameStmt.setString(1, normalizedAuthor); ResultSet alternateNameResults = getPreferredAuthorByAlternateNameStmt.executeQuery(); if (alternateNameResults.next()) { String authority = alternateNameResults.getString("normalizedName"); alternateNameResults.close(); return authority; } } } catch (Exception e) { logger.error("Error loading authority information from database", e); } } return null; }
@Test public void testUpsertDateValues() throws Exception { long ts = nextTimestamp(); Date now = new Date(System.currentTimeMillis()); ensureTableCreated(getUrl(), TestUtil.PTSDB_NAME, null, ts - 2); Properties props = new Properties(); props.setProperty( PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1 Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props); String dateString = "1999-01-01 02:00:00"; PreparedStatement upsertStmt = conn.prepareStatement( "upsert into ptsdb(inst,host,date) values('aaa','bbb',to_date('" + dateString + "'))"); int rowsInserted = upsertStmt.executeUpdate(); assertEquals(1, rowsInserted); upsertStmt = conn.prepareStatement( "upsert into ptsdb(inst,host,date) values('ccc','ddd',current_date())"); rowsInserted = upsertStmt.executeUpdate(); assertEquals(1, rowsInserted); conn.commit(); props.setProperty( PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1 conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props); String select = "SELECT date,current_date() FROM ptsdb"; ResultSet rs = conn.createStatement().executeQuery(select); Date then = new Date(System.currentTimeMillis()); assertTrue(rs.next()); Date date = DateUtil.parseDate(dateString); assertEquals(date, rs.getDate(1)); assertTrue(rs.next()); assertTrue(rs.getDate(1).after(now) && rs.getDate(1).before(then)); assertFalse(rs.next()); }
/** * This method gets the moments happening after the reference moments, in particular the moments * that occur in the range ]referenceMoment, referenceMoment + range{ * * @param referenceMoment the reference moment to be used to as boundary * @param range how many minutes after we want to capture (remember the ranges are not inclusive * of the boundaries) * @return a vector of moments that happened after the referenceMoment */ public Vector<Moment> getMomentsAfter(Moment referenceMoment, int range) throws SQLException { Vector<Moment> before = new Vector<Moment>(); String query = "SELECT * FROM minute WHERE id > " + referenceMoment.getMinute() + "AND id < " + String.valueOf(Integer.parseInt(referenceMoment.getMinute()) + range); Statement st = this.connection.createStatement(); ResultSet rs = st.executeQuery(query); while (rs.next()) { Moment moment = new Moment(); moment.setMinute(rs.getString("id")); moment.setDate(rs.getString("date")); moment.setLocation(rs.getString("location")); moment.setActivity(rs.getString("activity")); moment.setImagepath(rs.getString("image_path")); String queryimages = "SELECT `image-path` FROM image WHERE id='" + moment.getMinute() + "'"; Statement subst = this.connection.createStatement(); ResultSet subrs = subst.executeQuery(queryimages); while (subrs.next()) { moment.getImages().add(subrs.getString("image-path")); } } return before; }
public Moment queryMoment(String image_path) throws SQLException { Moment moment = new Moment(); String queryminute = "SELECT id FROM image WHERE `image-path`='" + image_path + "'"; Statement st = this.connection.createStatement(); ResultSet rs = st.executeQuery(queryminute); String minute = ""; while (rs.next()) { minute = rs.getString("id"); } String query = "SELECT * FROM minute WHERE id='" + minute + "'"; st = this.connection.createStatement(); rs = st.executeQuery(query); int counter = 0; while (rs.next()) { moment.setMinute(rs.getString("id")); moment.setDate(rs.getString("date")); moment.setLocation(rs.getString("location")); moment.setActivity(rs.getString("activity")); moment.setImagepath(image_path); String queryimages = "SELECT `image-path` FROM image WHERE id='" + moment.getMinute() + "'"; Statement subst = this.connection.createStatement(); ResultSet subrs = subst.executeQuery(queryimages); while (subrs.next()) { moment.getImages().add(subrs.getString("image-path")); } counter++; } if (counter > 1) System.err.println( "Attention! More than a moment associated to image " + image_path + ". Returning only the last moment"); return moment; }
public static void main(String args[]) { try { String urlBD = "jdbc:odbc:FOSA"; String usuarioBD = "SYSDBA"; String passwordBD = "masterkey"; Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection conexion = DriverManager.getConnection(urlBD, usuarioBD, passwordBD); Statement select = conexion.createStatement(); ResultSet resultadoSelect = select.executeQuery("SELECT MAX(NUM_REG) AS numreg FROM FACT01"); System.out.println("COMANDO EXITOSO"); System.out.println("numreg"); System.out.println("------"); int col = resultadoSelect.findColumn("numreg"); for (boolean seguir = resultadoSelect.next(); seguir; seguir = resultadoSelect.next()) System.out.println(resultadoSelect.getInt(col)); resultadoSelect.close(); select.close(); conexion.close(); } catch (SQLException ex) { System.out.println("Error: SQLException"); while (ex != null) { System.out.println( (new StringBuilder()).append("SQLState: ").append(ex.getSQLState()).toString()); System.out.println( (new StringBuilder()).append("Mensaje: ").append(ex.getMessage()).toString()); System.out.println( (new StringBuilder()).append("Vendedor: ").append(ex.getErrorCode()).toString()); ex = ex.getNextException(); System.out.println(""); } } catch (Exception ex) { System.out.println("Se produjo un error inesperado"); } }
/** Test large batch behavior. */ public void testLargeBatch() throws Exception { final int n = 5000; getConnection().close(); Statement stmt = con.createStatement(); stmt.executeUpdate("create table #testLargeBatch (val int)"); stmt.executeUpdate("insert into #testLargeBatch (val) values (0)"); PreparedStatement pstmt = con.prepareStatement("update #testLargeBatch set val=? where val=?"); for (int i = 0; i < n; i++) { pstmt.setInt(1, i + 1); pstmt.setInt(2, i); pstmt.addBatch(); } int counts[] = pstmt.executeBatch(); // System.out.println(pstmt.getWarnings()); assertEquals(n, counts.length); for (int i = 0; i < n; i++) { assertEquals(1, counts[i]); } pstmt.close(); ResultSet rs = stmt.executeQuery("select count(*) from #testLargeBatch"); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); assertFalse(rs.next()); rs.close(); stmt.close(); }
public boolean include(String file, String folder) { try { rs = stmt.executeQuery("SELECT id from Folders where name='" + folder + "'"); rs.next(); tmpMov = rs.getInt(1); rs = stmt.executeQuery( "SELECT folder_id from Files where name='" + file + "' AND folder_id='" + tmpMov + "'"); rs.next(); int tmpf = rs.getInt(1); if (tmpMov == tmpf) { rs = stmt.executeQuery("SELECT id from Files where name='" + file + "'"); rs.next(); fileID = rs.getInt(1); return true; } else { folderID = tmpMov; return false; } } catch (SQLException e) { folderID = tmpMov; return false; } }
public void actionPerformed(ActionEvent ae) { try { if (ae.getSource() == b7) { rs = st.executeQuery("select count(*) from employeemaster"); if (rs.next()) { t6.setText(rs.getString(1)); } rs = st.executeQuery("select * from Head"); if (rs.next()) { t1.setText(rs.getString(1)); t2.setText(rs.getString(2)); t7.setText(rs.getString(3)); t4.setText(rs.getString(4)); t5.setText(rs.getString(5)); t3.setText(rs.getString(6)); } } else if (ae.getSource() == b6) { dispose(); } } catch (Exception e) { JOptionPane.showMessageDialog(this, "Error is" + e); } }
@Test public void testBooleanString() throws SQLException { Statement stmt = sharedConnection.createStatement(); stmt.execute("insert into booleanvalue values(true)"); stmt.execute("insert into booleanvalue values(false)"); stmt.execute("insert into booleanvalue values(4)"); ResultSet rs = stmt.executeQuery("select * from booleanvalue"); if (rs.next()) { assertTrue(rs.getBoolean(1)); assertEquals("1", rs.getString(1)); if (rs.next()) { assertFalse(rs.getBoolean(1)); assertEquals("0", rs.getString(1)); if (rs.next()) { assertTrue(rs.getBoolean(1)); assertEquals("4", rs.getString(1)); } else { fail("must have a result !"); } } else { fail("must have a result !"); } } else { fail("must have a result !"); } }
public void checkTables(consumer<Boolean> consumer) { Connection connection = null; ResultSet result = null; try { connection = HikariManager.getInstance().getConnection(); DatabaseMetaData dbm = connection.getMetaData(); boolean playersExists = false; boolean periodicExists = false; boolean allExist = false; result = dbm.getTables(null, null, HikariManager.getInstance().prefix + "players", null); if (result.next()) playersExists = true; result = dbm.getTables(null, null, HikariManager.getInstance().prefix + "periodic", null); if (result.next()) periodicExists = true; if (playersExists && periodicExists) allExist = true; if (consumer != null) consumer.accept(allExist); } catch (SQLException e) { e.printStackTrace(); } finally { HikariManager.getInstance().close(connection, null, result); } }
public static Customer[] getCustomers() { Logger.log("Database.getCustomers", CALL_FLAG); Customer[] customers = new Customer[0]; try { String name; String phoneNumber; int id; ResultSet rs = query("SELECT COUNT(*) FROM kunder"); rs.next(); customers = new Customer[rs.getInt("COUNT(*)")]; rs.next(); rs = query("SELECT * FROM kunder"); while (rs.next()) { name = rs.getString("navn"); phoneNumber = rs.getString("telefon"); id = rs.getInt("kunde_id"); customers[rs.getRow() - 1] = new Customer(name, phoneNumber, id); } } catch (SQLException exception) { Logger.log(exception, "Database:getCustomers"); } Logger.log("Database.getCustomer Finised", FINISHED_FLAG); return customers; }
@Test public void testUpsertValuesWithExpression() throws Exception { long ts = nextTimestamp(); ensureTableCreated(getUrl(), "IntKeyTest", null, ts - 2); Properties props = new Properties(); props.setProperty( PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1 Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props); String upsert = "UPSERT INTO IntKeyTest VALUES(-1)"; PreparedStatement upsertStmt = conn.prepareStatement(upsert); int rowsInserted = upsertStmt.executeUpdate(); assertEquals(1, rowsInserted); upsert = "UPSERT INTO IntKeyTest VALUES(1+2)"; upsertStmt = conn.prepareStatement(upsert); rowsInserted = upsertStmt.executeUpdate(); assertEquals(1, rowsInserted); conn.commit(); conn.close(); props.setProperty( PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1 conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props); String select = "SELECT i FROM IntKeyTest"; ResultSet rs = conn.createStatement().executeQuery(select); assertTrue(rs.next()); assertEquals(-1, rs.getInt(1)); assertTrue(rs.next()); assertEquals(3, rs.getInt(1)); assertFalse(rs.next()); }
public static Show[] getShows() { Logger.log("Database.getShows", CALL_FLAG); Show[] shows = new Show[0]; try { Hall[] halls = getHalls(); Movie[] movies = getMovies(); Timestamp time; Hall hall; Movie movie; int ID; ResultSet rs = query("SELECT COUNT(*) FROM forestillinger"); rs.next(); shows = new Show[rs.getInt("COUNT(*)")]; rs = query("SELECT * FROM forestillinger ORDER BY tid ASC"); while (rs.next()) { hall = halls[rs.getInt("sal_id") - 1]; movie = movies[rs.getInt("film_id") - 1]; time = rs.getTimestamp("tid"); ID = rs.getInt("forestilling_id"); shows[rs.getRow() - 1] = new Show(hall, movie, time, ID); } } catch (Exception exception) { Logger.log(exception, "Database:getShows"); } Logger.log("Database.getShows", FINISHED_FLAG); return shows; }
public void deleteISOTimezone( CFSecurityAuthorization Authorization, CFSecurityISOTimezoneBuff Buff) { final String S_ProcName = "deleteISOTimezone"; ResultSet resultSet = null; try { Connection cnx = schema.getCnx(); short ISOTimezoneId = Buff.getRequiredISOTimezoneId(); String sql = "SELECT " + schema.getLowerDbSchemaName() + ".sp_delete_isotz( ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + " ) as DeletedFlag"; if (stmtDeleteByPKey == null) { stmtDeleteByPKey = cnx.prepareStatement(sql); } int argIdx = 1; stmtDeleteByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtDeleteByPKey.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtDeleteByPKey.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtDeleteByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtDeleteByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtDeleteByPKey.setShort(argIdx++, ISOTimezoneId); stmtDeleteByPKey.setInt(argIdx++, Buff.getRequiredRevision()); ; resultSet = stmtDeleteByPKey.executeQuery(); if (resultSet.next()) { boolean deleteFlag = resultSet.getBoolean(1); if (resultSet.next()) { throw CFLib.getDefaultExceptionFactory() .newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response"); } } else { throw CFLib.getDefaultExceptionFactory() .newRuntimeException( getClass(), S_ProcName, "Expected 1 record result set to be returned by delete, not 0 rows"); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } } }
private void okActionPerformed( java.awt.event.ActionEvent evt) { // GEN-FIRST:event_okActionPerformed try { if (txtuser.getText().equals("") || txtpassword.getText().equals("")) { JOptionPane.showMessageDialog(this, "Please Enter Username & Password", "Message", WIDTH); } else { // connect Class.forName("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/student_information", "root", "123"); Statement stm = conn.createStatement(); String qry = "select password from login where username = '******' ;"; ResultSet rst = stm.executeQuery(qry); Statement stm2 = conn.createStatement(); String qry2 = "select state from login where username = '******'; "; ResultSet rst2 = stm2.executeQuery(qry2); if (!rst.next()) { // validate username JOptionPane.showMessageDialog(this, "Invalid Username", "Error", WIDTH); } else if (rst.getString("password").equals(txtpassword.getText())) { // check password MainWindow m = new MainWindow(); // open main m.setVisible(true); m.lbluser.setText(txtuser.getText()); if (rst2.next() && rst2.getInt("State") == 1) { // block lecturer m.lblState.setText("Admin"); } else { m.lblState.setText("Lecturer"); /* MainWindow mw1 = new MainWindow(); mw1.btnStudentm.setVisible(false); mw1.btnCoursem.setVisible(false); mw1.btnLecturerm.setVisible(false); mw1.btnAdmin.setVisible(false); mw1.lbllec.setVisible(false);*/ } this.setVisible(false); txtuser.setText(""); txtpassword.setText(""); } else { JOptionPane.showMessageDialog(this, "Invalid Password", "Error", WIDTH); } } } catch (Exception e) { JOptionPane.showMessageDialog(this, "Error in Excecution " + e, "Error", WIDTH); } } // GEN-LAST:event_okActionPerformed
@Test public void testCompareLongGTEDecimal() throws Exception { long ts = nextTimestamp(); initTableValues(null, ts); String query = "SELECT l FROM LongInKeyTest where l >= 1.5"; Properties props = new Properties(); props.setProperty( PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2 Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props); try { PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); /* * Failing because we're not converting the constant to the type of the RHS * when forming the start/stop key. * For this case, 1.5 -> 1L * if where l < 1.5 then 1.5 -> 1L and then to 2L because it's not inclusive * */ assertTrue(rs.next()); assertEquals(2, rs.getLong(1)); assertFalse(rs.next()); } finally { conn.close(); } }
/** * Remove a student from a particular course. Also Deletes all the quiz vizualisation files in the * student's directory which relates to the course. Caution: vizualisation file will be deleted * eventhough it also relates to another course if the student is also registered to that course. * (FIX ME!) Throws InvalidDBRequestException if the student is not registered in the course, * error occured during deletion, or other exception occured. * * @param username student's user name * @param courseID course id (course number + instructor name) * @throws InvalidDBRequestException */ public void deleteStudent(String username, String courseID) throws InvalidDBRequestException { try { Class.forName(GaigsServer.DBDRIVER); db = DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD); Statement stmt = db.createStatement(); ResultSet rs; int count = 0; // check if student registered to the course rs = stmt.executeQuery( "select * from courseRoster where course_id = '" + courseID + "' and user_login = '******'"); if (!rs.next()) throw new InvalidDBRequestException("Student is not registered to the course"); // remove student from the course count = stmt.executeUpdate( "delete from courseRoster where course_id = '" + courseID + "' and user_login = '******'"); if (count != 1) throw new InvalidDBRequestException("Error occured during deletion!"); // delete the quiz visualization files rs = stmt.executeQuery( "select distinct unique_id, s.test_name from scores s, courseTest t " + "where s.test_name = t.test_name " + "and course_id = '" + courseID + "' " + "and user_login = '******'"); while (rs.next()) { deleteVisualization(rs.getString(1), username, rs.getString(2)); count = stmt.executeUpdate("delete from scores where unique_id = " + rs.getString(1).trim()); } rs.close(); stmt.close(); db.close(); } catch (SQLException e) { System.err.println("Invalid SQL in addstudent: " + e.getMessage()); throw new InvalidDBRequestException("???"); } catch (ClassNotFoundException e) { System.err.println("Driver Not Loaded"); throw new InvalidDBRequestException("Internal Server Error"); } }
public void deleteOption(String modelName, String optionSetName, String option) { try { int autoid = 0; String sql = "select id from automobile where name ='" + modelName + "';"; ResultSet rs; rs = statement.executeQuery(sql); while (rs.next()) { autoid = rs.getInt("id"); // get auto_id } int opsid = 0; sql = "select id from optionset where name ='" + optionSetName + "' and auto_id= " + autoid + ";"; rs = statement.executeQuery(sql); while (rs.next()) { opsid = rs.getInt("id"); // get option_id } sql = "delete from options where name= '" + option + "' and option_id= " + opsid; statement.executeUpdate(sql); // delete it using name and option_id } catch (SQLException e) { e.printStackTrace(); } }
private List<User> doQuery(String sqlStmt) throws Exception { ArrayList<User> resultList = new ArrayList<User>(); // Try-with-resources. Both Statement and ResultSet are AutoCloseable try (Statement statement = connection.createStatement(); // forward-only, read-only result set, // http://www.tutorialspoint.com/jdbc/jdbc-result-sets.htm ResultSet rs = statement.executeQuery(sqlStmt); ) { if (!rs.next()) { // positioned to first record, and then found there were none // http://stackoverflow.com/questions/8292256/get-number-of-rows-returned-by-resultset-in-java return null; } else { // get the rows do { User user = new User( rs.getString("firstname"), rs.getString("lastname"), rs.getString("username"), rs.getString("email")); resultList.add(user); } while (rs.next()); } } catch (SQLException se) { se.printStackTrace(); throw (se); // rethrow } return resultList; }
public CFSecurityTSecGroupBuff lockBuff( CFSecurityAuthorization Authorization, CFSecurityTSecGroupPKey PKey) { final String S_ProcName = "lockBuff"; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory() .newUsageException(getClass(), S_ProcName, "Transaction not open"); } ResultSet resultSet = null; try { Connection cnx = schema.getCnx(); long TenantId = PKey.getRequiredTenantId(); int TSecGroupId = PKey.getRequiredTSecGroupId(); String sql = "SELECT * FROM " + schema.getLowerDbSchemaName() + ".sp_lock_tsecgrp( ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + " )"; if (stmtLockBuffByPKey == null) { stmtLockBuffByPKey = cnx.prepareStatement(sql); } int argIdx = 1; stmtLockBuffByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtLockBuffByPKey.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtLockBuffByPKey.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtLockBuffByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtLockBuffByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtLockBuffByPKey.setLong(argIdx++, TenantId); stmtLockBuffByPKey.setInt(argIdx++, TSecGroupId); resultSet = stmtLockBuffByPKey.executeQuery(); if (resultSet.next()) { CFSecurityTSecGroupBuff buff = unpackTSecGroupResultSetToBuff(resultSet); if (resultSet.next()) { throw CFLib.getDefaultExceptionFactory() .newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response"); } return (buff); } else { return (null); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } } }
/** * Helper function intended to be overwritten by subclasses. Thsi is where the real requiest for * IDs happens */ protected void performIDRequest() throws Exception { Connection dbConnection = null; try { try { dbConnection = dataSource.getConnection(); Statement stmt = dbConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); ResultSet set = null; set = stmt.executeQuery( "SELECT id, " + dbColumn + " FROM " + dbTable); // $NON-NLS-1$ //$NON-NLS-2$ if (!set.next()) { set.moveToInsertRow(); set.insertRow(); set.updateLong(dbColumn, NUM_IDS_GRABBED); set.moveToCurrentRow(); set.next(); } long nextID = set.getLong(dbColumn); long upTo = nextID + mCacheQuantity; set.updateLong(dbColumn, upTo); set.updateRow(); stmt.close(); setMaxAllowedID(upTo); setNextID(nextID); } finally { if (dbConnection != null) { dbConnection.close(); } } } catch (SQLException e) { throw new NoMoreIDsException(e); } }
public String query7() { String name, coach, output; int budget; String sqlText; try { sql = connection.createStatement(); sqlText = "CREATE VIEW topScorer AS SELECT cid, MAX(goals) AS scores FROM player GROUP BY cid"; sql.executeUpdate(sqlText); sqlText = "CREATE VIEW maxScore AS SELECT MAX(scores) AS maxscore FROM topScorer"; sql.executeUpdate(sqlText); sqlText = "CREATE VIEW topScorerTeam AS SELECT cid FROM topScorer JOIN maxScore ON (topScorer.scores = maxScore.maxscore)"; sql.executeUpdate(sqlText); sqlText = "CREATE VIEW budgetTeam AS SELECT cid, SUM(value) AS budget FROM player GROUP BY cid"; sql.executeUpdate(sqlText); sqlText = "CREATE VIEW lowestBudget AS SELECT MIN(budget) AS minbudget FROM budgetTeam"; sql.executeUpdate(sqlText); sqlText = "CREATE VIEW lowestBudgetTeam AS SELECT cid, minbudget AS budget FROM lowestBudget JOIN budgetTeam ON (lowestBudget.minbudget = budgetTeam.budget)"; sql.executeUpdate(sqlText); sqlText = "CREATE VIEW countryLowestBudgetTopScorer AS SELECT topScorerTeam.cid AS cid, budget FROM topScorerTeam JOIN lowestBudgetTeam ON topScorerTeam.cid = lowestBudgetTeam.cid"; sql.executeUpdate(sqlText); // We project Query7 over here. sqlText = "SELECT name, coach, budget FROM countryLowestBudgetTopScorer JOIN country ON (countryLowestBudgetTopScorer.cid = country.cid)"; rs = sql.executeQuery(sqlText); if (!rs.isBeforeFirst()) { return ""; } else { rs.next(); // Query7 (String name, String coach, integer budget) name = rs.getString("name"); coach = rs.getString("coach"); budget = rs.getInt("budget"); output = name + ":" + coach + ":" + budget; while (rs.next()) { name = rs.getString("name"); coach = rs.getString("coach"); budget = rs.getInt("budget"); output += "#" + name + ":" + coach + ":" + budget; } return output; } } catch (SQLException e) { return ""; } }
public static void main(String[] args) { try { Connection con = DBopsMySql.setuptradesConnection(); con.setAutoCommit(false); String ul = "AUD"; PreparedStatement pstmt = DBopsMySql.getExpirysForUpdate(con, ul, 20090300, 20100700); PreparedStatement upDateStmt = DBopsMySql.updateBeginEndDatesForExpiry(con); ResultSet res = pstmt.executeQuery(); res.next(); // To get a lastexpiry for loop, so should be one extra early expiry int lastexp = res.getInt("expiry"); while (res.next()) { int exp = res.getInt("expiry"); String bdate = DateOps.dbShortFormatString(lastexp - 5); String edate = DateOps.dbShortFormatString(exp - 6); upDateStmt.setString(1, bdate); if (!res.isLast()) { upDateStmt.setString(2, edate); } upDateStmt.setString(3, ul); upDateStmt.setInt(4, exp); upDateStmt.addBatch(); lastexp = exp; } int[] updateCounts = upDateStmt.executeBatch(); upDateStmt.close(); con.close(); } catch (SQLException ex) { MsgBox.err2(ex); } }