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

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