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?
MiltonLClarkStudentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
MiltonLClarkStudentAuthor 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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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
MiltonLClarkStudentAuthor 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
MiltonLClarkStudentAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MiltonLClarkStudentAuthor 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
MiltonLClarkStudentAuthor 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
MiltonLClarkStudentAuthor Commented:
Good point, and very helpful.
Thanks again for your help.

Cheers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.