If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Other PC Databases > Sqlite3/PDO write lock issue

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 11-24-08, 05:51
cesarcesar cesarcesar is offline
Registered User
 
Join Date: Mar 2008
Posts: 55
Sqlite3/PDO write lock issue

ahhhhh.... Man my head hurts with all this table banging.

I'm having an issue with Sqlite not allowing me to write (UPDATE) to a table that i have just done a SELECT query on because it is "Locked" in a read only state. I have found a few articles on the web about this, but the suggested fix's seem not to work in my case. Any help is so much appreciated. Here's my code. I have commented as much as possible. I have also omitted some error checking code to trim this post.

You will see at the last db_query() is where its failing. My quetions is... How do i get the UPDATE query to work on table that was just SELECT queried? Thanks much for the help.

Code:
/* connect to the database (works) */
$dbHandle = new PDO('sqlite:'.$CFG->baseroot.'/db/mmt.db3');

function db_query($query) {
	global $dbHandle;

        /* load the query (works) */
	$handle = $dbHandle->query($query);

	/* check to see if Fetch is necessary (works) */ 
	if (substr($query,0,6) <> 'UPDATE') {

		/* do the fetch (works) */	
		$qr = $handle->fetch();

	}

        /* fix that releases the lock (doesn't work) */
	$handle=null;

	return $qr;
}

/* run the select query (works) */
$user = db_query("SELECT first_name FROM user WHERE id = '5'");

/* do the if (works) */
if ($user['name'] == 'John') {

	/* run the update. (this does not work and throws an error) */
	db_query("UPDATE user SET last_name = 'Doe' WHERE first_name = 'John'");
}
Reply With Quote
  #2 (permalink)  
Old 01-30-09, 08:49
wonea wonea is offline
Registered User
 
Join Date: Jan 2009
Posts: 1
-

Hello, just wondering do you solve this problem, or find a way around this?
Reply With Quote
  #3 (permalink)  
Old 01-30-09, 15:44
cesarcesar cesarcesar is offline
Registered User
 
Join Date: Mar 2008
Posts: 55
i did find a solution. Below is my new function.

Code:
/* connect to the database */
try{

	$dbHandle = new PDO('sqlite:'.'your_path_to_.db3_file');
	$dbHandle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

}catch( PDOException $exception ){
	die($exception->getMessage());
}

function db_query($queryString) {
	global $dbHandle;

	$query = $dbHandle->query($queryString);

	$i = 0;
	foreach ($query as $query2) {
		$queryReturn[$i] = $query2;
		$i++;
	}

	return $queryReturn[0];
}

// call like
$qdb = db_query("Select * From table_name Where field_name = 'x'");
// returns always an array
Also to note, if you use a "group by" clause, the returning array keys will always be in a "table.field_name" way. Good luck. Hit me up if you need more assistance.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On