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

Dear Monks,

Can any monks find an explanation for why this? 

    my $sthD = $dbh->prepare("INSERT into ICD_specifications_for_disea
+se (Range_key, A, B, C, D, E, F, G) VALUES
(1,'A',9,390,459),
(2,'A',10,I00,I99),
(13,'B',9,'NULL','NULL'),
(14,'B',10,'C43','C43'),
(15,'C',9,'460','519'),
(16,'C',10,'J00','J99'),
(17,'D',9,'490','496'),
(18,'D',10,'NULL','NULL'),
(19,'E',9,'430','438'),
(20,'E',10,'I60','I69');") or die "Couldn't prepare query: ".$dbh->err
+str;

[download]

It gives the following message: 


syntax error at Creating_table_J.pl line 295, near "->errstr;"
Execution of Creating_table_J.pl aborted due to compilation errors.

[download]

Replies are listed 'Best First'.
Re: DBI error
by gmax (Abbot) on Dec 01, 2003 at 15:59 UTC

    Your error message doesn't seem to agree with what I see. Let me give you a few hints, though.

    • Your code fragment, as you posted it, compiles fine. Therefore the error should not be in these lines.
    • From a SQL point of view, your query is wrong in several places:
      • You are declaring 8 fields but you are passing only 5 values.
      • You are passing unquoted values "I00" and "I99", which will fail once your program manages to send your query to the DBMS. Perhaps that you are using "I" instead of "1"?
    • You should be better off using placeholders. See DBI Recipes for examples.
    • Your query is using multiple VALUES clauses. AFAIK, only MySQL and IBM DB2 support this syntax. If you are using another DBMS, check its docs to be sure you can do it.
    • As for your specific problem, since the error is not visible in this example, perhaps reading Before asking a database related question ... could give you some ideas.
     _  _ _  _  
(_|| | |(_|><
 _|
[reply]
    A reply falls below the community's threshold of quality. You may see it by logging in.
Re: DBI error
by Art_XIV (Hermit) on Dec 01, 2003 at 17:56 UTC

    It's probably not causing the error that you getting, but you my want to keep an eye on your NULLs.

    'NULL' will be a literal, while NULL will be an actual database null.

    Hanlon's Razor - "Never attribute to malice that which can be adequately explained by stupidity"
[reply]
[d/l]
[select]
Re: DBI error
by 3dbc (Monk) on Dec 01, 2003 at 21:09 UTC 
    $sth = $dbh->prepare("$sql") or die $dbh->errstr;

$sth->execute() or die $dbh->errstr;

I would double check your $sql...  or possibly do multiple inserts rat
+her than using the range operator within the sql plus it would make t
+he code more platform independent.

    [download]
[reply]
[d/l]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom