User Rating: 3 / 5

Star ActiveStar ActiveStar ActiveStar InactiveStar Inactive

One of the new features in PowerBuilder 2017 R2 is support for PostgreSQL.  We're going to take a look at using this new (to PowerBuilder) database.

Installing PostgreSQL and ODBC driver

PostgreSQL is an open source database licensed under the PostgreSQL license, similar to the MIT license.  Downloads for the Windows operating system are available from here.  PowerBuilder uses ODBC to access PostgreSQL, so you'll need to get an ODBC driver as well.  The one from PostgreSQL is available here, although there are third party ODBC drivers as well.  When I was testing out this new feature I used the BookTown sample database from O'Reilly Media.

Creating an ODBC database profile

After importing the booktown data using pgAdmin (the admin tool for PostgreSQL), I defined the a system ODBC profile for the database as follows:

And a database profile in PowerBuilder as follows:

Using PostgreSQL in the Database Painter

Once you do that, you can connect to PostgreSQL and work with it in the database painter.  All of the items that you normally would use from the database painter are fully functional.  You can see the lists of functions and procedures, tables and views, run SQL in the ISQL pane, edit data, modify tables, etc.  You can't (at least yet) see the text of functions and procedures.  

RPCFUNC declarations

As you can with some other database (e.g., Oracle) you can declare database procedures and functions as local external functions on a user object of type transaction:

And then use that as a method of the transaction object in a script:

One thing you have to watch out for here is that PostgreSQL allows functions to be declared with arguments that have data types but not names.  That triple_price function that I created as a local external function was pasted in that way, which caused a compile error when I saved the transaction object.  The solution is simple:  if there wasn't an argument name on the function in PostgreSQL, simply add one to the RPCFUNC declaration.

Using PostgreSQL from DataWindows

PostgresQL also supports identity column functionality.  There are actually two different methods.  The first, older method (since at least version 6.4) is to declare the column that you want to autoincrement to be of type serial.   The second method, introduced in version 10, uses the SQL standard identity column syntax.  Both methods use a sequence in the background to generate the values, which fortunately means that the way you get the most recently generated value for the sequence for that session is the same: the select currval ( ) method.  By default, the sequence used to generate the value for either method is named using the pattern __seq.  If you look in the PBODB170.INI file where PowerBuilder stores the database specific tweaks it uses for ODBC database, you'll find this entry that has been added to support PostgreSQL:

          GetIdentity='Select currval(''GEN_&TableName'')'

That option doesn't actually work for me.  What I did was modify that value to the following, which does work:

          GetIdentity='Select currval(''&TableName._&ColumnName._seq')'

Two of the tables in that table list above aren't from the BookTown demo.  Instead, they are tables I created in order to test out the identity column support for PostgreSQL from the PowerBuilder DataWindow.  I'm happy to report that the DataWindow automatically picked up the identity column and handled the values for it correctly for both methods of declaring it.

If you like to use stored procedures for your DataWindows that is supported as well.  You'll need to do two things:

1.  Name your parameters ( not required by PostgreSQL, but required by PowerBuilder)
2.  Return a TABLE

We'll use this sample for the booktown database:

 CREATE OR REPLACE FUNCTION public.get_books_by_title(  
      p_title text)  
   RETURNS TABLE(id integer, title text)   
   LANGUAGE 'plpgsql'  
   COST 100  
   ROWS 1000  
 AS $BODY$  
   BEGIN RETURN QUERY SELECT, books.title FROM books WHERE books.title like $1 ;  

The other thing you will need to do is check the "Strip Parameter Names" option in the database connection:

Now just go ahead and create the stored procedure based DataWindow like you would for any other database.

Remember that the connection string that you use for the application will also need the StripParameterNames options as well.

          // Profile postgresql
​          SQLCA.DBMS = "ODBC"
          SQLCA.AutoCommit = False
          SQLCA.DBParm = "ConnectString='DSN=PostgreSQL35W;UID=dba;PWD=sql',StripParmNames='Yes'"
          CONNECT ;


With the changes that R2 provides to support PostgreSQL, it becomes a great additional database choice.  And given that SQL Anywhere is no longer packaged with the product, it's great to have another no cost database available for development, training and demos.


Comments (5)

  1. mike S

be aware that postgresql stored procedure datawindows are not supported.  however, they work if you don't use any parameter(s) in your procedure (function).

Your account does not have privileges to view attachments in the comment
  1. Bruce Armstrong

Yes, I missed that when I was reviewing the capability.  I also didn't covered embedded SQL.  That's probably because I don't use either of those significantly in production applications.

Your account does not have privileges to view attachments in the comment
  1. mike S

stored functions/procedures DO work, just not with parameter names.  

The odbc connection MUST have "strip parameter names" checked/selected.  This is in the syntax section of the database profile setup. 

In the connection string it is:    StripParmNames='Yes'

Your account does not have privileges to view attachments in the comment
  1. Richard Murray

I just installed PB 2019 R3 and I have a couple of issues, first there does not seem to be a PBODB190.ini, I found PBODB.INI in user\appdata\local\appeon\Powerbuilder 19.0 and I modified it with the three sections for PostgreSQL. I downloaded psqlodbc_13_00_00-x64 driver and installed it. The issue I am having I created a datawindow and set all of the options to match your example above and then in code, i do a dw.InsertRow(0) and I don't get the next available row back, I always get 1 back no matter what I have tried! Does anyone have an idea what the problem might be? Also, is that the ODBC.INI I should be modifying?

Your account does not have privileges to view attachments in the comment
  1. Bruce Armstrong

I ran the 2019 R3 IDE and watched with ProcessMonitor and confirmed that it was accessing the file in the user\appdata folder.

Note that in the more recent versions of PowerBuilder both of the methods of getting the identity are provided in the INI file. The default remains the one that was used in 2017, but the alternative I recommended was added as a commented out alternative. The only change you should need to make to the file is to comment out the original method and uncomment out the other. To that end, I'm not sure I know what you mean by "modified it with the three sections for PostgreSQL"

Lastly, that statement assumes that the column you are using for the key was created as an identity column. If you didn't do that, then there won't be a sequence to grab the value from.

Your account does not have privileges to view attachments in the comment
There are no comments posted here yet