PowerBuilder – Getting Database Identity Values after Multi Row Inserts from Datawindow

Posted on Thursday, December 22nd, 2011 at 7:40 pm in

This is a technique to use when you have a data entry window in which multiple parent records can be inserted along with multiple child records for each parent and your tables have their record keys set up as identity values. The database in question for this discussion is SQL Server.

This figure shows a datawindow with three new rows inserted into it. Normally this poses no particular problem. However, what if you needed the key values of each of these new rows so you could insert child records in the same process or for some other application logic? One approach would be to loop through the Parent datawindow, insert each record, obtain the key, process the child, take the next Parent record, blah, blah, blah… (not to mention slow).

A different approach is to save all the Parent records first, retrieve the new key values into a datastore, apply the keys to the appropriate child records, and then save them (or do whatever else needs doing). There are a few caveats for this, however, so we need to investigate things to make sure this will work for us.

First to verify how the records are inserted by PowerBuilder I set up a trace and captured the SQL Insert statements.

INSERT INTO dbo.mb_sample ( code, code_desc, code_type, created_by, created_dt ) 
VALUES ( 'TR000', 'Train Rails ', 'S', 'mbalent', {ts '2011-12-22 11:37:04.859'} )
INSERT INTO dbo.mb_sample ( code, code_desc, code_type, created_by, created_dt ) 
VALUES ( 'ST0100', 'Standard Track 100 length', 'I', 'mbalent', {ts '2011-12-22 11:37:04.859'} )
INSERT INTO dbo.mb_sample ( code, code_desc, code_type, created_by, created_dt ) 
VALUES ( 'ST0050', 'Standard Track 50 length', 'I', 'mbalent', {ts '2011-12-22 11:37:04.859'} )

So far so good. The records are inserted in the order they were inserted into the datawindow (top to bottom).

A quick select shows the records retrieved in the same order as inserted.

The next thing to verify that the table holding the records has some sort of datetime column you can use to retrieve the rows just inserted. Even better is to also have some sort of user id (or SPID) column which can also be used to ensure the records are exact matches.

This is the graphic information from the SQL Server Enterprise Manager regarding the table I am using for this example. Note that there is no primary key defined for the table (shudder).

Now we added a primary key but it isn’t on the Identity column for the table. (Maybe a mistake, maybe not.)

Oops, now if we select the records are not in the same order. So what you say, we already got the identity values. Well, now we insert some new ones

When we retrieve them they are all out of whack…

This means that unless we want to do a bunch of sorting behind the scenes when we save we need to have the Primary Key defined as the Identity column for the table containing the parent records.

The basic steps for this process in Powerbuilder are:
1. Set a datetime variable prior to the saving of the Parent records and set this into the datawindow rows prior to saving. This ensures all rows have the same ‘entered datetime’.
2. Define a datastore to retrieve rows from the Parent table using userid and entered datetime as retrieval arguments. The datetime variable from step 1 is used as an argument here.
3. Once the new rows are retrieved, obtain the key values and apply it to the appropriate datawindows/datastores prior to updating them.

While researching this I found an interesting ‘tidbit’ about SQL Server. SQL Server does not have any implicit order when rows are retrieved from tables. There are a number of examples out there showing how order changes based on re-indexing, collation, and etc.

You might also be interested in