Dear sqlite-users!
I'm going to make a Visual C++ application, which is able to read database that
was created on server side. However I'm not able to read database that was
generated on the server from Windows.
I've successfully compiled the lates "sqlite-3071502" into my Visual Studio
2008 project. I use the same library in Ubuntu and I could successfully compile
into my CGI project with gcc.
I'm able to create the database file with CGI, called from PHP, however when
I'm going to read it back on Windows, the database is malformed.
(I use CGI because PHP encountered exactly the same error, and I wanted to try
some other way)
Here is a snippet from my CGI code:
sqlite3 *hDb;
int nRet = sqlite3_open(File, &hDb);
if(nRet != SQLITE_OK)
{
printf("ERROR - %s\n", sqlite3_errmsg(hDb));
}
char *szErrMsg = 0;
int rc = sqlite3_exec(hDb, "CREATE TABLE groups(id INTEGER PRIMARY KEY
AUTOINCREMENT, abbr TEXT, name TEXT)", NULL, 0, &szErrMsg);
if(rc != SQLITE_OK)
{
printf("%s\n", szErrMsg);
sqlite3_free(szErrMsg);
}
const char *pSQL[21];
pSQL[0] = "INSERT INTO groups(abbr, name) VALUES ('001', 'Alan')";
pSQL[1] = "INSERT INTO groups(abbr, name) VALUES ('002', 'Alan')";
.
.
.
pSQL[20] = "INSERT INTO groups(abbr, name) VALUES ('021', 'Alan')";
for(int i = 0; i < 21; i++)
{
printf("%s\n", pSQL[i]);
int rc = sqlite3_exec(hDb, pSQL[i], NULL, 0, &szErrMsg);
if(rc != SQLITE_OK)
{
printf("%s\n", szErrMsg);
sqlite3_free(szErrMsg);
continue;
}
}
sqlite3_close(hDb);
The error is actually very interresting. When I open the CGI created database
from SqLite3 shell with Command Prompt the following happens:
sqlite> PRAGMA encoding;
UTF-8
sqlite> select * from groups;
Error: database disk image is malformed
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE groups(id INTEGER PRIMARY KEY AUTOINCREMENT, abbr TEXT, name TEXT);
/**** ERROR: (11) database disj image is malformed *****/
INSERT INTO "groups" VALUES(21,'021','Alan');
.
.
.
INSERT INTO "groups" VALUES(11,'011','Alan');
/**** ERROR: (11) database disj image is malformed *****/
/**** ERROR: (11) database disj image is malformed *****/
/**** ERROR: (11) database disj image is malformed *****/
ROLLBACK; -- due to errors
sqlite> PRAGMA integrity_check;
*** in database main ***
Page 3: btreeInitPage() returns error code 11
On tree page 2 cell 1: Rowid 11 out of order (prevous was 11)
On tree page 2 cell 2: Rowid 11 out of order (prevous was 11)
On tree page 2 cell 3: Rowid 11 out of order (prevous was 11)
On tree page 2 cell 4: Rowid 11 out of order (prevous was 11)
On tree page 2 cell 5: Rowid 11 out of order (prevous was 11)
On tree page 2 cell 6: Rowid 11 out of order (prevous was 11)
On tree page 2 cell 7: Rowid 11 out of order (prevous was 11)
On tree page 2 cell 8: Rowid 11 out of order (prevous was 11)
On tree page 2 cell 10: Rowid 11 out of order (prevous was 13)
Corruption detected in cell 0 on page 2
Corruption detected in cell 1 on page 2
Corruption detected in cell 2 on page 2
Corruption detected in cell 3 on page 2
Corruption detected in cell 4 on page 2
Corruption detected in cell 5 on page 2
Corruption detected in cell 6 on page 2
Corruption detected in cell 7 on page 2
Fragmentation of 5 bytes reported as 0 on page 2
sqlite> select ROWID from groups;
11
11
11
11
11
11
11
11
11
13
11
12
13
14
15
16
17
18
19
20
21
I've attempted to set manually the ROWIDs, but SqLite3 simply ignored my
settings, and results the exact same error.
What is more interresting:
sqlite> SELECT * FROM groups WHERE id > 11;
12|012|Alan
13|013|Alan
14|014|Alan
15|015|Alan
16|016|Alan
17|017|Alan
18|012|Alan
19|019|Alan
20|020|Alan
21|021|Alan
When I read the CGI generated sqlite3 database file with the following code;
the result is just what it should be.
print_r(SQLite3::version());
echo "<br />";
$pDb = new SQLite3('default.cfg');
$result = $pDb->query('SELECT * FROM groups');
if(!$result) {
echo "ERROR: " . $pDb->lastErrorMsg();
}
while($row = $result->fetchArray(SQLITE3_ASSOC)) {
if(!isset($row['abbr']) || !isset($row['name'])) continue;
print_r($row);
echo "<br />";
}
$pDb->close();
Array ( [versionString] => 3.6.22 [versionNumber] => 3006022 )
Array ( [id] => 1 [abbr] => 001 [name] => Alan )
Array ( [id] => 2 [abbr] => 002 [name] => Alan )
Array ( [id] => 3 [abbr] => 003 [name] => Alan )
Array ( [id] => 4 [abbr] => 004 [name] => Alan )
Array ( [id] => 5 [abbr] => 005 [name] => Alan )
Array ( [id] => 6 [abbr] => 006 [name] => Alan )
Array ( [id] => 7 [abbr] => 007 [name] => Alan )
Array ( [id] => 8 [abbr] => 008 [name] => Alan )
Array ( [id] => 9 [abbr] => 009 [name] => Alan )
Array ( [id] => 10 [abbr] => 010 [name] => Alan )
Array ( [id] => 11 [abbr] => 011 [name] => Alan )
Array ( [id] => 12 [abbr] => 012 [name] => Alan )
Array ( [id] => 13 [abbr] => 013 [name] => Alan )
Array ( [id] => 14 [abbr] => 014 [name] => Alan )
Array ( [id] => 15 [abbr] => 015 [name] => Alan )
Array ( [id] => 16 [abbr] => 016 [name] => Alan )
Array ( [id] => 17 [abbr] => 017 [name] => Alan )
Array ( [id] => 18 [abbr] => 018 [name] => Alan )
Array ( [id] => 19 [abbr] => 019 [name] => Alan )
Array ( [id] => 20 [abbr] => 020 [name] => Alan )
Array ( [id] => 21 [abbr] => 021 [name] => Alan )
I'm looking forward to any kind of help.
With kind regards;
S. G.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
