Inserting, updating, and deleting rows

UltraLite exposes the rows in a table to your application one at a time. The ULTable object has a current position, which may be on a row, before the first row, or after the last row of the table.

When your application changes location, UltraLite makes a copy of the row in a buffer. Any operations to get or set values affect only the copy of data in this buffer. They do not affect the data in the database.

Example

The following statement changes the value of the first column in the buffer to 3.

tCustomer.setInt( 1 , 3 );
Using UltraLite modes

The UltraLite mode determines the purpose for which the values in the buffer will be used. UltraLite has the following four modes of operation, in addition to a default mode.

  • Insert mode   The data in the buffer is added to the table as a new row when the ULTable.insert method is called.

  • Update mode   The data in the buffer replaces the current row when the ULTable.update method is called.

  • Find mode   Used to locate a row whose value exactly matches the data in the buffer when one of the ULTable.find methods is called.

  • Lookup mode   Used to locate a row whose value matches or is greater than the data in the buffer when one of the ULTable.lookup methods is called.

To update a row

  1. Move to the row you want to update.

    You can move to a row by scrolling through the table or by searching using Find and Lookup methods.

  2. Enter Update mode.

    For example, the following instruction enters Update mode on the table tCustomer.

    tCustomer.updateBegin();

  3. Set the new values for the row to be updated.

    For example, the following instruction sets the new value to Elizabeth.

    tCustomer.setString( 2, "Elizabeth" );

  4. Execute the Update.

    tCustomer.update();

After the update operation, the current row is the row that was just updated. If you changed the value of a column in the index specified when the ULTable object was opened, the current position is undefined.

By default, UltraLite operates in autoCommit mode, so that the update is immediately applied to the row in permanent storage. If you have disabled autoCommit mode, the update is not applied until you execute a commit operation. For more information about autoCommit mode, see Managing transactions.

Caution

Do not update the primary key of a row: delete the row and add a new row instead.

Inserting rows

The steps to insert a row are similar to those for updating rows, except that there is no need to locate any particular row in the table before carrying out the insert operation. Rows are automatically sorted by the index specified when opening the table.

To insert a row

  1. Enter Insert mode.

    For example, the following instruction enters Insert mode on the table CustomerTable.

    tCustomer.insertBegin();

  2. Set the values for the new row.

    If you do not set a value for one of the columns, and that column has a default, the default value is used. If the column has no default, NULL is used. If the column does not allow NULL, the following defaults are used:

    • For numeric columns, zero.

    • For character columns, an empty string.

    To set a value to NULL explicitly, use the setNull method.

    colID = tCustomer.schema.getColumnID( "id" );
colFirstName = tCustomer.schema.getColumnID( "fname" );
colLastName = tCustomer.schema.getColumnID( "lname" );
tCustomer.setInt( colID, 42 );
tCustomer.setString( colFirstName, "Mitch" );
tCustomer.setString( colLastName, "McLeod" );

  3. Execute the insertion.

    The inserted row is permanently saved to the database when a Commit is carried out. In autoCommit mode, a Commit is carried out as part of the Insert method. 

    tCustomer.insert();
Deleting rows

There is no delete mode corresponding to the insert or update modes.

The following procedure deletes a row.

To delete a row

  1. Move to the row you want to delete.

  2. Execute the delete:

    tCustomer.deleteRow();
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1