Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

Re: Perl DBI and Foreign Keys

by dsheroh (Prior)
on Apr 08, 2019 at 07:57 UTC ( #1232275=note: print w/replies, xml ) Need Help??

in reply to 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.

Replies are listed 'Best First'.
Re^2: Perl DBI and Foreign Keys
by Marshall (Abbot) on Apr 08, 2019 at 10:05 UTC
    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.

      And if you do not like last_insert_id, you can always build an index generator yourself, like max( index_column ) + 1.

        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.

      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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1232275]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (3)
As of 2020-06-07 03:43 GMT
Find Nodes?
    Voting Booth?
    Do you really want to know if there is extraterrestrial life?

    Results (42 votes). Check out past polls.