Re: Perl DBI and Foreign Keys

$dbh->last_insert_id is generally the way to go, despite the caveats which seem to be putting you off. Unless you're trying to write something that's compatible with multiple database backends, you don't need to worry about them, you only have to make it work with the database you're using. (And if you are trying to target multiple db engines, you probably want to use a higher-level abstraction on top of DBI anyway, so that you don't have to deal with the inconsistencies of SQL syntax from one db engine to the next.)

If you really don't think you can accept working with last_insert_id, then there's also the option of doing your insert, then SELECT id FROM Participants WHERE Url = $the_url_i_just_inserted, since you say the url is unique, but it seems a bit wasteful to do that extra query when last_insert_id is sitting right there, just waiting to be used for this exact purpose.

Comment on Re: Perl DBI and Foreign Keys Select or Download Code

Replies are listed 'Best First'.
Re^2: Perl DBI and Foreign Keys by Marshall (Canon) on Apr 08, 2019 at 10:05 UTC
Thanks! I wanted to hear from somebody who had used this function. My target DB's are MySQL and SQLite. If I have code that works with both of those DB's, I'm fine. It appears that last_insert_id will work with both of these DB's. I did consider the write, then read back to get the auto id, but that is rather goofy sounding and may not be possible in certain situations. I'm starting a new project using SQlite. The DB access is just a tiny part, but an important part.	[reply]
Re^3: Perl DBI and Foreign Keys by hdb (Monsignor) on Apr 08, 2019 at 12:30 UTC
And if you do not like last_insert_id, you can always build an index generator yourself, like `max( index_column ) + 1`.	[reply] [d/l]
Re^4: Perl DBI and Foreign Keys by Marshall (Canon) on Apr 08, 2019 at 12:45 UTC
I don't think that would work as a substitute for last_insert_id due to race conditions with multiple writers. I can read a table. Calculate the number of rows. And wind up with a different number of rows than you read doing the same thing if somebody else inserted a row during this process.	[reply]
Re^5: Perl DBI and Foreign Keys by soonix (Chancellor) on Apr 08, 2019 at 12:55 UTC
Re^6: Perl DBI and Foreign Keys by afoken (Chancellor) on Apr 09, 2019 at 23:24 UTC
Re^3: Perl DBI and Foreign Keys by dsheroh (Monsignor) on Apr 09, 2019 at 07:29 UTC
I wanted to hear from somebody who had used this function. My target DB's are MySQL and SQLite. In that case, since it wasn't explicitly stated before: I have personally used `last_insert_id` with both MySQL and SQLite.	[reply] [d/l]
Re^3: Perl DBI and Foreign Keys by afoken (Chancellor) on Apr 09, 2019 at 18:37 UTC
See also ~~Re: SELECT LAST_INSERT_ID does not work~~ SELECT LAST_INSERT_ID does not work. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply]