Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    1

    Unanswered: dbi multiple statements please help!

    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

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    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.

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •