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

Hi,

I'm having a porlbme using Win32::ODBC to run commands on an SQL 2000 server.

I have a connection set up, connecting and running remote SQL queries via perl isn't a problem, it's just in this particular instance Im trying to do something specific.

I'm basically trying to run the following SQL which can be run through query anaylzer without issues 

INSERT INTO T_Test (C_Data) VALUES ('data');
SELECT @@IDENTITY AS C_ID

[download]

The table T_Test consists of one auto-incrementing identity column and one data column, the insert puts a new row in and the select returns the unique id of the last row.

Implementing the SQL call in perl is proving to be a problem though.

I understand I need to use the MoreResults() function but it doesn't seem to work when one first SQL call is an INSERT.

The folowing code illustrates the problem... 

#!perl

use Win32::ODBC;
my $db=new Win32::ODBC('DB_SERVER');

#$sql="INSERT INTO T_Test (C_Data) VALUES ('data');SELECT \@\@IDENTITY
+ AS C_ID;";    #1

#$sql="INSERT INTO T_Test (C_Data) VALUES ('data');SELECT GETDATE() AS
+ C_ID;";    #2
$sql="SELECT GETDATE() AS C_ID;SELECT DATEADD(day,-10,GETDATE()) AS C_
+ID";    #3
$db->Sql($sql);
$db->FetchRow();
print "1 : ".$db->Data("C_ID")."\n";
$db->MoreResults();
$db->FetchRow();
print "2 : ".$db->Data("C_ID")."\n";

[download]

Note the SQL that's hashed out.

This works fine, two SQL SELECTS, both dates are output, however hash out the line marked #3 and unhash line #2.. 

#!perl

use Win32::ODBC;
my $db=new Win32::ODBC('DB_SERVER');

#$sql="INSERT INTO T_Test (C_Data) VALUES ('data');SELECT \@\@IDENTITY
+ AS C_ID;";    #1

$sql="INSERT INTO T_Test (C_Data) VALUES ('data');SELECT GETDATE() AS 
+C_ID;";    #2
#$sql="SELECT GETDATE() AS C_ID;SELECT DATEADD(day,-10,GETDATE()) AS C
+_ID";    #3
$db->Sql($sql);
$db->FetchRow();
print "1 : ".$db->Data("C_ID")."\n";
$db->MoreResults();
$db->FetchRow();
print "2 : ".$db->Data("C_ID")."\n";

[download]

And this fails, nothing out printed to the screen although the INSERT does actually happen.

Note that it's the first statement (sql #1) that's the proper one, it's just #2 and #3 nbetter illustrate the point.

Replies are listed 'Best First'.
Re: Win32::ODBC and MoreResults()
by davidrw (Prior) on Aug 11, 2005 at 12:22 UTC
    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.
[reply]
[d/l]
      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]
        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]

Back to Seekers of Perl Wisdom