Search code examples
oledbsql-server-2014powerbuilder

Identity Value not being returned by SQL Server 2014

We have a PowerBuilder client application (PB 12.5) that has been in production for the last 6 years and has been working fine against SQL MSDE and MS-SQL Server 2012.

Recently, we've upgraded some installations (about 40) to SQL Server 2014. In about half of these installations, we seeing an issue whereby an Identity column is not returned to the DataWindow after the update method is called. Here is the code in question:

If lds_Update.Update( ) < 1 then
   lb_Error = True
   ls_Temp = "The inspection header could not be created."
Else
   ll_InspID = lds_Update.GetItemNumber(1, "Insp_ID")
   If (ll_InspID <= 0) or Isnull(ll_InspID) then 
       lb_Error = True  // This is being hit as ll_InspID is null
       ls_Temp = "Could not retrieve the inspection identity."
   End If
End If

The DataWindow has the correct setting for the Identity column and Primary key.

enter image description here

This is not happening on all installations but only some. All installations are pretty much identical. The PB application is installed on a server and is launched from a thin-client.

The connection string is as follows:

SQLCA.DBParm = "PROVIDER='SQLOLEDB',DATASOURCE='ServerName',PROVIDERSTRING='Database=DBName',Identity='SCOPE_IDENTITY()'"

We have tried against several test installations and SQL Server 2014 Express also but it works fine. It is only on some installations that this fails and we don't know why. We've tried changing to Identity='@@IDENTITY' as well but there is no effect.

Does anyone have any ideas?

Solution

  • You can change the query used for retrieving the identity value via the pbodbxxx.ini file (where xxx is the version of PB you are using - 120 in this case for PB12). Despite the naming convention of the file, Sybase has chosen to make an OLE DB connection responsive to some settings within the file. In this particular case, change the following line in the [MS_SQLSERVER_SYNTAX] section

    from GetIdentity='Select @@identity'

    to GetIdentity='SELECT SCOPE_IDENTITY()'

    If you choose this path, you will need to distribute the INI file with your application. Note that this works in PB11 - I assume it still holds true for PB12.

    Interesting article: https://blogs.sap.com/2012/10/25/using-autoincrementing-columns-from-a-powerbuilder-datawindow/