Thursday, February 01, 2018

PowerBuilder 2017 R2 New Feature: PostgreSQL support

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 ( <sequencename > ) method.  By default, the sequence used to generate the value for either method is named using the pattern <tablename>_<columnname>_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:

          [PGSQL_SYNTAX]
          GetIdentity='Select currval(''GEN_&TableName'')'

Apparently "GEN_&TableName' is macro that it able to determine the name of the sequence from the table name.

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.



Summary

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.


No comments: