Second sqlite3_prepare_v2() call fails on iOS path-based databases

[Tito Ciuro]

Hello,

The following code snippet runs fine on Mac OS X, but fails on the iOS simulator:

// Obtain a path for the database
NSString *docs = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
NSString *path = [[docs stringByAppendingPathComponent:@"myDB.sqlite"]fileSystemRepresentation];

// Open the database
sqlite3 *db = NULL;
int statusOpen = sqlite3_open_v2( fileSystemRepresentation, &db,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_AUTOPROXY | SQLITE_OPEN_FULLMUTEX, NULL);


// Build the first statement
sqlite3_stmt *oneStatement = NULL;
const char *oneSQL = [[NSString stringWithFormat:@"INSERT INTO %@(%@, %@, %@, %@) VALUES (?,?,?,?);", NSFValues, NSFKey, NSFAttribute, NSFValue, NSFDatatype]UTF8String];
int statusOne = sqlite3_prepare_v2(db, oneSQL, (int)strlen(oneSQL), &oneStatement, &oneSQL);


// Build the second statement
sqlite3_stmt *twoStatement = NULL;
const char *twoSQL = [[NSString stringWithFormat:@"INSERT INTO %@(%@, %@, %@, %@) VALUES (?,?,?,?);", NSFKeys, NSFKey, NSFPlist, NSFCalendarDate, NSFObjectClass]UTF8String];
int statusTwo = sqlite3_prepare_v2(db, twoSQL, (int)strlen(twoSQL), &twoStatement, &twoSQL);

What I see is that statusTwo returns 1, and I have no idea why. What is really puzzling is that if I open the database in memory or temporary mode, it works fine in both Mac OS X and iOS!

So my question I have is, why would the second sqlite3_prepare_v2 statement fail only on path-based iOS apps? :-/

Thanks in advance,

-- Tito

[Simon Slavin]

Post by Tito Ciuro
So my question I have is, why would the second sqlite3_prepare_v2 statement fail only on path-based iOS apps? :-/

The other question is: Is it permissable to have two statements prepared but no further, for the same database connection.

Simon.

[Tito Ciuro]

Hi Simon,

Post by Simon Slavin

Post by Tito Ciuro
So my question I have is, why would the second sqlite3_prepare_v2 statement fail only on path-based iOS apps? :-/

The other question is: Is it permissable to have two statements prepared but no further, for the same database connection.

What do you mean by "no further"? In my app, I use both statements. I have trimmed down the code in this email thread to show the error. Be sure I use these two statements later on. The reason I cache the statements is to save time during inserts, since I use them a lot.

If having two statements prepared is not permissible, should't it fail on Mac OS X as well?

Thanks,

-- Tito

[Simon Slavin]

Post by Tito Ciuro
What do you mean by "no further"? In my app, I use both statements. I have trimmed down the code in this email thread to show the error. Be sure I use these two statements later on. The reason I cache the statements is to save time during inserts, since I use them a lot.

There's probably no reason not to do that. Sorry for any alarm.

Post by Tito Ciuro
If having two statements prepared is not permissible, should't it fail on Mac OS X as well?

There are plenty of examples of SQLite stuff that succeeds in one compiler and fails in another, or succeeds on one OS or File System and fails in another. SQLite doesn't exhaustively error-check every operation because if would slow it down too much. So the only things that are safe to do are the things the documentation says you can do.

But please ignore my previous question about having two simultaneous prepared statements.

Simon.

[Tito Ciuro]

Hello Simon,

Post by Simon Slavin

Post by Tito Ciuro
What do you mean by "no further"? In my app, I use both statements. I have trimmed down the code in this email thread to show the error. Be sure I use these two statements later on. The reason I cache the statements is to save time during inserts, since I use them a lot.

There's probably no reason not to do that. Sorry for any alarm.

Post by Tito Ciuro
If having two statements prepared is not permissible, should't it fail on Mac OS X as well?

There are plenty of examples of SQLite stuff that succeeds in one compiler and fails in another, or succeeds on one OS or File System and fails in another. SQLite doesn't exhaustively error-check every operation because if would slow it down too much. So the only things that are safe to do are the things the documentation says you can do.
But please ignore my previous question about having two simultaneous prepared statements.
Simon.

No, problem, thanks for your help.

After refactoring the code a bit and catching/reporting errors better, it started working again. I'm still not 100% sure why it didn't work before, which bugs me quite a bit. But, after spending some time testing it, I've had so far 100% success rate during testing and execution. Go figure... :-/

Best regards,

-- Tito

[Afriza N. Arief]

Post by Tito Ciuro
Hello,
// Obtain a path for the database
NSString *docs =
[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask,
YES) lastObject];
"myDB.sqlite"]fileSystemRepresentation];
// Open the database
sqlite3 *db = NULL;
int statusOpen = sqlite3_open_v2( fileSystemRepresentation, &db,
SQLITE_OPEN_READWRITE |
SQLITE_OPEN_CREATE | SQLITE_OPEN_AUTOPROXY | SQLITE_OPEN_FULLMUTEX, NULL);

Do you need UTF8String for the sqlite3_open_v2() ?

Post by Tito Ciuro
// Build the first statement
sqlite3_stmt *oneStatement = NULL;
NSFDatatype]UTF8String];
int statusOne = sqlite3_prepare_v2(db, oneSQL, (int)strlen(oneSQL),
&oneStatement, &oneSQL);
// Build the second statement
sqlite3_stmt *twoStatement = NULL;
NSFObjectClass]UTF8String];
int statusTwo = sqlite3_prepare_v2(db, twoSQL, (int)strlen(twoSQL),
&twoStatement, &twoSQL);
What I see is that statusTwo returns 1, and I have no idea why. What is
really puzzling is that if I open the database in memory or temporary mode,
it works fine in both Mac OS X and iOS!
So my question I have is, why would the second sqlite3_prepare_v2 statement
fail only on path-based iOS apps? :-/
Thanks in advance,
-- Tito

[Tito Ciuro]

Hi Afriza,

Post by Afriza N. Arief

Post by Tito Ciuro
Hello,
// Obtain a path for the database
NSString *docs =
[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask,
YES) lastObject];
"myDB.sqlite"]fileSystemRepresentation];
// Open the database
sqlite3 *db = NULL;
int statusOpen = sqlite3_open_v2( fileSystemRepresentation, &db,
SQLITE_OPEN_READWRITE |
SQLITE_OPEN_CREATE | SQLITE_OPEN_AUTOPROXY | SQLITE_OPEN_FULLMUTEX, NULL);

Do you need UTF8String for the sqlite3_open_v2() ?

I don't think so. The fileSystemRepresentation method should we used when dealing with file-based paths. This is because the length of the encoded string in foreign file systems can be longer than the number of Unicode characters in the NSString. So, you would end up with a different length (a "wrong" string) by using UTF8String.

Regards,

-- Tito

[Drake Wilson]

Post by Tito Ciuro
I don't think so. The fileSystemRepresentation method should we used
when dealing with file-based paths.

But not when dealing with SQLite paths, unless I'm mistaken about what
fileSystemRepresentation does. sqlite3_open_v2 always takes UTF-8 and
does any filesystem-specific encoding transformations internally. (It
may still be that it does it incorrectly on some platforms, in which
case that may be a bug.)

Post by Tito Ciuro
-- Tito

---> Drake Wilson

[Tito Ciuro]

Hi Drake,

Post by Drake Wilson

Post by Tito Ciuro
I don't think so. The fileSystemRepresentation method should we used
when dealing with file-based paths.

But not when dealing with SQLite paths, unless I'm mistaken about what
fileSystemRepresentation does. sqlite3_open_v2 always takes UTF-8 and
does any filesystem-specific encoding transformations internally. (It
may still be that it does it incorrectly on some platforms, in which
case that may be a bug.)

Thanks for the heads up.

-- Tito

[Marian Cascaval]

Since this topic has lead to different sub-topic I dare ask a question (I'm a
beginner both in C++ and, Oh boy, in SQLite too).

Tito, do you really need the 5th argument in sqlite3_prepare_v2() i.e.
"&oneSQL"?
From what I understand from your code, there's only one SQL statement to be
prepared, so there would be no need for the supposedly next SQL statement.
Do you reuse (reset) these statements?
I was under the impression that the 5th argument is used when the SQL statement
string contains more than one SQL statement.

Thanks for your patience if I misunderstood something .. or all.



Marian Cascaval




________________________________
From: Tito Ciuro <tciuro-***@public.gmane.org>
To: General Discussion of SQLite Database <sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org>
Sent: Mon, January 31, 2011 9:38:57 PM
Subject: [sqlite] Second sqlite3_prepare_v2() call fails on iOS path-based
databases


// Build the first statement
sqlite3_stmt *oneStatement = NULL;
const char *oneSQL = [[NSString stringWithFormat:@"INSERT INTO %@(%@, %@,
%@, %@) VALUES (?,?,?,?);", NSFValues, NSFKey, NSFAttribute, NSFValue,
NSFDatatype]UTF8String];
int statusOne = sqlite3_prepare_v2(db, oneSQL, (int)strlen(oneSQL),
&oneStatement, &oneSQL);

[Tito Ciuro]

Hello Marian,

Post by Marian Cascaval
Since this topic has lead to different sub-topic I dare ask a question (I'm a
beginner both in C++ and, Oh boy, in SQLite too).
Tito, do you really need the 5th argument in sqlite3_prepare_v2() i.e.
"&oneSQL"?
From what I understand from your code, there's only one SQL statement to be
prepared, so there would be no need for the supposedly next SQL statement.
Do you reuse (reset) these statements?
I was under the impression that the 5th argument is used when the SQL statement
string contains more than one SQL statement.
Thanks for your patience if I misunderstood something .. or all.

Yes, you understood right. I was using the 5th arg for debugging reasons, as I was checking whether the memory was being smashed somewhere. As for the statements, yes, I'm reusing them all the time.

Regards,

-- Tito