• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
SQLite3 + CI 1.7.2

#1
[eluser]wwwald[/eluser]
It's been a while since I messed with SQLite in CodeIgniter.

Let me see if I get this straight: searching the forums, it seems that with 1.7.2, the PDO solution for SQLite3 has been broken. The classic sqlite drivers included in CI is for SQLite2.

Now: I'm still hoping for some way to get SQLite3 up and running for a new project of mine... Is my only option using an SQLite2 database, for now? Or does anyone know of any progress regarding SQLite3 in CI 1.7.2?

Thanks a bunch for any pointers,
wwwald

#2
[eluser]mpc[/eluser]
[quote author="wwwald" date="1257824881"] It's been a while since I messed with SQLite in CodeIgniter.

Let me see if I get this straight: searching the forums, it seems that with 1.7.2, the PDO solution for SQLite3 has been broken. The classic sqlite drivers included in CI is for SQLite2. [/QUOTE]

Right ...

[quote author="wwwald" date="1257824881"] Is my only option using an SQLite2 database, for now?

Nope. One can call: SQLite3_whatever(), directly, i.e. without CodeIgniter.

[quote author="wwwald" date="1257824881"] Or does anyone know of any progress regarding SQLite3 in CI 1.7.2? [/QUOTE]

... 'above my paygrade'!

In theory, PDO was supposed to be roughly analogous to Microsoft's ODBC (early 1990's) generic ODBC database abstraction layer.

PDO is required to interface with SQLite3.

I've never read/heard explained what the CodeIgniter crew has against PHP's PDO generic database abstraction layer? I'd sure like to know, though ...

#3
[eluser]wwwald[/eluser]
Quote:[quote author="wwwald" date="1257824881"]Is my only option using an SQLite2 database, for now?

Nope. One can call: SQLite3_whatever(), directly, i.e. without CodeIgniter.
[/quote]

Now why didn't I think of that :-)
Very interesting... not really a long-term solution, but that will definitely do for the current project.

Thanks very much for the hint,
wwwald

#4
[eluser]umefarooq[/eluser]
Hi try this patch and its working really fine with CI and sqlite3 i tried it no problem at all

http://blog.trevorbramble.com/past/2009/...r_sqlite3/

#5
[eluser]wwwald[/eluser]
[quote author="umefarooq" date="1257945319"]Hi try this patch and its working really fine with CI and sqlite3 i tried it no problem at all

http://blog.trevorbramble.com/past/2009/...r_sqlite3/[/quote]

Very very very useful!

I found some problems with this patch, though. I have posted a reply on Trevor's blog, I hope he can give some feedback on it.

The problem is this: there seem to be problems fetching results using Codeigniter's standard database functions like result(), row(), ...
I have a table "registrations" with some records that I'm SELECTing. However, the following code outputs NO results:

Code:
$regs= $this->db->get('registrations');
foreach ($regs->result() as $row) {
   print_r($row);
}

The following does work. It directly uses functions of the underlying PDOStatement object, instead of using CI's database wrapper to get result records:

Code:
$regs= $this->db->get('registrations');
while ($row = $regs->result_id->fetchObject()) {
   print_r($row);
}

The $regs->result_id is the actual PDOStatement object.
I'm not completely sure why the first code sample does not work, but I suspect it has to do with the num_rows() function in sqlite3_result.php. Because of SQLite3 API specifics, it is not possible to retrieve the rowcount. Hence, PDO also cannot know the rowcount, and the num_rows() functions in sqlite3_result.php simply returns 0 in any case.
I think that causes CI database functions like fetch(), result(), row() to return empty arrays instead of the wanted results.

I don't know if this is the expected behaviour of the library... after all, it's a quick adaptation of an existing library, it doesn't claim full functionality and all that.
I can just resort to using PDOStatement's fetchObject() method to make this work, but I was definitely wondering if this is the intended way of working with this library...

Any thoughts of you guys are appreciated, I hope to hear from Trevor himself too.

Thanks a bunch for the help,

wwwald

#6
[eluser]umefarooq[/eluser]
great to hear about that well i tried this connector for connection and selecting data after that i haven't fond time to do some R&D on it always putting some question try to search in google i have many things with CI through google, it is also searching in forums also

#7
[eluser]mpc[/eluser]
[quote author="wwwald" date="1257968647"] I suspect it has to do with the num_rows() function in sqlite3_result.php. Because of SQLite3 API specifics, ... [/quote]

... just guessing, but where would sqlite3_result.php be called? i.e. sqlite_result.php wouldn't get you there in sqlite3.

#8
[eluser]wwwald[/eluser]
[quote author="mpc" date="1258340669"][quote author="wwwald" date="1257968647"] I suspect it has to do with the num_rows() function in sqlite3_result.php. Because of SQLite3 API specifics, ... [/quote]

... just guessing, but where would sqlite3_result.php be called? i.e. sqlite_result.php wouldn't get you there in sqlite3.[/quote]

Not sure if I completely get what you mean, but sqlite3_result.php is used whenever database results are requested in my code, using result() or row() or similar commands. It's a driver-specific subclass of CI's own CI_DB_result class.

In fact, I've found a workaround for the num_rows() function that partly solves that situation, inspired by the wiki page on the PDO-SQLite3 approach. There's still some stuff I don't fully get about the inner database workings, maybe I'll get those ironed out in the coming days/weeks. You'll hear from me when I do :-)

wwwald

#9
[eluser]Unknown[/eluser]
[quote author="wwwald" date="1258343705"]

In fact, I've found a workaround for the num_rows() function that partly solves that situation, inspired by the wiki page on the PDO-SQLite3 approach. There's still some stuff I don't fully get about the inner database workings, maybe I'll get those ironed out in the coming days/weeks. You'll hear from me when I do :-)

wwwald[/quote]

Hi wwwald,

(To all: First post, so be gentle with me... ;-) )

Any luck with this? I'm trying the PDO approach to reading a SQLite3 database. I'm running into some curious problems around 'off-by-one' errors and so I was wondering whether you had a better approach.

As for CodeIgniter, I like the lightweight approach and am grasping it quite quickly (I think).

Does anyone know (I admit, I'm a PHP amateur) whether there are known issues using the sqlite PDO driver with SQLite3 (sqlite library 3.3.7) that may cause off-by-one errors when accessing records (difficult situation to explain - I hope this triggers some reaction).

Thanks all!

vdlaan

*EDIT: Forget about the off-by-one error, fluke column mix-up. Found it thanks to FETCH_ASSOC. You live and learn... *


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2017 MyBB Group.