EllisLab text mark
Advanced Search
     
use new pdo drriver for SQLIte
Posted: 07 December 2011 04:20 PM
Joined: 2011-12-07
7 posts

hi
i noticed in new version of CI (2.1.0) there is a PDO driver, but i could noy use it for connecting to SQLite database.
i used this code in config/database.php

$db['default']['dbdriver''pdo';
$db['default']['database''sqlite:db/db.sqlite'

but i got error messages, does anyone here tried to use new pdo driver for connecting to sqlite, if so, plz share the approach here.
tanX

 
Posted: 07 December 2011 04:40 PM   [ # 1 ]   [ Rating: 0 ]
Avatar
Joined: 2011-03-25
220 posts

I haven’t used it, but some error messages would be nice. What are you getting?

 
Posted: 07 December 2011 04:45 PM   [ # 2 ]   [ Rating: 0 ]
Joined: 2011-12-07
7 posts

Fatal error: Uncaught exception ‘PDOException’ with message ‘could not find driver’ in
C:\xampp\htdocs\mywebsite\system\database\drivers\pdo\pdo_driver.php: 98

 
Posted: 07 December 2011 04:49 PM   [ # 3 ]   [ Rating: 0 ]
Avatar
Joined: 2009-02-19
4545 posts

The 2.1 from the website is missing the PDO driver.  You need to grab the latest version from github.

 Signature 
 
Posted: 07 December 2011 04:51 PM   [ # 4 ]   [ Rating: 0 ]
Joined: 2011-12-07
7 posts

thanX, u mean the version in the ci.com is different from latest version in github?
i don’t think so, error arrises at line 98 of pdo_driver.php, when trying to connect to db.

 
Posted: 07 December 2011 04:53 PM   [ # 5 ]   [ Rating: 0 ]
Avatar
Joined: 2009-02-19
4545 posts

Yes they are different.  Confusing, I know.  I didn’t do it smile

 Signature 
 
Posted: 08 December 2011 07:23 AM   [ # 6 ]   [ Rating: 0 ]
Joined: 2011-12-07
7 posts

i checked with the latest code in github and it doesn’t make any difference, the error arises in this function of pdo_driver.php file:

function db_connect()
 
{
  $this
->options['PDO::ATTR_ERRMODE'PDO::ERRMODE_SILENT;
  
  return new 
PDO($this->hostname$this->username$this->password$this->options);
 

 

 
Posted: 08 December 2011 08:22 AM   [ # 7 ]   [ Rating: 0 ]
Avatar
Joined: 2010-12-20
1591 posts

@CroNiX,
what you mean “missing driver”? Just check the one in download archive page and it contain all necessary files.

@shahroq,
First, did you already enable pdo for sqlite in your php.ini? In some version, it is not enabled by default.

 Signature 

“In Code We Trust.”


CI Library : Gas ORM | Proxy

 
Posted: 08 December 2011 01:28 PM   [ # 8 ]   [ Rating: 0 ]
Avatar
Joined: 2009-02-19
4545 posts

Related to the 2.1 release… the PDO_result class is missing from the PDO driver, so that will cause issues for anyone wanting to use the PDO database driver

https://github.com/EllisLab/CodeIgniter/issues/671#issuecomment-2751042
It was missing in the initial release of 2.1. 2 weeks ago.  They closed this bug report after fixing the missing docs issue, but that was not the PDO issue.  Don’t know if they ever resolved it as the PDO issue didn’t belong in that bug report and Phil didn’t mention anything about PDO when he closed it.

 Signature 
 
Posted: 09 December 2011 07:26 AM   [ # 9 ]   [ Rating: 0 ]
Joined: 2011-12-07
7 posts
toopay - 08 December 2011 08:22 AM

@CroNiX,

@shahroq,
First, did you already enable pdo for sqlite in your php.ini? In some version, it is not enabled by default.

yes i do, & no success.

 
Posted: 09 December 2011 08:48 AM   [ # 10 ]   [ Rating: 0 ]
Avatar
Joined: 2010-12-20
1591 posts

@shahroq, try this one.

Open under system/database/drivers/pdo/pdo_drivers.php, find in about line 80-81, change this line :

$this->hostname .= ";dbname=".$this->database

into

if (strpos($this->hostname'sqlite') === FALSE)
{
   $this
->hostname .= ";dbname=".$this->database;

The PDO driver need to give an exception for sqlite, and the previous uniformal DSN for other database(s), wont work for sqlite case.

Then, your db configuration should match with PDO spec for sqlite :

$db['default']['hostname''sqlite:/mydb.sqlite';
$db['default']['username''';
$db['default']['password''';
$db['default']['database''';
$db['default']['dbdriver''pdo'
 Signature 

“In Code We Trust.”


CI Library : Gas ORM | Proxy

 
Posted: 15 December 2011 04:04 PM   [ # 11 ]   [ Rating: 0 ]
Avatar
Joined: 2010-12-20
1591 posts

@shahroq, use above codes. It safe, previously the DSN spec within our PDO driver was wrong. Refer : Fix for PDO sqlite. The only issue if you working with PDO and sqlite, is rowCount(), since sqlite internally doesnt support unbuffered result, it is known issue, and it is completely related with PDO extension itself.

If you have further issue, let me know.

 Signature 

“In Code We Trust.”


CI Library : Gas ORM | Proxy

 
Posted: 21 December 2011 12:18 AM   [ # 12 ]   [ Rating: 0 ]
Joined: 2011-09-25
2 posts

@shahroq,

I’m using sqlite and I’m available to do inserts for example with “$this->db->query()” but not selects. I don’t get any error, but either data.
This is because the problem with rowCount() that you mentioned?
So I can’t do any select right now using sqlite?

Is there a solution for this?

Thank you!!!

 
Posted: 21 December 2011 02:32 AM   [ # 13 ]   [ Rating: 0 ]
Avatar
Joined: 2010-12-20
1591 posts

@bobafett, yes. SELECT clause via query on PDO driver, require rowCount method, and therefore for sqlite, most likely it will fails silently. I cant offer “clean” fixed for this one, since :
1. sqlite internally was not support unbuffered result handler, which is used by the CI PDO driver.
2. PDO extension itself already warn, about this :

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

Your option to perform the SELECT statement, while using sqlite PDO, is by using simple_query method,

$users $this->db->simple_query('SELECT * FROM users');
foreach (
$users as $user
{
   
// Do something with the result
   
echo '<pre>'.var_export($userTRUE).'</pre>';

Yes, that mean we lost all CI query builder functionality.

But, if you felt bold, and still want to use all CI query builder functionality with PDO-sqlite, and ignore all above warnings :
1. Open system/database/drivers/pdo/pdo_result.php
2. On line 37, within num_rows method, change

return $this->result_id->rowCount(); 

into

// pysqlite does not know the rowcount of SELECT statements,
// because it doesn't fetch all rows after executing the
// select statement. The rowcount has thus to be -1.

return -1

If you decide so, several things you should aware are:
1. Obviously, you cant rely on num_rows anymore.
2. You will not be able to use list_tables method.
3. If you run queries that might not produce a result (especially if you retrieve your query result via result_object), and previously you are often use this syntax:

$query $this->db->get('users');

if (
$query->num_rows() > 0)
{
   
foreach ($query->result() as $row)
   
{
      
echo $row->name;
      echo 
$row->email;
   
}
}
else
{
   
echo 'No result found';

Then, by above modification, you will use this kind of syntax :

$query $this->db->get('users');

// This can be result() or result_array()
$result $query->result();

// Here you can't use num_rows() anymore, but instead
// just check whether the result contain something or not
if ( ! empty($result))
{
   
foreach ($result as $row)
   
{
      
echo $row->name;
      echo 
$row->email;
   
}
}
else
{
   
echo 'No result found';
 Signature 

“In Code We Trust.”


CI Library : Gas ORM | Proxy

 
Posted: 21 December 2011 01:29 PM   [ # 14 ]   [ Rating: 0 ]
Joined: 2011-09-25
2 posts

@shahroq, thank you for complete and quick response!!!

It’s my first project tryng to use sqlite, fortunately I don’t require too much use of the database so I will go for the simpe_query solution. I thougth this method didn’t return any result, just TRUE or FALSE.
Is still good to know the more complete solution for future use.