Finding the next avaliable id in a SQLite database

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I currently have a SQLite database which I access through DBD::SQLite. I need to find the next avaliable empy ID # according to a users input... this is what I have compe up with.

 
   sub find_avaliable {
    # Create the SQL statement
    $sth = $dbh->prepare( "select CODE from INV where CODE = $user_inp
+ut");
    # Execute the SQL statement and generate a result set
    $sth->execute();
    #Get results and process
    ($CODE) = $sth->fetchrow_array;
    if ($CODE ne "") {
        $user_input+=1;
        &find_avaliable;
    } else {
        #$COUT->Write("$user_input");
        return;
    }
    }
[download]

There must be a better way of doing this?

Comment on Finding the next avaliable id in a SQLite database Download Code

Replies are listed 'Best First'.
Re: Finding the next avaliable id in a SQLite database by Ovid (Cardinal) on Apr 29, 2004 at 20:11 UTC
What problem are you trying to solve? Are you trying to figure out which ID you will need to use when inserting a record? In later versions (you probably want 2.3.4 or greater), SQLite will insert the ID for you if you try to insert a NUL value. If you need to find out what ID was inserted, just call the new (as of 2.2.3) `sqlite_last_insert_rowid()` function. Also, you have a security hole in that code. Please read up about SQL injection attacks. You can fix the security hole using placeholders. `$sth = $dbh->prepare( "select CODE from INV where CODE = ?"); # Execute the SQL statement and generate a result set $sth->execute($user_input);` [download] Cheers, Ovid New address of my CGI Course.	[reply] [d/l]
Re: Finding the next avaliable id in a SQLite database by matija (Priest) on Apr 29, 2004 at 20:10 UTC
As long as you declare your ID as `INTEGER PRIMARY KEY` , and insert a `NULL` value into it, it will autoincrement, and you can get the value it had through `$dbh->func('last_insert_rowid')` That is the best way to do it. If you find the largest value, increment it by one and write a record with it, you will introduce a race condition - and that will bite you when you least expect it (probably once you've gone into production).	[reply]
Re: Finding the next avaliable id in a SQLite database by halley (Prior) on Apr 29, 2004 at 20:19 UTC
Definitely learn to think in terms of inserting the record and asking the database what ID was assigned. Don't try to "predict" what the "next" ID will be. This misconception spawns what people call a "race condition." That's when two separate processes are generating results independently, but there are some tasks which should have been centrally coordinated to avoid mistakes or duplication. If written the wrong way, your program will crash in flames or introduce very subtle bugs, depending on which database engine or even how many CPUs your machine has. What's worse is that the first few times you run it, it will appear to work perfectly fine... but then start flaking out as soon as you forget about the issue. -- `[ e d @ h a l l e y . c c ]`	[reply]
Re: Finding the next avaliable id in a SQLite database by dragonchild (Archbishop) on Apr 29, 2004 at 20:07 UTC
If you read the SQLite documentation (linked in from the DBD::SQLite POD), you'll find a database keyword you can use. ------ We are the carpenters and bricklayers of the Information Age. Then there are Damian modules.... sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon.* - flyingmoose	[reply]