[sqlite] Proper SQL Syntax for a SELECT with LEFT JOIN Syntax for a 1 Primary Table, N Secondary Tables

This is mainly a SQL syntax question but I am using the SQL for creating views in SQLite database files. Basic question: What is the proper SQL syntax for "Left Outer Joins" for 1 primary with N secondary (support) tables; I have come up with 2 possibilities that work but not sure which one is

RE: [sqlite] Getting the no of rows using count(*)

It works fine..Thanks.. Kirrthana -Original Message- From: Bharath Booshan L [mailto:[EMAIL PROTECTED] Sent: Thursday, January 31, 2008 12:07 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Getting the no of rows using count(*) Hello Kirrthana, > should i use prepare and step in

[sqlite] sqlite3_free_table and sqlite3_close

Hi, is it safe to do the following in a C program while working with SQLite? I tried searching the docs but couldn't find any answer. sqlite3_open(); sqlite3_get_table(...result); sqlite3_close(); sqlite3_free_table(result); i.e. freeing the table AFTER the closing of the db_handle?

Re: [sqlite] sqlite3_free_table and sqlite3_close

Torsten Blix <[EMAIL PROTECTED]> wrote: > Hi, > > is it safe to do the following in a C program while working with SQLite? I > tried searching the docs but couldn't find any answer. > > sqlite3_open(); > sqlite3_get_table(...result); > sqlite3_close(); > sqlite3_free_table(result); > > i.e.

Re: [sqlite] Proper SQL Syntax for a SELECT with LEFT JOIN Syntax for a 1 Primary Table, N Secondary Tables

Scott McDonald wrote: Questions: - How would you do what I am trying to do? - Are these SELECT queries equivalent? Probably not as version 1 has a sub-select query so this is a "2 pass" query, but is version 1 done in "1 pass" or "2 passes"? - Is version 2 more efficient? - For some reason, in

[sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

Ran the tests on Freebsd, the make test summary follows: [delenda => ... Ok] 16 errors out of 38961 tests Failures on these tests: bind-4.4 bind-4.5 cast-3.14 cast-3.18 cast-3.24 printf-1.7.6 printf-1.8.6 printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4 vtab6-2.5 vtab6-2.6 vtab6-7.1 vtab6-9.1.1

Re: [sqlite] Version 3.5.5 Released

What about speed? Can we expect the same performance of version 3.5.4? Thanks a lot for your continue improvements. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Jan 31, 2008, at 6:33 PM, [EMAIL PROTECTED] wrote: SQLite version

[sqlite] Version 3.5.5 Released

SQLite version 3.5.5 is now available for download from the SQLite website: http://www.sqlite.org/ The big change from version 3.5.4 is that the internal virtual machine was reworked to use operands in registers rather than pulling operands from a stack. The virtual machine stack has now

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

Rob Sciuk <[EMAIL PROTECTED]> wrote: > Ran the tests on Freebsd, the make test summary follows: > [delenda => ... Ok] > > 16 errors out of 38961 tests > Failures on these tests: bind-4.4 bind-4.5 cast-3.14 cast-3.18 cast-3.24 > printf-1.7.6 printf-1.8.6 printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4

RE: [sqlite] Version 3.5.5 Released

With SQLITE_OMIT_VIRTUALTABLE defined, I get a linker fault under VC++ It claims that: error LNK2001: unresolved external symbol sqlite3VtabBeginParse Looking through the source code, I see the following Y:\SQLite3\Projects\3_5_5\src\sqliteInt.h(1986) #ifdef SQLITE_OMIT_VIRTUALTABLE # define

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

How to get error messages from test? Scroll up and copy-paste? Or there is some other more handy method? On Jan 31, 2008 7:03 PM, <[EMAIL PROTECTED]> wrote: > Rob Sciuk <[EMAIL PROTECTED]> wrote: > > Ran the tests on Freebsd, the make test summary follows: > > [delenda => ... Ok] > > > > 16

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

How to get error messages from test? Scroll up and copy-pase? Or there is some other more handy method? On Jan 31, 2008 7:03 PM, <[EMAIL PROTECTED]> wrote: > Rob Sciuk <[EMAIL PROTECTED]> wrote: > > Ran the tests on Freebsd, the make test summary follows: > > [delenda => ... Ok] > > > > 16

Re: [sqlite] Version 3.5.5 Released

"Noah Hart" <[EMAIL PROTECTED]> wrote: > With SQLITE_OMIT_VIRTUALTABLE defined, I get a linker fault under VC++ > > It claims that: > error LNK2001: unresolved external symbol sqlite3VtabBeginParse That call *is* surrounded by #ifndef SQLITE_OMIT_VIRTUALTABLE. But the #ifndef is located in

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

On Thu, 31 Jan 2008, [EMAIL PROTECTED] wrote: Rob Sciuk <[EMAIL PROTECTED]> wrote: Ran the tests on Freebsd, the make test summary follows: [delenda => ... Ok] 16 errors out of 38961 tests Failures on these tests: bind-4.4 bind-4.5 cast-3.14 cast-3.18 cast-3.24 printf-1.7.6 printf-1.8.6

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

Here is my "make test" on Mac OS 10.5.1: 8 errors out of 38117 tests Failures on these tests: lock4-1.3 vtab6-2.2 vtab6-2.4 vtab6-2.5 vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2 All memory allocations freed - no leaks Maximum memory usage: 14161966 bytes Current memory usage: 0 bytes make: ***

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote: > How to get error messages from test? Scroll up and copy-paste? Or > there is some other more handy method? make test | tee testout.txt -- D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote: > Here is my "make test" on Mac OS 10.5.1: > > 8 errors out of 38117 tests > Failures on these tests: lock4-1.3 vtab6-2.2 vtab6-2.4 vtab6-2.5 > vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2 > All memory allocations freed - no leaks > Maximum memory

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

On Thu, 31 Jan 2008, [EMAIL PROTECTED] wrote: Date: Thu, 31 Jan 2008 18:27:35 + From: [EMAIL PROTECTED] Reply-To: sqlite-users@sqlite.org To: sqlite-users@sqlite.org Subject: Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5 "Alexander Batyrshin" <[EMAIL PROTECTED]> wrote: How to get

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

Here is my errors on mac os 10.5 with 3.5.5 SQLite lock4-1.3... Error: database is locked vtab6-2.2... Expected: [1 2 3 {} 2 3 4 1 3 4 5 2] Got: [1 2 3 {} 2 3 4 {} 3 4 5 {}] vtab6-2.4... Expected: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3] Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {}

[sqlite] make test problems on Fedora 7

I downloaded sqlite 3.5.5 and ran make test. I get the following errors: /tmp/cc9i7nKr.o: In function `Sqlitetest1_Init': /tmp/sqlite-3.5.5/./src/test1.c:4562: undefined reference to `Tcl_CreateCommand' /tmp/sqlite-3.5.5/./src/test1.c:4565: undefined reference to `Tcl_CreateObjCommand'

Re: [sqlite] SQLite Compilation Problems on FreeBSD

I got "sqlite-3.5.4.tar.gz" to compile, cleanly, and installation went smooth. After compiling and installing tcl and tk, the slqite "make" step still failed, saying "*** Error code 1", just like it did before tcl/tk; it complained about tclsh being installed under multiple names (tclsh,

RE: [sqlite] Version 3.5.5 Released

Got it! Thanks for the clarification. Noah -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, January 31, 2008 10:43 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Version 3.5.5 Released Importance: High "Noah Hart" <[EMAIL PROTECTED]> wrote: >

Re: [sqlite] make test problems on Fedora 7

Looks like you built it without TCL support On Jan 31, 2008 7:54 PM, Scott Baker <[EMAIL PROTECTED]> wrote: > I downloaded sqlite 3.5.5 and ran make test. I get the following errors: > > /tmp/cc9i7nKr.o: In function `Sqlitetest1_Init': > /tmp/sqlite-3.5.5/./src/test1.c:4562: undefined reference

Re: [sqlite] make test problems on Fedora 7

Oh... you know about it. Sorry >Etc, etc, etc. I have tcl-devel installed, but I'm assuming I need >some other tcl package? Any idea what I need? On Jan 31, 2008 8:27 PM, Alexander Batyrshin <[EMAIL PROTECTED]> wrote: >> Looks like you built it without TCL support

Re: [sqlite] Implementation of TPCC benchmark

At 23:53 30/01/2008, you wrote: On Wed, 30 Jan 2008, Steve VanDeBogart wrote: SQLite-ers, OSDL-DBT is a set of TPC like benchmarks implemented by OSDL (http://osdldbt.sourceforge.net/). I've added SQLite support to the DBT2 benchmark (TPCC like). The patch is attached in case anyone is

[sqlite] Broken links to "sqlite3" on website

Hi, I'm not complaining (I love SQLite), but I just wanted to point out that the "sqlite3" link is broken from multiple web pages: http://www.sqlite.org/c3ref/objlist.html http://www.sqlite.org/c3ref/intro.html Also, links to "sqlite3_stmt" are broken. It seems that any link containing

RE: [sqlite] Version 3.5.5 Released

For any who care, 3.5.5 built flawlessly on Slackware-11.0 and -12.0. Works, too. Thank you, development team. Rich -- Richard B. Shepard, Ph.D. | IntegrityCredibility Applied Ecosystem Services, Inc.|Innovation

[sqlite] Make test on Ubuntu 7.10 SQLite 3.5.5

$ uname -a Linux linux 2.6.22-14-generic #1 SMP Sun Oct 14 23:05:12 GMT 2007 i686 GNU/Linux $ make test | grep -v Ok | tee test.output.ubuntu ./testfixture ../test/quick.test bind-4.4... Error: floating point value is Not a Number bind-4.5... Expected: [null real real] Got: []

RE: [sqlite] how to do this case when?

At the end (outside the subquery), add: WHERE Field1 IN (SELECT Field1 FROM Table2) - Jeff -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Thursday, January 31, 2008 3:15 PM To: sqlite-users@sqlite.org Subject: [sqlite] how to do this case when? How do I alter

Re: [sqlite] Broken links to "sqlite3" on website

"David Baird" <[EMAIL PROTECTED]> wrote: > The "sqlite3" link is broken from multiple web pages: > > http://www.sqlite.org/c3ref/objlist.html > http://www.sqlite.org/c3ref/intro.html > > Also, links to "sqlite3_stmt" are broken. It seems that any link > containing a %20 is broken, e.g.

Re: [sqlite] make test problems on Fedora 7

Alexander Batyrshin wrote: Looks like you built it without TCL support Heh... duh.. Still have problems though... I have tcl and tcl-devel install and I get this: checking whether to use an in-ram database for temporary tables... no checking if executables have the .exe suffix... unknown

[sqlite] how to do this case when?

How do I alter this SQL, so that the original field remains the same when there is no match? A case when else end should do it, but I can't get it right. UPDATE Table1 SET Field1 = (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1) Thanks for any advice. RBS

RE: [sqlite] how to do this case when?

Found this now: UPDATE Table1 SET Field1 = (case when Field1 IN (SELECT Field1 FROM Table2) then (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1) else Table1.Field1 end) Have a feeling though that this can be shorter. RBS -Original

[sqlite] Re: how to do this case when?

RB Smissaert <[EMAIL PROTECTED]> wrote: How do I alter this SQL, so that the original field remains the same when there is no match? A case when else end should do it, but I can't get it right. UPDATE Table1 SET Field1 = (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1)

Re: [sqlite] Number of columns in table

Am Wed, 30 Jan 2008 17:04:17 -0700 schrieb Dennis Cote: > > What if I like e.g. to write each table row into a file in one > > line? I need to know when a row is finished and the next begins. I > > could compare azColName, but I hope there is a better way. > > > > Andreas, > > Your

Re: [sqlite] Broken links to "sqlite3" on website

On Jan 31, 2008 1:45 PM, <[EMAIL PROTECTED]> wrote: > Tnx. Please try it again now. Great, seems to be working now, even for other pages that I didn't list. Thanks! - To unsubscribe, send email to [EMAIL PROTECTED]

RE: [sqlite] Re: how to do this case when?

Thanks; I came up with number 3, but I like your number 1. Any idea what could be the fastest or will it all be the same? RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 31 January 2008 20:59 To: SQLite Subject: [sqlite] Re: how to do this case when? RB

Re: [sqlite] Number of columns in table

Andreas Volz wrote: What is if the cell format is Number or BLOB? Does sqlite convert it always to char? Yes, the callback interface is a throwback to a time when all sqlite data was stored as strings. This interface only supports strings, so all fields are converted to strings to send to

Re: [sqlite] Implementation of TPCC benchmark

On Thu, 31 Jan 2008, Eduardo Morras wrote: On Wed, 30 Jan 2008, Steve VanDeBogart wrote: OSDL-DBT is a set of TPC like benchmarks implemented by OSDL (http://osdldbt.sourceforge.net/). I've added SQLite support to the DBT2 benchmark (TPCC like). The patch is attached in case anyone is

[sqlite] Re: Number of columns in table

Andreas Volz <[EMAIL PROTECTED]> wrote: Oh, you're right. I didn't notice that. What is if the cell format is Number or BLOB? Does sqlite convert it always to char? And how do I find out the cell type? When you are using sqlite3_exec, values passed to callback are always converted to string

[sqlite] Can not use Attached Tables in the SELECT part of a View

I came across an issue where I can not JOIN databases with some tables being in an attached tables in the SELECT part when creating a View. I keep getting errors about not being able to reference objects in the attached table. I understand this error but believe it is too pessimistic. I would

[sqlite] Solaris make test compilation error

Solaris 5.8 (solars 8) make test Undefined first referenced symbol in file sched_yield /var/tmp//cckDMcyL.o ld: fatal: Symbol referencing errors. No output written to .libs/testfixture collect2: ld returned 1 exit status

Re: [sqlite] Solaris make test compilation error

Ken <[EMAIL PROTECTED]> wrote: > Solaris 5.8 (solars 8) > make test > > Undefined first referenced > symbol in file > sched_yield /var/tmp//cckDMcyL.o > ld: fatal: Symbol referencing errors. No output written to

RE: [sqlite] Solaris make test compilation error

> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 31, 2008 2:23 PM > To: sqlite-users@sqlite.org; [EMAIL PROTECTED] > Subject: Re: [sqlite] Solaris make test compilation error > > Ken <[EMAIL PROTECTED]> wrote: > > Solaris 5.8 (solars 8) >

RE: [sqlite] Re: how to do this case when?

There isn't much in it, but it looks the one with IFNULL is the fastest. Will stick to that one. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 31 January 2008 21:17 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Re: how to do this case when? Thanks; I

Re: [sqlite] Solaris make test compilation error

On Thu, Jan 31, 2008 at 02:32:45PM -0800, James Dennett wrote: > > > Undefined first referenced > > > symbol in file > > > sched_yield /var/tmp//cckDMcyL.o > > > ld: fatal: Symbol referencing errors. No output written to >

[sqlite] Solaris bus error

After addressing the -lrt sched yield on solaris. make test resulted in: async3-1.0... Ok async3-1.1...make: *** [test] Bus Error (core dumped) Any ideas? Regards, Ken

RE: [sqlite] Solaris bus error

> -Original Message- > From: Ken [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 31, 2008 2:55 PM > To: sqlite > Subject: [sqlite] Solaris bus error > > > After addressing the -lrt sched yield on solaris. > > make test resulted in: > > async3-1.0... Ok > async3-1.1...make: ***

RE: [sqlite] Solaris bus error

Core/backtrace info: (gdb) core-file core Core was generated by `/home/ixion/LIB/sqlite3test/.libs/testfixture ../sqliteSrc/sqlite-3.5.5/test/qu'. Program terminated with signal 10, Bus error. Reading symbols from /home/ixion/LIB/sqlite3test/.libs/libsqlite3.so.0...done. Loaded symbols for

[sqlite] Using LIKE to check the first digits?

Hello I'm no SQL guru, and need to look up phone numbers in a SQLite database that start with certain digits. Some customers use http://en.wikipedia.org/wiki/Direct_Inward_Dialing, which means that the first part is common to all the numbers assigned to this customer, so I don't need to

Re: [sqlite] Using LIKE to check the first digits?

On 1/31/08, Gilles <[EMAIL PROTECTED]> wrote: > Hello > > I'm no SQL guru, and need to look up phone numbers in a SQLite database > that start with certain digits. Some customers use > http://en.wikipedia.org/wiki/Direct_Inward_Dialing, which means that the > first part is common to all the

[sqlite] Precompiled binaries for Windows Not Version 3.5.5

Under the Precompiled binaries for Windows section of the downloads page, the sqlite-3.5.5.zip, sqlitedll-3.5.5.zip and tclsqlite-3.5.5.zip files contain 3.5.4 binaries from December 12, 2007. Robert Wishlaw - To

RE: [sqlite] Solaris bus error

Recompiled without the -O2 flags: #0 0x00049750 in findLockInfo (fd=4, ppLock=0x1345ad, ppOpen=0x1345a9) at ../sqliteSrc/sqlite-3.5.5/src/os_unix.c:670 670 *ppLock = pLock; *ppLock is Null Ken <[EMAIL PROTECTED]> wrote: Core/backtrace info: (gdb) core-file core Core was

Re: [sqlite] Using LIKE to check the first digits?

Gilles wrote: Hello I'm no SQL guru, and need to look up phone numbers in a SQLite database that start with certain digits. Some customers use http://en.wikipedia.org/wiki/Direct_Inward_Dialing, which means that the first part is common to all the numbers assigned to this customer, so I

RE: [sqlite] Using LIKE to check the first digits?

> -Original Message- > From: P Kishor [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 31, 2008 3:35 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Using LIKE to check the first digits? > > On 1/31/08, Gilles <[EMAIL PROTECTED]> wrote: > > Hello > > > > I'm no SQL guru, and

RE: [sqlite] Using LIKE to check the first digits?

At 15:40 31/01/2008 -0800, James Dennett wrote: > WHERE col LIKE '123%' or WHERE substr(col,1, 3) = '123' The optimizer has a decent chance of using an index for LIKE '123%' but I'd be surprised (and impressed) if it looks inside function calls suchas substr for opportunities to use indexes.

Re: [sqlite] Solaris bus error

I didn't get any core dumps in Solaris Nevada, but seven tests failed: Failures on these tests: vtab6-2.2 vtab6-2.4 vtab6-2.5 vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2 Nico -- - To unsubscribe, send email to [EMAIL

[sqlite] solaris test suite errors, 3.5.5

Removed asyn3.test...Since it was core dumping. tcl version is 8.5.0 gcc is 3.4.2 sqlite 3.5.5 Solairs 8 (5.8) Test suite errors: 13 errors out of 38318 tests Failures on these tests: bind-4.4 bind-4.5 printf-1.7.6 printf-1.8.6 printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4 vtab6-2.5 vtab6-2.6

Re: [sqlite] Solaris bus error

Solaris Nevada is an x86 system if I'm reading the Hardware Compatibility List correctly. The bus erro was produced on a Solaris 8 (ultra Sparc) system... Different hardware and different OS. My system had other failures in addition to the ones you've listed, in addition to the bus error.

Re: [sqlite] Solaris bus error

On Thu, Jan 31, 2008 at 04:38:27PM -0800, Ken wrote: > Solaris Nevada is an x86 system if I'm reading the Hardware > Compatibility List correctly. No, it's an unreleased version of Solaris (the basis for the next release after Solaris 10). It runs on x86 and SPARC, but I was trying this on my

Re: [sqlite] Using LIKE to check the first digits?

Gilles <[EMAIL PROTECTED]> wrote: > At 15:40 31/01/2008 -0800, James Dennett wrote: > > > WHERE col LIKE '123%' or WHERE substr(col,1, 3) = '123' > > > >The optimizer has a decent chance of using an index for LIKE '123%' but > >I'd be surprised (and impressed) if it looks inside function calls

Re: [sqlite] Using LIKE to check the first digits?

At 02:01 01/02/2008 +, [EMAIL PROTECTED] wrote: Indices won't help with LIKE unless the column as a NOCASE collation. Use GLOB instead: ... WHERE number GLOB '1234*'; Thank you for the tip. - To unsubscribe,

Re: [sqlite] Using LIKE to check the first digits?

On 1/31/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Gilles <[EMAIL PROTECTED]> wrote: > > At 15:40 31/01/2008 -0800, James Dennett wrote: > > > > WHERE col LIKE '123%' or WHERE substr(col,1, 3) = '123' > > > > > >The optimizer has a decent chance of using an index for LIKE '123%' but > >

Re: [sqlite] Solaris bus error

On Feb 1, 2008, at 6:37 AM, Ken wrote: Recompiled without the -O2 flags: #0 0x00049750 in findLockInfo (fd=4, ppLock=0x1345ad, ppOpen=0x1345a9) at ../sqliteSrc/sqlite-3.5.5/src/os_unix.c:670 670 *ppLock = pLock; *ppLock is Null Hi Ken, This doesn't look quite right to

[sqlite] unfinalized statements upon close question.

In my code I'm getting an SQLITE_BUSY returned from sqlite3_close. Is there a way to get sqite to tell me which statements it thinks are not finalized? Thanks, Ken

Re: [sqlite] Version 3.5.5 Released

Hi, The Precompiled Binary for Windows : the command-line sqlite-3_5_5.zip still contain the 3.5.4 version regards, Radzi. - Original Message - From: <[EMAIL PROTECTED]> To: Sent: Friday, February 01, 2008 1:33 AM Subject: [sqlite] Version 3.5.5