large number of placeholders

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

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();
[download]

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.
[download]

can any one help me with this?

janitored by ybiC: Balanced <code> tags around snippet

Comment on large number of placeholders Select or Download Code

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()";` [download] 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.	[reply] [d/l]
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.) =cut --Brent Dax There is no sig.	[reply] [d/l] [select]
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;` [download] 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...	[reply] [d/l] [select]