jezgoldstone
Posts: 1
|
| Posted: 06/14/2005, 12:19 PM |
|
Hi,
I'm new at this game, and tearing my hair out...
I've successfully got some Perl that queries a database without a predicate (no WHERE statement). When I attempt to retrive specific data, I get either no results returned or errors ;
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. (SQL-07002)(DBD: st_execute/SQLExecute err=-1) at database connection2.pl line 16.
Could not execute SQL statement, maybe invalid? at database connection2.pl line 16
My perl code looks like this;
use DBI;
use warnings;
#open connection to Access database
$dbh = DBI->connect('dbi:ODBC:Deliveries');
#construct SQL statement
$sqlstatement=
"SELECT DestinationAddress,CustomerContactNumber
FROM DeliveryScheule
WHERE DeliveryID = \"6636\"\n";
print "SQL:"."\n".$sqlstatement."\n";
#prepare and execute SQL statement
$sth = $dbh->prepare($sqlstatement);
$sth->execute ||
die "Could not execute SQL statement, maybe invalid?";
#Put the results in an array
#@row=$sth->fetchrow_array;
($DestinationAddress,$CustomerContactNumber)=$sth->fetchrow_array;
#output database results
# while (@row=$sth->fetchrow_array)
# { print "@row\n" }
# $sth->dump_results;
while ($row=$sth->fetchrow_hashref)
{print "Address: $row->{DestinationAddress}\t
Phone Number: $row->{CustomerContactNumber}\n"};
print $sth->rows."\n";
If I play around with the SQL statement construction - by using escaped speach marks - I can get rid of the error, but get no results returned.
When I take my printed SQL statement (from the print "SQL:"."\n".$sqlstatement."\n"; line) then Copy and Paste into MS Access as a SQL query - all is fine - I get my expected result. What's going on?
Thanks in advance...
Jez
|
 |
 |
dhempy
Posts: 40
|
| Posted: 06/14/2005, 1:25 PM |
|
Quote jezgoldstone:
$sqlstatement=
"SELECT DestinationAddress,CustomerContactNumber
FROM DeliveryScheule
WHERE DeliveryID = \"6636\"\n";
print "SQL:"."\n".$sqlstatement."\n";
Try using single quotes in the where clause:
$sqlstatement = "
SELECT DestinationAddress,CustomerContactNumber
FROM DeliveryScheule
WHERE DeliveryID = '6636'
";
print "SQL: \n$sqlstatement \n";
Proper SQL likes single qoutes for strings. I suspect Access is a little more tollerant of creative syntax variations.
-dave
ps...if you find your self in serious quote hell, you can use qq{...} instead of "...". So you can write the statement above like this:
$sqlstatement = qq{
SELECT DestinationAddress,CustomerContactNumber
FROM DeliveryScheule
WHERE comment = 'He said, "Hello," and then departed.'
};
You can use any character instead of the {curly brace}. For example qq(hi), qq[hello], qq#this is the string#, etc.
Hope this helps,
-dave
_________________
David Hempy
Director of Broadcast and Education Technology
Kentucky Educational Television
800-333-9764 |
|
|
|
dhempy
Posts: 40
|
| Posted: 06/14/2005, 1:28 PM |
|
BTW...if you're hand-coding your perl code like that, then you're missing the great advantages of CodeCharge. Don't get me wrong...I'm a hand-coding kinda guy myself, but I don't use CodeCharge when hand-coding, and when I don't have to hand-code, I use CodeCharge.
The point is, you're probably working *way* harder than you have to. Spend a day or two learning CodeCharge and it will pay you back on your very first project. It does take some effort to get up to speed at first...hang in there and go through the tutorials.
-dave
_________________
David Hempy
Director of Broadcast and Education Technology
Kentucky Educational Television
800-333-9764 |
|
|
|
|
