On Sat, Aug 5, 2017 at 6:26 PM, Igor Korot <ikoro...@gmail.com> wrote:
> Hi,
>
> Did you try bringing it to SAP?
>
> Thank you.
>
> On Sat, Aug 5, 2017 at 2:39 PM, Dan Cooperstock at Software4Nonprofits
> <i...@software4nonprofits.com> wrote:
> > Yes my direct SQL testing used all caps and worked fine.
> >
> > There is no error message. It's just that PowerBuilder's built-in
> mechanism that should retrieve the identity key column's value after an
> insert is done using its DataWindow control, that is based on the setting I
> gave in my first post,
> >
> > GetIdentity="Select currval('GEN_&TableName')"
> >
> > isn't working - the value doesn't get filled in. I have no way of
> knowing whether that code isn't getting called, isn't working, or what. (I
> tried a trace option in PowerBuilder, and it didn't show any call to that
> code, but it also didn't show any call to the corresponding code in my
> setup for Firebird SQL, where this works perfectly.)
> >
> > I really need responses from people who have successfully done this with
> PowerBuilder, because I think it's an issue about the combination of
> PowerBuilder and PostgreSQL, not PostgreSQL alone.
> >
> > --------------------------------------------------------
> > Dan Cooperstock
> > DONATION and ACCOUNTS web site: http://www.Software4Nonprofits.com
> > Email: i...@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 subject line
> "UNSUBSCRIBE", and we will immediately remove you from our mailing list, if
> you are on it.
> >
> >
> > -----Original Message-----
> > From: Rob Sargent [mailto:robjsarg...@gmail.com]
> > Sent: August 5, 2017 5:30 PM
> > To: Dan Cooperstock at Software4Nonprofits <i...@software4nonprofits.com
> >
> > Cc: Forums postgresql <pgsql-general@postgresql.org>
> > Subject: Re: [GENERAL] PostgreSQL with PowerBuilder, and Identity keys
> (serials)
> >
> >
> >> On Aug 5, 2017, at 3:12 PM, Dan Cooperstock at Software4Nonprofits <
> i...@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 naming in my existing DB I’m
> using for the app, Firebird SQL, so the changes to make it work with either
> DB would be as minimal as 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: i...@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 subject line
> "UNSUBSCRIBE", and we will immediately remove you from our mailing list, if
> you are on it.
> >>
> >>
> >> From: Melvin Davidson [mailto:melvin6...@gmail.com]
> >> Sent: August 5, 2017 4:46 PM
> >> To: Dan Cooperstock at Software4Nonprofits
> >> <i...@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 generate a 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#DATAT
> >> YPE-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 unless enclosed in double
> quotes.
> >>
> >>
> >> On Sat, Aug 5, 2017 at 4:09 PM, Dan Cooperstock at Software4Nonprofits <
> i...@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 Identity keys – 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 it uses to know how to deal
> with different aspects of different DBs (PBODBxxx.INI) for Postgres,
> including the following line for 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, who can give me some pointers
> on how get this to work?
> >>>
> >>> Thanks.
> >>>
> >>> --------------------------------------------------------
> >>> Dan Cooperstock
> >>> DONATION and ACCOUNTS web site: http://www.Software4Nonprofits.com
> >>> Email: i...@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 subject line
> "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?
> >
> >
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>I’m on PostgreSQL 9.6, 64-bit Windows.
>That really is the correct name for the sequence, because I’m not using
SERIAL.> GetIdentity="Select currval('GEN_&TableName')"
>isn't working
I
*f you are not sure what the correct name is (or is generated), then use
the information schema to see what sequence names have actually been
generated.SELECT sequence_schema, sequence_name FROM
information_schema.sequences*
* ORDER BY 1, 2;*
*It might also be that you have not done a SELECT nextval(your_sequnce)
before SELECT currval(your_sequence) in that session.**currval*
*Return the value most recently obtained by nextval for this sequence in
the current session. (An error is reported if nextval has never been called
for this sequence in this session.) *
*lhttps://www.postgresql.org/docs/9.6/static/functions-sequence.html
<https://www.postgresql.org/docs/9.6/static/functions-sequence.html>*
*If that is the case, then you can get the current sequence value by
treating the sequence like a table.*
*EG: SELECT seqeunce_name,*
* last_value*
* FROM your_seqeunce_name;*
*FYI, the structure of sequence tables is as follows:*
* seqeunce_name name*
* last_value bigint*
* start_value bigint*
* increment_by bigint*
* max_value bigint*
* min_value bigint*
* cache_value bigint*
* log_cnt bigint*
* is_cycled boolean*
* is_called boolean*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
