RE: [sqlite] Limit statement size?
I can see now what the trouble is if I do the SELECT without the INSERT OR REPLACE (shortened): select t1.PATIENT_ID, g2.ENTRY_ID, g2.READ_CODE, g2.TERM_TEXT, g2.START_DATE, g2.ADDED_DATE, g2.NUMERIC_VALUE, g3.ENTRY_ID, g3.READ_CODE, g3.TERM_TEXT, g3.START_DATE, g3.ADDED_DATE, g3.NUMERIC_VALUE, g4.ENTRY_ID, g4.READ_CODE, g4.TERM_TEXT, g4.START_DATE, g4.ADDED_DATE, g4.NUMERIC_VALUE from A3TestB67_J t1, GROUP_2 g2, GROUP_3 g3, GROUP_4 g4 where t1.PATIENT_ID = g2.PID and t1.PATIENT_ID = g3.PID and t1.PATIENT_ID = g4.PID I only get the rows that have entries in all groups. So, this is like an inner join and I need a left join. Have tried this, but it didn't alter the table, although there was no error: INSERT OR REPLACE INTO A3TestB67_J(PATIENT_ID, ENTRY_ID_E1, READ_CODE_E1, TERM_TEXT_E1, START_DATE_E1, ADDED_DATE_E1, NUMERIC_VALUE_E1, ENTRY_ID_E2, READ_CODE_E2, TERM_TEXT_E2, START_DATE_E2, ADDED_DATE_E2, NUMERIC_VALUE_E2, ENTRY_ID_E3, READ_CODE_E3, TERM_TEXT_E3, START_DATE_E3, ADDED_DATE_E3, NUMERIC_VALUE_E3, ENTRY_ID_E4, READ_CODE_E4, TERM_TEXT_E4, START_DATE_E4, ADDED_DATE_E4, NUMERIC_VALUE_E4) select t1.PATIENT_ID, g2.ENTRY_ID, g2.READ_CODE, g2.TERM_TEXT, g2.START_DATE, g2.ADDED_DATE, g2.NUMERIC_VALUE, g3.ENTRY_ID, g3.READ_CODE, g3.TERM_TEXT, g3.START_DATE, g3.ADDED_DATE, g3.NUMERIC_VALUE, g4.ENTRY_ID, g4.READ_CODE, g4.TERM_TEXT, g4.START_DATE, g4.ADDED_DATE, g4.NUMERIC_VALUE from A3TestB67_J t1 left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID) left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID) left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID) My old method is actually quite fast and not sure if I can improve on it. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 30 January 2007 05:53 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? --- RB Smissaert <[EMAIL PROTECTED]> wrote: > Thanks, that is how I understood it to be. > I must be overlooking something simple here. Check your SELECT sub-statement within the REPLACE statement to see what rows it returns. .header on .mode tabs create table t1(id primary key, e2_a, e2_b, e3_a, e3_b); insert into t1 values(3, 30,31, 23,230); insert into t1 values(4, 40,41, 24,240); insert into t1 values(5, 50,51, 25,250); create table e2(id primary key, a, b); insert into e2 values(3, 300, 310); insert into e2 values(4, 400, 410); insert into e2 values(5, 500, 510); create table e3(id primary key, a, b); insert into e3 values(3, 23.1, 230.1); insert into e3 values(4, 24.1, 240.1); insert into e3 values(5, 25.1, 250.1); select * from t1 order by id; replace into t1(id, e2_a, e2_b, e3_a, e3_b) select t1.id, e2.a, e2.b, e3.a, e3.b from t1, e2, e3 where t1.id = e2.id and t1.id = e3.id; select * from t1 order by id; id e2_ae2_be3_ae3_b 3 30 31 23 230 4 40 41 24 240 5 50 51 25 250 id e2_ae2_be3_ae3_b 3 300 310 23.1230.1 4 400 410 24.1240.1 5 500 510 25.1250.1 > > RBS > > -Original Message- > From: Gerry Snyder [mailto:[EMAIL PROTECTED] > Sent: 29 January 2007 23:52 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Limit statement size? > > RB Smissaert wrote: > > Had a go at this, but sofar I haven't been able yet to get it to work. > > I get no error, but A3Test115_J remains just at it is. > > I couldn't find much information about INSERT OR REPLACE in the SQLite > > documentation. What exactly should it do? > > It will try to do an INSERT. If the PATIENT_ID field is already in the > file, it will delete the old conflicting entry before inserting. > > The best write-up is at: > > http://sqlite.org/lang_conflict.html > > > HTH, > > Gerry No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started. http://mobile.yahoo.com/mail - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Limit statement size?
> Your INSERT OR REPLACE statement is in error. Yes, you are right. In the end it all came down to a simple mistake on my side. Sorry if I have wasted anybody's time. Got this all working now and will now see if it is faster than my old method. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 03:49 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? Your INSERT OR REPLACE statement is in error. You have fewer columns in your SELECT clause than are specified in your INSERT column name list. You should have seen an error like this in SQLite version 3.3.12: SQL error: X values for Y columns Assuming PATIENT_ID is the sole unique key for A3TestB67_J and your SQL column counts match, the REPLACE should work. --- RB Smissaert <[EMAIL PROTECTED]> wrote: > Have tried this, but it didn't alter the table, although there was no error: > > INSERT OR REPLACE INTO > A3TestB67_J(PATIENT_ID, > ENTRY_ID_E1, > READ_CODE_E1, > TERM_TEXT_E1, > START_DATE_E1, > ADDED_DATE_E1, > NUMERIC_VALUE_E1, > ENTRY_ID_E2, > READ_CODE_E2, > TERM_TEXT_E2, > START_DATE_E2, > ADDED_DATE_E2, > NUMERIC_VALUE_E2, > ENTRY_ID_E3, > READ_CODE_E3, > TERM_TEXT_E3, > START_DATE_E3, > ADDED_DATE_E3, > NUMERIC_VALUE_E3, > ENTRY_ID_E4, > READ_CODE_E4, > TERM_TEXT_E4, > START_DATE_E4, > ADDED_DATE_E4, > NUMERIC_VALUE_E4) > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1 > left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID) > left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID) > left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID) No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started. http://mobile.yahoo.com/mail - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Limit statement size?
Can confirm now that the method with INSERT OR REPLACE is faster indeed. My timings tell me it is about twice as fast and that is worth it as that could be up to a few seconds. I now wonder if there is an even faster way avoiding all the GROUP tables and doing it all in one statement. Thanks again for the assistance. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 00:51 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? --- RB Smissaert <[EMAIL PROTECTED]> wrote: > I can see now what the trouble is if I do the SELECT without the INSERT OR > REPLACE (shortened): Can't suggest anything without seeing the schema for all the tables involved and any unique indexes related to those tables. It should work. Perhaps you're not specifying some columns related to a unique index on the table being updated. Maybe there's a bug in REPLACE? Specifically, what does ".schema A3TestB67_J" return? I suspect this REPLACE technique with its reduced number of database lookups ought to be much faster than all those subselects you are using now for every column, but if you're happy with the timings with the old way and it works, there's no point changing it. I am curious with it not updating the table, though. > > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1, > GROUP_2 g2, > GROUP_3 g3, > GROUP_4 g4 > where > t1.PATIENT_ID = g2.PID and > t1.PATIENT_ID = g3.PID and > t1.PATIENT_ID = g4.PID > > I only get the rows that have entries in all groups. So, this is like an > inner join and I need a left join. > Have tried this, but it didn't alter the table, although there was no error: > > INSERT OR REPLACE INTO > A3TestB67_J(PATIENT_ID, > ENTRY_ID_E1, > READ_CODE_E1, > TERM_TEXT_E1, > START_DATE_E1, > ADDED_DATE_E1, > NUMERIC_VALUE_E1, > ENTRY_ID_E2, > READ_CODE_E2, > TERM_TEXT_E2, > START_DATE_E2, > ADDED_DATE_E2, > NUMERIC_VALUE_E2, > ENTRY_ID_E3, > READ_CODE_E3, > TERM_TEXT_E3, > START_DATE_E3, > ADDED_DATE_E3, > NUMERIC_VALUE_E3, > ENTRY_ID_E4, > READ_CODE_E4, > TERM_TEXT_E4, > START_DATE_E4, > ADDED_DATE_E4, > NUMERIC_VALUE_E4) > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1 > left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID) > left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID) > left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID) > > My old method is actually quite fast and not sure if I can improve on it. > > RBS > > > > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: 30 January 2007 05:53 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Limit statement size? > > --- RB Smissaert <[EMAIL PROTECTED]> wrote: > > Thanks, that is how I understood it to be. > > I must be overlooking something simple here. > > Check your SELECT sub-statement within the REPLACE statement to see > what rows it returns. > > .header on > .mode tabs > > create table t1(id primary key, e2_a, e2_b, e3_a, e3_b); > insert into t1 values(3, 30,31, 23,230); > insert into t1 values(4, 40,41, 24,240); > insert into t1 values(5, 50,51, 25,250); > > create table e2(id primary key, a, b); > insert into e2 values(3, 300, 310); > insert into e2 values(4, 400, 410); > insert into e2 values(5, 500, 510); > > create table e3(id primary key, a, b); > insert into e3 values(3, 23.1, 230.1); > insert into e3 values(4, 24.1, 240.1); > insert into e3 values(5, 25.1, 250.1); > > select * from t1 order by id; > > replace into t1(id, e2_a, e2_b, e3_a, e3_b) > select t1.id, e2.a, e2.b, e3.a, e3.b > from t1, e2, e3 > where t1.id = e2.id and t1.id = e3.id; > > select * from t1 order by id; > > id e2_ae2_be3_ae3_b > 3 30 31 23 230 > 4 40 41 24 240 > 5 50 51 25 250 > > id e2_ae2_be3_ae3_b > 3 300 310 23.1230.1 > 4 400 410 24.1240.1 > 5
RE: [sqlite] Limit statement size?
Actually make that about 5 to 6 times as fast. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 17:39 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? Can confirm now that the method with INSERT OR REPLACE is faster indeed. My timings tell me it is about twice as fast and that is worth it as that could be up to a few seconds. I now wonder if there is an even faster way avoiding all the GROUP tables and doing it all in one statement. Thanks again for the assistance. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 00:51 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? --- RB Smissaert <[EMAIL PROTECTED]> wrote: > I can see now what the trouble is if I do the SELECT without the INSERT OR > REPLACE (shortened): Can't suggest anything without seeing the schema for all the tables involved and any unique indexes related to those tables. It should work. Perhaps you're not specifying some columns related to a unique index on the table being updated. Maybe there's a bug in REPLACE? Specifically, what does ".schema A3TestB67_J" return? I suspect this REPLACE technique with its reduced number of database lookups ought to be much faster than all those subselects you are using now for every column, but if you're happy with the timings with the old way and it works, there's no point changing it. I am curious with it not updating the table, though. > > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1, > GROUP_2 g2, > GROUP_3 g3, > GROUP_4 g4 > where > t1.PATIENT_ID = g2.PID and > t1.PATIENT_ID = g3.PID and > t1.PATIENT_ID = g4.PID > > I only get the rows that have entries in all groups. So, this is like an > inner join and I need a left join. > Have tried this, but it didn't alter the table, although there was no error: > > INSERT OR REPLACE INTO > A3TestB67_J(PATIENT_ID, > ENTRY_ID_E1, > READ_CODE_E1, > TERM_TEXT_E1, > START_DATE_E1, > ADDED_DATE_E1, > NUMERIC_VALUE_E1, > ENTRY_ID_E2, > READ_CODE_E2, > TERM_TEXT_E2, > START_DATE_E2, > ADDED_DATE_E2, > NUMERIC_VALUE_E2, > ENTRY_ID_E3, > READ_CODE_E3, > TERM_TEXT_E3, > START_DATE_E3, > ADDED_DATE_E3, > NUMERIC_VALUE_E3, > ENTRY_ID_E4, > READ_CODE_E4, > TERM_TEXT_E4, > START_DATE_E4, > ADDED_DATE_E4, > NUMERIC_VALUE_E4) > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1 > left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID) > left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID) > left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID) > > My old method is actually quite fast and not sure if I can improve on it. > > RBS > > > > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: 30 January 2007 05:53 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Limit statement size? > > --- RB Smissaert <[EMAIL PROTECTED]> wrote: > > Thanks, that is how I understood it to be. > > I must be overlooking something simple here. > > Check your SELECT sub-statement within the REPLACE statement to see > what rows it returns. > > .header on > .mode tabs > > create table t1(id primary key, e2_a, e2_b, e3_a, e3_b); > insert into t1 values(3, 30,31, 23,230); > insert into t1 values(4, 40,41, 24,240); > insert into t1 values(5, 50,51, 25,250); > > create table e2(id primary key, a, b); > insert into e2 values(3, 300, 310); > insert into e2 values(4, 400, 410); > insert into e2 values(5, 500, 510); > > create table e3(id primary key, a, b); > insert into e3 values(3, 23.1, 230.1); > insert into e3 values(4, 24.1, 240.1); > insert into e3 values(5, 25.1, 250.1); > > select * from t1 order by id; > > replace into t1(id, e2_a, e2_b, e3_a, e3_b) > select t1.id, e2.a, e2.b, e3.a, e3.b > from t1, e2, e3 > where t1.id = e2.id and t1.id = e3.id; > > select * from t1 order by id; > > id e2_ae2_be3_ae3_b > 3 30 31 23 2
RE: [sqlite] Limit statement size?
There is one important problem though that I just discovered. Just found out that the maximum number of tables in a join is 32! So, with my base table that is only 31 to add. This trouble doesn't of course apply to the old UPDATE method. So, I think after all I need the old way of doing it or what I could do is see how many tables are to be added and pick the method accordingly. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 17:54 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? Actually make that about 5 to 6 times as fast. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 17:39 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? Can confirm now that the method with INSERT OR REPLACE is faster indeed. My timings tell me it is about twice as fast and that is worth it as that could be up to a few seconds. I now wonder if there is an even faster way avoiding all the GROUP tables and doing it all in one statement. Thanks again for the assistance. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 00:51 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? --- RB Smissaert <[EMAIL PROTECTED]> wrote: > I can see now what the trouble is if I do the SELECT without the INSERT OR > REPLACE (shortened): Can't suggest anything without seeing the schema for all the tables involved and any unique indexes related to those tables. It should work. Perhaps you're not specifying some columns related to a unique index on the table being updated. Maybe there's a bug in REPLACE? Specifically, what does ".schema A3TestB67_J" return? I suspect this REPLACE technique with its reduced number of database lookups ought to be much faster than all those subselects you are using now for every column, but if you're happy with the timings with the old way and it works, there's no point changing it. I am curious with it not updating the table, though. > > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1, > GROUP_2 g2, > GROUP_3 g3, > GROUP_4 g4 > where > t1.PATIENT_ID = g2.PID and > t1.PATIENT_ID = g3.PID and > t1.PATIENT_ID = g4.PID > > I only get the rows that have entries in all groups. So, this is like an > inner join and I need a left join. > Have tried this, but it didn't alter the table, although there was no error: > > INSERT OR REPLACE INTO > A3TestB67_J(PATIENT_ID, > ENTRY_ID_E1, > READ_CODE_E1, > TERM_TEXT_E1, > START_DATE_E1, > ADDED_DATE_E1, > NUMERIC_VALUE_E1, > ENTRY_ID_E2, > READ_CODE_E2, > TERM_TEXT_E2, > START_DATE_E2, > ADDED_DATE_E2, > NUMERIC_VALUE_E2, > ENTRY_ID_E3, > READ_CODE_E3, > TERM_TEXT_E3, > START_DATE_E3, > ADDED_DATE_E3, > NUMERIC_VALUE_E3, > ENTRY_ID_E4, > READ_CODE_E4, > TERM_TEXT_E4, > START_DATE_E4, > ADDED_DATE_E4, > NUMERIC_VALUE_E4) > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1 > left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID) > left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID) > left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID) > > My old method is actually quite fast and not sure if I can improve on it. > > RBS > > > > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: 30 January 2007 05:53 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Limit statement size? > > --- RB Smissaert <[EMAIL PROTECTED]> wrote: > > Thanks, that is how I understood it to be. > > I must be overlooking something simple here. > > Check your SELECT sub-statement within the REPLACE statement to see > what rows it returns. > > .header on > .mode tabs > > create table t1(id primary key, e2_a, e2_b, e3_a, e3_b); > insert into t1 values(3, 30,31, 23,230); > insert into t1 values(4, 40,41, 24,240); > insert into t1 values(5, 50,51, 25,250); > > create table e2(id primary key, a, b); > insert into e2 values(3, 300, 310); > insert into e2 values(4, 400
RE: [sqlite] Limit statement size?
I code in VB and I think I stay out of altering the C source code.
> just do a single REPLACE command with a SELECT on 2 or more subqueries on
> sub-sets of the tables (more efficient).
Will try that one.
> This stands to reason since you're only doing a single lookup per
> sub-table instead of the 6 lookups per sub-table you did with the
> UPDATE command.
Maybe, but the speed is actually less as my figure of 5 to 6 times faster
was faulty due to me not noticing the error caused by the > 32 table joins.
I would say it is about 2 to 3 times faster. Still worth it, plus a nicer
looking SQL.
I wonder what the reason was to limit the number of table joins to 32.
RBS
-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED]
Sent: 01 February 2007 00:42
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Limit statement size?
--- RB Smissaert <[EMAIL PROTECTED]> wrote:
> There is one important problem though that I just discovered.
> Just found out that the maximum number of tables in a join is 32!
> So, with my base table that is only 31 to add.
Let's do some grepping...
#define BMS (sizeof(Bitmask)*8)
...
/* The number of tables in the FROM clause is limited by the number of
** bits in a Bitmask
*/
if( pTabList->nSrc>BMS ){
sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS);
return 0;
}
...
You could try changing src/sqliteInt.h:
-typedef unsigned int Bitmask;
+typedef u64 Bitmask;
and then recompiling sqlite. If all goes well, you should be able to
join up to 64 tables. Never tried it. It might work, or might not.
Alternatively, you can either perform 2 consecutive REPLACE commands
with half the tables in each update (less efficient), or just do a single
REPLACE command with a SELECT on 2 or more subqueries on sub-sets of
the tables (more efficient).
> Actually make that about 5 to 6 times as fast.
This stands to reason since you're only doing a single lookup per
sub-table instead of the 6 lookups per sub-table you did with the
UPDATE command.
Never Miss an Email
Stay connected with Yahoo! Mail on your mobile. Get started!
http://mobile.yahoo.com/services?promote=mail
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] How do I know sqlite_get_table is finished
Using the VB wrapper dll SQLite3VB.dll from Todd Tanner's site: http://www.tannertech.net/sqlite3vb/index.htm In one particular procedure I had a serious problem when doing a call to sqlite_get_table, causing Excel to crash. It took me a long time to pinpoint the trouble as VBA debugging methods didn't help here. Eventually it appeared that the trouble was calling sqlite3_close too soon after sqlite_get_table. Not sure if this makes sense, but after moving sqlite3_close some lines down in that procedure the problem seems to be solved, so I take it the connection was closed while SQLite was still fetching data, causing the error and the Excel crash. So what is the best way to determine if sqlite_get_table is finished? Running number_of_rows_from_last_call in a loop might be something, but maybe there is something better. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How do I know sqlite_get_table is finished
Thanks, yes, I somehow didn't think my explanation made sense and in fact I just had another Excel crash, caused by the same call to sqlite_get_table. I just can't understand why this is happening. There is a valid connection, there is a valid SQL, the DB file is there and working otherwise fine, etc. The wrapper works otherwise 100% perfect and I only have the problem in this particular procedure. I guess there must be a VBA bug then, but I just can't find it. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 01 February 2007 22:56 To: sqlite-users@sqlite.org Subject: Re: [sqlite] How do I know sqlite_get_table is finished sqlite_get_table does not terminate unless there is an error or it has retrieved all the records you asked for. Something else must have been causing the error, or the wrapper you are using is not implementing the function call correctly. -- Eric Pankoke Founder / Lead Developer Point Of Light Software http://www.polsoftware.com/ -- Original message ------ From: "RB Smissaert" <[EMAIL PROTECTED]> > Using the VB wrapper dll SQLite3VB.dll from Todd Tanner's site: > http://www.tannertech.net/sqlite3vb/index.htm > > In one particular procedure I had a serious problem when doing a call to > sqlite_get_table, causing Excel to crash. It took me a long time to pinpoint > the trouble as VBA debugging methods didn't help here. Eventually it > appeared that the trouble was calling sqlite3_close too soon after > sqlite_get_table. > Not sure if this makes sense, but after moving sqlite3_close some lines down > in that procedure the problem seems to be solved, so I take it the > connection was closed while SQLite was still fetching data, causing the > error and the Excel crash. > > So what is the best way to determine if sqlite_get_table is finished? > Running number_of_rows_from_last_call in a loop might be something, but > maybe there is something better. > > RBS > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How do I know sqlite_get_table is finished
Good thought, but 1. I set the connection 2. get the array 3. dump it in the sheet 4. and then close the connection. The crash happens at number 2. I am sure I must be overlooking something simple here. The trouble is that this error is not consistent. RBS -Original Message- From: Guy Hachlili [mailto:[EMAIL PROTECTED] Sent: 01 February 2007 23:50 To: sqlite-users@sqlite.org Subject: RE: [sqlite] How do I know sqlite_get_table is finished Well... At 23:17 2/1/2007 +, you wrote: >Thanks, yes, I somehow didn't think my explanation made sense and in fact I >just had another Excel crash, caused by the same call to sqlite_get_table. > >I just can't understand why this is happening. >There is a valid connection, there is a valid SQL, the DB file is there and >working otherwise fine, etc. > >The wrapper works otherwise 100% perfect and I only have the problem in this >particular procedure. I guess there must be a VBA bug then, but I just can't >find it. Is it possible that you are using the array returned by the function after you close the database, and that the VB wrapper frees the data when the database is closed? If that is the case, you will probably get a crash as you are accessing a memory that was already freed. Guy - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How do I know sqlite_get_table is finished
Optional bNoNewConnection As Boolean, _ Optional bStatement As Boolean) As Boolean Dim strSQL As String Dim lDBHandle As Long Dim lRows As Long Dim strError As String Dim arr On Error GoTo ERROROUT strSQL = "SELECT (SELECT ROWID FROM '" & strTable & "' limit 1) IS NOT NULL" If bNoNewConnection = False Then lDBHandle = OpenDB(strDB) End If If bStatement Then ShowStatement strSQL, , , 2, True, True End If arr = GetFromDB(strSQL, lRows, strError, lDBHandle, strDB) If Not arr(1, 0) = 1 Then SQLiteTableIsEmpty = True End If If bNoNewConnection = False Then OpenDB strDB, True End If Exit Function ERROROUT: If bNoNewConnection = False Then OpenDB strDB, True End If SQLiteTableIsEmpty = True End Function The function ArrayToSheet has nil to do with SQLite and I left that out. RBS -Original Message- From: Eric Pankoke [mailto:[EMAIL PROTECTED] Sent: 02 February 2007 02:04 To: sqlite-users@sqlite.org Subject: RE: [sqlite] How do I know sqlite_get_table is finished Is it possible for you to post the "offending" block of VBA code? Even seeing your list of steps, it might be easier to help if we can view the actual syntax. Eric Pankoke Founder Point Of Light Software http://www.polsoftware.com/ -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Thursday, February 01, 2007 7:06 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] How do I know sqlite_get_table is finished Good thought, but 1. I set the connection 2. get the array 3. dump it in the sheet 4. and then close the connection. The crash happens at number 2. I am sure I must be overlooking something simple here. The trouble is that this error is not consistent. RBS -Original Message- From: Guy Hachlili [mailto:[EMAIL PROTECTED] Sent: 01 February 2007 23:50 To: sqlite-users@sqlite.org Subject: RE: [sqlite] How do I know sqlite_get_table is finished Well... At 23:17 2/1/2007 +, you wrote: >Thanks, yes, I somehow didn't think my explanation made sense and in fact I >just had another Excel crash, caused by the same call to sqlite_get_table. > >I just can't understand why this is happening. >There is a valid connection, there is a valid SQL, the DB file is there and >working otherwise fine, etc. > >The wrapper works otherwise 100% perfect and I only have the problem in this >particular procedure. I guess there must be a VBA bug then, but I just can't >find it. Is it possible that you are using the array returned by the function after you close the database, and that the VB wrapper frees the data when the database is closed? If that is the case, you will probably get a crash as you are accessing a memory that was already freed. Guy - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How do I know sqlite_get_table is finished
lReturnedRows is one of the function arguments, so that is fine. I have made some progress though and that is that this problem only occurs with this particular table, called SQL. It is a table that logs all the SQL statements that run in my app. When I instead make this for example sqlite_master there is no problem ever. No idea though why this table would cause a problem. Could it be that one of the items in that table is a reserved word? CREATE TABLE 'SQL' ([STATEMENT_COUNT] INTEGER, [DB] TEXT, [QUERY_TIME] TEXT, [QUERY_LENGTH] REAL, [QUERY] TEXT) Any other ideas what could be wrong with this table? I could mail a db with that table if that would be helpful. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 02 February 2007 13:51 To: sqlite-users@sqlite.org Subject: RE: [sqlite] How do I know sqlite_get_table is finished I'll admit I'm not much of a C expert, so I'd say Guy has a much better handle on that part than I do. As far as your code goes I only had one thought, and forgive me if this is just my ignorance of VBA. I didn't see a declaration for lReturnedRows anywhere. If you don't declare a variable, what is the value of that variable if it's used before you explicitly assign a value? The reason I ask is because you only assign a value to lReturnedRows if the length of the error message is 0, and you only set the error flag if lReturnedRows = 0. Do you need to do: If Len(strError) = 0 then Else lReturnedRows = 0 End If Don't know if this would really make a difference, and sorry if it's just a waste of time, but I thought I'd suggest it. -- Eric Pankoke Founder / Lead Developer Point Of Light Software http://www.polsoftware.com/ -- Original message -- From: [EMAIL PROTECTED] > Thanks for looking at that. > The crash can happen at the actual call to sqlite_get_table or it can > happen when I assign the resulting array to the wrapper function, so when > I do: GetFromDB = arr > > Yes, I would be interested in making a boolean (optional) argument in > sqlite_get_table that can leave out the field headers. I haven't tried > to compile myself yet, but I do have VC6, so it should be OK. > > Still, I don't understand why this crashes. > > RBS > > > At 07:44 2/2/2007 +, you wrote: > >>Sure, here it is: > > > > > > > > Looking at the C code published at > > http://www.tannertech.net/sqlite3vb/index.htm , it seems very risky - > > there > > are a lot of places it can fail to allocate memory (for example), and the > > use of some variables looks like it could randomly crash at any time... > > but > > I didn't test it, just looked at it. > > > > As far as I can understand the problem, it happens around this line: > > arr = GetFromDB(strSQL, lRows, strError, lDBHandle, strDB) > > > > Are you seeing a crash on the actual call to sqlite_get_table or only > > after > > it (when you try to use the results)? > > > > If you compiled the SQLite3VB.dll on your own, I can probably make some > > suggestions about fixing the C code of sqlite_get_table implementation, > > and > > maybe even some improvments (for example, a boolean flag to allow you to > > request the results array without the column headers, which I remember you > > asking about), and returning the number of rows immediately instead of in > > another function (which is somewhat dangerous), etc.). > > You should also probably consider using some kind of wrapper around > > sqlite3_exec in addition to sqlite3_get_table so you will be able to run > > commands (like pragma) without going through sqlite_get_table. > > > > > > Guy > > > > > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How do I know sqlite_get_table is finished
The trouble always starts before I deal with the array, so I am sure that the trouble is not there. There are only 2 places where I have seen the trouble occur: 1. When I call sqlite_get_table 2. When I assign the array produced by sqlite_get_table to the wrapper function. Yes, this is a catastrophic Excel crash, so no useful error message. I do get the option to debug in VC6++ and that always points to this bit of assembly: 77124CB4 8B 40 FC mov eax,dword ptr [eax-4] Something to do with OLEAUT, but that doesn't help me much. It looks the trouble doesn't happen when I do exactly the same with for example sqlite_master, so it looks there is something wrong with that table and I am going to have a look that now. Very puzzling and a right pain. RBS -Original Message- From: Trey Mack [mailto:[EMAIL PROTECTED] Sent: 02 February 2007 18:30 To: sqlite-users@sqlite.org Subject: Re: [sqlite] How do I know sqlite_get_table is finished > The function ArrayToSheet has nil to do with SQLite and I left that out. Ah, but that's where you access the variant array. That can be tricksy. Do you always access that variant array (arr) in the range (0 To NumRecords, 0 To NumColumns - 1) where the row arr(0, *) holds the column names, and UBound(arr, 2) is NumColumns ?? Also, when you say "crash", I assume you mean catastrophic-style, so you don't have a specific error number or description. Correct? - Trey - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How do I know sqlite_get_table is finished
OK, all fixed now 100% sure. In the end it was a plain, simple VBA coding bug. Instead of escaping single quotes in that table called SQL I had escaped double quotes with double quotes. Just mixed up chr(39) with chr(34). I suppose because we are dealing with C coding here it could crash Excel. Something like this wouldn't happen if it was just VB coding. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 02 February 2007 19:31 To: sqlite-users@sqlite.org Subject: RE: [sqlite] How do I know sqlite_get_table is finished The trouble always starts before I deal with the array, so I am sure that the trouble is not there. There are only 2 places where I have seen the trouble occur: 1. When I call sqlite_get_table 2. When I assign the array produced by sqlite_get_table to the wrapper function. Yes, this is a catastrophic Excel crash, so no useful error message. I do get the option to debug in VC6++ and that always points to this bit of assembly: 77124CB4 8B 40 FC mov eax,dword ptr [eax-4] Something to do with OLEAUT, but that doesn't help me much. It looks the trouble doesn't happen when I do exactly the same with for example sqlite_master, so it looks there is something wrong with that table and I am going to have a look that now. Very puzzling and a right pain. RBS -Original Message- From: Trey Mack [mailto:[EMAIL PROTECTED] Sent: 02 February 2007 18:30 To: sqlite-users@sqlite.org Subject: Re: [sqlite] How do I know sqlite_get_table is finished > The function ArrayToSheet has nil to do with SQLite and I left that out. Ah, but that's where you access the variant array. That can be tricksy. Do you always access that variant array (arr) in the range (0 To NumRecords, 0 To NumColumns - 1) where the row arr(0, *) holds the column names, and UBound(arr, 2) is NumColumns ?? Also, when you say "crash", I assume you mean catastrophic-style, so you don't have a specific error number or description. Correct? - Trey - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Still Excel crash when running sqlite_get_table
Still having a problem when selecting data from one particular table with the VB wrapper dll SQLite3VB.dll. It is only a small table and I just can't see why there would be a problem. Would anybody be willing to have a look at this table? The zipped database file is only 15 Kb. Thanks. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Still Excel crash when running sqlite_get_table
Wasn't sure if it was allowed to post attachments to this forum. I will see if I can reproduce this and send the zipped db to here. RBS -Original Message- From: Guy Hachlili [mailto:[EMAIL PROTECTED] Sent: 07 February 2007 15:40 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Still Excel crash when running sqlite_get_table Hmmm... At 13:38 2/7/2007 +, you wrote: >Still having a problem when selecting data from one particular table with >the VB wrapper dll SQLite3VB.dll. >It is only a small table and I just can't see why there would be a problem. >Would anybody be willing to have a look at this table? >The zipped database file is only 15 Kb. Nothing attached... can you post it somewhere for download? Guy - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Still Excel crash when running sqlite_get_table
Ah, now after closing Excel and reopening it and doing the select again on that same table it now does crash Excel. This is the same table I sent in the zipped db. RBS -Original Message- From: Guy Hachlili [mailto:[EMAIL PROTECTED] Sent: 07 February 2007 15:40 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Still Excel crash when running sqlite_get_table Hmmm... At 13:38 2/7/2007 +, you wrote: >Still having a problem when selecting data from one particular table with >the VB wrapper dll SQLite3VB.dll. >It is only a small table and I just can't see why there would be a problem. >Would anybody be willing to have a look at this table? >The zipped database file is only 15 Kb. Nothing attached... can you post it somewhere for download? Guy - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Still Excel crash when running sqlite_get_table
Not sure now the zip file has come through to this forum. I can see it, but I also got a message that it wasn't allowed. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 07 February 2007 16:19 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Still Excel crash when running sqlite_get_table Ah, now after closing Excel and reopening it and doing the select again on that same table it now does crash Excel. This is the same table I sent in the zipped db. RBS -Original Message- From: Guy Hachlili [mailto:[EMAIL PROTECTED] Sent: 07 February 2007 15:40 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Still Excel crash when running sqlite_get_table Hmmm... At 13:38 2/7/2007 +, you wrote: >Still having a problem when selecting data from one particular table with >the VB wrapper dll SQLite3VB.dll. >It is only a small table and I just can't see why there would be a problem. >Would anybody be willing to have a look at this table? >The zipped database file is only 15 Kb. Nothing attached... can you post it somewhere for download? Guy - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Still Excel crash when running sqlite_get_table
Thanks for that. I am not familiar with compiling C code and haven't compiled SQLite yet. Could you send me your dll so I could try that? Database seems fine to me as well, but I definitely have trouble with only that particular table. RBS -Original Message- From: Guy Hachlili [mailto:[EMAIL PROTECTED] Sent: 07 February 2007 17:22 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Still Excel crash when running sqlite_get_table At 17:04 2/7/2007 +, you wrote: >Not sure now the zip file has come through to this forum. >I can see it, but I also got a message that it wasn't allowed. Didn't get through the first time, did get through the second time. I tried my version of the VBSqlite3 DLL and it works, although I didn't try it with Excel but with VB6. I did make some changes to my DLL code - if you want to compile that, I can post them. The database itself seems OK. Guy - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Still Excel crash when running sqlite_get_table
It looks this crash only happens if the data in one one array element exceeds about 4000 characters or more precisely when the data in one element (row and column) of the database exceeds about 4000 characters. Maybe after all this is not a SQLite problem, but a VB problem and maybe VB arrays can't handle this, making Excel crash. As SQLite can handle large blob data it must be very unlikely indeed that SQLite is to blame. Will see if there is anything known about this in VB/VBA. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 07 February 2007 16:16 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Still Excel crash when running sqlite_get_table The trouble is that after dropping some tables and doing a VACUUM to make the file smaller the crash doesn't happen, despite that table still being the same. I will attach the zipped db in any case although this particular table (table called SQL) didn't actually cause a problem. RBS -Original Message- From: Guy Hachlili [mailto:[EMAIL PROTECTED] Sent: 07 February 2007 15:40 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Still Excel crash when running sqlite_get_table Hmmm... At 13:38 2/7/2007 +, you wrote: >Still having a problem when selecting data from one particular table with >the VB wrapper dll SQLite3VB.dll. >It is only a small table and I just can't see why there would be a problem. >Would anybody be willing to have a look at this table? >The zipped database file is only 15 Kb. Nothing attached... can you post it somewhere for download? Guy - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Still Excel crash when running sqlite_get_table
When searching for this problem the first one I found was one from myself of a few years back: http://shorterlink.co.uk/6813 So, I think this is what the trouble is and nil to do with SQLite. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 07 February 2007 21:37 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Still Excel crash when running sqlite_get_table It looks this crash only happens if the data in one one array element exceeds about 4000 characters or more precisely when the data in one element (row and column) of the database exceeds about 4000 characters. Maybe after all this is not a SQLite problem, but a VB problem and maybe VB arrays can't handle this, making Excel crash. As SQLite can handle large blob data it must be very unlikely indeed that SQLite is to blame. Will see if there is anything known about this in VB/VBA. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 07 February 2007 16:16 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Still Excel crash when running sqlite_get_table The trouble is that after dropping some tables and doing a VACUUM to make the file smaller the crash doesn't happen, despite that table still being the same. I will attach the zipped db in any case although this particular table (table called SQL) didn't actually cause a problem. RBS -Original Message- From: Guy Hachlili [mailto:[EMAIL PROTECTED] Sent: 07 February 2007 15:40 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Still Excel crash when running sqlite_get_table Hmmm... At 13:38 2/7/2007 +, you wrote: >Still having a problem when selecting data from one particular table with >the VB wrapper dll SQLite3VB.dll. >It is only a small table and I just can't see why there would be a problem. >Would anybody be willing to have a look at this table? >The zipped database file is only 15 Kb. Nothing attached... can you post it somewhere for download? Guy - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] x000 line queries - only for benchmarks?
Yes, VBA supports both, but as it is just for logging purposes my solution is fine, particularly as these long queries have repetitions of similar elements. Also the function sqlite_get_table will produce an array and the error happens directly when that function is called, so how is using a collection or a recordset going to help me? Did you mean alter the C source code? RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 08 February 2007 12:50 To: sqlite-users@sqlite.org Subject: Re: [sqlite] x000 line queries - only for benchmarks? Does VBA support collections? Or you could use an ADO recordset. Either one should easily let you store elements that are over 2K bytes in length. -- Eric Pankoke Founder / Lead Developer Point Of Light Software http://www.polsoftware.com/ -- Original message -- From: [EMAIL PROTECTED] > There is no problem running queries of whatever > length, so I could do benchmarks fine, it is just > that VBA has a problem with array elements holding > over 1823 characters. > > RBS > > > > [EMAIL PROTECTED] wrote: > >> In my (commercial) app I regularly have queries with over 1000 > >> characters. > >> Not over 1000 lines though. > >> As VBA (not sure now about VB6) has a problem with > >> array elements having over 1823 characters I had > >> to truncate my SQL logging routine. > >> > > Under 2k? That seems a bit restrictive. No benchmarking for you then, > > because the 25k line queries in the SQLite benchmark are nearly 2 meg. I > > guess they're unusual but even the (more typical?) 100 liners can get up > > to about 6-8k. > > > > Martin > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] x000 line queries - only for benchmarks?
No trouble, I thought that might be the case. I am pleased I finally nailed this down. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 08 February 2007 13:14 To: sqlite-users@sqlite.org Subject: RE: [sqlite] x000 line queries - only for benchmarks? No, I just forgot that this all stemmed from the original problem of sqlite_get_table throwing errors. After a while these threads all tend to get a little fuzzy to me. Sorry about that. -- Eric Pankoke Founder / Lead Developer Point Of Light Software http://www.polsoftware.com/ -- Original message -- From: "RB Smissaert" <[EMAIL PROTECTED]> > Yes, VBA supports both, but as it is just for logging purposes my solution > is fine, particularly as these long queries have repetitions of similar > elements. > > Also the function sqlite_get_table will produce an array and the error > happens directly when that function is called, so how is using a collection > or a recordset going to help me? Did you mean alter the C source code? > > RBS > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: 08 February 2007 12:50 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] x000 line queries - only for benchmarks? > > Does VBA support collections? Or you could use an ADO recordset. Either > one should easily let you store elements that are over 2K bytes in length. > > -- > Eric Pankoke > Founder / Lead Developer > Point Of Light Software > http://www.polsoftware.com/ > > -- Original message -- > From: [EMAIL PROTECTED] > > There is no problem running queries of whatever > > length, so I could do benchmarks fine, it is just > > that VBA has a problem with array elements holding > > over 1823 characters. > > > > RBS > > > > > > > [EMAIL PROTECTED] wrote: > > >> In my (commercial) app I regularly have queries with over 1000 > > >> characters. > > >> Not over 1000 lines though. > > >> As VBA (not sure now about VB6) has a problem with > > >> array elements having over 1823 characters I had > > >> to truncate my SQL logging routine. > > >> > > > Under 2k? That seems a bit restrictive. No benchmarking for you then, > > > because the 25k line queries in the SQLite benchmark are nearly 2 meg. I > > > guess they're unusual but even the (more typical?) 100 liners can get up > > > to about 6-8k. > > > > > > Martin > > > > > > > > - > > > To unsubscribe, send email to [EMAIL PROTECTED] > > > > > - > > > > > > > > > > > > > > > > > > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > > > - > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SELECT DISTINCT but ignore one column?
It sometimes would be very useful if you could do a SELECT DISTINCT, but ignoring the data in one (or maybe more) particular column. So for example col1 col2 col3 - ABC ADC Then doing SELECT DISTINCT (IGNORE col2) * from table would produce: ABC It wouldn't matter for me if it produced the above or ADC But there could be rules/logic to that. Is this possible in SQLite or would it be possible to add this as a new option? RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SELECT DISTINCT but ignore one column?
Thanks for the tip, will try that. RBS -Original Message- From: Samuel R. Neff [mailto:[EMAIL PROTECTED] Sent: 15 February 2007 22:27 To: sqlite-users@sqlite.org Subject: RE: [sqlite] SELECT DISTINCT but ignore one column? You could do this with a group by and use a min or max aggregate function on the "ignored" column HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Thursday, February 15, 2007 4:37 PM To: sqlite-users@sqlite.org Subject: [sqlite] SELECT DISTINCT but ignore one column? It sometimes would be very useful if you could do a SELECT DISTINCT, but ignoring the data in one (or maybe more) particular column. So for example col1 col2 col3 - ABC ADC Then doing SELECT DISTINCT (IGNORE col2) * from table would produce: ABC It wouldn't matter for me if it produced the above or ADC But there could be rules/logic to that. Is this possible in SQLite or would it be possible to add this as a new option? RBS - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SELECT DISTINCT but ignore one column?
I came across this problem a while ago and not sure now why I couldn't do that. Maybe I needed to keep the column, but then I suppose I could do: select distinct col1, null as col2, coll3 from table Maybe I needed the max or min from the ignore column and that works indeed fine with SR Neff's suggestion. RBS -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 15 February 2007 23:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] SELECT DISTINCT but ignore one column? RB Smissaert wrote: > It sometimes would be very useful if you could do a SELECT DISTINCT, but > ignoring the data in one (or maybe more) particular column. > So for example > > col1 col2 col3 > - > ABC > ADC > > Then doing SELECT DISTINCT (IGNORE col2) * from table > would produce: > > ABC > > It wouldn't matter for me if it produced the above or > ADC > > But there could be rules/logic to that. > > Is this possible in SQLite or would it be possible to add this as a new > option? > > > > Why can't you simply do this? select distinct col1, col3 from table; If you don't care about the value returned for col2, why bother returning anything? Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Update and insert questions
Nearly new to SQLite as well, but shouldn't this: UPDATE Person SET LastName=?, FirstName=?, Address=?, Age=? Be altered to this: UPDATE Person SET LastName='?', FirstName='?', Address='?', Age='?' RBS -Original Message- From: Jim Crafton [mailto:[EMAIL PROTECTED] Sent: 16 February 2007 16:04 To: sqlite-users@sqlite.org Subject: [sqlite] Update and insert questions OK, please bear with me here, as I'm very much of an SQL newbie. I'm writing a wrapper around sqlite. I want the to code to be able to modify a given value (column) of a specific row. To do this, as I understand it, I need to use the SQL UPDATE statement coupled with a WHERE clause. So assuming the following table : CREATE TABLE Person ( LastName varchar, FirstName varchar, Address varchar, Age int ); With a single row of: Doe John 100 Nowhere Ave. 45 I want to change "John" to "Bob". If I want to update this, I would write UPDATE Person SET LastName=?, FirstName=?, Address=?, Age=? WHERE LastName='Doe' AND FirstName='John' AND Address='100 Nowhere Ave.' AND Age=45; I then use the sqlite bind APIs to change values accordingly. The first time I execute this in sqlite, the sql execution succeeds. The second time I execute this there is no change to the DB, but the API calls don't return any error code! If I change the "=" operator in the WHERE clause to "like" then the operation makes the change to the DB. Is there something strange going on? I notice that if I then run some external tool (like sqlite3Explorer) and run SQL statements directly, the update statement with the "=" fails, and I get weird errors from the tool if I use the GUI to edit the row. Is there something that is being corrupted in the db file? Thanks Jim C - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Update and insert questions
I did say I was nearly new to SQLite. RBS -Original Message- From: Jim Crafton [mailto:[EMAIL PROTECTED] Sent: 16 February 2007 16:28 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Update and insert questions On 2/16/07, RB Smissaert <[EMAIL PROTECTED]> wrote: > Nearly new to SQLite as well, but shouldn't this: > > UPDATE Person SET LastName=?, FirstName=?, Address=?, Age=? > > Be altered to this: > > UPDATE Person SET LastName='?', FirstName='?', Address='?', Age='?' > I thought that the plain "?" character was an indicator that you were going to modify the column value via the sqlite3_bindXXX functions. Cheers Jim - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] retrieval speedup help requested
Try running: analyze city_loc after adding the index. RBS -Original Message- From: Tom Shaw [mailto:[EMAIL PROTECTED] Sent: 17 February 2007 22:16 To: sqlite-users@sqlite.org Subject: [sqlite] retrieval speedup help requested In sqlite 3 I have two tables. city_loc has 156865 entries and city_block has 1874352 entries: CREATE TABLE city_loc (loc_id INTEGER PRIMARY KEY, cc TEXT, region TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT); CREATE TABLE city_block (start INTEGER UNSIGNED NOT NULL, end INTEGER UNSIGNED NOT NULL, loc_id INTEGER NOT NULL); And my retrieval is but it is slow (6 seconds!): SELECT cc, region, city, postalCode, lat, lon, areaCode FROM city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end; I tried using: CREATE INDEX city_block_idx ON city_block (start,end); but it did not appear to speedup anything but it did use up a lot of space. Any suggestions on how to speedup retrievals? All help is appreciated. TIA Tom - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] retrieval speedup help requested
http://www.sqlite.org/lang_analyze.html RBS -Original Message- From: Anderson, James H (IT) [mailto:[EMAIL PROTECTED] Sent: 18 February 2007 01:15 To: sqlite-users@sqlite.org Subject: RE: [sqlite] retrieval speedup help requested Where does one get "analyze"? -Original Message----- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Saturday, February 17, 2007 5:25 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] retrieval speedup help requested Try running: analyze city_loc after adding the index. RBS -Original Message- From: Tom Shaw [mailto:[EMAIL PROTECTED] Sent: 17 February 2007 22:16 To: sqlite-users@sqlite.org Subject: [sqlite] retrieval speedup help requested In sqlite 3 I have two tables. city_loc has 156865 entries and city_block has 1874352 entries: CREATE TABLE city_loc (loc_id INTEGER PRIMARY KEY, cc TEXT, region TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT); CREATE TABLE city_block (start INTEGER UNSIGNED NOT NULL, end INTEGER UNSIGNED NOT NULL, loc_id INTEGER NOT NULL); And my retrieval is but it is slow (6 seconds!): SELECT cc, region, city, postalCode, lat, lon, areaCode FROM city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end; I tried using: CREATE INDEX city_block_idx ON city_block (start,end); but it did not appear to speedup anything but it did use up a lot of space. Any suggestions on how to speedup retrievals? All help is appreciated. TIA Tom - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Month string from yyyy-mm-dd
Is it possible with the date-time functions to get the month as a string, so
January etc. from the date in the format -mm-dd?
I can get the month as a number like this:
select strftime('%m', '2007-02-17')
and I could do a table update by joining to a month lookup table, but I
wonder if there is a simpler way to do this.
Thanks for any advice.
RBS
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Month string from yyyy-mm-dd
Yes, it looks it isn't there. It is no problem though to update the table and 25000 records takes about a third of a second when I do it from the integer mmdd format like this: UPDATE A2IDB3F_J SET DATE_OF_BIRTH = (SELECT MONTH_TEXT FROM MONTH_LOOKUP WHERE (SELECT CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 1 AS INTEGER) * 100) = MONTH_NUMBER) Will see if doing it with a substr function is any faster. Would that work on integer numbers? RBS -Original Message- From: Martin Jenkins [mailto:[EMAIL PROTECTED] Sent: 18 February 2007 18:53 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Month string from -mm-dd RB Smissaert wrote: > Is it possible with the date-time functions to get the month as a string, so > January etc. from the date in the format -mm-dd? Doesn't look like it. Nothing in the wiki and I couldn't see anything in the source either. I suppose you could use a big case statement if you wanted to avoid joining with a month table. Martin - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Month string from yyyy-mm-dd
Thanks, I thought that would be the reason it wasn't included. Doing a join with a lookup table is very much fast enough, so there is no problem there. Just tried it with substr instead of integer division (starting with mmdd) and they are equally fast. Will now try a big CASE statement, but I somehow guess it will be slower. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 18 February 2007 19:37 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Month string from -mm-dd "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Is it possible with the date-time functions to get the month as a string, so > January etc. from the date in the format -mm-dd? This is difficult to internationalize so I omitted it in my implementation of the date/time functions. You can, of course, grab the source to the date/time functions, make what modifications you want, and use the modified date/time functions in your code. But text month names are not supported by the core distribution of SQLite. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Month string from yyyy-mm-dd
Analyzing the lookup table knocks the time down from 0.36 to 0.31 secs, something I didn't expect. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 18 February 2007 19:59 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Month string from -mm-dd Thanks, I thought that would be the reason it wasn't included. Doing a join with a lookup table is very much fast enough, so there is no problem there. Just tried it with substr instead of integer division (starting with mmdd) and they are equally fast. Will now try a big CASE statement, but I somehow guess it will be slower. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 18 February 2007 19:37 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Month string from yyyy-mm-dd "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Is it possible with the date-time functions to get the month as a string, so > January etc. from the date in the format -mm-dd? This is difficult to internationalize so I omitted it in my implementation of the date/time functions. You can, of course, grab the source to the date/time functions, make what modifications you want, and use the modified date/time functions in your code. But text month names are not supported by the core distribution of SQLite. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] What is wrong with this SELECT CASE statement?
Trying to update my mmdd integers to months with a SELECT CASE statement: SELECT CASE (CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 1 AS INTEGER) * 100) WHEN 1 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'January' WHEN 2 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'February' WHEN 3 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'March' WHEN 4 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'April' WHEN 5 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'May' WHEN 6 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'June' WHEN 7 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'July' WHEN 8 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'August' WHEN 9 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'September' WHEN 10 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'October' WHEN 11 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'November' WHEN 12 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'December' END FROM A2IDC21_J But no updates take place. Thanks for any advice. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: What is wrong with this SELECT CASE statement?
Thanks, I understand now. Will fix it and see how it compares the other methods. RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 19 February 2007 04:54 To: SQLite Subject: [sqlite] Re: What is wrong with this SELECT CASE statement? RB Smissaert <[EMAIL PROTECTED]> wrote: > Trying to update my mmdd integers to months with a SELECT CASE > statement: > > SELECT CASE > (CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 1 AS > INTEGER) * 100) > WHEN 1 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'January' > WHEN 2 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'February' > WHEN 3 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'March' > WHEN 4 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'April' > WHEN 5 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'May' > WHEN 6 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'June' > WHEN 7 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'July' > WHEN 8 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'August' > WHEN 9 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'September' > WHEN 10 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'October' > WHEN 11 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'November' > WHEN 12 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'December' > END > FROM A2IDC21_J > > But no updates take place. UPDATE is a statement, not an expression. It cannot appear nested in another statement. You want UPDATE A2IDC21_J SET DATE_OF_BIRTH = CASE (CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 1 AS INTEGER) * 100) WHEN 1 THEN 'January' WHEN 2 THEN 'February' ... END Also, the expression in the CASE can be simplified to CAST(DATE_OF_BIRTH AS INTEGER) / 100 % 100 Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] What is wrong with this SELECT CASE statement?
Thanks. Yes, it will return the errors, but this fell away in my wrapper function. Understand now how this works and will fix it and see how it compares to the Other methods. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 19 February 2007 01:42 To: sqlite-users@sqlite.org Subject: Re: [sqlite] What is wrong with this SELECT CASE statement? --- RB Smissaert <[EMAIL PROTECTED]> wrote: > Trying to update my mmdd integers to months with a SELECT CASE > statement: > > SELECT CASE > (CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 1 AS > INTEGER) * 100) > WHEN 1 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'January' > WHEN 2 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'February' > WHEN 3 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'March' > WHEN 4 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'April' > WHEN 5 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'May' > WHEN 6 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'June' > WHEN 7 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'July' > WHEN 8 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'August' > WHEN 9 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'September' > WHEN 10 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'October' > WHEN 11 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'November' > WHEN 12 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'December' > END > FROM A2IDC21_J > > But no updates take place. SQL error: near "UPDATE": syntax error Does the environment you run in return error codes? You can't perform an UPDATE in a WHEN sub-clause. UPDATE TABLE1 SET FOO = (CASE ...whatever... END) > Thanks for any advice. > > RBS Be a PS3 game guru. Get your game face on with the latest PS3 news and previews at Yahoo! Games. http://videogames.yahoo.com/platform?platform=120121 - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: What is wrong with this SELECT CASE statement?
Got this worked out now and in fact it looks a big case statement is a bit faster than the other methods. I also found that I don't need the CAST AS INTEGER: UPDATE A2ID965_J SET DATE_OF_BIRTH = CASE (DATE_OF_BIRTH / 100) % 100 WHEN 1 THEN 'January' WHEN 2 THEN 'February' WHEN 3 THEN 'March' WHEN 4 THEN 'April' WHEN 5 THEN 'May' WHEN 6 THEN 'June' WHEN 7 THEN 'July' WHEN 8 THEN 'August' WHEN 9 THEN 'September' WHEN 10 THEN 'October' WHEN 11 THEN 'November' WHEN 12 THEN 'December' END This is about 20% faster than a table lookup, even when the lookup table is already present. So, learned something useful there. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 19 February 2007 08:04 To: Bart Smissaert2 Subject: FW: [sqlite] Re: What is wrong with this SELECT CASE statement? -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 19 February 2007 04:54 To: SQLite Subject: [sqlite] Re: What is wrong with this SELECT CASE statement? RB Smissaert <[EMAIL PROTECTED]> wrote: > Trying to update my mmdd integers to months with a SELECT CASE > statement: > > SELECT CASE > (CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 1 AS > INTEGER) * 100) > WHEN 1 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'January' > WHEN 2 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'February' > WHEN 3 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'March' > WHEN 4 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'April' > WHEN 5 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'May' > WHEN 6 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'June' > WHEN 7 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'July' > WHEN 8 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'August' > WHEN 9 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'September' > WHEN 10 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'October' > WHEN 11 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'November' > WHEN 12 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'December' > END > FROM A2IDC21_J > > But no updates take place. UPDATE is a statement, not an expression. It cannot appear nested in another statement. You want UPDATE A2IDC21_J SET DATE_OF_BIRTH = CASE (CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 1 AS INTEGER) * 100) WHEN 1 THEN 'January' WHEN 2 THEN 'February' ... END Also, the expression in the CASE can be simplified to CAST(DATE_OF_BIRTH AS INTEGER) / 100 % 100 Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] compiling with VC++
Could anybody pass me step by step instructions to compile the source with MS VC++ ? I don't code or compile in C at all and only code in VB/VBA. I am following the tutorial by Todd Tanner: http://www.tannertech.net/sqlite3vb/index.htm But this is with Visual Studio Net and I am running in some problems. For now I can see 2 trouble spots: The tutorial talks about sqlite.h, but in the download I don't have that file, although I have sqlite.h.in, which seems to have the code as described in the tutorial. Secondy at the end of the tutorial there is a change in the configuration settings: We need to set our Module Definition File. * Right click your project in the Solution Explorer and select Properties. * Select "All Configurations" from the Configuration drop down box. (Important!) * Select Configuration Properties -> Linker -> Input. * Under "Module Definition File" enter "$(InputDir)sqlite3.def" without the quotes. It should be located in the same folder in your project as your SQLite source files. Not sure how to do the same in VC++ I have tried compiling after altering the extension from sqlite.h.in to sqlite.h and obviously this is completely wrong. When I compile I get this error the most: fatal error C1083: Cannot open include file: 'sqliteInt.h': No such file or directory Thanks for any advice. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] compiling with VC++
Thanks, will have a look at that. Do you know from experience that it will compile OK with VC6++? RBS -Original Message- From: Martin Jenkins [mailto:[EMAIL PROTECTED] Sent: 24 February 2007 12:02 To: sqlite-users@sqlite.org Subject: Re: [sqlite] compiling with VC++ RB Smissaert wrote: > Could anybody pass me step by step instructions to compile the source with > MS VC++ ? I don't code or compile in C at all and only code in VB/VBA. There a some instructions at: http://www.sqlite.org/cvstrac/wiki?p=HowToCompile and a zip file - sqlite-source-3_3_13.zip - containing preprocessed source for Windows users at: http://www.sqlite.org/download.html I don't know if that latter file contains the headers you want as I mostly work on Linux. This is a bit of an FAQ so you might want to take a look through the mailing list archives. There's one at news.gmane.org. Shout again if this didn't help. ;) Martin - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] compiling with VC++
OK, thanks a lot.
I think I have made a lot of progress, probably because of the different
source files you pointed me to.
I get one error though and that has to do with this bit of code:
/*
** The version of the library
*/
const char sqlite3_version[] = SQLITE_VERSION;
BSTR __stdcall sqlite3_libversion(void){ return SysAllocStringByteLen(
sqlite3_version,strlen( sqlite3_version) ); }
BSTR __stdcall sqlite3_libversion_number(void){ return
SQLITE_VERSION_NUMBER; }
The error originates from the second line of code and shows like this in the
build window:
c:\sqlite\sourcewin\main.c(33) : error C2373: 'sqlite3_libversion' :
redefinition; different type modifiers
No idea what to do about this.
RBS
-Original Message-
From: Martin Jenkins [mailto:[EMAIL PROTECTED]
Sent: 24 February 2007 13:29
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] compiling with VC++
RB Smissaert wrote:
> Thanks, will have a look at that.
> Do you know from experience that it will compile OK with VC6++?
I'm pretty sure I've compiled it with VC6++. I've been compiling on
Debian and Solaris machines recently and have sort of lost track of
building stuff on Windows. I tried upgrading to the current MS compilers
but there seemed to be so many things that I didn't know I wanted that I
think I gave up.
I'm having the same problems with installing the software for my phone.
Been at it a couple of hours and so far it's changed my default email
application, my default image viewer and my MP3 player and I still can't
access the information I wanted off my phone. First and last Windows
Smartphone I'll buy. It's "Smart" as in "Sting" rather than "Clever".
I'm going for a cup of tea and then I'll have a look and see if I can
find some VC6 project files for you. If I do I'll mail them straight to
you rather than clutter up the list. If they work I suppose they could
be uploaded somewhere.
Martin
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] compiling with VC++
Think I got this fixed now.
It simply was something I had overlooked to change:
//const char *sqlite3_libversion(void);
BSTR __stdcall sqlite3_libversion(void);
Lots of warnings (265) but no errors and a dll is produced, which I will try
now.
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 24 February 2007 13:47
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] compiling with VC++
OK, thanks a lot.
I think I have made a lot of progress, probably because of the different
source files you pointed me to.
I get one error though and that has to do with this bit of code:
/*
** The version of the library
*/
const char sqlite3_version[] = SQLITE_VERSION;
BSTR __stdcall sqlite3_libversion(void){ return SysAllocStringByteLen(
sqlite3_version,strlen( sqlite3_version) ); }
BSTR __stdcall sqlite3_libversion_number(void){ return
SQLITE_VERSION_NUMBER; }
The error originates from the second line of code and shows like this in the
build window:
c:\sqlite\sourcewin\main.c(33) : error C2373: 'sqlite3_libversion' :
redefinition; different type modifiers
No idea what to do about this.
RBS
-Original Message-
From: Martin Jenkins [mailto:[EMAIL PROTECTED]
Sent: 24 February 2007 13:29
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] compiling with VC++
RB Smissaert wrote:
> Thanks, will have a look at that.
> Do you know from experience that it will compile OK with VC6++?
I'm pretty sure I've compiled it with VC6++. I've been compiling on
Debian and Solaris machines recently and have sort of lost track of
building stuff on Windows. I tried upgrading to the current MS compilers
but there seemed to be so many things that I didn't know I wanted that I
think I gave up.
I'm having the same problems with installing the software for my phone.
Been at it a couple of hours and so far it's changed my default email
application, my default image viewer and my MP3 player and I still can't
access the information I wanted off my phone. First and last Windows
Smartphone I'll buy. It's "Smart" as in "Sting" rather than "Clever".
I'm going for a cup of tea and then I'll have a look and see if I can
find some VC6 project files for you. If I do I'll mail them straight to
you rather than clutter up the list. If they work I suppose they could
be uploaded somewhere.
Martin
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] compiling with VC++
> which I will try now
And it works fine.
So the instructions at:
http://www.tannertech.net/sqlite3vb/index.htm
are fine to compile with VC6++ if you pick the right source files:
http://www.sqlite.org/download.html
and just ignore this bit:
We need to set our Module Definition File.
* Right click your project in the Solution Explorer and select
Properties.
* Select "All Configurations" from the Configuration drop down box.
(Important!)
* Select Configuration Properties -> Linker -> Input.
* Under "Module Definition File" enter "$(InputDir)sqlite3.def" without
the quotes. It should be located in the same folder in your project as your
SQLite source files.
Quite impressive to compile a real dll (as compared to a VB ActiveX dll).
Now will need to figure out if I can get some more function available in VB
than the ones I got now:
Private Declare Sub sqlite3_open Lib "SQLiteVB.dll" _
(ByVal FileName As String, _
ByRef handle As Long)
Private Declare Sub sqlite3_close Lib "SQLiteVB.dll" _
(ByVal DB_Handle As Long)
Private Declare Function sqlite3_last_insert_rowid _
Lib "SQLiteVB.dll" _
(ByVal DB_Handle As Long) As Long
Private Declare Function sqlite3_changes _
Lib "SQLiteVB.dll" _
(ByVal DB_Handle As Long) As Long
Private Declare Function sqlite_get_table _
Lib "SQLiteVB.dll" _
(ByVal DB_Handle As Long, _
ByVal SQLString As String, _
ByRef ErrStr As String) As Variant()
Private Declare Function sqlite_libversion _
Lib "SQLiteVB.dll" () As String
Private Declare Function number_of_rows_from_last_call _
Lib "SQLiteVB.dll" () As Long
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 24 February 2007 14:31
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] compiling with VC++
Think I got this fixed now.
It simply was something I had overlooked to change:
//const char *sqlite3_libversion(void);
BSTR __stdcall sqlite3_libversion(void);
Lots of warnings (265) but no errors and a dll is produced, which I will try
now.
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 24 February 2007 13:47
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] compiling with VC++
OK, thanks a lot.
I think I have made a lot of progress, probably because of the different
source files you pointed me to.
I get one error though and that has to do with this bit of code:
/*
** The version of the library
*/
const char sqlite3_version[] = SQLITE_VERSION;
BSTR __stdcall sqlite3_libversion(void){ return SysAllocStringByteLen(
sqlite3_version,strlen( sqlite3_version) ); }
BSTR __stdcall sqlite3_libversion_number(void){ return
SQLITE_VERSION_NUMBER; }
The error originates from the second line of code and shows like this in the
build window:
c:\sqlite\sourcewin\main.c(33) : error C2373: 'sqlite3_libversion' :
redefinition; different type modifiers
No idea what to do about this.
RBS
-Original Message-
From: Martin Jenkins [mailto:[EMAIL PROTECTED]
Sent: 24 February 2007 13:29
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] compiling with VC++
RB Smissaert wrote:
> Thanks, will have a look at that.
> Do you know from experience that it will compile OK with VC6++?
I'm pretty sure I've compiled it with VC6++. I've been compiling on
Debian and Solaris machines recently and have sort of lost track of
building stuff on Windows. I tried upgrading to the current MS compilers
but there seemed to be so many things that I didn't know I wanted that I
think I gave up.
I'm having the same problems with installing the software for my phone.
Been at it a couple of hours and so far it's changed my default email
application, my default image viewer and my MP3 player and I still can't
access the information I wanted off my phone. First and last Windows
Smartphone I'll buy. It's "Smart" as in "Sting" rather than "Clever".
I'm going for a cup of tea and then I'll have a look and see if I can
find some VC6 project files for you. If I do I'll mail them straight to
you rather than clutter up the list. If they work I suppose they could
be uploaded somewhere.
Martin
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send ema
RE: [sqlite] compiling with VC++
Just one simple question:
I have a VC6++ project now that compiles a dll called SQLiteVB.dll
Now what do I do make it compile a dll called SQLite3VB.dll?
I can see no simple configuration setting for this. I could of course start
a new project, but that seems silly.
Thanks for any advice.
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 24 February 2007 14:31
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] compiling with VC++
Think I got this fixed now.
It simply was something I had overlooked to change:
//const char *sqlite3_libversion(void);
BSTR __stdcall sqlite3_libversion(void);
Lots of warnings (265) but no errors and a dll is produced, which I will try
now.
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 24 February 2007 13:47
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] compiling with VC++
OK, thanks a lot.
I think I have made a lot of progress, probably because of the different
source files you pointed me to.
I get one error though and that has to do with this bit of code:
/*
** The version of the library
*/
const char sqlite3_version[] = SQLITE_VERSION;
BSTR __stdcall sqlite3_libversion(void){ return SysAllocStringByteLen(
sqlite3_version,strlen( sqlite3_version) ); }
BSTR __stdcall sqlite3_libversion_number(void){ return
SQLITE_VERSION_NUMBER; }
The error originates from the second line of code and shows like this in the
build window:
c:\sqlite\sourcewin\main.c(33) : error C2373: 'sqlite3_libversion' :
redefinition; different type modifiers
No idea what to do about this.
RBS
-Original Message-
From: Martin Jenkins [mailto:[EMAIL PROTECTED]
Sent: 24 February 2007 13:29
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] compiling with VC++
RB Smissaert wrote:
> Thanks, will have a look at that.
> Do you know from experience that it will compile OK with VC6++?
I'm pretty sure I've compiled it with VC6++. I've been compiling on
Debian and Solaris machines recently and have sort of lost track of
building stuff on Windows. I tried upgrading to the current MS compilers
but there seemed to be so many things that I didn't know I wanted that I
think I gave up.
I'm having the same problems with installing the software for my phone.
Been at it a couple of hours and so far it's changed my default email
application, my default image viewer and my MP3 player and I still can't
access the information I wanted off my phone. First and last Windows
Smartphone I'll buy. It's "Smart" as in "Sting" rather than "Clever".
I'm going for a cup of tea and then I'll have a look and see if I can
find some VC6 project files for you. If I do I'll mail them straight to
you rather than clutter up the list. If they work I suppose they could
be uploaded somewhere.
Martin
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] compiling with VC++
Thanks, that is it, nice and simple. RBS -Original Message- From: Martin Jenkins [mailto:[EMAIL PROTECTED] Sent: 24 February 2007 15:40 To: sqlite-users@sqlite.org Subject: Re: [sqlite] compiling with VC++ RB Smissaert wrote: > Now what do I do make it compile a dll called SQLite3VB.dll? I think it defaults to the project name, but have a look at the Link tab in the Project|Settings dialog (Alt-F7) - there's an edit box titled "Output file name" which might do what you want. Martin - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] compiling with VC++
When compiling this source code I get 265 warning, which doesn't really worry me that much as it all seems to be working fine, but in general what kind of warning should be taken seriously? I only have 8 different types of warnings: warning C4018: '!=' : signed/unsigned mismatch warning C4028: formal parameter 1 different from declaration warning C4047: 'initializing' : '__int64 (__cdecl *)(struct Mem *)' differs in levels of indirection from 'char *(__cdecl *)(const char *,char *)' warning C4090: 'function' : different 'const' qualifiers warning C4113: '__int64 (__cdecl *)(struct Mem *)' differs in parameter lists from 'void *(__cdecl *)(struct sqlite3 *,void (__cdecl *)(void *,int ,const char *,const char *,__int64 ),void *)' warning C4133: 'initializing' : incompatible types - from '__int64 (__cdecl *)(struct sqlite3_stmt *,int )' to 'double (__cdecl *)(struct sqlite3_stmt *,int )' warning C4244: '+=' : conversion from '__int64 ' to 'int ', possible loss of data warning C4761: integral size mismatch in argument; conversion supplied RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 24 February 2007 16:18 To: sqlite-users@sqlite.org Subject: RE: [sqlite] compiling with VC++ Thanks, that is it, nice and simple. RBS -Original Message- From: Martin Jenkins [mailto:[EMAIL PROTECTED] Sent: 24 February 2007 15:40 To: sqlite-users@sqlite.org Subject: Re: [sqlite] compiling with VC++ RB Smissaert wrote: > Now what do I do make it compile a dll called SQLite3VB.dll? I think it defaults to the project name, but have a look at the Link tab in the Project|Settings dialog (Alt-F7) - there's an edit box titled "Output file name" which might do what you want. Martin - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] compiling with VC++
Here examples of all 3, all from the same source file and the same code
block:
C:\SQLite\SourceWin\loadext.c(138) : warning C4047: 'initializing' : 'int
(__cdecl *)(struct sqlite3_stmt *,int )' differs in levels of indirection
from 'const char *(__cdecl *)(struct sqlite3_stmt *,int )'
const sqlite3_api_routines sqlite3_apis = {
sqlite3_column_double,
C:\SQLite\SourceWin\loadext.c(125) : warning C4113: 'int (__cdecl *)(struct
sqlite3 *,void *,void (__cdecl *)(void *,struct sqlite3 *,int ,const char
*))' differs in parameter lists from 'int (__cdecl *)(struct sqlite3 *)'
const sqlite3_api_routines sqlite3_apis = {
sqlite3_collation_needed,
C:\SQLite\SourceWin\loadext.c(208) : warning C4133: 'initializing' :
incompatible types - from 'void *(__cdecl *)(struct sqlite3 *,void (__cdecl
*)(void *,int ,const char *,const char *,__int64 ),void *)' to 'char
*(__cdecl *)(int ,char *,const char
*,... )'
const sqlite3_api_routines sqlite3_apis = {
sqlite3_update_hook,
This codeblock ends like this:
/*
** The original API set ends here. All extensions can call any
** of the APIs above provided that the pointer is not NULL. But
** before calling APIs that follow, extension should check the
** sqlite3_libversion_number() to make sure they are dealing with
** a library that is new enough to support that API.
*
*/
sqlite3_overload_function,
};
RBS
-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED]
Sent: 24 February 2007 17:29
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] compiling with VC++
They're probably harmless.
Even so, can you post the file name/line of one example of each of the
following 3 warnings as they seem odd:
warning C4047: 'initializing' : '__int64 (__cdecl *)(struct Mem *)' differs
in levels of indirection from 'char *(__cdecl *)(const char *,char *)'
warning C4113: '__int64 (__cdecl *)(struct Mem *)' differs in parameter
lists from 'void *(__cdecl *)(struct sqlite3 *,void (__cdecl *)(void *,int
,const char *,const char *,__int64 ),void *)'
warning C4133: 'initializing' : incompatible types - from '__int64 (__cdecl
*)(struct sqlite3_stmt *,int )' to 'double (__cdecl *)(struct sqlite3_stmt
*,int )'
--- RB Smissaert <[EMAIL PROTECTED]> wrote:
> When compiling this source code I get 265 warning, which doesn't really
> worry me that much as it all seems to be working fine, but in general what
> kind of warning should be taken seriously?
>
> I only have 8 different types of warnings:
>
> warning C4018: '!=' : signed/unsigned mismatch
> warning C4028: formal parameter 1 different from declaration
> warning C4047: 'initializing' : '__int64 (__cdecl *)(struct Mem *)'
differs
> in levels of indirection from 'char *(__cdecl *)(const char *,char *)'
> warning C4090: 'function' : different 'const' qualifiers
> warning C4113: '__int64 (__cdecl *)(struct Mem *)' differs in parameter
> lists from 'void *(__cdecl *)(struct sqlite3 *,void (__cdecl *)(void *,int
> ,const char *,const char *,__int64 ),void *)'
> warning C4133: 'initializing' : incompatible types - from '__int64
(__cdecl
> *)(struct sqlite3_stmt *,int )' to 'double (__cdecl *)(struct sqlite3_stmt
> *,int )'
> warning C4244: '+=' : conversion from '__int64 ' to 'int ', possible loss
of
> data
> warning C4761: integral size mismatch in argument; conversion supplied
Looking for earth-friendly autos?
Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.
http://autos.yahoo.com/green_center/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] compiling with VC++
Thanks, that is very helpful and reassuring as well. Will see if I can figure out then what is causing this one: warning C4028, as you say that could be a typo. RBS -Original Message- From: Martin Jenkins [mailto:[EMAIL PROTECTED] Sent: 24 February 2007 18:11 To: sqlite-users@sqlite.org Subject: Re: [sqlite] compiling with VC++ RB Smissaert wrote: > When compiling this source code I get 265 warning, which doesn't really > worry me that much as it all seems to be working fine, but in general what > kind of warning should be taken seriously? This has come up the list before. Dr Hipp assigns a lot more weight to his tests passing than the complete absence of compiler warnings. This may sound cavalier but many of these warnings are in fact pretty benign. If you have a comprehensive test suite (as SQLite does) you can assume that these warnings *are* benign. One widely accepted reason for not "fixing" them is that most of the fixes (usually casts to similar type) just clutter up your source code and mask the intentions of the author. > I only have 8 different types of warnings: I won't go through them in huge detail because it's hard to say what the problem is without seeing the specific source lines, but generally compilers play safe and it's up to the author to know what he's doing. It can be a bit scary when you compile someone else's code for the first time - you see a load of warnings and think what a load of [EMAIL PROTECTED] but most of the time it's just the compiler being picky. As I said above, if you have a decent test suite you can relax a lot. > warning C4018: '!=' : signed/unsigned mismatch This is usually benign. Say you have an variable in which you store the unsigned return value from a library function. You know the value is always +ve so you make the variable unsigned. The compiler doesn't know the number will always be +ve but it knows signed/unsigned mismatch is a common source of bugs so it reports a warning. A cast would fix the warning but might suggest to the reader that the author wanted to force some kind of conversion. Or it might not. > warning C4028: formal parameter 1 different from declaration Without seeing the code, it's hard to say, but I'd expect this to be an error or a typo. Whether it matters is something else - it could be the signed/unsigned issue above. > warning C4047: 'initializing' : '__int64 (__cdecl *)(struct Mem *)' differs > in levels of indirection from 'char *(__cdecl *)(const char *,char *)' Hard to say without seeing the code. Indirection in C can be quite tricky and is a common source of bugs, but I'd be surprised to see anything like that in SQLite. > warning C4090: 'function' : different 'const' qualifiers This is common when using string library functions. They're declared const because, say, strlen won't alter your data and your pointers are not declared const because they do. Sometimes an error but usually ok. > warning C4113: '__int64 (__cdecl *)(struct Mem *)' differs in parameter > lists from 'void *(__cdecl *)(struct sqlite3 *,void (__cdecl *)(void *,int > ,const char *,const char *,__int64 ),void *)' > warning C4133: 'initializing' : incompatible types - from '__int64 (__cdecl > *)(struct sqlite3_stmt *,int )' to 'double (__cdecl *)(struct sqlite3_stmt > *,int )' Hard to say without seeing the code, but it looks related to the indirection warning above. > warning C4244: '+=' : conversion from '__int64 ' to 'int ', possible loss of > data > warning C4761: integral size mismatch in argument; conversion supplied Common warnings where the compiler has converted between say 16/32 bit ints and 32/64 bit longs. It's OK in one direction but it may not be OK in the other, so the compiler warns and it's up to you to check. If the int/long variable only holds small numbers it's not significant but the compiler can't tell what numbers will be stored and so it's back to you. This explanation is brief and fairly rushed so may it not be completely rigorous in places but I hope it helps a bit. Martin - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] compiling with VC++
All C4028 warnings originate from this code block:
const sqlite3_api_routines sqlite3_apis = {
sqlite3_aggregate_context,
sqlite3_aggregate_count,
sqlite3_bind_blob,
sqlite3_bind_double,
sqlite3_bind_int,
sqlite3_bind_int64,
sqlite3_bind_null,
sqlite3_bind_parameter_count,
sqlite3_bind_parameter_index,
sqlite3_bind_parameter_name,
sqlite3_bind_text,
sqlite3_bind_text16,
sqlite3_bind_value,
sqlite3_busy_handler,
sqlite3_busy_timeout,
//sqlite3_changes,
//sqlite3_close,
sqlite3_collation_needed,
sqlite3_collation_needed16,
sqlite3_column_blob,
sqlite3_column_bytes,
sqlite3_column_bytes16,
sqlite3_column_count,
sqlite3_column_database_name,
sqlite3_column_database_name16,
sqlite3_column_decltype,
sqlite3_column_decltype16,
sqlite3_column_double,
sqlite3_column_int,
sqlite3_column_int64,
sqlite3_column_name,
sqlite3_column_name16,
sqlite3_column_origin_name,
sqlite3_column_origin_name16,
sqlite3_column_table_name,
sqlite3_column_table_name16,
sqlite3_column_text,
sqlite3_column_text16,
sqlite3_column_type,
sqlite3_column_value,
sqlite3_commit_hook,
sqlite3_complete,
sqlite3_complete16,
sqlite3_create_collation,
sqlite3_create_collation16,
sqlite3_create_function,
sqlite3_create_function16,
sqlite3_create_module,
sqlite3_data_count,
sqlite3_db_handle,
sqlite3_declare_vtab,
sqlite3_enable_shared_cache,
sqlite3_errcode,
sqlite3_errmsg,
sqlite3_errmsg16,
sqlite3_exec,
sqlite3_expired,
sqlite3_finalize,
sqlite3_free,
sqlite3_free_table,
sqlite3_get_autocommit,
sqlite3_get_auxdata,
sqlite3_get_table,
0, /* Was sqlite3_global_recover(), but that function is deprecated */
sqlite3_interrupt,
//sqlite3_last_insert_rowid,
//sqlite3_libversion,
//sqlite3_libversion_number,
sqlite3_malloc,
sqlite3_mprintf,
//sqlite3_open,
//sqlite3_open16,
sqlite3_prepare,
sqlite3_prepare16,
sqlite3_profile,
sqlite3_progress_handler,
sqlite3_realloc,
sqlite3_reset,
sqlite3_result_blob,
sqlite3_result_double,
sqlite3_result_error,
sqlite3_result_error16,
sqlite3_result_int,
sqlite3_result_int64,
sqlite3_result_null,
sqlite3_result_text,
sqlite3_result_text16,
sqlite3_result_text16be,
sqlite3_result_text16le,
sqlite3_result_value,
sqlite3_rollback_hook,
sqlite3_set_authorizer,
sqlite3_set_auxdata,
sqlite3_snprintf,
sqlite3_step,
sqlite3_table_column_metadata,
sqlite3_thread_cleanup,
sqlite3_total_changes,
sqlite3_trace,
sqlite3_transfer_bindings,
sqlite3_update_hook,
sqlite3_user_data,
sqlite3_value_blob,
sqlite3_value_bytes,
sqlite3_value_bytes16,
sqlite3_value_double,
sqlite3_value_int,
sqlite3_value_int64,
sqlite3_value_numeric_type,
sqlite3_value_text,
sqlite3_value_text16,
sqlite3_value_text16be,
sqlite3_value_text16le,
sqlite3_value_type,
sqlite3_vmprintf,
/*
** The original API set ends here. All extensions can call any
** of the APIs above provided that the pointer is not NULL. But
** before calling APIs that follow, extension should check the
** sqlite3_libversion_number() to make sure they are dealing with
** a library that is new enough to support that API.
*
*/
sqlite3_overload_function,
};
Not sure it is relevant but some lines are commented out to do with making
it VB compatible.
If it helps, these are the lines that give this C4028 warning:
sqlite3_enable_shared_cache,
sqlite3_errmsg,
sqlite3_expired,
sqlite3_free_table,
sqlite3_malloc,
sqlite3_mprintf,
sqlite3_realloc,
sqlite3_result_error16,
sqlite3_result_int64,
sqlite3_result_null,
sqlite3_result_value,
sqlite3_set_auxdata,
sqlite3_snprintf,
sqlite3_trace,
sqlite3_update_hook,
sqlite3_user_data,
sqlite3_value_bytes,
sqlite3_value_bytes16,
sqlite3_value_numeric_type,
RBS
-Original Message-
From: Martin Jenkins [mailto:[EMAIL PROTECTED]
Sent: 24 February 2007 18:11
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] compiling with VC++
RB Smissaert wrote:
> When compiling this source code I get 265 warning, which doesn't really
> worry me that much as it all seems to be working fine, but in general what
> kind of warning should be taken seriously?
This has come up the list before. Dr Hipp assigns a lot more weight to
his tests passing than the complete absence of compiler warnings. This
may sound cavalier but many of these warnings are in fact pretty benign.
If you have a comprehensive test suite (as SQLite does) you can assume
that these warnings *are* benign. One widely accepted reason for not
"fixing" them is that most of the fixes (usually casts to similar type)
just clutter up your source code and mask the intentions of the author.
> I only have 8 different types of warnings:
I won't go through them in huge detail because it's hard to say what the
p
RE: [sqlite] compiling with VC++
Did you make the alterations to make the dll VB compatible? RBS -Original Message- From: Dennis Jenkins [mailto:[EMAIL PROTECTED] Sent: 24 February 2007 18:39 To: sqlite-users@sqlite.org Subject: Re: [sqlite] compiling with VC++ RB Smissaert wrote: > Thanks, will have a look at that. > Do you know from experience that it will compile OK with VC6++? > > I use VC 6.0 and it compiles just fine. I did not follow the instructions on the web site. I didn't even read them. I just put all of the C files into a single directory (except for the tcl one and shell.c - leave those out) and build a static library project using the source files. I did use the pre-processed C/H files from the windows zip file though (so I used sqlite.h, not sqlite.h.in). - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] compiling with VC++
Microsoft Visual C++ 6 Enterprise edition. I think it is the latest before .net came in. I am compiling the latest 3.3.13 source as from the URL you mentioned to me. Keep in mind I altered the source to make it VB compatible as in this URL: http://www.tannertech.net/sqlite3vb/index.htm RBS -Original Message- From: Martin Jenkins [mailto:[EMAIL PROTECTED] Sent: 24 February 2007 18:52 To: sqlite-users@sqlite.org Subject: Re: [sqlite] compiling with VC++ RB Smissaert wrote: > Thanks, that is very helpful and reassuring as well. > Will see if I can figure out then what is causing this one: > warning C4028, as you say that could be a typo. I've just built 3.3.12 source with VC6 and didn't see that error. I changed the warning level to 4 and got 500 warnings (signed/unsigned mismatch, oddities in M$ headers, unreferenced parameter NotUsed ;)) but not a C4028 in sight. VC6 is pretty old now - what version are you using? And which sqlite source are you building? Martin - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] compiling with VC++
Downloaded it from the one you pointed to me:
and a zip file - sqlite-source-3_3_13.zip - containing preprocessed source
for Windows users at:
http://www.sqlite.org/download.html
Where else could I get it from?
RBS
-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED]
Sent: 24 February 2007 19:06
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] compiling with VC++
The function signatures at the lines in question look wrong to me.
Do you have the correct version of sqlite3ext.h?
--- RB Smissaert <[EMAIL PROTECTED]> wrote:
> Here examples of all 3, all from the same source file and the same code
> block:
>
> C:\SQLite\SourceWin\loadext.c(138) : warning C4047: 'initializing' : 'int
> (__cdecl *)(struct sqlite3_stmt *,int )' differs in levels of indirection
> from 'const char *(__cdecl *)(struct sqlite3_stmt *,int )'
>
> const sqlite3_api_routines sqlite3_apis = {
> sqlite3_column_double,
>
>
> C:\SQLite\SourceWin\loadext.c(125) : warning C4113: 'int (__cdecl
*)(struct
> sqlite3 *,void *,void (__cdecl *)(void *,struct sqlite3 *,int ,const char
> *))' differs in parameter lists from 'int (__cdecl *)(struct sqlite3 *)'
>
> const sqlite3_api_routines sqlite3_apis = {
> sqlite3_collation_needed,
>
>
> C:\SQLite\SourceWin\loadext.c(208) : warning C4133: 'initializing' :
> incompatible types - from 'void *(__cdecl *)(struct sqlite3 *,void
(__cdecl
> *)(void *,int ,const char *,const char *,__int64 ),void *)' to 'char
> *(__cdecl *)(int ,char *,const char
> *,... )'
>
>
> const sqlite3_api_routines sqlite3_apis = {
> sqlite3_update_hook,
>
>
> This codeblock ends like this:
>
> /*
> ** The original API set ends here. All extensions can call any
> ** of the APIs above provided that the pointer is not NULL. But
> ** before calling APIs that follow, extension should check the
> ** sqlite3_libversion_number() to make sure they are dealing with
> ** a library that is new enough to support that API.
>
*
> */
> sqlite3_overload_function,
> };
>
>
> RBS
>
>
> -Original Message-
> From: Joe Wilson [mailto:[EMAIL PROTECTED]
> Sent: 24 February 2007 17:29
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] compiling with VC++
>
> They're probably harmless.
>
> Even so, can you post the file name/line of one example of each of the
> following 3 warnings as they seem odd:
>
> warning C4047: 'initializing' : '__int64 (__cdecl *)(struct Mem *)'
differs
> in levels of indirection from 'char *(__cdecl *)(const char *,char *)'
>
> warning C4113: '__int64 (__cdecl *)(struct Mem *)' differs in parameter
> lists from 'void *(__cdecl *)(struct sqlite3 *,void (__cdecl *)(void *,int
> ,const char *,const char *,__int64 ),void *)'
>
> warning C4133: 'initializing' : incompatible types - from '__int64
(__cdecl
> *)(struct sqlite3_stmt *,int )' to 'double (__cdecl *)(struct sqlite3_stmt
> *,int )'
>
> --- RB Smissaert <[EMAIL PROTECTED]> wrote:
> > When compiling this source code I get 265 warning, which doesn't really
> > worry me that much as it all seems to be working fine, but in general
what
> > kind of warning should be taken seriously?
> >
> > I only have 8 different types of warnings:
> >
> > warning C4018: '!=' : signed/unsigned mismatch
> > warning C4028: formal parameter 1 different from declaration
> > warning C4047: 'initializing' : '__int64 (__cdecl *)(struct Mem *)'
> differs
> > in levels of indirection from 'char *(__cdecl *)(const char *,char *)'
> > warning C4090: 'function' : different 'const' qualifiers
> > warning C4113: '__int64 (__cdecl *)(struct Mem *)' differs in parameter
> > lists from 'void *(__cdecl *)(struct sqlite3 *,void (__cdecl *)(void
*,int
> > ,const char *,const char *,__int64 ),void *)'
> > warning C4133: 'initializing' : incompatible types - from '__int64
> (__cdecl
> > *)(struct sqlite3_stmt *,int )' to 'double (__cdecl *)(struct
sqlite3_stmt
> > *,int )'
> > warning C4244: '+=' : conversion from '__int64 ' to 'int ', possible
loss
> of
> > data
> > warning C4761: integral size mismatch in argument; conversion supplied
Need Mail bonding?
Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.
http://answers.yahoo.com/dir/?link=list&sid=396546091
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] compiling with VC++
Thanks, that sounds it might be something.
How exactly do I do this:
Instead you should disable compiling the loadable module extension by
defining SQLITE_OMIT_LOAD_EXTENSION.
RBS
-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED]
Sent: 24 February 2007 19:24
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] compiling with VC++
The suggested changes the web site recommends are incorrect:
loadext.c
Comment out the following lines is loadext.c by adding 2 back slashes
("//") to the start of the
line. This prevents errors due to our changes.
sqlite3_changes,
sqlite3_close,
sqlite3_last_insert_rowid,
sqlite3_libversion,
sqlite3_libversion_number,
sqlite3_open,
sqlite3_open16,
Instead you should disable compiling the loadable module extension by
defining SQLITE_OMIT_LOAD_EXTENSION.
--- RB Smissaert <[EMAIL PROTECTED]> wrote:
> Microsoft Visual C++ 6 Enterprise edition.
> I think it is the latest before .net came in.
>
> I am compiling the latest 3.3.13 source as from the URL you mentioned to
me.
> Keep in mind I altered the source to make it VB compatible as in this URL:
>
> http://www.tannertech.net/sqlite3vb/index.htm
>
> RBS
>
>
> -Original Message-
> From: Martin Jenkins [mailto:[EMAIL PROTECTED]
> Sent: 24 February 2007 18:52
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] compiling with VC++
>
> RB Smissaert wrote:
> > Thanks, that is very helpful and reassuring as well.
> > Will see if I can figure out then what is causing this one:
> > warning C4028, as you say that could be a typo.
>
> I've just built 3.3.12 source with VC6 and didn't see that error. I
> changed the warning level to 4 and got 500 warnings (signed/unsigned
> mismatch, oddities in M$ headers, unreferenced parameter NotUsed ;)) but
> not a C4028 in sight. VC6 is pretty old now - what version are you
> using? And which sqlite source are you building?
>
> Martin
Be a PS3 game guru.
Get your game face on with the latest PS3 news and previews at Yahoo! Games.
http://videogames.yahoo.com/platform?platform=120121
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] compiling with VC++
Will give that a try and see if it gives any less warnings. RBS -Original Message- From: Martin Jenkins [mailto:[EMAIL PROTECTED] Sent: 24 February 2007 19:21 To: sqlite-users@sqlite.org Subject: Re: [sqlite] compiling with VC++ RB Smissaert wrote: > Did you make the alterations to make the dll VB compatible? I don't think Dennis was building the VB version. The lines that Todd says you have to comment out - did you comment them out or replace them with a 0? I haven't analysed the code fully but I'm a bit suspicious about that // mod. Firstly, comments in loadext.c says new functions have to go at the end of the list to maintain backwards compatibility, so it sounds like the order is significant and 2) when sqlite3_global_recover was deprecated it was replaced with 0 rather than being removed completely, which also suggests that the order is significant. On that basis I'd expect to see the line ... sqlite3_changes, ... replaced with ... 0, // sqlite3_changes, ... and not ... // sqlite3_changes, ... I can't comment on why Todd chose to remove those lines. Martin - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] compiling with VC++
> You're confusing me with someone else. No, I know it wasn't you. Could try with MinGW and MSYS and it may give less warnings, but would it produce a dll that is any better? If possible I want to avoid installing more software when I already have VC6++. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 24 February 2007 19:45 To: sqlite-users@sqlite.org Subject: RE: [sqlite] compiling with VC++ --- RB Smissaert <[EMAIL PROTECTED]> wrote: > Downloaded it from the one you pointed to me: You're confusing me with someone else. Here's a way to build sqlite3.dll without Microsoft tools using the official sources: Grab and install MinGW and MSYS (google for it), download http://sqlite.org/sqlite-3.3.13.tar.gz, launch the bash shell and issue these commands: tar xzvf sqlite-3.3.13.tar.gz cd sqlite-3.3.13 ./configure make sqlite3.dll > > and a zip file - sqlite-source-3_3_13.zip - containing preprocessed source > for Windows users at: > > http://www.sqlite.org/download.html > > Where else could I get it from? > > RBS Want to start your own business? Learn how on Yahoo! Small Business. http://smallbusiness.yahoo.com/r-index - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] compiling with VC++
Ok, thanks. I am not a C coder and was following some (faulty?)
instructions.
How would I do this:
compile with -DSQLITE_OMIT_LOAD_EXTENSION=1 to leave it out.
I mean where do I put it?
RBS
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 24 February 2007 20:06
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] compiling with VC++
"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> All C4028 warnings originate from this code block:
>
> const sqlite3_api_routines sqlite3_apis = {
[...]
> sqlite3_busy_timeout,
> //sqlite3_changes,
> //sqlite3_close,
> sqlite3_collation_needed,
> sqlite3_collation_needed16,
>
> Not sure it is relevant but some lines are commented out to do with making
> it VB compatible.
>
Yes, it is relevant. Commenting out those lines breaks the
code in a big way.
If that module is given you problems, why don't you compile
with -DSQLITE_OMIT_LOAD_EXTENSION=1 to leave it out.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] compiling with VC++
Sure, but it seems I messed up the source code, so maybe VC6++ is not to blame. Not sure now how to correct it though. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 24 February 2007 20:37 To: sqlite-users@sqlite.org Subject: RE: [sqlite] compiling with VC++ --- RB Smissaert <[EMAIL PROTECTED]> wrote: > Could try with MinGW and MSYS and it may give less warnings, but would it > produce a dll that is any better? If possible I want to avoid installing > more software when I already have VC6++. It's completely up to you. How much time do you want to spend on learning VC6 when you have an alternative that is known to work? No more VC++ for me, thanks. Good luck. Need Mail bonding? Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users. http://answers.yahoo.com/dir/?link=list&sid=396546091 - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] compiling with VC++
OK, when I do instead of commenting those lines out:
0, //sqlite3_last_insert_rowid,
Etc. I get less warnings. Got 143 now instead 265 and no more
Warnings of the type C4028 and nil anymore in the .c file loadext.c
So, that looks much better.
Is it OK though to do this:
0, //sqlite3_libversion,
0, //sqlite3_libversion_number,
Or should I put something different than that zero, like an empty string?
If so how do I do that in C?
It looks then that although it compiled and worked those instructions from
Tod Tanner weren't quite right then.
RBS
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 24 February 2007 20:06
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] compiling with VC++
"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> All C4028 warnings originate from this code block:
>
> const sqlite3_api_routines sqlite3_apis = {
[...]
> sqlite3_busy_timeout,
> //sqlite3_changes,
> //sqlite3_close,
> sqlite3_collation_needed,
> sqlite3_collation_needed16,
>
> Not sure it is relevant but some lines are commented out to do with making
> it VB compatible.
>
Yes, it is relevant. Commenting out those lines breaks the
code in a big way.
If that module is given you problems, why don't you compile
with -DSQLITE_OMIT_LOAD_EXTENSION=1 to leave it out.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] compiling with VC++
I think it compiles much better now when I do in loadext.c :
const sqlite3_api_routines sqlite3_apis = {
sqlite3_aggregate_context,
sqlite3_aggregate_count,
sqlite3_bind_blob,
sqlite3_bind_double,
sqlite3_bind_int,
sqlite3_bind_int64,
sqlite3_bind_null,
sqlite3_bind_parameter_count,
sqlite3_bind_parameter_index,
sqlite3_bind_parameter_name,
sqlite3_bind_text,
sqlite3_bind_text16,
sqlite3_bind_value,
sqlite3_busy_handler,
sqlite3_busy_timeout,
0, //sqlite3_changes,
0, //sqlite3_close,
sqlite3_collation_needed,
sqlite3_collation_needed16,
sqlite3_column_blob,
sqlite3_column_bytes,
sqlite3_column_bytes16,
sqlite3_column_count,
sqlite3_column_database_name,
sqlite3_column_database_name16,
sqlite3_column_decltype,
sqlite3_column_decltype16,
sqlite3_column_double,
sqlite3_column_int,
sqlite3_column_int64,
sqlite3_column_name,
sqlite3_column_name16,
sqlite3_column_origin_name,
sqlite3_column_origin_name16,
sqlite3_column_table_name,
sqlite3_column_table_name16,
sqlite3_column_text,
sqlite3_column_text16,
sqlite3_column_type,
sqlite3_column_value,
sqlite3_commit_hook,
sqlite3_complete,
sqlite3_complete16,
sqlite3_create_collation,
sqlite3_create_collation16,
sqlite3_create_function,
sqlite3_create_function16,
sqlite3_create_module,
sqlite3_data_count,
sqlite3_db_handle,
sqlite3_declare_vtab,
sqlite3_enable_shared_cache,
sqlite3_errcode,
sqlite3_errmsg,
sqlite3_errmsg16,
sqlite3_exec,
sqlite3_expired,
sqlite3_finalize,
sqlite3_free,
sqlite3_free_table,
sqlite3_get_autocommit,
sqlite3_get_auxdata,
sqlite3_get_table,
0, /* Was sqlite3_global_recover(), but that function is deprecated */
sqlite3_interrupt,
0, //sqlite3_last_insert_rowid,
0, //sqlite3_libversion,
0, //sqlite3_libversion_number,
sqlite3_malloc,
sqlite3_mprintf,
0, //sqlite3_open,
0, //sqlite3_open16,
sqlite3_prepare,
sqlite3_prepare16,
sqlite3_profile,
sqlite3_progress_handler,
sqlite3_realloc,
sqlite3_reset,
sqlite3_result_blob,
sqlite3_result_double,
sqlite3_result_error,
sqlite3_result_error16,
sqlite3_result_int,
sqlite3_result_int64,
sqlite3_result_null,
sqlite3_result_text,
sqlite3_result_text16,
sqlite3_result_text16be,
sqlite3_result_text16le,
sqlite3_result_value,
sqlite3_rollback_hook,
sqlite3_set_authorizer,
sqlite3_set_auxdata,
sqlite3_snprintf,
sqlite3_step,
sqlite3_table_column_metadata,
sqlite3_thread_cleanup,
sqlite3_total_changes,
sqlite3_trace,
sqlite3_transfer_bindings,
sqlite3_update_hook,
sqlite3_user_data,
sqlite3_value_blob,
sqlite3_value_bytes,
sqlite3_value_bytes16,
sqlite3_value_double,
sqlite3_value_int,
sqlite3_value_int64,
sqlite3_value_numeric_type,
sqlite3_value_text,
sqlite3_value_text16,
sqlite3_value_text16be,
sqlite3_value_text16le,
sqlite3_value_type,
sqlite3_vmprintf,
/*
** The original API set ends here. All extensions can call any
** of the APIs above provided that the pointer is not NULL. But
** before calling APIs that follow, extension should check the
** sqlite3_libversion_number() to make sure they are dealing with
** a library that is new enough to support that API.
*
*/
sqlite3_overload_function,
};
Instead of commenting out those lines. I now get much less warnings and only
of the type: integral size mismatch and similar.
Due to me not knowing C I hadn't realized that this structure had to be left
intact. Looking through the postings in the SQLite NG these kind of warnings
are quite common and maybe this is as good as it gets.
RBS
-Original Message-
From: Martin Jenkins [mailto:[EMAIL PROTECTED]
Sent: 24 February 2007 23:38
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] compiling with VC++
RB Smissaert wrote:
> Will give that a try and see if it gives any less warnings.
OK, but as Dennis suggested and Dr Hipp has confirmed this isn't the way
to go. I was assuming the VB thing was a bit of a hack and didn't really
read enough of the C code.
Martin
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] compiling with VC++
OK, I understand better now. This though doesn't seem quite right to me: 0, //sqlite3_libversion, It looks sqlite3_libversion should be a string. Would it matter that I make it 0, rather than say ""? If it should be an empty string then how would I do that in C? RBS -Original Message- From: Martin Jenkins [mailto:[EMAIL PROTECTED] Sent: 25 February 2007 02:52 To: sqlite-users@sqlite.org Subject: Re: [sqlite] compiling with VC++ RB Smissaert wrote: > Due to me not knowing C I hadn't realized that this structure had to be left > intact. It's not a C thing as such, it's the way Dr Hipp has designed it. It's an interface - the third entry in the list always does the same thing but the function that actually runs to do that thing can be changed, possibly at runtime. You can disable entries by setting them to 0 (if the infrastructure knows about it) but if you swap list entries around (or delete them) things will break. That's where the VB DLL instructions fell down. Martin - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] compiling with VC++
> No, this is a list of function pointers Ah, I see now. This all a bit alien for a simple VBA coder like me. Thanks for clearing that up and I suppose I got it right now then. RBS -Original Message- From: Martin Jenkins [mailto:[EMAIL PROTECTED] Sent: 25 February 2007 09:46 To: sqlite-users@sqlite.org Subject: Re: [sqlite] compiling with VC++ RB Smissaert wrote: > OK, I understand better now. This though doesn't seem quite right to > me: > > 0, //sqlite3_libversion, > > It looks sqlite3_libversion should be a string. No, this is a list of function pointers. C has no notion of the *name* of the function, only its address. Functions are called by writing that address with a following "()". If you used a string containing the name of the function you end up trying to execute ASCII text. This is never going to work, though it is the basis of the buffer overflow technique that our friends the virus writers use. :( > Would it matter that I make it 0, rather than say ""? If it should be > an empty string then how would I do that in C? You're confusing 0 the string terminator with 0 the NULL pointer. "" is an empty string but it has an address and at that address is a 0 which (as defined by the C standard) terminates the string. 0 in a pointer context tells the compiler to generate a special (NULL) pointer which cannot be dereferenced. The C standard defines that 0 in source code means NULL pointer but the implementation value is not defined. That is, a NULL pointer may or may not be numerically 0 in the binary object but usually is. It matters because Dr Hipp has set it up that way. Wherever it is that these functions are dispatched, there will be a NULL pointer check. If the pointer is NULL then no attempt is made to run the function. As I said above, "" has an address so it will pass the not NULL test and an attempt will be made to execute whatever that string pointer points at (0 followed by ???) and...crash. The real trouble with this is that execution might proceed for a while or even end up at a random but legitimate location within the program code, meaning the crash occurs some time later and far away from the actual bug location. M$ have something called DEP in XP which is meant to prevent this kind of thing, but it needs a reasonably up to date CPU. We might be in danger of getting coughed soon. ;) Martin - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Locked database
Came across a situation where it was impossible to delete or rename a SQLite db file even after the application (Excel) that had locked the database was closed. It wasn't me, so I don't have very exact information, but there was a statement to create a table and to insert data. For some reason this couldn't be completed and there was a db file and the corresponding journal file. I thought that it should be that closing the app that initiated the db connection should always release the handle to the db and allow deletes of renames of the file. If this is not so then should I maybe write the db handle to a safe place, like an .ini file, so I could use it later to close the db. Also would there be a way to release this db lock without a reboot of the PC? Thanks for any advice. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Locked database
Thanks; that must be a possibility. Will pass it on in case it happens again. RBS -Original Message- From: Guy Hachlili [mailto:[EMAIL PROTECTED] Sent: 25 February 2007 14:26 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Locked database Hello. At 13:37 2/25/2007 +, you wrote: >Came across a situation where it was impossible to delete or rename a SQLite >db file even after the application (Excel) that had locked the database was >closed. It wasn't me, so I don't have very exact information, but there was >a statement to create a table and to insert data. For some reason this >couldn't be completed and there was a db file and the corresponding journal >file. >I thought that it should be that closing the app that initiated the db >connection should always release the handle to the db and allow deletes of >renames of the file. > >If this is not so then should I maybe write the db handle to a safe place, >like an .ini file, so I could use it later to close the db. >Also would there be a way to release this db lock without a reboot of the >PC? I have had a lot of experience with Excel crashing the UI and leaving some Excel application running in the background. Open the task manager (Ctrl+Shift+Esc) and check to see if you have any Excel leftovers in the Processes tab (NOT in the Applications tab!). If you do, just select them and use the End Process button to close them. The DB file should be freed. Guy - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] What is wrong with this simple query (offset)?
Why does this query give a syntax error near offset? SELECT Name FROM SQLITE_MASTER WHERE TYPE = 'table' ORDER BY 1 ASC offset 2 limit works fine. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] What is wrong with this simple query (offset)?
I can see now that doing SELECT name FROM SQLITE_MASTER WHERE TYPE = 'table' ORDER BY 1 ASC limit -1 offset 2 works, but it doesn't do what I was trying and that is to keep the field name out. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 06 March 2007 19:40 To: sqlite-users@sqlite.org Subject: [sqlite] What is wrong with this simple query (offset)? Why does this query give a syntax error near offset? SELECT Name FROM SQLITE_MASTER WHERE TYPE = 'table' ORDER BY 1 ASC offset 2 limit works fine. RBS - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: What is wrong with this simple query (offset)?
Yes, thanks I understand now. I take it there is no way to make it not return the field name. RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 06 March 2007 19:53 To: SQLite Subject: [sqlite] Re: What is wrong with this simple query (offset)? RB Smissaert <[EMAIL PROTECTED]> wrote: > Why does this query give a syntax error near offset? > > SELECT > Name > FROM SQLITE_MASTER > WHERE TYPE = 'table' > ORDER BY 1 ASC > offset 2 The syntax doesn't allow OFFSET on its own, but only together with LIMIT. Make it LIMIT -1 OFFSET 2 -- or LIMIT 2, -1 -1 means no limit. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: What is wrong with this simple query (offset)?
Ah, thanks. I am not using the sqlite3 command line utility, but a VB adapted version of the regular sqlite3.dll. I take it should be no problem to do the same with that. Is there a pragma for this? RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 06 March 2007 20:28 To: SQLite Subject: [sqlite] Re: Re: What is wrong with this simple query (offset)? RB Smissaert <[EMAIL PROTECTED]> wrote: > I take it there is no way to make it not return the field name. I'm not sure what you mean by "not return the field name". My wild guess is you are using sqlite3 command line utility. In this case, type .header OFF before running the query. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] What is wrong with this simple query (offset)?
OK, thanks. Will have a look now in the code where the field names are added to the array and will see if I can re-code this to make it optional. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 06 March 2007 21:22 To: sqlite-users@sqlite.org Subject: Re: [sqlite] What is wrong with this simple query (offset)? "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Ah, thanks. > I am not using the sqlite3 command line utility, but a VB adapted version of > the regular sqlite3.dll. I take it should be no problem to do the same with > that. Is there a pragma for this? > No. The returning of field names is going to be a function of your VB interface layer. The SQLite core does not do this unless requested - which means that your VB interface is requesting it. So turning of the field names is going to be some sort of call into your VB interface, not into the SQLite core. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] What is wrong with this simple query (offset)?
Yes, I can see now where this happens:
TempWideDataHolder = CoTaskMemAlloc(1);
// Set up array bounds
SA_Bounds[0].cElements = NumberRows + 1;
SA_Bounds[0].lLbound = 0;
SA_Bounds[1].cElements = NumberofCols;
SA_Bounds[1].lLbound = 0;
//Create array
resultp = SafeArrayCreate(VT_VARIANT, 2, SA_Bounds);
// Start Place column headers in first row
for (intCurrentColumn=0;intCurrentColumn< (ULONG)NumberofCols
;intCurrentColumn++) {
long indices[] = {0,intCurrentColumn};
VariantInit(&tmpVariant);
// Start Convert to unicode
WideCharacterLength=strlen(SQL_Results[intCurrentColumn]) + 1 ;
*TempWideDataHolder = (LPOLESTR)
CoTaskMemAlloc(WideCharacterLength*2);
MultiByteToWideChar( CP_ACP, 0, SQL_Results[intCurrentColumn],
strlen(SQL_Results[intCurrentColumn])+1, *TempWideDataHolder,
WideCharacterLength );
//Convert the VT_Object element to the BSTR - Here we may change if
SQLite type casts
tmpVariant.bstrVal=SysAllocString(*TempWideDataHolder);
V_VT(&tmpVariant) = VT_BSTR;
CoTaskMemFree(*TempWideDataHolder);
// End convert to unicode
// Safearray Column headers
hr = SafeArrayPutElement(resultp, indices, &tmpVariant);
SysFreeString(tmpVariant.bstrVal);
VariantClear(&tmpVariant);
}
// End Place column headers in first row
// Start Loop through array and fill SAFEARRAY
for (intCurrentRow=1;intCurrentRow<=(ULONG)NumberRows
;intCurrentRow++) {
for (intCurrentColumn=0;intCurrentColumn< (ULONG)NumberofCols
;intCurrentColumn++) {
long indices[] = {intCurrentRow , intCurrentColumn};
// set return array index
VariantInit(&tmpVariant);
sqlite_return_array_int= (intCurrentRow * (ULONG)NumberofCols) +
intCurrentColumn ;
// Start Convert to unicode
if (SQL_Results[sqlite_return_array_int]) {
WideCharacterLength=strlen(SQL_Results[sqlite_return_array_int])
+ 1 ;
*TempWideDataHolder= (LPOLESTR)
CoTaskMemAlloc(WideCharacterLength*2);
MultiByteToWideChar( CP_ACP, 0,
SQL_Results[sqlite_return_array_int],
strlen(SQL_Results[sqlite_return_array_int])+1,
*TempWideDataHolder,
WideCharacterLength );
//Convert the VT_Object element to the BSTR -
//Here we may change if SQLite type casts
tmpVariant.bstrVal=SysAllocString(*TempWideDataHolder);
V_VT(&tmpVariant) = VT_BSTR;
CoTaskMemFree(*TempWideDataHolder);
// End convert to unicode
hr = SafeArrayPutElement(resultp, indices, &tmpVariant);
SysFreeString(tmpVariant.bstrVal);
VariantClear(&tmpVariant);
}
}
}
// END Loop through array and fill SAFEARRAY
}
}
else
{
*ErrMsg = SysAllocStringByteLen( ErrMessage,strlen( ErrMessage) );
}
Now I will have to figure out how to re-code this.
RBS
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 06 March 2007 21:22
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What is wrong with this simple query (offset)?
"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> Ah, thanks.
> I am not using the sqlite3 command line utility, but a VB adapted version
of
> the regular sqlite3.dll. I take it should be no problem to do the same
with
> that. Is there a pragma for this?
>
No.
The returning of field names is going to be a function of
your VB interface layer. The SQLite core does not do this
unless requested - which means that your VB interface is
requesting it. So turning of the field names is going to
be some sort of call into your VB interface, not into the
SQLite core.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] What is wrong with this simple query (offset)?
Could I ask if somebody could tell me how I add an optional Boolean argument
to this:
SAFEARRAY * __stdcall sqlite_get_table(
sqlite3 *db, /* The database on which the SQL executes */
const char *zSql, /* The SQL to be executed */
BSTR *ErrMsg /* Write error messages here */
) /* Return the SAFEARRAY */
I had a go, but I know nil about C and it isn't that simple.
Once I have the Boolean argument (variable) I probably manage to do the if
To either put the fields in the array or leave them out.
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 06 March 2007 21:45
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] What is wrong with this simple query (offset)?
Yes, I can see now where this happens:
TempWideDataHolder = CoTaskMemAlloc(1);
// Set up array bounds
SA_Bounds[0].cElements = NumberRows + 1;
SA_Bounds[0].lLbound = 0;
SA_Bounds[1].cElements = NumberofCols;
SA_Bounds[1].lLbound = 0;
//Create array
resultp = SafeArrayCreate(VT_VARIANT, 2, SA_Bounds);
// Start Place column headers in first row
for (intCurrentColumn=0;intCurrentColumn< (ULONG)NumberofCols
;intCurrentColumn++) {
long indices[] = {0,intCurrentColumn};
VariantInit(&tmpVariant);
// Start Convert to unicode
WideCharacterLength=strlen(SQL_Results[intCurrentColumn]) + 1 ;
*TempWideDataHolder = (LPOLESTR)
CoTaskMemAlloc(WideCharacterLength*2);
MultiByteToWideChar( CP_ACP, 0, SQL_Results[intCurrentColumn],
strlen(SQL_Results[intCurrentColumn])+1, *TempWideDataHolder,
WideCharacterLength );
//Convert the VT_Object element to the BSTR - Here we may change if
SQLite type casts
tmpVariant.bstrVal=SysAllocString(*TempWideDataHolder);
V_VT(&tmpVariant) = VT_BSTR;
CoTaskMemFree(*TempWideDataHolder);
// End convert to unicode
// Safearray Column headers
hr = SafeArrayPutElement(resultp, indices, &tmpVariant);
SysFreeString(tmpVariant.bstrVal);
VariantClear(&tmpVariant);
}
// End Place column headers in first row
// Start Loop through array and fill SAFEARRAY
for (intCurrentRow=1;intCurrentRow<=(ULONG)NumberRows
;intCurrentRow++) {
for (intCurrentColumn=0;intCurrentColumn< (ULONG)NumberofCols
;intCurrentColumn++) {
long indices[] = {intCurrentRow , intCurrentColumn};
// set return array index
VariantInit(&tmpVariant);
sqlite_return_array_int= (intCurrentRow * (ULONG)NumberofCols) +
intCurrentColumn ;
// Start Convert to unicode
if (SQL_Results[sqlite_return_array_int]) {
WideCharacterLength=strlen(SQL_Results[sqlite_return_array_int])
+ 1 ;
*TempWideDataHolder= (LPOLESTR)
CoTaskMemAlloc(WideCharacterLength*2);
MultiByteToWideChar( CP_ACP, 0,
SQL_Results[sqlite_return_array_int],
strlen(SQL_Results[sqlite_return_array_int])+1,
*TempWideDataHolder,
WideCharacterLength );
//Convert the VT_Object element to the BSTR -
//Here we may change if SQLite type casts
tmpVariant.bstrVal=SysAllocString(*TempWideDataHolder);
V_VT(&tmpVariant) = VT_BSTR;
CoTaskMemFree(*TempWideDataHolder);
// End convert to unicode
hr = SafeArrayPutElement(resultp, indices, &tmpVariant);
SysFreeString(tmpVariant.bstrVal);
VariantClear(&tmpVariant);
}
}
}
// END Loop through array and fill SAFEARRAY
}
}
else
{
*ErrMsg = SysAllocStringByteLen( ErrMessage,strlen( ErrMessage) );
}
Now I will have to figure out how to re-code this.
RBS
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 06 March 2007 21:22
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What is wrong with this simple query (offset)?
"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> Ah, thanks.
> I am not using the sqlite3 command line utility, but a VB adapted version
of
> the regular sqlite3.dll. I take it should be no problem to do the same
with
> that. Is there a pragma for this?
>
No.
The returning of field names is going to be a function of
your VB interface layer. The SQLite core does not do this
unless requested - which means that your VB interface is
requesting it. So turning of the field names is going to
be some sort of call into your VB interface, not into the
SQLite core.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
RE: [sqlite] What is wrong with this simple query (offset)?
OK, forget about this, I think I am nearly there.
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 06 March 2007 22:42
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] What is wrong with this simple query (offset)?
Could I ask if somebody could tell me how I add an optional Boolean argument
to this:
SAFEARRAY * __stdcall sqlite_get_table(
sqlite3 *db, /* The database on which the SQL executes */
const char *zSql, /* The SQL to be executed */
BSTR *ErrMsg /* Write error messages here */
) /* Return the SAFEARRAY */
I had a go, but I know nil about C and it isn't that simple.
Once I have the Boolean argument (variable) I probably manage to do the if
To either put the fields in the array or leave them out.
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 06 March 2007 21:45
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] What is wrong with this simple query (offset)?
Yes, I can see now where this happens:
TempWideDataHolder = CoTaskMemAlloc(1);
// Set up array bounds
SA_Bounds[0].cElements = NumberRows + 1;
SA_Bounds[0].lLbound = 0;
SA_Bounds[1].cElements = NumberofCols;
SA_Bounds[1].lLbound = 0;
//Create array
resultp = SafeArrayCreate(VT_VARIANT, 2, SA_Bounds);
// Start Place column headers in first row
for (intCurrentColumn=0;intCurrentColumn< (ULONG)NumberofCols
;intCurrentColumn++) {
long indices[] = {0,intCurrentColumn};
VariantInit(&tmpVariant);
// Start Convert to unicode
WideCharacterLength=strlen(SQL_Results[intCurrentColumn]) + 1 ;
*TempWideDataHolder = (LPOLESTR)
CoTaskMemAlloc(WideCharacterLength*2);
MultiByteToWideChar( CP_ACP, 0, SQL_Results[intCurrentColumn],
strlen(SQL_Results[intCurrentColumn])+1, *TempWideDataHolder,
WideCharacterLength );
//Convert the VT_Object element to the BSTR - Here we may change if
SQLite type casts
tmpVariant.bstrVal=SysAllocString(*TempWideDataHolder);
V_VT(&tmpVariant) = VT_BSTR;
CoTaskMemFree(*TempWideDataHolder);
// End convert to unicode
// Safearray Column headers
hr = SafeArrayPutElement(resultp, indices, &tmpVariant);
SysFreeString(tmpVariant.bstrVal);
VariantClear(&tmpVariant);
}
// End Place column headers in first row
// Start Loop through array and fill SAFEARRAY
for (intCurrentRow=1;intCurrentRow<=(ULONG)NumberRows
;intCurrentRow++) {
for (intCurrentColumn=0;intCurrentColumn< (ULONG)NumberofCols
;intCurrentColumn++) {
long indices[] = {intCurrentRow , intCurrentColumn};
// set return array index
VariantInit(&tmpVariant);
sqlite_return_array_int= (intCurrentRow * (ULONG)NumberofCols) +
intCurrentColumn ;
// Start Convert to unicode
if (SQL_Results[sqlite_return_array_int]) {
WideCharacterLength=strlen(SQL_Results[sqlite_return_array_int])
+ 1 ;
*TempWideDataHolder= (LPOLESTR)
CoTaskMemAlloc(WideCharacterLength*2);
MultiByteToWideChar( CP_ACP, 0,
SQL_Results[sqlite_return_array_int],
strlen(SQL_Results[sqlite_return_array_int])+1,
*TempWideDataHolder,
WideCharacterLength );
//Convert the VT_Object element to the BSTR -
//Here we may change if SQLite type casts
tmpVariant.bstrVal=SysAllocString(*TempWideDataHolder);
V_VT(&tmpVariant) = VT_BSTR;
CoTaskMemFree(*TempWideDataHolder);
// End convert to unicode
hr = SafeArrayPutElement(resultp, indices, &tmpVariant);
SysFreeString(tmpVariant.bstrVal);
VariantClear(&tmpVariant);
}
}
}
// END Loop through array and fill SAFEARRAY
}
}
else
{
*ErrMsg = SysAllocStringByteLen( ErrMessage,strlen( ErrMessage) );
}
Now I will have to figure out how to re-code this.
RBS
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 06 March 2007 21:22
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What is wrong with this simple query (offset)?
"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> Ah, thanks.
> I am not using the sqlite3 command line utility, but a VB adapted version
of
> the regular sqlite3.dll. I take it should be no problem to do the same
with
> that. Is there a pragma for this?
>
No.
The returning of field names is going to be a function of
your VB interface layer. The SQLite core does not do this
unless requested - which means that your VB interface is
requesting it. So turning of the field names is going to
be some sort of call into your VB interface, not into the
SQLite core.
--
D. Richard Hipp <[EMAIL PROTECTED]>
--
RE: [sqlite] What is wrong with this simple query (offset)?
uot;3.3.13" /* Source Code Version */
SAFEARRAY * __stdcall sqlite_get_table(sqlite3 * , const char *, BSTR *,
int);
BSTR __stdcall sqlite_libversion(void);
int __stdcall number_of_rows_from_last_call(void);
And this my function in VB:
Private Declare Function sqlite_get_table _
Lib "SQLite3VB.dll" _
(ByVal DB_Handle As Long, _
ByVal SQLString As String, _
ByRef ErrStr As String, ByVal iFields As
Long) As Variant()
Have tried ByRef iFields as long as well, but same error.
Any obvious mistakes here? I know this is not that much to do with SQLite,
but maybe it is something simple I am doing wrong and somebody could tell
me, other than that I should learn C first.
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 06 March 2007 23:14
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] What is wrong with this simple query (offset)?
OK, forget about this, I think I am nearly there.
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 06 March 2007 22:42
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] What is wrong with this simple query (offset)?
Could I ask if somebody could tell me how I add an optional Boolean argument
to this:
SAFEARRAY * __stdcall sqlite_get_table(
sqlite3 *db, /* The database on which the SQL executes */
const char *zSql, /* The SQL to be executed */
BSTR *ErrMsg /* Write error messages here */
) /* Return the SAFEARRAY */
I had a go, but I know nil about C and it isn't that simple.
Once I have the Boolean argument (variable) I probably manage to do the if
To either put the fields in the array or leave them out.
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 06 March 2007 21:45
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] What is wrong with this simple query (offset)?
Yes, I can see now where this happens:
TempWideDataHolder = CoTaskMemAlloc(1);
// Set up array bounds
SA_Bounds[0].cElements = NumberRows + 1;
SA_Bounds[0].lLbound = 0;
SA_Bounds[1].cElements = NumberofCols;
SA_Bounds[1].lLbound = 0;
//Create array
resultp = SafeArrayCreate(VT_VARIANT, 2, SA_Bounds);
// Start Place column headers in first row
for (intCurrentColumn=0;intCurrentColumn< (ULONG)NumberofCols
;intCurrentColumn++) {
long indices[] = {0,intCurrentColumn};
VariantInit(&tmpVariant);
// Start Convert to unicode
WideCharacterLength=strlen(SQL_Results[intCurrentColumn]) + 1 ;
*TempWideDataHolder = (LPOLESTR)
CoTaskMemAlloc(WideCharacterLength*2);
MultiByteToWideChar( CP_ACP, 0, SQL_Results[intCurrentColumn],
strlen(SQL_Results[intCurrentColumn])+1, *TempWideDataHolder,
WideCharacterLength );
//Convert the VT_Object element to the BSTR - Here we may change if
SQLite type casts
tmpVariant.bstrVal=SysAllocString(*TempWideDataHolder);
V_VT(&tmpVariant) = VT_BSTR;
CoTaskMemFree(*TempWideDataHolder);
// End convert to unicode
// Safearray Column headers
hr = SafeArrayPutElement(resultp, indices, &tmpVariant);
SysFreeString(tmpVariant.bstrVal);
VariantClear(&tmpVariant);
}
// End Place column headers in first row
// Start Loop through array and fill SAFEARRAY
for (intCurrentRow=1;intCurrentRow<=(ULONG)NumberRows
;intCurrentRow++) {
for (intCurrentColumn=0;intCurrentColumn< (ULONG)NumberofCols
;intCurrentColumn++) {
long indices[] = {intCurrentRow , intCurrentColumn};
// set return array index
VariantInit(&tmpVariant);
sqlite_return_array_int= (intCurrentRow * (ULONG)NumberofCols) +
intCurrentColumn ;
// Start Convert to unicode
if (SQL_Results[sqlite_return_array_int]) {
WideCharacterLength=strlen(SQL_Results[sqlite_return_array_int])
+ 1 ;
*TempWideDataHolder= (LPOLESTR)
CoTaskMemAlloc(WideCharacterLength*2);
MultiByteToWideChar( CP_ACP, 0,
SQL_Results[sqlite_return_array_int],
strlen(SQL_Results[sqlite_return_array_int])+1,
*TempWideDataHolder,
WideCharacterLength );
//Convert the VT_Object element to the BSTR -
//Here we may change if SQLite type casts
tmpVariant.bstrVal=SysAllocString(*TempWideDataHolder);
V_VT(&tmpVariant) = VT_BSTR;
CoTaskMemFree(*TempWideDataHolder);
// End convert to unicode
hr = SafeArrayPutElement(resultp, indices, &tmpVariant);
SysFreeString(tmpVariant.bstrVal);
VariantClear(&tmpVariant);
}
}
}
// END Loop through array and fill SAFEARRAY
}
}
else
{
*ErrMsg = SysAl
RE: [sqlite] What is wrong with this simple query (offset)?
Thanks, will have a look at that, but I am sure I am calling the new dll as the declaration in VB doesn't mention the full path and I point to the dll by changing the curdir. RBS -Original Message- From: Trey Mack [mailto:[EMAIL PROTECTED] Sent: 07 March 2007 13:12 To: sqlite-users@sqlite.org Subject: Re: [sqlite] What is wrong with this simple query (offset)? > It looks all as it should work and it compiles with the same number of > warnings, but I get a bad dll calling convention in VB with the extra > integer argument iFields. You've changed the signature of the method you're calling, and it looks like you changed it correctly in the VB declaration. Maybe you have an older version of the DLL with the older signature in your system32 directory? It's possible this older version is being loaded, and that would cause the error you see. There's a method called sqlite_libversion in that dll that returns VB_SQLITE_VERSION (#defined in vbsql.h). Mine's "3.3.8c" now. Added the extra letter just so I could make sure I have the right version of the dll loaded. HTH, - Trey - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] What is wrong with this simple query (offset)?
You were absolutely right, I didn't call the new dll. All solved now. RBS -Original Message- From: Trey Mack [mailto:[EMAIL PROTECTED] Sent: 07 March 2007 13:12 To: sqlite-users@sqlite.org Subject: Re: [sqlite] What is wrong with this simple query (offset)? > It looks all as it should work and it compiles with the same number of > warnings, but I get a bad dll calling convention in VB with the extra > integer argument iFields. You've changed the signature of the method you're calling, and it looks like you changed it correctly in the VB declaration. Maybe you have an older version of the DLL with the older signature in your system32 directory? It's possible this older version is being loaded, and that would cause the error you see. There's a method called sqlite_libversion in that dll that returns VB_SQLITE_VERSION (#defined in vbsql.h). Mine's "3.3.8c" now. Added the extra letter just so I could make sure I have the right version of the dll loaded. HTH, - Trey - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] journal - "Unable to open the database file"
If I remember well this is a problem if you have the extension .db for the database file and I think if you change that to something like .db3 then it won't happen. RBS -Original Message- From: Allan, Mark [mailto:[EMAIL PROTECTED] Sent: 09 March 2007 16:23 To: sqlite-users@sqlite.org Subject: [sqlite] journal - "Unable to open the database file" I have been using SQLite as a replacement for MS Access for use in a PC application. It works well but we have been experiencing the following problem when the database file is located on a network drive. We are getting "Unable to open the database file" when trying to start a new transaction. Investigating this it appeared the reason this was happening was that the journal file is open by another process and cannot be deleted. SQLite cannot delete it and neither can I manually via explorer. After much investiagtion with out IT department it looks like the reason this was open is that Microsoft Windows Desktop Search (a file indexer program) has opened it. I am still unsure why it keeps it open and does not allow for anyone other than an admin to delete it. A file may stay in this state for hours before mysteriously disappearing of its own accord. If we configure Windows Desktop Search to ignore the folder, we do not get (or at least we have not yet got) this problem. Basically I would like to:- 1) Make this problem known to the community. 2) Ask if anyone has experienced anything like this? 3) Ask if anyone can offer any advise us as to what we can do to get around the problem of a journal file being locked and SQLite cannot delete it? Because at the moment if this situation occurs no one can write to that Db until the journal file has gone. Which is quite a severe problem... Thanks Mark DISCLAIMER: This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] journal - "Unable to open the database file"
Doesn't have to be .db3, but there are certain extension to avoid as posted. RBS -Original Message- From: Allan, Mark [mailto:[EMAIL PROTECTED] Sent: 09 March 2007 16:52 To: sqlite-users@sqlite.org Subject: RE: [sqlite] journal - "Unable to open the database file" RB Smissaert, The database file is named .vdb. Is .vdb ok or wrong? Does it have to be .db3? Or is there just certian extensions I need to avoid? Dr Hipp, Currently all journals have been deleted by admin, so I am currently trying to create another one to find out exactly that information for you. > -Original Message- > From: RB Smissaert [mailto:[EMAIL PROTECTED] > Sent: 09 March 2007 16:40 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] journal - "Unable to open the database file" > > > If I remember well this is a problem if you have the > extension .db for the > database file and I think if you change that to something > like .db3 then it > won't happen. > > RBS > > -Original Message- > From: Allan, Mark [mailto:[EMAIL PROTECTED] > Sent: 09 March 2007 16:23 > To: sqlite-users@sqlite.org > Subject: [sqlite] journal - "Unable to open the database file" > > I have been using SQLite as a replacement for MS Access for > use in a PC > application. It works well but we have been experiencing the following > problem when the database file is located on a network drive. > We are getting > "Unable to open the database file" when trying to start a new > transaction. > Investigating this it appeared the reason this was happening > was that the > journal file is open by another process and cannot be > deleted. SQLite cannot > delete it and neither can I manually via explorer. After much > investiagtion > with out IT department it looks like the reason this was open is that > Microsoft Windows Desktop Search (a file indexer program) has > opened it. I > am still unsure why it keeps it open and does not allow for > anyone other > than an admin to delete it. A file may stay in this state for > hours before > mysteriously disappearing of its own accord. If we configure > Windows Desktop > Search to ignore the folder, we do not get (or at least we > have not yet got) > this problem. > > Basically I would like to:- > 1) Make this problem known to the community. > 2) Ask if anyone has experienced anything like this? > 3) Ask if anyone can offer any advise us as to what we can do > to get around > the problem of a journal file being locked and SQLite cannot > delete it? > Because at the moment if this situation occurs no one can > write to that Db > until the journal file has gone. Which is quite a severe problem... > > Thanks > > Mark > > > DISCLAIMER: > This information and any attachments contained in this email > message is > intended only for the use of the individual or entity to which it is > addressed and may contain information that is privileged, > confidential, and > exempt from disclosure under applicable law. If the reader > of this message > is not the intended recipient, or the employee or agent > responsible for > delivering the message to the intended recipient, you are > hereby notified > that any dissemination, distribution, forwarding, or copying of this > communication is strictly prohibited. If you have received this > communication in error, please notify the sender immediately by return > email, and delete the original message immediately. > > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > > DISCLAIMER: This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] What is wrong with this UPDATE?
UPDATE A3SQLADC_J SET ADDED_DATE = '' WHERE ADDED_DATE = 0 OR ADDED_DATE IS NULL, START_DATE = '' WHERE START_DATE = 0 OR START_DATE IS NULL near ",": syntax error I am sure I have run this before with no trouble. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: What is wrong with this UPDATE?
Yes, thanks, that works indeed. RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 11 March 2007 02:55 To: SQLite Subject: [sqlite] Re: What is wrong with this UPDATE? RB Smissaert <[EMAIL PROTECTED]> wrote: > UPDATE > A3SQLADC_J > SET > ADDED_DATE = '' WHERE ADDED_DATE = 0 OR ADDED_DATE IS NULL, > START_DATE = '' WHERE START_DATE = 0 OR START_DATE IS NULL > > near ",": syntax error Make it UPDATE A3SQLADC_J SET ADDED_DATE = (case when ADDED_DATE = 0 OR ADDED_DATE IS NULL then '' else ADDED_DATE end), START_DATE = (case when START_DATE = 0 OR START_DATE IS NULL then '' else START_DATE end); Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Question about speed of CASE WHEN
Looking at the fastest way to convert a field in a table and wonder if in general an update with a CASE WHEN construction or an update with a join to a lookup table is faster. These conversions are of this type: UPDATE A3SQL77D_J SET ENTRY_TYPE = (case when ENTRY_TYPE = 9 then 'Issue when ENTRY_TYPE = 2 then 'Note' when ENTRY_TYPE = 1 then 'Encounter' when ENTRY_TYPE = 8 then 'Authorisation' when ENTRY_TYPE = 11 then 'Prescription' when ENTRY_TYPE = 5 then 'Treatment' when ENTRY_TYPE = 3 then 'Problem' when ENTRY_TYPE = 13 then 'Discontinuation' when ENTRY_TYPE = 6 then 'Reminder' when ENTRY_TYPE = 14 then 'Adverse reaction' when ENTRY_TYPE = -1 then 'Unknown' when ENTRY_TYPE = 4 then 'Sub-problem' when ENTRY_TYPE = 7 then 'Battery' when ENTRY_TYPE = 10 then 'Return-Script' else ENTRY_TYPE end) So, an integer value to be converted to a limited number of strings. I could figure this out with some testing, but maybe there are some general rules that apply to this. Also, would the order of the CASE WHEN options make a difference, speed-wise, so would it be faster to have the WHEN options in decreasing order of frequency? RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Question about speed of CASE WHEN
> Perhaps the alternative form of the CASE statement would be faster What form is that? RBS -Original Message- From: T&B [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 12:41 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Question about speed of CASE WHEN Hi RBS, > Looking at the fastest way to convert a field in a table and wonder > if in > general an update with a CASE WHEN construction or an update with a > join to > a lookup table is faster. Perhaps the alternative form of the CASE statement would be faster, but I don't know: case ENTRY_TYPE when 9 then 'Issue when 2 then 'Note' when 1 then 'Encounter' when 8 then 'Authorisation' when 11 then 'Prescription' when 5 then 'Treatment' when 3 then 'Problem' when 13 then 'Discontinuation' when 6 then 'Reminder' when 14 then 'Adverse reaction' when -1 then 'Unknown' when 4 then 'Sub-problem' when 7 then 'Battery' when 10 then 'Return-Script' else ENTRY_TYPE end Tom - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Question about speed of CASE WHEN
Sorry, had to rush off and missed your alternative. Will do some testing now. RBS -Original Message- From: T&B [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 14:55 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Question about speed of CASE WHEN Hi RBS, >> Perhaps the alternative form of the CASE statement would be faster > > What form is that? The form that I showed in my previous email. That is, using: > case ENTRY_TYPE > when 9 then 'Issue > when 2 then 'Note' etc instead of: >> case >> when ENTRY_TYPE = 9 then 'Issue >> when ENTRY_TYPE = 2 then 'Note' etc Tom - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Question about speed of CASE WHEN
Done some testing now and surprisingly, it seems the lookup method with a join to a lookup table is very slightly faster than the CASE WHEN construction. There isn't much in it though. Also tried the shorter CASE WHEN construction, like this: > case ENTRY_TYPE > when 9 then 'Issue > when 2 then 'Note' Etc But didn't get that to work as it always produced the first WHEN option. RBS -Original Message- From: Christian Smith [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 15:29 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Question about speed of CASE WHEN RB Smissaert uttered: > Looking at the fastest way to convert a field in a table and wonder if in > general an update with a CASE WHEN construction or an update with a join to > a lookup table is faster. My guess is that the CASE WHEN form will be faster for small number of possibilities. It compiles into a straight list of compare instructions. > > These conversions are of this type: > > UPDATE A3SQL77D_J > SET ENTRY_TYPE = (case > when ENTRY_TYPE = 9 then 'Issue > when ENTRY_TYPE = 2 then 'Note' > when ENTRY_TYPE = 1 then 'Encounter' > when ENTRY_TYPE = 8 then 'Authorisation' > when ENTRY_TYPE = 11 then 'Prescription' > when ENTRY_TYPE = 5 then 'Treatment' > when ENTRY_TYPE = 3 then 'Problem' > when ENTRY_TYPE = 13 then 'Discontinuation' > when ENTRY_TYPE = 6 then 'Reminder' > when ENTRY_TYPE = 14 then 'Adverse reaction' > when ENTRY_TYPE = -1 then 'Unknown' > when ENTRY_TYPE = 4 then 'Sub-problem' > when ENTRY_TYPE = 7 then 'Battery' > when ENTRY_TYPE = 10 then 'Return-Script' > else ENTRY_TYPE end) > > So, an integer value to be converted to a limited number of strings. > I could figure this out with some testing, but maybe there are some general > rules that apply to this. Testing is probably the best way. However, the difference in speed may not be significant given the increased maintainability of the table lookup based solution. > > Also, would the order of the CASE WHEN options make a difference, > speed-wise, so would it be faster to have the WHEN options in decreasing > order of frequency? The sequence of code generated compares the cases in the order written. So the common cases should go first. > > RBS > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Question about speed of CASE WHEN
Ok, now done some better testing and the method with CASE WHEN is indeed, as expected a bit faster, I would say about a third. I have only tested this with some 8 different convert values, so maybe it will be different if there are much more different values to convert. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 17:17 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Question about speed of CASE WHEN Done some testing now and surprisingly, it seems the lookup method with a join to a lookup table is very slightly faster than the CASE WHEN construction. There isn't much in it though. Also tried the shorter CASE WHEN construction, like this: > case ENTRY_TYPE > when 9 then 'Issue > when 2 then 'Note' Etc But didn't get that to work as it always produced the first WHEN option. RBS -Original Message- From: Christian Smith [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 15:29 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Question about speed of CASE WHEN RB Smissaert uttered: > Looking at the fastest way to convert a field in a table and wonder if in > general an update with a CASE WHEN construction or an update with a join to > a lookup table is faster. My guess is that the CASE WHEN form will be faster for small number of possibilities. It compiles into a straight list of compare instructions. > > These conversions are of this type: > > UPDATE A3SQL77D_J > SET ENTRY_TYPE = (case > when ENTRY_TYPE = 9 then 'Issue > when ENTRY_TYPE = 2 then 'Note' > when ENTRY_TYPE = 1 then 'Encounter' > when ENTRY_TYPE = 8 then 'Authorisation' > when ENTRY_TYPE = 11 then 'Prescription' > when ENTRY_TYPE = 5 then 'Treatment' > when ENTRY_TYPE = 3 then 'Problem' > when ENTRY_TYPE = 13 then 'Discontinuation' > when ENTRY_TYPE = 6 then 'Reminder' > when ENTRY_TYPE = 14 then 'Adverse reaction' > when ENTRY_TYPE = -1 then 'Unknown' > when ENTRY_TYPE = 4 then 'Sub-problem' > when ENTRY_TYPE = 7 then 'Battery' > when ENTRY_TYPE = 10 then 'Return-Script' > else ENTRY_TYPE end) > > So, an integer value to be converted to a limited number of strings. > I could figure this out with some testing, but maybe there are some general > rules that apply to this. Testing is probably the best way. However, the difference in speed may not be significant given the increased maintainability of the table lookup based solution. > > Also, would the order of the CASE WHEN options make a difference, > speed-wise, so would it be faster to have the WHEN options in decreasing > order of frequency? The sequence of code generated compares the cases in the order written. So the common cases should go first. > > RBS > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Question about speed of CASE WHEN
I use SQLite as a data manipulator, not as a database. I get data from a server database, dump to SQLite, manipulate the data and finally dump to Excel. As this is reporting software speed is important, so I will go with the fastest method. RBS -Original Message- From: Gerry Snyder [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 23:46 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Question about speed of CASE WHEN RB Smissaert wrote: > Ok, now done some better testing and the method with CASE WHEN is indeed, as > expected a bit faster To me the lookup table method seems like exactly what a relational database is used for. The CASE WHEN would have to be dramatically faster, and in an area where timing was critical, for me to choose that way. If there were thousands of items, and changes were frequent, you wouldn't even consider CASE WHEN, would you? Remember: timing isn't important, except when it is. Gerry - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Any way to do this faster?
Simplified I have the following situation: 2 tables, tableA and tableB both with an integer field, called ID, holding unique integer numbers in tableA and non-unique integer numbers in tableB. Both tables have an index on this field and for tableA this is an INTEGER PRIMARY KEY. Now I need to delete the rows in tableB where this number doesn't appear in the corresponding field in tableA. Currently I do this with this SQL: Delete from tableB where ID not in (select tableA.ID from tableA) When table tableB gets big (say some 10 rows) this will get a bit slow and I wonder if there is a better way to do this. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Any way to do this faster?
Had a good look at this now and doing: delete from tableB where not exists (select id from tableA where tableA.id = tableB.id) Is indeed quite a bit faster than doing: delete from tableB where id not in (select tableA.id from tableA) In my case about 3 times as fast. Looking at the query plan with EXPLAIN QUERY PLAN was a good tip! I think though that I gained more by looking more carefully when to put the index on the id column. I need the index, but not before the delete, so I gained a lot by creating the index after the delete. This meant less rows to index plus less work to be done with the delete. Probably there will be more places in my app where looking at the timing of the index creation will speed things up, so thanks again for the advice. RBS -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 26 March 2007 18:16 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Any way to do this faster? [EMAIL PROTECTED] wrote: > > I will need an index on that field later, but > I could drop it prior to the delete and create > a new one after. > > Don't do that. If you need the index, then leave it as is. > Thinking about it I am not sure in any case of the > value of an index after deletes on a table. > Is it usually better to re-index after deletes? > > Indexes are updated automatically as records are added and deleted from a table, that's why they add overhead if they are not serving some purpose. Your index will be correct after you delete the records from tableB. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Difference in these indices?
Is there any difference in an index created like this: Create table 'table1'([ID] INTEGER PRIMARY KEY) with this: Create table 'table1'([ID] INTEGER) Create unique index idx_table1_ID on table1(ID) I tended to use the first form, but as that can make subsequent table inserts or deletes slower I am now moving to the second form. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Difference in these indices?
Thanks for that.
So if I can then I should create the table with INTEGER PRIMARY KEY.
Is it right that this won't affect the speed of any subsequent inserts or
deletes?
About the single quotes etc:
This is VB code, so I can't do:
Create table "table1"("ID" INTEGER PRIMARY KEY)
I can do:
Create table table1(ID INTEGER PRIMARY KEY)
As the table and the columns are often variables it will be something like:
strTable = "table1"
strColumn = "ID"
strSQL = "create " & strTable & "(" & strColumn & " INTEGER PRIMARY KEY)"
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 27 March 2007 22:51
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Difference in these indices?
RB Smissaert wrote:
> Is there any difference in an index created like this:
>
> Create table 'table1'([ID] INTEGER PRIMARY KEY)
>
> with this:
>
> Create table 'table1'([ID] INTEGER)
>
> Create unique index idx_table1_ID on table1(ID)
>
> I tended to use the first form, but as that can make subsequent table
> inserts or deletes slower I am now moving to the second form.
>
>
Yes there is. The first uses the key for the btree that stores the table
to hold the id. The second uses a second comlpetely independent btree to
store an index that holds records that contain the id number and the
rowid of the corresponding record in the table. The table itself
contains a rowid as the key of the table btree and the user id field.
SQLite version 3.3.13
Enter ".help" for instructions
sqlite> Create table 'table1'([ID] INTEGER PRIMARY KEY);
sqlite> select * from sqlite_master;
table|table1|table1|2|CREATE TABLE 'table1'([ID] INTEGER PRIMARY KEY)
SQLite version 3.3.13
Enter ".help" for instructions
sqlite> Create table 'table1'([ID] INTEGER);
sqlite> Create unique index idx_table1_ID on table1(ID);
sqlite> select * from sqlite_master;
table|table1|table1|2|CREATE TABLE 'table1'([ID] INTEGER)
index|idx_table1_ID|table1|3|CREATE UNIQUE INDEX idx_table1_ID on table1(ID)
The first version stores on integer for each record, and stores it in
the btree key. The second stores four integers for each record, two in
the table record and two in the index record. You are making your
database much larger for no reason.
Also, you are using literal strings (delimited with a single quote) for
your table names. This is not standard SQL and will not be portable. You
are also using square brackets to quote your column names. This is also
an SQLite and MS extension to standard SQL. You should quote identifiers
such as table and column names with double quotes.
Create table 'table1'([ID] INTEGER PRIMARY KEY)
should be:
Create table "table1"("ID" INTEGER PRIMARY KEY)
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Difference in these indices?
Thanks for clarifying that. I think all the data to be inserted in tables with an INTEGER PRIMARY KEY will be sorted on that key, but I will have to check as sometimes these tables can be big, say a few million rows. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 27 March 2007 23:34 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Difference in these indices? "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Thanks for that. > So if I can then I should create the table with INTEGER PRIMARY KEY. > Is it right that this won't affect the speed of any subsequent inserts or > deletes? > That depends on the data. If you insert records in order of ascending integer primary key, then the inserts will be very fast. If you insert records where the integer primary key is randomized, inserts will be reasonably fast until the size of your table exceeds the size of your disk cache. Then each insert will need to do multiple reads and writes to disk as it tries to figure out where in your massive table is the right place to put the new record, and then make space for that new record. All this disk I/O will slow things down dramatically. Every table has an integer primary key whether you declare one or not. If you do not specify an integer primary key then one is created for you automatically named "ROWID" or "OID". If you do not specify a value for the integer primary key when inserting, a value is selected automatically. The value selected is one more than the largest existing integer primary key in that table. That means that if you do not specify integer primary keys, the keys choosen are in ascending order and inserts are very fast. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Difference in these indices?
Looks then that doing the table creation with INTEGER PRIMARY KEY
Is the way to go, but as always it will come down to a lot of testing.
As to quotes etc.
As my code works fine as it is I probably will leave this as the double
quotes look ugly and it will be a reasonably big job to alter all this.
Did I get you right that the only benefit of doing create "table1" etc.
is compatibility with running sqlite with SQLite.exe?
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 27 March 2007 23:41
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Difference in these indices?
RB Smissaert wrote:
> Is it right that this won't affect the speed of any subsequent inserts or
> deletes?
>
Well inserts will be done in id order. If you have predefined ids
assigned by some outside source and specify them when you insert into
sqlite, it will have to insert at random location in the btree. This
will take longer than always appending at the end of the btree. If you
let sqlite assign the ids, or the ids are in order, then this is not an
issue. If you are always going to create the external index afterwards
anyway, it will also probably not make much difference (you would have
to test it each way).
> About the single quotes etc:
> This is VB code, so I can't do:
> Create table "table1"("ID" INTEGER PRIMARY KEY)
>
> I can do:
> Create table table1(ID INTEGER PRIMARY KEY)
>
> As the table and the columns are often variables it will be something
like:
>
> strTable = "table1"
> strColumn = "ID"
>
> strSQL = "create " & strTable & "(" & strColumn & " INTEGER PRIMARY KEY)"
>
>
>
VB and SQL both use the same technique of escaping quotes embedded in
strings using a pair of quotes back to back.
In VB
print "Test ""quoted"" strings."
will output
Test "quoted" strings.
You can do the same with the strings you are building to send to SQLite.
Using the following VB statement
strSQL = "create """ & strTable & """(""" & strColumn & """ INTEGER
PRIMARY KEY)"
will produce a strSQL that contains the string
create "table1"("ID" INTEGER PRIMARY KEY)
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Difference in these indices?
Dennis,
OK, you convinced me and I think I will alter this.
Does this only apply to table and column names?
I will never use double quote characters in my identifier
names, so there should be no problem there.
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 28 March 2007 15:41
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Difference in these indices?
RB Smissaert wrote:
> As to quotes etc.
> As my code works fine as it is I probably will leave this as the double
> quotes look ugly and it will be a reasonably big job to alter all this.
> Did I get you right that the only benefit of doing create "table1" etc.
> is compatibility with running sqlite with SQLite.exe?
>
>
The benefit to using standard quoting for identifiers is portability.
Your table definitions will almost certainly be rejected by almost any
other database engine. Most don't support the same extended quoting
rules that sqlite has added for compatibility with files coming from
other sources.
If adding the escaped quotes to the SQL generation statements doesn't
work for you, then you could create a simple function that adds the
escaped quotes to your identifier variables. If you simplify the problem
and assume you will never use double quote characters in your identifier
names themselves this function is very simple;
Function Quote(id As String) As String
Quote = """" & id & """"
End Function
and your code becomes something like this.
strTable = "table1"
strColumn = "ID"
strSQL = "create " & Quote(strTable) & "(" & Quote(strColumn) & "
INTEGER PRIMARY KEY)"
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Difference in these indices?
Ok, thanks. A bit more work then to deal with all the indices. Just one question; as I log nearly all my SQL statements to a SQLite table, will this be OK with the double quotes added? RBS -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 28 March 2007 16:10 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Difference in these indices? RB Smissaert wrote: > Does this only apply to table and column names? > I will never use double quote characters in my identifier > names, so there should be no problem there. > > It applies to all the identifiers: table, column, index, trigger, database (using attach as id), transactions (if named which they seldom are), collation, and view names. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Search on Age, from DOB column
I am also working with a clinical application, using SQLite and VBA.
I use this function to produce the SQL to convert dates in the ISO8601
format to an integer age.
Function ISO8601Date2Age(strField, Optional strAlias As String) As String
Dim strAS As String
If Len(strAlias) > 0 Then
strAS = " AS "
End If
ISO8601Date2Age = "case when date(" & strField & ", '+' || " & _
"(strftime('%Y', 'now') - strftime('%Y', " & strField &
")) || " & _
"' years') <= date('now') then " & _
"strftime('%Y', 'now') - strftime('%Y', " & strField &
") " & _
"else " & _
"strftime('%Y', 'now') - strftime('%Y', " & strField &
") -1 End" & _
strAS & strAlias
End Function
You may not be coding in VB, but you will get the idea.
RBS
-Original Message-
From: Allan, Mark [mailto:[EMAIL PROTECTED]
Sent: 03 May 2007 11:57
To: sqlite-users@sqlite.org
Subject: [sqlite] Search on Age, from DOB column
Hi,
I need to be able offer the user the ability to search for patients in the
database based on age. i.e. age > 17 or age = 45 etc etc...
I only store the patient DOB in the database however, what is the SQL to
achive this? Can I subract todays date from the DOB and get the number of
years within an SQL string?
The patient table is similar to:-
Patients
{
INTEGER PrimaryKey;
TEXT Surname;
TEXT FirstName;
TIMESTAMP DOB;
...
...
...
}
Thanks in advance for your help.
Mark
DISCLAIMER:
This information and any attachments contained in this email message is
intended only for the use of the individual or entity to which it is
addressed and may contain information that is privileged, confidential, and
exempt from disclosure under applicable law. If the reader of this message
is not the intended recipient, or the employee or agent responsible for
delivering the message to the intended recipient, you are hereby notified
that any dissemination, distribution, forwarding, or copying of this
communication is strictly prohibited. If you have received this
communication in error, please notify the sender immediately by return
email, and delete the original message immediately.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] excel and sqlite
I have been using SQLite in Excel for the last half year now (in a commercial application) and I think I will be able to help. Currently I am using the wrapper written by Olaf Schmidt and this works very well: www.datenhaus.de/Downloads/dhSQLite-Demo.zip If you are interested then I can send you a demo workbook. RBS -Original Message- From: steve31415 [mailto:[EMAIL PROTECTED] Sent: 07 May 2007 18:03 To: sqlite-users@sqlite.org Subject: [sqlite] excel and sqlite Hi, I am developing a VBA app in excel 2003 and I would like to know how to setup sqlite so that I can use it. I am a newbie when it comes to databases. Currently I have installed the sqlite ODBC driver (www.ch-werner.de/sqliteodbc/) and have created a database using sqlitebrowser (http://sourceforge.net/projects/sqlitebrowser/). What should I do next? Thanks for any suggestions -- View this message in context: http://www.nabble.com/excel-and-sqlite-tf3705051.html#a10361323 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Longest "real" SQL statement
This is one of my biggest and it is part of a number of queries to transpose a table: INSERT OR REPLACE INTO A3BP619_J(PATIENT_ID, ENTRY_ID_E1, START_DATE_E1, ADDED_DATE_E1, SYST_E1, DIAST_E1, ENTRY_ID_E2, START_DATE_E2, ADDED_DATE_E2, SYST_E2, DIAST_E2, ENTRY_ID_E3, START_DATE_E3, ADDED_DATE_E3, SYST_E3, DIAST_E3, ENTRY_ID_E4, START_DATE_E4, ADDED_DATE_E4, SYST_E4, DIAST_E4, ENTRY_ID_E5, START_DATE_E5, ADDED_DATE_E5, SYST_E5, DIAST_E5, ENTRY_ID_E6, START_DATE_E6, ADDED_DATE_E6, SYST_E6, DIAST_E6, ENTRY_ID_E7, START_DATE_E7, ADDED_DATE_E7, SYST_E7, DIAST_E7, ENTRY_ID_E8, START_DATE_E8, ADDED_DATE_E8, SYST_E8, DIAST_E8, ENTRY_ID_E9, START_DATE_E9, ADDED_DATE_E9, SYST_E9, DIAST_E9, ENTRY_ID_E10, START_DATE_E10, ADDED_DATE_E10, SYST_E10, DIAST_E10, ENTRY_ID_E11, START_DATE_E11, ADDED_DATE_E11, SYST_E11, DIAST_E11, ENTRY_ID_E12, START_DATE_E12, ADDED_DATE_E12, SYST_E12, DIAST_E12, ENTRY_ID_E13, START_DATE_E13, ADDED_DATE_E13, SYST_E13, DIAST_E13, ENTRY_ID_E14, START_DATE_E14, ADDED_DATE_E14, SYST_E14, DIAST_E14, ENTRY_ID_E15, START_DATE_E15, ADDED_DATE_E15, SYST_E15, DIAST_E15, ENTRY_ID_E16, START_DATE_E16, ADDED_DATE_E16, SYST_E16, DIAST_E16, ENTRY_ID_E17, START_DATE_E17, ADDED_DATE_E17, SYST_E17, DIAST_E17, ENTRY_ID_E18, START_DATE_E18, ADDED_DATE_E18, SYST_E18, DIAST_E18, ENTRY_ID_E19, START_DATE_E19, ADDED_DATE_E19, SYST_E19, DIAST_E19, ENTRY_ID_E20, START_DATE_E20, ADDED_DATE_E20, SYST_E20, DIAST_E20, ENTRY_ID_E21, START_DATE_E21, ADDED_DATE_E21, SYST_E21, DIAST_E21, ENTRY_ID_E22, START_DATE_E22, ADDED_DATE_E22, SYST_E22, DIAST_E22, ENTRY_ID_E23, START_DATE_E23, ADDED_DATE_E23, SYST_E23, DIAST_E23, ENTRY_ID_E24, START_DATE_E24, ADDED_DATE_E24, SYST_E24, DIAST_E24, ENTRY_ID_E25, START_DATE_E25, ADDED_DATE_E25, SYST_E25, DIAST_E25, ENTRY_ID_E26, START_DATE_ <<---etc.--->> T JOIN GROUP_39 g39 ON (t1.PATIENT_ID = g39.PID) LEFT JOIN GROUP_40 g40 ON (t1.PATIENT_ID = g40.PID) It can be a lot longer even in Excel 2007 as that has many more available columns. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 10 May 2007 00:33 To: sqlite-users@sqlite.org Subject: [sqlite] Longest "real" SQL statement I'm looking for an upper bound on how big legitimate SQL statements handed to SQLite get to be. I'm not interested in contrived examples. I want to see really big SQL statements that are actually used in real programs. "Big" can be defined in several ways: * Number of bytes of text in the SQL statement. * Number of tokens in the SQL statement * Number of result columns in a SELECT * Number of terms in an expression If you are using really big SQL statements, please tell me about them. I'd like to see the actual SQL text if possible. But if your use is proprietary, please at least tell me how big your query is in bytes or tokens or columns or expression terms. Thanks. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Age calculation on literal
Thanks to Dennis Cote I got a nice way to get the age from the date in the
form '-nmm-dd'. It works fine when I run it on a field, but when I run
it on a literal date it gives me 100 too much:
select
case when
date('2002-01-01', '+' || (strftime('%Y', 'now') -
strftime('%Y', '2002-01-01')) || ' years') <= date('now')
then
strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
else
strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
end
Why is this?
RBS
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Age calculation on literal
Sorry, forget about this, it was something in the VBA code, so nil to do
with SQLite.
Could I ask you how I would get the age in months? I can see it will be
along similar lines, but maybe you have worked it out already.
I need it to be full calendar months, so, if current date is 2007-05-31 then
DOB Age in months
--
2007-05-01 0
2007-04-30 1
2007-01-01 4
Etc.
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 31 May 2007 22:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal
RB Smissaert wrote:
> Thanks to Dennis Cote I got a nice way to get the age from the date in the
> form '-nmm-dd'. It works fine when I run it on a field, but when I run
> it on a literal date it gives me 100 too much:
>
> select
> case when
> date('2002-01-01', '+' || (strftime('%Y', 'now') -
> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
> then
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
> else
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
> end
>
> Why is this?
>
>
This works for me in the sqlite shell as shown below:
C:\Documents and Settings\DennisC>sqlite3
SQLite version 3.3.15
Enter ".help" for instructions
sqlite>
sqlite> select
...> case when
...> date('2002-01-01', '+' || (strftime('%Y', 'now') -
...> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
...> then
...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
...> else
...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
...> end
...> ;
5
How are you running this query?
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Age calculation on literal
> How are you running this query?
I am running this from Excel VBA with a free wrapper from Olaf Schmidt,
dhSQLite, based on 3.3.17. I will check my code, but can't think of a way
why it should add 100 with literals and not on table fields.
Must admit I have been wrong before with these kind of things ...
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 31 May 2007 22:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal
RB Smissaert wrote:
> Thanks to Dennis Cote I got a nice way to get the age from the date in the
> form '-nmm-dd'. It works fine when I run it on a field, but when I run
> it on a literal date it gives me 100 too much:
>
> select
> case when
> date('2002-01-01', '+' || (strftime('%Y', 'now') -
> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
> then
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
> else
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
> end
>
> Why is this?
>
>
This works for me in the sqlite shell as shown below:
C:\Documents and Settings\DennisC>sqlite3
SQLite version 3.3.15
Enter ".help" for instructions
sqlite>
sqlite> select
...> case when
...> date('2002-01-01', '+' || (strftime('%Y', 'now') -
...> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
...> then
...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
...> else
...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
...> end
...> ;
5
How are you running this query?
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Age calculation on literal
Got this nearly worked out now, but somehow I can't get the nested case when
syntax right:
SELECT
case
when
date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
'2006-10-14')) || ' years') <= date('now')
then
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14'))) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
end
else
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1) * 12 +
(strftime('%m', 'now') +
(12 - strftime('%m', '2006-10-14' -1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1) * 12 +
(strftime('%m', 'now') +
(12 - strftime('%m', '2006-10-14')))
end
end
It will give me an error (from my VB wrapper) syntax error near else.
Any idea what is wrong here?
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 31 May 2007 22:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal
RB Smissaert wrote:
> Thanks to Dennis Cote I got a nice way to get the age from the date in the
> form '-nmm-dd'. It works fine when I run it on a field, but when I run
> it on a literal date it gives me 100 too much:
>
> select
> case when
> date('2002-01-01', '+' || (strftime('%Y', 'now') -
> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
> then
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
> else
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
> end
>
> Why is this?
>
>
This works for me in the sqlite shell as shown below:
C:\Documents and Settings\DennisC>sqlite3
SQLite version 3.3.15
Enter ".help" for instructions
sqlite>
sqlite> select
...> case when
...> date('2002-01-01', '+' || (strftime('%Y', 'now') -
...> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
...> then
...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
...> else
...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
...> end
...> ;
5
How are you running this query?
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Age calculation on literal
Yes, that looks better and thanks for that.
Still get the same error though.
I will keep fiddling with it.
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 01 June 2007 19:53
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal
On 6/1/07, RB Smissaert <[EMAIL PROTECTED]> wrote:
>
> Got this nearly worked out now, but somehow I can't get the nested case
> when
> syntax right:
>
> SELECT
> case
> when
> date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
> '2006-10-14')) || ' years') <= date('now')
> then
> case when
> strftime('%d', 'now') > strftime('%d', '2006-10-14')
> then
> ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
> (strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1
> else
> (strftime('%Y', 'now') - strftime('%Y', '2006-10-14'))) * 12 +
You have an extra closing bracket in the line above.
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
> end
> else
> case when
> strftime('%d', 'now') > strftime('%d', '2006-10-14')
> then
> ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1) * 12 +
> (strftime('%m', 'now') +
> (12 - strftime('%m', '2006-10-14' -1
> else
> (strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1) * 12 +
> (strftime('%m', 'now') +
> (12 - strftime('%m', '2006-10-14')))
> end
> end
>
> It will give me an error (from my VB wrapper) syntax error near else.
> Any idea what is wrong here?
Try this instead, I find the extra indentation makes it easier to see what
you are doing.
SELECT
case when
date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
'2006-10-14')) || ' years') <= date('now')
then
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
end
else
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') +
(12 - strftime('%m', '2006-10-14' -1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') + (12 - strftime('%m', '2006-10-14')))
end
end
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Age calculation on literal
Got this now, after correcting the brackets:
SELECT
case when
date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y', '2006-
10-14')) || ' years') <= date('now') then
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))) - 1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
end
else
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') +
(12 - strftime('%m', '2006-10-14'))) - 1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') + (12 - strftime('%m', '2006-10-14')))
end
end
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 01 June 2007 19:53
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal
On 6/1/07, RB Smissaert <[EMAIL PROTECTED]> wrote:
>
> Got this nearly worked out now, but somehow I can't get the nested case
> when
> syntax right:
>
> SELECT
> case
> when
> date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
> '2006-10-14')) || ' years') <= date('now')
> then
> case when
> strftime('%d', 'now') > strftime('%d', '2006-10-14')
> then
> ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
> (strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1
> else
> (strftime('%Y', 'now') - strftime('%Y', '2006-10-14'))) * 12 +
You have an extra closing bracket in the line above.
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
> end
> else
> case when
> strftime('%d', 'now') > strftime('%d', '2006-10-14')
> then
> ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1) * 12 +
> (strftime('%m', 'now') +
> (12 - strftime('%m', '2006-10-14' -1
> else
> (strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1) * 12 +
> (strftime('%m', 'now') +
> (12 - strftime('%m', '2006-10-14')))
> end
> end
>
> It will give me an error (from my VB wrapper) syntax error near else.
> Any idea what is wrong here?
Try this instead, I find the extra indentation makes it easier to see what
you are doing.
SELECT
case when
date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
'2006-10-14')) || ' years') <= date('now')
then
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
end
else
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') +
(12 - strftime('%m', '2006-10-14' -1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') + (12 - strftime('%m', '2006-10-14')))
end
end
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
