Re: [sqlite] Can I use sqlite3_bind for colmun name or ASC/DESCselector?
Filip, Igor, Thanks for your quick reply and help! I could understand the behavior of binding. I will try to make SQL without binding for these scenarios. Thanks again! minoru 2008/10/26 Igor Tandetnik <[EMAIL PROTECTED]>: > "mfujisaw" <[EMAIL PROTECTED]> wrote in > message > news:[EMAIL PROTECTED] >> I am developping allication with SQLite3 functions. >> When I will use sqlite3_bind and wild card for some SQL, it wouldn't >> work well. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I use sqlite3_bind for colmun name or ASC/DESCselector?
"mfujisaw" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I am developping allication with SQLite3 functions. > When I will use sqlite3_bind and wild card for some SQL, it wouldn't > work well. > > 1. SELECT ? FROM records WHERE age=30 > I want to set some table name into ? every time. A parameter can appear in the statement only where a literal could legally appear. E.g., you could do SELECT * FROM records WHERE age=? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree cast warnings on 64bit OS - strange parameter use
I just wanted to explain the reason of the warning. There's valid reason to emit these warnings as the compiler can not infer the usage from static type analysis. I did not specifically call this use wrong. Mike Am 25.10.2008 um 19:44 schrieb D. Richard Hipp: > > On Oct 25, 2008, at 1:28 PM, Michael Ruck wrote: > >> The problem with these types of C tricks is that they only work >> right if the >> platform they're used on has the property of sizeof(void*) == >> sizeof(int). >> Unfortunately this is not always the case and not mandated by the C >> standard. That's the reason for these warnings. Its also a reason >> not to >> turn off the warnings. >> > > Dan is using the void* to hold a 0 or a 1. So his code will work > correctly as long as sizeof(void*)>=1. It would be a strange machine > indeed that failed to meet that requirement. > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree cast warnings on 64bit OS - strange parameter use
On Oct 25, 2008, at 1:28 PM, Michael Ruck wrote: > The problem with these types of C tricks is that they only work > right if the > platform they're used on has the property of sizeof(void*) == > sizeof(int). > Unfortunately this is not always the case and not mandated by the C > standard. That's the reason for these warnings. Its also a reason > not to > turn off the warnings. > Dan is using the void* to hold a 0 or a 1. So his code will work correctly as long as sizeof(void*)>=1. It would be a strange machine indeed that failed to meet that requirement. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] converting mdb to sqlite using mdbtools
thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree cast warnings on 64bit OS - strange parameter use
The problem with these types of C tricks is that they only work right if the platform they're used on has the property of sizeof(void*) == sizeof(int). Unfortunately this is not always the case and not mandated by the C standard. That's the reason for these warnings. Its also a reason not to turn off the warnings. Mike > -Ursprüngliche Nachricht- > Von: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Im Auftrag von Dan > Gesendet: Samstag, 25. Oktober 2008 19:22 > An: General Discussion of SQLite Database > Betreff: Re: [sqlite] rtree cast warnings on 64bit OS - > strange parameter use > > > On Oct 25, 2008, at 11:40 PM, William Kyngesburye wrote: > > > I added rtree to my sqlite compilation for the first time and got > > these warnings for OSX 64bit: > > > > /Users/Shared/src/sqlite/sqlite-3.6.4/sqlite3.c: In function > > rtreeCreate: > > /Users/Shared/src/sqlite/sqlite-3.6.4/sqlite3.c:94784: > warning: cast > > from pointer to integer of different size > > /Users/Shared/src/sqlite/sqlite-3.6.4/sqlite3.c: In function > > rtreeConnect: > > /Users/Shared/src/sqlite/sqlite-3.6.4/sqlite3.c:94797: > warning: cast > > from pointer to integer of different size > > > > These are in the rtreeCreate() and rtreeConnect() functions, calling > > rtreeInit(): > > > > return rtreeInit(db, pAux, argc, argv, ppVtab, pzErr, 1, (int)pAux); > > > > I thought the two pAux parameters were odd - one bare and > one cast to > > (int), so I looked up rtreeInit(). > > Good point. I removed the first of the two "pAux" parameters > from rtreeInit(). It was not being used. > >http://www.sqlite.org/cvstrac/chngview?cn=5842 > > > static int rtreeInit(sqlite3 *db, void *pAux, int argc, const char > > *const*argv, sqlite3_vtab **ppVtab, char **pzErr, int isCreate, int > > eCoordType) > > > > The first pAux is a pointer, so this one looks correct. But the > > second is an int (eCoordType), and the only two values I > found defined > > are 0 & 1: > > > > #define RTREE_COORD_REAL32 0 > > #define RTREE_COORD_INT32 1 > > > > Forget the cast warnings now - why is pAux used to set the > eCoordType? > > My C skills are pretty basic, so maybe there is some pointer/cast > > magic happening? Or maybe it's simply screwed up? > > The two functions that call rtreeInit() are registered as > callbacks with SQLite. When you register the callback > function you also specify a void* pointer that is passed to > the callback whenever it is invoked. > This is not an uncommon pattern in C code. > > So, since the interface allows us to pass a void* as context > to the callback function, but in this instance we really just > want an integer, the value has to be cast to a void* when the > callback function is registered, and back to an integer when > the callback is invoked. > > A lot of compilers throw a warning when they encounter this. > In my opinion (having never had anything to do with compiler design or > implementation) they shouldn't. > > Dan. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree cast warnings on 64bit OS - strange parameter use
On Oct 25, 2008, at 11:40 PM, William Kyngesburye wrote: > I added rtree to my sqlite compilation for the first time and got > these warnings for OSX 64bit: > > /Users/Shared/src/sqlite/sqlite-3.6.4/sqlite3.c: In function > ‘rtreeCreate’: > /Users/Shared/src/sqlite/sqlite-3.6.4/sqlite3.c:94784: warning: cast > from pointer to integer of different size > /Users/Shared/src/sqlite/sqlite-3.6.4/sqlite3.c: In function > ‘rtreeConnect’: > /Users/Shared/src/sqlite/sqlite-3.6.4/sqlite3.c:94797: warning: cast > from pointer to integer of different size > > These are in the rtreeCreate() and rtreeConnect() functions, calling > rtreeInit(): > > return rtreeInit(db, pAux, argc, argv, ppVtab, pzErr, 1, (int)pAux); > > I thought the two pAux parameters were odd - one bare and one cast to > (int), so I looked up rtreeInit(). Good point. I removed the first of the two "pAux" parameters from rtreeInit(). It was not being used. http://www.sqlite.org/cvstrac/chngview?cn=5842 > static int rtreeInit(sqlite3 *db, void *pAux, int argc, const char > *const*argv, sqlite3_vtab **ppVtab, char **pzErr, int isCreate, int > eCoordType) > > The first pAux is a pointer, so this one looks correct. But the > second is an int (eCoordType), and the only two values I found defined > are 0 & 1: > > #define RTREE_COORD_REAL32 0 > #define RTREE_COORD_INT32 1 > > Forget the cast warnings now - why is pAux used to set the > eCoordType? My C skills are pretty basic, so maybe there is some > pointer/cast magic happening? Or maybe it's simply screwed up? The two functions that call rtreeInit() are registered as callbacks with SQLite. When you register the callback function you also specify a void* pointer that is passed to the callback whenever it is invoked. This is not an uncommon pattern in C code. So, since the interface allows us to pass a void* as context to the callback function, but in this instance we really just want an integer, the value has to be cast to a void* when the callback function is registered, and back to an integer when the callback is invoked. A lot of compilers throw a warning when they encounter this. In my opinion (having never had anything to do with compiler design or implementation) they shouldn't. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I use sqlite3_bind for colmun name or ASC/DESC selector?
No, it's impossible. The main idea of prepared statements and binding is that the statement has to be compiled only once into the virtual machine code that is then executed when you run sqlite3_step. This works only as long as the compiled form isn't affected by the bound data. Binding column names, ASC/DESC, etc. would change the meaning of the query and the compiled code wouldn't be reusable (eg. using ASC or DESC could result in use of different index and so on). Best regards, Filip NAvara On Sat, Oct 25, 2008 at 6:54 PM, mfujisaw <[EMAIL PROTECTED]> wrote: > Hi, guys, > > I am developping allication with SQLite3 functions. > When I will use sqlite3_bind and wild card for some SQL, it wouldn't work > well. > > 1. SELECT ? FROM records WHERE age=30 > I want to set some table name into ? every time. > > 2. SELECT * FROM records WHERE age=30 ORDER BY ? > I want to set some order target into ? every time. > > 3. SELECT * FROM records WHERE age=30 ORDER BY weight ? > I want to select ASC/DESC into ? every time. > > In every those 3 cases, I cant get correct results. > Does any one help me? > Can I make these parameter wildcard? > > > Thanks, > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can I use sqlite3_bind for colmun name or ASC/DESC selector?
Hi, guys, I am developping allication with SQLite3 functions. When I will use sqlite3_bind and wild card for some SQL, it wouldn't work well. 1. SELECT ? FROM records WHERE age=30 I want to set some table name into ? every time. 2. SELECT * FROM records WHERE age=30 ORDER BY ? I want to set some order target into ? every time. 3. SELECT * FROM records WHERE age=30 ORDER BY weight ? I want to select ASC/DESC into ? every time. In every those 3 cases, I cant get correct results. Does any one help me? Can I make these parameter wildcard? Thanks, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] setting the timezone on windows xp
Hi Jay --
I used to have a problem like this a few years back. I don't remember all
the hows and whys, but my apps call the following at start up and the
problems are gone:
_tsetlocale(LC_ALL, _T(""));
_tzset();
HTH
Doug
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:sqlite-users-
> [EMAIL PROTECTED] On Behalf Of Jay Sprenkle
> Sent: Saturday, October 25, 2008 10:32 AM
> To: General Discussion of SQLite Database
> Subject: [sqlite] setting the timezone on windows xp
>
> Hello all,
> this isn't really an sqlite question but it tangentially touches on it.
> Please disregard it if it offends.
>
> I'm trying to write an atom feed exporter for my sqlite database and I
> need
> to export the utc time. My development box, windows xp, insists (both
> in C
> and in SQLite) that UTC is 10 hours different than localtime. I've
> clearly
> set the timezone to US CST in the control panel. I've also tried
> setting
> both the TZ and TIME_ZONE environment variables (CST6CDT). No luck.
>
> Any suggestions?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] rtree cast warnings on 64bit OS - strange parameter use
I added rtree to my sqlite compilation for the first time and got these warnings for OSX 64bit: /Users/Shared/src/sqlite/sqlite-3.6.4/sqlite3.c: In function ‘rtreeCreate’: /Users/Shared/src/sqlite/sqlite-3.6.4/sqlite3.c:94784: warning: cast from pointer to integer of different size /Users/Shared/src/sqlite/sqlite-3.6.4/sqlite3.c: In function ‘rtreeConnect’: /Users/Shared/src/sqlite/sqlite-3.6.4/sqlite3.c:94797: warning: cast from pointer to integer of different size These are in the rtreeCreate() and rtreeConnect() functions, calling rtreeInit(): return rtreeInit(db, pAux, argc, argv, ppVtab, pzErr, 1, (int)pAux); I thought the two pAux parameters were odd - one bare and one cast to (int), so I looked up rtreeInit(). static int rtreeInit(sqlite3 *db, void *pAux, int argc, const char *const*argv, sqlite3_vtab **ppVtab, char **pzErr, int isCreate, int eCoordType) The first pAux is a pointer, so this one looks correct. But the second is an int (eCoordType), and the only two values I found defined are 0 & 1: #define RTREE_COORD_REAL32 0 #define RTREE_COORD_INT32 1 Forget the cast warnings now - why is pAux used to set the eCoordType? My C skills are pretty basic, so maybe there is some pointer/cast magic happening? Or maybe it's simply screwed up? - William Kyngesburye http://www.kyngchaos.com/ [Trillian] What are you supposed to do WITH a maniacally depressed robot? [Marvin] You think you have problems? What are you supposed to do if you ARE a maniacally depressed robot? No, don't try and answer, I'm 50,000 times more intelligent than you and even I don't know the answer... - HitchHiker's Guide to the Galaxy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Segfault in initialiazation
Hi, I am trying to add sqllite3 to a C-Program to replace a hand-made on-disk-datastructure that has proven to be cumbersome to change and inefficient. Unfortunately the program crashes very early in the initialization before the first line of my own code executes, making the problem difficult to debug. The linux-system this is to run on is built from scratch using the T2 build-system (www.t2-project.org). I am cross-compiling the system from an Ubuntu-System for a semi-embedded machine. The program itself is cross-compiled on top of that using standard GNU auto-tools. The sqlite3 command-line tool works fine, so I suspect the library itself has been built OK, and that I am doing something wrong in my compile-and-link stuff. Because as said above, none of my own code get's executed, the program never reaches main(). Are there any common pitfalls for beginners like me that could cause this problem? The only idea I had so far is running both programs with strace. The output of strace for sqlite3 is at http://www.marian-aldenhoevel.de/tmp/sqlite.txt And the output from my program is at http://www.marian-aldenhoevel.de/tmp/kbox.txt I cannot see anything obvious that precedes the problem. Ciao, MM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] converting mdb to sqlite using mdbtools
On Sat, 25 Oct 2008 18:32:20 +0300, "Muayyad AlSadi" <[EMAIL PROTECTED]> wrote in General Discussion of SQLite Database : >I wrote an article which can be found here >http://www.cltb.net/en/articles/mdb2sqlite.html > >please update the note on http://www.sqlite.org/cvstrac/wiki?p=ConverterTools >which says >Note: I don't think it supports SQLite right now, but this project can >be continued and used for that purpose. > >to point to my article It's a wiki, you can edit it yourself. Click the [Edit] link in the bar below the site menu. >thank you -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] converting mdb to sqlite using mdbtools
I wrote an article which can be found here http://www.cltb.net/en/articles/mdb2sqlite.html please update the note on http://www.sqlite.org/cvstrac/wiki?p=ConverterTools which says Note: I don't think it supports SQLite right now, but this project can be continued and used for that purpose. to point to my article thank you ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] setting the timezone on windows xp
Hello all, this isn't really an sqlite question but it tangentially touches on it. Please disregard it if it offends. I'm trying to write an atom feed exporter for my sqlite database and I need to export the utc time. My development box, windows xp, insists (both in C and in SQLite) that UTC is 10 hours different than localtime. I've clearly set the timezone to US CST in the control panel. I've also tried setting both the TZ and TIME_ZONE environment variables (CST6CDT). No luck. Any suggestions? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to add multiple columns at a time
"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I want to add multiple columns, the following works for only one > column: > > ALTER TABLE Data ADD COLUMN Password TEXT Just run ALTER TABLE once for each column you want to add. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to add multiple columns at a time
Hi, I want to add multiple columns, the following works for only one column: ALTER TABLE Data ADD COLUMN Password TEXT but not this: ALTER TABLE Data ADD COLUMN (Password TEXT, User TEXT) nor this: ALTER TABLE Data ADD (Password TEXT, User TEXT) -- Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 delete action is too slow
"yhuang" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I create a DB and only one table in the DB. There are 3641043 records > in the DB file. Min id is 27081364, Max id is 30902585. > > I did follow operation: > > sqlite> delete from XXX where userId>3090 and userId<30902000; > > took 1'32'' > > > > sqlite> delete from XXX where userId>2900 and userId<29902000; > > spent 3 hours and 33minutes and 26secs If your question is about why the second statment takes so much longer than the first, it's because it deletes 902,000 records vs 2,000 for the first. Did you perhaps mean to write userId>2990? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Success in extending genfkey
Hello, If you are interested, i successfully extended the genfkey tool to make sure all declared foreign keys have an associated index. Tables 'temp.idx2' and 'temp.idx' cannot be reused because of the 'il.isunique' clause... Thanks Dan for your response in the previous post. Thanks all for your kindness. --- /tmp/genfkey.c 2008-10-25 13:24:03.0 +0200 +++ genfkey.c 2008-10-25 13:42:09.0 +0200 @@ -663,6 +663,23 @@ "fkid, from_tbl, to_tbl, sj(dq(to_col),',') AS cols " "FROM (SELECT * FROM temp.fkey ORDER BY to_col) " "GROUP BY fkid, from_tbl;" + +"CREATE TABLE temp.idx3 AS SELECT " + "il.tablename AS tablename," + "ii.indexname AS indexname," + "ii.name AS col " + "FROM temp.v_idxlist AS il, temp.v_idxinfo AS ii " + "WHERE il.database='main' AND ii.indexname = il.name;" + +"CREATE TABLE temp.idx4 AS SELECT " + "tablename, indexname, sj(dq(col),',') AS cols " + "FROM (SELECT * FROM temp.idx3 ORDER BY col) " + "GROUP BY tablename, indexname;" + +"CREATE TABLE temp.fkey3 AS SELECT " +"fkid, from_tbl, sj(dq(from_col),',') AS cols " +"FROM (SELECT * FROM temp.fkey ORDER BY from_col) " +"GROUP BY fkid, from_tbl;" , 0, 0, pzErr ); if( rc!=SQLITE_OK ) return rc; @@ -674,6 +691,14 @@ ")", pHasErrors ); if( rc!=SQLITE_OK ) return rc; + rc = detectSchemaProblem(db, "foreign key has no associated index", +"SELECT fkid, from_tbl " +"FROM temp.fkey3 " +"WHERE NOT EXISTS (SELECT 1 " +"FROM temp.idx4 WHERE tablename=from_tbl AND fkey3.cols==idx4.cols" +")", pHasErrors + ); + if( rc!=SQLITE_OK ) return rc; return rc; } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite3 delete action is too slow
I create a DB and only one table in the DB. There are 3641043 records in the DB file. Min id is 27081364, Max id is 30902585. I did follow operation: sqlite> delete from XXX where userId>3090 and userId<30902000; took 1’32’’ sqlite> delete from XXX where userId>2900 and userId<29902000; spent 3 hours and 33minutes and 26secs The table schema: CREATE TABLE XXX ( userIdinteger primary key, userName varchar not null, c1varchar not null, c2 bigint not null, c3 varchar, c4 varchar not null, c5 varchar, c6varchar, c7 bigint default 0, c8bigint default 0, c9 integer default 0, c10 integer default 0, c11 integer default 0, c12 bigint default 0, c13 bigint default 0, c14 integer default 0, c15integer default 1, c16varchar, c17varchar, c18 varchar , c19 integer default 0, c20 varchar default '', CONSTRAINT xxx_key UNIQUE (userName, c1, c4, c7) ); CREATE INDEX idx_1 on XXX(c7); CREATE INDEX idx_2 on XXX(username,c8); CREATE INDEX idx_3 on XXX (c5, c8); CREATE INDEX idx_4 on XXX (userName); CREATE INDEX idx_5 on XXX (c1); CREATE TRIGGER xxx_limit_size after insert on XXX when ((select max(userId) from XXX) - (select min(userId) from XXX) > 1000) begin delete from XXX where userId < 1000 + (select min(userId) from XXX); end; The table is complex. Penny ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
