dbi multiple statements please help!

sapphyre · #1 (**permalink**) 07-23-09, 10:56

Hello

I'm trying to write a function that returns the id of an inserted col. Here's the code I have so far:

Code:

## sqlInsert: takes a table and a value and inserts the value into the table, returns the id.
sub sqlInsert(){
	my($table,$values) = @_;
	my $qry = $dbh->prepare("DECLARE @GUID uniqueidentifier;
				SET @GUID = newid();
				INSERT INTO scans VALUES (@GUID, '2009-07-23');
				SELECT @GUID");
	$qry->execute();
	return $qry->fetchrow_array;
}

Here's how I'm connecting to the database:

Code:

use DBI;

...

## Connect to the database!
	my $dbh = DBI->connect("dbi:ODBC:$dsn",$usr,$pss);

And I'm getting the following error:

DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver] Incorrect syntax near ';'.
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver] Incorrect syntax near ','.
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver] Statement(s) could not be prepared.

Anybody know what I'm doing wrong? Thanks

sco08y · #2 (**permalink**) 07-24-09, 21:21

Either DBD::ODBC doesn't understand multiple statements within a single prepare or the SQL Server ODBC driver can't handle it.

DBD:

ybase claims to handle it, and while it is possible to connect to SQL Server using the Sybase driver (SQL Server actually used to be Sybase and still uses the TDS protocol) it may be flaky.

Also, aside from slightly modifying your SQL, you'll have to change how you connect and be aware of some limitations.

The more general solution is to bundle your code in a stored procedure.

sco08y · #3 (**permalink**) 07-24-09, 21:33

Also, you could simply use a Perl module to generate the GUID and avoid using multiple statements. If you search for Win32 and GUID on CPAN you should find a module that makes the same library call SQL Server is using to generate the GUID.

Internet Services

Web Development

Digital Marketing

Consumer Tech