asked on

PHP and MS SQLServer Prepared Insert Not Getting Updated Values in Bound Variable

I have a function which has a data array passed to it. Within the function, I iterate through and assign another variable a row from the data array. I prepare the INSERT statement okay, but when I iterate through, I verify the bound variable it getting new values, but when the INSERT runs, it inserts the values from the first iteration (i.e. data from the first row gets inserted multiple times.)

The calling program builds the array as

 		$handle = fopen($fs, "r");
 		if ($handle !== FALSE){
 			$datRow = fgetcsv($handle, 1000, "|");
 			for ($ii=0; ($datRow !== FALSE); $ii++) {
 				$datArr[$ii] = $datRow;
 				$datRow = fgetcsv($handle, 1000, "|");
 			}

 			fclose($handle);
 		}

Open in new window

$datArr is then passed to a function to insert as rows in the database"

   function wrtMR10toDB(&$datArr) {
            . . .
		// Define the parameterized SQL statement.
			$tsql =
				"INSERT INTO ".$tbl." (".
					"VENDOR_ID, ".
					"CATALOG_NUM, ".
					"DESCRIPTION, ".
					"CUST_CATALOG_NUM, ".
					"ACCOUNT_NUM, ".
					"SALESMAN1_ID, ".
					"SALESMAN2_ID, ".
					"ORDER_TYPE, ".
					"LINE_NUM, ".
					"ORDER_NUM, ".
					"TICKET_NUM, ".
					"CUST_ORDER_NUM, ".
					"REQUISITION_NUM, ".
					"ORDER_DATE, ".
					"SHIP_DATE, ".
					"INVOICE_DATE, ".
					"INVOICE_NUM, ".
					"SHIP_QTY, ".
					"COST, ".
					"COST_UOM, ".
					"COST_EXT, ".
					"PRICE, ".
					"PRICE_UOM, ".
					"PRICE_EXT, ".
					"GP_DLR, ".
					"GP_PCT, ".
					"BUY_FROM_ID, ".
					"LPP, ".
					"COMMODITY_CODE, ".
					"BENCHMARK, ".
					"SHIP_TO, ".
					"STATE, ".
					"ZIP, ".
					"DUE_DATE, ".
					"CUST_ITEM_CATLG_NUM  ".

					") ".
				"VALUES (".
					"?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ".
					"?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ".
					"?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ".
					"?, ?, ?, ?, ?".
				")";
        . . .
			// Initialize parameters and prepare the statement.
			$datRow = $datArr[0];
			$stmt1 = sqlsrv_prepare( $conn, $tsql,  $datRow);	// $datRow will be bound to $stmt1
       . . .
		// Iterate thru data array and insert into the table
			 for ($ii=0; $ii < count($datArr); $ii++) {
				// Because $datRow is bound to $stmt1, updated values are used with each execution of the statement.
				$datRow = $datArr[$ii];
                                echo("\$datRow = ".implode($datRow,"|")."\n");
				$res = sqlsrv_execute( $stmt1 );
				if( $res === false ) {
					echo("Error in \$STMT1 execution.\n");
					die( print_r( sqlsrv_errors(), true));
				} else {
					echo("...row $ii inserted");
				}
			}		

			// Free $stmt1 resources. (allows vars to be bound to a different statement.)
			$res = sqlsrv_free_stmt( $stmt1);

Open in new window

So in this example, the values of $datArr[0] gets written many times even though I've verified that the value of $datRow is changing each time just before the sqlsrv_execute runs.

Any ideas here?

Kim Walker

$stmt1 is never updated. It is created on line 52 with $datArr[0] but never updated in your iterations.

Kim Walker

I just did a little research on the sqlsrv_prepare function. Try putting an ampersand (&) before the variable name $datRow to pass a reference to $datRow instead of the value.

$stmt1 = sqlsrv_prepare( $conn, $tsql,  &$datRow);

Open in new window

Milton Clark

ASKER

xmediaman,

1. $stmt1 is the prepared statement, which doesn't change. $datRow is the parameter which is bound to $stmt1. Changes in $datRow should be seen when $stmt1 is used in subsequent sqlsrv_execute statements.

2. Although the SQL driver documentation says the value should be passed by reference, PHP 5.4 has deprecated the reference in the call and doesn't like it ... it gives a Fatal Error... this may be a classic catch-22 where the driver needs it, but PHP no longer allows it. If that's the case, I can't be the first person to encounter this problem.

I read somewhere that wrapping the parameterized variable in an array statement ( i.e. ...array($datRow) .. would allow the value within to be referenced. I tried that, but got other errors .. the structure of the value being passed changes, and I don't see how the referenced value nested within the array could possibly get updated the way that the driver expects it to.

Kim Walker

I'm just shooting in the dark here as I am not familiar with SQL Server and have never used the sqlsrv_prepare function. The Microsoft documentation suggests that the sqlsrv_prepare function expects an array of references for params instead of a reference to an array. This makes me wonder how it manages to work with $datRow[0].

Have you tried moving line 52 to just before line 59 (the sqlsrv_execute)? This seems to be contrary to the purpose of the sqlsrv_prepare function, but if it works...

Milton Clark

ASKER

That's exactly what I did already, and it does work .. just not "correctly".

You certainly get brownie points for a solution... would love to know if anyone else has a way to get it to work properly.

Thanks

Kim Walker

What happens if you change line 52 to:

$stmt1 = sqlsrv_prepare( $conn, $tsql,  array(
	&$datRow[0], &$datRow[1], &$datRow[2], &$datRow[3], &$datRow[4], &$datRow[5], &$datRow[6], &$datRow[7], &$datRow[8], &$datRow[9],
	&$datRow[10], &$datRow[11], &$datRow[12], &$datRow[13], &$datRow[14], &$datRow[15], &$datRow[16], &$datRow[17], &$datRow[18], &$datRow[19],
	&$datRow[20], &$datRow[21], &$datRow[22], &$datRow[23], &$datRow[24], &$datRow[25], &$datRow[26], &$datRow[27], &$datRow[28], &$datRow[29],
	&$datRow[30], &$datRow[31], &$datRow[32], &$datRow[33], &$datRow[34]
) );

Open in new window

Milton Clark

ASKER

That $stmt1 still inserts multiple instances of the first row.

ASKER CERTIFIED SOLUTION

Kim Walker

membership

This solution is only available to members.

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

Start Free Trial

Milton Clark

ASKER

That did the trick, xmediaman... thanks.

So it would seem the structure of the array that's bound to $stmt1 must have the structure you enumerated in the code. The question is, how is that structure different that the original $datArr[$ii]? Guess I can put some var_dump function calls in to try and identify the difference.

Thanks again.. all points to you.
Cheers

Milton Clark

ASKER

In my opinion, this site and the skill level of the people who participate make this site invaluable.

Kim Walker

It was actually binding the values within $datRow instead of binding the variable $datRow. What we did was present a group of variable references to bind to instead. Your approach was more efficient in my opinion, but unfortunately it didn't work that way.

Milton Clark

ASKER

Good point, and very helpful.
Thanks again for your help.

Cheers