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

Open in new window


Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 12 Comments.
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>


Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 12 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004