PHP Programming - SQLite Version 2.1?

Author

SQLite Version 2.1?

Jim Carlock

2007-01-23, 7:59 am

I added the following lines to PHP.INI.

extension=php_pdo.dll
extension=php_pdo_sqlite.dll
extension=php_sqlite.dll

specifically in that order. I noticed the extensions getting loaded
are alphabetally listed, so I'm thinking that the order of the load
doesn't matter.

Is that true?

And when I use the following statements to create a test.db file...

It creates a file with the following text as the first 48 characters
in the file...

** This file contains an SQLite 2.1 database **

Searching php.net for that string turned up the following link:

http://gcov.php.net/PHP_5_2/lcov_ht...tree.c.gcov.php

It appears that PHP 5.2 mis-identifies the file. I took a look at
some other software found at SourceForge advertised as a
visual front end to access and create SQLite databases. However,
the SQLite Browser identifies the SQLite data file as NOT a 3.1
version (perhaps due to PHP's mis-tagging (?)).

http://sqlitebrowser.sourceforge.net
http://sourceforge.net/projects/sqlitebrowser

Can anyone else confirm this?

Thanks.

--
Jim Carlock
Post replies to the group.
http://www.microcosmotalk.com/tech/windows/php/sqlite

Jim Carlock

2007-01-23, 7:06 pm

"Jim Carlock" wrote:
: I added the following lines to PHP.INI.
:
: extension=php_pdo.dll
: extension=php_pdo_sqlite.dll
: extension=php_sqlite.dll
:
: specifically in that order. I noticed the extensions getting loaded
: are alphabetally listed, so I'm thinking that the order of the load
: doesn't matter.
:
: Is that true?

I don't have an answer to that question.

: And when I use the following statements to create a test.db file...

I forgot to type in the statements. The [extension=php_sqlite.dll]
line above provides the mechanisms...

sqlite_array_query()
sqlite_busy_timeout()
sqlite_changes()
sqlite_close()
sqlite_column()
sqlite_create_aggregate()
sqlite_create_function()
sqlite_current()
sqlite_error_string()
sqlite_escape_string()
sqlite_exec()
sqlite_factory()
sqlite_fetch_all()
sqlite_fetch_array()
sqlite_fetch_column_types()
sqlite_fetch_object()
sqlite_fetch_single()
sqlite_fetch_string()
sqlite_has_more()
sqlite_has_prev()
sqlite_key()
sqlite_last_error()
sqlite_next()
sqlite_prev()
sqlite_rewind()
sqlite_s

()
sqlite_query()
sqlite_query()
sqlite_open()
sqlite_popen()

There's a sqlite.api file available at with provides the parameter lists
for SciTE at the following link...

http://www.microcosmotalk.com/tech/windows/php/sqlite

// The following makes the data file, and creates a table in it...
// The file created though, contains the following string identifying
// it as:
// ** This file contains an SQLite 2.1 database **

var $hDb;
var $sSQL_MT;
var $sErrMsg;

$hDb = sqlite_open("path_to_folder/names.db", 0666, $sErrMsg);
$sSQL_MT = "CREATE TABLE (first_name text, last_name text);";
sqlite_exec($hDb, $sSQL_MT, $sErrMsg);
sqlite_close($hDb);

My aplogies about leaving that out in the last post.

: Searching php.net for that string turned up the following link:
:
: http://gcov.php.net/PHP_5_2/lcov_ht...tree.c.gcov.php
:

I believe I've identified a way to create SQLite version 3 files.
It involves commenting the following line out of the PHP.INI.

; extension=php_sqlite.dll

I'll update this thread once I get a query together to test out
creating a PDO object to create the file, unless someone else
has already run through this and beats me to it.

: It appears that PHP 5.2 mis-identifies the file. I took a look
: at some other software found at SourceForge advertised as
: a visual front end to access and create SQLite databases.
: However, the SQLite Browser identifies the SQLite data file
: as NOT a 3.1 version (perhaps due to PHP's mis-tagging (?)).
:
: http://sqlitebrowser.sourceforge.net
: http://sourceforge.net/projects/sqlitebrowser
:
: Can anyone else confirm this?

I answered that question in the above comments.

There's two SciTE .api files at the following link with instructions
on how to get the SQLite API drop-downs/parameters activated
in SciTE at the following link (sqlite.api and pdo.api).
http://www.microcosmotalk.com/tech/windows/php/sqlite

--
Jim Carlock
Post replies to the group.
http://www.microcosmotalk.com/tech/windows/php/sqlite

Andy Hassall

2007-01-23, 7:06 pm

On Tue, 23 Jan 2007 18:29:59 -0500, "Jim Carlock" <anonymous@127.0.0.1> wrote:

>"Jim Carlock" wrote:
>: I added the following lines to PHP.INI.
>:
>: extension=php_pdo.dll
>: extension=php_pdo_sqlite.dll
>: extension=php_sqlite.dll
>:
>: specifically in that order. I noticed the extensions getting loaded
>: are alphabetally listed, so I'm thinking that the order of the load
>: doesn't matter.
>:
>: Is that true?
>
>I don't have an answer to that question.

It doesn't appear to matter what order they're listed.

>: And when I use the following statements to create a test.db file...
>
>I forgot to type in the statements. The [extension=php_sqlite.dll]
>line above provides the mechanisms...
>
[snip]

Possibly just to add to the confusion, it appears that php_pdo_sqlite.dll is
linked against SQLite 3.x, but php_sqlite.dll is linked against 2.x:

$ strings php_sqlite.dll | grep -i sqlite[23]
sqlite2
sqlite2_create_function
ext\sqlite\pdo_sqlite2.c

$ strings php_pdo_sqlite.dll | grep -i sqlite[23]
sqlite3_extension_init
sqlite3_get_table() called with two or more incompatible queries

See also:
http://bugs.php.net/bug.php?id=34010
http://bugs.php.net/bug.php?id=31848

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Jim Carlock

2007-01-26, 3:59 am

"Andy Hassall" <andy@andyh.co.uk> posted...
: Possibly just to add to the confusion, it appears that php_pdo_sqlite.dll
: is linked against SQLite 3.x, but php_sqlite.dll is linked against 2.x:

Yes, that's exactly what I see. The first characters of the data files, when
viewed with a hex viewer identify the versions. Specifically, here's what
I see...

"** This file contains an SQLite 2.1 database **"
"SQLite format 3"

The syntax to create the version 3 file...

$sDSN = "sqlite:./test/test.db";
$sSQL = "CREATE TABLE tNames(name_first text, name_last text);";
$sUserName = "myname";
$sPW = "My321Goofy123Password";
$hPDO = new PDO($sDSN, $sUserName, $sPW);
$hPDO->exec($sSQL);
$hPDO = null;

The above created the table in the appropriate folder with two fields,
as expected.

I got into the data file using the SQLite Database Browser, from here,
http://sqlitebrowser.sourceforge.net/, and view the internals of the file
created by the above statements. However, I noticed it didn't require
a username nor password to get into the file and add new data to the
file. I'm missing out on something as far getting a userid and password to
work for the file.

Thanks for your comments, Andy.

--
Jim Carlock
Post replies to the group.
http://www.microcosmotalk.com/tech/windows/php/sqlite

Andy Hassall

2007-01-26, 6:59 pm

On Fri, 26 Jan 2007 01:51:37 -0500, "Jim Carlock" <anonymous@127.0.0.1> wrote:

>"Andy Hassall" <andy@andyh.co.uk> posted...
>: Possibly just to add to the confusion, it appears that php_pdo_sqlite.dll
>: is linked against SQLite 3.x, but php_sqlite.dll is linked against 2.x:
>
>Yes, that's exactly what I see. The first characters of the data files, when
>viewed with a hex viewer identify the versions. Specifically, here's what
>I see...
>
>"** This file contains an SQLite 2.1 database **"
>"SQLite format 3"
>
>The syntax to create the version 3 file...
>
>$sDSN = "sqlite:./test/test.db";
>$sSQL = "CREATE TABLE tNames(name_first text, name_last text);";
>$sUserName = "myname";
>$sPW = "My321Goofy123Password";
>$hPDO = new PDO($sDSN, $sUserName, $sPW);
>$hPDO->exec($sSQL);
>$hPDO = null;

OK, I get a different result. I get just the format 3 label in the datafile.
This is on Windows XP:

$ php -v
PHP 5.2.0 (cli) (built: Nov 2 2006 11:57:36)
Copyright (c) 1997-2006 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2006 Zend Technologies

This is from the standard Windows binary distribution from php.net.

The file it produces is below:

$ hexdump -C test.db
00000000 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 |SQLite format 3.|
00000010 04 00 01 01 00 40 20 20 00 00 00 01 00 00 00 00 |.....@ ........|
00000020 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 01 |................|
00000030 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00 |................|
00000040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
*
00000060 00 00 00 00 0d 00 00 00 01 03 b2 00 03 b2 00 00 |................|
00000070 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
*
000003b0 00 00 4c 01 06 17 19 19 01 75 74 61 62 6c 65 74 |..L......utablet|
000003c0 4e 61 6d 65 73 74 4e 61 6d 65 73 02 43 52 45 41 |NamestNames.CREA|
000003d0 54 45 20 54 41 42 4c 45 20 74 4e 61 6d 65 73 28 |TE TABLE tNames(|
000003e0 6e 61 6d 65 5f 66 69 72 73 74 20 74 65 78 74 2c |name_first text,|
000003f0 20 6e 61 6d 65 5f 6c 61 73 74 20 74 65 78 74 29 | name_last text)|
00000400 0d 00 00 00 00 04 00 00 00 00 00 00 00 00 00 00 |................|
00000410 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
*
00000800

>I got into the data file using the SQLite Database Browser, from here,
>http://sqlitebrowser.sourceforge.net/, and view the internals of the file
>created by the above statements. However, I noticed it didn't require
>a username nor password to get into the file and add new data to the
>file. I'm missing out on something as far getting a userid and password to
>work for the file.

I didn't think SQLite supported username or passwords.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Sponsored Links