lundi 12 janvier 2015

Recursion in SQLite Database is locked

I build something like tree using SQLite. I use recursion in my task:



$this->recursion($row['id']...


My code works in old MySQL, but not in SQLite.


I get Fatal error: Database is locked in... line where I have INSERT.

Because SELECT connection is using the database before INSERT.

And PHP insert only one records.


I used BeginTransaction and Commit, I used busyTimeout. It doesn't help me.



public function recursion($from, $to){
$db = new SQLite3("db.sqlite");
$stmt = $db->prepare('SELECT value, s_id, sex FROM table WHERE id='. $from);
$result = $stmt->execute();
$row = $result->fetchArray();
//'database is locked' in next line
$db->exec('INSERT INTO table (id, s_id, value) VALUES (null,'. $to . $row['value'] .'");');

if ($row['sex']=='fem'){
$stmt = $db->prepare('SELECT id FROM table WHERE s_id=' . $from);
$result = $stmt->execute();
while ($row = $result->fetchArray()){
$this->recursion($row['id'], $db->lastInsertRowID());
}
}

}




public function recursion($from, $to){
$res=mysql_connect($mysql_server,$mysql_user,$mysql_pass);
mysql_select_db($mysql_db);

$sql = 'SELECT value, s_id, s_id, sex FROM table WHERE id='. $from;
$result = mysql_query($sql, $res);
$row = mysql_fetch_array($result, MYSQL_ASSOC);

mysql_query('INSERT INTO table (id, s_id, value) VALUES (null,'. $to . $row['value'] .'");', $res);

if ($row['sex']=='fem'){
$sql = 'SELECT id FROM table WHERE s_id=' . $from;
$result = mysql_query($sql, $res);
while ( $row = mysql_fetch_array($result, MYSQL_ASSOC) ) {
$this->recursion($row['id'], mysql_insert_id());
}
}
}


I repeat that my code works in MySQL.


Any idea? Any hint? Thanks.


Aucun commentaire:

Enregistrer un commentaire