Re: Win32::ODBC and MoreResults()

I'm pretty sure the problem is that you can't run multiple statements at once .. but why does the double-SELECT work? Dunno.. my guess is that it's treating it implicitly like a UNION ?? If you really need these two statements executed as one then created a stored proc for them.. otherwise just break it into two calls:

$db->Sql("INSERT INTO T_Test (C_Data) VALUES ('data')");
$db->Sql("SELECT GETDATE() AS C_ID");
$db->FetchRow();
print "2 : ".$db->Data("C_ID")."\n";
[download]

Looking once more a the Win32::ODBC docs, for MoreResults() it says "Not all drivers support this." .. but still, even if your driver does support it, i doubt it can handle it for mixed INSERT and SELECT statements.

Comment on Re: Win32::ODBC and MoreResults() Download Code

Replies are listed 'Best First'.
Re^2: Win32::ODBC and MoreResults() by Lyndley (Novice) on Aug 11, 2005 at 12:30 UTC
Cheers for the reply. =) Yep stored procedure was going to be plan B, didn't really want to go down that route though to be honest, is just something extra to manage insetad of having it all in one script and I reckon this should work without it anyway. I can't really do them as seperate $db->SQL's as what I'm actually trying to do is run an INSERT then retrieve the @@IDENTITY value of the last insert, needs to be done in one transaction really. Thus... `INSERT INTO T_Test (C_Data) VALUES ('data'); SELECT @@IDENTITY AS C_ID` [download] The multiple SELECTS in one statement does and should work, two statements in one transaction basically produces two result sets and it's the MoreResults() method that tells ODBC to move on to the next result set. I don't think it works like a UNION as there isn't a requirement for the two SELECT's to have the same number of columns as a UNION statement would.	[reply] [d/l]
Re^3: Win32::ODBC and MoreResults() by davidrw (Prior) on Aug 11, 2005 at 12:36 UTC
Did you try it as two separate statements? It should still be in the same transaction -- do you have the transaction BEGIN'd properly? Win32::ODBC supports transactions if the underlying db driver does.	[reply]
Re^4: Win32::ODBC and MoreResults() by Lyndley (Novice) on Aug 11, 2005 at 13:03 UTC
Interesting I tried this which is what I think you were suggesting... `#!perl use Win32::ODBC; my $db=new Win32::ODBC('DB_SERVER'); my $sql="INSERT INTO T_Test (C_Data) VALUES ('data');"; #2 $db->Sql($sql); sleep(20); $sql="SELECT \@\@IDENTITY AS C_ID;"; $db->Sql($sql); $db->FetchRow(); print $db->Data("C_ID")."\n";` [download] It runs the insert, pauses for 20 seconds, gets the last identity value and outputs to the screen. During the 20 second pause you can rerun the same script but without the pause in that version and both versions pull the correct identity value. This can be verified by watching the database through query analyzer. I guess it mean the the @@IDENTITY value must be unique to each session which I didn't realise.... and which of course sorts my main issue so ta for that. =D Would still be nice to know why the INSERT-SELECT fails when the SELECT-SELECT works while using MoreResults() if anyone knows though.	[reply] [d/l]