Data manipulation using INSERT, UPDATE, DELETE

You can perform SQL data manipulation 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 SQL statement. For example, the first ? is referred as parameter 1, and the second as parameter 2.

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. Assign the String to the PreparedStatement.

    PreparedStatement inserter = 
    conn.prepareStatement(sql_string);

  3. Assign input parameter values for the statement.

    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();

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. Assign the String to the PreparedStatement.

    PreparedStatement inserter = 
    conn.prepareStatement(sql_string);

  3. Assign input parameter values for the statement.

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

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

    UPDATE Department SET dept_no = 102 WHERE dept_no = 101

  4. Execute the statement.

    inserter.execute();

  5. Close the PreparedStatement to free resources.

    inserter.close()

  6. Commit all changes to the database.

    conn.commit();

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. Assign the String to the PreparedStatement.

    PreparedStatement inserter = 
    conn.prepareStatement(sql_string);

  3. Assign input parameter values for the statement.

    inserter.set(1, 102);

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

    DELETE FROM Department WHERE dept_no = 102

  4. Execute the statement.

    inserter.execute();

  5. Close the PreparedStatement to free resources.

    inserter.close()

  6. Commit all changes to the database.

    conn.commit();
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0