Re: [GENERAL] PostgreSQL with PowerBuilder, and Identity keys(serials) - Mailing list pgsql-general

> On Aug 5, 2017, at 3:12 PM, Dan Cooperstock at Software4Nonprofits <info@software4nonprofits.com> wrote:
>
> I’m on PostgreSQL 9.6, 64-bit Windows.
>
> That really is the correct name for the sequence, because I’m not using SERIAL. (I needed everything to match the
namingin my existing DB I’m using for the app, Firebird SQL, so the changes to make it work with either DB would be as
minimalas possible.) The setup of tables I’m using with this sort of thing are like the following example: 
>
> CREATE SEQUENCE GEN_CATEGORY MINVALUE 0;
>
> CREATE TABLE CATEGORY(
>   CATEGORYNUM    SMALLINT NOT NULL DEFAULT NEXTVAL('GEN_CATEGORY'),
>   DESCRIPTION        VARCHAR(20) NOT NULL,
>   CONSTRAINT PK_CATEGORY PRIMARY KEY (CATEGORYNUM)
> );
>
> So as you can see GEN_ plus the tablename is indeed correct. The default on the CATEGORYNUM column is definitely
working,which I tested with direct SQL commands: after inserting a row (with the CATEGORYNUM not specified in the
INSERT),if I SELECT currval(‘GEN_CATEGORY’), it gives me the correct value, which is also what got saved in that
column.
>
> --------------------------------------------------------
> Dan Cooperstock
> DONATION and ACCOUNTS web site: http://www.Software4Nonprofits.com
> Email: info@Software4Nonprofits.com
> Phone: 416-423-7722
> Mail: 57 Gledhill Ave., Toronto ON M4C 5K7, Canada
>
> If you do not want to receive any further emails from Software4Nonprofits, please reply to this email with the
subjectline "UNSUBSCRIBE", and we will immediately remove you from our mailing list, if you are on it. 
>
>
> From: Melvin Davidson [mailto:melvin6925@gmail.com]
> Sent: August 5, 2017 4:46 PM
> To: Dan Cooperstock at Software4Nonprofits <info@software4nonprofits.com>
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] PostgreSQL with PowerBuilder, and Identity keys (serials)
>
> >GetIdentity="Select currval('GEN_&TableName')"
>
> FYI, it would be helpful to specify the PostgreSQL version & O/S, but generically speaking, in PostgreSQL, when you
generatea sequence  
> by specifying serial as data type, the name takews the form of tablename_columnname_seq, so in your case, try
>
> https://www.postgresql.org/docs/9.4/static/datatype-numeric.html#DATATYPE-SERIAL
>
> GetIdentity="Select currval('tablename_column_seq')".
>
> BTW, in PostgreSQL, it is NOT recommended to use mixed case object names, as all names are converted to lowercase
unlessenclosed in double quotes. 
>
>
> On Sat, Aug 5, 2017 at 4:09 PM, Dan Cooperstock at Software4Nonprofits <info@software4nonprofits.com> wrote:
>> I’m trying to get a Postgres DB version of an application I write in PowerBuilder working. The thing I’m stuck on is
Identitykeys – what you set up with the SERIAL attribute or SEQUENCEs / GENERATORs in Postgres. 
>>
>> I have the sequence set up and clearly working. And in PowerBuilder, I have added a section I found online to a file
ituses to know how to deal with different aspects of different DBs (PBODBxxx.INI) for Postgres, including the following
linefor retrieving an identity key after saving a record, when the DB automatically fills in the value: 
>>
>> GetIdentity="Select currval('GEN_&TableName')"
>>
>> That obviously depends on the generator being named “GEN_” plus the table’s name – which is true in our case.
>>
>> But nothing like that is happening. Does anyone else have PostgresSQL working with PowerBuilder and identity keys,
whocan give me some pointers on how get this to work? 
>>
>> Thanks.
>>
>> --------------------------------------------------------
>> Dan Cooperstock
>> DONATION and ACCOUNTS web site: http://www.Software4Nonprofits.com
>> Email: info@Software4Nonprofits.com
>> Phone: 416-423-7722
>> Mail: 57 Gledhill Ave., Toronto ON M4C 5K7, Canada
>>
>> If you do not want to receive any further emails from Software4Nonprofits, please reply to this email with the
subjectline "UNSUBSCRIBE", and we will immediately remove you from our mailing list, if you are on it. 
>>
>>
>
>
>
> --
> Melvin Davidson
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.

I don’t see that you posted any sort of error message?
Also don’t test that sequence more than 64K times as you’ll certainly get a number too big for smallest :)
Did your direct sql testing actually use ALL CAPS?