[sqlite] 0 bytes in -wal file indicates successful checkpoint?

[Jerry Krinock]

Assertion:  If the size of the -wal file is 0 bytes, then this means that the 
associated database has been checkpointed and, if I am sure that no process has 
the database open, I can safely discard the -shm and -wal files, and pass only 
the main database file, to another user say, with no fear of data loss.

Am I correct?

Thank you,

Jerry

Re: [sqlite] Adding WHERE to query --> database disk image is malformed

[Jerry Krinock]

On 2014 Jun 24, at 14:46, David Empson  wrote:

> The most likely explanation is that it got processed by something which 
> thought it should be treated as ASCII text and was doing a spurious LF-to-CR 
> translation. If there was only one 0x0A byte in the "good" file, then that is 
> the only one which would have been modified.

Yes, that makes sense, David.  I don’t think that happened.  But I’m still 
thinking :)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding WHERE to query --> database disk image is malformed

[Jerry Krinock]

(I think it’s cleaner to reply to three replies in one message; here goes…)

On 2014 Jun 23, at 22:43, Keith Medcalf  wrote:

> Unique is implemented (as it must be) via a unique index.

Very good, Keith.  So my database *does* have an index.

> You should be able to rebuild the corrupted index with:
> REINDEX ItemTable;

Nope.  If I try that on the “bad” file, I get Error: Database disk image is 
malformed.  If I manually change byte 2048 to 0x0A first, then the command 
succeeds.  Looking at the resulting database, it seemed to change the order of 
the columns listed in the last page, which I assume is not significant.

On 2014 Jun 24, at 00:06, Clemens Ladisch  wrote:

>  says …

Thank you, Clemens.  0x0A=index.  0x0D=table.  I suppose that is a sensible 
re-use of “carrige return” and “line feed”.

> So the bad index claims to be a table.


> Is there any other 0x0A byte in the good file?

No, only that one.

On 2014 Jun 24, at 02:10, Eduardo Morras  wrote:

> Did you create the db schema with newer version of sqlite3? Perhaps you did 
> that and create a partial index, not supported on older sqlite3 versions.

I see that the Partial Index feature was added in August 2013, in sqlite 3.8.  
I don’t think I have anything greater than 3.7.x on this Mac, but I’m looking 
into it.

* * *

According to the git history for my project, I committed the corrupt file about 
10 days ago.  Still trying to figure out how that happened.  Thank you guys for 
all of the clues.

Jerry

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding WHERE to query --> database disk image is malformed

[Jerry Krinock]

Oh, I just realized that, although Igor’s suggestion “fixed” the problem, this 
database definitely does not contain any indexes.  The schema dump is simply 
this…

CREATE TABLE ItemTable (
  "key" text UNIQUE,
  value blob NOT NULL
);


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding WHERE to query --> database disk image is malformed

[Jerry Krinock]

On 2014 Jun 23, at 21:49, Igor Tandetnik  wrote:

> One possibility: the query with WHERE clause attempts to use some index, 
> while the query without doesn't.
> 
> Try this query:
> 
> SELECT * FROM itemTable WHERE +key = 'profileName' ;
> 
> Note the + sign - this suppresses the use of index.

Very good, Igor!  Indeed, adding “+” to the query makes it work, no database 
corruption.

>  The data corruption happens to affect the area of the file where this index 
> is stored.

Ah, so now we still need to find the root cause.  Fortunately, I was able to 
dig up an older database file which contains exactly the same data, but works 
properly.  Performing a binary diff on the Good vs. Bad file found only four 
bytes different.

The first three differences are in the Header: the file change counter, the 
version_valid_for, and the SQLITE_VERSION_NUMBER.  As expected, changing these 
did not help.  (The Good file is 3.7.6, the Bad file is 3.7.13, and I am using 
the sqlite command-line tool version 3.7.13.)

The culprit is byte 2048, the first byte in the 3rd page, assuming the 100-byte 
Header is part of the first page.  In the Good file, it is 0x0A and in the Bad 
file, it’s 0x0D.  Kind of weird that this would be part of an index - looks 
like a Mac vs. Unix line ending clash.

But that’s the easy part.  I wonder how long I’d have to study the sqlite file 
format document to decode the purpose of Byte 2048.  For practical purposes, I 
could maybe just fix the bad byte and move on, but this database is a resource 
in an app I develop, and I sure would like to know how this happened.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Adding WHERE to query --> database disk image is malformed

[Jerry Krinock]

How can it be that adding a WHERE clause to a successful query causes ‘database 
disk image is malformed’?

My database has one table named `itemTable`.  This table has two columns, `key` 
which is type text and `value` which is type blob.  There are two rows of data. 
 Their `value` blobs are actually strings encoded as UTF16 little endian.

The issue is demonstrated in the following transcript, using the sqlite command 
line tool in Mac OS X.

Air2: jk$ sqlite3 Test.sql 
SQLite version 3.7.13 2012-07-17 17:46:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT key from itemTable ;
profileName
extoreName
sqlite> SELECT key FROM `itemTable` WHERE `key` = 'profileName' ;
Error: database disk image is malformed
sqlite> SELECT * FROM `itemTable` WHERE `key` = 'nonexistent' ;
Error: database disk image is malformed

// Same succeed,fail result if I change query to "SELECT *" instead of "SELECT 
key".

sqlite> SELECT * FROM `itemTable` ;
profileName|Fooobar
extoreName|Baah
sqlite> SELECT * FROM `itemTable` WHERE `key` = 'profileName' ;
Error: database disk image is malformed

One thing I find rather surprising is that sqlite seems to know that the 
14-byte and 24-byte blobs are UTF16-LE encoded strings, and prints them as 
“Fooobar” and “Baah”.

Is my database OK or malformed?

The same thing happens when I execute the failing query with the sqlite3 C 
Library, using years-old tested code.

Thank you!

Jerry Krinock

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Not reading data in the -wal file? (Not Reproducible)

[Jerry Krinock]

Thank you to Igor and Richard.  I've studied this issue more, and still don't 
have an answer, although I've not been able to reproduce it either.  I'm not 
using shared cache, and even if I did leave a database connection open, which 
seems impossible since sqlite3_open(), sqlite3_finalize() and sqlite3_close() 
appear in that order with no early returns in the code and which is always 
called on the main thread, the next query would open a new database connection.

But at least I tried, and got advice from the smartest people out there.

Jerry

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Not reading data in the -wal file? (Not Reproducible)

[Jerry Krinock]

On 2013 Jun 06, at 13:28, Richard Hipp  wrote:

> My guess is that App1 never actually committed the transaction.  Are you
> sure that you ran COMMIT?  And are you sure that the COMMIT was successful?

Thank you, Richard.  I didn't have a scope on App 1, and it's much more 
complicated.  It's possible that it did not successfully COMMIT.

Let's retell the story, calling them processes instead of apps.  The 
command-line tool is Process 3.  The App2, which misses the new record at 
first, is Process 2, then Process 4.

• Launch Process 2.
• Launch Process 1.
• Add a record in Process 1.  Possibly does not COMMIT.
• Launch Process 3.
• In Process 3, run the query, then terminate.  New record is PRESENT.
• In Process 2, open database, run the query, checkpoint, and close.  New 
record is ABSENT.
• Terminate Process 2, relaunch as Process 4.
• Process 4 opens database, runs the query, checkpoints, and closes.  New 
record is PRESENT.

Examining my code carefully, I'm sure that Process 2/4 does *not* keep the 
database open.  It opens the database when it needs to do a query, does the 
query, checkpoints, closes it, and forgets the database connection handle.

It don't see how Process 3 could get the new record, but Process 2 would not 
get it until a relaunch.

Is the story as I told it possible, or must I have gotten something wrong?

Jerry

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Not reading data in the -wal file? (Not Reproducible)

[Jerry Krinock]

I just spent a couple hours on a really strange problem that went away.

• Ann sqlite database had 13 rows in one of its tables.
• In App 1, which uses the "C" interface, add a new row.
• In App 2, which also uses the "C" interface, open that database with 
sqlite3_open(), then run query "SELECT * from 'tableName'", using 
sqlite3_prepare(), sqlite3_step() iteratively, and sqlite3_finalize(), then 
sqlite3_wal_checkpoint_v2(passing SQLITE_CHECKPOINT_PASSIVE) and 
sqlite3_close().  This is working code which I have not touched in five years, 
except to update to the new checkpoint function.

Expected Result: 14 rows

Actual Result: 13 rows.  The new row is absent.

• Open the database using the sqlite3 command line tool built into Mac OS X 
10.8.4, and do the same query.  Result: 14 rows.
• Repeat the open,query,checkpoint,close in App 2.  Result: still 13 rows.

• Peek inside the database main file, -shm file and -wal file with a text 
editor.  I see that the new row is not in the main file but is still in the 
-wal file.  I understand that -shm and -wal files are an implementation detail 
of sqlite3, and that I should not worry about where the records are.

• Quit and relaunch App 2.

• Repeat the open,query,checkpoint,close in App 2.  Result: now it gets all 14 
rows!

I've since been retesting these apps for the last couple hours and all has been 
fine since.

App 1 is built with sqlite 3.7.15.
App 2 is built with sqlite 3.7.14.
The Mac's command line tool is sqlite 3.7.12.

So, there's no explanation in the version numbers.

This is very strange, but I did get down to the query level with the debugger.

Just prior to this sequence of events, I had deleted a half dozen records in 
App 1, which kept showing up in queries in App 2.

Any possible explanations would be appreciated.

Jerry Krinock

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug: Compiler Warning, Mac OS X, in proxyGetHostID()

[Jerry Krinock]

In sqlite3.c, version 3.7.4, line 28396 is:

  struct timespec timeout = {1, 0}; /* 1 sec timeout */

This declaration should be moved inside this #if

#if defined(__MAX_OS_X_VERSION_MIN_REQUIRED)\
   && __MAC_OS_X_VERSION_MIN_REQUIRED<1050

because presently you get a compiler warning of "unused variable" if the #if 
condition is not satisfied, that is, if you're compiling for only recent 
versions of Mac OS X.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How rebuild with larger page size from command line?

[Jerry Krinock]

On 2008 Dec, 02, at 21:19, Thomas Briggs wrote:

>   Try removing the semi-colon at the end of the .read statement.  The
> semi-colon is the query terminator, but because dot-commands aren't
> queries they don't require the semi.  As such the .read command in
> twoLiner.sh is either seeing a third (and invalid) argument or an
> invalid file name ("placesDump.txt ;").

Ah that works.  Although I haven't tried Kishor's idea, that should  
obviously work too.

Thanks all,

Jerry
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How rebuild with larger page size from command line?

[Jerry Krinock]

On 2008 Dec, 02, at 19:44, Thomas Briggs wrote:

>   Put both commands (the pragma and the read) into a file (e.g.
> foo.txt) and then do:
>
> sqlite3 newDatabase.sqlite '.read foo.txt'

Looked like a great idea, Thomas but it doesn't work for me:

jk$ echo 'PRAGMA page_size=4096 ;' > twoLiner.sh
jk$ echo '.read placesDump.txt ;' >> twoLiner.sh
jk$ sqlite3 places.sqlite '.read twoLiner.sh'
unknown command or invalid arguments:  "read".

The file twoLiner.sh does have the expected contents:

PRAGMA page_size=4096 ;
.read placesDump.txt ;

I get the same error if I delete the PRAGMA line and just have  
the .read in the file.

In the man page for sqlite3, .read will "Execute SQL in [a file]".   
The problem is probably that .read itself is a meta-command, not  
"SQL"; hence .read cannot be nested.

Any other ideas?

Thanks,

Jerry


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How rebuild with larger page size from command line?

[Jerry Krinock]

I need a command-line script running on Mac OS 10.5 to rebuild sqlite  
3 database files with a page_size of 4096 bytes.

The first line of my script dumps the database to a text file, then  
next line should read it create a new one.  Since the default page  
size is 1024 bytes, documentation says that I need to change it with a  
PRAGMA before creating the database.  So I do this:

sqlite3 newDatabase.sqlite 'PRAGMA page_size=4096; .read dump.txt'

Result:

SQL error: near ".": syntax error

If I eliminate either the PRAGMA or the .read statement, there is no  
error.  But I need them both.  What can I do?

Thank you,

Jerry Krinock
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 file format

[Jerry Krinock]

Well, is there any way to determine, from an sqlite database file, the  
exact dot dot version of sqlite3 which produced the file?  A quick  
hack is OK since I don't need to do this in production, just  
troubleshoot a possible forward-compatibility issue with a remote user.

I see that the first few bytes in the file are always "SQLite format  
3" but the following bytes don't seem to add up to the dot dots.

Thanks,

Jerry Krinock
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "Unsupported File Format" from 3.1.3

[Jerry Krinock]

Someone sent me an sqlite database file.

Opening in Mac OS 10.5 with the built-in sqlite 3.4.0, no problems.

Opening in Mac OS 10.4 with the built-in sqlite 3.1.3, any query  
returns sqlite error 1, "unsupported file format".

Similar files from other users open in either Mac OS/sqlite version.

I'd thought that sqlite3 databases were generally backward- 
compatible.  Is there any way to find out what is "unsupported" by  
sqlite 3.1.3 in this database?

Thanks,

Jerry Krinock
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Does sqlite3_prepare() clean up after itself if it fails?

[Jerry Krinock]

The Blob Example [1] contains code [2] in which, if sqlite3_prepare()  
fails, the subsequent call to sqlite3_finalize() is skipped.  Is this  
OK?  Does sqlite3_prepare() free up any memory it may have allocated  
if it fails?

I've read the documentation for these two functions but still don't  
know.

Thanks,

Jerry Krinock

[1] http://www.sqlite.org/cvstrac/wiki?p=BlobExample

[2]:
static int writeBlob(
   sqlite3 *db,
   const char *zKey,
   const unsigned char *zBlob,
   int nBlob) {
   const char *zSql = "INSERT INTO blobs(key, value) VALUES(?, ?)";
   sqlite3_stmt *pStmt;
   int rc;

   do {
 rc = sqlite3_prepare(db, zSql, -1, , 0);
 if( rc!=SQLITE_OK ){
   return rc;
 }

 sqlite3_bind_text(pStmt, 1, zKey, -1, SQLITE_STATIC);
 sqlite3_bind_blob(pStmt, 2, zBlob, nBlob, SQLITE_STATIC);

 rc = sqlite3_step(pStmt);
 assert( rc!=SQLITE_ROW );

 rc = sqlite3_finalize(pStmt);

   } while( rc==SQLITE_SCHEMA );

   return rc;
}

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can I manually Lock a database?

[Jerry Krinock]

 From reading the documentation I see that sqlite seems to have a very  
smart locking mechanism going on under the hood, regulating concurrent  
access by multiple applications.  Unfortunately, the designers of  
another application with which I share a database have decided to  
cache data internally, so that the only safe access is when that other  
app is not running.

That's easy enough to detect with API from the OS, but a conflict can  
still occur if they launch and start reading while I am in the middle  
of writing something I don't want to stop.  I need to lock the  
database so that they get SQLITE_BUSY until I'm done.

How can I manually lock the database using the C API?  I can't find  
any "lock" function.

Thanks,

Jerry Krinock
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_MAX_EXPR_DEPTH

[Jerry Krinock]

On 2008 Jan, 18, at 19:34, [EMAIL PROTECTED] wrote:


But instead of all that trouble, why not just say:

  id IN (1,2,3,4,5,...,N)


Well, I guess the reason I did not say that is because: I had not yet  
had my SQL lesson for the day.


All is wonderful now.  Thank you for this, and the explanations!



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] SQLITE_MAX_EXPR_DEPTH

[Jerry Krinock]

My query:

DELETE FROM `table1` WHERE (`id`=1 OR `id`=2 OR `id`=3 OR ... OR `id`=N)

using the C API.  When N exceeds 999, I get an error stating that the  
maximum depth of 1000 has been exceeded, and this is documented in http://www.sqlite.org/limits.html 
, item 5.


Of course, I could fix this by doing multiple queries when N>999, but  
my code is very nicely encapsulated as is, and that change would make  
it all yucky.  So I'd like other alternatives.


1.  I would describe my query as "1000 clauses wide".  I'm not nesting  
anything "1000 levels deep".  Is there a way to rewrite my query and  
make it work?


2.  Documentation implies that I can change the parameter  
SQLITE_MAX_EXPR_DEPTH from the default of 1000.  But I can't find  
SQLITE_MAX_EXPR_DEPTH in sqlite3.h.  Seems to be neither a compiler  
macro nor a global.  (Mac OS X 10.5, sqlite 3.4.0).  Where is it?


Thanks,

Jerry Krinock



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] C API: Manifest type SQLITE_INTEGER: Is it 32- or 64-bit?

[Jerry Krinock]

On 2008 Jan, 03, at 17:21, Kees Nuyt wrote:


If I understand the info at
http://www.sqlite.org/c3ref/c_blob.html
well, the INTEGER is always a 64-bit signed integer.
Internally, integers are compressed, so they don't occupy eight
bytes all the time.

sqlite3_column_int64(); will always return a sqlite3_int64.
So, no need to worry.


Thanks, Kees (and John S. too).  So, apparently the function  
sqlite3_column_int() is an alternative which can be used, under  
"normal" circumstances, when you know the value is small enough, and  
want to assign it to a int.




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] C API: Manifest type SQLITE_INTEGER: Is it 32- or 64-bit?

[Jerry Krinock]

I need to read an sqlite database generated by others.  So I wrote an  
outer loop which steps through the rows of a table using sqlite3_step,  
and an inner loop which steps through the columns.  The inner loop  
finds the type using sqlite3_column_type(), then 'switches' to get the  
value using the appropriate sqlite3_column_X() function.


It works fine if, when encountering an SQLITE_INTEGER type, I use  
sqlite_column_int64() to get the data.


I don't know whether or not I'm just "lucky" that the application  
which wrote the database uses 64 bit for all of its integers?  If so,  
what if someone throws a 32-bit integer at me someday?  How can I tell  
whether integer data objects in a table are 32 or 64 bit?  The column  
specifications I get from pragma_table_info() are likewise  
uninformative, saying simply type=INTEGER.


Thanks again,

Jerry Krinock


// Method Implementation (Objective-C for Mac OS X)

- (NSArray*)dicsOfRowsInTable:(NSString*)table {
// Will return nil if fails, empty array if no rows
void* db = [self db] ;
//char* errMsg = NULL ;
int result ;

NSString* statement = [[NSString alloc] initWithFormat:@"SELECT *  
FROM '%@'", table] ;


// Compile the statement into a virtual machine
sqlite3_stmt* preparedStatement ;
result = sqlite3_prepare(db, [statement UTF8String], -1,  
, NULL) ;

[statement release] ;

NSArray* output = nil ;
if (result != SQLITE_OK) {
[self showError:"prepare" from:11 code:result] ;
}
else {
NSMutableArray* rowDics = [[NSMutableArray alloc] init] ;
NSArray* keys = [self keysInTable:table] ;
int nColumns = [keys count] ;
while (result = sqlite3_step(preparedStatement) ==  
SQLITE_ROW) {
NSMutableDictionary* rowDic = [[NSMutableDictionary  
alloc] init] ;


int iColumn  ;
for (iColumn= 0; iColumn<nColumns; iColumn++) {
int type = sqlite3_column_type(preparedStatement,  
iColumn) ;
// The sqlite3_column_type() routine returns datatype  
code

// for the initial data type of the result column.
// The returned value is one of SQLITE_INTEGER,
// SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, or  
SQLITE_NULL


// Initialize to null in case object is not found
const void* pFirstByte = NULL ;
int nBytes = 0 ;
id object = nil ;
long long int intValue ;
const unsigned char* utf8String ;
double doubleValue ;
switch(type) {
case SQLITE_BLOB:
nBytes =  
sqlite3_column_bytes(preparedStatement, iColumn) ;
// "The return value from  
sqlite3_column_blob() for a zero-length
// blob is an arbitrary pointer, possibly  
even a NULL pointer."

// Therefore, we qualify...
if (nBytes > 0) {
pFirstByte =  
sqlite3_column_blob(preparedStatement, iColumn) ;
object = [[NSData alloc]  
initWithBytes:pFirstByte length:nBytes] ;

}
break ;
case SQLITE_INTEGER:
intValue =  
sqlite3_column_int64(preparedStatement, iColumn) ;
object = [NSNumber  
numberWithLongLong:intValue] ;

break ;
case SQLITE_TEXT:
// "Strings returned by sqlite3_column_text()  
and sqlite3_column_text16(),
// even zero-length strings, are always zero  
terminated."

// So, we ignore the length and just convert it
utf8String =  
sqlite3_column_text(preparedStatement, iColumn) ;
object = [NSString stringWithUTF8String: 
(char*)utf8String] ;

break ;
case SQLITE_FLOAT:
doubleValue =  
sqlite3_column_double(preparedStatement, iColumn) ;
object = [NSNumber  
numberWithDouble:doubleValue] ;

break ;
case SQLITE_NULL:
default:
// Just leave object nil, will replace with  
[NSNull null] soon.

;
}

if (object == nil) {
object = [NSNull null] ;
}

[rowDic setObject:object forKey:[keys  
objectAtIndex:iColumn]] ;

}

NSDictionary* rowDicCopy = [rowDic copy] ;
[rowDics addObject:rowDicCopy] ;
[rowDicCopy release] ;
}

output = [rowDics copy] ;
[rowDics release] ;
}

// Finalize the statement (this release

Re: [sqlite] sqlite3_get_table(); How to get all column names in C?

[Jerry Krinock]

On 2008 Jan, 02, at 15:52, Jay Sprenkle wrote:


Did you try to query the table 'sqlite_master'? You can get the schema
for any table by referencing the query results..


Thank you, Jay.  I kept getting syntax errors when I tried that;  
apparently I don't know how to query the sqlite_master.  But while  
thinking about it I realized that pragma SQL can be executed by the C  
API.  So I did this, and it worked.


Jerry

(It's Objective-C code but anyone who's interested should be able to  
get the idea.)


- (NSArray*)allColumnNamesInTableNamed:(NSString*)tableName {
// Will return nil if fails, empty array if no columns
void* db = [self db] ;  // database, a class instance variable
char* errMsg = NULL ;
int result ;

NSString* statement ;
statement = [[NSString alloc] initWithFormat:@"pragma  
table_info(%@)", tableName] ;

char** results ;
int nRows ;
int nColumns ;
result = sqlite3_get_table(
   db,/* An open database */
   [statement UTF8String], /* SQL to be  
executed */
   ,  /* Result is in char *[]  
that this points to */
   ,/* Number of result rows  
written here */
   , /* Number of result columns  
written here */

   /* Error msg written here */
) ;

[statement release] ;

NSMutableArray* columnNames = nil ;
if (!(result == SQLITE_OK)) {
// Invoke the error handler for this class
[self showError:errMsg from:16 code:result] ;
sqlite3_free(errMsg) ;
}
else {
int j ;
for (j=0; j

[sqlite] sqlite3_get_table(); How to get all column names in C?

[Jerry Krinock]

Is there any way to get a list of all column names in a table via the  
C API, which works even when there are no data rows in the table?


The example given for sqlite3_get_table() indicates that the column  
names are returned as the "zeroth" row.  Indeed, it states:


"In general, the number of values inserted into azResult will be  
((*nrow) + 1)*(*ncolumn)."


However, I when the table has no data rows, the returned ncolumn=0, so  
the expression evaluates to 0 and indeed I get 0 values.


Thanks,

Jerry Krinock

(Using sqlite 3.4.0, as shipped in Mac OS X version 10.5)

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Compiling a Library or Mac OS X using Xcode

[Jerry Krinock]

Apple is currently shipping SQLite 3.1.3 in Mac OS 10.4, but SQLite 3.3.5 is
much better.  Anybody with a Mac and Developer Tools installed can build a
the latest version of SQLite as a library in 5 minutes.

The hard part was getting my project to use it instead of
/usr/lib/libsqlite3.dylib (version 3.1.3) which comes with Mac OS 10.4.
According to some advice I got from Apple DTS, to "insure" that your app to
uses the version in its package instead of a dylib installed on the system
using the same name and symbol set, you should build and ship it as a
*static* library.

The following builds a static library of sqlite's C API from the latest
source code, which you can then use in other Xcode projects.  (It does not
build the command-line tool.)

*  Activate Xcode.  I have Xcode 2.2.1.
*  menu File > New Project > Static Library > BSD Static Library.  Name the
project "sqlite3".  (Xcode will add the prefix "lib" and the extension ".a"
to the product).
*  Browse to http://sqlite.org/download.html#cvs
*  Download the link named something like "sqlite-source-x_x_x.zip" which is
described as the "pure C code".
*  You will get a folder named something like "sqlite-source-X_X_X" which
contains .c, .h and one .def files.
*  Move this folder into the project folder of your new Xcode project.
*  In Xcode project window, select the "sqlite3" group at the top and click
menu > Project > Add to project.
*  Navigate to and choose the "sqlite-source-X_X_X" directory in your new
project's folder.
*  In Xcode project window, select the "sqlite3" group at the top, click the
gear pulldown menu and select Add > New Group.
*  Give your new group a name, I suggest: "Frameworks".
*  Select this new group and click menu > Project > Add to project.
*  Navigate to and choose /System/Library/Frameworks/Tcl.framework
*  In Xcode, menu > Project > Set Active Build Configuration > Release
*  In the Groups and Files pane, expand Target and doubleclick on sqlite3 to
show the target settings.  Click the "Build" pane.  Find "Installation
Directory" and change it from "/usr/local/lib" to
"@executable_path/../Libraries"
*  In the Groups and Files pane, expand Products > sqlite > Copy Headers.
Change the Role of sqlite.h to "public".
*  cmd-B to Build.
*  You'll get four warnings about "build" directories not existing, but
ignore these.
*  You should get "Build succeeded".
*  cmd-B again and you should immediately get a clean "Build succeeded"
because the warned-about "build" directories will have been created during
the first build.

In any project which depends directly on this sqlite build,

*  Add the just-built libsqlite3.a to your project
*  In the Target, Build settings, find the "Other Linker Flags" Setting and
enter the Value "-Wl,-search_paths_first".
*  While you're there, check that the path to it has been added in the
Target > Build > Libary Search Paths
*  #include "sqlite3.h" where needed.

In the final product project (which may be the same as above)

*  Add a "Shell Script Build Phase" to copy the libsqlite3.a you have
produced to the product's Contents/Libraries/.  (There may be other ways to
"Copy Files", but I don't trust Xcode.  I like my shell scripts because I
know what they're going to do.)
*  To verify that everything worked, log the value of the global char*
variable sqlite3_version.

The Tcl framework which I used is symlinked to the latest version of Tcl,
which is 8.4 in Mac OS 10.4.  The product of the above procedure seems to
work in Mac OS 10.3 too; at least, the library loads and responds to
sqlite_version() when the app runs.  I have not done any real testing in
10.3 yet.

Re: [sqlite] Advice on compiling 3.5.5 for Mac OS X?

[Jerry Krinock]

on 06/05/21 17:05, Kon Lovett at [EMAIL PROTECTED] wrote:

> An XCode project for SQlite 3 would be nice but I don't have one.

Hey, it was easy.  Summary: Download the "pure C" source code, create a new
Xcode "BSD Dynamic Library" project, add the source code, add Tcl.Framework
from /System, click Build and you're done!!

I'll write back and post step-by-step details after I test it for a few days
and find the bugs ;|

Jerry 

[sqlite] Advice on compiling 3.5.5 for Mac OS X?

[Jerry Krinock]

I would like to compile SQLite 3.3.5 for Mac OS 10.3 and Mac OS 10.4.  I see
from the list archives a couple months ago that some people were using the
TEA compatible distribution for this.

Are there any tricks that I should know about before attempting this?  I
have compiled a couple packages using ./configure and makefile, but this is
not my forte.  Should I try Xcode first?

Sincerely,

Jerry Krinock

P.S. I realize that SQLite 3.1.3 is built into Tiger, but 3.1.3 is missing
many basic features.