large number of placeholders

[Anonymous Monk]

hi
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 alldb.pl line 132, <DATA> line 283.

can any one help me with this?

janitored by ybiC: Balanced <code> tags around snippet

[jhourcle]

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.

[BrentDax]

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.)

[cog]

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...