Re: [sqlite] sqlite for 16bit
Hello Christoph, Christoph Pross wrote: > Hello everybody, > > I am new to this list. We are looking for a sql > database that can run on a 16bit OS. I looked > over the sqlite C source but I found too may > 64bit integers, our OS only supports 23 bit longs. > > Has someone ported sqlite to a 16bit OS before? > > Or maybe someone knows another solution. An > open database that runs on MS DOS? > I am not sure that this will run without a 32-bit DOS extender, but you could try looking at Konstantin Knizhnik's MiniDB: http://www.garret.ru/~knizhnik/databases.html (Scroll down to the bottom.) Btw, did you mean 32 bits instead of 23 bits? Cheers, Ulrik Petersen -- Ulrik Petersen http://ulrikp.org -- Homepage http://emdros.org -- Emdros is a text database engine - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Lemon parser generator question
Medi, Lemon is a parser, not a lexer. The terminals are defined outside of Lemon. Perhaps you inherited the .y file but did not receive the lexer/tokenizer? SQLite, for example, has a hand-coded tokenizer. Other projects (such as my own) may use a lexer-generator such as flex. In Lemon, the tokenizer drives the parser, not the other way around. For more information, you can see the sources of SQLite, which includes a document on Lemon, or you can read this: http://www.hwaci.com/sw/lemon/lemon.html HTH Ulrik Petersen Medi Montaseri wrote: Hi, Firstly, if this is not the proper forum for Lemon questions, please let me know where I need to go... Second, I am looking at a SQL grammer written for Lemon parser generator and am failing to see where some terminals are defined. For example COMMA, FROM, SELECT are terminals and releatively easy to deduce. However in the grammer I am studying, I see references to NAME and STRING (all in uppercase, indicating a terminal production). Here is my higher level problem... I have inherited a SqlGrammer.y that does not parse quoted-table-name and is failing in cases where the FROM clause have things like "my, table". For example select * from "my, table". Currently it works with select * from "table one" Chasing the grammer rules, it see a rule that reads from_source(P) ::= NAME(C). { someClass:someAction() } Based on Lemons' doc, NAME must be a terminal rule (all upper case) and since I don't define it, then lemon must. Can someone shed some light on this Thanks Medi - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite and borland c++ builder
Hi Jonathan, I don't use Borland C, so I can' help you with the specifics of that compiler. But no, you don't necessarily get an executable just by compiling with a C compiler. You should be able to compile SQLite with a C compiler, and in the process obtain one or more .o files. (Perhaps they are called .obj in Borland C.) With the GNU C compiler (and many other compilers), the switch to create a .o file instead of an executable is -c. Maybe this translates to /c on Borland C, but you'd have to consult your Borland C manual for that. These .o/.obj files can be linked into your C++ program. If you follow Joe Wilson's advice and just #include from within C++, it should work. That is exactly what the 'extern "C"' clause is for in C++: It tells the C++ compiler that the functions within the 'extern "C" { ... }' block were compiled with a compiler that emits code with C calling conventions. This enables calling C code from within C++. As you probably know, calling conventions have to do with, among other things, the way function parameters are put on the stack, and the way any return value is returned. HTH Regards, Ulrik Petersen Jonathan Kahn wrote: Hi Ulrik, Thank you for responding. I'll try anything! The frustration that all this has brought me I am determined to solve it. If I built SQLite with a C compiler what would be the result? What would I be able to work with from within c++? Won't compiling leave me with an executable? I am fairly new to dealing with different compilers and things, so please forgive my ignorance. Thanks a lot, - Jon -Original Message- From: Ulrik Petersen [mailto:[EMAIL PROTECTED] Sent: Sunday, April 29, 2007 2:29 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite and borland c++ builder Hi Jon, is it not an option to build SQLite with a C compiler, then call it from within C++? Regards, Ulrik Petersen Jonathan Kahn wrote: Even when I try to build a new dll I get errors with attach.c and it says cannot convert 'void *' to 'Db *', no matter what route I take I always hit a bump. I'm just trying anything at this point - Jon -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Sunday, April 29, 2007 1:59 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite and borland c++ builder I wrote too quickly - sqlite3.h correctly uses __cplusplus for extern "C". But the almalgomated sqlite3.c cannot be compiled from a C++ compiler for the reasons described below. --- Joe Wilson <[EMAIL PROTECTED]> wrote: When I try to use the header I get errors [C++ Error] sqlite3.h(1778): E2232 Constant member 'sqlite3_index_info::nConstraint' in class without constructors It appears it is trying to compile the sqlite header file as if it were C++. Lemme guess - you're using the almalgomated sqlite3.c from 3.3.17, right? from the generated sqlite3.c: /* ** Make sure we can call this stuff from C++. */ #if 0 extern "C" { #endif See the #if 0? That's the problem. It should be: #if __cplusplus SQLite 3.3.17 has a bug in sqlite3.c generation. To work around this issue, do this: extern "C" { #include "sqlite3.h" } __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] - __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - 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] -
Re: [sqlite] sqlite and borland c++ builder
Hi Jon, is it not an option to build SQLite with a C compiler, then call it from within C++? Regards, Ulrik Petersen Jonathan Kahn wrote: Even when I try to build a new dll I get errors with attach.c and it says cannot convert 'void *' to 'Db *', no matter what route I take I always hit a bump. I'm just trying anything at this point - Jon -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Sunday, April 29, 2007 1:59 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite and borland c++ builder I wrote too quickly - sqlite3.h correctly uses __cplusplus for extern "C". But the almalgomated sqlite3.c cannot be compiled from a C++ compiler for the reasons described below. --- Joe Wilson <[EMAIL PROTECTED]> wrote: When I try to use the header I get errors [C++ Error] sqlite3.h(1778): E2232 Constant member 'sqlite3_index_info::nConstraint' in class without constructors It appears it is trying to compile the sqlite header file as if it were C++. Lemme guess - you're using the almalgomated sqlite3.c from 3.3.17, right? from the generated sqlite3.c: /* ** Make sure we can call this stuff from C++. */ #if 0 extern "C" { #endif See the #if 0? That's the problem. It should be: #if __cplusplus SQLite 3.3.17 has a bug in sqlite3.c generation. To work around this issue, do this: extern "C" { #include "sqlite3.h" } __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] - __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Where could i find?
Hi Cesar, you can find what you are seeking in the CVS repository: 1) log in as anonymous: cvs -d :pserver:[EMAIL PROTECTED]:/sqlite login password: anonymous 2) checkout the current version cvs -d :pserver:[EMAIL PROTECTED]:/sqlite checkout sqlite 3) Enter the sqlite directory: cd sqlite 4) Update to something that was prior to 2.0. Since 2.0 was started on or around 2001-09-16, the following will work: cvs update -D '2001-09-01' . Notice the dot at the end. HTH Ulrik Petersen http://emdros.org -- Emdros is a corpus query system Cesar Rodas wrote: > I just try-out on http://www.sqlite.org/sqlite-1.0.18.tar.gz and doesn't > work... please if any body have the source.. I really need it.. thanks to > all > > On 02/02/07, Cesar Rodas <[EMAIL PROTECTED]> wrote: >> >> I am wondering if there is a place on the net that I could download all >> the >> version of sqlite? >> I mean sqlite v1.x.x, sqlite v2.x.x >> >> I really want the 1.x.x becouse the code is easier to understand, and is >> simplest that the sqlite3 >> >> >> -- >> Cesar Rodas >> http://www.sf.net/projects/pagerank (The PageRank made easy...) >> http://www.sf.net/projects/fastfs ( The Fast File System) >> Mobile Phone: 595 961 974165 >> Phone: 595 21 645590 >> [EMAIL PROTECTED] >> [EMAIL PROTECTED] >> >> > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] fast Java solution?
Brannon King wrote: Is there some standard tool that will generate a JNI dll from the sqlite.dll? Have you looked at SWIG? http://www.swig.org It doesn't generate a JNI dll from the sqlite.dll, but does generate a JNI dll from the sqlite sourcecode -- there is a learning curve, so you might want to roll your own JNI code after all, especially if you want to do custom things with callbacks. Might be worth a look. HTH Ulrik Petersen
Re: [sqlite] Need sql query help
onemind wrote: Thanks, The thing is, i am going to need to use different letters each time to search through over 200,000 words in a database and it needs to be fast. What technology would be best suited for this task? I just assumed that a databse would be ideal, why do you say sql isn't suited for this and what is? Thanks again. Derrel Lipman's recent post may answer your question better, but here's a sketch of a solution that involves SQLite. 1) Find a suitable regular expression library, say, PCRE (Perl Compatible Regular Expressions) -- www.prce.org 2) Write a C function to be used from within SQLite, using the instructions found at: http://www.sqlite.org/capi3ref.html#sqlite3_create_function The C function might be a custom one that, given a string of letters, searched for all letters (AND) or any letters (OR), possibly using the RegEx library. 3) Recompile SQLite with said regex library added into the SQLite code, as well as your C function. 4) Register your C function with SQLite using the above API 5) Use the function with the regex '[spqd]' to search for words containing the letters "s", "p", "q", OR "d". Doing it for all letters (AND) may be doable with a single regex, but if not, you can always, in your custom function, search for all the letters, mark them off one by one as you find them, and return the appropriate value when all have been found, otherwise, if you get to the end of the string, then return another appropriate value. Another poster mentioned that you should really test the straightforward, simple-minded approach that he mentioned, first. If it is fast enough, then why bother doing it the hard way. The above probably also won't use an index, so it is also an O(n) approach, like the simple-minded approach of doing several LIKE's probably is. 200,000 words does not sound like a whole lot. The first query might be a little slow, but if your table fits in memory, then your operating system's cache will probably make subsequent queries rather fast. Having said all this, the fastest way would probably be to use an in-memory datastructure, and simply query that in-memory. One possible -- and very simple -- solution would be to have a hash-map for every character you wished to be able to search, then store pointers to the strings of the words in each hash-map. That would make your lookup-times be O(m), where m is the number of letters to search for, rather than O(n), where n is the number of words. HTH Ulrik Petersen
Re: [sqlite] Scrolling thru an index
Hi JP, JP wrote: Anyway, maybe separate topic, I tried to create a "snapshot" window of the above using plain SQL, but it doesn't seem to work on Sqlite 3.3.4: CREATE TABLE clients (custid integer primary key, lastname varchar(50)); CREATE INDEX cidx ON (lastname); (insert 10,000 records here) SELECT idx,lastname FROM ( -- get names before search criteria SELECT '1' as idx,lastname FROM (select lastname from customers where lastname<'sprenkle' ORDER BY lastname DESC LIMIT 50) UNION -- get names matching search criteria plus 50 more SELECT '2' as idx,lastname FROM (select lastname from customers where lastname>='sprenkle' ORDER BY lastname ASC LIMIT 50) ) order by 1,2; Individually, the queries work fine. In UNION, each seems to lose the inner order clause and show innacurate results. jp Instead of "order by 1,2", don't you mean "order by idx"? Ulrik P.
Re: [sqlite] Sqlite and Java
Jonathan Ballet wrote: Noel Frankinet wrote: Nilo Paim wrote: Hi Noel, Maybe slower, maybe memory hungry... but not less portable in my opinion. Java is machine independent, unless when using native code. On that scenario ( native code ) Java is not portable. My point is: I would like to have access to sqlite databases from java without worrying me about native code, dll's, .so'es, etc..., just using only pure java code, without lossing the normal access to the same databases, using the "normal" way, via C, C++ or any other compiled language. Thanks for your quick response. Cheers, Nilo Porto Alegre - Brasil Hello Nilo, I think there a IBM open source sql engine in pure Java. But I don't rember the name right now. May be you could use that ? Regards I think you're talking of http://hsqldb.org/, used among other project by OpenOffice ... I think Noël may be talking about Apache Derby, formerly IBM Cloudscape: http://db.apache.org/derby/ Regards, Ulrik Petersen
Re: [sqlite] converting a mysql database
Hi Dave, Dave Dyer wrote: I'm taking a test cut at converting a existing mysql database to sqlite. I dumped the mysql database, tweaked the prototype into slite format, and converted the escape characters in the data to standard sql format. Here's what happens when I attempt an import: sqlite> .read proto.txt sqlite> .read city-part.sql INSERT INTO city VALUES (112.,7617.,'Hartford',41.7640,-72.6860); SQL error: unable to open database file INSERT INTO city VALUES (52.,6583.,'Eindhoven',51.4500,5.4670); SQL error: unable to open database file INSERT INTO city VALUES (13.,5828.,'Bradford',50.5480,-4.6610); SQL error: unable to open database file ... These sql errors are random. Since there's no other activity affecting this database, I wouldn't expect any contention problem. Is there something I'm missing? Without knowing the contents of proto.txt, I'd say you probably have defective RAM in your machine, or a defective harddrive. If your machine is a x86, you can Google memtest86 to get an excellent free tool for checking for RAM defects. Oh yea, and this is EXTREMELY slow, presumably because I haven't wrapped the inserts in a transaction. Very, very likely, that shuld be the cause of the slowness. Ulrik
Re: [sqlite] how to secure SQLite database
Greeting sailendra, sai kalyanam wrote: Greetings all, can you pls any help me how to secure sqlite database. there is no userid and password to open the database. what are the security issues in SQLite database. can you pls help in this issue. You need to encrypt your database. Dr. Hipp, the creator of SQLite, has a very reasonably priced solution. See http://www.hwaci.com/sw/sqlite/prosupport.html Ulrik Petersen -- Ulrik Petersen, Denmark
Re: [sqlite] questions from a new user
Hi Dan, dan greene wrote: 1. Does anyone have a zipped up version of the SQLITE documentation? The online documentation is great but on at least one of my development systems I don't have web access. A local copy on my windows machine would make things easier. That's part of the sourcecode. If you are on a Unix/Linux box, you can do make doc and it will be built for you in doc/. Cheers, Ulrik -- Ulrik Petersen, Denmark
Re: [sqlite] Please test on Win95/98/ME
Robert Simpson wrote: I'm back in the office today -- let me have a quick crack at the issue before you settle on something. I've got a pretty good idea how to clean it up. I don't like using the MSLU because its a dependency that up until now SQLite has not had. Since the unicows.dll is not part of a standard Windows installation, it would require developers to redistribute that DLL in addition to the sqlite DLL. Yes, and distributing the DLL may not be an option for some developers (mostly Open Source developers). The EULA for UnicoWS.dll specifies, among other things: "3.1 ... you agree ... (iii) to distribute the Licensee Product containing the Redistributables pursuant to an end user license agreement (which may be “break-the-seal”, “click-wrap” or signed), with terms no less protective than those contained in this EULA; " If someone can tell me why this doesn't exclude releasing software under most Open Source licenses (including the GPL, BSD, MIT, Artistic, and Apache, to name a few -- they aren't "no less protective" than Microsoft's EULA) and at the same time distribute UnicoWS.dll with the application, I'd be glad to hear about it. IANAL. Just my $0.02 :-). Ulrik - Original Message - From: "D. Richard Hipp" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Tuesday, September 06, 2005 8:53 AM Subject: Re: [sqlite] Please test on Win95/98/ME On Tue, 2005-09-06 at 08:35 -0700, Roger Binns wrote: > To sum up: You need to convert UTF-8 to UTF-16-LE first. Then, > if the OS is NT, you can pass these to the ...W functions. > Otherwise, you need to further convert to ANSI user codepage > and pass it to the ...A functions. Alternatively tell people to link against unicows if they need win9x support and you can stick to only using the W functions. http://www.microsoft.com/globaldev/handson/dev/mslu_announce.mspx I like this solution a lot. This is probably what I will end up doing unless somebody can suggest a good reason not to. -- D. Richard Hipp <[EMAIL PROTECTED]> -- Ulrik Petersen, Denmark
Re: [sqlite] Linker Error when trying to use Sqlite with GCC
Hi Michael, Michael Gaskins wrote: I'm working on an application that will require an embedded database backend and Sqlite is looking to be my prime choice here. Now, I typically write the GUI and the working code seperately anyways and tie them together later, so I figured I'd begin work on my backend that talks with the database. I've never actually done any previous database programming so I'm learning as I go here :). I'm having trouble getting my (very primitive) experimental program to link however. Below is my code for test.c: #include #include int main (int argc, char **argv) { struct sqlite *pilotLog; pilotLog = sqlite_open("logbook.dat", 0, NULL); sqlite_close(pilotLog); How about using sqlite3_open and sqlite3_close ? HTH Ulrik P. -- Ulrik Petersen, Denmark
Re: [sqlite] Re: sqite2.8 performance snag when filesize grows more than 70MB on a 256MB ram
Ligesh, Ligesh wrote: I am running a very simple sql query with only one instance. The query is something like: select * from table where parent_name = 'parent' order by name limit 10 My guess (and it is only a guess) is that, because of the ORDER BY clause, SQLite is loading all rows into memory, then sorting them in-memory. Here's a tip which might actually help you: If your rows are around 1kb in size, it might be worth increasing the page size to, say, 4096, so as not to cause spillovers onto other pages. You don't say what platform you are running on. At least for me, having a page size of 4096 increases performance on Win32, probably because it matches the size of the Win32 memory cache page size. You will find the relevant #define in pager.h. There, it says that increasing the page size will not really give you a performance edge. This is true on Linux, not on Windows, I've found. However, increasing the page size beyond 4096 does nothing for me in terms of increased performance. Cheers, Ulrik P. -- Ulrik Petersen, Denmark
RE: [sqlite] Linking libsqlite statically
Hi, > There are libsqlite3.a, libsqlite3.la, and libsqlite3.so files > in /usr/lib/ Could you show us the exact command line you are trying to use when running g++ ? I'm especially interested in the version where you give the /usr/lib/libsqlite3.a fully qualified filename on the commandline. You might be interested in an introduction to doing static/dynamic libraries on Unix, written by Dr. Kirk Lowery: http://emdros.org/progref/page.php?pid=1080 Ulrik P.
Re: [sqlite] how to force a database to be corrupted
Hi Olivier, Olivier Singla wrote: Hi, I was wondering, is there is way to force a database to be corrupted ? (obviously I need this for testing purposes). Basically I'd like to patch the database so the next sql command will return SQLITE_CORRUPT. You might want to look at: http://www.sqlite.org/lockingv3.html Scroll down to section 6.0 "How To Corrupt Your Database Files". Ulrik Petersen -- Ulrik Petersen University of Aalborg, Denmark http://ulrikp.org/
Re: [sqlite] Lemon grammar question
Ludvig Strigeus wrote: With Bison, you can do something like this (not quite bison syntax): myrule: TYPE IDENT {DoSomethingRightAfterIdent($1,$2); } LP more_rules RP; {DoSomethingAfterEverything($1,$2,$5); } I.e. you have a chunk of C code that's called in the middle of the processing of the production. (In the above case right after TYPE IDENT) Can you do this with lemon? I don't know if you can do it bison-style, but you can do this: 1) have a struct that wraps a TYPE(T) and IDENT(I). 2) myrule : prefix(P) LP more_rules(R) RP . { /* process rule */ } 3) prefix(P) : TYPE(T) IDENT(I) . { /* process ident; wrap T and I in the struct from (1); */ } 4) remember to set the default destructor of the prefix rule to destroy the struct from (1). I know you can do something like this for other cases, myrule ::= TYPE(T) IDENT(I) temp LP more_rules(R) RP. { DoSomethingAfterEverything(T,I,R); } temp ::= DoSomethingRightAfterIdent(...how would I access TYPE/INDENT from here..); but it doesn't quite work for this case... Any ideas? /Ludvig HTH Ulrik Petersen -- Ulrik Petersen, PhD student, MA, B.Sc. Aalborg University, Denmark
Re: [sqlite] Basic Text Bind Question
Hi, Kiel W. wrote: Could someone point out what I missing or not understanding on this? I'm trying to do a simple text bind to search for people with the last name "Fullman". However my return code (rc) from sqlite3_step() is the same as SQLITE_DONE. I'm assuming this means it doesn't find anything. Also, how do I pull the character string of the sqlite statement to be executed from 'sqlite3_stmt' ? Thanks for the hand. -- code snipet -- sqlite3_open( "test.db", ); size = sprintf( buf, "CREATE TABLE People( fname varchar(25), fname varchar(25) );" ); I think it stops right here, because you've got fname twice. That induces an error. Also, I'd execute each statement by itself. HTH Ulrik Petersen -- Ulrik Petersen, PhD student, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] Newbie sql: query and joining more than two tables
Hi Karim, Cláudio Leopoldino wrote: You may use EXPLAIN clause and verify the reazon... Cláudio Hi! I hope to get some feedback whether the query time is what I should expect. Running this query below takes several seconds - typically 1-3s. SELECT package.id, package.name, package.description, package.size, package.latest, version.version FROMcategory, package, version WHERE package.idCategory = category.id AND category.name = '" + category + "'" AND version.idPackage = package.id " ORDER BY lower( package.name ); The three tables are like this: CREATE TABLE category ( id INTEGER UNIQUE, name VARCHAR(32) ); CREATE INDEX index_name ON category ( name ); CREATE TABLE package ( id INTEGER UNIQUE, idCategory INTEGER, name VARCHAR(32), latest VARCHAR(32), description VARCHAR(255), size VARCHAR(32), keyword VARCHAR(32)); CREATE INDEX index_name ON package ( name ); CREATE TABLE version ( id INTEGER UNIQUE, idPackage INTEGER, version VARCHAR(32), date VARCHAR(32)); The table category has 136 rows, package 9379 rows and version 19369 rows. Regards, /Karim A couple of points: 1) You may wish to say "INTEGER PRIMARY KEY" rather than "INTEGER UNIQUE". The reason can be read here: http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning (search for "INTEGER PRIMARY KEY"). 2) I don't know if this will help, but try moving the category.name = '" + category + "'" term to the front of the WHERE clause. 3) Have you read Dr. Hipp's slides from PHP2004? http://www.sqlite.org/php2004/page-001.html On slide 48, it starts talking about how to organize your WHERE clauses for using indexes: http://www.sqlite.org/php2004/page-048.html HTH Ulrik P. -- Ulrik Petersen, PhD student, MA, B.Sc. Aalborg University, Denmark
Re: [sqlite] beginner's question
Hi, Lloyd Dupont wrote: a question about sqlite3.exe reading some documentation aboit it I see you could have memory database. how do I create them? I'm not sure about Windows, but on Unix/Linux, it is: sqlite :memory: or attach them? You issue the statement ATTACH DATABASE ':memory:' AS mynameforthememorydatabase; HTH Ulrik P. -- Ulrik Petersen, PhD student, MA, B.Sc. Aalborg University, Denmark http://ulrikp.org/
Re: [sqlite] Error while loading shared libraries: libsqlite.so.0
Hi Sombra, Saul wrote: Ok, but when I make the cross-compile for Power-Pc in my computer (running Red Hat 8.0) it doesn’t create any file named "libsqlite.so.0", just "libsqlite.so.0.8.6". Is this file a result from a correct compile or I can download this from other place? libsqlite.so.0 should just be a soft link to libsqlite.so.0.8.6 ln -s libsqlite.so.0.8.6 libsqlite.so.0 should do the trick. HTH Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] does sqlite run on 64 bit?
Hi Patrick, Patrick Dunnigan wrote: Has anyone been successful running SQLite on a 64 bit platform? If so, what platform? Using C libs or TCLSQLite? I've had success in running 2.8.15 in 64-bit mode on AMD64, Gentoo Linux, gcc compiler, C (not tcl). It worked out of the box, so I'm afraid I can't tell you how to make it work. What platform are you trying to run it on? Ulrik Petersen -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] Double quotes in C++ SQL statements
Hi Corwin, Corwin Burgess wrote: I need to know what the solution is to translate the following Delphi lines so that they will compile with C++ and be valid SQL statements. Delphi sSQL := 'INSERT INTO testtable(Name,OtherID,Number,Notes) VALUES ("Some Name",4,587.6594,"Here are some notes");'; sSQL := 'INSERT INTO testtable(Name,OtherID,Number,Notes) VALUES ("Another Name",12,4758.3265,"More notes");'; The following won' t compile. What do you do about the double-quotes as in "Some Name", "Here are some notes", "Another Name", and "More notes"? I can get it to compile if I use \" but that causes a SQL exception. BCB6 sSQL = "INSERT INTO testtable(Name,OtherID,Number,Notes) VALUES ("Some Name",4,587.6594,"Here are some notes");"; sSQL = "INSERT INTO testtable(Name,OtherID,Number,Notes) VALUES ("Another Name",12,4758.3265,"More notes");"; One solution would be to use 'Some Name' and 'Here are some notes' -- the SQL standard says that 'this is a literal string' whereas "this is a delimited (column) name". See a post to this list by Darren Duncan a few days back on column names. HTH Ulrik -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark Homepage: http://ulrikp.org
Re: [sqlite] SQLite on Motorola Power PC
Hi, Cem Vedat ISIK wrote: Thank you very much for your reply, I'm using gcc but I think I have nothing to do with a Mac, since the PowerPC I mention is not the PowerPC of Macintosh. I'm working on a Motorola PowerPC. Last time I checked, Motorola were one of the vendors delivering PowerPC chips to Apple's Macintosh line. That may have changed, of course. If you don't want to incur the expense of buying a hardware Mac, there is always software emulation. One Open Source PowerPC/Mac emulator which I'm very happy with is PearPC: http://pearpc.sourceforge.net http://www.pearpc.net You might get a cross-compiler to work more easily under that platform. PearPC supports networking, so you can probably even use something like Fink to get the required packages. If you don't want to buy Mac OS X, there is always OpenDarwin. The PearPC website has instructions for how to install OpenDarwin on PearPC. PearPC supports configurable sizes of RAM, so you shouldn't run into RAM problems using PearPC. HTH Ulrik Thomas Steffen wrote: On 4/18/05, Cem Vedat ISIK <[EMAIL PROTECTED]> wrote: Is there any information about How mant bytes of RAM does SQLite need to be compiled/built? I assume you are using gcc? Unfortunately gcc is known to have a big resource hunger. According to my experience, I would not try it with less than 64MB of RAM and 128MB of swap space. But that is for x86, the ppc version may be slightly different. What about using a Mac to compile it? I guess you would need to install Linux, because Mac OS doesn't know ELF, but then just about every PPC Mac should be able to compile SQLite. Thomas -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark Homepage: http://ulrikp.org
Re: [sqlite] Callback when table contents have changed
Hi Frank, F.W.A. van Leeuwen wrote: I've asked this two weeks ago but no reply yet... I've got one application that writes to the database, and one that reads from it. When a table in the database has changed, the reading application needs to know that. Of course I can send a signal from the writer to the reader app, but if the database would have a means for that, I think it would be more elegant (not directly coupling the writer and the reader). So it would be nice if a C application could subscribe to "table changed" events. I don't think it is currently possible with SQLite, is it? Frank. Isn't that what triggers are for? Perhaps you could couple a trigger with a user-defined function. HTH Ulrik -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] Version 3.2.0
D. Richard Hipp wrote: also when I use the "up arrow" (within the 3.2.0 version) to retreve the last command [it doesn't work] I used to compile the command-line client using GNU readline so that the arrow keys would work. But a lot of users complained that readline didn't work on their systems because their system didn't have the right libraries installed. And in fact, when I recently upgraded to SuSE 9.2 (from RedHat 7.2) I found that SuSE is missing GNU readline. (SuSE is missing a lot of other stuff too, I've found.) So my latest builds do not have readline support. If somebody can suggest a simple readline facility that can be statically linked and which is easy to support, I be happy to start using it. Pardon my ignorance of Unix linker semantics, but can't you statically link GNU readline in through the -Bstatic flag given to ld (or libtool)? $ man ld [snip] -Bstatic -dn -non_shared -static Do not link against shared libraries. This is only meaningful on platforms for which shared libraries are supported. The different variants of this option are for compatibility with various systems. You may use this option multiple times on the command line: it affects library searching for -l options which follow it. This option also implies --unresolved-symbols=report-all. [snip] HTH Ulrik Petersen -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] Adding a column to an sqlite database through the sqlite utility interface
Steve Frierdich wrote: Does anyone know how to add a column to an sqlite database through the sqlite utility interface. I tried the SQL statement , ALTER TABLE table Name ADD column variable, and it did not work. Anyone know any other way? Thanks Steve http://www.sqlite.org/faq.html#q13
Re: [sqlite] database encryption
Hi, Olivier Singla wrote: Hi, I am planning to use sqlite in a PowerPC embedded device (using of course Linux). So far I am extremely happy (and impressed!) by sqlite. The only thing I think I am missing is a way to protect the database (not only the data by themself, but also the database schema). [snip] Also, any other solution available ? Dr. Hipp himself offers an encryption-enhanced version for a fee: http://www.hwaci.com/sw/sqlite/prosupport.html It can't get much better than getting it straight from the author ;-). Cheers, Ulrik Petersen
Re: [sqlite] lobjc
Hi, SlackRat wrote: I seem to be having some trouble linking a small test programme as included below. I am totally new to Windows and am sure that I have missed something elementary. I obtained sqlite and have installed: windows98(2) dev-c++ 4.9.9.2 (latest modified version) sqlite-3.1.3.zip sqlitedll-3.1.3.zip sqlite-source.zip This sounds like more of a problem with configuring dev-c++ than with SQLite. Perhaps you would be better served on a dev-c++ forum. The sqlite3.h that I am using I took directly from the sqlite-source package The programme compiles fine but the linker advises: " cannot find -lobjc " and ld exits with status 1 This means that the linker is configured (somewhere, in some dialog box) to use Objective C (is my guess), where you really want to use plain C. Perhaps you mistakenly selected Objective C as your default language when you created the project. I don't know -- as I said, this sounds more like a configuration problem with dev-c++ than a problem with SQLite. Go hunt for references to libobjc, -lobjc, and Objective C in the configuration dialogs. And make sure that all paths in any path-configuration dialog box are set correctly. I have a copy of libobjc.a in cygwin/lib/mingw and copied it to c:/dev-cpp/lib/gcc/mingw32/3.4.2 as lobjc.a but this is not working Unless your Cygwin version of libobjc.a is exactly the same as the one that comes with dev-c++, I suspect this may not be a good idea. Perhaps try uninstalling and reinstalling dev-c++. HTH Ulrik Petersen -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] Re: sqlite performance variationin linux and windows
Hi, Neelamegam Appadurai wrote: Hi, Thanks for the quick response and the interest you're showing, I am testing the performance of linux and windows using a. same testing data for both. b. db schema is common for both. c. though the test is conducted on two different machines but the machine configurations are similar. Only consideration is at very few places jin the application, code is platform dependent ie the api calls may differ based on the type of OS, but this is also seldom used in our application. But still, For the same application on windows, performance of sqlite is slower compared to file system read or write which we were using earlier. Thanks once again for the reply, appadurai The file system you are using could also have a large impact. If using FAT or FAT32, I recommend moving to NTFS if possible. HTH Ulrik P.
Re: [sqlite] Syntax error ?
Richard Nagle wrote: Sorry, let me get this straight: WHAT syntax command do I use ? sqlite> Make new database ? sqlite>contacts; The problem there is no Contacts database created ? I would have to make a database first, before creating tables? please explain. You don't make the database explicitly. You give the name of the database on the commandline, before you enter the sqlite command shell: $ pwd /projects/test $ sqlite contacts SQLite version 2.8.15 Enter ".help" for instructions sqlite> .database seq name file --- --- -- 0main /projects/test/contacts 1temp /var/tmp/sqlite_ZFSmuMGwKY4qVLY This is the same in SQLite 3. HTH Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] sqlite performance variationin linux and windows
Hi, Neelamegam Appadurai wrote: Can anyone please help me how to increase the performance on windows enviroment One thing you can do is to increase the page size from 1024 to 4096. That seems to match either Windows' swapfile-pagesize or the default page size of NTFS (there is disagreement over which it is that causes the speed increase). This has given a speed increase for me. Not a factor 2, mind you, but still a speed increase. Also, you might try increasing the SQLite page cache size on Windows. HTH Ulrik Petersen -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] Write issues on some computers?
Luc, Luc Vandal said: The database is stored in the [User]\Local Settings\Application Data\ folder for the current user. Forgive my ignorance of Windows user directories, but could it be that some of these users are keeping their [User] folder on a network drive? That is sometimes reported to be a bad idea for keeping SQLite databases. Just a thought. Ulrik Petersen -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] New to SQLite...............2
Hi Jan, Jan Ekström wrote: I am using Windows Home Edition. I think the SQLite cammand shell is the same as windows command shell. The SQLite command shell is the sqlite.exe or sqlite3.exe file that you can download from the "Precompiled Binaries for Windows" section of the SQLite download page: http://www.sqlite.org/download.html HTH Ulrik Petersen
Re: [sqlite] Making a SEQUENCE?
Hi again, Brown, Dave wrote: Is it possible to create the behaviour of a sequence with SQLite? I need to do something like this: [pseudocode] var id = SELECT next_val FROM my_sequence; INSERT INTO table1 VALUES(id, ...); INSERT INTO table2 VALUES(id, ...); I forgot to say that if you want to insert the same ID into different tables, you'll have to create a single table that is an "id generator" using the method provided in the FAQ. For an example of how this could be done, see my Open Source text database project: http://emdros.org/preview/ You'll want to look in EMdF/sqliteemdfdb.cpp and EMdF/sqliteconn.cpp as well as their counterparts in include/ Cheers, Ulrik
Re: [sqlite] Making a SEQUENCE?
Hi Dave, Brown, Dave wrote: Is it possible to create the behaviour of a sequence with SQLite? I need to do something like this: [pseudocode] var id = SELECT next_val FROM my_sequence; INSERT INTO table1 VALUES(id, ...); INSERT INTO table2 VALUES(id, ...); This should return the next value, AND also increment it so that the next call will be +1. (I'm then taking this value and using it as key for a row inserted into multiple tables). I read the docs as well as searched the mailing list archives, but didnt' find much. There was something about using triggers, but would this guarantee an atomic increment? Does anyone have a solution to this? Check the FAQ: http://www.sqlite.org/faq.html#q1
Re: [sqlite] Is it bug?
Witold Czarnecki wrote: sqlite> select typeof(round(1)); text Is it bug? What version? Ulrik Petersen
Re: [sqlite] Best way to check for existence of a table?
Hi Richard, Richard Boyd wrote: Thanks for the prompt reply... I tried what you suggested and I always get the error message: "SQL error: no such column: table32" Whether the table exists or not, I always get returned value of 1 from sqlite3_exec(). The exact command that I use is: SELECT count(*) FROM sqlite_master WHERE name=table32 AND type='table'; I also tried single quotes around the table32 name: SELECT count(*) FROM sqlite_master WHERE name='table32' AND type='table'; And get no errors whether the table exists or not When I try the other method suggested ("SELECT NULL FROM sqlite_master WHERE tbl_name = 'your-table';") I don’t get any error messages whether the table exists or not. The return value is always 0. I'm obviously missing where the error is being flagged, have you any more pointers? Sorry if I'm being dense here but I'm new to SQL databases. Thanks again, Richard. SELECT COUNT(*) etc. should return a tuple with one column, not an error message. You then retrieve that tuple. If the value in the tuple is 0, then the table does not exist. If the value is 1, then it does exist. HTH Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark Homepage: http://ulrikp.org
Re: [sqlite] Problem with this simple example
Ulrik Petersen wrote: b) Compile with -DTHREAD_SAFE. Sorry, that should have been: b) Make sure that SQLite has been compiled with -DTHREAD_SAFE. Ulrik P.
Re: [sqlite] Problem with this simple example
Hi Dave, Dave Furey wrote: Below is a very simplied example of what I'm trying to do with a recursive routine call: == sqlite3_prepare (hDB,CstrCommand,strlen(CstrCommand),,); while ( sqlite3_step(ppStmt) == SQLITE_ROW ) { sqlite3_prepare (hDB,CstrCommand2,strlen(CstrCommand2),,); sqlite3_step(ppStmt2); sqlite3_finalize(ppStmt2); } sqlite3_finalize(ppStmt); sqlite3_prepare (hDB,CstrCommand3,strlen(CstrCommand3),,); sqlite3_step(ppStmt3); sqlite3_finalize(ppStmt3); == Both the "CStrCommand" and "CStrCommand2" contain SELECT query statements. This coding example works fine with the query (not returning any errors). However, when I get to "CStrCommand3" (which contains an INSERT statement), the sqlite3_step() call returns SQLITE_ERROR. Is it legal for me to have the prepare/step/finalize coding embedded as I've shown in my simple example above? Any help would be appreciated. No, you cannot have nested queries on the same connection. To do that, you should: a) Use two different connections, and b) Compile with -DTHREAD_SAFE. HTH Ulrik P.
Re: [sqlite] VACUUM question
Clay and Derrell, Clay Dowling wrote: [EMAIL PROTECTED] wrote: Do you know if this bug exist in 2.8.15 as well? If so, is the fix back-portable? Darrell, I believe that the VACUUM statement didn't exist in 2.8.x, so there shouldn't be a problem. That's not true. It did exist in 2.8.15. I've used it many times, and my Emdros project, which uses SQLite 2.8.15, takes advantage of it. I don't know whether the problem Derrell was referring to exists in 2.8.15, though. Ulrik P. -- Ulrik Petersen, Denmark Emdros -- the text database engine for analyzed or annotated text http://emdros.org/
Re: [sqlite] Re: [Bulk] Re: [sqlite] [OT] SQL: limit a query by sum(val)?
Hi Stefan, > Hi, > > I have released a new version of my task tracking software Yatt. It's > based on SQLite. > The new version adds a bunch of new features including user management > from the > html interface. You can find it on www.yatt.de. It's freeware. > > It's currently using SQLite 2.8.x. I am thinking about upgrading to 3.1.x. > Is there > an automated tool for converting a database from 2.8 to 3.1, which I could > give to > users for simplifying the upgrade? I mean a tool, which opens the old > database, reads > the scheme, creates a new database and copies all entries. Or do I have to > build > something myself? I know this can be done from the commandline, but that's > not an > option for normal users - especially on windows, I think. I don't think it would be hard to do yourself. Here's how: 1) Extract the code for the ".dump" meta-command from shell.c in SQLite 2.8.15. 2) Make a program that consists of: a) The code extracted in (1) b) SQLite 2.8.15. c) The latest SQLite3 d) Some glue code As far as I know, SQLite 2.8 is supposed to be able to co-exist in the same binary as SQLite3. Otherwise, why don't you supply a .bat file that does the job? It can, of course, be run from within your program. HTH Ulrik Petersen -- Ulrik Petersen, Denmark
Re: [sqlite] SQLite3 and version 2.1 DBs
> I have built sqlite3.lib from the source tarball, and > when I try to create a statement in my 2.1 DB (created > via SQLite Database Browser 1.01 from > sqlitebrowser.sf.net) I get an error message that the > file is encrypted. I've also used SQLite Control > Center (v0.06) against the db, so I know it's good. I > suspect the problem may be that sqlite3 simply doesn't > play nice with DB's created by these utilities in 2.1 > format. Is this correct? Yes. The file format changed between 2.8.x and 3.0. See http://www.sqlite.org/version3.html That page also has a note about how to migrate data. To Brass Tilde and others who wondered about the 2.1 version number: Version 2.8.x has a string at the beginning of the database which says: "** This file contains an SQLite 2.1 database **" so that may be where Bryan got the version number from. Is that true, Bryan? > What GUI tools do people use > to manage their 3.x databases if that's the case? I haven't tried it myself, but you might want to look at Mike Cariotoglou's sqlite3Explorer: http://www.sqlite.org/contrib/download/sqlite3Explorer.zip?get=5 There are also other goodies in the contrib section: http://www.sqlite.org/contrib HTH Ulrik Petersen -- Ulrik Petersen, Denmark
Re: [sqlite] Do _ErrMsg strings have to be freed?
List, sorry about that. I misread Nathan's post, and so came up with a bogus answer. Never try to answer technical questions when it's 03:45am for you ;-). Ulrik Petersen Randall Fox wrote: On Mon, 7 Feb 2005 18:14:42 -0800, you wrote: Does this var pMsg: PChar; .. pMsg := SQLite3_ErrMsg(aDB); necessitate this? if pMsg <> nil then SQLite3_Free(pMsg); It depends. If you use sqlite3_exec, then yes, if you use the other method (sqlite_prepare) then I would say no, depending on your definition of the word "ephemeral" ;-) From the docs: sqlite3_exec method: If an error occurs while parsing or evaluating the SQL (but not while executing the callback) then an appropriate error message is written into memory obtained from malloc() and *errmsg is made to point to that message. The calling function is responsible for freeing the memory that holds the error message. Use sqlite3_free() for this. If errmsg==NULL, then no error message is ever written. sqlite3_prepare method The sqlite3_errcode() routine returns a result code for the most recent major API call. sqlite3_errmsg() returns an English-language text error message for the most recent error. The error message is represented in UTF-8 and will be ephemeral - it could disappear on the next call to any SQLite API function. sqlite3_errmsg16() works like sqlite3_errmsg() except that it returns the error message represented as UTF-16 in host native byte order.
Re: [sqlite] Do _ErrMsg strings have to be freed?
Hi Nathan, [EMAIL PROTECTED] wrote: Does this var pMsg: PChar; .. pMsg := SQLite3_ErrMsg(aDB); necessitate this? if pMsg <> nil then SQLite3_Free(pMsg); You'd have to read the docs of your Delphi wrapper, but if this were the plain C interface, then, yes, you would need to free the memory. From: http://www.sqlite.org/capi3ref.html#sqlite3_exec Comes this: "If an error occurs while parsing or evaluating the SQL (but not while executing the callback) then an appropriate error message is written into memory obtained from malloc() and *errmsg is made to point to that message. The calling function is responsible for freeing the memory that holds the error message. Use sqlite3_free <http://www.sqlite.org/capi3ref.html#sqlite3_free>() for this. If errmsg==NULL, then no error message is ever written." And now a meta-comment about asking questions in a technical forum. It is fine to ask questions, and newbies are certainly welcome, but... Your best bet is to check the www.sqlite.org site first, then check your Delphi wrapper documentation, then if that doesn't anwer your questions, you're more than welcome to ask questions here. One of the first rules of asking questions in a technical forum is to try to answer the question yourself from the docs before asking the question. It may not save you some time, but if a forum has many readers, the time saved collectively if you find the answer yourself is perhaps greater than the time it takes you to find the answer yourself. That's what documentation is for, after all. Plus you'll likely learn something in the process which WILL save you some time later down the road. I would encourage you to read through the SQLite3 C reference. It doesn't take that long, and will be a rewarding experience: http://www.sqlite.org/capi3ref.html And if that doesn't answer your questions, as I said, you're more than welcome to ask questions here, I am sure. This forum is really outstanding in its helpfulness, and we have much to be thankful for to a bunch of people. The tone of this forum is also unexpectedly pleasant most of the time. Kudos to everyone on this list who answers questions with kindness and insight. HTH Ulrik Petersen -- Ulrik Petersen, Denmark MA, B.Sc
Re: [sqlite] Make error installing sqlite3.0.8 on Solaris 9 (Sparc)
Hi Mike, Rightmire, Mike wrote: -Original Message- From: Rightmire, Mike Sent: Thursday, January 27, 2005 3:31 PM To: 'sqlite-users@sqlite.org' Subject: Make error installing sqlite3.0.8 on Solaris 9 (Sparc) I am trying to install SQlite3.0.8 on Solaris 9 Sparc. I have no issues when configuring, but when I try to make I get errors (see below.) I am using gcc 3.2.4 and the GNU ld, as, ETC. I have tried using both /usr/ccs/bin/make and the GNU make. Help! Please send me the output of make off-list, and I will see what I can do. Did you untar sqlite-3.0.8.tar.gz into a fresh directory, or did you untar it over an existing directory? The latter might give you the behavior you experienced. Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] debugging a locking problem
Jason Jobe wrote: I did find some where I wasn't doing that but I think I got them all. Sometimes I use sqlite3_exec; other times, when I need to get the rows I use sqlite3_step (with the finalize). Then perhaps you are calling sqlite3_exec in between an sqlite3_step and its corresponding sqlite3_finalize? HTH Ulrik P. PS: Please use bottom-posting, i.e., writing replies after replies, not before. It helps when one wants to follow the thread of the conversation. Should I wrap either or both of these in a transaction? I've also tried it backed by a file as well as the in-memory version; same results. On Jan 30, 2005, at 8:10 PM, D. Richard Hipp wrote: On Sun, 2005-01-30 at 19:56 -0500, Jason Jobe wrote: Hey out there. I'm having a dickens of a time trying to debug a locking issue. I thought I was doing something simple enough; accessing a database from within one process with no threading. 2005-01-30 19:28:10.736[5716] sqlite:ERROR database table is locked Trying again I get 2005-01-30 19:28:10.737[5716] sqlite:ERROR cannot commit transaction - SQL statements in progress I can't figure why the db thinks it should be locked. Any pointers would be most appreciated. Did you remember to sqlite3_finalize() statements that you were finished with? -- D. Richard Hipp <[EMAIL PROTECTED]> - jason [EMAIL PROTECTED]
Re: [sqlite] compiling the demo example
Alex Bartonek wrote: LOL.. ok I'm getting farther... the problem was in my makefile.. I can actually create a executable (SuSE 9.2) but when I run it I get: ~/workspace/test> ./a.out ./a.out: error while loading shared libraries: libsqlite3.so.0: cannot open shared object file: No such file or directory the library is located in /usr/local/lib .. how would I make this work? man ldconfig
Re: [sqlite] Sqlite with MFC SDI
Hi Ming, [EMAIL PROTECTED] wrote: Hi All, I am using Sqlite 3.1.0 with a MFC SDI project. I want Sqlite database to be my file format of SDI, which I can open, close and save through (File) menu. Does anyone have experience with this and know how to do it? It sounds like you may get more help from an MFC forum (do they exist still?) than from an SQLite mailinglist. It's been four years since I've done any serious MFC programming, so if my answers seem a bit vague, you now know why. That said, here are some pointers: 1) You need to derive your own document class from CDocument. On this class, override the methods with names such as OnSave, OnFileNew, OnFileOpen, OnClose. In these methods, you can do your SQLite magic. 2) You need to figure out a mapping from your application data model into the relational database model, and back again. 3) Jeff Prosise is your friend. If you are serious about using MFC, then I can highly recommend getting a copy of his book, "Programming Windows with MFC". It taught me everything I knew about MFC, and was well worth the price -- it paid for itself several times over in terms of productivity. 4) Are you sure you want to program with MFC? MFC is, so far as I know, an outdated, non-supported technology from 1998. If you want a modern application framework, I can recoomend the free, Open Source WxWidgets: http://www.wxwidgets.org Porting an MFC application over to WxWidgets isn't that difficult, as the frameworks are very similar. Except that WxWidgets is richer, clearner, and more modern. And cross-platform, too. HTH Ulrik P. -- Ulrik Petersen, Denmark
Re: [sqlite] Memory usage (3.1.0 alpha)
Hi Clive, To: sqlite-users@sqlite.org cc:(bcc: clive/Emultek) Subject: Re: [sqlite] Memory usage (3.1.0 alpha) Hi Clive, [EMAIL PROTECTED] wrote: I am benchmarking sqlite3 as a potential database for Windows and embedded applications. I am running the following code in a Rapid development environment that calls the equivalent sqlite3 functions in a Window's DLL that I built from the release . I am seeing that memory usage goes up and up with every loop, until Windows runs out of virtual memory. Am I doing something wrong? while(true) SQL exec: 'BEGIN TRANSACTION'; for from 1 to 1000 step 1 SQL query: 'INSERT INTO Contacts values(''aaa'',''bbb'',''4'')'; SQL exec: 'COMMIT TRANSACTION'; It looks like you've wrapped it in some sort of Visual Basic. Is that true? If you are using the sqlite3_prepare/sqlite3_step/sqlite3_finalize API, yhe behavior you experience may be because you don't call sqlite3_finalize. Do you use that API? HTH Ulrik P. [EMAIL PROTECTED] wrote: The environment I am using is RapidPlus. It makes calls directly to the DLL. I changed the sqlite3 functions just to return in order to eliminate the possibility of it being a problem with the environment, and there was no memory loss. Sorry, I don't understand what you mean. Have you changed the SQLite3 code at all? Since I am using sqlite3_exec I do not think I need to use sqlite3_finalize. Is that correct? That is correct. Perhaps the normal behaviour of sqlite3 is to use system memory until there is non left? No, that is not the case. I cannot find a #define that specifies how many database pages are cached in memory. It is not a #define, it's PRAGMA: http://www.sqlite.org/pragma.html Search the page for "cache_size" and "default_cache_size". The behavior you experience would be exhibited if: 1) The sqlite3_exec function returned an error, and you did not call sqlite3_free on the error message. (See http://www.sqlite.org/capi3ref.html#sqlite3_exec ) 2) You sqlite3_open'ed a new connection every time without sqlite3_close'ing it. That's all I can think of right now. HTH Ulrik
Re: [sqlite] Memory usage (3.1.0 alpha)
Hi Clive, [EMAIL PROTECTED] wrote: I am benchmarking sqlite3 as a potential database for Windows and embedded applications. I am running the following code in a Rapid development environment that calls the equivalent sqlite3 functions in a Window's DLL that I built from the release . I am seeing that memory usage goes up and up with every loop, until Windows runs out of virtual memory. Am I doing something wrong? while(true) SQL exec: 'BEGIN TRANSACTION'; for from 1 to 1000 step 1 SQL query: 'INSERT INTO Contacts values(''aaa'',''bbb'',''4'')'; SQL exec: 'COMMIT TRANSACTION'; It looks like you've wrapped it in some sort of Visual Basic. Is that true? If you are using the sqlite3_prepare/sqlite3_step/sqlite3_finalize API, yhe behavior you experience may be because you don't call sqlite3_finalize. Do you use that API? HTH Ulrik P.
Re: [sqlite] ATTACH in memory
Hi Ricard, Ricard Pillosu wrote: Hi all, Just reading the sqlite-user mailing list I found here http://www.mail-archive.com/sqlite-users@sqlite.org/msg01521.html that there is a way to dump from memory databases to file databases. What we are trying in our database is to ATTACH a read-only database and make changes to it in MEMORY. We don't want to save the changes, just be able to make updates to same rows. Yes, I can do a schema like: ATTACH 'abc.db' AS external; BEGIN; DELETE FROM xyz; INSERT INTO xyz SELECT * FROM external.xyz; ... COMMIT; DETACH external; But the "abc.db" is usually modified, and is a mess to have to change the code (+recompile) for every table added or changed. I just want to create an exact copy of it in memory, do updates and some selects, and then discard changes. Something like "ATTACH INMEMORY 'file.db' AS external" Do you think is there a way clean way to do it? Sorry to be glib, but I am not near my SQLite installation at the moment... Try a SELECT * FROM sqlite_master; it should give you some ideas. Specifically, look at the statements that were used to create the tables. You might be able to parse them to extract the table structure dynamically. Otherwise, try some of the PRAGMAs described on the SQLite language page on the website. HTH Ulrik P.
Re: [sqlite] synchronizing databases across LAN
Hi Shawn, Downey, Shawn wrote: Thank you all for the great mailing list. What would be the best way to keep n Sqlite databases up-to-date across n nodes on a LAN? The platform would be Windows and the implementation language would be C++ (but I am open to other language suggestions if it makes the job easier). The maximum number of nodes (n) would not exceed 15. I am not sure it runs in Windows, but this might be worth looking at: http://raa.ruby-lang.org/project/rq/ HTH Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] How to .import NULL values?
Hi Daniele, Ulrik Petersen wrote: Only in sqlite-3.0.8-nullimport/: config.h Only in sqlite-3.0.8-nullimport/: config.log Only in sqlite-3.0.8-nullimport/: config.status Only in sqlite-3.0.8: doc Only in sqlite-3.0.8-nullimport/: libtool Only in sqlite-3.0.8-nullimport/: Makefile Only in sqlite-3.0.8-nullimport/: sqlite3.pc diff -cr sqlite-3.0.8/src/shell.c sqlite-3.0.8-nullimport/src/shell.c *** sqlite-3.0.8/src/shell.c 2004-10-08 15:03:07.0 +0200 --- sqlite-3.0.8-nullimport/src/shell.c 2005-01-22 18:31:11.0 +0100 *** *** 1091,1097 break; } for(i=0; i<nCol; i++){ ! sqlite3_bind_text(pStmt, i+1, azCol[i], -1, SQLITE_STATIC); } sqlite3_step(pStmt); rc = sqlite3_reset(pStmt); --- 1091,1102 break; } for(i=0; i<nCol; i++){ ! if (strcmp(azCol[i], "NULL") == 0 ! || strcmp(azCol[i], "null") == 0) { ! sqlite3_bind_null(pStmt, i+1); ! } else { ! sqlite3_bind_text(pStmt, i+1, azCol[i], -1, SQLITE_STATIC); ! } } sqlite3_step(pStmt); rc = sqlite3_reset(pStmt); Only in sqlite-3.0.8-nullimport/src: shell.c~ I forgot to say this about my "contribution": /The author [that would be me, Ulrik Petersen] or authors of this code dedicate any and all copyright interest in this code to the public domain. We make this dedication for the benefit of the public at large and to the detriment of our heirs and successors. We intend this dedication to be an overt act of relinquishment in perpetuity of all present and future rights this code under copyright law. / It's best to be clear :-). Cheers, Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] How to .import NULL values?
Hi Daniele, Daniele Nicolucci (Jollino) wrote: Il giorno 22 gen 2005, alle 18:16, Ulrik Petersen ha scritto: As you can see, sqlite3_bind_text is used for all columns. You would have to write some ad-hoc code inside the loop that checked whether the value in azCol[i] was "null" or "NULL", and then used sqlite3_bind_null if that was the case, instead of sqlite3_bind_text. Ok, I guess I'll have to live without having an auto-increment field or find a way to fill it in the csv file with explicit values... my C skills are almost null so I don't even attempt to do that. Or I could write a simple script to generate INSERTs from csv files and keep the field. I'll play with it. The attached patch will do for you want you want. Apply it to the 3.0.8 sources like this: $ cd sqlite3 $ patch -p1 < /path/to/patch/patch.txt then recompile. I've tested this against your example table and your example data, and it works for me, i.e., the nulls get translated into sqlite3_bind_null calls, so that the autoincrement works as expected. HTH Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark Only in sqlite-3.0.8-nullimport/: config.h Only in sqlite-3.0.8-nullimport/: config.log Only in sqlite-3.0.8-nullimport/: config.status Only in sqlite-3.0.8: doc Only in sqlite-3.0.8-nullimport/: libtool Only in sqlite-3.0.8-nullimport/: Makefile Only in sqlite-3.0.8-nullimport/: sqlite3.pc diff -cr sqlite-3.0.8/src/shell.c sqlite-3.0.8-nullimport/src/shell.c *** sqlite-3.0.8/src/shell.c2004-10-08 15:03:07.0 +0200 --- sqlite-3.0.8-nullimport/src/shell.c 2005-01-22 18:31:11.0 +0100 *** *** 1091,1097 break; } for(i=0; i<nCol; i++){ ! sqlite3_bind_text(pStmt, i+1, azCol[i], -1, SQLITE_STATIC); } sqlite3_step(pStmt); rc = sqlite3_reset(pStmt); --- 1091,1102 break; } for(i=0; i<nCol; i++){ ! if (strcmp(azCol[i], "NULL") == 0 ! || strcmp(azCol[i], "null") == 0) { ! sqlite3_bind_null(pStmt, i+1); ! } else { ! sqlite3_bind_text(pStmt, i+1, azCol[i], -1, SQLITE_STATIC); ! } } sqlite3_step(pStmt); rc = sqlite3_reset(pStmt); Only in sqlite-3.0.8-nullimport/src: shell.c~
Re: [sqlite] How to .import NULL values?
Hi Daniele, Daniele Nicolucci (Jollino) wrote: Hello, I'm using sqlite 3.0.7 on OS X and I'm having a hard time using the .import function to import data which includes NULL values. I made a test database to describe the problem. This is the schema of the table: sqlite> .schema CREATE TABLE quest (id integer primary key default null, sesso, eta, dom1, dom2, dom3, dom4); As you can see it's pretty basic, the only notable thing is the auto-incremental "id" field. The "quest" table already has some data, and inserting new records using SQL works just fine: sqlite> select * from quest; 1|m|40|a|b|a|c 2|m|40|a|b|a|c sqlite> insert into quest values(null,'f',25,'a','b','c','d'); sqlite> insert into quest(sesso,eta,dom1,dom2,dom3,dom4) values('f', 32, 'c', 'd', 'a', 'a'); sqlite> select * from quest; 1|m|40|a|b|a|c 2|m|40|a|b|a|c 3|f|25|a|b|c|d 4|f|32|c|d|a|a The problem arises when using .import. I have a hand-made CSV file, and it looks like this: innocence:~/Temp jollino$ cat valori.csv null,f,35,c,d,a,a null,f,48,a,b,c,d null,m,22,b,c,d,a but sqlite doesn't like it: sqlite> .import valori.csv quest Error: datatype mismatch I tried removing "null" from, therefore having a literally null field, and it still doesn't work: innocence:~/Temp jollino$ cat valori.csv ,f,35,c,d,a,a ,f,48,a,b,c,d ,m,22,b,c,d,a sqlite> .import valori.csv quest Error: datatype mismatch I even tried using a 0 for that field, but of course it complains: innocence:~/Temp jollino$ cat valori.csv 0,f,35,c,d,a,a 0,f,48,a,b,c,d 0,m,22,b,c,d,a sqlite> .import valori.csv quest Error: PRIMARY KEY must be unique And I tried removing the field altogether, to no avail: innocence:~/Temp jollino$ cat valori.csv f,35,c,d,a,a f,48,a,b,c,d m,22,b,c,d,a sqlite> .import valori.csv quest valori.csv line 1: expected 7 columns of data but found 6 So the question is: how do I import NULL values? I searched the mailing list archives and I found that the same question has been asked a month ago, but it got no replies. (http://www.mail-archive.com/sqlite-users@sqlite.org/msg05168.html) I also had a look at the documentation but I couldn't find any detailed explanation for the .dot commands at all, but I might have missed it. Could anyone point me to the right direction, please? I checked the code (in src/shell.c). As far as I can see, what you are trying to do isn't possible with the current implementation. The code in question is line 1094 (in version 3.0.8), which I've marked with a star below: if( i+1!=nCol ){ fprintf(stderr,"%s line %d: expected %d columns of data but found %d\n", zFile, lineno, nCol, i+1); zCommit = "ROLLBACK"; break; } for(i=0; i<nCol; i++){ *sqlite3_bind_text(pStmt, i+1, azCol[i], -1, SQLITE_STATIC); } sqlite3_step(pStmt); rc = sqlite3_reset(pStmt); free(zLine); As you can see, sqlite3_bind_text is used for all columns. You would have to write some ad-hoc code inside the loop that checked whether the value in azCol[i] was "null" or "NULL", and then used sqlite3_bind_null if that was the case, instead of sqlite3_bind_text. Search for the string "import" in the shell.c code, and you will find the place where this is implemented. HTH Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: AW: [sqlite] sqlite3_column_tablename
Hi Joerg, Plenert, Joerg wrote: Hi Ulrik, picture this: A user types a query with output of fields from two ore more tables. Now, before you output the data, you'll check if the user has the right to see the data in the fields. So I check if the columns name is in my "allowed" list. Because two tables may have a column with the same name I need the table name to make it unique. But SQLite does not deliver the table name to the column returned. MySQL for example delivers this information in MYSQL_FIELD struct. Here you'll get the name, original name of column and table of each coulumn in result set. OK, I think I get it. For example: SELECT ET.x, MT.y FROM ET employee_table, MT manager_table WHERE ET.id = MT.id; So you want to know that column x comes from table employee_table and column y comes from table manager_table. Sorry, I can't help you any more than helping clarify what you want. I don't know whether you can do what you want already, or whether it's a real feature-request. Regards, Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] sqlite3_column_tablename
Hi Joerg, Plenert, Joerg wrote: Hi ! I need a possibility to get the table name (actual and/or original) from a column. There is a function sqlite3_column_name that returns the name of the column. How about a function sqlite3_column_tablename that returns the tablename ? I need that function to check user rights on the data returned by sqlite. Column names are always local to a table, i.e., tables form orthogonal/independent name spaces with respect to column names. This means that the same column name can be used in several different tables. So clearly you are not asking "for any given column name, show me the table from which it came": This is not a one-to-one mapping, but a one-to-many mapping. In what circumstances do you know the column name but not the table name? Please elaborate. Regards, Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] Version 3.1.0 API suggestion: sqlite3_commit()
Eric, Eric Scouten wrote: D. Richard Hipp wrote: This released is labeled "alpha" but it is still very well tested. By being "alpha" it means that there is still a small window of opportunity during when users can suggest API changes. Once we go to beta (in about a week) no more changes will be accepted. So if you want to suggest changes, please do so quickly. I'm making a push to use prepared statements as much as possible and I've been unable to make use of a prepared statement that commits a transaction. What I've tried (roughly, I'm doing this from memory since the code is gone now): sqlite3_exec( "BEGIN TRANSACTION;" ); // do other stuff... sqlite3_stmt commitStmt; sqlite3_prepare( db, "COMMIT;", -1, , NULL ); sqlite3_step( commitStmt ); // error... I forget the exact error message and result code, but the jist of it was that the transaction couldn't be committed because one or more statements were still running. From what I can tell, the only statement that hadn't been run to completion was the commit statement itself! Bear in mind that this is a quick-n-dirty example to demonstrate the issue. In real life, I would have prepared and reused the "BEGIN TRANSACTION" statement (which works, BTW), cached commitStmt, paid attention to result codes, etc., etc. It seems kind of silly to me to have to use sqlite3_exec( ... "COMMIT" ... ) to work around that problem, but that's what I'm doing at the moment. If there's a way to prepare and use a "COMMIT" statement, please let me know. If not, consider this a feature request for a sqlite3_commit() function so I don't have to spend the time compiling "COMMIT" over and over again. It's not completely clear how you detect the error, so I have to ask: Did you use sqlite3_finalize? From <http://www.sqlite.org/capi3.html>: "The sqlite3_finalize() routine deallocates a prepared SQL statement. All prepared statements must be finalized before the database can be closed. The sqlite3_reset() routine resets a prepared SQL statement so that it can be executed again." HTH Ulrik P. -- Ulrik Petersen, MA, B.Sc.
Re: [sqlite] expression syntax
Hi NK, [EMAIL PROTECTED] wrote: Hi, what is the correct syntax to use for WHERE expression: if (sqlite3_exec(test_db, "CREATE TABLE ana_db (item1 integer, item2 integer, item3 integer, item4 integer, item5 integer);", NULL, 0, NULL)) sqlite_error(test_db); // create index if (sqlite3_exec(test_db, "CREATE INDEX item1idx ON ana_db (item1);", NULL, 0, NULL)) sqlite_error(test_db); // insert values for 1000 records for (ii=0; ii< 1000; ii++) { if (error_code = sqlite3_exec(test_db, "INSERT INTO ana_db VALUES ('ii', 1, 0, 1, 100);", NULL, 0, NULL)) { sqlite_error(test_db); return (-1); } } // update values for 1000 records for (ii=0; ii< 1000; ii++) { if (error_code = sqlite3_exec(test_db, "UPDATE ana_db SET item2=item2+1 WHERE item1='ii';", NULL, 0, NULL)) { sqlite_error(test_db); return (-1); } } It looks like I'm not using the right syntax in UPDATE statement, I tried with: WHERE item1 = $ii, w/out success. What am I doing wrong? Thanks, NK you want the sqlite3_bind_int API. Look it up on the www.sqlite.org website. HTH Ulrik -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark Homepage: http://ulrikp.org
Re: [sqlite] Newbie help
Greetings Ram, Ram Kumar wrote: I am using lcc compiler for my basic learning. Is that sufficient to compile SQLite? Is there any free/openSource alternative? You will need both a compiler and a linker; I don't know lcc so I am not sure if it supplies a linker. Otherwise, here are a couple of free alternatives: http://www.mingw.org/ http://www.cygwin.com/ (really a Unix-like environment on Win32, but includes gcc) If you want a good, free IDE, I can recommend both Dev-C++ and Mingw Developer Studio: http://sourceforge.net/projects/dev-cpp http://petra.hos.u-szeged.hu/~aking/www.parinya.ca/ HTH Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark Homepage: http://ulrikp.org
Re: [sqlite] LOCK problem in 2.8
Hi Michael, Michael Keilhofer wrote: For sqlite 2.8 the documentation says that I can get a SQLITE_LOCKED when I try to execute a statement while in a callback function. I'm not using callbacks but find that when I try to do an sqlite_exec() while iterating records using sqlite_compile() and sqlite_step() I still get the SQLITE_LOCKED even though the execute is on a tatally different table than was the query. Can anyone tell me if this behavior is correct and if so, how do I update any table while reading records? e.g., To mark a record as being handled so another thread doesn't grab it. It is never safe to use the same sqlite structure in the way you describe. That would be equivalent to using it from two different threads (if I am not mistaken), which is specifically warned against in the documentation. The way to do it is probably to open the database twice, i.e., get two different sqlite structures. HTH Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] Syntax problem in where clause of Select statement
Hi Anirban, Anirban Sarkar wrote: Hi all, I have a variable xyz with the value 100. I want to write a sql statement in sqlite where the variable xyz should be in the 'where' clause. For eg: select * from 'tablename' where 'fieldname' = $xyz What language are you using? You might want to use the sqlite3_bind_int API. http://www.sqlite.org/capi3ref.html HTH Ulrik P.
Re: [sqlite] regd. sqlite 3
Hi Neera, neera sharma wrote: thankx Ulrix for replying. I am using sqlite_exec with call back option. Neera. From http://sqlite.org/capi3ref.html#sqlite3_step " SQLITE_MISUSE means that the this routine was called inappropriately. Perhaps it was called on a virtual machine that had already been finalized or on one that had previously returned SQLITE_ERROR or SQLITE_DONE. Or it could be the case the the same database connection is being used simultaneously by two or more threads." Is any of this true? HTH Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] regd. sqlite 3
neera sharma wrote: I am using sqlite3. when i pass an sqlite command string, following error is reported - "SQL error: library routine called out of sequence" Could anybody suggest the correct way of doing it. Do you want to use the "sqlite3_exec with callback function" API? Or the "sqlite3_prepare/sqlite3_step/sqlite3_finalize" API? The API is described here: http://www.sqlite.org/capi3.html HTH Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] speed
Hi Brandon, Brandon Whalen wrote: I'm currently trying to use sqlite to manage a database in a c program. I have 4 tables where each table has at most 6 columns. I've found that if I use a select statement and run that statement through a callback function that I get incredibly slow response times. I've found that the select statement itself happens rather fast, but its taking the selected data and sending it to my callbacks that is taking the most time, specifically I've found from gprof: Each sample counts as 0.01 seconds. % cumulative self self total time seconds secondscalls ms/call ms/call name 22.90 1592.40 1592.40 1649956995 0.00 0.00 getPayload 21.97 3120.02 1527.622326265.67 272.85 sqlite3VdbeExec Is this common in sqlite? I've tested my sql statement on the command line and its rather fast, but I've also found that if I run a built in function(count) on the results that it too suffers from a severe slowdown in performance. What you call "the select statement itself" is, I think, just compiling the statement to a program in the virtual machine language. This is very fast. When using built-in functions (such as "count"), SQLite often has to do a full table scan, which can take a long time, since it can't use an index. The getPayload function is defined in btree.c, and is the function that takes values and keys out of the B-Tree. As you can see, it is called a little over 1.6 billion times, which suggests that you have a lot of rows (maybe on the order of 1.6E09/24 ~ 69 million (since you have at most 24 columns). That is a lot of rows, and may induce "poor" performance, regardless of whether you are using SQLite or MySQL, PostgreSQL or any of the other big databases. You should check that indices are used (using the EXPLAIN statement) whenever possible. HTH Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] URGENT : library routine called out of sequence
Hi Sumit, Sumit Nagpal wrote: I was searching the web for links related to my problem . I came across one thread "Sqlite Thread safety" SQLite requires one connection per thread for threadsafe operation. If two or more threads share an SQLite connection, the program might get the error "library routine called out of sequence" or a corrupted database. This could well be the problem. Please see this FAQ: http://www.sqlite.org/faq.html#q8 Cheers, Ulrik P. -- Ulrik Petersen, Denmark
Re: [sqlite] Sqlite installation problems
Hi Anirban, Anirban Sarkar wrote: Hi! everyone, I am a newbie as far as Sqlite is concerend. So I just need some help to get things started. I am working on Mandrake Linux 10.0 platform. I have downloaded the rpm 'sqlite-2.8.15-1.i386.rpm' from the downloads section of www.sqlite.org. I log into my linux system as root and execute the above rpm when the following error pops up : "Some package requested cannot be installed: sqlite-2.8.15-1.i386(due to unsatisfied libreadline.so.4) do you agree?" You have to install the libreadline package, which is either called libreadline-X.rpm or readline-.rpm (fill in the 's). If you can't find it in the package manager, go through the CDs one by one, then, from the command-line (and as root), do: rpm -uvh readline-.rpm (or whatever it is called), with the current directory being in /mnt/cdrom/Mandrake or similar. HTH Ulrik -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark Homepage: http://ulrikp.org
Re: [sqlite] VACUUM function problem
D.W. wrote: Thanks for your reply. I have just checked the version. It's 2.8.15. There's no active transaction. Do you have another idea? How do you verify that nothing happens? Ulrik -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] VACUUM function problem
Hi D.W., D.W. wrote: I use sqlite 2.8.x in combination with PHP. I want to clean up my database and remove empty spaces. I used this command: $ok=sqlite_query($sqlite,"VACUUM tablename"); But it doesnt't work. An error message didn't come either. Does anybody know what is wrong? Daniel I hope you're using something later than 2.8.0, because VACUUM was only (re)implemented in 2.8.1. From the docs: http://www.sqlite.org/lang.html#vacuum "The index or table name argument is now ignored." Also from the same page: "This command will fail if there is an active transaction. This command has no effect on an in-memory database." HTH Ulrik -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] Sequences In SQLite
> Is there anything like serial sequences in SQLite? What I want to do is > have a primary interger key that auto-increments as records are added to > a table. http://www.sqlite.org/faq.html#q1 http://www.catb.org/~esr/faqs/smart-questions.html Ulrik P.
Re: [sqlite] commas in columns and temporary tables
Taj, > Now, I've got two questions. > The first is that I have a column (company) in a customers table. The > problem is that this column has a comma in it for some rows. For example: > > sqlite> select lastname,business FROM customers WHERE id=449; > Toleser|St Lawrence University, Biology Dept. > > Now, that's all fine and everything for interactive SQL, but when I'm > using this in a program (written in Delphi), the comma messes up the > returned values (since they are comma seperated). So, the Delphi > interface ends up returning: > Toleser|St Lawrence University|Biology Dept. > > That's no good :(. Does anyone have any suggestions on what to do about > this problem? I'm using SQLite 2.8.15. You can do escaping that replaces the comma with something else when writing to the table, then converts it back after you have gotten the info from the comma-separated format. For example, URLs regularly use %XX to escape characters such as space, where XX is the hexadecimal ASCII value. So "space" (ASCII 32) will be "%20". Just remember to escape not only the comma, but also the percentage sign or whatever signals your escape sequences. > As for my question about temporary tables: How long does SQLite keep the > temporary tables around? Only for 1 query? Or until the table hasn't > been modified for X amount of time? Or something I haven't thought of > yet... Can't answer this one, sorry. Ulrik -- Ulrik Petersen, Denmark
Re: [sqlite] Help compiling sqlite2
Hi, > Hi, > > I need to compile sqlite 2.8.15 under Linux and an other UNIX platform > with THREAD_SAFE, NDEBUG and -DTEMP_STORE=3 enabled. > If I write something like: > > ./configure --enable-tempdb-in-ram=always > > I can see in the makefile that -DTEMP_STORE is defined as 3. > But what about THREAD_SAFE and NDEBUG? You need to do this before you run configure (assuming bash is the shell): $ export BUILD_CFLAGS="-DTHREAD_SAFE=1 -DNDEBUG=1" $ export TARGET_CFLAGS="-DTHREAD_SAFE=1 -DNDEBUG=1" Read the top of the configure.ac script for why. > And in Linux, have I to manually add -lpthread to the Makefile? I don't know about this, but it would probably be in TARGET_CFLAGS if you need to add it. That would add it to the TCC Makefile variable, which in turn would add it to LTLINK, which is used when linking the libraries and executables. HTH Ulrik Petersen -- Ulrik Petersen, Denmark
Re: [sqlite] Why does my query take so long
Hi, > Hi, I am having a problem with the following query. It seems to force php > to timeout after 30secs. The query goes through 150K records. Is there > anything I can do to speed it up? > > code- > SELECT call_id, C.extn_no AS extn_no, dest, dest_name, call_time, > duration, cost, U.firstname AS firstname, U.surname AS surname > FROM call_data as C LEFT JOIN users as U on C.extn_no = U.extn_no > WHERE C.stamptime >= $unixtimestart > AND C.stamptime <= $unixtimeend > AND direction = 'Out' > ORDER BY cost desc LIMIT 0,16 > -- You can try using "C.stamptime BETWEEN $unixtimestart AND $unixtimeend" instead, and then put an index on C.stamptime. I have found that BETWEEN ... AND ... is faster than the "<= AND >=" version, especially if you put an index on the column. If you look at the VM code with EXPLAIN, you will see why. > > Lloydie-t Ulrik P. -- Ulrik Petersen, Denmark
Re: [sqlite] [ANN] SQLcrypt 1.0
Dennis Volodomanov wrote: Hi all, Is anyone using the mentioned library? Is it stable and fast? Are there any other similar products for SQLite v3? Can't answer the first question, but Dr. Hipp, the author and designer of SQLite, offers a version of SQLite (both 2.8 and 3.0) that does encryption: http://www.hwaci.com/sw/sqlite/prosupport.html#crypto HTH Ulrik P.
Re: [sqlite] upgrade?
Hi Michael, Michael Hunley wrote: Hi, I am currently using SQLite v 2.8.13 for a commercial product for Palm Handhelds. I am noticing some performance issues; most notably when I do a database validate() to verify my integrity at app open and close. I see that version 3.0.8 has some code optimizations, but am unsure if upgrading from the 2.8 series to the 3.0 series is just a drop in or if my code will need to change. Can anyone tell me if the API has changed in a notable way? The API has changed slightly. Mostly it's a matter of changing the sqlite_ prefix to sqlite3_, but you also need to change the name of the structure which you pass to sqlite3_step, and a few other changes. I can send you two pieces of code that show "before" and "after" use if you contact me off-list. Also, is my performance going to improve with the 3.0.8 over 2.8.13? If not, what about 2.8.15? I experienced a performance increase when I moved from 2.8.13 to 2.8.15, but a performance decrease when moving from 2.8.13 to 3.0.7. A recent thread on this list deals with this perceived decrease in performance (the thread is from around 5 October 2004, entitled "Degradation of performance in SQLite3?" -- you can find it in the archives, which are linked to from www.sqlite.org). PLEASE note that some people seem to experience performance *increases* when going from SQLite 2.8 to 3.0, so Your Mileage May Vary. Basically, Dr. Hipp said back then that SQLite 3 uses less disk space (and so fewer disk reads) at the expense of using more CPU cycles. This may or may not give you a performance increase, and in my case, it gave me a performance decrease, probably because the databases I have are so small that the operating system (Linux in my case) can map most of the file into virtual memory both for 2.8 and 3.0, and so the usage of more CPU cycles in 3.0 gives an overall performance decrease. However, there are several other good reasons to move to SQLite 3 than performance issues, and I'm sure that others can fill in the details. HTH. Cheers, Ulrik
Re: [sqlite] sqlite project--working with table structure
Hi Jim, > hi- > > i was hoping to get a little feedback on an idea i had. > > the create table statement can get complex with its variable number > field constraints and table constraints etc. etc. > when i first tackled the problem i tried to parse it. now i have a > different idea. what if i viewed the create table statement > as a group of tables with records . then when i am finished adding > records to the structure db tables i could call > a routine to write the create table statement based on the structure db > database for that particular table. > > a problem i see with this design is someone with a table already > designed would not want to use a program like this > because they have all ready generated the table structure in > sqlite_master.sql. > > i'll probably be the only user anyways. > > i thought i could use delphi personal edition and libsql to create an > interface in this manner to insert,update,delete,select > table structure data. > > is this a really bad idea? i have to try something. changing the > structure is the worst part of my program :-( > > thanks, > jim > If you go with this design, please make sure you include a unique autoincrementing column which always increases as you insert rows. To see how, go to http://www.sqlite.org/faq.html#q1 Then when you retrieve the rows to be created as columns, be sure to ORDER BY this autoincrement field. The reason is, the way I understand SQLite, if you delete a row with the DELETE statement, the row will not actually be deleted, only marked as deleted. Then the next time you insert a row, the row may be reused. At least that's how I understand it... could somebody please correct me if I'm wrong? But, you see, if I am right, and you don't have an autoincrement field to ORDER BY, then your columns may be inserted out of order, and thus retrieved out of order, and so your table will have a different column order when you inserted the rows representing the columns. HTH Cheers, Ulrik -- Ulrik Petersen, Denmark
Re: [sqlite] Speeding up quer
Hi again, > There are no indexes in may tables. Please find the following schemas for > my > tables. Would it make more sense to convert my datetime columns to > microtime?. What other recommendations would you make for these tables? > CREATE TABLE users ( > user_id INTEGER PRIMARY KEY, > extn_no varchar(16) default NULL, > username varchar(255) default NULL, > password varchar(255) default NULL, > admin NOT NULL default 'No', > depthead NOT NULL default 'No', > user NOT NULL default 'Yes', > firstname varchar(255) default NULL, > surname varchar(255) default NULL, > job_title varchar(255) default NULL, > user_email varchar(255) default NULL, > deleted NOT NULL default 'No' > ); > > CREATE TABLE call_data ( > call_id INTEGER PRIMARY KEY, > direction NOT NULL default 'Out', > group_no varchar(16) default NULL, > start_no varchar(16) default NULL, > extn_no varchar(16) NOT NULL default '', > trunk_no varchar(16) NOT NULL default '', > trans NOT NULL default 'No', > ddi varchar(16) default NULL, > dest varchar(32) NOT NULL default '', > dest_name varchar(255) default NULL, > duration varchar(8) NOT NULL default '', > ring_time varchar(5) default NULL, > call_time datetime default NULL, > cost decimal(10,2) default NULL, > band varchar(10) default NULL, > site_id varchar(10) default NULL > ); You could change the columns that seem to be boolean values to INTEGER and store "0" and "1" instead of "Yes" and "No". Because of the way SQlite 2 stores these things (namely as strings), this will most likely save you some space. Ulrik -- Ulrik Petersen, Denmark Homepage: <http://www.hum.aau.dk/~ulrikp/>
Re: [sqlite] Speeding up quer
Hi there, > I am have a problem with a query which may well have over 200,000 records. > I > have building a website using PHP and PHP is timing out after 30secs due > the > the size of the call_data table (I think). Is there anyway I can improve > the > following query so that it is faster. I think I am using sqlite 2.8.14 > (not > sure). > > SELECT call_id, C.extn_no AS extn_no, dest, dest_name, call_time, > duration, > cost, U.firstname AS firstname, U.surname AS surname FROM call_data as C > LEFT JOIN users as U on C.extn_no = U.extn_no WHERE 1 = '1' AND > julianday(call_time) >= julianday('2004-10-16 09:00:00') AND > julianday(call_time) <= julianday('2004-11-16 17:29:59') AND direction = > 'Out' ORDER BY cost desc LIMIT 0,16; Two things: 1) Why do you have the "1 = '1'" expression? It's just wasting processor time. 2) You can do preprocessing of the julian dates, converting the expressions (like '2004-11-16 17:29:59') into whatever format is stored in the database, then comparing that directly. This will allow you to use an index on call_time. Be aware, however, that maintaining an index can be time-consuming if you have many records going in and out. Right now, it is my guess that every record in the table will have to be read, in order to apply the juliandate() function. Thus no index is used, and every record is read. This is just an uneducated guess, though -- I haven't checked the EXPLAIN output. Ulrik P.
Re: [sqlite] SQLite V2 CAPI Reference
Hi, > Hi > > I have been trying to get a SQLite V2 pugin driver for Rekall working in > Windows, which was donated by one of our users. It includes a call to > sqlite_get_table_printf( m_sqlite, "SELECT sql FROM sqlite_master WHERE > name=%Q;", > , , , , ( const char* )oldName ); > which will not compile. It appears that the last parameter is wrong > (according to the compiler - Intell C++ V8.1). Since I don't have a copy > of > the SQLite V2 CAPI Reference, I am unable to correct this problem. > > I would be very grateful if somebody could tell me where I might find a > copy of the V2 CAPI. If it no longer exists and if some kind person still > has a copy, I would be grateful if that kind person would let me have a > copy I would be eternally grateful. If all else fails I would grateful if > somebody with much more experience of SQLite would help me overcome my > problem > > Thank you in advance. http://www.sqlite.org/c_interface.html Ulrik -- Ulrik Petersen, Denmark
Re: [sqlite] Sample code
Paul, You wrote: > Greetings all, > > I'm putting together a small test program based on some code I found in > "C/C++ Users Journal". The original code was in sqlite2, and I'm using > sqlite3. I'm having a couple of issues with the sqlite3_stmt definition > and its use in sqlite3_prepare and sqlite3_step. Does anyone have a small > example that I could refer to? I would greatly appreciate it. > > Thanks, > > Paul I wrote: > Otherwise, for a smaller example, you can look at my Emdros project, which > is under the GPL: Oops, I forgot that the online version does not use SQLite 3. Email me off-list if you want a copy of the code that uses SQLite 3. Ulrik -- Ulrik Petersen, Denmark Homepage: <http://www.hum.aau.dk/~ulrikp/>
Re: [sqlite] Sample code
Paul, > Greetings all, > > I'm putting together a small test program based on some code I found in > "C/C++ Users Journal". The original code was in sqlite2, and I'm using > sqlite3. I'm having a couple of issues with the sqlite3_stmt definition > and its use in sqlite3_prepare and sqlite3_step. Does anyone have a small > example that I could refer to? I would greatly appreciate it. > > Thanks, > > Paul The src/shell.c program in the SQLite distribution serves such a purpose. Otherwise, for a smaller example, you can look at my Emdros project, which is under the GPL: http://emdros.org/preview/index.php?dir==emdros-1.2.0.pre79.tar.gz You'll want to concentrate on EMdF/sqliteconn.cpp and EMdF/conn.cpp (the header files are include/sqliteconn.h and include/conn.h) HTH Ulrik P. -- Ulrik Petersen, Denmark
Re: [sqlite] Index Usage
Christian Jensen wrote: I noticed that you use { instead of ( What do those do? Sorry. They were meant as pseudo-syntax so that he could insert whatever his own value was. I did that because I didn't want him to write BETWEEN 10 AND 15-1 but rather calculate the 15-1 inside his program, and then do BETWEEN 10 AND 14 I guess I should have made that clear. Cheers, Ulrik -Original Message- From: Ulrik Petersen [mailto:[EMAIL PROTECTED] Sent: Thursday, October 28, 2004 10:28 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Index Usage William, William Hachfeld wrote: Hi, Have a question for everyone regarding index usage in SQLite... Say that I have the following database schema: CREATE TABLE Example ( id INTEGER PRIMARY KEY, grp INTEGER, begin INTEGER, end INTEGER ); and I want to perform the following query: SELECT id FROM Example WHERE grp=g AND x < end AND y >= begin; on a large number of rows (say around one million) for some group 'g' and an interval '[x, y)'. And, of course, with the assumption that (end begin) for all rows. Will my query performance be substantially improved by creating an index such as: CREATE INDEX MultiColumnIndex ON Example (grp, begin, end) or will the operators "<" and ">=" prohibit SQLite from using the index? I have almost the same table in my linguistic database, Emdros (http://emdros.org). What I have found that works best for me is to put an index on what you call "begin" (not a double index), then do SELECT id FROM Example WHERE grp = g AND begin BETWEEN {x} AND {y-1} AND end BETWEEN {x} AND {y-1}. For some strange reason, this is about 5% faster than what you were proposing. It could be because SQLite does not know that begin <= end, and so can't make optimizations about when to stop looking. Also, I'm aware that SQLite supports multi-column indicies, but not the use of multiple indicies per query. Is it possible to get around the later restriction by expressing my above query using a sub-select: SELECT id FROM (SELECT * FROM Example WHERE grp=g) WHERE x < end AND y >= begin; and then creating the following indicies instead: CREATE INDEX GroupIndex ON Example (group) CREATE INDEX IntervalIndex ON Example (begin, end) And if so, can any generalizations be made regarding the performance of using the two indicies versus the first, single, index? How about disk usage? I cannot comment on this, except that I've run EXPLAIN on my versions of the above queries, and found that SQLite wouldn't consult the "end" part of the (begin,end) index. Instead, it would consult the "end" part of the table column, and then only use the "begin" part of the index. At least that's how I understood the EXPLAIN output, but I may be wrong. The upshot of the above is that you can save diskspace by not doing the double index, and only indexing "begin", since for these queries, the "end" part is redundant (i.e., not used) in the index. Cheers, Ulrik -- Ulrik Petersen, MA, B.Sc. Emdros -- the text database engine for analyzed or annotated text http://emdros.org/ -- Ulrik Petersen, MA, B.Sc.
Re: [sqlite] Index Usage
William, William Hachfeld wrote: Hi, Have a question for everyone regarding index usage in SQLite... Say that I have the following database schema: CREATE TABLE Example ( id INTEGER PRIMARY KEY, grp INTEGER, begin INTEGER, end INTEGER ); and I want to perform the following query: SELECT id FROM Example WHERE grp=g AND x < end AND y >= begin; on a large number of rows (say around one million) for some group 'g' and an interval '[x, y)'. And, of course, with the assumption that (end > begin) for all rows. Will my query performance be substantially improved by creating an index such as: CREATE INDEX MultiColumnIndex ON Example (grp, begin, end) or will the operators "<" and ">=" prohibit SQLite from using the index? I have almost the same table in my linguistic database, Emdros (http://emdros.org). What I have found that works best for me is to put an index on what you call "begin" (not a double index), then do SELECT id FROM Example WHERE grp = g AND begin BETWEEN {x} AND {y-1} AND end BETWEEN {x} AND {y-1}. For some strange reason, this is about 5% faster than what you were proposing. It could be because SQLite does not know that begin <= end, and so can't make optimizations about when to stop looking. Also, I'm aware that SQLite supports multi-column indicies, but not the use of multiple indicies per query. Is it possible to get around the later restriction by expressing my above query using a sub-select: SELECT id FROM (SELECT * FROM Example WHERE grp=g) WHERE x < end AND y >= begin; and then creating the following indicies instead: CREATE INDEX GroupIndex ON Example (group) CREATE INDEX IntervalIndex ON Example (begin, end) And if so, can any generalizations be made regarding the performance of using the two indicies versus the first, single, index? How about disk usage? I cannot comment on this, except that I've run EXPLAIN on my versions of the above queries, and found that SQLite wouldn't consult the "end" part of the (begin,end) index. Instead, it would consult the "end" part of the table column, and then only use the "begin" part of the index. At least that's how I understood the EXPLAIN output, but I may be wrong. The upshot of the above is that you can save diskspace by not doing the double index, and only indexing "begin", since for these queries, the "end" part is redundant (i.e., not used) in the index. Cheers, Ulrik -- Ulrik Petersen, MA, B.Sc. Emdros -- the text database engine for analyzed or annotated text http://emdros.org/
Re: [sqlite] Page sizes other than 1024 bytes
> Is there anybody using a non-power-of-2 database page size? > If I modify SQLite so that you cannot select a page size > that is not a power of two, will it break anybody's code? I don't use a non-power-of-2 database page size, so I can't comment on the above. However, slightly off-topic, but still about page sizes: I have found empirically that a page size of 4096 gives better performance on Windows (particularly Windows 9X). The SQLite source (pager.h if I am not mistaken) says that a page size of 1024 seems to be best. This is correct in so far as Linux doesn't see any performance increase with larger page sizes (I've verified this empirically, too, for 4096 and 16384). However, if others have experienced the same as I have, it might be good to add something to the comment in pager.h about 4096 being a better page size on Win9X. HTH Ulrik Petersen -- Ulrik Petersen, Denmark Emdros -- the text database engine for analyzed or annotated text http://emdros.org/
Re: [sqlite] Degradation of performance in SQLite 3?
Dr. Hipp, > Ulrik Petersen wrote: >> >> has anyone experienced less performance with SQLite 3.0.7 over 2.8.13 on >> the same data? That is what I am experiencing. I'd appreciate help in >> figuring out why and perhaps what I can do about it. >> > > SQLite 3.0 requires less disk I/O at the expense of using more CPU cycles. > So if you have a fast disk and a slow CPU, SQLite 3.0 might well be > slower. On the other hand, a slow disk connected to a fast CPU will > make SQLite 3.0 faster. On my 3-year-old Athlon with an 7200RPM > IDE disk, SQLite 2.8 and 3.0 are about the same speed. But I figured > that CPUs tend to increase in speed more rapidly that disk drives, so > it was best to optimize for a faster CPU. > > Might this explain the result you are seeing? Do you (perhaps) have an > older CPU and/or an exceptionally fast disk drive? Thanks for the information. I have an AMD Athlon 3000+ with a 7200RPM IDE disk running at ATA100, so I am not sure whether that explains it. I may try it on one of my other computers and see what benchmark results I can get. Does SQLite 3 take longer to parse the schema at startup? The benchmark queries I run are run in strict, non-overlapping sequence, with a full sqlite3_open and sqlite3_close in separate processes. Yet even when I concatenate the queries and run them all in one go, SQLite 2.8 is faster than SQLite 3. Thanks in advance. Ulrik
[sqlite] Degradation of performance in SQLite 3?
Hi all, has anyone experienced less performance with SQLite 3.0.7 over 2.8.13 on the same data? That is what I am experiencing. I'd appreciate help in figuring out why and perhaps what I can do about it. I have a linguistic database (it's from my project, Emdros http://emdros.org/) and the relevant parts of the schema look like this: CREATE TABLE clause_monad_ms ( object_id_d INT NOT NULL, mse_first INT NOT NULL, mse_last INT NOT NULL, is_first CHAR(1) NOT NULL DEFAULT 'N', PRIMARY KEY (object_id_d, mse_first) ); CREATE TABLE clause_objects( object_id_d INTEGER PRIMARY KEY, first_monad INT NOT NULL, last_monad INT NOT NULL, mdf_text_type TEXT NOT NULL , mdf_number_within_sentence INT NOT NULL , -- etc etc. plus other data for the clause ); -- similarly for phrase CREATE TABLE word_gut( object_id_d INTEGER PRIMARY KEY, first_monad INT NOT NULL, last_monad INT NOT NULL, mdf_word_number INT NOT NULL , mdf_verbal_tense INT NOT NULL , -- etc etc. plus many other columns containing word-data ); CREATE INDEX clause_mm_monads_i ON clause_monad_ms (mse_first, mse_last); CREATE INDEX clause_mm_o_i ON clause_monad_ms (object_id_d); CREATE INDEX clause_o_fm_i ON clause_objects (first_monad); CREATE INDEX clause_o_lm_i ON clause_objects (last_monad); CREATE INDEX word_g_flm_i ON word_gut (first_monad, last_monad); My application, Emdros, is a query engine for linguistic data. I have a suite of Emdros queries that I run to test the speed of Emdros. Emdros translates these queries into a series of SQL queries. I've ported Emdros over to SQLite 3.0.7, but experienced between 27% and 49% speed degradation over 2.8.13. This is on a 137MB database with around 1.7 million rows in various tables (up to 430,000 rows in one table, namely the word_gut table). I use the sqlite3_prepare/step interface, with sqlite3_column_XXX calls to get the data. Has anyone experienced anything similar? Can anyone suggest ways I could improve the above schema and/or indexes? Thanks in advance. Ulrik Petersen PS: Kudos to Dr. Hipp and all the contributors for making SQLite such a worthwhile and pleasant piece of software to interact with. -- Ulrik Petersen, Denmark Emdros - the text database engine for analyzed or annotated text http://emdros.org
[sqlite] Re: sqlite-users Digest 22 May 2004 05:23:11 -0000 Issue 115
Hello all, Michael Roth wrote: > and I wrote: - Microsoft Visual C++ Toolkit 2003. Microsoft recently released their compiler and toolchain for free download: http://msdn.microsoft.com/visualc/vctoolkit2003/ Check the license! It is not really fair! Don't use this toolchain. Oops, I hadn't read the license (nor used the software). The licensing restrictions are rather agressive towards all Open Source software. Read especially section 3.2. The EULA is here: http://msdn.microsoft.com/visualc/vctoolkit2003/eula.aspx IANAL, but the way I understand it, you can't link against their libraries and still distribute your code under an Open Source license, or distribute your binaries under a license that requires that the software be offered at no charge. My understanding may be flawed, so read the EULA yourself before deciding whether the toolchain is for you. Sorry for suggesting Microsoft's "free" toolchain. I didn't know any better. Cheers, Ulrik - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Newbie --question about multiple PCs accessing sqlite
Hello Shamil, [EMAIL PROTECTED] wrote: I do not have a C++ compiler If you are using Linux or some other Unix-like environment, you can get g++ (i.e., gcc) for free. If you are using Windows, there are several options for getting one (also for free): - Mingw or Cygwin (google for each) - Microsoft Visual C++ Toolkit 2003. Microsoft recently released their compiler and toolchain for free download: http://msdn.microsoft.com/visualc/vctoolkit2003/ - Borland also has a free command-line version of their C++ compiler on their website. HTH. Ulrik - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]