PowerBuilder: Get identity value after insert into Sql Server table

Question

I'm using a PowerBuilder 12 Classic DataWindow to insert a new record into a SQL Server 2008 database table that has an identity column. After the insert, I'd like to get the value of the identity column for use in other tables.

In my pbodb120.ini under [MS_SQLSERVER_SYNTAX] I've changed the GetIndentity line to this:

GetIdentity='Select IDENT_CURRENT (''&TableName'')'

My code looks like this (docid is the identity column):

dw_document_add.update(true, true)

li_docid = dw_document_add.getitemnumber(dw_document_add.getrow(), "docid")

The trouble is it only seems to return the identity about half the time. I think it may be a timing issue. (The records are getting inserted into the database.)

Is there any way I can be sure to get the identity every time?

UPDATE

I've added Identity='@@IDENTITY' to my connection string and it seems to be working now. But I'm not sure if that needs to be in the pbodbxxx.ini file instead.

score 4 · Answer 1 · answered Nov 10 '11 at 17:18

4

The best advice is to not try to reinvent the wheel; the DataWindow already does this. In the DataWindow painter, go into Rows / Update Properties, and at the bottom you'll see Identity Column. Select your identity column (it needs to be in your SQL statement already), and PB will retrieve the identity value after INSERTs.

Good luck,

Terry.

answered Nov 10 '11 at 17:18

Terry

6,160
17
16

I've already done that. I've got the Identity Column set to docid, but it still only fills it in about half the time. The records always get into the database, but the docid doesn't end up back in the datawindow column. – Slapout Nov 10 '11 at 19:10
Any chance on the failure cases a trigger is executing, trashing the variables used to retrieve the value? I've never had any problems with SQL Server 2008 myself, so it might be worth looking for something specific environmentally. – Terry Nov 11 '11 at 03:36
Dumb question now that I'm re-reading: Any particular reason you're using ODBC instead of the native driver? Do you have the same problems with the native driver? The ODBC driver may be what's getting in your way of getting identity values; I've had ODBC drivers do worse! – Terry Nov 11 '11 at 05:18
I am using the native driver. Is setting the GetIdentity in the pbodb120.ini file something you should only do for the ODBC driver? I've added Identity='@@IDENTITY' to my connection string and it seems to be working now. – Slapout Nov 14 '11 at 19:58

score 1 · Answer 2 · edited Aug 05 '14 at 14:12

1

And I wouldn't trust the GetRow() call either.

You should already have the row number in a local variable for the dw row you inserted, otherwise you wouldn't have had a proper argument for all the SetItem() calls that certainly preceeded the Update() call.

edited Aug 05 '14 at 14:12

Bill the Lizard

398,270
210
566
880

answered Nov 10 '11 at 19:08

NoazDad

608
3
9

score 0 · Answer 3 · edited Feb 06 '13 at 10:16

I am using PB 11.5 and MSSQL 2005.

I connect using SQL SNC driver. I always add identity=SCOPE_IDENTITY() to my connection string.

If you use @@identity you might run into a trouble if your application is multiuser. Also, I recommend you to use, if it is possible, stored procedure datawindow update. I mark my identity column as OUTPUT parameter. In stored procedure I call SELECT @id_column = SCOPE_IDENTITY() after INSERT clause. And I never had trouble with it.

PowerBuilder: Get identity value after insert into Sql Server table

3 Answers3