Exemple #1
1
 /** 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);
    }
  }
Exemple #6
0
 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();
   }
 }
Exemple #7
0
 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());
   }
 }
Exemple #8
0
 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) {
      }
    }
  }
Exemple #12
0
  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);
    }
  }
Exemple #13
0
 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);
    }
  }
Exemple #17
0
  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());
    }
  }
Exemple #20
0
 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();
  }