1. James Medick
  2. PowerBuilder
  3. Tuesday, 26 May 2020 21:52 PM UTC

I hate to ask this, but is there an effective way to retrieve the key value after insert on a table using Auto Increment?  I've read all the blogs, some say to use the Identify Column on the Update Rows option.  Doesn't work.  Or if it does, the problem is in actually retrieving it (just using GetItemNumber, last row?).  MYSQL will let me do it through Select Last_Insert_id() but only if it is on the same line as the Insert statement.  PB doesn't seem to like this and separating them doesn't work.

I'm sure there is something obvious regarding how to do this but darned if I can find it.

Olan Knight Accepted Answer Pending Moderation
  1. Friday, 29 May 2020 23:11 PM UTC
  2. PowerBuilder
  3. # 1

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;


 

Comment
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 27 May 2020 17:21 PM UTC
  2. PowerBuilder
  3. # 2

Hi James;

   Appeon PB currently does not officially support the MySQL DBMS.

Regards .. Chris

Comment
  1. James Medick
  2. Wednesday, 27 May 2020 21:38 PM UTC
Thanks Armeen for the clarification. This is very important info that I need to share with my clients so they know what they can expect when choosing a technical platform.
  1. Helpful
  1. Armeen Mazda @Appeon
  2. Wednesday, 27 May 2020 21:49 PM UTC
Just to clarify, we already support MySQL for C# projects (i.e. SnapDevelop IDE). We just haven't officially supported for PowerScript projects yet (i.e. PB IDE). But we do have some customers using it through ODBC interface. We will most likely add this to PB IDE also since SnapDevelop IDE supports it...it is just a timing issue.

  1. Helpful
  1. Kevin Ridley
  2. Friday, 29 May 2020 14:19 PM UTC
Just to clarify my response below, you would need a [MYSQL] section in your PBODBxxx.ini file. I had it working with an older version of PB, circa 10.5 with whatever the current MYSQL version was at that time. I don't have access to the code anymore to see what I did with the ini file, but I'm sure I could get it to work again. You'll just have to experiment with that section.
  1. Helpful
There are no comments made yet.
James Medick Accepted Answer Pending Moderation
  1. Wednesday, 27 May 2020 16:40 PM UTC
  2. PowerBuilder
  3. # 3

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.

 

Comment
  1. mike S
  2. Wednesday, 27 May 2020 17:07 PM UTC
i get what you are saying. yeah, i don't see that working at all in pb, its too weird. if mysql supports stored procedure/functions, then maybe try that if you really need to use that feature.

i would just use a nextnumber type of table.

  1. Helpful
  1. James Medick
  2. Wednesday, 27 May 2020 21:39 PM UTC
Mike,

I used a stored procedure as you suggested. Works great and is a really simple solution.



Thanks so much.
  1. Helpful
There are no comments made yet.
James Medick Accepted Answer Pending Moderation
  1. Wednesday, 27 May 2020 15:52 PM UTC
  2. PowerBuilder
  3. # 4

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.

Comment
  1. mike S
  2. Wednesday, 27 May 2020 16:25 PM UTC
the only reason it is a problem with PB is that mysql requires the use of non-standard sql to get the identity efficiently. It will be interesting to see where this ends up on appeon's to do list. sap/sybase neglected so much for so long that appeon still has a lot to do.



You could always code the insert yourself rather than use a datawindow (embedded sql or stored procedure). or you might be able to change the sql on the fly in the sqlpreviewevent to add that database function



sequences make a lot more sense than identity since it is perfect for this use. Most databases have added sequences, i'm surprised oracle didn't add it to mysql since oracle db has always used them



  1. Helpful
There are no comments made yet.
Kevin Ridley Accepted Answer Pending Moderation
  1. Wednesday, 27 May 2020 14:36 PM UTC
  2. PowerBuilder
  3. # 5

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/

Comment
There are no comments made yet.
mike S Accepted Answer Pending Moderation
  1. Wednesday, 27 May 2020 13:58 PM UTC
  2. PowerBuilder
  3. # 6

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

 

Comment
There are no comments made yet.
James Medick Accepted Answer Pending Moderation
  1. Wednesday, 27 May 2020 13:06 PM UTC
  2. PowerBuilder
  3. # 7

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.

 

 

Comment
  1. Miguel Leeuwe
  2. Monday, 1 June 2020 22:24 PM UTC
Yes, that's a likely thing to happen. Sorry. Maybe there's some way you could get around the row changed between retrieve and update, using a second transaction object. But that's an ugly patch.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 27 May 2020 05:42 AM UTC
  2. PowerBuilder
  3. # 8

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

regards

Comment
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.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.