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'')'

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

          [PGSQL_SYNTAX]
          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  
   VOLATILE   
   ROWS 1000  
 AS $BODY$  
   BEGIN RETURN QUERY SELECT books.id, books.title FROM books WHERE books.title like $1 ;  
   END;  
 $BODY$;  

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 ;

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.