1. Simone Olianti
  2. PowerBuilder
  3. Tuesday, 23 January 2024 11:23 AM UTC

Hello everyone i'm trying to understand how PostgreSQL is behaving against Powerbuilder 2022 R3, in particular the auto-increment columns.

I've create a new postgres DB with a simple table called "test"; Reading the documentation i see that there are two ways to handle identity columns:

Support for auto-increment column

PostgreSQL supports the auto-increment column with the following two methods:

  • Method 1: GetIdentity='Select currval(''GEN_&TableName'')'

  • Method 2: GetIdentity='Select currval(''&TableName._&ColumnName._seq')'

    Method 2 uses the serial to create the auto-increment column.

These methods require that the sequence name follows the name conversions specified by GetIdentity in pbodb.ini, for example, gen_TableName, TableName_ColumnName_seq. If the sequence name is not in the required format, the identity value cannot be obtained automatically.

i've added the identity column as "serial" data type from postgresql PGadmin4 and it created automatically the sequence script.

i've also enabled the PBODB.INI [PGSQL_SYNTAX] this way:

AddMembers='GRANT &GroupID TO &UserID'
DeleteMembers='REVOKE &GroupID FROM &UserID'
;GetIdentity='Select currval(''GEN_&TableName'')'
GetIdentity='Select currval(''&TableName._&ColumnName._seq')'
; Alternative technique if table has identity or serial column
GetCatalogIdentity='SELECT a.attname as name FROM pg_catalog.pg_class as c Left JOIN pg_catalog.pg_attrdef as p ON c.oid = p.adrelid LEFT JOIN

I've created a simple pb app with a datawindow, but when i try to insert a new record on it, the identity column does not populate automatically like it does on SQL Anywhere. The id is generated correctly cos if i do a retrieve after the update it shows correctly.
I guess i missing something, but any help would be appreciated

i'll add some screeshots showing how i configured the table

tia,
simone

 

Attachments (3)
Accepted Answer
Simone Olianti Accepted Answer Pending Moderation
  1. Wednesday, 24 January 2024 15:52 PM UTC
  2. PowerBuilder
  3. # Permalink

i've finally managed to make it work. i'll share here the solution in case someone else encounters the same issue.
I was running the app from the pb IDE and i was configuring the wrong pbodb.ini

i've changed the one found in the runtime folder (C:\Program Files (x86)\Appeon\Common\PowerBuilder\Runtime 22.2.0.3289) but i've forgot to change the one inside C:\Users\user\AppData\Local\Appeon\PowerBuilder 22.0 

the correct configuration of pbodb.ini on my environment to make the identity columns work right inside the datawindow is this:
;GetIdentity='Select currval(''GEN_&TableName'')'
GetIdentity='Select currval(''&TableName._&ColumnName._seq')'

Comment
  1. Chris Pollach @Appeon
  2. Wednesday, 24 January 2024 19:53 PM UTC
Hi Simone;

Thank for that update & the great news!

Regards ...Chris
  1. Helpful
  1. Fernando Gilli
  2. Wednesday, 24 January 2024 20:27 PM UTC
for me happens the same on Postgres, so

as solution you described, now its all ok.

thanks Simone!
  1. Helpful
There are no comments made yet.
Kevin Ridley Accepted Answer Pending Moderation
  1. Tuesday, 23 January 2024 13:30 PM UTC
  2. PowerBuilder
  3. # 1

First I'll admit that I'm no Postgres expert, but I've never had a problem.  Typically these are identity columns and the primary key for a table.  Are you saying you want this to be an auto increment column but not be the primary key for the table?  I noticed in your screen shot you have the column defined as not identity.  I have setup mine as identity columns and the primary key for the table.  I've never had to create a sequence.  Maybe you have some other requirement for it not to be identity/primary key?

 

Comment
  1. Simone Olianti
  2. Tuesday, 23 January 2024 15:49 PM UTC
dw_1.insertrow(0)

if dw_1.update() = 1 then

commit;

end if



Using SQL anywhere, immediately after the update the value of my identity column is auto-populated in the datawondow object.

this behaviour failing if i do the same using the same table in a Postgresql db. I mean the id is interlally generated correctly in the table column but it is not showing immediately in my datawindow until i do a dw_1.retrieve() after the update.

Sorry if i am not being clear, but i don't know how to explain better than this





  1. Helpful
  1. Kevin Ridley
  2. Wednesday, 24 January 2024 16:47 PM UTC
Yes now it's clear. It would've been helpful originally if you said that the issue was that the identity column was displayed in your datawindow and the value wasn't displaying after the update. I also suggested you try ReSelectRow which should have worked, but looks like you didn't try. So I wrote a quick app to test this out and used the ReSelectRow function and got an error saying "Row changed between retrieve and reselect". This appears to be a bug, so I'm going to report it to Appeon and see what they say. IMO it should handle this and probably works for other DBMS's.
  1. Helpful
  1. Chris Pollach @Appeon
  2. Wednesday, 24 January 2024 19:57 PM UTC
Hi Kevin;

It's probably not a bug as when PB sees an Identity Column or an AutoIncrement column (depending on the DBMS ) ... after a DW Update(), the PBVM / DB Interface will automatically execute a "ReSelectRow" command "behind he scenes) to get the assigned value. So your ReSelectRow was probably conflicting the DB interface. HTH

Regards .. Chris
  1. Helpful
There are no comments made yet.
Simone Olianti Accepted Answer Pending Moderation
  1. Tuesday, 23 January 2024 16:38 PM UTC
  2. PowerBuilder
  3. # 2

to be clear, i'm inserting a new record on the datawindow, populating the columns and then executing the update.
the id is not populated on the datawindow.

dw_1.insertrow(0)
//changing dw items
if dw_1.update() = 1 then
    commit;
end if

the DataWindow does not automatically picking up the identity column and does not handle the value for it.
I need to call a retrieve after the update to make the dw show the new id inserted.
Using sql anywhere the id is picked up automatically just after the update

Attachments (2)
Comment
There are no comments made yet.
Ronnie Po Accepted Answer Pending Moderation
  1. Tuesday, 23 January 2024 19:21 PM UTC
  2. PowerBuilder
  3. # 3

Hi Simone,

Did you verify that the Update Properties of the DataWindow has the proper column specified as the Identity Column?

Comment
  1. Simone Olianti
  2. Wednesday, 24 January 2024 07:40 AM UTC
hi Ronnie, thanks for your response. i double checked the update properties and everything is ok. The record is updated on the db including the id, but not showing inside the dw. I'm using the latest postgres (16), i think i'll try an older one to see if thats the issue
  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.