Re: [sqlite] Regarding explicitly managing SQLite Databases....[Resend]

2007-12-11 Thread Yuvaraj Athur Raghuvir
1) Can I assume that MAX_PAGE_COUNT * PAGE_SIZE = size of file on disk? 2) When insert statements are being executed, I would like to - for example - redirect the queries to another data base when 70% of the space is reached. How can I do that? ~Yuva On Dec 11, 2007 11:48 AM, Dan <[EMAIL

Re: [sqlite] Regarding explicitly managing SQLite Databases....[Resend]

2007-12-11 Thread Dan
On Dec 11, 2007, at 3:00 PM, Yuvaraj Athur Raghuvir wrote: 1) Can I assume that MAX_PAGE_COUNT * PAGE_SIZE = size of file on disk? No. It is the maximum size of the file. An attempt to insert data that would cause the file to grow larger than this will return SQLITE_FULL. 2) When insert

RE: [sqlite] how to cast utf16 text to int?

2007-12-11 Thread Maxim V. Shiyanovsky
Thanks, Trevor. I'll try it. One more question - is there any way to insert utf-16 string from sqlite3.exe? As I understand I can't use syntax like this: insert into t values (X'31003700', 1); -Original Message- From: Trevor Talbot [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 11,

Re: [sqlite] Regarding explicitly managing SQLite Databases....[Resend]

2007-12-11 Thread Yuvaraj Athur Raghuvir
Thanks. To ensure that the database size is closest to the data in the database, I see the following have to be taken care off: a) The latest in-memory transactions have to be flushed to disk. How? b) The space has to be reclaimed after delete/drop. For this I use the PRAGMA VACUUM statement.

RE: [sqlite] how to cast utf16 text to int?

2007-12-11 Thread Maxim V. Shiyanovsky
Sorry, I forgot initial problem. As I wrote in the first letter I need cast from utf-16 text to int. In other words I need select like this: Select * from t, d where cast(t.value as int) = d.id Assuming t.value is utf-16 presentation of numeric. This select works fine for one-digit value. It

Re: [sqlite] Seg fault after calling sqlite3_exec while running under cygwin

2007-12-11 Thread Simon Davies
Hi John, initdb() does not modify the db handle in main(). Call should be: err_c = initdb(); and initdb prototype should be int initdb(sqlite3** db) etc... Rgds, Simon On 11/12/2007, John Williams <[EMAIL PROTECTED]> wrote: > I forgot to attach my sample code to my previous

RE: [sqlite] how to cast utf16 text to int?

2007-12-11 Thread Maxim V. Shiyanovsky
Sorry again. The problem is solved. I had to read FM :) and use sqlite3_open16 instead of sqlite3_open. -Original Message- From: Maxim V. Shiyanovsky [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 11, 2007 2:34 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] how to cast utf16

[sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

2007-12-11 Thread Jim Correia
I notice that SQLite 3.4.0 and later impose hard limits on some sizes. I'm running into a problem where a .dump/.load cycle fails on a database with columns that have blobs which are about 2MB in size. Looking at the source for 3.5.3 (I can't find a tarball of 3.4 on the web site, but I'm

Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-11 Thread Joe Wilson
--- Trevor Talbot <[EMAIL PROTECTED]> wrote: > On 12/10/07, Robert Wishlaw <[EMAIL PROTECTED]> wrote: > > > IBM DB2 9.5 > > > > select a AS "foo" from t1 union select b from t1 order by foo > > SQL0206N "FOO" is not valid in the context where it is used. > > SQLSTATE=42703 > > The problem

[sqlite] Unable to open database: out of memory

2007-12-11 Thread DarĂ­o Mariani
Hello: I'm using SQLite 3.4.1 on an AIX 5.3. I have a file that grew up to 2 GB, now when I try to open it with the sqlite3 command I cannot, the message is the following: $ sqlite3 my_file.db Unable to open database "my_file.db": out of memory File permissions are ok, user limits are to the

Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-11 Thread Dennis Cote
Joe Wilson wrote: Let's add another row to table t1... postgres=> insert into t1 values(2, -1000, 5); INSERT 0 1 test=> select * from t1; a | b | c ---+---+--- 1 | 2 | 4 2 | -1000 | 5 postgres=> select a, a+b AS "c" from t1 order by c; a | c ---+-- 2 | -998 1 |3

Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

2007-12-11 Thread Joe Wilson
--- Jim Correia <[EMAIL PROTECTED]> wrote: > I notice that SQLite 3.4.0 and later impose hard limits on some > sizes. I'm running into a problem where a .dump/.load cycle fails on > a database with columns that have blobs which are about 2MB in size. > > Looking at the source for 3.5.3 (I

Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-11 Thread Brad Stiles
> Could someone please post the results of these queries on Oracle, > DB2 or SQL Server? On MS SQL Server 2000, your queries result in the following: a c --- --- 2-998 1 3 (2 row(s) affected) foo ---

RE: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-11 Thread Samuel R. Neff
MSSQL results match your MySQL and PostgreSQL results. (I only changed the table name to be a temporary table) create table #t1(a INT, b INT, c INT); insert into #t1 values(1, 2, 4); insert into #t1 values(2, -1000, 5); (1 row(s) affected) (1 row(s) affected) -- See if select alias

Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-11 Thread drh
Dennis Cote <[EMAIL PROTECTED]> wrote: > > So SQLite should be sorting by the result column "c" not the column "c" > in the table T1 which it doing. > My understand as of yesterday is that column name aliases are significant for ORDER BY, but not for WHERE, in standard SQL. This is not as

Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-11 Thread Michael Schlenker
Joe Wilson schrieb: --- Trevor Talbot <[EMAIL PROTECTED]> wrote: On 12/10/07, Robert Wishlaw <[EMAIL PROTECTED]> wrote: IBM DB2 9.5 select a AS "foo" from t1 union select b from t1 order by foo SQL0206N "FOO" is not valid in the context where it is used. SQLSTATE=42703 The problem here is

Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-11 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote: > The current name resolution rules for SQLite are that it > first tries to resolve names using just the tables in the > join. If that fails, then it looks at result column aliases. > I think that approach continues to work on WHERE. But I need > to reverse the

[sqlite] Improving performance of SQLite. Anyone heard of DeviceSQL?

2007-12-11 Thread Robert Smith
After spending some time trying various methods to optimize the performance of SQLite for an ARM-based application, I've come across a technology called DeviceSQL. The developers of DeviceSQL (Encirq) claim it has 5x the performance of SQLite and they are putting on a webinar on Dec. 13th to go

Re: [sqlite] Improving performance of SQLite. Anyone heard of DeviceSQL?

2007-12-11 Thread drh
Robert Smith <[EMAIL PROTECTED]> wrote: > After spending some time trying various methods to optimize the performance > of SQLite for an ARM-based application, I've come across a technology called > DeviceSQL. The developers of DeviceSQL (Encirq) claim it has 5x the > performance of SQLite and

Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

2007-12-11 Thread Jim Correia
On Dec 11, 2007, at 11:03 AM, Joe Wilson wrote: If this is intentional, what is the recommended replacement for .dump/.load for large rows? You have to recompile with a large value for SQLITE_MAX_SQL_LENGTH via a compiler -D flag or other means. Monotone encountered this issue as well for

Re: [sqlite] sample code

2007-12-11 Thread Clay Dowling
Tom Parke wrote: > Where can I find some sample C code for stepping thru a result set and > for binding variables to columns? I am just beginning to experiment > with Sqlite3 and I am having a hard time getting aquainted. http://www.lazarusid.com/sqlite3 >From a Linux Journal article several

[sqlite] sample code

2007-12-11 Thread Tom Parke
Where can I find some sample C code for stepping thru a result set and for binding variables to columns? I am just beginning to experiment with Sqlite3 and I am having a hard time getting aquainted. Thanks, Tom

[sqlite] Strange .dump output

2007-12-11 Thread Mitchell Vincent
I've noticed something strange in some of the SQL statements created from the command line utility in Windows (the most current version as of this morning). For nearly all of the tables it works well but it puts a ' character around the name of one table named invoice_master, which is breaking

Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

2007-12-11 Thread drh
Jim Correia <[EMAIL PROTECTED]> wrote: > > Is a 1MB limit on the SQL intentional? > > Per my previous message, the comment in the source disagrees with the > value. > > Also, at the default value, .dump/.load will only support rows of > about 1/2 MB (to account for hex expansion), while the

Re: [sqlite] Strange .dump output

2007-12-11 Thread drh
"Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > I've noticed something strange in some of the SQL statements created > from the command line utility in Windows (the most current version as > of this morning). > > For nearly all of the tables it works well but it puts a ' character > around the

Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

2007-12-11 Thread Jim Correia
On Dec 11, 2007, at 2:10 PM, [EMAIL PROTECTED] wrote: You should normally not be inserting megabyte-sized blobs and strings using raw SQL. Instead, use bound parameters: sqlite3_prepare("INSERT INTO tablexyz VALUES(:blobcontent)"); sqlite3_bind_blob(pStmt, 1, pBlobContent,

Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

2007-12-11 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote: > Yes, this does create problems for .dump/.load in the shell. > But, as has been pointed out, you can work around it using > a compile-time switch: > > gcc -DSQLITE_MAX_SQL_LENGTH=10 shell.c sqlite3.c -o sqlite3 > > I should probably modify the makefile

Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

2007-12-11 Thread Jim Correia
On Dec 11, 2007, at 2:10 PM, [EMAIL PROTECTED] wrote: The limits in SQLite (introduced in version 3.4.0) were added at the request of the Google Gears developers. Consider the situation that Gears and similar applications (such as Adobe AIR) are in. They have to accept generic SQL from

Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

2007-12-11 Thread drh
Joe Wilson <[EMAIL PROTECTED]> wrote: > --- [EMAIL PROTECTED] wrote: > > Yes, this does create problems for .dump/.load in the shell. > > But, as has been pointed out, you can work around it using > > a compile-time switch: > > > > gcc -DSQLITE_MAX_SQL_LENGTH=10 shell.c sqlite3.c -o

Re: [sqlite] Strange .dump output

2007-12-11 Thread Mitchell Vincent
Sure enough, that's in the sqlite_master table. I guess I did do it back when I created the database but I can't imagine why.. Thanks! On Dec 11, 2007 2:19 PM, <[EMAIL PROTECTED]> wrote: > "Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > > I've noticed something strange in some of the SQL

Re: [sqlite] Regarding explicitly managing SQLite Databases....[Resend]

2007-12-11 Thread Kees Nuyt
On Tue, 11 Dec 2007 15:37:52 +0530, "Yuvaraj Athur Raghuvir" <[EMAIL PROTECTED]> wrote: >Thanks. > >To ensure that the database size is closest to the data in the database, I >see the following have to be taken care off: >a) The latest in-memory transactions have to be flushed to disk. >How? >b)

Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-11 Thread Robert Wishlaw
On 12/11/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > --- [EMAIL PROTECTED] wrote: > > The current name resolution rules for SQLite are that it > > first tries to resolve names using just the tables in the > > join. If that fails, then it looks at result column aliases. > > I think that approach

Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

2007-12-11 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote: > I think those exceedingly rare programs that need a larger > SQL statement length limit can include their own copy of > sqlite3.c. I does not take up that much space, after all. It's easy enough to recompile with the new setting once you're aware of it. But

Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-11 Thread Robert Wishlaw
On 12/11/07, Robert Wishlaw <[EMAIL PROTECTED]> wrote: > On 12/11/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > > --- [EMAIL PROTECTED] wrote: > > > The current name resolution rules for SQLite are that it > > > first tries to resolve names using just the tables in the > > > join. If that fails,

[sqlite] select date using variables in Python

2007-12-11 Thread C M
I'm new to SQLite and can't figure out the right way to write this. I want to select a range of dates, let's say anything beyond tomorrow So in my table called test I want to select the column called string based on the date being tomorrow or later... This statement (from the sql wiki about