Re: [sqlite] Functions and index

2011-06-28 Thread hilaner
On 2011-06-27 17:34 Simon Davies simon.james.dav...@gmail.com wrote: select julianday( ( select max( day_date ) from days ) ); Of course I tried this, but with a single bracket I got a syntax error. With double bracket it works. Thanks! Adam ___

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

2011-06-28 Thread Black, Michael (IS)
I'd recommend NOT relying on the system sqlite3. That way you can control your changes. Get the amalgamation and put sqlite3.c and sqlite3.h in your project. And, you forgot to put in the name for -o -- so you would get a file named -lsqlite3 in your directory. And you'll probably need

[sqlite] Using Unicode to display data?

2011-06-28 Thread Gilles Ganault
Hello After importing DBF files into SQLite, it looks like data are in Unicode, so I get funny characters when running sqlite3.exe in a DOS box on Windows: sqlite select * from varmod_stent2010 limit 5; A10|BE|Industrie manufacturiFre, industries extractives et autres A10|FZ|Construction

Re: [sqlite] Using Unicode to display data?

2011-06-28 Thread Richard Hipp
On Tue, Jun 28, 2011 at 7:24 AM, Gilles Ganault gilles.gana...@free.frwrote: Hello After importing DBF files into SQLite, it looks like data are in Unicode, so I get funny characters when running sqlite3.exe in a DOS box on Windows: SQLite uses only unicode (utf8 by default, but it

Re: [sqlite] Using Unicode to display data?

2011-06-28 Thread Igor Tandetnik
Gilles Ganault gilles.gana...@free.fr wrote: After importing DBF files into SQLite, it looks like data are in Unicode Actually, it doesn't look like Unicode, but rather some ANSI codepage (my guess would be 1252, Western European). Show the output of this statement: select hex(name) from

[sqlite] help needed to optimize a query

2011-06-28 Thread Lazarus 101
Hi guys, i'm working on an Android app and using sqlite to store some data and i need some help with a query. I have the following table structure: FILES file_id INTEGER NOT NULL, name TEXT DATA file_id INTEGER REFERENCES files(resource_id) ON DELETE CASCADE, data_type TEXT, value TEXT

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Pavel Ivanov
This has to run as fast as possible. A left join between these tables is too slow, for 10.000 entries it takes around 15 seconds just to navigate through the cursor, if I add a where clause selecting only one kind of data then it reduces to less than 5 seconds which is acceptable. What kind

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Simon Slavin
On 28 Jun 2011, at 3:33pm, Lazarus 101 wrote: FILES file_id INTEGER NOT NULL, name TEXT I assume that SQLite has identified 'file_id' as its own 'rowid' column and made in INDEX for it. DATA file_id INTEGER REFERENCES files(resource_id) ON DELETE CASCADE, data_type TEXT, value TEXT

[sqlite] Auto index with wrong number of entries

2011-06-28 Thread Adam DeVita
Good day, Following a data collection reporting error from a workstation, I have found that pragma integrity_check reported that 2 of my tables have a few thousand entries missing in their auto indexes. wrong number of entries in index sqlite_auto_index_tablename_1 rowid 87973 missing from

Re: [sqlite] Auto index with wrong number of entries

2011-06-28 Thread Simon Slavin
On 28 Jun 2011, at 4:22pm, Adam DeVita wrote: I can see the data that I want to export. How do I fix these indexes? Use the sqlite3 command-line shell to dump the database to SQL commands, then create a new database by reading it back in. While the data is in the SQL command file, you can

Re: [sqlite] Using Unicode to display data?

2011-06-28 Thread Gilles Ganault
On Tue, 28 Jun 2011 07:48:09 -0400, Richard Hipp d...@sqlite.org wrote: SQLite uses only unicode (utf8 by default, but it also works with utf16). Probably your DBF file was exported as MBCS. You need to convert the MBCS from the export into utf8 or utf16 prior to import into SQLite. Thanks for

Re: [sqlite] Selecting indexes to use NOT INDEXED

2011-06-28 Thread Mohit Sindhwani
Hi Igor and Puneet, On 27/6/2011 11:47 PM, Igor Tandetnik wrote: You can suppress the index on CAT with a unary plus operator, like this: ... AND +CAT=25; Thanks for the suggestions. I'll try these :) Best Regards, Mohit. 28/6/2011 | 11:50 PM.

[sqlite] Documentation for when/what exceptions are thrown?

2011-06-28 Thread Down, Jason
I'm having trouble finding documentation for the Sqlite.Net data provider (System.Data.Sqlite.dll - http://sqlite.phxsoftware.com/) (new development has forked here - http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki) that lists what exceptions are thrown by the various classes

Re: [sqlite] Documentation for when/what exceptions are thrown?

2011-06-28 Thread Alessandro Caliaro
you should find in C:\Program Files\SQLite.NET\Doc\ -Messaggio originale- From: Down, Jason Sent: Tuesday, June 28, 2011 6:01 PM To: 'sqlite-users@sqlite.org' Subject: [sqlite] Documentation for when/what exceptions are thrown? I'm having trouble finding documentation for the Sqlite.Net

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

2011-06-28 Thread Jan Hudec
On Tue, Jun 28, 2011 at 11:07:50 +, Black, Michael (IS) wrote: I'd recommend NOT relying on the system sqlite3. That way you can control your changes. Let me express very, very strong disagreement with that. In Linux you should *always* use system sqlite and specify minimal required

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

2011-06-28 Thread Stephan Beal
On Tue, Jun 28, 2011 at 6:45 AM, Phong Cao phn...@gmail.com wrote: However, the program was not compiled. I also read on some forums saying that sqlite3 must be compiled with gcc. But since I am using gtkmm and C++ code for my project I wonder if there is anyway possible to compile sqlite3

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Jan Hudec
On Tue, Jun 28, 2011 at 17:33:23 +0300, Lazarus 101 wrote: Hi guys, i'm working on an Android app and using sqlite to store some data and i need some help with a query. I have the following table structure: FILES file_id INTEGER NOT NULL, Do file_ids repeat? If

[sqlite] Documentation for when/what exceptions are thrown?

2011-06-28 Thread Down, Jason
Wow, not sure how I missed that. Seems obvious now thanks. I do have one more question though. While this does show great documentation, it still does not tell me what methods throw certain exceptions. For example, if I look at SqliteCommand.ExecuteNonQuery, it does not list that it can throw

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

2011-06-28 Thread Simon Slavin
On 28 Jun 2011, at 5:34pm, Jan Hudec wrote: On Tue, Jun 28, 2011 at 11:07:50 +, Black, Michael (IS) wrote: I'd recommend NOT relying on the system sqlite3. That way you can control your changes. Let me express very, very strong disagreement with that. In Linux you should *always*

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

2011-06-28 Thread Stephan Beal
On Tue, Jun 28, 2011 at 7:53 PM, Simon Slavin slav...@bigfraud.org wrote: On 28 Jun 2011, at 5:34pm, Jan Hudec wrote: Let me express very, very strong disagreement with that. In Linux you should *always* use system sqlite and specify minimal required version as desired This works

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

2011-06-28 Thread Stephan Beal
On Tue, Jun 28, 2011 at 8:04 PM, Stephan Beal sgb...@googlemail.com wrote: You're both very right, and might i suggest a compromise: in my latest Might i add that all involved machines were some flavour of Linux, which favour's Simon's argument against relying on the system's sqlite3. --

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

2011-06-28 Thread Simon Slavin
On 28 Jun 2011, at 7:04pm, Stephan Beal wrote: in my latest sqlite3-using project i structured the build so that if sqlite3.[ch] are found in the build tree, that sqlite3 is used, otherwise we use whatever's on the system. i did that because when i launched my project on my web hoster i

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

2011-06-28 Thread Stephan Beal
On Tue, Jun 28, 2011 at 8:13 PM, Simon Slavin slav...@bigfraud.org wrote: That's clever. And it allows quick regression testing in case something mysteriously stops working. :-D Here's the makefile code... it of course relies on other project details, but you'll get the idea:

[sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

2011-06-28 Thread thilo
Hello there! I found a strange behavior while doing a select with a sub select that has a where clause with a value (here 'a') which is the same as a column id: What am I missing here ? SQLite version 3.7.5 Enter .help for instructions Enter SQL statements terminated with a ; sqlite .mode

Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

2011-06-28 Thread Stephan Beal
On Tue, Jun 28, 2011 at 8:42 PM, thilo th...@nispuk.com wrote: sqlite select (select v from t1 where n=a) wrong,* from a1; use SINGLE quotes, not double quotes. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users

Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

2011-06-28 Thread Jim Morris
string literals are enclose in single quotes not double quotes select (select v from t1 where n='a') wrong,* from a1; On 6/28/2011 11:42 AM, thilo wrote: select (select v from t1 where n=a) wrong,* from a1; ___ sqlite-users mailing list

Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

2011-06-28 Thread Black, Michael (IS)
Use single quotes instead of double sqlite select (select v from t1 where n='a') wrong,* from a1; wrong|a|b 2000|123|456 2000|999|999 Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From:

Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

2011-06-28 Thread thilo
On 6/28/2011 8:45 PM, Stephan Beal wrote: On Tue, Jun 28, 2011 at 8:42 PM, thilo th...@nispuk.com wrote: sqlite select (select v from t1 where n=a) wrong,* from a1; use SINGLE quotes, not double quotes. bummer, Thanks a lot thilo -- Dipl. Ing. Thilo Jeremias Zur Rabenwiese 14 27239

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

2011-06-28 Thread Charles Samuels
On Tuesday, June 28, 2011 9:36:22 f.h. Stephan Beal wrote: There is NOTHING wrong with mixing .c and .cpp files in one C++ project. Compile the C code with gcc and C++ code with g++, and then link them together as you would any other objects. Compiling sqlite as C++ is hopeless, so this is a

Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

2011-06-28 Thread Stephan Beal
On Tue, Jun 28, 2011 at 8:50 PM, thilo th...@nispuk.com wrote: use SINGLE quotes, not double quotes. bummer, Thanks a lot i PROMISE that you won't find such an obvious bug in sqlite3 ;). sqlite uses single quotes because that's what ANSI SQL specifies. MySQL uses (or can use) double

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

2011-06-28 Thread Stephan Beal
On Tue, Jun 28, 2011 at 8:55 PM, Charles Samuels char...@cariden.comwrote: However, be warned that if you use exceptions, you can't use sqlite3_exec, because then the exceptions can't make it through the C code. It's easy enough to roll your own sqlite3_exec and compile it as C++. To expand

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Lazarus 101
On Tue, Jun 28, 2011 at 5:48 PM, Simon Slavin slav...@bigfraud.org wrote: On 28 Jun 2011, at 3:33pm, Lazarus 101 wrote: FILES file_id INTEGER NOT NULL, name TEXT I assume that SQLite has identified 'file_id' as its own 'rowid' column and made in INDEX for it. it's also marked as

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Lazarus 101
On Tue, Jun 28, 2011 at 8:18 PM, Jan Hudec b...@ucw.cz wrote: On Tue, Jun 28, 2011 at 17:33:23 +0300, Lazarus 101 wrote: Hi guys, i'm working on an Android app and using sqlite to store some data and i need some help with a query. I have the following table structure: FILES

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Simon Slavin
On 28 Jun 2011, at 9:58pm, Lazarus 101 wrote: You should make an index on the columns 'file_id' and 'data_type' from the 'DATA' table. This will allow it to be searched far more quickly. Your command will be something like CREATE UNIQUE INDEX dfd ON data (file_id,data_type) Then do

Re: [sqlite] PRAGMA bug in 3.7.7 (but fine in 3.7.6.3)

2011-06-28 Thread Greg Stein
I see that 3.7.7.1 has just been released with this bugfix. Thanks!! On Mon, Jun 27, 2011 at 14:01, Roger Binns rog...@rogerbinns.com wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/26/2011 03:52 PM, Richard Hipp wrote: The bug is that it is returning SQLITE_SCHEMA instead of

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Lazarus 101
the select statement is SELECT * from files left join data on files.file_id=data.file_id; to test the performance i'm only doing long t1 = System.currentTimeMillis(); Cursor cursor = db.rawQuery(...); while (cursor.moveToNext()) { } android.util.Log.e(TAG, loaded in: +

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Simon Slavin
On 29 Jun 2011, at 2:26am, Lazarus 101 wrote: the select statement is SELECT * from files left join data on files.file_id=data.file_id; So you read all the records for the correct file_id, and deal with each one as you find it, ignoring those you don't want. Hmm. I don't see why your app

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Lazarus 101
On Tue, Jun 28, 2011 at 8:18 PM, Jan Hudec b...@ucw.cz wrote: name TEXT DATA file_id INTEGER REFERENCES files(resource_id) ON DELETE CASCADE, data_type TEXT, If nothing else, you want to define integer identifiers for the data types and use integer here. That will save you some space