Re: mssql query

Er, just a minor point not directly related, but I think you want to do that _right_after_ the insert. In other words not as two seperate calls to the DBI object. I do it something like this:

my ($index)=$dbh->selectrow_array('insert into foo (bar,baz) values (1
+,2) select @@identity');
[download]

I think if you do it as a do() and as a select() seperately the @@identity will be cleared. Or you can set up a stored proc and then have the stored proc return the identity after the insert for you.

BTW, the @@identity only exists on Sybase and MSSql to the best of my knowledge.

---
demerphq

_{First they ignore you, then they laugh at you, then they fight you, then you win.

-- Gandhi}

Comment on Re: mssql query Select or Download Code

Replies are listed 'Best First'.
Re: Re: mssql query by mpeppler (Vicar) on Dec 01, 2003 at 16:25 UTC
As long as the insert and the `select @@identity` happen sequentially on the same connection you'll be fine: `$dbh->do("insert ... "); $sth = $dbh->prepare('select @@identity'); $sth->execute(); my $data = $sth->fetch;...` [download] What will NOT work is selecting the @@identity value IF the insert happened in a stored procedure, because the @@identity variable is localized. So the following: `$dbh->do("exec some_proc_with_an_insert 1,2,3"); $sth = $dbh->prepare('select @@identity'); $sth->execute(); my $data = $sth->fetch;` [download] will NOT return the @@identity value generated in the stored procedure. This is the reason why you can't get the @@identity value after doing an insert with placeholders (at least with Sybase): any statement with placeholders generates a temporary stored procedure, hence localizing the @@identity variable. Michael	[reply] [d/l] [select]
Re: Re: Re: mssql query by demerphq (Chancellor) on Dec 02, 2003 at 00:08 UTC
Ah. Ok I stand corrected. Thanks for the heads up. Cheers mate. :-) --- demerphq _{First they ignore you, then they laugh at you, then they fight you, then you win. -- Gandhi}	[reply] [d/l]
Re: Re: mssql query by l3nz (Friar) on Dec 01, 2003 at 14:43 UTC
I'm not trying this, but I'd use a semicolon between the first and the second SQL statement. Anyway, a dirty and bad looking solution is to have a separate statement MAX() the value of the identity column (that will likely be a unique key, right?) so you get the last inserted value with no table or key scan, and the disk page will very likely be in RAM as you just used it. It's quite fast, if you can afford the two separate database statements. Of course demerphq solutiuon is better. :-)	[reply]
Re: Re: Re: mssql query by mpeppler (Vicar) on Dec 01, 2003 at 16:32 UTC
I'd use a semicolon between the first and the second SQL statement No semicolon needed between statements with MS-SQL, and in fact the semi-colon would be a syntax error with Sybase. ...a dirty and bad looking solution is to have a separate statement MAX() the value of the identity... Unless you use a transaction around the insert and the `select max()` (which effectively single threads inserts) then there is no guarantee that the max() will be the value that you just inserted (unless of course you know that you are the only process inserting data to that particular table). Michael	[reply] [d/l]