OLEDB – Anvil of Time https://anvil-of-time.com Nature Forges Everything on the Anvil of Time Thu, 11 Mar 2021 16:19:52 +0000 en-US hourly 1 PowerBuilder – SQL Native Client and DisableBind https://anvil-of-time.com/powerbuilder/powerbuilder-sql-native-client-and-disablebind/ Mon, 19 Mar 2012 22:51:59 +0000 http://anvil-of-time.com/wordpress/?p=1257 Okay so you are upgrading your application, perhaps using the latest version of PowerBuilder (12.5) and SQL Server (2008 R2), and decide to use the SQL Native Client (SNC) to take advantage of new features in the database. Now like many organizations which use Microsoft SQL Server, you may have gone to OLE DB in the distant past (say around PB v7 days) for any number of reasons.

Something to look out for is the Disable Bind setting in your connection string. In OLEDB the default is DisbleBind=1 which disables the binding. For the SNC the default is DisableBind=0 which tells Powerbuilder to bind input parameters to a compiled SQL statement. More information can be found at the following Sybase link.

]]> PowerBuilder ‘Gotcha’ – SQLServer OLE DB and Identity columns https://anvil-of-time.com/powerbuilder/powerbuilder-gotcha-sqlserver-ole-db-and-identity-columns/ Thu, 03 Mar 2011 01:20:05 +0000 http://anvil-of-time.com/wordpress/?p=723 So I get a new machine at work and I’m busy loading all my development tools on it as well as changing settings and all such stuff. SQL Server and PowerBuilder get installed and upgraded, connected to Source Control, and I run the application from within the IDE. Things are fine until I insert a new set of records and save – BAM! ‘column such-and-such does not allow NULLs’ blah, blah, blah…

A quick trip to the debugger shows that the parent record is being inserted fine, but when I call my function to get the Identity value of the item just inserted, I’m getting back a NULL value.

After digging around awhile my boss remembers something. You need to modify the pbodb11.5.ini file in the appropriate sections

[MS_SQLSERVER_SYNTAX]
GetIdentity='Select SCOPE_IDENTITY()'

This has been around since 2009, see link.

and

[Microsoft SQL Server]
ServerCursor='No'

From Sybase web site:

ServerCursor database parameter
When you use the OLE DB database interface with a Microsoft SQL Server database and retrieve data into a DataWindow, server-side cursors are used to support multiple command execution. If this has a negative impact on performance, try increasing the size of the Block database parameter to 500 or more, or adding the following line to the [Microsoft SQL Server] section in the PBODB initialization file to turn off server-side cursors:

ServerCursor = ‘NO’
The ServerCursor parameter can be used only in the PBODB initialization file.

]]>