SQLite select call returning nothing.

[QuickConnect]

Dominic,

I have setup a database connection and made a call to the connection
in an attempt to see how database connectivity works. Here is the
code I tried. No errors are generated. The connection is created in
one class and used in another.

//connection creation. This is stored in a NSMutable dictionary.
[[BxDatabaseConnection alloc] initWithSQLiteFile:@"demo.sqlite"
locking:YES error:&errString]

BxDatabaseConnection *theDB = [controller.theDatabaseDefinitions
getDatabaseForName:@"exampleSQLiteDB"];

NSArray *result = [theDB fetchAll:@"SELECT * FROM user"];
NSLog(@"result: %@",result);

The log always reports "results (null)". I have verified that the
table exists and that it has four records.

Ideas?

Thanks,

Lee

[Dominic Blais]

The SQLite driver included in Bombax is for SQLite version 3. Is this
a version 2 database perhaps? If you create the database and tables
anew using Bombaxtic and then read it, does it work then?
Thanks,
Dominic Blais
COO & Hacker-in-Chief

[QuickConnect]

Dominic,

I generated the file with the Firefox SQLiteManager plugin. I believe
it generates SQLite 3.0. I have used these generated files, including
this one, in my iPhone apps and have had no problem. I know that
there I am using SQLite 3.0.

I changed the code to start a transaction, insert a single record,
retrieve all of the records, end the transaction. No errors were
thrown. The query still returns nothing.

Where next?

This is the last item for the example app of my port of the
QuickConnect MVC framework. Once this is working I can send it to you
with an explanation for feedback regarding creating an Xcode template
for QC and Bombax together.

Thanks,

Lee

[Dominic Blais]

That is odd indeed. I've just tried the following code and it worked
correctly when I did. Would you please give it a try and see if it
works when you do it also? If it does work, I think it is likely a
version incompatibility in the SQLite database (this can be verified
by using the vanilla C SQLite calls to open and query the database).

#import "MyHandler.h"

@implementation MyHandler

- (id)renderWithTransport:(BxTransport *)transport {
[transport setHeader:@"Content-Type"
value:@"text/plain"];
BxDatabaseConnection *conn = [[BxDatabaseConnection alloc]
initWithSQLiteFile:@"test.sqlite3"

locking:YES

error:nil];
[conn beginTransaction];
[conn execute:@"CREATE TABLE users (name TEXT, password TEXT)"];
[conn execute:@"INSERT INTO users (name, password) VALUES
('Stephen', 'joshua')"];
[conn executeWith:@"INSERT INTO users (name, password) VALUES (:
1, :2)", @"John", @"secret"];
NSArray *results = [conn fetchAll:@"SELECT * FROM users"];
for (NSArray *result in results) {
[transport writeFormat:@"Name: %@ Password: %@\n", [result
objectAtIndex:0], [result objectAtIndex:1]];
}
[conn commitTransaction];
[conn close];
[conn release];
return self;
}

@end

[QuickConnect]

Dominic,

I think I have isolated the cause of the problem.

I have included the .sqlite file in the Resources group of the
project. It is being copied into the debug build directory by the
target declarations. If I open the orignal file in the project
directory using the Firefox plugin or MesaSQLite everything is fine.

If I open the file found in the debug build directory after a build
and debug is begun The Firefox plugin shows an empty database and
MesaSQLite displays the message "file is encrypted or is not a
database file".

If I copy the original sqlite file directly into the debug build
directory after a build and debug is begun everything works fine when
the query executes.

The code you sent does work. It creates the file after the build
process is complete.

Are you doing something in a special build step? Am I including the
file in the project in a way that you did not intend?

Thanks,

Lee

[QuickConnect]

Dominic,

More information.

I moved my SQLite wrapper from my iPhone framework over into my Bombax
test project. Each time I use it it works. The data is accessed
correctly. No errors are generated.

Directly after using my wrapper I call yours. I get no connection
error yet the result of calling fetchAll is nil. I checked the last
error attribute of the BxDatabaseConnection object and it is
displaying 'no such table: user'.

I ensured that the SQL executed in each wrapper is the same by using a
local variable. I also ensured that the name of the database is the
same using a variable.

Could the problem be that your code is generating a new demo.sqlite
file and using it instead of the one I have declared? If so where
would I look to see this newly generated file?

Ideas?

Thanks,


Lee

[Dominic Blais]

Hello, Lee. It is definitely possible that it's a matter of there
being two different files: one newly created by Bombaxtic and the
existing SQLite file you are trying to open. I would recommend trying
to use an absolute path for the SQLite database you are opening. The
path used will be the current path for the executable as returned by
getcwd() -- so, typically where the binary executable is. No munging
or other changes are done on the path passed to initWithSQLiteFile, as
shown in the code from BxDatabaseConnection.m below. I hope this
helps and that it is just a matter of pointing it to the right file.
Sincerely,

Dominic Blais
COO & Hacker-in-Chief

- (id)initWithSQLiteFile:(NSString *)path
locking:(BOOL)locking
error:(NSString **)error {
[super init];
_isLocking = locking;
_connectionType = BxDatabaseConnectionTypeSQLite;
self.lastError = nil;
_isClosed = NO;
sqlite3 *conn;
if (sqlite3_open([path UTF8String], &conn) != SQLITE_OK) {
if (error != nil && error != NULL) {
*error = [NSString
stringWithUTF8String:sqlite3_errmsg(conn)];
}
sqlite3_close(conn);
return nil;
}
_rawConnection = (void *) conn;
if (_isLocking) {
_lock = [[NSRecursiveLock alloc] init];
}
return self;

[QuickConnect]

Even more info.

I found the generated SQLite file. It appears that your wrapper
generates a file in the Build/Debug directory. The file generated
there, assuming no tables are created in the code and no data
insertions are done, is not a valid SQLite file. Thus when I try to
open it I get the invalid database file error message.

My framework code looks inside the .app package to find the SQLite
file since it expects the database to be included in the Xcode project
resources.


Suggestion:

In your code check inside the .app package to see if the file
requested already exists. Why would a developer want to include
a .sqlite file in the project? They, as I do, may want to include a
database where the tables are already existent and are empty or they
may have a partially or fully pre-populated database that they want to
include in the application. This makes it easier at deployment time.

Here is the code to get the full path.

NSString *path = [[[NSBundle mainBundle] resourcePath]
stringByAppendingPathComponent:dbName];

If it doesn't exist there, and it doesn't exist parallel with the .app
file then the user must want a new sqlite database file generated.

Here is the code to check if the file exists:

if([[NSFileManager defaultManager] fileExistsAtPath:path] == YES)


Thanks,



Lee