/** Insert record in table with current data object. */ public void insert(Connection con) throws SQLException { String sql = "select * from T_Parameters where F_Name=?"; PreparedStatement stmt = null; ResultSet rs = null; try { stmt = con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); stmt.setString(1, name_); stmt.setFetchSize(1); rs = stmt.executeQuery(); rs.moveToInsertRow(); if (setreg_[0]) rs.updateString(1, name_); if (setreg_[1]) rs.updateString(2, value_); if (setreg_[2]) rs.updateString(3, type_); rs.insertRow(); } catch (SQLException e) { log_.severe(e.toString()); throw e; } finally { try { if (rs != null) rs.close(); if (stmt != null) stmt.close(); } catch (SQLException x) { } } }
void insertpostid(String phn_no, String pid) { String host = "jdbc:derby://localhost:1527/JhalMuri"; String uName = "jhalmuri"; String uPass = "******"; if (searchusers(phn_no)) { updateid(phn_no, pid); } else { try (Connection con = DriverManager.getConnection(host, uName, uPass)) { Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); String sql = "SELECT * FROM POSTID"; ResultSet rs = stmt.executeQuery(sql); rs.moveToInsertRow(); rs.updateString("PHN", phn_no); rs.updateString("ID", pid); rs.insertRow(); rs.close(); con.close(); } catch (SQLException err) { System.out.println(err.getMessage()); } } }
public static void main(String args[]) { try { // Step 2: load driver Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // Step 3: define the connection URL String url = "jdbc:odbc:personDSN"; // Step 4: establish the connection Connection con = DriverManager.getConnection(url); // Step 5: create PrepareStatement by passing sql and // ResultSet appropriate fields String sql = "SELECT * FROM Person"; PreparedStatement pStmt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); // Step 6: execute the query ResultSet rs = pStmt.executeQuery(); // moving cursor to insert row rs.moveToInsertRow(); // updating values in insert row rs.updateString("Name", "imitiaz"); rs.updateString("Address", "cantt"); rs.updateString("phoneNum", "9201211"); // inserting row in resultset & into database rs.insertRow(); // Step 8: close the connection con.close(); } catch (Exception sqlEx) { System.out.println(sqlEx); } } // end main
public void insertRow(String coffeeName, int supplierID, float price, int sales, int total) throws SQLException { Statement stmt = null; try { stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES"); uprs.moveToInsertRow(); uprs.updateString("COF_NAME", coffeeName); uprs.updateInt("SUP_ID", supplierID); uprs.updateFloat("PRICE", price); uprs.updateInt("SALES", sales); uprs.updateInt("TOTAL", total); uprs.insertRow(); uprs.beforeFirst(); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmt != null) { stmt.close(); } } }
/** * 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 void addToDb(Contact c, Connection con) { try (Statement s = con.createStatement(); ResultSet resultSet = s.executeQuery("SELECT * FROM contact")) { resultSet.moveToInsertRow(); resultSet.updateInt("ID", c.getId()); resultSet.updateString("name", c.getName()); if (c.getClass().getName().equals("jdbase.model.Person")) resultSet.updateString("surname", ((Person) c).getSurname); else resultSet.updateString("surname", " ---- "); resultSet.updateString("email", c.getEmail()); resultSet.updateString("phoneNumber", c.getPhoneNumber()); resultSet.insertRow(); System.out.println(" After adding :"); System.out.println("ID \tfName \tsurname \temail \t\tphoneNo"); while (resultSet.next()) { System.out.println( resultSet.getInt("id") + "\t" + resultSet.getString("name") + "t\"" + resultSet.getString("surname") + "t\"" + resultSet.getString("email") + "t\"" + resultSet.getString("phoneNumber")); } } catch (SQLException e) { e.printStackTrace(); } }
protected void index(ResultSet rs, ILinguist linguist, long sid, String sent) throws LoadException { if (rs == null) throw new LoadException("Index ResultSet null"); try { // System.out.println(sent); String[] words = linguist.indexkeys(sent); if (words.length > 0) { Map<String, String> dup = new HashMap<String, String>(); int i = 0; for (String word : words) { // System.out.print(word+" "); if (!dup.containsKey(word)) { dup.put(word, word); rs.moveToInsertRow(); rs.updateString("F_Word", word); rs.updateLong("F_SID", sid); rs.updateInt("F_Offset", i++); rs.insertRow(); } } // System.out.println(); } } catch (SQLException se) { throw new LoadException("SQLException occurred: " + se.getMessage()); } catch (LanguageException le) { throw new LoadException("LanguageException occurred indexing: " + le.getMessage()); } }
private void SalvarTelefone() { if (!TFcodCli.getText().isEmpty()) { if (tfTelefone .getText() .replace("(", "") .replace(")", "") .replace("-", "") .trim() .equals("")) { JOptionPane.showMessageDialog( null, "Informe o telefone!", "Alerta", JOptionPane.ERROR_MESSAGE); return; } ResultSet rsTelefone = FMaster.con.executeSQL("select * from dltelefonecliente where telefone = ''"); try { rsTelefone.first(); rsTelefone.moveToInsertRow(); rsTelefone.updateInt( "id_cliente", Integer.parseInt(TFcodCli.getText().isEmpty() ? "0" : TFcodCli.getText())); rsTelefone.updateString( "telefone", tfTelefone.getText().replace("(", "").replace(")", "").replace("-", "")); rsTelefone.insertRow(); rsTelefone.close(); PreencherJtableTelefones(); tfTelefone.setText(""); } catch (SQLException ex) { Logger.getLogger(IFCliente.class.getName()).log(Level.SEVERE, null, ex); } } }
@Override public void moveToInsertRow() throws SQLException { try { _res.moveToInsertRow(); } catch (SQLException e) { handleException(e); } }
public void testQuery() { try { Connection c = newConnection(); Statement st = c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); String s = "CREATE TABLE T (I INTEGER, C CHARACTER(10), B BIT(4) DEFAULT B'')"; st.execute(s); s = "INSERT INTO T VALUES(?,?, DEFAULT)"; PreparedStatement ps = c.prepareStatement(s); for (int i = 1; i <= 20; i++) { ps.setInt(1, i); ps.setString(2, "TEST " + i); ps.execute(); } c.setAutoCommit(false); s = "SELECT * FROM T"; ResultSet rs = st.executeQuery(s); rs.absolute(10); rs.updateString(2, "UPDATE10"); rs.updateRow(); rs.absolute(11); rs.deleteRow(); rs.moveToInsertRow(); rs.updateInt(1, 1011); rs.updateString(2, "INSERT1011"); rs.updateString(3, "0101"); rs.insertRow(); rs.close(); rs = st.executeQuery(s); while (rs.next()) { System.out.println( "" + rs.getInt(1) + " " + rs.getString(2) + " " + rs.getString(3)); } } catch (Exception e) { System.out.print(e); } }
public static void main(String[] args) { Connection connection = null; Statement statement = null; ResultSet resultSet = null; // note that we're doing this the old way - without a try-with-resources block try { // make a connection to the database connection = DriverManager.getConnection(URL + DATABASE, USER, PASSWORD); // ensure that we're using transactions connection.setAutoCommit(false); // create a statement object. make sure the resultset is updateable statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); // execute the query resultSet = statement.executeQuery("SELECT * FROM Person"); // prepare to insert a row into the resultset. recall that the resultset can also be used for // updates resultSet.moveToInsertRow(); resultSet.updateString("name", "Austin"); resultSet.updateInt("age", 87); // insert the row resultSet.insertRow(); // commit the transaction connection.commit(); } catch (SQLException sqle) { // rollback the transaction try { connection.rollback(); } catch (SQLException sqle2) { sqle2.printStackTrace(); System.out.println("Transaction was rolled back!"); } } finally { // close the resources try { if (connection != null) connection.close(); if (statement != null) statement.close(); if (resultSet != null) resultSet.close(); } catch (SQLException sqle3) { } } }
public static void toDataBase(InputStream inputStream, String fileName) { boolean validation = true; DOMParser parser = new DOMParser(); Document doc; try { // InputStream inputStream = file.getInputStream(); InputSource source = new InputSource(inputStream); // String fileName = file.getFileName(); int indexForm = fileName.indexOf("_"); int indexDemo = fileName.indexOf("_", indexForm + 1); int indexTimeStamp = fileName.indexOf(".", indexDemo); String formName = fileName.substring(0, indexForm); String demographicNo = fileName.substring(indexForm + 1, indexDemo); String timeStamp = fileName.substring(indexDemo + 1, indexTimeStamp); // check if the data existed in the database already... String sql = "SELECT * FROM " + formName + " WHERE demographic_no='" + demographicNo + "' AND formEdited='" + timeStamp + "'"; MiscUtils.getLogger().debug(sql); ResultSet rs = DBHandler.GetSQL(sql); if (!rs.first()) { rs.close(); sql = "SELECT * FROM " + formName + " WHERE demographic_no='" + demographicNo + "' AND ID='0'"; MiscUtils.getLogger().debug("sql: " + sql); rs = DBHandler.GetSQL(sql, true); rs.moveToInsertRow(); // To validate or not parser.setFeature("http://xml.org/sax/features/validation", validation); parser.parse(source); doc = parser.getDocument(); rs = toResultSet(doc, rs); rs.insertRow(); } rs.close(); } catch (Exception e) { MiscUtils.getLogger().debug("Errors " + e); } }
public Boolean begin_new_record(String table) { try { Statement s = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); s.execute("Select * From " + table + " Where " + table + "_id = 0"); if (results != null) results.close(); results = s.getResultSet(); results.moveToInsertRow(); current_table = table; error = false; return true; } catch (Exception ex) { throw new InvalidQueryException("Database update failed"); } }
public static void addAction( String description, String notes, String context, String status, int projectid, String datum) { try { java.sql.Connection con = Database.getConnection(); java.sql.Statement s = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet statuses; statuses = s.executeQuery("select * from statuses"); statuses.first(); boolean klaar = false; int statusid = 0; while (!klaar) { if (statuses.getString("name").equals(status)) { statusid = statuses.getInt("id"); klaar = true; } else { statuses.next(); } } int actionid = aantalID("actions"); int context_id = getContextID(context); ResultSet actions; actions = s.executeQuery("select * from actions"); actions.moveToInsertRow(); actions.updateInt("id", actionid); actions.updateString("description", description); actions.updateString("notes", notes); actions.updateInt("contexts_id", context_id); actions.updateInt("status_id", statusid); actions.updateInt("project_id", projectid); actions.updateDate("action_date", Date.valueOf(datum)); actions.updateDate("statuschange_date", null); actions.updateBoolean("done", false); actions.insertRow(); } catch (SQLException ex) { Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex); } }
public static void putGedachte(String notes) { try { java.sql.Connection con = Database.getConnection(); java.sql.Statement s = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); int id = aantalID("thoughts"); ResultSet thoughtInvoegen; thoughtInvoegen = s.executeQuery("select * from thoughts"); thoughtInvoegen.moveToInsertRow(); thoughtInvoegen.updateInt("id", id); thoughtInvoegen.updateString("notes", notes); thoughtInvoegen.insertRow(); } catch (SQLException ex) { ex.printStackTrace(); } }
private void SalvaCliente() { ResultSet rsCliComanda = FMaster.con.executeSQL("select * from atncomcli where id_comanda = '" + comanda + "'"); try { rsCliComanda.moveToInsertRow(); rsCliComanda.updateInt("id_comanda", Integer.parseInt(comanda)); rsCliComanda.updateInt("id_com_clie", Utilitario.getSequencia("atncomcli", "id_com_clie")); rsCliComanda.updateString("nome", TFNomecliente.getText()); rsCliComanda.updateString("cod_mesa", id_mesa); rsCliComanda.insertRow(); TFNomecliente.setText(""); TFNomecliente.requestFocus(); rsCliComanda.close(); } catch (SQLException ex) { Logger.getLogger(DLCadCliComanda.class.getName()).log(Level.SEVERE, null, ex); } }
public int saveAlphaRecord(Properties props) throws SQLException { String demographic_no = props.getProperty("demographic_no"); String sql = "SELECT * FROM formAlpha WHERE demographic_no=" + demographic_no + " AND ID=0"; ResultSet rs = DBHandler.GetSQL(sql, true); rs.moveToInsertRow(); ResultSetMetaData md = rs.getMetaData(); for (int i = 1; i <= md.getColumnCount(); i++) { String name = md.getColumnName(i); if (name.equalsIgnoreCase("ID")) { rs.updateNull(name); continue; } String value = props.getProperty(name, null); if (md.getColumnTypeName(i).equalsIgnoreCase("TINY") && md.getScale(i) == 1) { if (value != null) { if (value.equalsIgnoreCase("on")) rs.updateInt(name, 1); else rs.updateInt(name, 0); } else { rs.updateInt(name, 0); } continue; } if (md.getColumnTypeName(i).equalsIgnoreCase("date")) { java.util.Date d; if (md.getColumnName(i).equalsIgnoreCase("formEdited")) d = UtilDateUtilities.Today(); else d = UtilDateUtilities.StringToDate(value, "yyyy/MM/dd"); if (d == null) rs.updateNull(name); else rs.updateDate(name, new Date(d.getTime())); continue; } if (value == null) rs.updateNull(name); else rs.updateString(name, value); } rs.insertRow(); rs.close(); int ret = 0; sql = "SELECT LAST_INSERT_ID()"; rs = DBHandler.GetSQL(sql); if (rs.next()) ret = rs.getInt(1); rs.close(); return ret; }
public static void addContext(String context) { try { java.sql.Connection con = Database.getConnection(); java.sql.Statement s = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); int aantal = aantalID("contexts"); ResultSet contexts; contexts = s.executeQuery("select * from contexts"); contexts.moveToInsertRow(); contexts.updateInt("id", aantal); contexts.updateString("name", context); contexts.insertRow(); } catch (SQLException ex) { Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex); } }
void insertusers(String phn_no, String Postal_Code, String name) { try { String host = "jdbc:derby://localhost:1527/JhalMuri"; String uName = "jhalmuri"; String uPass = "******"; try (Connection con = DriverManager.getConnection(host, uName, uPass)) { Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); String sql = "SELECT * FROM USERS"; ResultSet rs = stmt.executeQuery(sql); rs.moveToInsertRow(); rs.updateString("PHN", phn_no); rs.updateString("POSATAL", Postal_Code); rs.updateString("NAME", name); rs.insertRow(); } } catch (SQLException err) { System.out.println(err.getMessage()); } }
private void SalvarCliente() { ResultSet rsCliente = FMaster.con.executeSQL("select * from dlcliente where id_cliente = 0"); try { rsCliente.first(); rsCliente.moveToInsertRow(); rsCliente.updateInt("id_cliente", Utilitario.getSequencia("dlcliente", "id_cliente")); rsCliente.updateString("nome", TFNome.getText()); rsCliente.updateString("cidade", TFCidade.getText()); rsCliente.updateString("uf", CBEstado.getSelectedItem().toString()); rsCliente.updateString("rua", TFRua.getText()); rsCliente.updateInt( "numero", Integer.parseInt(TFNumero.getText().isEmpty() ? "0" : TFNumero.getText())); rsCliente.updateString("bairro", TFBairro.getText()); rsCliente.updateString("cep", TFCep1.getText().replace(".", "").replace("-", "")); rsCliente.updateString("cpf", TFCPF.getText().replace(".", "").replace("-", "")); rsCliente.updateString("obs", TAObs.getText()); rsCliente.updateFloat( "taxa_entrega", Float.parseFloat( TFVlrTaxaEntrega.getText().isEmpty() ? "0" : TFVlrTaxaEntrega.getText().replace(",", "."))); rsCliente.updateInt( "id_regiao", Integer.parseInt( CBRegiao.getSelectedItem() .toString() .substring( CBRegiao.getSelectedItem().toString().length() - 1, CBRegiao.getSelectedItem().toString().length()))); rsCliente.insertRow(); prencherHeder(rsCliente.getInt("id_cliente")); rsCliente.close(); PreencherJtableConsulta(); } catch (SQLException ex) { Logger.getLogger(IFCliente.class.getName()).log(Level.SEVERE, null, ex); } }
public static String addProject(String naam) { try { java.sql.Connection con = Database.getConnection(); java.sql.Statement s = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); int id = aantalID("projects"); ResultSet projects; projects = s.executeQuery("select * from projects"); projects.moveToInsertRow(); projects.updateInt("id", id); projects.updateString("name", naam); projects.updateString("notes", "test"); projects.insertRow(); return id + " " + naam + " test"; } catch (SQLException ex) { Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex); } return null; }
private void insertIntoVTI(ResultSet target, ExecRow row) throws StandardException { try { target.moveToInsertRow(); DataValueDescriptor[] rowArray = row.getRowArray(); for (int index = 0; index < rowArray.length; index++) { DataValueDescriptor dvd = rowArray[index]; try { if (dvd.isNull()) target.updateNull(index + 1); else dvd.setInto(target, index + 1); } catch (Throwable t) { // backwards compatibility - 5.0 and before used // updateObject always. target.updateObject(index + 1, dvd.getObject()); } } target.insertRow(); } catch (Throwable t) { throw StandardException.unexpectedUserException(t); } }
private void testUpdateDeleteInsert() throws SQLException { Connection c1 = getConnection(); Connection c2 = getConnection(); Statement stat = c1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); stat.execute("DROP TABLE IF EXISTS TEST"); stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))"); int max = 8; for (int i = 0; i < max; i++) { stat.execute("INSERT INTO TEST VALUES(" + i + ", 'Hello" + i + "')"); } ResultSet rs; rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID"); rs.next(); assertEquals(0, rs.getInt(1)); rs.moveToInsertRow(); rs.updateInt(1, 100); rs.moveToCurrentRow(); assertEquals(0, rs.getInt(1)); rs = stat.executeQuery("SELECT * FROM TEST"); int j = max; while (rs.next()) { int id = rs.getInt(1); if (id % 2 == 0) { Statement s2 = c2.createStatement(); s2.execute("UPDATE TEST SET NAME = NAME || '+' WHERE ID = " + rs.getInt(1)); if (id % 4 == 0) { rs.refreshRow(); } rs.updateString(2, "Updated " + rs.getString(2)); rs.updateRow(); } else { rs.deleteRow(); } // the driver does not detect it in any case assertFalse(rs.rowUpdated()); assertFalse(rs.rowInserted()); assertFalse(rs.rowDeleted()); rs.moveToInsertRow(); rs.updateString(2, "Inserted " + j); rs.updateInt(1, j); j += 2; rs.insertRow(); // the driver does not detect it in any case assertFalse(rs.rowUpdated()); assertFalse(rs.rowInserted()); assertFalse(rs.rowDeleted()); } rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID"); while (rs.next()) { int id = rs.getInt(1); String name = rs.getString(2); assertEquals(0, id % 2); if (id >= max) { assertEquals("Inserted " + id, rs.getString(2)); } else { if (id % 4 == 0) { assertEquals("Updated Hello" + id + "+", rs.getString(2)); } else { assertEquals("Updated Hello" + id, rs.getString(2)); } } trace("id=" + id + " name=" + name); } c2.close(); c1.close(); // test scrollable result sets Connection conn = getConnection(); for (int i = 0; i < 5; i++) { testScrollable(conn, i); } conn.close(); }
private void testInsertRowWithUpdatableResultSetDefault() throws Exception { stat.execute("create table test(id int primary key, " + "data varchar(255) default 'Hello')"); PreparedStatement prep = conn.prepareStatement( "select * from test", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = prep.executeQuery(); rs.moveToInsertRow(); rs.updateInt(1, 1); rs.insertRow(); rs.close(); rs = stat.executeQuery("select * from test"); assertTrue(rs.next()); assertEquals("Hello", rs.getString(2)); assertEquals("Hello", rs.getString("data")); assertEquals("Hello", rs.getNString(2)); assertEquals("Hello", rs.getNString("data")); assertEquals("Hello", IOUtils.readStringAndClose(rs.getNCharacterStream(2), -1)); assertEquals("Hello", IOUtils.readStringAndClose(rs.getNCharacterStream("data"), -1)); assertEquals("Hello", IOUtils.readStringAndClose(rs.getNClob(2).getCharacterStream(), -1)); assertEquals("Hello", IOUtils.readStringAndClose(rs.getNClob("data").getCharacterStream(), -1)); rs = prep.executeQuery(); rs.moveToInsertRow(); rs.updateInt(1, 2); rs.updateNString(2, "Hello"); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt(1, 3); rs.updateNString("data", "Hello"); rs.insertRow(); Clob c; Writer w; rs.moveToInsertRow(); rs.updateInt(1, 4); c = conn.createClob(); w = c.setCharacterStream(1); w.write("Hello"); w.close(); rs.updateClob(2, c); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt(1, 5); c = conn.createClob(); w = c.setCharacterStream(1); w.write("Hello"); w.close(); rs.updateClob("data", c); rs.insertRow(); InputStream in; rs.moveToInsertRow(); rs.updateInt(1, 6); in = new ByteArrayInputStream("Hello".getBytes("UTF-8")); rs.updateAsciiStream(2, in); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt(1, 7); in = new ByteArrayInputStream("Hello".getBytes("UTF-8")); rs.updateAsciiStream("data", in); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt(1, 8); in = new ByteArrayInputStream("Hello-".getBytes("UTF-8")); rs.updateAsciiStream(2, in, 5); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt(1, 9); in = new ByteArrayInputStream("Hello-".getBytes("UTF-8")); rs.updateAsciiStream("data", in, 5); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt(1, 10); in = new ByteArrayInputStream("Hello-".getBytes("UTF-8")); rs.updateAsciiStream(2, in, 5L); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt(1, 11); in = new ByteArrayInputStream("Hello-".getBytes("UTF-8")); rs.updateAsciiStream("data", in, 5L); rs.insertRow(); rs = stat.executeQuery("select * from test"); while (rs.next()) { assertEquals("Hello", rs.getString(2)); } stat.execute("drop table test"); }
public void moveToInsertRow() throws SQLException { rs.moveToInsertRow(); }
/** * General test of scrollable cursor functionality. * * <p>When running on SQL Server this test will exercise MSCursorResultSet. When running on Sybase * this test will exercise CachedResultSet. */ public void testCachedCursor() throws Exception { try { dropTable("jTDS_CachedCursorTest"); Statement stmt = con.createStatement(); stmt.execute( "CREATE TABLE jTDS_CachedCursorTest " + "(key1 int NOT NULL, key2 char(4) NOT NULL," + "data varchar(255))\r\n" + "ALTER TABLE jTDS_CachedCursorTest " + "ADD CONSTRAINT PK_jTDS_CachedCursorTest PRIMARY KEY CLUSTERED" + "( key1, key2)"); for (int i = 1; i <= 16; i++) { assertEquals( 1, stmt.executeUpdate( "INSERT INTO jTDS_CachedCursorTest VALUES(" + i + ", 'XXXX','LINE " + i + "')")); } stmt.close(); stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery("SELECT * FROM jTDS_CachedCursorTest ORDER BY key1"); assertNotNull(rs); assertEquals(null, stmt.getWarnings()); assertTrue(rs.isBeforeFirst()); assertTrue(rs.first()); assertEquals(1, rs.getInt(1)); assertTrue(rs.isFirst()); assertTrue(rs.last()); assertEquals(16, rs.getInt(1)); assertTrue(rs.isLast()); assertFalse(rs.next()); assertTrue(rs.isAfterLast()); rs.beforeFirst(); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); rs.afterLast(); assertTrue(rs.previous()); assertEquals(16, rs.getInt(1)); assertTrue(rs.absolute(8)); assertEquals(8, rs.getInt(1)); assertTrue(rs.relative(-1)); assertEquals(7, rs.getInt(1)); rs.updateString(3, "New line 7"); rs.updateRow(); // assertTrue(rs.rowUpdated()); // MS API cursors appear not to support this rs.moveToInsertRow(); rs.updateInt(1, 17); rs.updateString(2, "XXXX"); rs.updateString(3, "LINE 17"); rs.insertRow(); rs.moveToCurrentRow(); rs.last(); // assertTrue(rs.rowInserted()); // MS API cursors appear not to support this Statement stmt2 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs2 = stmt2.executeQuery("SELECT * FROM jTDS_CachedCursorTest ORDER BY key1"); rs.updateString(3, "NEW LINE 17"); rs.updateRow(); assertTrue(rs2.last()); assertEquals(17, rs2.getInt(1)); assertEquals("NEW LINE 17", rs2.getString(3)); rs.deleteRow(); rs2.refreshRow(); assertTrue(rs2.rowDeleted()); rs2.close(); stmt2.close(); rs.close(); stmt.close(); } finally { dropTable("jTDS_CachedCursorTest"); } }
private void testUpdateDataType() throws Exception { Connection conn = getConnection(); Statement stat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); stat.execute( "CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255), " + "DEC DECIMAL(10,2), BOO BIT, BYE TINYINT, BIN BINARY(100), " + "D DATE, T TIME, TS TIMESTAMP, DB DOUBLE, R REAL, L BIGINT, " + "O_I INT, SH SMALLINT, CL CLOB, BL BLOB)"); ResultSet rs = stat.executeQuery("SELECT * FROM TEST"); ResultSetMetaData meta = rs.getMetaData(); assertEquals("java.lang.Integer", meta.getColumnClassName(1)); assertEquals("java.lang.String", meta.getColumnClassName(2)); assertEquals("java.math.BigDecimal", meta.getColumnClassName(3)); assertEquals("java.lang.Boolean", meta.getColumnClassName(4)); assertEquals("java.lang.Byte", meta.getColumnClassName(5)); assertEquals("[B", meta.getColumnClassName(6)); assertEquals("java.sql.Date", meta.getColumnClassName(7)); assertEquals("java.sql.Time", meta.getColumnClassName(8)); assertEquals("java.sql.Timestamp", meta.getColumnClassName(9)); assertEquals("java.lang.Double", meta.getColumnClassName(10)); assertEquals("java.lang.Float", meta.getColumnClassName(11)); assertEquals("java.lang.Long", meta.getColumnClassName(12)); assertEquals("java.lang.Integer", meta.getColumnClassName(13)); assertEquals("java.lang.Short", meta.getColumnClassName(14)); assertEquals("java.sql.Clob", meta.getColumnClassName(15)); assertEquals("java.sql.Blob", meta.getColumnClassName(16)); rs.moveToInsertRow(); rs.updateInt(1, 0); rs.updateNull(2); rs.updateNull("DEC"); // 'not set' values are set to null assertThrows(ErrorCode.NO_DATA_AVAILABLE, rs).cancelRowUpdates(); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt(1, 1); rs.updateString(2, null); rs.updateBigDecimal(3, null); rs.updateBoolean(4, false); rs.updateByte(5, (byte) 0); rs.updateBytes(6, null); rs.updateDate(7, null); rs.updateTime(8, null); rs.updateTimestamp(9, null); rs.updateDouble(10, 0.0); rs.updateFloat(11, (float) 0.0); rs.updateLong(12, 0L); rs.updateObject(13, null); rs.updateShort(14, (short) 0); rs.updateCharacterStream(15, new StringReader("test"), 0); rs.updateBinaryStream(16, new ByteArrayInputStream(new byte[] {(byte) 0xff, 0x00}), 0); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt("ID", 2); rs.updateString("NAME", "+"); rs.updateBigDecimal("DEC", new BigDecimal("1.2")); rs.updateBoolean("BOO", true); rs.updateByte("BYE", (byte) 0xff); rs.updateBytes("BIN", new byte[] {0x00, (byte) 0xff}); rs.updateDate("D", Date.valueOf("2005-09-21")); rs.updateTime("T", Time.valueOf("21:46:28")); rs.updateTimestamp("TS", Timestamp.valueOf("2005-09-21 21:47:09.567890123")); rs.updateDouble("DB", 1.725); rs.updateFloat("R", (float) 2.5); rs.updateLong("L", Long.MAX_VALUE); rs.updateObject("O_I", 10); rs.updateShort("SH", Short.MIN_VALUE); // auml, ouml, uuml rs.updateCharacterStream("CL", new StringReader("\u00ef\u00f6\u00fc"), 0); rs.updateBinaryStream("BL", new ByteArrayInputStream(new byte[] {(byte) 0xab, 0x12}), 0); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt("ID", 3); rs.updateCharacterStream("CL", new StringReader("\u00ef\u00f6\u00fc")); rs.updateBinaryStream("BL", new ByteArrayInputStream(new byte[] {(byte) 0xab, 0x12})); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt("ID", 4); rs.updateCharacterStream(15, new StringReader("\u00ef\u00f6\u00fc")); rs.updateBinaryStream(16, new ByteArrayInputStream(new byte[] {(byte) 0xab, 0x12})); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt("ID", 5); rs.updateClob("CL", new StringReader("\u00ef\u00f6\u00fc")); rs.updateBlob("BL", new ByteArrayInputStream(new byte[] {(byte) 0xab, 0x12})); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt("ID", 6); rs.updateClob(15, new StringReader("\u00ef\u00f6\u00fc")); rs.updateBlob(16, new ByteArrayInputStream(new byte[] {(byte) 0xab, 0x12})); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt("ID", 7); rs.updateNClob("CL", new StringReader("\u00ef\u00f6\u00fc")); Blob b = conn.createBlob(); OutputStream out = b.setBinaryStream(1); out.write(new byte[] {(byte) 0xab, 0x12}); out.close(); rs.updateBlob("BL", b); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt("ID", 8); rs.updateNClob(15, new StringReader("\u00ef\u00f6\u00fc")); rs.updateBlob(16, b); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt("ID", 9); rs.updateNClob("CL", new StringReader("\u00ef\u00f6\u00fc"), -1); rs.updateBlob("BL", b); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt("ID", 10); rs.updateNClob(15, new StringReader("\u00ef\u00f6\u00fc"), -1); rs.updateBlob(16, b); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt("ID", 11); rs.updateNCharacterStream("CL", new StringReader("\u00ef\u00f6\u00fc"), -1); rs.updateBlob("BL", b); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt("ID", 12); rs.updateNCharacterStream(15, new StringReader("\u00ef\u00f6\u00fc"), -1); rs.updateBlob(16, b); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt("ID", 13); rs.updateNCharacterStream("CL", new StringReader("\u00ef\u00f6\u00fc")); rs.updateBlob("BL", b); rs.insertRow(); rs.moveToInsertRow(); rs.updateInt("ID", 14); rs.updateNCharacterStream(15, new StringReader("\u00ef\u00f6\u00fc")); rs.updateBlob(16, b); rs.insertRow(); rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID NULLS FIRST"); rs.next(); assertTrue(rs.getInt(1) == 0); assertTrue(rs.getString(2) == null && rs.wasNull()); assertTrue(rs.getBigDecimal(3) == null && rs.wasNull()); assertTrue(!rs.getBoolean(4) && rs.wasNull()); assertTrue(rs.getByte(5) == 0 && rs.wasNull()); assertTrue(rs.getBytes(6) == null && rs.wasNull()); assertTrue(rs.getDate(7) == null && rs.wasNull()); assertTrue(rs.getTime(8) == null && rs.wasNull()); assertTrue(rs.getTimestamp(9) == null && rs.wasNull()); assertTrue(rs.getDouble(10) == 0.0 && rs.wasNull()); assertTrue(rs.getFloat(11) == 0.0 && rs.wasNull()); assertTrue(rs.getLong(12) == 0 && rs.wasNull()); assertTrue(rs.getObject(13) == null && rs.wasNull()); assertTrue(rs.getShort(14) == 0 && rs.wasNull()); assertTrue(rs.getCharacterStream(15) == null && rs.wasNull()); assertTrue(rs.getBinaryStream(16) == null && rs.wasNull()); rs.next(); assertTrue(rs.getInt(1) == 1); assertTrue(rs.getString(2) == null && rs.wasNull()); assertTrue(rs.getBigDecimal(3) == null && rs.wasNull()); assertTrue(!rs.getBoolean(4) && !rs.wasNull()); assertTrue(rs.getByte(5) == 0 && !rs.wasNull()); assertTrue(rs.getBytes(6) == null && rs.wasNull()); assertTrue(rs.getDate(7) == null && rs.wasNull()); assertTrue(rs.getTime(8) == null && rs.wasNull()); assertTrue(rs.getTimestamp(9) == null && rs.wasNull()); assertTrue(rs.getDouble(10) == 0.0 && !rs.wasNull()); assertTrue(rs.getFloat(11) == 0.0 && !rs.wasNull()); assertTrue(rs.getLong(12) == 0 && !rs.wasNull()); assertTrue(rs.getObject(13) == null && rs.wasNull()); assertTrue(rs.getShort(14) == 0 && !rs.wasNull()); assertEquals("test", rs.getString(15)); assertEquals(new byte[] {(byte) 0xff, 0x00}, rs.getBytes(16)); rs.next(); assertTrue(rs.getInt(1) == 2); assertEquals("+", rs.getString(2)); assertEquals("1.20", rs.getBigDecimal(3).toString()); assertTrue(rs.getBoolean(4)); assertTrue((rs.getByte(5) & 0xff) == 0xff); assertEquals(new byte[] {0x00, (byte) 0xff}, rs.getBytes(6)); assertEquals("2005-09-21", rs.getDate(7).toString()); assertEquals("21:46:28", rs.getTime(8).toString()); assertEquals("2005-09-21 21:47:09.567890123", rs.getTimestamp(9).toString()); assertTrue(rs.getDouble(10) == 1.725); assertTrue(rs.getFloat(11) == (float) 2.5); assertTrue(rs.getLong(12) == Long.MAX_VALUE); assertEquals(10, ((Integer) rs.getObject(13)).intValue()); assertTrue(rs.getShort(14) == Short.MIN_VALUE); // auml ouml uuml assertEquals("\u00ef\u00f6\u00fc", rs.getString(15)); assertEquals(new byte[] {(byte) 0xab, 0x12}, rs.getBytes(16)); for (int i = 3; i <= 14; i++) { rs.next(); assertEquals(i, rs.getInt(1)); assertEquals("\u00ef\u00f6\u00fc", rs.getString(15)); assertEquals(new byte[] {(byte) 0xab, 0x12}, rs.getBytes(16)); } assertFalse(rs.next()); stat.execute("DROP TABLE TEST"); conn.close(); }