GetIdentity in PBODB170.INI section for PostgreSQL apparently not working

1
0
-1

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.

Question Tags: 

Answers

Dan Cooperstock answered GetIdentity in PBODB170.INI section for PostgreSQL apparently not working

1
0
-1

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

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

and it still doesn't work.

Dan Cooperstock's picture

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.

Mike S answered GetIdentity in PBODB170.INI section for PostgreSQL apparently not working

1
0
-1

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.

 

 

 

Dan Cooperstock answered GetIdentity in PBODB170.INI section for PostgreSQL apparently not working

1
0
-1

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.

Dan Cooperstock answered GetIdentity in PBODB170.INI section for PostgreSQL apparently not working

1
0
-1

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

David Peace's picture

Nice work Dan, I hope you can post the outcome from Appeon support here soon.

bmettee@pchotshots.com's picture

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’

Dan Cooperstock's picture

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.