If you are just trying to avoid another database request, you will probably have trouble.
If you don't care so much about the quanitity of queries, and what you want ( like me ) is to specifically know the value BEFORE you insert the record, what you're looking for are sequences. Sequences are the Oracle/Postgres way of doing autoincrements. A sequence is a standalong database object that generates guarenteed unique numbers for you, which you can then use as indexs. This way, you know the value of the id BEFORE you insert into the database. Now, not all databases support sequences. My problem was writing a cross database application on Oracle/Posgres/MySQL, and MySQL not having sequences ( but the solution could apply to any database without sequences ). My solutions was to implement a sequence emulation layer for MySQL. I'm currently cleaning it up for inclusion in CPAN as DBIx::MySQLSequence. However, the implementation could apply just as easily to any other database system that doesn't have sequences, and the code could probably be fairly easily be ported across to your database. The only trick is to find a lock safe way of adding a value to a single column in a record in your database platform of choice. In MySQL, this was achieved using the little known assigning version of LAST_INSERT_ID(column)... To do it in your DBMS of choice, the solution might vary a little. I've attached my preliminary copy of DBIx::MySQLSequence ( code works, should be largely bug free, but undocumented ), in the hope you can make some use of it. The module is pure perl. Anyone else interested in getting "real" sequence support on MySQL may find it interesting as well. Liscensing is under the same terms as Perl itself. Adam ----- Original Message ----- From: "Troy Sniff" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, March 22, 2002 10:09 AM Subject: RE: Returning auto incremented value on insert > Maybe one of these days I can do a bit more research to find out why. If > I ever do, I'll post my findings. > > Luckily this insert will only be one row at a time. > > I ended up inserting and then reading what I just inserted to grab the > value. > > Troy > > > > -----Original Message----- > > From: John Boucher [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, March 21, 2002 3:42 PM > > To: 'Troy Sniff'; [EMAIL PROTECTED] > > Subject: RE: Returning auto incremented value on insert > > > > > > Just a comment: > > It seems to me that the reason you can't have the ID of the > > last inserted > > row automatically returned in a variable is that some (most?) > > databases > > allow INSERT to insert more than one row at a time. > > This would affect auto-increment as well. > > > > Sure, you may only be inserting one row _now_, but what about > > the times when > > someone inserts many? > > > > > > -----Original Message----- > > From: Troy Sniff [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, March 21, 2002 2:26 PM > > To: [EMAIL PROTECTED] > > Subject: RE: Returning auto incremented value on insert > > > > > > Yeah I figured I would have to go back in. > > > > I have a timestamp being placed in. I can do a select using it and > > another value to get the record number after the insert. > > > > I sure wished Access support @@IDENTITY or something like it. > > > > Troy > > > > > -----Original Message----- > > > From: MacGown, Peter [mailto:[EMAIL PROTECTED]] > > > Sent: Thursday, March 21, 2002 2:51 PM > > > To: 'Troy Sniff'; [EMAIL PROTECTED] > > > Subject: RE: Returning auto incremented value on insert > > > > > > > > > I work with MS Access quite a bit and you can't quite do what > > > you want in > > > Access either. What I do there is create a unique > > > combination of something > > > like the following: > > > > > > TIMESTAMP as datefield > > > USERNAME as text(50) > > > > > > SAVETIME = NOW() > > > > > > I write the record and then, do a select on the record where > > > TIMESTAMP = > > > SAVETIME AND USERNAME = CURRENT USER NAME > > > > > > I suppose you could even use a session number as well if you > > > could get it. > > > > > > I have been programming pretty intensely with MS Access for a > > > number of > > > years now and this is the only way I found to do what you > > > want. I'm not > > > quite sure how to do it in Perl since I am a newbie at it, > > > but I'm sure you > > > could use the same approach. > > > > > > I'm very interested in how you get Perl to work with MS > > > Access databases. I > > > can get it to work using the command line, but I can't get it > > > to work using > > > CGI. > > > > > > I hope that this was helpful. > > > > > > Thanks, > > > Pete MacGown > > > AT&T Broadband > > > > > > -----Original Message----- > > > From: Troy Sniff [mailto:[EMAIL PROTECTED]] > > > Sent: Thursday, March 21, 2002 4:09 PM > > > To: [EMAIL PROTECTED] > > > Subject: Returning auto incremented value on insert > > > > > > > > > I am performing an insert into an access table where the > > > primary key is > > > set to auto increment. > > > > > > When I insert a record, is there an easy way to grab the > > value created > > > by the auto increment without having to go back in read the newly > > > created record? > > > > > > I need the value to display a result such as: > > > > > > "Your information has been added. The record number assigned > > > was 1000. > > > We recommend you write this down for future reference". > > > > > > Troy > > > > > > > > > > > > > >
MySQLSequence.pm
Description: Binary data
