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.
Last modified: September 4, 1996
mail to: firstname.lastname@example.org
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: