[sqlite] which db admin tool to use for the java wrapper??
hi- i tried dgSqlite admin tool and it says the db is loaded but when i try to run a query against a table it says table does not exist. when i query the same table using my eclipse set up the query returns a result. there has to be an explanation. i know dgSqlite is not broke. i'd like to use a free tool that automates editing and creation of tables for db's using the werner java wrapper. anyone know what is wrong or share a similar experience? thanks, jim __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
RE: [sqlite] Pre-compiled SQL using the commandline interface
If you're going to all the trouble to write a program in C#, why not just use the ADO.NET data provider for SQLite and insert into the databsae yourself? Seems rather silly to generate an insert statement and not actually execute it in your code. The command-line interface to sqlite isn't designed for prepared statements and parameterized queries. If you're using VS2003/.NET 1.1, use the Finisar ADO.NET library at http://sourceforge.net/projects/adodotnetsqlite If you're using VS2005/.NET 2.0, use my ADO.NET provider at http://sourceforge.net/projects/sqlite-dotnet2 Robert > -Original Message- > From: Alan McGovern [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 23, 2005 5:51 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Pre-compiled SQL using the commandline interface > > Hi, > > I can't seem to find information about creating precompiled > statements when using the commandline program to access the > sqlite database. What i'm doing at the moment is using a C# > program to generate text files of insert statements up to > 100megs in size and then using .read to import them. Each > insert statement is identical except for the values that are > being imported, so in this scenario if i could use > precompiled SQL, it would result in quite a benefit, but i > can't seem to be able to do this. > > Thanks, > Alan. >
[sqlite] java wrapper question solved
hi- it is printing data in the console. i ran across some code that helped. thanks, jim new code /* * Created on Aug 23, 2005 * * TODO To change the template for this generated file go to * Window - Preferences - Java - Code Style - Code Templates */ package calcpackage; import java.lang.System; /** * @author HP_Owner * * TODO To change the template for this generated type comment go to * Window - Preferences - Java - Code Style - Code Templates */ class calc { static final String JDBC_DRIVER ="SQLite.JDBCDriver"; static final String DATABASE_URL = "jdbc:sqlite://C:/calcDB/t1.db"; public static void main(String args[]) { try { SQLite.Database d4 = new SQLite.Database(); d4.open("t1.db",0); String arr4[] = new String[1]; arr4[0] = "tab%"; // d4.exec("create table mytbl (a integer)", // null, arr4); d4.exec("insert into mytbl values(18)", null, arr4); System.out.println(d4.get_table("select * from mytbl")); System.out.println(d4.dbversion()); d4.close(); } catch(SQLite.Exception myException) { myException.printStackTrace(); } } } Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs
[sqlite] ok wrapper...what gives???
hi- i am trying to use christian werners wrapper with mixed results. it recognizes the db version but nothing prints out in the console of eclipse when i run this code. if anyone else is using it could you take a glance and see why i am not getting a console print out of the data? i get this version number for my console print out. 3.2.1 i tested the table for data with the command line utility. it is the right table and the data is in there. thanks very much, jim package calcpackage; import SQLite.Database; import SQLite.Callback; import java.lang.System; import SQLite.TableResult; /** * @author HP_Owner * * TODO To change the template for this generated type comment go to * Window - Preferences - Java - Code Style - Code Templates */ public class calc { static final String JDBC_DRIVER ="SQLite.JDBCDriver"; static final String DATABASE_URL = "jdbc:sqlite://C:/calcDB/t1.db"; public static void main(String args[]) { try { SQLite.Database d4 = new SQLite.Database(); d4.open("t1.db",0); System.out.print(d4.get_table("select * from mytbl")); System.out.print(d4.dbversion()); d4.close(); } catch(SQLite.Exception myException) { myException.printStackTrace(); } } } Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs
Re: [sqlite] "where not exists (union-select)" fails on 2nd where
> SELECT * FROM PRIM AS P > WHERE NOT EXISTS > ( > SELECT REFID FROM REF1 WHERE REF1.REFID=P.ID > UNION > SELECT REFID FROM REF2 WHERE REF2.REFID=P.ID > ); I looks like correct SQL according to the SQLite docs, but I don't understand why you coded the select that way. You should get the same result from select * from prim as p where not exists (select refid from ref1 where refid=p.id) and not exists (select refid from ref2 where refid=p.id) I would guess that this form would be more efficient because if the first test fails, the second sub-select should not be executed, perhaps saving a complete scan of ref2. Regards
[sqlite] "where not exists (union-select)" fails on 2nd where
Hi folks, I've got a little problem with a - at least I think so - correct SQL- statement: three tables, two referencing the 1st one --- SELECT * FROM PRIM AS P WHERE NOT EXISTS ( SELECT REFID FROM REF1 WHERE REF1.REFID=P.ID UNION SELECT REFID FROM REF2 WHERE REF2.REFID=P.ID ); --- chokes with "SQL error: no such column: P.ID"; as long as I do not use a WHERE statement in the 2nd 'inner' SELECT statement, it works as expected. I've worked around this by creating a VIEW from the union of the two referencing tables so I do not need the UNION in the original query. Is this a bug or did I just not get the docs? thanks, -Markus PS: sqlite really rocks & the C-API doc is very nice! --- Markus W. Weissmann http://www.mweissmann.de/ http://www.opendarwin.org/~mww/
Re: [sqlite] read and update record at the same time
On Tue, 2005-08-23 at 21:46 +0200, tone skoda wrote: > read (SELECT) has to find physical position of record on disk. > update too has to find physical position of record on disk. > this is inefficient if it's done twice in a row, because two searches for > record have to be performed. > only one search would be enough and more efficient. > is there any way SELECT and UPDATE can be done in in one SQL statement? > > in fact, why isn't it possible to refer to records by their physicall offset > on > disk? as far as i know thay don't change. > SQLite supports variable-length records with automatic compaction and defragmentation. Records are subject to being moved about on the disk after any change to nearby records. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] read and update record at the same time
read (SELECT) has to find physical position of record on disk. update too has to find physical position of record on disk. this is inefficient if it's done twice in a row, because two searches for record have to be performed. only one search would be enough and more efficient. is there any way SELECT and UPDATE can be done in in one SQL statement? in fact, why isn't it possible to refer to records by their physicall offset on disk? as far as i know thay don't change. http://www.email.si/
[sqlite] Please unsubscribe me
I have tried uncuccessfully to unsubscribe several times PLEASE REMOVE ME FROM THIS LIST - Appel audio GRATUIT partout dans le monde avec le nouveau Yahoo! Messenger Téléchargez le ici !
Re: [sqlite] Speed.html
I just ran a speed comparison between version 2.8.16 and 3.2.3. Version 3.2.3 is faster in almost every case. See http://www.sqlite.org/speed-2816-v-323.html -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Speed.html
On Tue, 2005-08-23 at 17:36 +0200, Steven Van Ingelgem wrote: > Hi, > > I just redid the speedtest.tcl-script in PHP > (http://www.karels0ft.be/tmp/speed_script.html). > > Can anyone check why the SQLite3's are so slow compared to SQLite2's ? > > Aha! Probably you are using an older test script that tries to run of synchronous using PRAGMA default_synchronous=OFF; The "default_synchronous" pragma was dropped form SQLite 3 because it was considered too dangerous. There was too much risk of data loss and database corruption after a power failure. Thus the synchronous behavior is never being disabled in your tests and you are reporting synchronous times in the "no-sync" column. Another thing to note is that SQLite3 has PRAGMA synchronous=FULL by default whereas version 2 had synchronous=ON by default. FULL is a little slower to commit, but it is also safer in the face of power failures. In spite of this handicap, SQLite version 3 still manages to be faster than version 2 in many tests. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Speed.html
On Tue, 2005-08-23 at 17:36 +0200, Steven Van Ingelgem wrote: > Hi, > > I just redid the speedtest.tcl-script in PHP > (http://www.karels0ft.be/tmp/speed_script.html). > > Can anyone check why the SQLite3's are so slow compared to SQLite2's ? > > Most of the important SQLite3 times are faster than SQLite2's. What times are you specifically concerned about? Perhaps you are concerned about Test1 with SQLite3 set to no-sync. It appears that the no-sync is not working. I'll look into it. On the other hand, you should always leave synchronization enabled to prevent data loss after a power failure. So this is not a real high priority. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Speed.html
- Original Message - From: "Steven Van Ingelgem" <[EMAIL PROTECTED]> To: Sent: Tuesday, August 23, 2005 8:36 AM Subject: [sqlite] Speed.html Hi, I just redid the speedtest.tcl-script in PHP (http://www.karels0ft.be/tmp/speed_script.html). Can anyone check why the SQLite3's are so slow compared to SQLite2's ? This script maybe could be used by someone with too much time to check up the timings of the latest versions, to recreate the speed.html-document, as it's a little outdated. [http://www.karels0ft.be/tmp/speed.html] To be complete, you should include prepared statement execution tests in your speed comparisons. Most modern databases are designed around a prepared statement/parameterized execution model, and you're not getting ideal performance when you're inserting or updating thousands and thousands of rows by manufacturing an INSERT/UPDATE statement for each row. Even your select tests would benefit from being prepared once and queried using parameters. As an example, using a prepared statement and an integer parameter inside a transaction, I can insert about 100,000 rows a second into an indexed SQLite3 table using my ADO.NET 2.0 provider in C#. (Computer is a 3.2ghz HT laptop running XP Media Center Ed.). Times will of course drop when inserting strings or adding multiple parameters, but in theory prepared statements using parameters should always win over self-generated statements in all bulk op tests. Robert
[sqlite] Speed.html
Hi, I just redid the speedtest.tcl-script in PHP (http://www.karels0ft.be/tmp/speed_script.html). Can anyone check why the SQLite3's are so slow compared to SQLite2's ? This script maybe could be used by someone with too much time to check up the timings of the latest versions, to recreate the speed.html-document, as it's a little outdated. [http://www.karels0ft.be/tmp/speed.html] Reactions would be nice too :) Greetings, Steven
Re: [sqlite] How to insert a binary file into the database of sqlite in C++?
我本楚狂人 wrote: I have search with Google , and find these information as below sqlite3_prepare(..., "insert into foo values(?);", -1, &stmt, ...); sqlite3_bind_blob(stmt, 1, "bar", 3, SQLITE_TRANSIENT); sqlite3_step(stmt); But if there is a file in this path "C:\a.mp3", I use "ifstream mp3("C:\\a.mp3") open this file . Then I don't know how can I insert it to the database with the pointer "sqlite3 *db; " (the database has two column, the first's type is text(to write the name of file) ; the second's type is blob(to write the binary file ) ) Would you please give me some code about this problem?Thank you. And another question, What's the lastest parameter's mean of sqlite3_prepare? I have read the help of this problem , but I can't understand. This (or something like it) should do what you want, but I'm not sure if putting large objects like MP3 files into the database is really a good idea. Others have said they had better performance using the file system to store large files and simply saving the file names in the database. ifstream mp3("C:\\a.mp3"); mp3.seekg(0, ios::end); long sz = mp3.tellg(); mp3.seekg(0, ios::beg); stringstream sbuf; sbuf << mp3.rdbuf(); char* buf = sbuf.str(); sqlite3_prepare(..., "insert into foo values(?);", -1, &stmt, ...); sqlite3_bind_blob(stmt, 1, buf, sz, SQLITE_TRANSIENT); sqlite3_step(stmt); HTH Dennis Cote
Re: [sqlite] malformed database schema - near "AUTOINCREMENT": syntax error
> "malformed database schema - near "AUTOINCREMENT": syntax error" It might help if you published your schema. Also, it would be interesting to know whether your v3.2.3 passed all the tests in the test suite. Regards
RE: [sqlite] Can you use random(*) to retrieve a pseudo random rowfrom 3.2.3?
>Your logic is broken. As every of your row's start_col is less than >end_row, the result set will be empty for every possible value of >rand.number. >Don't even know what you expected to get with this query. Prize to Jacob for spotting the (now) obviously mistake. The following SQL works successfully: sqlite> select * from MyTable join (select random(*) as number) as rand where rand.number between start_col AND end_col; Many Thanks Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
Re: [sqlite] malformed database schema - near "AUTOINCREMENT": syntax error
On Aug 23, 2005, at 5:10 AM, Christiane Lemke wrote: Dear Mailing List users, I am new to SQLite and didn't find help on the mailing list archives or google for my problem. I am using SQLite in my CGI/C program. I got it running perfectly under three Linux systems (one ubuntu, two gentoos), but now that I want to run the program under a kubuntu system, I get a "malformed database schema - near "AUTOINCREMENT": syntax error" I had a similar experience where everything worked well on my Mac (SQLite version 321) but gave me the above error on Windows (SQLite version 320, I think... well, definitely not 321). Upgrading on Windows settled everything. Weird. -- Puneet Kishor
[sqlite] Pre-compiled SQL using the commandline interface
Hi, I can't seem to find information about creating precompiled statements when using the commandline program to access the sqlite database. What i'm doing at the moment is using a C# program to generate text files of insert statements up to 100megs in size and then using .read to import them. Each insert statement is identical except for the values that are being imported, so in this scenario if i could use precompiled SQL, it would result in quite a benefit, but i can't seem to be able to do this. Thanks, Alan.
Re: [sqlite] Can you use random(*) to retrieve a pseudo random r owfrom 3.2.3?
Brandon, Nicholas wrote: > CREATE TABLE MyTable(start_col int,end_col int); > INSERT INTO "MyTable" VALUES(-2, 2); ... > sqlite> select * from MyTable join (select random(*) as number) as rand > where start_col >= rand.number and end_col < rand.number; Your logic is broken. As every of your row's start_col is less than end_row, the result set will be empty for every possible value of rand.number. Don't even know what you expected to get with this query.
[sqlite] malformed database schema - near "AUTOINCREMENT": syntax error
Dear Mailing List users, I am new to SQLite and didn't find help on the mailing list archives or google for my problem. I am using SQLite in my CGI/C program. I got it running perfectly under three Linux systems (one ubuntu, two gentoos), but now that I want to run the program under a kubuntu system, I get a "malformed database schema - near "AUTOINCREMENT": syntax error" and an SQL_ABORT when issuing queries by the sqlite3_exec function. I used the very same database file on each of those machines, only difference so far as I can see, is that the kubuntu machine uses SQLite 3.2.3 compiled from the sources from the website, while the others use 3.2.2. I can even browse the database with the sqlite3 command line tool on the same computer. I'd be very grateful for any idea or hint. Thanks a lot, Christiane
RE: [sqlite] Can you use random(*) to retrieve a pseudo random r owfrom 3.2.3?
>Nick, > >I just thought I should clarify my SQL. The select clause should be > >select * from MyTbale join... > >since the random number is already included in each row of the joined >table. >Dennis Cote Thanks Dennis for the tip. Did you give it try? I've tried it and its not selecting any rows after a couple hundred attempts. I appreciate its "random" but to test I subsequently put in a row with the min/max random values and that row is still not selected. CREATE TABLE MyTable(start_col int,end_col int); INSERT INTO "MyTable" VALUES(-2, 2); INSERT INTO "MyTable" VALUES(-21, 21); INSERT INTO "MyTable" VALUES(-21, 21); INSERT INTO "MyTable" VALUES(-2147483648, 2147483647); sqlite> select * from MyTable join (select random(*) as number) as rand where st art_col >= rand.number and end_col < rand.number; sqlite> Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
[sqlite] CAST documentation error in lang_expr.html
Hello DRH, there is a documentation error in lang_expr.html. The current HTML reads: "A CAST expression changes the datatype of the into the type specified by . can be any non-empty type name that if valid for the type in a column definition of a CREATE TABLE statement." The in the TCL srcipt not properly converted and interpreted as a HTML tag which is not displayed by the browser. Regards, Ralf The Delphi Inspiration product brief:http://www.yunqa.de/delphi/ DIUcl: The Delphi port of the popular UCL Compression Library using the same lossless algorithm as the UPX Ultimate Packer for eXecutables. Super- fast, realtime decompression. Ratio up to ZIP and BZIP2. Freeware.
Re: [sqlite] Why can i open a textfile?
Wow, that suprises me, i just stepped of the _gettable() call to get me the result using prepare and step. So i finally could read the $NUL BLOB data. I can't imagne what would be better since a forward only behaviour is the best there is. Besides it's side-effects of not being able to navigate back. There are people like me they never heard about TCL, i don't know what it is and also what schema's are. It's an area i never visit since it's not familiar to me. I can not imagne to do things like this not using your API. I don't think i'm blind for other things but there siomply are programmers used to use plain functions. To make sure you understand, through PowerBASIC i'm able to connect to your sqlite through api's. The issue is that conversion from your api to a BASIC syntax is pretty hard. Espec. if calls have pointers which are in fact only interesting for including the lib into c but is irrellevant to dll users. Don't get me wrong, it's not to annoy you, just want to show my perspective and might even help to consider this for the future. Like a major rewrite to standard calls and making use of not-so-c syntax (pointers/casting or whatever removed, just an example) (Again: it's not handy to show *sqlite in a param but simply have a byval hstmt as long (but then in c syntax :) ) would be much better since it does not tell unecessary info that *sqlite is actually a pointer to a structure or similar, this might reach more end-users) I usually keep in mind a 'dumb' VB should be able to access it. If that works it usually works with anything. Using wrappers is no option to me unless it's for dotnet (asp.net), i fell dumb if i would use a wrapper lib. Of course the PowerBASIC code being spread for sqlite gives me a headstart to access the dll. But the code is not my taste so i do it myself. Is there any reason not to stick with prepare() and step()? Works for me at this time. :) - Original Message - From: "D. Richard Hipp" <[EMAIL PROTECTED]> To: Sent: Tuesday, August 23, 2005 1:28 AM Subject: Re: [sqlite] Why can i open a textfile? On Tue, 2005-08-23 at 00:04 +0200, Edwin Knoppert wrote: All i meant is that sqlite is created by a c programmer, like my colleague he seems to forget there are different languages but he doesn't care. Actually, I'm a TCL programmer. I only resort to C code to write new TCL extensions. I personally never use the SQLite C API except when I am working on internals of the TCL bindings. I use SQLite in my professional work a lot, but I always access it through the TCL bindings. Even when I am working on SQLite itself, I almost always access it through the TCL bindings. (All of the test scripts are writtne in TCL.) And this brings up an important point: I'm increasingly aware of the fact that the C API to SQLite really wants to be wrapped. There are a lot of loose ends that can cause complication and trouble if you try to use the C API directly in your application. Things like the infamous SQLITE_SCHEMA returns. And the notorious SQLITE_BUSY. And then the recent conversation about how to tell if the file is really a database or not. These kinds of things are best dealt with once in a wrapper and then forgotten. You do not want to have to be worrying about error return codes when you are trying to make the next great klller app. Programmers need to save their brain cycles to focus on the really hard problems, not on handling lots of pesky return codes. Most people who are using SQLite successfully have, I imagine, either written their own wrappers around the core API (which is not hard as I do provide you with a lot of helper routines such as sqlite3_vmprintf and friends) or they are using an existing wrapper written by someone else. If you are not doing this - if you are trying to make low-level SQLite API calls like sqlite3_prepare and sqlite3_step for each bit of SQL you want to run, then let me suggest that you are using the library incorrectly. That is way too much work. I expose those calls because they provide you with a lot of control and give wrapper writers the freedom to do lots of interesting things with the database. If you are working on an application that just needs access to data, sqlite3_prepare and sqlite3_step are way too low level. Find a wrapper suitable for your needs and use it instead. Or write your own wrapper if an appropriate one can't be found. This will result in a program that is easier to write, easier to maintain, has fewer bugs, and just works better. -- D. Richard Hipp <[EMAIL PROTECTED]>