Foxhound is the better* Database Monitor for SQL Anywhere. *better: More thorough, more relevant, more effective. ...more Alerts, more All Clears, more details, more control in your hands.

Auto-Incrementing DataWindows

How can I have primary key values automatically generated for new rows in a table and then have those new values reflected back in a DataWindow?

SQL Anywhere 5 offers several automatic default values that let you tell the DBMS how to initialize columns. The following create statement shows how they're specified, including the new AutoIncrement default:

   create table auto
      ( pkey               integer not null
                              default autoincrement,
        inserted_date      date not null
                              default current date,
        inserted_time      time not null
                              default current time,
        inserted_date_time timestamp not null
                              default current timestamp,
        updated_date_time  timestamp not null
                              default timestamp,
        inserted_user      varchar (10 ) not null
                              default current user,
        data               varchar ( 10 ),
      primary key ( pkey ) );

The AutoIncrement default tells SQL Anywhere 5 to assign each new row a value one greater than that of the previous highest value in the column. Your program does not have to do anything to this column after calling InsertRow() because the Update() call will take care of generating a new value. In particular, the column does not have to have a DataWindow initial value, and your program doesn't have to call SetItem() to fill it.

If you need to know the value that was generated by the call to Update() then you can use Rows - Update Properties to specify pkey as the "Identify Column" for this DataWindow. Note that you don't even have to mark pkey as one of the "Updateable Columns":

Here's what a sample DataWindow looks like after InsertRow() has been called, after the user has typed in the data column, but before Update() has been called:

As soon as the program calls Update() the new value of pkey is echoed back to the DataWindow:

At this point your program can call GetItemNumber() to find out what the new value is. That is necessary if you need to populate foreign key values in child table rows that also need to be inserted.

AutoIncrement columns are a special case. In order to see the values of other columns that were automatically generated by SQL Anywhere 5 you must re-retrieve the row: