RE: [sqlite] Transpose table

2006-12-13 Thread RB Smissaert
The example I gave shows exactly what I need to do. I have a column of ID numbers with duplicates. I have to make this column hold only unique ID numbers by moving the values to the first row where that ID number appears, with that increasing the number of columns. Hope this makes it a clearer.

Re: [sqlite] Transpose table

2006-12-13 Thread Darren Duncan
Can you please provide a use case for your example, so we know what you're trying to accomplish? That should help us to help you better. -- Darren Duncan At 12:08 AM + 12/14/06, RB Smissaert wrote: I am moving my code away from VBA and transferring it to SQL. There is one particular

Re: [sqlite] faster SELECT time on second run

2006-12-13 Thread Joe Wilson
>From personal experience on Windows and Linux, if your sqlite database is smaller than the amount of available RAM, then sequentially read()ing the database file in 8K or larger chunks (and ignoring the read results) outside of the SQLite API is several times faster than first running a dummy

Re: [sqlite] Re: Re: Select with left outer join - Am I going mad ? Second edition

2006-12-13 Thread Alexandre Guion
I don't know how sqlite handles parenthesis in joins. the first query is not recognized by the syntax (mpm.MediumID undefined). The second one just returns ids with name and value being empty. However I only use 3.3.4, is there any documentation on this ? Igor Tandetnik wrote: Alexandre

[sqlite] Re: Re: Select with left outer join - Am I going mad ? Second edition

2006-12-13 Thread Igor Tandetnik
Alexandre Guion <[EMAIL PROTECTED]> wrote: I tried this one of course, and it doesn't work, it could be a bug. It returns every property for every medium (not just 'myprops') I believe this should work (untested): SELECT m.MediumID AS id, IFNULL(pn.Text, '') AS name, IFNULL(pv.Text, '') AS

Re: [sqlite] faster SELECT time on second run

2006-12-13 Thread Jay Sprenkle
On 12/12/06, John Stanton <[EMAIL PROTECTED]> wrote: If you want to get as much as possible of the Sqlite database into physical memory on the computer, read all of it. If you just want to preload the cache and VM so that the first user gets faster response execute a dummy query like one you

[sqlite] Transpose table

2006-12-13 Thread RB Smissaert
I am moving my code away from VBA and transferring it to SQL. There is one particular routine where I haven't found a good replacement for and that is to transpose a table from a vertical layout to a horizontal one, like this example: ID Value - 1 A 1 B 1 A 3

Re: [sqlite] Re: Select with left outer join - Am I going mad ? Second edition

2006-12-13 Thread Alexandre Guion
I tried this one of course, and it doesn't work, it could be a bug. It returns every property for every medium (not just 'myprops') Igor Tandetnik wrote: Alexandre Guion <[EMAIL PROTECTED]> wrote: SELECT m.MediumID AS id, IFNULL(pn.Text, '') AS name, IFNULL(pv.Text, '') AS value FROM Media

[sqlite] Re: Select with left outer join - Am I going mad ? Second edition

2006-12-13 Thread Igor Tandetnik
Alexandre Guion <[EMAIL PROTECTED]> wrote: SELECT m.MediumID AS id, IFNULL(pn.Text, '') AS name, IFNULL(pv.Text, '') AS value FROM Media AS m LEFT JOIN MediumPropMap AS mpm ON mpm.MediumID=m.MediumID LEFT JOIN Property AS p ON mpm.PropID=p.PropID LEFT JOIN PropName AS pn ON

[sqlite] Select with left outer join - Am I going mad ? Second edition

2006-12-13 Thread Alexandre Guion
Hey all, I'm getting crazy on a problem with left join in SQLite. Either I didn't understand something, or there's a bug somewhere. Here's a sample of my DB CREATE TABLE Media ( MediumId INTEGER PRIMARY KEY, ... ) CREATE TABLE MediumPropMap ( MediumId INTEGER NOT NULL, (foreign key from Media)

Re: [sqlite] Re: File Syste

2006-12-13 Thread John Stanton
If you are using a B-Tree index for a file system why incorporate Sqlite, why not just write the file system code? B-Trees are well documented. Cesar Rodas wrote: I am developing a File System, and I'd like to use B+ Tree and not lost time and CPU understanding SQL... On 13/12/06, John

Re: [sqlite] Check for empty table

2006-12-13 Thread Dennis Cote
RB Smissaert wrote: Trying to find the fastest way to determine if a table has no rows. I think this will do: SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL; If a table has rows then the result should be 1. But I am not sure if a table always has the hidden field ROWID. RBS

[sqlite] Re: Re: Check for empty table

2006-12-13 Thread Igor Tandetnik
Nicolas Williams <[EMAIL PROTECTED]> wrote: On Wed, Dec 13, 2006 at 01:33:35PM -0500, Igor Tandetnik wrote: Nicolas Williams <[EMAIL PROTECTED]> wrote: On Wed, Dec 13, 2006 at 06:07:56PM +, RB Smissaert wrote: count(*) doesn't read every record in the table. Does too. Run EXPLAIN and see

Re: [sqlite] Re: File Syste

2006-12-13 Thread Nicolas Williams
On Wed, Dec 13, 2006 at 05:03:50PM -0400, Cesar Rodas wrote: > On 13/12/06, John Stanton <[EMAIL PROTECTED]> wrote: > > > >It is hard to imagine why you would want to use Sqlite B-Tree access. > > I am developing a File System, and I'd like to use B+ Tree and not lost time > and CPU understanding

Re: [sqlite] Re: Check for empty table

2006-12-13 Thread Nicolas Williams
On Wed, Dec 13, 2006 at 01:33:35PM -0500, Igor Tandetnik wrote: > Nicolas Williams <[EMAIL PROTECTED]> wrote: > >On Wed, Dec 13, 2006 at 06:07:56PM +, RB Smissaert wrote: > >count(*) doesn't read every record in the table. > > Does too. Run EXPLAIN and see for yourself. Ah. Yes. It could

Re: [sqlite] Re: File Syste

2006-12-13 Thread Cesar Rodas
I am developing a File System, and I'd like to use B+ Tree and not lost time and CPU understanding SQL... On 13/12/06, John Stanton <[EMAIL PROTECTED]> wrote: It is hard to imagine why you would want to use Sqlite B-Tree access. Kees Nuyt wrote: > On Wed, 13 Dec 2006 13:02:37 -0400, you

[sqlite] Reusing SQLiteCommand

2006-12-13 Thread jfbaro
Hi, I am new to C# and SQLite and I am having problems using Paramaters.Add... I prepare the "INSERT" statement and inside a loop I set the values for each parameter and call ExecuteNonQuery(); Something similar to that: checkCmd.CommandText = "SELECT userId FROM users WHERE

Re: [sqlite] Re: File Syste

2006-12-13 Thread John Stanton
It is hard to imagine why you would want to use Sqlite B-Tree access. Kees Nuyt wrote: On Wed, 13 Dec 2006 13:02:37 -0400, you wrote: I mean the SQLite Core API, something like Berkeley DB. I'd like to use SQLite B+tree API. It has quite recently been discussed, you may want to try to

Re: [sqlite] sqlite3 close() run time errors

2006-12-13 Thread Dennis Cote
Michele Santucci wrote: Following your hint I create a simple project (linked sqlite3.lib and included sqlite3.h) where I just do this: Edit2->Text = AnsiString( sqlite3_libversion() ); I got this at compile time: [C++ Error] sqlite3.h(1719): E2232 Constant member

Re: [sqlite] sqllite ddb from win to linux

2006-12-13 Thread Kees Nuyt
On Wed, 13 Dec 2006 20:33:29 +0100, you wrote: >The page where this is documented : > http://www.sqlite.org/sqlite.html >is quite hard to find, the only link i found is on the > http://www.sqlite.org/download.html >page, next to the "Precompiled Binaries For Windows"

Re: [sqlite] Re: File Syste

2006-12-13 Thread Kees Nuyt
On Wed, 13 Dec 2006 13:02:37 -0400, you wrote: > I mean the SQLite Core API, something like Berkeley DB. > I'd like to use SQLite B+tree API. It has quite recently been discussed, you may want to try to search the mailing list archives. The conclusion was: the B+tree API is usable but it is easy

Re: [sqlite] Re: File Syste

2006-12-13 Thread John Stanton
The Sqlite API is fully documented and the source code is clearly written and commented and reads quite simply as a definition of its philosophy and function. Cesar Rodas wrote: I mean.. is there a manual that explain how to use SQLite Core in low level API, like berkeley DB, without SQL.

[sqlite] Logo usage

2006-12-13 Thread Muhammad Hassan
Can i use Sqlite logo on my companies website technology page http://badrit.com/page/technology, i want to refer to the technologies we used? Please reply. Thanks. -- Muhammad Hassan Nasr www.BadrIT.com mobile: +2 010 606 8822 e-mail: [EMAIL PROTECTED]

Re: [sqlite] Check for empty table

2006-12-13 Thread Scott Hess
On 12/13/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Scott Hess" <[EMAIL PROTECTED]> writes: > On 12/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote: >> Trying to find the fastest way to determine if a table has no rows. >> >> I think this will do: >> SELECT (SELECT ROWID FROM table limit 1)

[sqlite] Re: Check for empty table

2006-12-13 Thread Igor Tandetnik
Nicolas Williams <[EMAIL PROTECTED]> wrote: On Wed, Dec 13, 2006 at 06:07:56PM +, RB Smissaert wrote: count(*) doesn't read every record in the table. Does too. Run EXPLAIN and see for yourself. Igor Tandetnik -

Re: [sqlite] Check for empty table

2006-12-13 Thread Martin Jenkins
Scott Hess wrote: What's wrong with "SELECT COUNT(*) FROM table;"? It scans the whole table. This is OK if the table is small (nearly) empty, but if it has a couple of million rowZzzz Martin - To unsubscribe,

Re: [sqlite] Check for empty table

2006-12-13 Thread Nicolas Williams
On Wed, Dec 13, 2006 at 06:07:56PM +, RB Smissaert wrote: > Nothing wrong, but is it the fastest? count(*) doesn't read every record in the table. - To unsubscribe, send email to [EMAIL PROTECTED]

Re: [sqlite] Check for empty table

2006-12-13 Thread Derrell . Lipman
"Scott Hess" <[EMAIL PROTECTED]> writes: > On 12/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote: >> Trying to find the fastest way to determine if a table has no rows. >> >> I think this will do: >> SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL; >> If a table has rows then the result should

RE: [sqlite] Check for empty table

2006-12-13 Thread RB Smissaert
Nothing wrong, but is it the fastest? RBS -Original Message- From: Scott Hess [mailto:[EMAIL PROTECTED] Sent: 13 December 2006 17:25 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Check for empty table On 12/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote: > Trying to find the fastest

Re: [sqlite] Check for empty table

2006-12-13 Thread Scott Hess
On 12/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote: Trying to find the fastest way to determine if a table has no rows. I think this will do: SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL; If a table has rows then the result should be 1. What's wrong with "SELECT COUNT(*) FROM

Re: [sqlite] Re: File Syste

2006-12-13 Thread Cesar Rodas
I mean the SQLite Core API, something like Berkeley DB. I'd like to use SQLite B+tree API. On 13/12/06, Pat Wibbeler <[EMAIL PROTECTED]> wrote: The public C API is well-documented here: http://www.sqlite.org/capi3.html There may be other documentation of the internals, but I'd imagine the

RE: [sqlite] Re: File Syste

2006-12-13 Thread Pat Wibbeler
The public C API is well-documented here: http://www.sqlite.org/capi3.html There may be other documentation of the internals, but I'd imagine the public API will get you pretty far. Pat -Original Message- From: Cesar Rodas [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 13, 2006

Re: [sqlite] Check for empty table

2006-12-13 Thread Rich Shepard
On Wed, 13 Dec 2006, RB Smissaert wrote: Trying to find the fastest way to determine if a table has no rows. I think this will do: SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL; If a table has rows then the result should be 1. RBS About a month ago, I sought a better solution than

Re: [sqlite] sqlite3 close() run time errors

2006-12-13 Thread Michele Santucci
Maybe it is but it's hard to find the way to generate a suitable sqlite3.lib then. - Original Message - From: "Roberto" <[EMAIL PROTECTED]> To: Sent: Tuesday, December 12, 2006 1:49 PM Subject: Re: [sqlite] sqlite3 close() run time errors On 12/12/06,

Re: [sqlite] sqlite3 close() run time errors

2006-12-13 Thread Michele Santucci
Following your hint I create a simple project (linked sqlite3.lib and included sqlite3.h) where I just do this: Edit2->Text = AnsiString( sqlite3_libversion() ); I got this at compile time: [C++ Error] sqlite3.h(1719): E2232 Constant member 'sqlite3_index_info::nConstraint' in class without

[sqlite] Check for empty table

2006-12-13 Thread RB Smissaert
Trying to find the fastest way to determine if a table has no rows. I think this will do: SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL; If a table has rows then the result should be 1. But I am not sure if a table always has the hidden field ROWID. RBS

Re: [sqlite] sqllite ddb from win to linux

2006-12-13 Thread Martin Jenkins
[EMAIL PROTECTED] wrote: Use "sqlite" to read an sqlite version 2 database. Use "sqlite3" to read a version 3 database. Just a thought, but would it be worth detecting attempts to open v2 databases with sqlite3 and printing "this database appears to be version 2 and I'm version 3 so I can't

Re: [sqlite] sqllite ddb from win to linux

2006-12-13 Thread Rob Coenen
Kees, thanks this helped me a lot. It turned out the DB was actually in sqlite V2 so I did a sqlite3 olddatabasefile .dump >transportfile end then transported the transportfile in a ZIP (binary safe) to the Linux machine where I did a sqlite newdatabasefile wrote: On Tue, 12 Dec 2006

[sqlite] Re: File Syste

2006-12-13 Thread Cesar Rodas
I mean.. is there a manual that explain how to use SQLite Core in low level API, like berkeley DB, without SQL. Thank to all On 13/12/06, Cesar Rodas <[EMAIL PROTECTED]> wrote: Hello to all I starting a project http://code.google.com/p/gxdfs/ that is a Distributed File System, following

[sqlite] File Syste

2006-12-13 Thread Cesar Rodas
Hello to all I starting a project http://code.google.com/p/gxdfs/ that is a Distributed File System, following the white paper of GFS(Google File system). If some one want to contribute please send emails with ideas. I would like to know how sqlite works.. is there a manual that explain every

Re: [sqlite] Usage of special characters in insert query

2006-12-13 Thread Roberto
Escape you input strings: insert into foo values ('I haven''t done that'); On 13/12/06, Kirrthana M <[EMAIL PROTECTED]> wrote: Hi, In my query for inserting values into the database i have a statement as given below insert into table_name values('I haven't done that'); table is created with

[sqlite] Usage of special characters in insert query

2006-12-13 Thread Kirrthana M
Hi, In my query for inserting values into the database i have a statement as given below insert into table_name values('I haven't done that'); table is created with this option create table table_name(str varchar(20)); when the insert statement is executed it gives the error in haven't,It is