• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

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?
0
MiltonLClark
Asked:
MiltonLClark
  • 6
  • 6
1 Solution
 
Kim WalkerWeb Programmer/TechnicianCommented:
$stmt1 is never updated. It is created on line 52 with $datArr[0] but never updated in your iterations.
0
 
Kim WalkerWeb Programmer/TechnicianCommented:
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

0
 
MiltonLClarkAuthor Commented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Kim WalkerWeb Programmer/TechnicianCommented:
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...
0
 
MiltonLClarkAuthor Commented:
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
0
 
Kim WalkerWeb Programmer/TechnicianCommented:
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

0
 
MiltonLClarkAuthor Commented:
That $stmt1 still inserts multiple instances of the first row.
0
 
Kim WalkerWeb Programmer/TechnicianCommented:
I have two more shots in the dark.

1) change the array reference in line 51 to something besides 0 and see if it inserts multiple instances of a different row.
$datRow = $datArr[5];

Open in new window


2) if that changes the row that's repeated, try this (changes begin at line 51):
   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.
			list($f0,$f1,$f2,$f3,$f4,$f5,$f6,$f7,$f8,$f9,
				$f10,$f11,$f12,$f13,$f14,$f15,$f16,$f17,$f18,$f19,
				$f20,$f21,$f22,$f23,$f24,$f25,$f26,$f27,$f28,$f29,
				$f30,$f31,$f32,$f33,$f34) = $datArr[0];
			$stmt1 = sqlsrv_prepare( $conn, $tsql, array(
				&$f0,&$f1,&$f2,&$f3,&$f4,&$f5,&$f6,&$f7,&$f8,&$f9,
				&$f10,&$f11,&$f12,&$f13,&$f14,&$f15,&$f16,&$f17,&$f18,&$f19,
				&$f20,&$f21,&$f22,&$f23,&$f24,&$f25,&$f26,&$f27,&$f28,&$f29,
				&$f30,&$f31,&$f32,&$f33,&$f34
			) );	// $f# will be bound to $stmt1
//       . . .
		// Iterate thru data array and insert into the table
			 for ($ii=0; $ii < count($datArr); $ii++) {
				// Because $f# is bound to $stmt1, updated values are used with each execution of the statement.
				list($f0,$f1,$f2,$f3,$f4,$f5,$f6,$f7,$f8,$f9,
					$f10,$f11,$f12,$f13,$f14,$f15,$f16,$f17,$f18,$f19,
					$f20,$f21,$f22,$f23,$f24,$f25,$f26,$f27,$f28,$f29,
					$f30,$f31,$f32,$f33,$f34) = $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

0
 
MiltonLClarkAuthor Commented:
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
0
 
MiltonLClarkAuthor Commented:
In my opinion, this site and the skill level of the people who participate make this site invaluable.
0
 
Kim WalkerWeb Programmer/TechnicianCommented:
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.
0
 
MiltonLClarkAuthor Commented:
Good point, and very helpful.
Thanks again for your help.

Cheers
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now