I'm having problems with my Prepare and Execute statements.
I get a syntax error onmy prepare and of course the execute won't run on an undefined value.
I don't know what I am overlooking. -
#!/Perl/bin/perl
-
# Program to read information sent to the server
-
# from the form in the contact_list.xhtml document.
-
-
use CGI qw( :standard );
-
use DBI;
-
-
$dtd =
-
"-//W3C//DTD XHTML 1.0 Transitional//EN\"
-
\"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";
-
-
print( header() );
-
print( start_html( { dtd => $dtd,
-
title => "Order Response" } ) );
-
-
# generic variable to hold output message for user, with default success value
-
my $message = "<b>Order Successful! <br /> Thanks!</b>";
-
-
# try updating database
-
# open connection to Access database
-
# no passwords on our database
-
$dbh = DBI->connect('dbi:ODBC:KTC')or die "Database connection failed.";
-
-
# server-side check of quantity
-
$requestedQuantity = param( "quantity" );
-
my $check=quantityValid();
-
if ( ! $check ) {
-
# @TODO add not available message here
-
$message = "<b>Order NOT Successful! <br /> Please Try Again!</b>";
-
} else {
-
$customerID=param("customer_id");
-
$productID=param("product_id");
-
$quantity=param("quantity");
-
$deliveryDate=param("delivery_date");
-
-
$query1 = "INSERT INTO Orders (CustomerID, ProductID, OrderQuantity, RequestedShipDate)
-
VALUES ($customerID,$productID,$quantity,$deliveryDate);
-
## VALUES (?,?,?,?)";
-
-
my $insertHandle = $databaseHandle->prepare_cached($query1)
-
or die "Couldn't prepare insert query; aborting "
-
unless defined $insertHandle;
-
-
my $success = 1;
-
$success &&= $insertHandle->execute($customerID,$productID,$quantity,$deliveryDate)
-
or die "Execute failed.";
-
-
# autocommit is on, so no need to commit
-
# my $result = ($success ? $databaseHandle->commit : $databaseHandle->rollback);
-
unless ($success) {
-
die "Couldn't finish transaction: " . $databaseHandle->errstr
-
}
-
-
$insertHandle->finish();
-
}
-
-
$databaseHandle->disconnect();
-
-
print( $message );
-
print( end_html() );
-
-
-
sub quantityValid {
-
-
# use ProductID to find a product name
-
-
$productID = param("product_id");
-
# @TODO get product ID from web page
-
-
$query1 = "SELECT UnitsInStock FROM Products WHERE ProductID = $productID";
-
-
#@TODO add query to product table
-
-
# @TODO prepare query with existing database handle
-
$query_handle = $dbh->prepare($query1);
-
-
# @TODO execute query
-
$query_handle->execute() or die $dbh->errstr;
-
-
# @TODO loop through results put available amount into $availAmount
-
while ( @availAmount = $query_handle->fetchrow_array()){
-
$availAmount = $availAmount[0];
-
}
-
-
# @TODO test quantity and return 1 (for true) is ok
-
# return 0 (for false) if not ok
-
if ($requestedQuantity <= $availAmount){
-
return 1;
-
} else {
-
return 0;
-
}
-
}
-
Any help wopuld be greatly appreciated.
1 3635
Your error occurs because you created the database handle using the variable name "$dbh", but you atttempt to use the variable name "$databaseHandle" which does not exist.
Ultimately your biggest problem is the fact that you do not have the line "use strict;" included at the top of your file, therefore enforcing that you scope all of your variables. If you had done this it would have been able to throw an error when you attempted to access the $databaseHandle variable which would be undefined.
Before noticing that error through, I went through your script and added comments to all of the lines that I think you should change or at least think about. I hope that this helps, and good luck. -
#!/Perl/bin/perl
-
# Program to read information sent to the server
-
# from the form in the contact_list.xhtml document.
-
-
use CGI qw( :standard );
-
use DBI;
-
-
#WARNING: "use strict;" It will help you avoid all sorts of simple typo and spelling errors. Yes, it will require that you always scope your variables with either "my" or "our", but it is good to be thinking about such things anyway.
-
-
$dtd =
-
"-//W3C//DTD XHTML 1.0 Transitional//EN\"
-
\"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";
-
-
print( header() );
-
print( start_html( { dtd => $dtd,
-
title => "Order Response" } ) );
-
-
# generic variable to hold output message for user, with default success value
-
my $message = "<b>Order Successful! <br /> Thanks!</b>";
-
-
# try updating database
-
# open connection to Access database
-
# no passwords on our database
-
$dbh = DBI->connect('dbi:ODBC:KTC')or die "Database connection failed.";
-
-
# server-side check of quantity
-
$requestedQuantity = param( "quantity" );
-
my $check=quantityValid();
-
if ( ! $check ) {
-
# @TODO add not available message here
-
$message = "<b>Order NOT Successful! <br /> Please Try Again!</b>";
-
} else {
-
$customerID=param("customer_id");
-
$productID=param("product_id");
-
$quantity=param("quantity");
-
$deliveryDate=param("delivery_date");
-
-
#WARNING: When INSERTing a single record, it is better to use the SET syntax for the query instead of values as that way you can confirm that the key value pairs are matched properly.
-
#WARNING: Stick with placeholders. It appears that you commented out their use, but they take care of a lot of important security and functional concerns dealing with escaping characters, dealing with empty values, and avoiding security vulnerabilities.
-
$query1 = "INSERT INTO Orders (CustomerID, ProductID, OrderQuantity, RequestedShipDate)
-
VALUES ($customerID,$productID,$quantity,$deliveryDate);
-
## VALUES (?,?,?,?)";
-
-
#WARNING: your "unless defined" statement is never reached. In this instance it doesn't do anything wrong since there is no way that $insertHandle is defined in this script. But if there were an $insertHandle higher in scope, this statement could potentially lead to bad bad things. Never a statement like this: "my $var = 'value' if $some_condition;" This leads to really really wierd things in perl. Instead if you truly mean optional assignment, do "my $var = $some_condition ? 'value' : undef;". In this instance though, that test always passes so just remove it.
-
#WARNING: Once your $dbh is created, always include the $dbh->errstr in your "or die" statement. This will ensure the best changes of debugging hte problem
-
my $insertHandle = $databaseHandle->prepare_cached($query1)
-
or die "Couldn't prepare insert query; aborting "
-
unless defined $insertHandle;
-
-
#WARNING 1: Your assignment of $success here is redundant. Simply assign it the return value of the execute statement directly instead of the unnecessary boolean operator trick.
-
#WARNING 2: Your assignment of $success is redundant for a second reason. If $success is assigned a null value, than also means that the die statement would have been called, and so the assignment is meaningless. Instead simply include the $dbh->errstr directly in your "or die" statement.
-
my $success = 1;
-
$success &&= $insertHandle->execute($customerID,$productID,$quantity,$delivery Date)
-
or die "Execute failed.";
-
-
# autocommit is on, so no need to commit
-
# my $result = ($success ? $databaseHandle->commit : $databaseHandle->rollback);
-
unless ($success) {
-
die "Couldn't finish transaction: " . $databaseHandle->errstr
-
}
-
-
$insertHandle->finish();
-
}
-
-
$databaseHandle->disconnect();
-
-
print( $message );
-
print( end_html() );
-
-
-
sub quantityValid {
-
-
# use ProductID to find a product name
-
-
$productID = param("product_id");
-
# @TODO get product ID from web page
-
-
#ERROR Possible: What if $productID has not value? The below sql query will likely fail. Instead use placeholders in this instance.
-
$query1 = "SELECT UnitsInStock FROM Products WHERE ProductID = $productID";
-
-
#@TODO add query to product table
-
-
# @TODO prepare query with existing database handle
-
$query_handle = $dbh->prepare($query1);
-
-
#NOTE: Very good use of "or die $dbh->errstr" here. You could also add it to the prepare statement, but in truth most errors are through in the execute.
-
# @TODO execute query
-
$query_handle->execute() or die $dbh->errstr;
-
-
#WARNING: Is ProductID really a 1 to many relationship for Products? This use of a while is very suspect. If there really are more than one record, than there is no predicted which amount will be used for the test. This is just messy.
-
# @TODO loop through results put available amount into $availAmount
-
while ( @availAmount = $query_handle->fetchrow_array()){
-
$availAmount = $availAmount[0];
-
}
-
-
#WARNING: you use a global variable $requestedQuantity here. Any subroutine should be passed all the variables that it requires. This is just smarter programming style.
-
# @TODO test quantity and return 1 (for true) is ok
-
# return 0 (for false) if not ok
-
if ($requestedQuantity <= $availAmount){
-
return 1;
-
} else {
-
return 0;
-
}
-
}
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Randy Rodent |
last post by:
I have a problem with some SQL code that can be run at the mySQL
command prompt, and in PHP, I can prepare it with no problem, but I
get a syntax error when I try and exexute it.
$sql = <<< EOF...
|
by: Markku Uttula |
last post by:
I think I'm doing something wrong. I'm able to connect to Oracle just
fine, execute queries and all, but I'm having serious problems with
the speed :(
For example, the following PHP-script on my...
|
by: Ryan |
last post by:
I have two similar stored procedures which I'm running. One runs and
one doesn't. I can run both with no problems in SQL Enterprise (7.0
standard) and have checked the permissions and am happy with...
|
by: claus.hirth |
last post by:
I wrote a stored procedure that uses a prepared INSERT INTO statement
in order to play with the PREPARE and EXECUTE keywords.
In transcript 1 below the call to that stored procedure does not...
|
by: Rajat Katyal |
last post by:
Hi:
I prepare the statement for execution as follows:
PREPARE query(text) as SELECT count(*) FROM transform_customer_billing where inv_no = $1;
The problem is Iam not able to execute this...
|
by: howachen |
last post by:
Hi,
When doing mysql query (SELECT statements) in php, we often use prepare
statement to prevent SQL injection. However, I just noticed that the
prepare statements can SLOW the number of queries...
|
by: gp |
last post by:
******************************************************************************************************************************
Catchable fatal error: Object of class stdClass could not be...
|
by: chromis |
last post by:
Hi there,
I've been reading an OOP book recently and it gives some nice Adaptor / Template patttern code to wrap around the php Mysql functions. I thought that I'd try and create a Simple Address...
|
by: pankaj17 |
last post by:
hello everyone,
MYSQL query ............
how to set prepare stmt result in variable
i have written prepare stmt in store procedure and i want to set result of prepare stmt in variable
...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |