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

I seem to have an ODBC problem. My query works fine when I run it using the Microsoft SQL 2000 Query Analyzer. But when I run the same EXEC query through a Perl Program the stored procedure that is activated by the EXEC statement stalls half way through the process. I have tried to fiddle with the permission setting of the table that I think is read at the time of the stall, but with no success. The error message I get is: 

U:\2004_2005\Development\Perl_development\20_October_2004>perl 20_Octo
+ber_2004_E.pl
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Inval
+id cursor
state (SQL-24000)(DBD: dbd_describe/SQLNumResultCols err=-1) at 20_Oct
+ober_2004_E.pl line 290.
Couldn't execute query: [Microsoft][ODBC SQL Server Driver]Invalid cur
+sor state
(SQL-24000)(DBD: dbd_describe/SQLNumResultCols err=-1) at 20_October_2
+004_E.pl line 290.

[download]
The important bits of Perl code for this are: 
#! perl -w scipt


use strict;
use warnings;
use DBI;

use DBD::ODBC;


my ($data_source, $database, $user_id, $password) = qw( <ip address> S
+eries_requests ********* ********* ); 
my $conn_string = "driver={SQL Server}; Server=$data_source; Database=
+$database; Trusted_Connection=yes"; 

my $dbh = DBI->connect( "DBI:ODBC:$conn_string" ) or die $DBI::errstr;

[download]
Then, after the query is defined. 
  my $sthB_A =  $dbh->prepare("$Query_A") or die "Couldn't prepare que
+ry: ".$dbh->errstr;
  
  $sthB_A->execute() or die "Couldn't execute query: ".$sthB_A->errstr
+;

[download]

Replies are listed 'Best First'.
Re: ODBC problem
by mpeppler (Vicar) on Nov 12, 2004 at 10:19 UTC
    Actually - the important bit is the actual SQL statement that you try to run. Without knowing that it's unlikely that someone here will be able to tell why you get the "invalid cursor state" message.

    Michael

[reply]
      The point at which I think that the stored procedure statement fails is about here: 
       print 'UPDATE ICD_specifications_for_disease_ICD_9_ICD_10_calc_spec S
+ET ICD_end_adjusted';
 UPDATE ICD_specifications_for_disease_ICD_9_ICD_10_calc_spec SET ICD_
+end_adjusted = (ICD_end + '9') FROM ICD_specifications_for_disease_IC
+D_9_ICD_10_calc_spec;


 if exists(select 1 from INFORMATION_SCHEMA.tables where table_name = 
+'Geo_mortality_count') begin raiserror ('Concurrent database access e
+rror',1,1); GOTO start; end 

 CREATE TABLE Geo_mortality_count (
    Key_m INT IDENTITY,
    Cause VARCHAR(30) NULL,
    ICD INT NOT NULL,
    Output_Area VARCHAR(20) NULL,
    Sex NUMERIC NOT NULL,
    Age_start INT NULL,
    Age_end INT NULL,
    Age_range VARCHAR(10) NULL,
    Person_count NUMERIC DEFAULT '1' NOT NULL
 );

      [download]
[reply]
[d/l]
        This error message in conjuction with PRINT was discussed here.
[reply]

Back to Seekers of Perl Wisdom