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

Please could someone explain why the following code. 
 


#! perl -w scipt


use strict;
use warnings;
use DBI;

use DBD::ODBC;


my ($data_source, $database, $user_id, $password) = qw( ************ *
+********** ******** *********); 
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;


my $Stored_procedure;
    
my $Standardisation;
my $Disease_cat;
my $Sex;
my $Sex_code;
my $Constraint_ref;
my $ICD_start_9;
my $ICD_end_9;
my $ICD_start_10;
my $ICD_end_10;
my $ICD_start_9_B;
my $ICD_end_9_B;
my $ICD_start_10_B;
my $ICD_end_10_B;
my $ICD_start_9_C;
my $ICD_end_9_C;
my $ICD_start_10_C;
my $ICD_end_10_C;
my $yearfrom;
my $yearto;
my $min_age;
my $max_age;
my $region;
my $Instance_name;
my $Geo_scope;
my $number_of_years;
my $unique_identifier;

my $Query;


$Stored_procedure = "make_geo_mortality_count_C_test";
    
$Standardisation = "\"EuropeanStandard\"";
$Disease_cat = "\"All_Cancer\"";
$Sex = 1;
$Sex_code = "\"MF\"";
$Constraint_ref = 1;
$ICD_start_9 = "\"A140\"";
$ICD_end_9 = "\"A208\"";
$ICD_start_10 = "\"C45\"";   
$ICD_end_10 = "\"C97\"";  
$ICD_start_9_B = "\"A140\""; 
$ICD_end_9_B =  "\"A208\"";  
$ICD_start_10_B = "\"C45\"";  
$ICD_end_10_B =  "\"C97\"";  
$ICD_start_9_C =  "\"A140\"";  
$ICD_end_9_C =  "\"A208\""; 
$ICD_start_10_C = "\"C45\""; 
$ICD_end_10_C = "\"C97\"";  
$yearfrom = 2003;
$yearto = 2004;
$min_age = 0;
$max_age = 75;
$region = "\"Community\"";
$Instance_name = "\"Random_place\"";
$Geo_scope = "\"Random_town\"";
$number_of_years = ($yearto - $yearfrom)+1;
$unique_identifier = "\"tttttttttttttttttttttttttttttttttttt\"";

$Query = "EXEC ".$Stored_procedure." ".$Standardisation.", ".$Disease_
+cat.", ".$Sex.", ".$Sex_code.", ".$Constraint_ref.", ".$ICD_start_9."
+, ".$ICD_end_9.", ".$ICD_start_10.", ".$ICD_end_10.", ".$ICD_start_9_
+B.", ".$ICD_end_9_B.", ".$ICD_start_10_B.", ".$ICD_end_10_B.", ".$ICD
+_start_9_C.", ".$ICD_end_9_C.", ".$ICD_start_10_C.", ".$ICD_end_10_C.
+", ".$yearfrom.", ".$yearto.", ".$min_age.", ".$max_age.", ".$region.
+", ".$Instance_name.", ".$Geo_scope.", ".$number_of_years.", ".$uniqu
+e_identifier."\;";

print "$Query";

my $sthB_A =  $dbh->prepare("$Query") or die "Couldn't prepare query: 
+".$dbh->errstr;

$sthB_A->execute() or die "Couldn't execute query: ".$sthB_A->errstr;

[download]
gives me the following error message. 


DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL 
+Server]Err
or converting data type nvarchar to int. (SQL-42000)(DBD: st_execute/S
+QLExecute
err=-1) at 20_October_2004_D.pl line 185.
Couldn't execute query: [Microsoft][ODBC SQL Server Driver][SQL Server
+]Error con
verting data type nvarchar to int. (SQL-42000)(DBD: st_execute/SQLExec
+ute err=-1
) at 20_October_2004_D.pl line 185.
EXEC make_geo_mortality_count_C_test "EuropeanStandard", "All_Cancer",
+ 1, "MF",
1, "A140", "A208", "C45", "C97", "A140", "A208", "C45", "C97", "A140",
+ "A208", "
C45", "C97", 2003, 2004, 0, 75, "Community", "Random_place", "Random_t
+own", 2, "tttttt
tttttttttttttttttttttttttttttt";

[download]

Replies are listed 'Best First'.
Re: Error message puzzle
by Mutant (Priest) on Oct 25, 2004 at 10:09 UTC

    This is actually an error message passed from SQL Server, rather than Perl or DBI. The stored procedure you're using has some of it's paramaters as type int, but it's getting a string.

    This might be solved by using placeholders in your query, eg:

    $var1 = 'text';
$var2 = 1;
$var3 = $foo;

$Query = "EXEC $Stored_procedure ?, ?, ?";

my $sthB_A =  $dbh->prepare($Query) or die "Couldn't prepare query: ".
+$dbh->errstr;

$sthB_A->execute($var1, $var2, $var3) or die "Couldn't execute query: 
+".$sthB_A->errstr;

    [download]
    The execute will then figure out what needs to be quoted, etc.
[reply]
[d/l]
Re: Error message puzzle
by cLive ;-) (Prior) on Oct 25, 2004 at 17:14 UTC
    An aside: since "" interpolates variables and qq{} is the same (except you don't need to escape quotes in it) you might find this easier to read: 
    $Query = qq{
   EXEC $Stored_procedure $Standardisation, $Disease_cat,
        $Sex, $Sex_code, $Constraint_ref, $ICD_start_9,
        $ICD_end_9, $ICD_start_10, $ICD_end_10, $ICD_start_9_B, 
        $ICD_end_9_B, $ICD_start_10_B, $ICD_end_10_B,
        $ICD_start_9_C, $ICD_end_9_C, $ICD_start_10_C,
        $ICD_end_10_C, $yearfrom, $yearto, $min_age,
        $max_age, $region, $Instance_name, $Geo_scope,
        $number_of_years, $unique_identifier;
);

    [download]
    Just makes things a little easier on the eye :)

    cLive ;-)

[reply]
[d/l]
Re: Error message puzzle
by Anonymous Monk on Oct 25, 2004 at 10:18 UTC
    I've fixed the problem by converting all the varchar in the stored procedure to nvarchar.
[reply]

Back to Seekers of Perl Wisdom