[sqlite] Re: v2 and v3 differences - Help with some SQL
Dr Gerard Hammond wrote: This SQL in a sqlite v2 database gives me 306 rows... This is what I would expect. SELECT date_trans, t.description, c.CategoryAbsolutePath, GST, total, TransID, HasSplitTrans, t.categoryID, t.Currency, t.locked FROM Transn t left outer join Category c on c.CategoryID=t.categoryID and AcctID in (3, 12, 11, 6, 28, 15) and t.categoryID is null ORDER BY date_trans I upgraded the db to a v3 db but I had some problems. In SQL v3 the same SQL on the same upgraded database now gives me 8743 rows (ie every single row in the Transn table) This is expected. If v2 behaved differently, there was a bug in it. A left outer join produces every row from the left-hand-side table at least once. That's the whole point of an outer join. If that's not what you want, why do you use one? Further, the query doesn't make any sense to me: when t.categoryID is null, c.CategoryID=t.categoryID is always false (even null=null is false), so you never actually retrieve any data from Category table and could just as well not join with it at all. Does anybody know how I re-write the SQL to give me 306 items again. It would help if you described the structure of the two tables, and explained what your query was supposed to achieve. I thought this would have worked but it gave me zero rows. It appears that you can't join a table if one value is a NULL. SELECT date_trans, t.description, c.CategoryAbsolutePath, GST, total, TransID, HasSplitTrans, t.categoryID, t.Currency, t.locked FROM Transn t,Category c where c.CategoryID=t.categoryID and AcctID in (3, 12, 11, 6, 28, 15) and t.categoryID is null ORDER BY date_trans The expression (t.categoryID is null and c.CategoryID=t.categoryID) is always false. If you mean to retrieve a cross-product of all records where t.categoryID is null and c.CategoryID is null, say so (not that I can imagine why anybody would want to do that). Even this didn't work: SELECT date_trans, t.description, c.CategoryAbsolutePath, GST, total, TransID, HasSplitTrans, t.categoryID, t.Currency, t.locked, coalesce(t.categoryID, -1) as 't.categoryID' FROM Transn t, Category c where c.CategoryID=t.categoryID and AcctID in (3, 12, 11, 6, 28, 15) and t.categoryID = -1 ORDER BY date_trans Do you have any records in Category table where c.CategoryID = -1 ? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] v2 and v3 differences - Help with some SQL
Hi, This SQL in a sqlite v2 database gives me 306 rows... This is what I would expect. SELECT date_trans, t.description, c.CategoryAbsolutePath, GST, total, TransID, HasSplitTrans, t.categoryID, t.Currency, t.locked FROM Transn t left outer join Category c on c.CategoryID=t.categoryID and AcctID in (3, 12, 11, 6, 28, 15) and t.categoryID is null ORDER BY date_trans I upgraded the db to a v3 db but I had some problems. In SQL v3 the same SQL on the same upgraded database now gives me 8743 rows (ie every single row in the Transn table) Does anybody know how I re-write the SQL to give me 306 items again. I thought this would have worked but it gave me zero rows. It appears that you can't join a table if one value is a NULL. SELECT date_trans, t.description, c.CategoryAbsolutePath, GST, total, TransID, HasSplitTrans, t.categoryID, t.Currency, t.locked FROM Transn t,Category c where c.CategoryID=t.categoryID and AcctID in (3, 12, 11, 6, 28, 15) and t.categoryID is null ORDER BY date_trans Even this didn't work: SELECT date_trans, t.description, c.CategoryAbsolutePath, GST, total, TransID, HasSplitTrans, t.categoryID, t.Currency, t.locked, coalesce(t.categoryID, -1) as 't.categoryID' FROM Transn t, Category c where c.CategoryID=t.categoryID and AcctID in (3, 12, 11, 6, 28, 15) and t.categoryID = -1 ORDER BY date_trans Here are the two tables: CREATE TABLE Category (CatParent Integer, Description varchar, CategoryID integer NOT NULL DEFAULT '0', CategoryAbsolutePath varchar, PRIMARY KEY(CategoryID)) CREATE TABLE Transn (GST double DEFAULT '0', AcctID integer, TransID integer NOT NULL, CategoryID integer, Total double DEFAULT '0', Description varchar, Date_Trans date, Notes varchar, NeedsAttention boolean DEFAULT 'False', Reconciled boolean DEFAULT 'False', GSTClaimed boolean DEFAULT 'False', HasSplitTrans boolean DEFAULT 'False', Currency varchar(10) DEFAULT 'AUD', CurrConverter float DEFAULT '1.00', ForeignCurrencyAmount double DEFAULT '0', locked boolean DEFAULT 'false', PRIMARY KEY(TransID)) -- Cheers, Dr Gerard Hammond We are on the cutting edge of catching up. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] attached databases
Use UNION to run queries against each db and return a single result. HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Tom Deblauwe [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 04, 2007 5:28 AM To: sqlite-users@sqlite.org Subject: [sqlite] attached databases Hello, As I understand it, you can attach databases together with sqlite and then perform 1 query on all the databases together. I have this situation where I have 3 disks, and every disk contains a database, and each of those databases has the same structure: ID, TimeFrom, TimeTo, Events I would like to do a query on all 3 databases, where I check the bitmask of 'Events' for a certain value and sort the results in ascending order based on TimeFrom. Can I do this with SQlite? How does the query look like, and what columns are returned then? Hopefully the full list with only 4 columns, not 12(3 db's x 4 columns)? Thanks for your time, Best regards, Tom, - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite 64-Bit
Of course, you also need a 64-bit capable processor. I've been compiling SQLite on AMD Athlon 64 boxes using Fedora Core and CentOS x86_64 operating systems (and that means the gcc toolchain) for ages. Bob Cochran > -Original Message- > From: Ahmed Sulaiman [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 4, 2007 07:38 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] SQLite 64-Bit > > Thanks Ian,.. I will give it a try :) > > -Original Message- > From: Ian Frosst [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 04, 2007 3:26 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] SQLite 64-Bit > > I build SQLite's code using Visual Studio 2005 using the 64 bit tool > chain, > and it runs without a hitch. I haven't seen a pre-compiled library for > it, > so you may have to get your hands a bit dirty. Building it is a snap > though; add the sqlite3.c and sqlite3.h files to a project, define > NO_TCL > and THREADSAFE in the preprocessor for your various configurations, and > voila. > > On 7/4/07, Ahmed Sulaiman <[EMAIL PROTECTED]> wrote: > > > > Hi all, > > > > > > > > We are interested in using the SQLite in our project. I wonder if > there > > a version that works in 64-Bit machine? I have looked in the download > > page and noticed that only Win32 (x86) builds are available. > > > > > > > > Regards > > > > > > > > Ahmed > > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite 64-Bit
Thanks Ian,.. I will give it a try :) -Original Message- From: Ian Frosst [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 04, 2007 3:26 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQLite 64-Bit I build SQLite's code using Visual Studio 2005 using the 64 bit tool chain, and it runs without a hitch. I haven't seen a pre-compiled library for it, so you may have to get your hands a bit dirty. Building it is a snap though; add the sqlite3.c and sqlite3.h files to a project, define NO_TCL and THREADSAFE in the preprocessor for your various configurations, and voila. On 7/4/07, Ahmed Sulaiman <[EMAIL PROTECTED]> wrote: > > Hi all, > > > > We are interested in using the SQLite in our project. I wonder if there > a version that works in 64-Bit machine? I have looked in the download > page and noticed that only Win32 (x86) builds are available. > > > > Regards > > > > Ahmed > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite 64-Bit
I build SQLite's code using Visual Studio 2005 using the 64 bit tool chain, and it runs without a hitch. I haven't seen a pre-compiled library for it, so you may have to get your hands a bit dirty. Building it is a snap though; add the sqlite3.c and sqlite3.h files to a project, define NO_TCL and THREADSAFE in the preprocessor for your various configurations, and voila. On 7/4/07, Ahmed Sulaiman <[EMAIL PROTECTED]> wrote: Hi all, We are interested in using the SQLite in our project. I wonder if there a version that works in 64-Bit machine? I have looked in the download page and noticed that only Win32 (x86) builds are available. Regards Ahmed
[sqlite] SQLite 64-Bit
Hi all, We are interested in using the SQLite in our project. I wonder if there a version that works in 64-Bit machine? I have looked in the download page and noticed that only Win32 (x86) builds are available. Regards Ahmed
Re: [sqlite] Database File size not exceeding 2GB
According to http://en.wikipedia.org/wiki/File_Allocation_Table , the limit on FAT16 is 2 gigabytes per file, on FAT32 it's 4 gigabytes per file, and on NTFS it's very, very large. In my application I needed to deal with splitting my data into 2 gigabyte (maximum) database file sizes, and I had two options: I could implement the DISKIO subfeature of SQLite3 which would let me emulate a very large file system on top of smaller file chunks, or... I could just implement a "Collection" object which implements the same C++ interface to my database, but splits the data across multiple databases, each of which are limited in size to 2 gigabytes. I found for my application that the latter choice was much easier and faster to implement. For each database file opened on a new connection, my code executes "PRAGMA max_page_count=XXX;" after figuring out how large a page is and dividing my desired maximum size by it. As of the 3.4.0 release, this max_page_count is per-connection and not per-file. And it's only checked when allocating new pages to the file through the pager allocation routines. --andy On 7/4/07, Ian Frosst <[EMAIL PROTECTED]> wrote: Is the file system holding your file Fat32, or NTFS? If it's Fat32, it may be the source of your problem, as it doesn't support very large files. Ian On 7/4/07, Krishnamoorthy, Priya (IE10) < [EMAIL PROTECTED]> wrote: > > Hi all, > > > > I am using SQLite3 database in my application. > > > > My application runs on Windows XP (32 bit) platform. I am not able to > store more than 2GB of data in my database. Is it not possible to store > more than 2gb data in windows XP? > > > > I used SQlite3 in Linux and could store more than 2GB. > > > > Please help me in this regard > > > > Best regards, > > Priya > > > > > >
Re: [sqlite] Re: sqlite_finalize() releases resources?
On 7/4/07, Dan Kennedy <[EMAIL PROTECTED]> wrote:
On Wed, 2007-07-04 at 09:58 +0200, Jef Driesen wrote:
> Igor Tandetnik wrote:
> > Mario Figueiredo wrote:
> >> I'm a tad bit confused with sqlite3_finalize() usage when the query
> >> fails. As it is, I'm assuming it releases resources and I use it in
> >> this context:
> >>
> >>
> >> rc = sqlite3_prepare_v2(/* ... */);
> >> if (rc != SQLITE_OK)
> >> {
> >> sqlite3_finalize(stmt);
> >> /* ... */
> >> }
> >
> > This doesn't make any sense. If prepare fails, you do not have a valid
> > statement handle to call finalize on.
> >
> > Igor Tandetnik
>
> Are you sure about that? The documentation for sqlite3_prepare_v2 says:
Igor is, as usual, correct.
The situation in 3.4.0 is that if sqlite3_prepare() returns other
than SQLITE_OK, you are guaranteed that *ppStmt is set to NULL.
You may call sqlite3_finalize() on this if you wish - it's a no-op.
Historically, it may have been that *ppStmt was sometimes left
uninitialized if an error occured (hence the "may" in the docs).
This is in contrast to sqlite3_open(). You must call sqlite3_close(),
even if sqlite3_open() returned an error code.
Dan.
> *ppStmt is left pointing to a compiled SQL statement structure that can
> be executed using sqlite3_step(). Or if there is an error, *ppStmt may
> be set to NULL. If the input text contained no SQL (if the input is and
> empty string or a comment) then *ppStmt is set to NULL. The calling
> procedure is responsible for deleting the compiled SQL statement using
> sqlite3_finalize() after it has finished with it.
>
> I also thought this means the statement has to be deleted with
> sqlite3_finalize, even when sqlite3_prepare_v2 failed (except for the
> case where NULL is returned). Notice the "may be set to NULL" in the
> documentation. Doesn't a non-NULL value indicate some memory was
> allocated and thus need to be freed?
It's that "may" indeed that caused the confusion and that is still
present on the current documentation. I suggest perhaps a review of
that paragraph now that it is clear the memory will always be
released.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: sqlite_finalize() releases resources?
Dan Kennedy wrote:
On Wed, 2007-07-04 at 09:58 +0200, Jef Driesen wrote:
Igor Tandetnik wrote:
Mario Figueiredo wrote:
I'm a tad bit confused with sqlite3_finalize() usage when the query
fails. As it is, I'm assuming it releases resources and I use it in
this context:
rc = sqlite3_prepare_v2(/* ... */);
if (rc != SQLITE_OK)
{
sqlite3_finalize(stmt);
/* ... */
}
This doesn't make any sense. If prepare fails, you do not have a valid
statement handle to call finalize on.
Are you sure about that? The documentation for sqlite3_prepare_v2 says:
Igor is, as usual, correct.
The situation in 3.4.0 is that if sqlite3_prepare() returns other
than SQLITE_OK, you are guaranteed that *ppStmt is set to NULL.
You may call sqlite3_finalize() on this if you wish - it's a no-op.
Historically, it may have been that *ppStmt was sometimes left
uninitialized if an error occured (hence the "may" in the docs).
What do you mean with uninitialized? Leaving the pointer unchanged, or
pointing to some memory that is already freed or still needs to be
freed? This is important if you need to support older versions.
I suppose you mean the first one, but I'm asking anyway just to be sure.
In my code, I always initialize pointers to NULL, so this case would not
cause any problems at all. The second case is a completely different
story of course.
Anyway, the code I'm using should be fine in all cases:
sqlite3_stmt *stmt = 0;
#if SQLITE_VERSION_NUMBER >= 3003009
int rc = sqlite3_prepare_v2 (db, sql, nbytes, , tail);
#else
int rc = sqlite3_prepare (db, sql, nbytes, , tail);
#endif
if (rc != SQLITE_OK && stmt != 0) {
sqlite3_finalize (stmt);
stmt = 0;
}
This is in contrast to sqlite3_open(). You must call sqlite3_close(),
even if sqlite3_open() returned an error code.
I know. I had a topic on that a few months ago [1].
[1] http://www.mail-archive.com/sqlite-users@sqlite.org/msg21324.html
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Database File size not exceeding 2GB
Is the file system holding your file Fat32, or NTFS? If it's Fat32, it may be the source of your problem, as it doesn't support very large files. Ian On 7/4/07, Krishnamoorthy, Priya (IE10) <[EMAIL PROTECTED]> wrote: Hi all, I am using SQLite3 database in my application. My application runs on Windows XP (32 bit) platform. I am not able to store more than 2GB of data in my database. Is it not possible to store more than 2gb data in windows XP? I used SQlite3 in Linux and could store more than 2GB. Please help me in this regard Best regards, Priya
[sqlite] attached databases
Hello, As I understand it, you can attach databases together with sqlite and then perform 1 query on all the databases together. I have this situation where I have 3 disks, and every disk contains a database, and each of those databases has the same structure: ID, TimeFrom, TimeTo, Events I would like to do a query on all 3 databases, where I check the bitmask of 'Events' for a certain value and sort the results in ascending order based on TimeFrom. Can I do this with SQlite? How does the query look like, and what columns are returned then? Hopefully the full list with only 4 columns, not 12(3 db's x 4 columns)? Thanks for your time, Best regards, Tom, - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] self join instead of temporary table
The following query works fine (and is quick), but I can't help thinking that it should be possible to it with a single SELECT and a self join. CREATE TEMPORARY TABLE temp (eid INT, P DOUBLE); INSERT INTO temp (eid, P) SELECT eid, MIN(P) FROM barforce_1 GROUP BY eid; SELECT a1.eid, a1.P, a2.subcase FROM temp a1, barforce_1 a2 WHERE a1.P = a2.P ORDER BY a1.eid; DROP TABLE temp; I had the following in mind, but it doesn't work (the subcase column contains the max subcase value, rather than the subcase at which the minimum P value occurred): SELECT a1.eid, MIN(a1.P), a2.subcase FROM barforce_1 a1, barforce_1 a2 WHERE a1.P = a2.P GROUP BY a1.eid; Any ideas? Jeff Ratcliffe - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: sqlite_finalize() releases resources?
On Wed, 2007-07-04 at 09:58 +0200, Jef Driesen wrote:
> Igor Tandetnik wrote:
> > Mario Figueiredo wrote:
> >> I'm a tad bit confused with sqlite3_finalize() usage when the query
> >> fails. As it is, I'm assuming it releases resources and I use it in
> >> this context:
> >>
> >>
> >> rc = sqlite3_prepare_v2(/* ... */);
> >> if (rc != SQLITE_OK)
> >> {
> >> sqlite3_finalize(stmt);
> >> /* ... */
> >> }
> >
> > This doesn't make any sense. If prepare fails, you do not have a valid
> > statement handle to call finalize on.
> >
> > Igor Tandetnik
>
> Are you sure about that? The documentation for sqlite3_prepare_v2 says:
Igor is, as usual, correct.
The situation in 3.4.0 is that if sqlite3_prepare() returns other
than SQLITE_OK, you are guaranteed that *ppStmt is set to NULL.
You may call sqlite3_finalize() on this if you wish - it's a no-op.
Historically, it may have been that *ppStmt was sometimes left
uninitialized if an error occured (hence the "may" in the docs).
This is in contrast to sqlite3_open(). You must call sqlite3_close(),
even if sqlite3_open() returned an error code.
Dan.
> *ppStmt is left pointing to a compiled SQL statement structure that can
> be executed using sqlite3_step(). Or if there is an error, *ppStmt may
> be set to NULL. If the input text contained no SQL (if the input is and
> empty string or a comment) then *ppStmt is set to NULL. The calling
> procedure is responsible for deleting the compiled SQL statement using
> sqlite3_finalize() after it has finished with it.
>
> I also thought this means the statement has to be deleted with
> sqlite3_finalize, even when sqlite3_prepare_v2 failed (except for the
> case where NULL is returned). Notice the "may be set to NULL" in the
> documentation. Doesn't a non-NULL value indicate some memory was
> allocated and thus need to be freed?
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: sqlite_finalize() releases resources?
Igor Tandetnik wrote:
Mario Figueiredo wrote:
I'm a tad bit confused with sqlite3_finalize() usage when the query
fails. As it is, I'm assuming it releases resources and I use it in
this context:
rc = sqlite3_prepare_v2(/* ... */);
if (rc != SQLITE_OK)
{
sqlite3_finalize(stmt);
/* ... */
}
This doesn't make any sense. If prepare fails, you do not have a valid
statement handle to call finalize on.
Igor Tandetnik
Are you sure about that? The documentation for sqlite3_prepare_v2 says:
*ppStmt is left pointing to a compiled SQL statement structure that can
be executed using sqlite3_step(). Or if there is an error, *ppStmt may
be set to NULL. If the input text contained no SQL (if the input is and
empty string or a comment) then *ppStmt is set to NULL. The calling
procedure is responsible for deleting the compiled SQL statement using
sqlite3_finalize() after it has finished with it.
I also thought this means the statement has to be deleted with
sqlite3_finalize, even when sqlite3_prepare_v2 failed (except for the
case where NULL is returned). Notice the "may be set to NULL" in the
documentation. Doesn't a non-NULL value indicate some memory was
allocated and thus need to be freed?
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
