asked on

Form data and stored procedure

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.

<html>
<form id="new_order" method="post" action="">
	<p>
		<label for="cust_name">Customer name:</label>
		<input name="cust_name" id="cust_name" type="text" class="formbox" />
	</p>
	<p>
		<label for="order_date">Order date:</label>
		<input name="order_date" id="order_date" type="text" class="formbox" />
	</p>
	<p>
		<label for="part_num">Part Number:</label>
		<input name="part_num" id="part_num" type="text" class="formbox" />
	</p>
	<p>
		<label for="qty">Quantity:</label>
		<input name="qty" id="qty" type="text" class="formbox" />
	</p>
	<p>
		<label for="price">Price:</label>
		<input name="price" id="price" type="text" class="formbox" />
	</p>
	<p>
		<label for="order_num">Order Number:</label>
		<input name="order_num" id="order_num" type="text" class="formbox" />
	</p>
	<p>
		<label for="order_sum">Order total:</label>
		<input name="order_sum" id="order_sum" type="text" class="formbox" />
	</p>
	<p>
		<input name="send" id="send" type="submit" value="Submit form data" />
	</p>
</form>
</html>
<?php
	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";
		}
		else
		{
			print "Uh-oh!" . db2_stmt_error($stmt); //displays the SQLSTATE value
		}
	}
?>
********************output (does not display text fields)**************************
 
Customer name:
 
Order date: 
 
Part Number:
 
Quantity:
 
Price:
 
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
Uh-oh!22007

Open in new window

Beverley Portlock

I would suspect your date format. Most SQL databases want their dates in YYYY-MM-DD format and IIRC then DB2 is no different. Assuming that your date is in MM/DD/YYYY format try adding

$dt = explode("/", $order_date);
$order_date = sprintf("%04d-%02d-%02d", $dt[2], $dt[0], $dt[1] );

just before the commented out code

Member_2_4694817

Could you ouput the $stmt itself for diagnostic purposes?
When you do, are there superfluous or missing quotes?

Ray Paseur

This is a variation on the theme from bportlock, and it will accommodate more kinds of dates.

Near this statement...

$order_date = $_POST["order_date"];

do something like what's in the code snippet.

Best to all, ~Ray

// CONVERT THE DATE FROM THE INPUT INTO A TIMESTAMP
$order_ts = strtotime($_POST["order_date"]);
 
if ($order_ts) // IF THE DATE IS VALID, REFORMAT IT FOR MySQL DATETIME
{
   $order_date = date('Y-m-d\TH:i:s', $order_ts);
} 
else // IF THE DATE IS NOT VALID, strtotime() RETURNS ZERO ( FALSE )
{
   /* DEAL WITH UNINTELLIGIBLE DATE STRING */
}

Open in new window

InOz

ASKER

DB2 accepts dates in both YYYY-MM-DD and MM/DD/YYYY formats. (In php-speak, this is Y-m-d and m/d/Y.) When I hardcode the date value in a MM/DD/YYYY format, the app works. I did try the first suggestion using the explode function and there is no change. I'm not sure that converting the date into a timestamp will help either, but I tried it by modifying it (for db2) to:
$order_ts = strtotime($_POST["order_date"]);
$order_date = date('Y-m-d', $order_ts);
This caused no change either.
Not sure how to output the contents of $stmt, but the print_r($POST) should do the same thing. I tried print_r($stmt) and it outputs "Resource id #3".

Member_2_4694817

Hm, indeed there seems to be no easy way to extract the explicit sql statement.
However, I suspect that the variable types may be wrong. Maybe the following changes help

db2_bind_param($stmt, 1, "cust_name", DB2_PARAM_IN, DB2_CHAR);
db2_bind_param($stmt, 2, "order_date", DB2_PARAM_IN, DB2_CHAR);
db2_bind_param($stmt, 3, "part_num", DB2_PARAM_IN, DB2_LONG);
db2_bind_param($stmt, 4, "qty", DB2_PARAM_IN, DB2_LONG);
db2_bind_param($stmt, 5, "price", DB2_PARAM_IN, DB2_DOUBLE);
db2_bind_param($stmt, 6, "order_num", DB2_PARAM_OUT, DB2_LONG);
db2_bind_param($stmt, 7, "order_sum", DB2_PARAM_OUT, DB2_DOUBLE);

Open in new window

Ray Paseur

That error code 22007 is a date format error, isn't it? Here is some old news on the topic:
http://www.sqrug.org/pipermail/rm-users/2001/000218.html

So maybe my suggestion of an ISO8601-format date is not so good! Sorry if my input was not helpful. ~Ray

Beverley Portlock

Having done some reading of the IBM manuals, it seems that this error arises in the data item is bigger than the field it is being inserted into. Maybe you need to trim() the data to remove whitespaces in case any are present.

Check your field size on the date field

InOz

ASKER

Okay, I have 3 more suggestions, so each one at a time: 1) hagman: I tried adding the datatypes in the db2_bind_param statements to no avail; 2) Ray--the hard coded data will go in as either Y-m-d or d/m/Y but it fails only when posting the data from the HTML form--this is why the whole thing is so mystifying because the output is showing that it is correctly formatted; and 3) portlock: I added the trim() function also to no avail. The db2 attributes in the table are: customer_name char(35), order_date date, part_num char(4), order_num decimal(3,0) and price decimal(6,2). I would have thought these were large enough to accommodate any additional padding.
So, back to square 1--I'm still open for suggestions. I think I will try changing the date field to a character and see what happens.

Beverley Portlock

If you are going to use a text field to store the date then you may as well make it 10 characters in size and store the date in YYYY-MM-DD format as this will make comparisons easier and sequencing in sorts

Are you using ODBC to access the DB2 database? There have been issues in the past with ODBC and date fields in non-MS databases such as Oracle. That does not answer why it works as a static string and not as form data. My only other suggestion in that respect is character set differences - is your web page in UTF-8 or some other character set? What character set is the DB2 database in?

Does DB2 expect the date in a null-terminated string?

ASKER CERTIFIED SOLUTION

InOz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

@InOz: I would suggest that BPortlock suggested EXACTLY this issue in his post #23291495. The error number seemed to be misleading, but his guidance and your observation about the field size seem to be so close that you might want to give him the points. Just a thought. ~Ray

InOz

ASKER

Ray: good observation, but his suggestion (using the trim() function) would not have helped since it only removes white space--the number of digits contained in the parameter are all significant and cannot (nor should they) be removed.