[RESOLVED] Problem with Update query, using PEAR/MYSQL

johanafm

I'm having issues with an update query. I can't get it to work using either $db->Query($qry, $data) or prepare/execute. However, when I construct the query myself using the same info as is supplied in $data, it works just fine.

public function DbQuery($queryString, $data = null)
{
// $queryString contains:
// UPDATE ProjectLight SET oID = ?, units = ?, cost = ?, currencyID = ?,
// costtype = ?, startDate = ?, endDate = ? WHERE projID = ? AND lightID = ?

// The block commented out below returns an unknown error       
/*$result = $this->db->query($queryString, $data);
if (DB::isError($result))
trigger_error($result->getMessage(), E_USER_ERROR);
return $result;
*/

// However, doing the above manually does indeed work without problems
$qry = "UPDATE ProjectLight SET oID = " . $data[0] . ", units = " . $data[1] .
", cost = " . $data[2] . ", currencyID = " . $data[3] . ", costtype = " .
$data[4] . ", startDate = " . $data[5] . ", endDate = " . $data[6] .
" WHERE projID = " . $data[7] . " AND lightID = " . $data[8];

$result = $this->db->query($qry);
if (DB::isError($result))
	trigger_error($result->getMessage(), E_USER_ERROR);
return $result;		
}

Any ideas on what goes wrong would be appreciated.
regards
johan

alimadzi

No clue. I've used PEAR DB for several years now and I can't see what the issue would be. Could you try using prepare and execute just to see what happens?

http://pear.php.net/manual/en/package.database.db.intro-execute.php

johanafm

I actually started off using prep/executemultiple, then switched over to prep/execute, and finally ended up with query. Here's the relevant code.

I've tried some different things in my functions and as such I present the different things in this way:
comments shown with //
code blocks commented out with / /

public function UpdateProjectLights($qry, &$data) {
	//initial functionality of this function
	$prep = $this->mDbManager->DbPrepare($qry);
	$this->mDbManager->DbExecuteMultiple($prep, $data);

// thenI tried the below. Will need to loop over $data
// to make it work properly, but I just wanted to get one
// actual SQL statement to be executed
/*
 $this->mDbManager->DbExecute($prep, $data[0]);
*/

// and finally I tried this, which is what's shown in the above post
/*
 $result = $this->mDbManager->DbQuery($qry, $data[0]);
*/

return $result;
}


// DbPrepare, DbExecute, DbExecuteMultiple were initially nothing but wrappers
// for the PEAR layer to avoid repetitive error checks.
// Eventually I tried calling DbExecute (which in turn calls PEAR's execute)
// from within a for loop within DbExecuteMultiple

public function DbPrepare($statement) {
	$result = $this->db->prepare($statement);
	if(DB::isError($result))
		trigger_error($result->getMessage(), E_USER_ERROR);
	$return $result;
}


public function DbExecute($preparedStatement, $data) {
	$result =& $this->db->execute($preparedStatement, $data);
	if (DB::isError($result))
		trigger_error($result->getMessage(), E_USER_ERROR);
}


public function DbExecuteMultiple($preparedStatement, $data) {
	//initial functionality od DbExecute
	$result =& $this->db->executeMultiple($preparedStatement, $data);
	if (DB::isError($result))
		trigger_error($result->getMessage(), E_USER_ERROR);

// eventually replaced by
/*
for ($i = 0; $i < count($data); $i++) {
	$this->DbExecute($preparedStatement, $data[$i]);
}
*/
}

alimadzi

Could you try prepare/execute without the wrapper functions? Just call the PEAR DB methods directly.

Next thing I would try is prepare/execute with a simplified query. I'm not sure if you're using production data or if you have a test copy of your database, but it would be nice to have something available that you're free to nuke and then resync with the production database.

johanafm

I'm working against a database which is empty except for whatever I put into it, so there's no problem putting in whatever I feel like and as such there is absolutely no problem deleting all entries from any table.

The table which I'm trying to run the update query against looks as follows:

projID		bigint(20)
lightID		bigint(20)
oID		bigint(20)
units 		mediumint(9)
cost	 	double
currencyID	smallint(5)
costType	set('setUnitCost', 'unitCostHour', 'unitCostDay', 'unitCostWeek',
				'unitCostMonth', 'BUR')
startDate	datetime
endDate		datetime

And the php code:

public function UpdateProjectLights($qry, &$data) {
	/* ORIGINAL FUNCTIONALITY. RESTORE AFTER CHECK
	$this->mDoProject->UpdateProjectLights($qry, $data);		
	*/
	require_once('./include/config.inc.php');
	require_once('DB.php');

$db = DB::connect(MYSQL_CONNECTION_STRING,
				USE_PERSISTENT_CONNECTIONS);
if (DB::isError($db))
	trigger_error($db->getMessage(), E_USER_ERROR);
$db->setFetchMode(DB_FETCHMODE_ASSOC);

echo '$qry:' . $qry . "<br/>";
echo '$data[0][$i]:<br/>';
for ($i = 0; $i < count($data[0]); $i++) {
	echo '&nbsp;&nbsp;&nbsp;$i = ' . $i .": " . $data[0][$i] . "<br/>";
}
echo "<br/>";

$prep = $db->prepare($qry, $data);
if(DB::isError($prep))
	trigger_error($prep->getMessage(), E_USER_ERROR);
else echo "Success: statement prepared<br/>";

$result = $db->executeMultiple($prep, $data);
if(DB::isError($result))
	trigger_error($result->getMessage(), E_USER_ERROR);
else echo "Success: statement executed<br/>";

$this->db->disconnect();
}

Which outputs:
$qry: UPDATE ProjectLight SET oID = ?, units = ?, cost = ?, currencyID = ?,
costtype = ?, startDate = ?, endDate = ? WHERE projID = ? AND lightID = ?
$data[0][$i]:
$i = 0: 1
$i = 1: 2
$i = 2: 100
$i = 3: 3
$i = 4: 'unitCostHour'
$i = 5: '2007-07-02 00:00:00'
$i = 6: '2007-07-04 00:00:00'
$i = 7: 10
$i = 8: 1

Success: statement prepared

ERRNO: 256
TEXT: DB Error: unknown error
LOCATION: bo_project.php, line 190, atJuly 11, 2007, 11:45 pm
Showing backtrace:

alimadzi

I would just start simplifying that query until it works. Try to isolate the problem. Go all the way down to "UPDATE ProjectLight SET oID = ?" if you have to.

johanafm

Excellent suggestion. It turns out the error was due to an erroneous assumption I made. The strings should apparently not be enclosed in single quotes when used in a prepare statement as opposed to a direct query.

Thread can be set to [resolved]. I'm not certain where to do this myself.

Thanks a lot 🙂
regards
johan

alimadzi

Ah yes, that can be confusing. Glad you've gotten it fixed now.

I've never started a thread so I'm not sure how to mark it RESOLVED either. Maybe just search the page for "resolved"? Good luck...