CodeCharge Studio
search Register Login  

Visual Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> Perl

 When 1 + 1 doesn't equal 2

Print topic Send  topic

Author Message
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
View profile  Send private message
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
View profile  Send private message
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
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

MS Access to Web

Convert MS Access to Web.
Join thousands of Web developers who build Web applications with minimal coding.

CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.