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

SQL Anywhere 10.0.1 » UltraLite - M-Business Anywhere Programming » Understanding UltraLite for M-Business Anywhere Development » Working with data using the table API

Searching rows with find and lookup Next Page

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.


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.

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.

  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.


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.


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.

  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:

    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.

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: