Tech Articles


Defining a PostgreSQL Database Profile in PB2019R3


PB2019R3
PostgreSQL v12 database


Summary:
   Ensure that the database properties are defined correctly for the PostgreSQL database in the DB Painter.

   If those properties are not defined correctly, the PB2019R3 IDE automatically creates the PB Catalog tables in the "public" schema every time it connects to the PostgreSQL (PG) database even if the catalog tables are already defined in the named "PowerBuilder Catalog Table Owner" schema.

 

Details:
In our Oracle schemas, we have the PB Catalog table owner specified as the superuser CHADBA.


Once created in Oracle, the catalog tables are in the CHADBA schema.


The PB IDE opens up smoothly with NO prompt to "Create the PowerBuilder catalog tables."

~~~~~~~~~~~

We are in the process of migrating to PostgreSQL (PG).
 - PG is case sensitive, and in our case all table and columns names are in lower case.
 - Note: Users can migrate to an UPPER or a LOWER case copy. Choose the LOWER case to eliminate a variety of
            issues.

Our PG development database also has the PB catalog tables defined in the chadba schema.
We set up the PG database mostly in the same way we set up the Oracle databases; chadba is in lowercase:






Defined in this manner, the IDE opens up smoothly and does not give you the "PB catalog tables not available" message.

- - - - - - - - - - -

If the setup is NOT done correctly, then when I open PB2019R3 and the connection is to my dev12c_PG database, PowerBuilder creates the complete set of catalog tables in the public schema, but does not populate either of the new the EDT or FMT catalog tables.

Thus when I get into the DB Painter and "select * from pbcatfmt;" the result set is empty. Why? Because the new set of tables defined in "public" are overriding the set of tables previously defined in "chadba".

I have to manually delete the public.pbcat* tables ....

drop table public.pbcatcol;
drop table public.pbcattbl;
drop table public.pbcatfmt;
drop table public.pbcatvld;
drop table public.pbcatedt;
commit;

.... before I have access to the FMT and EDT data again:

-- pbcattbl, pbcatcol, pbcatfmt, pbcatvld, pbcatedt
//drop table public.pbcatcol;
//drop table public.pbcattbl;
//drop table public.pbcatfmt;
//drop table public.pbcatvld;
//drop table public.pbcatedt;
//commit;

select * from pbcatfmt;

 

 

Comments (0)
There are no comments posted here yet

Find Articles by Tag

Open Source Debugger Syntax Performance PowerServer Mobile PowerServer Web Source Control COM JSON Automated Testing PBNI Event Handler OAuth 2.0 NativePDF WinAPI iOS HTTPClient DataWindow JSON Service CI/CD External Functions 64-bit Outlook Elevate Conference Database Object Database Table Schema BLOB Authentication Database Connection Error DataWindow Encryption Encoding PowerScript (PS) MessageBox Event Handling TreeView ODBC Icon Source Code Linux OS ActiveX License Web Service Proxy TLS/SSL RESTClient Text .NET Assembly Android Mobile Git Expression DLL .NET DataStore UI Array Installation Database Table Database Profile Database Table Data Window XML Validation PowerBuilder Compiler 32-bit Event PBDOM RibbonBar Filter Migration RibbonBar Builder Charts PowerBuilder (Appeon) WebBrowser OrcaScript Data PDF REST SnapObjects Branch & Merge Trial Model TFS Excel Authorization Bug Debugging Resize Export DevOps Class Application Jenkins Menu PowerBuilder PBVM Repository CoderObject Graph Variable Web API Debug Deployment UI Themes RichTextEdit Control Transaction Script Sort JSONGenerator OLE Platform Database Painter Interface IDE Stored Procedure API SOAP Windows OS UI Modernization Windows 10 Icons Visual Studio TortoiseGit SVN Oracle OAuth InfoMaker SnapDevelop Testing DragDrop CrypterObject Configuration Messagging PDFlib JSONParser Design SQL Server Export JSON Database PostgreSQL ODBC driver PFC PostgreSQL File Import JSON SDK SQL Import DataType C# GhostScript .NET Std Framework Azure SqlModelMapper SqlExecutor