[sqlite] Firebird-SQLite conversion tool
You can find it and download for free: http://www.rosa.nl/Bold2/ It makes experimenting with SQLite in Bold/Eco (Borland) easier. kind regards Bert Verhees
RE: [sqlite] 3.0 ship date?
I am working on a Delphi wrapper of the sqlite3 API. Where should I post the file when properly tested ?
RE: [sqlite] Versions 2.8.15 and 3.0.3 available
I noticed that the previous version had some PRAGMA's not implemented, notably DEFAULT_SYNCHRONOUS (and, I think, also SHOW_DATATYPES, or EMPTY_RESULT_CALLBACKS, I am not sure). any changes on this ?
Re: [sqlite] Versions 2.8.15 and 3.0.3 available
CARIOTOGLOU MIKE wrote: I noticed that the previous version had some PRAGMA's not implemented, notably DEFAULT_SYNCHRONOUS (and, I think, also SHOW_DATATYPES, or EMPTY_RESULT_CALLBACKS, I am not sure). any changes on this ? DEFAULT_SYNCHRONOUS no longer exists. The default setting for synchronous is FULL. If you want something different, you have to change it every time you open a new connection. SHOW_DATATYPES no longer exists. The following pragmas are scheduled to be removed in the future: EMPTY_RESULT_CALLBACKS FULL_COLUMN_NAMES SHORT_COLUMN_NAMES COUNT_CHANGES -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
RE: [sqlite] v3 pragmas
Is there any way to know the type of the current statement, i.e.: select, insert, delete, etc? I would like to know it to retrieve the sqlite3_changes() value after the statement has finished to return that value to the user. (almost) what you need : ** Return the number of columns in the result set returned by the compiled ** SQL statement. This routine returns 0 if pStmt is an SQL statement ** that does not return data (for example an UPDATE). */ int sqlite3_column_count(sqlite3_stmt *pStmt);
Re: [sqlite] 3.0 ship date?
CARIOTOGLOU MIKE wrote: I am working on a Delphi wrapper of the sqlite3 API. Where should I post the file when properly tested ? I'm not sure if you can post files to the wikki or not, but that might be one place. I have been thinking of adding some of my delphi source to my webpage, if/when I do I'd be happy to make you a page and host for you, if you'v got no where better. the following exports are missing from sqlite3.def (for windows): sqlite3_version sqlite3_get_auxdata sqlite3_set_auxdata I assume they are missing from my DLL as well, I'll recompile tonight when I get off work and put it out. -Andy
Re: [sqlite] Versions 2.8.15 and 3.0.3 available
D. Richard Hipp, dando pulos de alegria, escreveu : The following pragmas are scheduled to be removed in the future: EMPTY_RESULT_CALLBACKS FULL_COLUMN_NAMES SHORT_COLUMN_NAMES COUNT_CHANGES What would be the equivalent to EMPTY_RESULT_CALLBACKS=TRUE then? I use it to get the column names list to display in a grid, even when the result is empty. I think it is better to the user to see them, even if no results are present. Regards, ~Nuno Lucas
Re: [sqlite] sqluite3.dll
the following exports are missing from sqlite3.def (for windows): sqlite3_version Since this is DATA there are some issues around how to add this. Various tools expect different things. I would prefer to see the sqlite3_libversion function added to the C API. sqlite3_get_auxdata sqlite3_set_auxdata These have been added to sqlite3.def in cvs. e
Re: [sqlite] Versions 2.8.15 and 3.0.3 available
I also use EMPTY_RESULT_CALLBACKS for the same reason Nuno does. And my software also needs FULL_COLUMN_NAMES as well. So, no 3.x for me... =( Roger Reghin. - Original Message - From: Nuno Lucas [EMAIL PROTECTED] To: sqlite [EMAIL PROTECTED] Sent: Monday, July 26, 2004 12:33 PM Subject: Re: [sqlite] Versions 2.8.15 and 3.0.3 available D. Richard Hipp, dando pulos de alegria, escreveu : The following pragmas are scheduled to be removed in the future: EMPTY_RESULT_CALLBACKS FULL_COLUMN_NAMES SHORT_COLUMN_NAMES COUNT_CHANGES What would be the equivalent to EMPTY_RESULT_CALLBACKS=TRUE then? I use it to get the column names list to display in a grid, even when the result is empty. I think it is better to the user to see them, even if no results are present. Regards, ~Nuno Lucas
Re: [sqlite] Versions 2.8.15 and 3.0.3 available
Hello Richard. Please dont remove EMPTY_RESULT_CALLBACKS. This will make it hard to continue with Callback. It is a very nessessary part of Callback. So please dont remove it. Regards Erwin Jabor
Re: [sqlite] like, but not equal?
On Jul 24, 2004, at 1:32 AM, [EMAIL PROTECTED] wrote: sqlite select count(*) from newsgroups where name = 'rec.arts.anime.fandom'; 0 sqlite select count(*) from newsgroups where name like 'rec.arts.anime.fandom'; 1 Figured it out: I was using sqlite3_bind_blob(), but if I change to sqlite3_bind_text() it works right. So I'm guessing that a string on the command line is a text value and can never be strictly equal to a blob value (even if they're byte-for-byte the same), but LIKE coerces the blob into text? -D
Re: [sqlite] RE: Data Types
Drew, Stephen wrote: This seems to be the result of creating a table as: CREATE TABLE temp AS SELECT * FROM another_table Surely the datatypes of the original query (SELECT * FROM another_table) should be used for the new table temp? See http://www.sqlite.org/cvstrac/tktview?tn=521. The problem is fixed in version 3.0. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] RE: Data Types
When a table is created with create table as select ..., version 2.8 does not use any type info from the old table when creating the new one; all it uses are the column names. Version 3 uses the type info but doesn't pick up at least some of the constraints. You can verify all this with pragma table_info(). If you really want to duplicate a table, get the table's sql from sqlite_master, change the table name, and execute that to create your new table; then fill the new table with insert ... select Regards
Re: [sqlite] sqlite3 crashing on OS X
On Sat, 24 Jul 2004 23:13:53 -0500, Gus Mueller [EMAIL PROTECTED] wrote: On Sat, 24 Jul 2004 15:36:50 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: snip Can another OS X user give this a try, see if it crashes for you? Any ideas what's going on? I gave it a try, and it crashes for me as well- This is going to sound a little weird.. but I added this right above where you spawn off your thread: [NSThread detachNewThreadSelector:nil toTarget:nil withObject:nil]; and it no longer crashes. Don't ask me why though. Maybe it has to be warmed up or something :) I've discovered a little bit more about the crash, but I don't know what's causing it really- but here's the stack trace with the crash: #0 0x01010ff8 in balance_shallower at btree.c:3286 #1 0x01010f1c in balance_nonroot at btree.c:3262 #2 0x010114e0 in balance_deeper at btree.c:3398 #3 0x01011570 in balance at btree.c:3414 #4 0x010119bc in sqlite3BtreeInsert at btree.c:3530 #5 0x0104f084 in sqlite3VdbeExec at vdbe.c:3053 #6 0x010522fc in sqlite3_step at vdbeapi.c:159 #7 0x0105db5c in sqlite3_exec at legacy.c:79 #8 0x000e37e8 in execQuery at Test.m:13 #9 0x000e3a3c in -[Test testDatabase] at Test.m:76 #10 0x90a39b74 in forkThreadForFunction #11 0x900246e8 in _pthread_body What's interesting is that balance_nonroot doesn't call balance_shallower- it calls balance, and then balance calls balance_shallower. Here's what the stack trace looks like when you kick off an extra thread right before the [NSThread detachNewThreadSelector:@selector(testDatabase) toTarget:test withObject:nil]; #0 0x01011008 in balance_shallower at btree.c:3295 #1 0x010115c0 in balance at btree.c:3420 #2 0x01010f1c in balance_nonroot at btree.c:3262 #3 0x010114e0 in balance_deeper at btree.c:3398 #4 0x01011570 in balance at btree.c:3414 #5 0x010119bc in sqlite3BtreeInsert at btree.c:3530 #6 0x0104f084 in sqlite3VdbeExec at vdbe.c:3053 #7 0x010522fc in sqlite3_step at vdbeapi.c:159 #8 0x0105db5c in sqlite3_exec at legacy.c:79 #9 0x000e37e8 in execQuery at Test.m:13 #10 0x000e3a3c in -[Test testDatabase] at Test.m:76 #11 0x90a39b74 in forkThreadForFunction #12 0x900246e8 in _pthread_body The stack is correct, and the app doesn't crash. But that obviously a silly hack that doesn't explain or fix the problem. So is the stack getting corrupted? In the debugger, when it crashes at balance_shallower, the value of the pointer to pPage is 0x0. Maybe this will help someone figure out what's going on.. especially since I plan on using sqlite3 on osx as well :) -- -gus
Re: [sqlite] Versions 2.8.15 and 3.0.3 available
Doug Currie, dando pulos de alegria, escreveu : In 3.x column names are available as soon as the query is prepared. See the C API reference at http://www.sqlite.org/capi3ref.html#sqlite3_column_name This wasn't implemented in 3.0.2, right? (I didn't test 3.0.3 yet) I believe I tried that (exactly because I understood that from the docs) but found it didn't work for empty tables. Regards, ~Nuno Lucas
Re: [sqlite] sqlite3 crashing on OS X
On Jul 26, 2004, at 2:13 PM, Gus Mueller wrote: What's interesting is that balance_nonroot doesn't call balance_shallower- it calls balance, and then balance calls balance_shallower. Yeah, it looks like a frame header is getting tromped on and confusing the debugger. I filed it at bugreporter.apple.com--we'll see if they can be convinced it's their fault. (If there's any CoreData lurkers out there, it's radar #3741210..) More evidence it's Apple's problem: it works fine when you use pthread_create() instead of [NSThread detachNewThreadSelector:::] (thanks to Michael Robinette for finding this out). Odd, since NSThread uses pthreads itself. -D
Re: [sqlite] sqlite3 crashing on OS X
On Jul 24, 2004, at 6:36 PM, [EMAIL PROTECTED] wrote: I've been digging through a problem I'm having with spontaneous crashing with sqlite 3.0.3 on OS X 10.3.4, and finally have it down to a small amount of demo code. and here's the code (XCode 1.2 project): http://www.opaque.net/~dave/dbtest.tgz Can another OS X user give this a try, see if it crashes for you? Any ideas what's going on? If you've got a fix for me I'll send you a swank Panic brand t-shirt. If it's something really obvious and stupid I've done, two. :) Thanks, -Dave Hi Dave, I'm using OS X v10.3.4 as well and was able to Build and Run DBTest.app As expected, DBTest.app initialized all the tables for me ( creating, inserting ) Except there's a problem when trying to open test.db in SQLite v2.8.11: %Unable to open database test.db: database disk image is malformed I am able to open test.db in SQLite v3.0.0 but not in earlier versions. So, to answer your question, DBTest.app hasn't crashed. -gohaku
[sqlite] Large rows and performance (storing large files)
Hello all, I raised the questions below a couple of weeks ago, but didn't receive an answer, so I'd like to politely ask again :-) If I go the easy way and increase the MAX_BYTES_PER_ROW to 4294967296 (for example), will it have any performance effect on the normal sized rows? The idea behind this is that I store all binary stuff into a separate table and access it only when needed. This will make the database file huge probably, but will it affect the access time for other tables in this file? Thank you for your time! Dennis // Software for animal shelters http://www.smartpethealth.com //
[sqlite] where is config.h?
Hello all, I just downloaded the whole repository and tried to recompile (v3), but in sqliteInt.h there's a header include config.h and this file is not present anywhere :-( Am I missing something? I was able to compile fine before... TIA Dennis // Software for animal shelters http://www.smartpethealth.com //
Re: [sqlite] Large rows and performance (storing large files)
Dennis Volodomanov wrote: If I go the easy way and increase the MAX_BYTES_PER_ROW to 4294967296 (for example), will it have any performance effect on the normal sized rows? No. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Versions 2.8.15 and 3.0.3 available
Monday, July 26, 2004, 5:46:48 PM, Nuno Lucas wrote: Doug Currie, dando pulos de alegria, escreveu : In 3.x column names are available as soon as the query is prepared. See the C API reference at http://www.sqlite.org/capi3ref.html#sqlite3_column_name This wasn't implemented in 3.0.2, right? (I didn't test 3.0.3 yet) I believe I tried that (exactly because I understood that from the docs) but found it didn't work for empty tables. Please try it again; I just tried it with an sqlite3 I built on June 24, and it is working... Lua 5.0.2 Copyright (C) 1994-2004 Tecgraf, PUC-Rio requiresqlite3 db=sqlite.openfoo.db db:execcreate table baz (a,b,c); vm=db:compileselect * from baz; t=vm:get_names() for i,n in pairs( t) do print (i,n) end 1 a 2 b 3 c n 3 e
RE: [sqlite] where is config.h?
Sorry about this one - I forgot that I have to build the source, as opposed to getting the one already pre-formatted for Windows. :-/ -Original Message- From: Dennis Volodomanov Sent: Tuesday, 27 July 2004 11:09 AM To: [EMAIL PROTECTED] Subject: [sqlite] where is config.h? Hello all, I just downloaded the whole repository and tried to recompile (v3), but in sqliteInt.h there's a header include config.h and this file is not present anywhere :-( Am I missing something? I was able to compile fine before... TIA Dennis // Software for animal shelters http://www.smartpethealth.com //
Re: [sqlite] where is config.h?
Dennis, Run 'configure' and then 'make'. The file config,h will be created automatically. Regards, -- Tito On 26 jul 2004, at 21:09, Dennis Volodomanov wrote: Hello all, I just downloaded the whole repository and tried to recompile (v3), but in sqliteInt.h there's a header include config.h and this file is not present anywhere :-( Am I missing something? I was able to compile fine before... TIA Dennis // Software for animal shelters http://www.smartpethealth.com // smime.p7s Description: S/MIME cryptographic signature
Re: [sqlite] where is config.h?
Dennis Volodomanov wrote: Hello all, I just downloaded the whole repository and tried to recompile (v3), but in sqliteInt.h there's a header include config.h and this file is not present anywhere :-( Am I missing something? I was able to compile fine before... config.h is generated by the makefile. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] where is config.h?
On Monday 26 July 2004 6:09 pm, Dennis Volodomanov wrote: Hello all, I just downloaded the whole repository and tried to recompile (v3), but in sqliteInt.h there's a header include config.h and this file is not present anywhere :-( Am I missing something? I was able to compile fine before... TIA Dennis It gets created during the .../sqlite/configure process. Scott // Software for animal shelters http://www.smartpethealth.com // -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.95-default x86_64
Re: Re: [sqlite] C callback that returns numeric data without conversion from text?
Thanks for the clarification. I'll need to study the docs on the three
part method and slowly figure it out. A working example sure would
be nice! -- Al
On Mon, 26 Jul 2004 11:21:08 +0100 (BST), Christian Smith
[EMAIL PROTECTED] wrote:
Typed data retrieval is not supported by the callback method of execution.
You must use the prepare/step/finalize execution method.
Check out:
http://www.sqlite.org/capi3.html
in particular, the sqlite3_step and related functions in section 2.2
Executing SQL statements. This section gives the functions to retrieve
typed data from a row.
Sorry, no sample code as I've yet to dive headlong into v3 yet. My work
for the moment is v2 only.
Christian
On Sun, 25 Jul 2004, Al Danial wrote:
Since SQLite v2 was 'typeless', one had to call atoi() and atof() on terms
of the array *azArg to convert the text strings returned by a query into
integers and doubles.
As I understand it SQLite v3 stores integers and doubles in their native
binary format so one should be able to get at the numeric data without
text string conversions via atoi()/atof(). Does anyone have C code that
demonstrates how this could be done?
Here's a sample database and corresponding query + callback I'm currently
using:
create table people ( name text, age integer , lat float, lon float );
insert into people values ( 'Alice' , 43 , 1.1 , -3.4e-3 );
insert into people values ( 'Bob' , 28 , 5.5 , -3.1e+3 );
insert into people values ( 'Cindy' , 21 , 8.8 , 3.2e+5 );
The query:
rc = sqlite3_exec_printf(sql_db,
select * from people order by age asc
, a1_i1_d2_cb,
sql_data,
zErrMsg);
The callback:
int a1_i1_d2_cb(void *pArg, int nFields, char **azArg, char **azCol) {
/* return array of [string, integer, double, double] */
callback_data *p = (callback_data*) pArg;
if (!azArg)
return 0;
strncpy(p-a[p-nRows][0], azArg[0], SQL_STR_SIZE);
p-i[p-nRows][0] = atoi(azArg[1]);
p-x[p-nRows][0] = atof(azArg[2]);
p-x[p-nRows][1] = atof(azArg[3]);
++(p-row_index);
++(p-nRows);
return 0;
}
The callback variable 'sql_data' has type 'callback_data' defined like this:
#define SQL_BLOCK_SIZE 1000
#define SQL_STR_SIZE200
#define SQL_MAX_COLUMNS 20
typedef struct {
sqlite3 *db;/* database handle */
FILE *out;/* output file handle*/
charnullvalue[SQL_MAX_COLUMNS]; /* string to display for NULL's */
charzDbFilename[SQL_STR_SIZE]; /* db filename */
int nRows; /* size of a[]/i[]/x[] with valid data */
int row_index; /* pointer to current row within table */
chara[SQL_BLOCK_SIZE][SQL_MAX_COLUMNS][SQL_STR_SIZE];/* string */
int i[SQL_BLOCK_SIZE][SQL_MAX_COLUMNS]; /* integer */
double x[SQL_BLOCK_SIZE][SQL_MAX_COLUMNS]; /* double */
} callback_data;
My questions:
How can I populate sql_data.i[] and sql_data.x[] without calling atoi()
and atof() on terms of azArg[]?
Is there example C code out there that demonstrates the technique?
-- Al
--
/\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
[sqlite] Compile Help - conflicting types for `sqlite3UnlinkAndDeleteIndex'
I'm trying to compile the latest cvs version of 3.0 on my AMD64 system and I'm running into this problem. gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I../sqlite/src -DNDEBUG -c ../sqlite/src/btree.c -fPIC -DPIC -o .libs/btree.o gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I../sqlite/src -DNDEBUG -c ../sqlite/src/btree.c -o btree.o /dev/null 21 ./libtool --mode=compile gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I../sqlite/src -DNDEBUG -c ../sqlite/src/build.c gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I../sqlite/src -DNDEBUG -c ../sqlite/src/build.c -fPIC -DPIC -o .libs/build.o ../sqlite/src/build.c:219: error: conflicting types for `sqlite3UnlinkAndDeleteIndex' ../sqlite/src/sqliteInt.h:1278: error: previous declaration of `sqlite3UnlinkAndDeleteIndex' make: *** [build.lo] Error 1 [EMAIL PROTECTED]:~/bld I'm pretty clueless when it comes to compiles that bomb out, can anyone point me in the right direction to fix this? Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.95-default x86_64
