Would this resolve your issue?

1. Set the SQCLA parameters to lock the table at the lowest level allowed by your database.

2.  If the primary key value is not known at the time the INSERT occurs, then

INSERT X into Y;
COMMIT;

SELECT max (pk_table) FROM Y;

and if required, you can be more specific:

SELECT max (pk_table) FROM Y WHERE <whatever>;

3. If there is a way to get the next PK value and reserve it, the do so and have that value prior to the INSERT.

 Oracle allows this with their SEQUENCES.
   Thus if I have table Y whose pk is "user_id" and the pk has a SEQUENCE NUMBER named "user_id_seq" associated with it, I can call:

select user_id_seq.NextVal into :ll_pk_value from dual;

Hi James;

   Appeon PB currently does not officially support the MySQL DBMS.

Regards .. Chris

Thanks Mike,

I did try and code an embeded SQL statement to do the Insert; however, the PB parser doesn't like the syntax as you have to follow the Insert with a ; and the select statement.  Sort of like this:

INSERT INTO table (column) VALUE ("data");SELECT LAST_INSERT_KEY()

That's where I got hung up, combing them together in PB.

I will try it again, maybe I can get a different syntax.

Thanks so much for the help - greatly appreciated.

Thanks to everyone for the replies.

I'm on the latest version of PB, also in development so all of the PFODBxxx.Ini files are there.

MySQL does provide a method for retrieving the latest insert id, SELECT LAST_INSERT_ID().  Unfortunately, it has to appear immediately after the INSERT statement semicolon and PB doesn't allow that.

The other options seem difficult.  Selecting Max identity is dicey, someone else can insert and in the meantime. Locking the table first would work but not sure if this would lead to a performance issue.

What surprises me is that this is an age old issue going back over a decade or more.  I don't understand why others don't run into this problem (or why Appeon doesn't address it - seems like it was at one time by using the Identity Column on the Update Properties menu). 

I guess it throws me back into the surrogate key debate.  I understand there is passion both ways, to use or not to use. 

I think I will redesign the table structure, eliminate the auto increment key column, and use a CHAR variable containing the name for the key, relying on the database to replace (CASCADE) any changes made to the key to the other tables referenced if required.

Once again, still perplexed as to why these issues remain after all these years.

And again, thanks to everyone for their input - invaluable.

What version of PB are you using?  Did you deploy the PBODBxxx.ini file?  Even if you're not using ODBC you need to deploy that file for autoincrement values.  Here's a snip from an article I'm going to put at the end from PB MVP Bruce Armstrong:

If you are referencing autoincrement columns from DataWindows, you must deploy the PBODB[XXX].INI file with your application regardless of which driver you are using to talk to the database.

I remember I had to do this once for MYSQL many years ago, but I don't remember what I used for the GetIdentity entry, possibly the same as Watcom.  I think I had to create a section for MYSQL, but you can experiment, unless anyone else remembers exactly what they did.

https://blogs.sap.com/2012/10/25/using-autoincrementing-columns-from-a-powerbuilder-datawindow/

myssql doesn't really provide a good way to handle that.  

if you really need to select that value back then some options are:

1) build your own 'reselect row' type logic, but just bring back max(identity).  leave out the identity in the where clause (why reselectrow doesn't work).  assumes the row with the largest identity is your newly inserted row.  most of the time it will be correct.  

2) put a lock on the entire table, insert the row, select the identity with max value, then commit.

3) build your own increment table instead of using identity

Thanks Miguel,

I tried that, but PB gives me an error as it says the row changed between update and the re-retrieve.

I've tried various GetItems after the Update and both before and after the Commit but to no avail.

I think I've used "ReSelectRow()" for that purpose in the past.

regards