1. Daniel Vivier
  2. PowerBuilder
  3. Friday, 4 August 2017 22:19 PM UTC

I'm working on a port of my PB2017 version of my app to PostgreSQL. I have updated all copies of the PBODB170.INI file to include the following sections (irrelevant bits omitted):

[PostgreSQL]
PBSyntax='PGSQL_SYNTAX'
PBTableOwner='NO'
...
PBNoCatalog='YES'

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

I am quite sure this section is being found, because before I added this section, every time I connected in PB to Postgres I was warned about the fact that the catalog tables couldn't be created. Now I'm not warned about that. (So that's the PBNoCatalog setting working.)

However, in DWs with Identity Columns, the identity value isn't being retrieved properly after an Update. I'm quite sure from the Postgres docs that the syntax for GetIdentity in the code above is good.

I've tested this in the DB painter, to make sure my DDL is correct in that it is correctly using a SEQUENCE to insert the identity values. To test that, I did an INSERT statement to insert a row (with no identity column value), did a SELECT to retrieve that row and saw that the identity column was filled in, and did the Select currval('GEN_...') which correctly returned that same identity column I saw in the SELECT of the row itself.

So, any bright ideas of what I might be missing here? Thanks.

Daniel Vivier Accepted Answer Pending Moderation
  1. Thursday, 10 August 2017 21:29 PM UTC
  2. PowerBuilder
  3. # 1

Brad Mettee just gave me the correct answer:

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

Apparently you have to double the single quotes around GEN_&TableName. If only there was some detailed documentation for the PBODB*.INI files ...

Comment
There are no comments made yet.
Daniel Vivier Accepted Answer Pending Moderation
  1. Sunday, 6 August 2017 18:52 PM UTC
  2. PowerBuilder
  3. # 2

OK, I think I've narrowed this down to what I think is a PB bug (or at least something someone with some internals knowledge needs to tell me how to work around), and have submitted as it a bug. What helped me was turning on SQL statement logging in PostgreSQL (as suggested by someone on one of their mailing lists I posted this same question to).

The first thing I found out is that when I put the GetIdentity line in the PBODB170.INI file like this:

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

the double quotes were persisted into the query sent to the Postgres DB (which is not how INI files are supposed to work - they should have been trimmed). So after some experimentation to see how quotes worked in PB's INI file functions I changed that to:

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

Oddly enough the embedded single quotes are fine in terms of the reading from the INI file - it seems to just strip the outer quotes and leave the inner ones.

However, when I looked at the SQL logging after that, when doing a save in a DW where that should have been called, was that Postgres was receiving it as shown - the variable &TableName wasn't being translated into the actual table name the DW was acting upon. My guess is that it's because it's within single quotes, and PB's internals for dealing with GetIdentity interpreted that to mean it was a section that couldn't have variables expanded in it. The PostgreSQL syntax does require exactly single quotes there, around the generator name that generates the identity keys.

If I get a solution back to my bug report I will post it here as well. (Of course, if this further information allows someone reading this community post to give me the answer, that would be great too!)

Comment
  1. David Peace (Powersoft)
  2. Wednesday, 9 August 2017 08:55 AM UTC
Nice work Dan, I hope you can post the outcome from Appeon support here soon.

  1. Helpful
  1. Brad Mettee
  2. Wednesday, 9 August 2017 13:30 PM UTC
I just re-checked the pbodb170 file. Every instance of the &TableName that is wrapped in quotes has TWO single quotes on each end. Have you tried it that way? (there are no doublequotes in the below line)

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



What shows up in the log if you use this?

   GetIdentity=’Select max(IDENTCOL) from &TableName’

  1. Helpful
  1. Daniel Vivier
  2. Thursday, 10 August 2017 21:28 PM UTC
Oh my, that works with the doubled single quotes around GEN_&TableName! I have switched to that. I'll also post this as a separate top-level reply, for those who aren't clicking into replies to replies!



I got IDENTCOL from the Bruce Armstrong article about this. It should have be &ColumnName, and that works too, though I really don't think it's as good a solution at all, and if there were weird timing errors, I believe it could give incorrect answers.

  1. Helpful
There are no comments made yet.
Daniel Vivier Accepted Answer Pending Moderation
  1. Saturday, 5 August 2017 15:13 PM UTC
  2. PowerBuilder
  3. # 3

I've tried turning on tracing, and I see SQL stuff happening in the dbtrace.log file, but even in another DB (Firebird) where the identity key stuff is working perfectly, nothing goes into the trace file for retrieving the identity key. Can anyone suggest anything else I can do to trace this better? Anyone got identity keys working in Postgres? (I don't care about returning tables from procedures, that someone else wrote about.) Or does anyone else have any killer issues with Postrgres? I was assuming it would be fine since it's supported in Appeon Web.

Comment
There are no comments made yet.
Mike S Accepted Answer Pending Moderation
  1. Saturday, 5 August 2017 13:05 PM UTC
  2. PowerBuilder
  3. # 4

IMO, postgresql isn't supported very well yet in pb.

for example, PB does not support the returns table syntax in a stored function (or maybe doesn't just when there are parameters)

CREATE OR REPLACE FUNCTION customer_cdname_search(searchon text)
RETURNS TABLE(cus_cd text, cus_name text)
AS $$
SELECT customer.cus_cd, customer.cus_name
FROM customer
WHERE customer.cus_cd like UPPER(searchon)
;
$$ language 'sql';



the function runs fine in pgAdmin.

powerbuilder throws an error:
column searchon does not exist:

----------------
anyway, i gave up on postgresql until PB support has been added.
I don't have any CR number to reference, but ZhaoKai added it as a requirement.

 

 

 

Comment
There are no comments made yet.
Daniel Vivier Accepted Answer Pending Moderation
  1. Friday, 4 August 2017 22:24 PM UTC
  2. PowerBuilder
  3. # 5

P.S. I tried changing the GetIdentity row to:

    GetIdentity=’Select max(IDENTCOL) from &TableName’

and it still doesn't work.

Comment
  1. Daniel Vivier
  2. Thursday, 10 August 2017 21:20 PM UTC
I believe it does work if you use:



GetIdentity=’Select max(&ColumnName) from &TableName’



However, I wouldn't be entirely happy using that, both from an efficiency standpoint, and the fact that it seems to me it could be error prone, if two people were inserting to the same table at exactly the same time, they might get the same number.

  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.