Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

large number of placeholders

by Anonymous Monk
on Mar 26, 2005 at 11:50 UTC ( #442466=perlquestion: print w/replies, xml ) Need Help??

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

when using dbi and odbc,
the %prophash has keys as field names and the values as the
values of the fields..
the code snippet is
my @a=keys(%prophash); my $a=join(',',@a); my @b=values(%prophash); my $b=join(',',@b); my $temp="insert into maintable ($a) values($b)"; $sth = $dbh->prepare ($temp); $sth->execute();
the error says

DBD::ODBC::db prepare failed: [Microsoft][ODBC Microsoft Access Driver +] Syntax error in INSERT INTO statement. (SQL-42000)(DBD: st_prepare/ +SQLPrepare err=-1) at line 132, <DATA> line 283.
can any one help me with this?

janitored by ybiC: Balanced <code> tags around snippet

Replies are listed 'Best First'.
Re: large number of placeholders
by jhourcle (Prior) on Mar 26, 2005 at 12:33 UTC

    The way you're doing things, you're not using placeholders. You're hard coding the values into the SQL string (which is bad, for many reasons). Unless all of the values are numbers, or already quoted and escaped, it'll fail horribly.

    I'd suggest the following (and as with BrentDax, I'd recommend looking at the string you're creating when you have errors).

    my @fields = sort keys %prophash; my $sql = 'INSERT INTO maintable ('.join(',',@fields) .') values ('.join(',',('?')x(scalar @fields)).')'; my $sth = $dbh->prepare($sql) or die "Can't prepare statement\n\n$sql\n\n$dbh->errstr()"; $sth->execute(@prophash{@fields}) or die "Can't execute statement : $db->errstr()";

    Yes, the sort may seem extraneous, but it'll make it so your sql statements are more likely to be consistent between systems, and so have its execution plan cached.

Re: large number of placeholders
by BrentDax (Hermit) on Mar 26, 2005 at 12:07 UTC

    This is probably more of a SQL problem than a Perl problem. I suggest you add the line warn "Statement: $temp"; right before the prepare call, and make sure your SQL statement is what you expect it to be. (In particular, do you need to quote the values? If you do, the $dbh->quote() method might be your friend.)

    --Brent Dax
    There is no sig.

Re: large number of placeholders
by cog (Parson) on Mar 26, 2005 at 13:43 UTC
    Have you tried:

    print $temp;


    That will probably help you out. Look at the output and you'll probably find that you have a value which is empty and therefore resulting into two consecutive commas (or perhaps a comma next to a bracket), or maybe something similar...

    Another thing you could try would be:

    use Data::Dumper; print Dumper \%prophash;

    Just to see if everything's as expected... but I suspect the print $temp will probably give the error away...

    Anyway, I think you should use placeholders instead of constructing the queries yourself... your program will be much safer that way...

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://442466]
Approved by BrentDax
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (4)
As of 2022-05-28 08:29 GMT
Find Nodes?
    Voting Booth?
    Do you prefer to work remotely?

    Results (99 votes). Check out past polls.