troubleshooting Question

Form data and stored procedure

Avatar of InOz
InOz asked on
12 Comments1 Solution431 ViewsLast Modified:
I have a simple HTML form that accepts 5 values from text inputs and attempts to invoke a db2 stored procedure using php and the php_ibm_db2.dll module.  The application breaks at the db2_execute line when I pass the data obtained from a post action from the input text boxes.  However, if I hard-code the data values into the php code, it works.  So, that is the problem I'm trying to solve--I need to send the use the user input values to the SP and not the hard coded ones.  Below is more info regarding the app and its code. The stored procedure accepts 5 inputs and returns two outputs, all of which are single values.  (It's a simple customer order transaction form--the return values are a unique order number and the order total.)  The code is shown below.  Note that the hard coded values are listed but are commented out.  When the app does break, I can retrieve an SQLSTATE value, which is always 22001.  In IBM-speak this indicates a bad date format.  I have tried to ensure that the date was formatted correctly when I tested it, so I'm not sure why this is showing up. In fact, there are two additional lines inserted just prior to calling the db2_execute function, which display both the posted array and the individual input parameters.  The output (with errors) is shown below the attached code.
Any assistance is appreciated.
<form id="new_order" method="post" action="">
		<label for="cust_name">Customer name:</label>
		<input name="cust_name" id="cust_name" type="text" class="formbox" />
		<label for="order_date">Order date:</label>
		<input name="order_date" id="order_date" type="text" class="formbox" />
		<label for="part_num">Part Number:</label>
		<input name="part_num" id="part_num" type="text" class="formbox" />
		<label for="qty">Quantity:</label>
		<input name="qty" id="qty" type="text" class="formbox" />
		<label for="price">Price:</label>
		<input name="price" id="price" type="text" class="formbox" />
		<label for="order_num">Order Number:</label>
		<input name="order_num" id="order_num" type="text" class="formbox" />
		<label for="order_sum">Order total:</label>
		<input name="order_sum" id="order_sum" type="text" class="formbox" />
		<input name="send" id="send" type="submit" value="Submit form data" />
	if (array_key_exists('send', $_POST))
		$database = 'my_db2_database';
		$user = 'username';
		$password = 'password';
		$sql = 'CALL STORED_PROC(?, ?, ?, ?, ?, ?, ?)';
		$conn = db2_connect($database, $user, $password);
		$stmt = db2_prepare($conn, $sql);
		$cust_name =  $_POST["cust_name"];
		$order_date = $_POST["order_date"];
		$part_num =   $_POST["part_num"];
		$qty =        $_POST["qty"];
		$price =      $_POST["price"];
  	$order_num =  $_POST['order_num'];
		$order_sum =  $_POST['order_sum'];
		$cust_name =  "Customer1";
		$order_date = "04/02/2009";
		$part_num =   "Part_num1";
		$qty =        3;
		$price =      32.98;
		$order_num =  0;
		$order_sum =  0;
		db2_bind_param($stmt, 1, "cust_name", DB2_PARAM_IN);
		db2_bind_param($stmt, 2, "order_date", DB2_PARAM_IN);
		db2_bind_param($stmt, 3, "part_num", DB2_PARAM_IN);
		db2_bind_param($stmt, 4, "qty", DB2_PARAM_IN);
		db2_bind_param($stmt, 5, "price", DB2_PARAM_IN);
		db2_bind_param($stmt, 6, "order_num", DB2_PARAM_OUT);
		db2_bind_param($stmt, 7, "order_sum", DB2_PARAM_OUT);
		if ($_POST) {print_r($_POST);}
// next are diagnostic lines
		print "Values of input parameters:\n";
		print "  1: {$cust_name} 2: {$order_date} 3: {$part_num} 4. {$qty} 5. {$price} 6. {order_num} 7. {order_sum}\n\n";
	  if (db2_execute($stmt))  //this is line 89
	    print "Values of output parameters:\n";
			print "  1: {$order_num} 2: {$order_sum} \n\n";
			print "Uh-oh!" . db2_stmt_error($stmt); //displays the SQLSTATE value
********************output (does not display text fields)**************************
Customer name:
Order date: 
Part Number:
Order Number:
Order total:
Values of input parameters: 1: Customer1 2: 04/02/2009 3: Part_num1 4. 3 5. 32.98 6. {order_num} 7. {order_sum} 
Array ( [cust_name] => [order_date] => [part_num] => [qty] => [price] => [order_num] => [order_sum] => [send] => Submit form data ) 
Values of input parameters: 1: Customer1 2: 04/02/2009 3: Part_num1 4. 5. 32.98 
6. {order_num} 7. {order_sum}
Warning: db2_execute() [function.db2-execute]: Statement Execute Failed in C:\Inetpub\wwwroot\SP_Test.php on line 89
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 12 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros