PowerBuilder and SQL Server: Tips On Working Together
I recently worked on a project that used PowerBuilder for the front end and SQL Server for the database. I've worked with both of these technologies in the past but this was the first time I used them together. Below are some notes about the things that I learned regarding PB and SQL Server working together.
Single Quoted Strings
If you do any work with SQL statements in SQL Server Management Studio (SSMS), you'll find that SQL Server only accepts strings if they are surrounded by single quotes. You can use double quotes in the PowerBuilder painters and they'll get translated properly on their way to SQL Server. But you may want to just go ahead and use single quotes everywhere, especially if you are trying to track down a problem by copying SQL from PowerBuilder and pasting it into SSMS.
Identity Columns
One of my tables had its primary key set in SQL Server to autoincrement. In my datawindow I would insert a row and then do an update. I was expecting the value SQL Server assigned to the primary key column to appear in my datawindow. However, this didn't happen -- at least not reliably -- until I changed my connection string. I had to add Identity='@@IDENTITY':
Database='DATABASENAME',Provider='SQLNCLI10',Identity='@@IDENTITY'
Bit fields
I had one of my fields defined in SQL Server as a bit. If you looked at the data in SSMS you would see this represented as 1s and 0s. However, when accessing this data from PowerBuilder, every 1 was being converted to a -1. Since I was in control of the schema, I changed the field to tinyint to solve the problem. Of course, if you don't have control of the database layout, I'm not sure how you would handle that.
Files Required
On your client machines, in addition to the PB runtimes (including PBSNC120.dll, which is for SQL Server), you also need to install the SQL Server Native Client runtime. These come as an msi installer package from Microsoft. When you install the SQL Server client, you'll need to do so as an administrator. Otherwise you'll get a cryptic error message along the lines of "0: -1".
There's a 32-bit and a 64-bit version of the runtime. The 64-bit version won't run on a 32-bit version of Windows and vice versa.
Footnote: My project used PowerBuilder Classic version 12.1 and SQL Server 2008
Single Quoted Strings
If you do any work with SQL statements in SQL Server Management Studio (SSMS), you'll find that SQL Server only accepts strings if they are surrounded by single quotes. You can use double quotes in the PowerBuilder painters and they'll get translated properly on their way to SQL Server. But you may want to just go ahead and use single quotes everywhere, especially if you are trying to track down a problem by copying SQL from PowerBuilder and pasting it into SSMS.
Identity Columns
One of my tables had its primary key set in SQL Server to autoincrement. In my datawindow I would insert a row and then do an update. I was expecting the value SQL Server assigned to the primary key column to appear in my datawindow. However, this didn't happen -- at least not reliably -- until I changed my connection string. I had to add Identity='@@IDENTITY':
Database='DATABASENAME',Provider='SQLNCLI10',Identity='@@IDENTITY'
Bit fields
I had one of my fields defined in SQL Server as a bit. If you looked at the data in SSMS you would see this represented as 1s and 0s. However, when accessing this data from PowerBuilder, every 1 was being converted to a -1. Since I was in control of the schema, I changed the field to tinyint to solve the problem. Of course, if you don't have control of the database layout, I'm not sure how you would handle that.
Files Required
On your client machines, in addition to the PB runtimes (including PBSNC120.dll, which is for SQL Server), you also need to install the SQL Server Native Client runtime. These come as an msi installer package from Microsoft. When you install the SQL Server client, you'll need to do so as an administrator. Otherwise you'll get a cryptic error message along the lines of "0: -1".
There's a 32-bit and a 64-bit version of the runtime. The 64-bit version won't run on a 32-bit version of Windows and vice versa.
Footnote: My project used PowerBuilder Classic version 12.1 and SQL Server 2008
Comments
You helped answer a question that I have open on Stack Overflow and it has a bounty attached. I would like to award the bounty to you as you helped the most, but you have not entered an answer, but rather added comments. If you'd enter an answer to the question, something to the effect that Sybase only supports IE for Web Forms applications then I will award the bounty to you. I can't award it to you without an answer to select. :) Thanks - Rich (aka DisplacedGuy.com)