Data manipulation: INSERT, UPDATE, and DELETE

With UltraLite, you can perform SQL Data Manipulation Language operations and DDL operations. These operations are performed using the ExecuteStatement method, a member of the PreparedStatement class.

For more information the PreparedStatement class, see PreparedStatement class.

Parameter markers in prepared statements

UltraLite handles variable values using the ? parameter marker. For any INSERT, UPDATE or DELETE, each ? is referenced according to its ordinal position in the prepared statement. For example, the first ? is referred to as 1, and the second as 2.

To insert a row
  1. Declare a PreparedStatement object.

    var PrepStmt;
  2. Assign an INSERT statement to your prepared statement object. In the following, TableName and ColumnName are the names of a table and column.

    PrepStmt = conn.prepareStatement( 
        "INSERT into TableName(ColumnName) values (?)", null );

    The null parameter indicates that the statement has no persistent name.

  3. Assign parameter values to the statement.

    var NewValue;
    NewValue = "Bob";
    PrepStmt.setStringParameter(1, NewValue);
  4. Execute the statement.

    PrepStmt.executeStatement( null );
To update a row
  1. Declare a PreparedStatement object.

    var PrepStmt;
  2. Assign an UPDATE statement to your prepared statement object. In the following, TableName and ColumnName are the names of a table and column.

    PrepStmt = conn.prepareStatement(
      "UPDATE TableName SET ColumnName = ? WHERE ID = ?", null);

    The null parameter indicates that the statement has no persistent name.

  3. Assign parameter values to the statement using methods appropriate for the data type.

    var NewValue;
    NewValue = "Bob";
    PrepStmt.setStringParameter(1, NewValue);
    PrepStmt.setIntParameter(2, 6);
  4. Execute the statement

    PrepStmt.executeStatement( );
To delete a row
  1. Declare a PreparedStatement object.

    var PrepStmt;
  2. Assign a DELETE statement to your prepared statement object.

    PrepStmt = conn.prepareStatement(
        "DELETE FROM customer WHERE ID = ?", null );

    The null parameter indicates that the statement has no persistent name.

  3. Assign parameter values for the statement.

    var IDValue;
    IDValue = 6;
    PrepStmt.setIntParameter( 1, IDValue );
  4. Execute the statement.

    PrepStmt.executeStatement( );