Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.0 » UltraLiteJ » Using UltraLiteJ » Developing UltraLiteJ applications » Accessing and modifying data using SQL

 

Data modification using INSERT, UPDATE, DELETE

You can perform SQL data modification using the execute method of a PreparedStatement. A PreparedStatement queries the database with a user-defined SQL statement.

When applying a SQL statement to a PreparedStatement, query parameters are indicated by the ? character. For any INSERT, UPDATE or DELETE statement, each ? parameter is referenced according to its ordinal position in the statement. For example, the first ? is referenced as parameter one, and the second as parameter two.

 To INSERT a row in a table
  1. Prepare a new SQL statement as a String.

    String sql_string = 
        "INSERT INTO Department(dept_no, name) VALUES( ?, ? )";
  2. Pass the String to the PreparedStatement.

    PreparedStatement inserter = 
        conn.prepareStatement(sql_string);
    
  3. Pass input values to the PreparedStatement using the set method.

    This example sets 101 for the dept_no, referenced as parameter 1, and "Electronics" for the name, referenced as parameter 2.

    inserter.set(1, 101);
    inserter.set(2, "Electronics");
  4. Execute the statement.

    inserter.execute();
  5. Close the PreparedStatement to free resources.

    inserter.close()
  6. Commit all changes to the database.

    conn.commit();

Steps(3) and (4) can be repeated as many times as needed.

Alternatively, when only a single INSERT is to be performed, the statement INSERT INTO Department(dept_no, name) VALUES(2, 'Electronics') could be PREPAREd, EXECUTEd and CLOSEd.

The statement could be created using Java String concatenation.

 To UPDATE a row in a table
  1. Prepare a new SQL statement as a String.

    String sql_string = 
        "UPDATE Department SET dept_no = ? WHERE dept_no = ?";
  2. Pass the String to the PreparedStatement.

    PreparedStatement updater = 
        conn.prepareStatement(sql_string);
    
  3. Pass input values to the PreparedStatement using the set method.

    updater.set(1, 102);
    updater.set(2, 101);

    The example above is the equivalent of declaring the following SQL statement:

    UPDATE Department SET dept_no = 102 WHERE dept_no = 101
  4. Execute the statement.

    updater.execute();
  5. Close the PreparedStatement to free resources.

    updater.close()
  6. Commit all changes to the database.

    conn.commit();

Steps(3) and (4) can be repeated as many times as needed.

Alternatively, when only a single INSERT is to be performed, the statement INSERT INTO Department(dept_no, name) VALUES(2, 'Electronics') could be PREPAREd, EXECUTEd and CLOSEd.

The statement could be created using Java String concatenation.

 To DELETE a row in a table
  1. Prepare a new SQL statement as a String.

    String sql_string = 
        "DELETE FROM Department WHERE dept_no = ?";
  2. Pass the String to the PreparedStatement.

    PreparedStatement deleter = 
        conn.prepareStatement(sql_string);
    
  3. Pass input values to the PreparedStatement using the set method.

    deleter.set(1, 102);

    The example above is the equivalent of declaring the following SQL statement:

    DELETE FROM Department WHERE dept_no = 102
  4. Execute the statement.

    deleter.execute();
  5. Close the PreparedStatement to free resources.

    deleter.close()
  6. Commit all changes to the database.

    conn.commit();
 Example

Steps(3) and (4) can be repeated as many times as needed.

Alternatively, when only a single INSERT is to be performed, the statement INSERT INTO Department(dept_no, name) VALUES(2, 'Electronics') could be PREPAREd, EXECUTEd and CLOSEd as described in the section above.

The statement could be created using Java String concatenation.