in reply to Sequences, last_insert_id, SQLite, and Oracle
My solution to provide total platform agnosticism (not in Perl, BTW, but the solution still applies) was to skip the assorted platform specific autoincrement features and roll my own, using a single row/single column table to hold an integer used as a unique ID on all insert statements.
The pseudo-code would look something like this:
It's neither pretty or very efficient (I certainly wouldn't recommend it for a high- or even medium-volume multiuser environment), but it will work on any database platform which implements the most basic of SQL commands.
The pseudo-code would look something like this:
where sequenceTable has a single integer column called "id".sub insertRecord { my $newRow = shift; my $id = getSequence; execute "insert table (id, data) values ($id, $newRow);"; } sub getSequence { my $seq = execute "select id from sequenceTable;"; #now increment sequence execute "update sequenceTable set id = id + 1;"; return $seq; }
It's neither pretty or very efficient (I certainly wouldn't recommend it for a high- or even medium-volume multiuser environment), but it will work on any database platform which implements the most basic of SQL commands.
In Section
Seekers of Perl Wisdom