[sqlite] disk I/O error

2007-06-05 Thread Arun Bhalla

Hello,

This message may be off-topic in that I don't think the following issue
 corresponds to a bug with SQLite, but it's something I've discovered
while using SQLite.  Perhaps someone on the list has had a similar
experience or could make a suggestion.

A Linux 2.6/x86_64 system reports a "disk I/O error" (SQLITE_IOERR)
while generating a specific report from a SQLite database (SQLite
3.3.6).  The database and temporary files are accessed through an NFS
mount.  After running the program again with SQLite tracing enabled
(plus a bit more I added), I see that SQLITE_IOERR is returned by
unixRead() because read() unexpectedly returned 0!

Here's some relevant strace output:

open("/nfs/tmp/sqlite_dBjTG5bZdsqFVPb", O_RDWR|O_CREAT|O_EXCL, 0644) = 8

[...]

lseek(8, 193536, SEEK_SET)= 193536
write(8, "\n\0\0\0\30\0\222\0\0\266\0\332\0\376\1\"\1F\1l\1\222\1"...,
1024) = 1024

[...]

lseek(8, 226304, SEEK_SET)= 226304
write(8, "\n\0\0\0\30\0\240\0\0\240\0\304\0\350\1\f\0010\1T\1x\1"...,
1024) = 1024
lseek(8, 193536, SEEK_SET)= 193536
read(8, "", 1024) = 0
fstat(8, {st_mode=S_IFREG|0644, st_size=227328, ...}) = 0

The read() call shouldn't fail -- the same page was written to at the
beginning of the transaction!  At least by the time fstat() is called,
the file is 227328 bytes long, so a read at an offset of 193536 should
not fail.

I'm suspecting that the NFS server in question is buggy or
misconfigured.  Unfortunately I don't have access to either the NFS
server or the host running the program, so mainly all I can access is
some strace and SQLite tracing output.

Thanks for any suggestions,
Arun


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Stack usage

2007-06-05 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> My application's doesn't create any databases itself. It allows users to 
> store any data. And users need to be able to store any number of columns in 
> 1 table (the most I'v heard about is about 1, but I wouldn't be 
> surprised if they had more). Trust me, they need it and they can't do 
> anything about it.. 
> But the problem isnt't actually caused by many columns in the table. The 
> query i posted before doesn't need any table in the database - it was 
> created based on the a table but any similar query will crash SQLite. The 
> query was created by JDBC driver - I will change driver's code and the 
> problem will be solved for now, but it doesn't mean that everything is ok. 
> Similar automatically created queries may be created based on anything, for 
> example rows of some table - 1 rows isn't too many, right? And that 
> query may cause any application to crash. And possibly there are other 
> innocent looking queries that are implemented using recursion that will do 
> the same damage (maybe nested selects ?).

Since your program uniquely requires such a high level of fault tolerance
and you have very demanding customers, you should consider buying some 
commercial support:

 http://www.hwaci.com/sw/sqlite/prosupport.html



   

Yahoo! oneSearch: Finally, mobile search 
that gives answers, not web links. 
http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Stack usage

2007-06-05 Thread Eduardo Morras

At 20:31 05/06/2007, you wrote:


> Excuse me for make this question but have you
> normalized your database? Try to design/define it in at least 3NormalForm.

My application's doesn't create any databases itself. It allows users to
store any data. And users need to be able to store any number of columns in
1 table (the most I'v heard about is about 1, but I wouldn't be
surprised if they had more). Trust me, they need it and they can't do
anything about it..
But the problem isnt't actually caused by many columns in the table. The
query i posted before doesn't need any table in the database - it was
created based on the a table but any similar query will crash SQLite. The
query was created by JDBC driver - I will change driver's code and the
problem will be solved for now, but it doesn't mean that everything is ok.
Similar automatically created queries may be created based on anything, for
example rows of some table - 1 rows isn't too many, right? And that
query may cause any application to crash. And possibly there are other
innocent looking queries that are implemented using recursion that will do
the same damage (maybe nested selects ?).


The number of rows is not a problem, the number of columns may be. My 
database teacher said that "the best table have 2 columns" too 
theoric for me but several hundreds or thousands... too practical.




-
Antivirus Warning: User detected. Please keep away from keyboard or 
you will be erased. Thank you. 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Sorted index

2007-06-05 Thread Tom Briggs

   When querying the table be sure to put the indexed column(s) in the
ORDER BY clause though - otherwise the index won't do you any good. :)

   -T 

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, June 05, 2007 9:20 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Sorted index
> 
> B-Tree indices are in sorted sequence.  Just raise an index 
> on the column.
> 
> [EMAIL PROTECTED] wrote:
> > 
> > 
> > I would like to maintain a sorted list of ~3000 entries.
> > I know that I can create a table and the SELECT from it 
> with the ORDER BY clause
> > in order to sort it.
> > However I do not want the overhead of doing this after 
> adding a new entry.
> > It would be good if I could create an index that was 
> sorted, and then when I
> > add a new entry to the table it would automatically be 
> inserted in the index at
> > the correct position.
> > Is this possibe?
> > If not, can anyone suggest any other solution, baring in 
> mind that RAM memory
> > needs to be kep to a minimum?
> > 
> > Thanks
> > Clive
> > 
> > 
> > 
> > 
> --
> ---
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > 
> --
> ---
> > 
> 
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 
> 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Why does "SELECT julianday('some_date') AS dateNumber" get me a string via ODBC?

2007-06-05 Thread Trey Mack

I see that it is a variant of type VT_BSTR containing
"2345678.9123" (or whatever), and I have to use strtod() to convert it
to the number I want.  Is there a way to write the query to ensure that
it will give me a number instead of a string?


The type selection is done in your ODBC wrapper, and if it's storing that 
floating-point value as a BSTR in its returned variant, my guess is that 
it's not checking the return value from sqlite3_column_type(), and it'll 
just store everything as a BSTR. No query will change that. (The exception 
is blobs, they should be VT_ARRAY | VT_UI1).


So I would think the answer is no. Then again, I've never used the wrapper 
you're using.


- TMack


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Re: Re: Newbie - can't create database but I can attach

2007-06-05 Thread Igor Tandetnik

nvi-yMULe1UMIukeIZ0/[EMAIL PROTECTED] wrote:

I wasn't specific enough, here's what's happening in my DOS window...

SQLite version 3.3.17
Enter ".help" for instructions
sqlite> sqlite3 testdatabase //hard return


That's not how it works. "sqlite3 testdatabase" is supposed to be run 
from DOS command line, not from SQLite command line. This way you start 
SQLite shell and tell it which DB file to open. Once the shell is 
running, there's no way to open a different database as MAIN (but you 
can attach one).


If you run sqlite3 with no command line, it will open :memory: database 
(a database not backed by any disk file).


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Re: Newbie - can't create database but I can attach

2007-06-05 Thread nvi
Thank you Igor, I appreciate your time!

I wasn't specific enough, here's what's happening in my DOS window...

---

SQLite version 3.3.17
Enter ".help" for instructions
sqlite> sqlite3 testdatabase //hard return
   ...> ; //hard return
SQL error: near "sqlite3": syntax error
sqlite>

---

I was following the example at http://sqlite.org/sqlite.html
(substituting testdatabase for ex1) and my experiences seem to deviate
from what I would expect.  

Also, I tried this, per your comment about needing a table first...

---

SQLite version 3.3.17
Enter ".help" for instructions
sqlite> sqlite3 testdatabase //hard return
   ...> create table tbl1(one varchar(10), two smallint); //hard return
SQL error: near "sqlite3": syntax error
sqlite>

---

I also continued with an 'insert' statement, before a semicolon, but to
no avail.

Is there a SQLite newbie group / further resource you'd recommend, I
hate wasting people's time.  








On Tue, 5 Jun 2007 13:54:50 -0400, "Igor Tandetnik"
<[EMAIL PROTECTED]> said:
> nvi-yMULe1UMIukeIZ0/[EMAIL PROTECTED] wrote:
> > Hello, thanks in advance, I've tried to answer this through extensive
> > searching.
> >
> > The following syntax does not create a database, e.g.;
> >
> > sqlite> sqlite3 testdatabase
> 
> SQLite defers creating the database file until you create the first 
> table.
> 
> > However, if I create a sqlite database with TkSQLite, sqlite.exe can
> > 'attach' to that test database, and, using the sqlite.exe command
> > '.databases', I can see that database, however, using '.tables'
> > doesn't show me the test table.
> 
> .tables command only shows tables in the main database. To see tables in 
> attached databases, use
> 
> select * from db.sqlite_master;
> 
> where 'db' is the name under which you attached the database.
> 
> Igor Tandetnik 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
-- 
  
  [EMAIL PROTECTED]


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Why does "SELECT julianday('some_date') AS dateNumber" get me a string via ODBC?

2007-06-05 Thread Omar Eljumaily
I believe ODBC will do type conversions for you.  If not, use sscanf.  
All sqlite data is stored as strings, I believe, anyway, so there's lots 
of type conversion going whether it's explicit or not.


Representing dates as floating point numbers can be tricky because of 
subtle rounding issues, especially when you're dealing with DST issues, 
btw.  You're better off dealing with them as Julian strings.



Rob Richardson wrote:

Greetings!

 


I am trying to talk to a small SQLite database through ADO and an ODBC
driver.  I want to convert a date to a Julian day before using the date
in a query of a table that could have over a million records.  In
SQliteSpy, the query "SELECT julianday('2007-06-05 12:34:56',
'localtime') AS dateNumber" gives me a floating-point number.  But in
ODBC, I use that query to create a recordset and examine the first value
in the recordset, I see that it is a variant of type VT_BSTR containing
"2345678.9123" (or whatever), and I have to use strtod() to convert it
to the number I want.  Is there a way to write the query to ensure that
it will give me a number instead of a string?

 


RobR

 


P.S.  Since I am going to turn around and use the value in another
query, I think I actually do want it in a string, but I'd still like to
know the answer for future reference.


  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Why does "SELECT julianday('some_date') AS dateNumber" get me a string via ODBC?

2007-06-05 Thread Rob Richardson
Greetings!

 

I am trying to talk to a small SQLite database through ADO and an ODBC
driver.  I want to convert a date to a Julian day before using the date
in a query of a table that could have over a million records.  In
SQliteSpy, the query "SELECT julianday('2007-06-05 12:34:56',
'localtime') AS dateNumber" gives me a floating-point number.  But in
ODBC, I use that query to create a recordset and examine the first value
in the recordset, I see that it is a variant of type VT_BSTR containing
"2345678.9123" (or whatever), and I have to use strtod() to convert it
to the number I want.  Is there a way to write the query to ensure that
it will give me a number instead of a string?

 

RobR

 

P.S.  Since I am going to turn around and use the value in another
query, I think I actually do want it in a string, but I'd still like to
know the answer for future reference.



Re: [sqlite] Stack usage

2007-06-05 Thread BardzoTajneKonto

> Excuse me for make this question but have you 
> normalized your database? Try to design/define it in at least 3NormalForm.

My application's doesn't create any databases itself. It allows users to 
store any data. And users need to be able to store any number of columns in 
1 table (the most I'v heard about is about 1, but I wouldn't be 
surprised if they had more). Trust me, they need it and they can't do 
anything about it.. 
But the problem isnt't actually caused by many columns in the table. The 
query i posted before doesn't need any table in the database - it was 
created based on the a table but any similar query will crash SQLite. The 
query was created by JDBC driver - I will change driver's code and the 
problem will be solved for now, but it doesn't mean that everything is ok. 
Similar automatically created queries may be created based on anything, for 
example rows of some table - 1 rows isn't too many, right? And that 
query may cause any application to crash. And possibly there are other 
innocent looking queries that are implemented using recursion that will do 
the same damage (maybe nested selects ?).


--
Nowy darmowy serwis og³oszeniowy Populada. Kup, sprzedaj, zamieñ  
>>> http://link.interia.pl/f1a8a


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Stack usage

2007-06-05 Thread Eduardo Morras

At 16:40 05/06/2007, you wrote:

Joe Wilson napisa³(a):
> Please respond to the mailing list in the future.

Sorry. Different client. I didn't notice the adress.

> At least theres a known workaround, so no problem.

Workaround is not a solution.

>
> > > hence your problem.
> >
> > Sure it is. Just like any bug or missing feature in any application. But
> Iv always hoped that
> > SQLite wont constraint me. Its 
sad the it does, and horrible that it

> crashes instead returning
> > an error.
>
> You should ask for your money back.

Yes it's free. But I think that no user (or 
author) of software like when software crashes. Unfortunatelly SQLite does.


Last time wasn't sent to the mailing list so once more:
Does anyone know other places apart from unions 
where deep recursion may be used?


Excuse me for make this question but have you 
normalized your database? Try to design/define it in at least 3NormalForm.



A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail? 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Re: Newbie - can't create database but I can attach

2007-06-05 Thread Igor Tandetnik

nvi-yMULe1UMIukeIZ0/[EMAIL PROTECTED] wrote:

Hello, thanks in advance, I've tried to answer this through extensive
searching.

The following syntax does not create a database, e.g.;

sqlite> sqlite3 testdatabase


SQLite defers creating the database file until you create the first 
table.



However, if I create a sqlite database with TkSQLite, sqlite.exe can
'attach' to that test database, and, using the sqlite.exe command
'.databases', I can see that database, however, using '.tables'
doesn't show me the test table.


.tables command only shows tables in the main database. To see tables in 
attached databases, use


select * from db.sqlite_master;

where 'db' is the name under which you attached the database.

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Newbie - can't create database but I can attach

2007-06-05 Thread nvi
Hello, thanks in advance, I've tried to answer this through extensive
searching.

The following syntax does not create a database, e.g.;

sqlite> sqlite3 testdatabase

However, if I create a sqlite database with TkSQLite, sqlite.exe can
'attach' to that test database, and, using the sqlite.exe command
'.databases', I can see that database, however, using '.tables' doesn't
show me the test table.  But, oddly enough, if I correctly specify the
table name with a 'select' query, I can get the table data.

I did the above on two Windows XP boxes with full rights but same
problem using SQLite version 3.3.17.





 

-- 
  
  [EMAIL PROTECTED]


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Does Attach improve Concurrency ?

2007-06-05 Thread BardzoTajneKonto

> >> Does
> >> using separate databases and attaching them improve concurrency (by
> >> providing finer-grained locking)?
> >
> > Yes it does. You can open a connection to a memory database, store each 
> table
> > in a separate database and attach  them if needed. I'v already changed
> > sqlite to do it transparently. It still has a lot of bugs, but it has
> > already proven to work. But if you don't mind attaching databases manually
> > originall sqlite will work perfectly.
> 
> 
> As stated above, not needed if you avoid using BEGIN EXCLUSIVE.

It is impossible to write to 2 tables even with default transaction.
It is impossible to insert inside a select callback.
Seeing numerous "Table is locked" topics I think there are lots of other 
impossible things to do.
And commit does appear at some time, so reading should be synchronized 
outside of SQLite (or perhaps repeated when error occures).
Attach allows to forget about those problems and makes possible table level 
locks instead of database level locks.


--
Nowy darmowy serwis og³oszeniowy Populada. Kup, sprzedaj, zamieñ  
>>> http://link.interia.pl/f1a8a


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Does Attach improve Concurrency ?

2007-06-05 Thread Christian Smith

Eduardo Morras uttered:


At 19:32 01/06/2007, you wrote:


When you have a connection with multiple attached databases and the
connection acquires an exclusive lock, does it always lock all attached
databases or does it keep track of which databases require the lock?  Does
using separate databases and attaching them improve concurrency (by
providing finer-grained locking)?


It locks all attached databases. No, it does not improve concurrency but 
i can improve speed if database files are on more than one phisical 
disk. The sqlite bottleneck is i/o access on most cases.




Tip for performance on Linux and ext3.

Mount your database filesystem with "data=journal" option to write data to 
the journal before being written in place. This reduces latency 
drastically, as the journal is contiguous and written at the full IO rate 
of the device without seeks. An example of the effect it has on a test I 
did can be found here:

http://www.osnews.com/permalink.php?news_id=16522_id=184137


Christian

--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Does Attach improve Concurrency ?

2007-06-05 Thread Christian Smith

[EMAIL PROTECTED] uttered:




When you have a connection with multiple attached databases and the
connection acquires an exclusive lock, does it always lock all attached
databases or does it keep track of which databases require the lock?


1st process:
C:\Documents and Settings\Administrator\Pulpit>sqlite3.exe a.db
SQLite version 3.3.17
Enter ".help" for instructions
sqlite> attach 'b.db' as b;
sqlite> begin exclusive;

2nd:
C:\Documents and Settings\Administrator\Pulpit>sqlite3.exe b.db
SQLite version 3.3.17
Enter ".help" for instructions
sqlite> create table tab(col);
SQL error: database is locked

So it locks all attached databases.



Not by default. BEGIN EXCLUSIVE is not the default transaction locking 
mode. By default, BEGIN will not lock anything until needed, in which case 
you can have different sessions locking different attached databases in 
different ways. SQLite will not block readers until it gets an EXCLUSIVE 
lock, which is usually upon committal of a transaction, or the spillage of 
pages from a full page cache. But that will only be on the database that 
is being updated. BEGIN EXCLUSIVE is explicit and applied to all attached 
databases. But upgrading an attached database to an EXCLUSIVE lock does 
not upgrade locks on other attached databases.


So, by default, using multiple attached databases may increase 
concurrency, but watch for deadlock conditions.








Does
using separate databases and attaching them improve concurrency (by
providing finer-grained locking)?


Yes it does. You can open a connection to a memory database, store each table
in a separate database and attach  them if needed. I'v already changed
sqlite to do it transparently. It still has a lot of bugs, but it has
already proven to work. But if you don't mind attaching databases manually
originall sqlite will work perfectly.



As stated above, not needed if you avoid using BEGIN EXCLUSIVE.


Christian

--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Stack usage

2007-06-05 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> Joe Wilson napisa³(a):
> > Please respond to the mailing list in the future.
> 
> Sorry. Different client. I didn't notice the adress.
> 
> > At least there's a known workaround, so no problem.
> 
> Workaround is not a solution.

Increasing the stack will fix your problem.

> > > > hence your problem.
> > > 
> > > Sure it is. Just like any bug or missing feature in any application. But
> > I'v always hoped that
> > > SQLite won't constraint me. It's sad the it does, and horrible that it
> > crashes instead returning
> > > an error. 
> > 
> > You should ask for your money back.
> 
> Yes it's free. But I think that no user (or author) of software like when 
> software crashes.
> Unfortunatelly SQLite does.

And you're also free to patch it.

> Last time wasn't sent to the mailing list so once more:
> Does anyone know other places apart from unions where deep recursion may be 
> used?

Because you've asked so nicely, it seems that SQLite already handles this:

#if SQLITE_MAX_EXPR_DEPTH>0
/* The following three functions, heightOfExpr(), heightOfExprList()
** and heightOfSelect(), are used to determine the maximum height
** of any expression tree referenced by the structure passed as the
** first argument.
**
** If this maximum height is greater than the current value pointed
** to by pnHeight, the second parameter, then set *pnHeight to that
** value.
*/
static void heightOfExpr(Expr *p, int *pnHeight){
  if( p ){
if( p->nHeight>*pnHeight ){
  *pnHeight = p->nHeight;
}
  }
}
static void heightOfExprList(ExprList *p, int *pnHeight){
  if( p ){
int i;
for(i=0; inExpr; i++){
  heightOfExpr(p->a[i].pExpr, pnHeight);
}
  }
}
static void heightOfSelect(Select *p, int *pnHeight){
  if( p ){
heightOfExpr(p->pWhere, pnHeight);
heightOfExpr(p->pHaving, pnHeight);
heightOfExpr(p->pLimit, pnHeight);
heightOfExpr(p->pOffset, pnHeight);
heightOfExprList(p->pEList, pnHeight);
heightOfExprList(p->pGroupBy, pnHeight);
heightOfExprList(p->pOrderBy, pnHeight);
heightOfSelect(p->pPrior, pnHeight);
  }
}


/*
** Set the Expr.nHeight variable in the structure passed as an
** argument. An expression with no children, Expr.pList or
** Expr.pSelect member has a height of 1. Any other expression
** has a height equal to the maximum height of any other
** referenced Expr plus one.
*/
void sqlite3ExprSetHeight(Expr *p){
  int nHeight = 0;
  heightOfExpr(p->pLeft, );
  heightOfExpr(p->pRight, );
  heightOfExprList(p->pList, );
  heightOfSelect(p->pSelect, );
  p->nHeight = nHeight + 1;
}

/*
** Return the maximum height of any expression tree referenced
** by the select statement passed as an argument.
*/
int sqlite3SelectExprHeight(Select *p){
  int nHeight = 0;
  heightOfSelect(p, );
  return nHeight;
}
#endif



 

Food fight? Enjoy some healthy debate 
in the Yahoo! Answers Food & Drink Q
http://answers.yahoo.com/dir/?link=list=396545367

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Stack usage

2007-06-05 Thread Nuno Lucas

On 05 Jun 2007 16:40:32 +0200, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:

Joe Wilson napisał(a):
> At least theres a known workaround, so no problem.

Workaround is not a solution.


For an embedded (and lite) SQL engine like SQLite, you have to bear in
mind some features will never be implemented, but you can always open
a ticket and "lobby" for that feature to be implemented.


> You should ask for your money back.

Yes it's free. But I think that no user (or author) of software like when 
software crashes. Unfortunatelly SQLite does.


I would agree with you that it is a bug if the the library crashes,
but I don't think there is any portable way for sqlite to know the
stack size (or available stack bytes), so it's that kind of error
SQLite simply can't do nothing about. To tell the truth, I don't know
of any easy non-portable way of doing it either in Windows or Linux.

You should file a bug report, anyway, because the developers know
better than me.
If for nothing else, it could be used to update the documentation
about unions to warn about this case.


Last time wasn't sent to the mailing list so once more:
Does anyone know other places apart from unions where deep recursion may be 
used?


I believe your question is more for other places where you can avoid
the deep recursion (as the deep recursion will always lead to the same
problem).


Best regards,
~Nuno Lucas


--
Kasia Cichopek eksponuje biust
>>> http://link.interia.pl/f1a6f


Re: [sqlite] Stack usage

2007-06-05 Thread BardzoTajneKonto
Joe Wilson napisał(a):
> Please respond to the mailing list in the future.

Sorry. Different client. I didn't notice the adress.

> At least theres a known workaround, so no problem.

Workaround is not a solution.

> 
> > > hence your problem.
> > 
> > Sure it is. Just like any bug or missing feature in any application. But
> Iv always hoped that
> > SQLite wont constraint me. Its sad the it does, and horrible 
> > that it
> crashes instead returning
> > an error. 
> 
> You should ask for your money back.

Yes it's free. But I think that no user (or author) of software like when 
software crashes. Unfortunatelly SQLite does.

Last time wasn't sent to the mailing list so once more:
Does anyone know other places apart from unions where deep recursion may be 
used?


--
Kasia Cichopek eksponuje biust
>>> http://link.interia.pl/f1a6f


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Stack usage

2007-06-05 Thread Joe Wilson
Such a statement would never be issued on a low memory device.

This is an exceptional case involving a select with 2000 
unions - I would not worry about it.

--- [EMAIL PROTECTED] wrote:
> This is very worrying since it means that the statement cannot be compiled on 
> a
> low memory device.
> I am new to Sqlite, but I would guess that a precompiled query could be used,
> where memory is low
> and I also suppose that variable values could be bound to that precompiled
> query.


 

TV dinner still cooling? 
Check out "Tonight's Picks" on Yahoo! TV.
http://tv.yahoo.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Stack usage

2007-06-05 Thread Jonas Sandman

Is it wise to have a database with 2000 columns? Wouldn't it be better to
split this into several tables and query them separately?

Jonas

On 6/5/07, [EMAIL PROTECTED] <
[EMAIL PROTECTED]> wrote:





This is very worrying since it means that the statement cannot be compiled
on a
low memory device.
I am new to Sqlite, but I would guess that a precompiled query could be
used,
where memory is low
and I also suppose that variable values could be bound to that precompiled
query.

Clive








Joe Wilson <[EMAIL PROTECTED]> on 05/06/2007 14:33:42

Please respond to sqlite-users@sqlite.org

To:   sqlite-users@sqlite.org
cc:(bcc: clive/Emultek)

Subject:  Re: [sqlite] Stack usage



--- [EMAIL PROTECTED] wrote:
> I'v read in change log that some stack allocted memory were moved to the
heap,
but I think that
> there is still to much allocated memory on the stack.
> After creating a table with 2000 columns, jdbc driver created a query
that run
out of stack.
> Default java's stack limit is low, but it wasn't hard to create simillar
query
that crashed C
> application with default stack limit. And the fact that it crashed
instead
repoting an error
> isn't really nice.
> The query created by the driver looks like that:
>
> select null as TABLE_CAT, null as TABLE_SCHEM, 'test' as TABLE_NAME, cn
as
COLUMN_NAME, dt as
> DATA_TYPE, tn as TYPE_NAME, 20 as COLUMN_SIZE, 20 as
BUFFER_LENGTH, 10   as
> DECIMAL_DIGITS, 10   as NUM_PREC_RADIX, colnullable as NULLABLE, null as
REMARKS, null as
> COLUMN_DEF, 0as SQL_DATA_TYPE, 0as SQL_DATETIME_SUB, 20
as
CHAR_OCTET_LENGTH,
> ordpos as ORDINAL_POSITION, (case colnullable when 0 then 'N' when 1
then 'Y'
else '' end)as
> IS_NULLABLE, null as SCOPE_CATLOG, null as SCOPE_SCHEMA, null as
SCOPE_TABLE,
null as
> SOURCE_DATA_TYPE from (select 0 as ordpos, 1 as colnullable, 'col1' as
cn,
'double' as tn, 8 as
> dt union all select 1 as ordpos, 1 as colnullable, 'col2' as cn,
'double' as
tn, 8 as dt union
> all select 2 as ordpos, 1 as colnullable, 'col3' as cn, 'double' as tn,
8 as
dt union all select
> 3 as ordpos, 1 as colnullable, 'col4' as cn, 'double' as tn, 8 as dt
union all
select 4 as
> ordpos, 1 as colnullable, 'lastcol' as cn, 'double' as tn, 8 as dt);
>
> but uses more columns.

SQLite uses recursion to generate code for SELECT UNION chains.

** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
**
** This statement is parsed up as follows:
**
** SELECT c FROM t3
**  |
**  `->  SELECT b FROM t2
**|
**`-->  SELECT a FROM t1

So for your example you will have a stack of 2000 nested calls
of sqlite3Select's (via multiSelect).

Using gcc -O2 on x86 I see that each level's stack is 480 bytes.
So for 2000 unions in a select, SQLite will consume at least
480 bytes/frame x 2000 frames = 960,000 bytes. I'm guessing that Java
limits the stack to 1M per thread in your case, hence your problem.

It is not easy to adapt SQLite's code to not use recursion for
code generation, although with enough time and effort anything is
possible. Conceivably, the processing of compound queries could
be turned into a for loop.

The SQLite authors have recently added a number of maximum limits
via #defines. Perhaps the SQLITE_MAX_EXPR_DEPTH limit could also
be applied here:

  /* If there is are a sequence of queries, do the earlier ones first.
  */
  if( p->pPrior ){
if( p->pRightmost==0 ){
  Select *pLoop;
  for(pLoop=p; pLoop; pLoop=pLoop->pPrior){
pLoop->pRightmost = p;
  }
}
return multiSelect(pParse, p, eDest, iParm, aff);
  }








It's here! Your new message!
Get new email alerts with the free Yahoo! Toolbar.
http://tools.search.yahoo.com/toolbar/features/mail/


-
To unsubscribe, send email to [EMAIL PROTECTED]

-









-
To unsubscribe, send email to [EMAIL PROTECTED]

-




Re: [sqlite] Stack usage

2007-06-05 Thread Clive . Bluston



This is very worrying since it means that the statement cannot be compiled on a
low memory device.
I am new to Sqlite, but I would guess that a precompiled query could be used,
where memory is low
and I also suppose that variable values could be bound to that precompiled
query.

Clive








Joe Wilson <[EMAIL PROTECTED]> on 05/06/2007 14:33:42

Please respond to sqlite-users@sqlite.org

To:   sqlite-users@sqlite.org
cc:(bcc: clive/Emultek)

Subject:  Re: [sqlite] Stack usage



--- [EMAIL PROTECTED] wrote:
> I'v read in change log that some stack allocted memory were moved to the heap,
but I think that
> there is still to much allocated memory on the stack.
> After creating a table with 2000 columns, jdbc driver created a query that run
out of stack.
> Default java's stack limit is low, but it wasn't hard to create simillar query
that crashed C
> application with default stack limit. And the fact that it crashed instead
repoting an error
> isn't really nice.
> The query created by the driver looks like that:
>
> select null as TABLE_CAT, null as TABLE_SCHEM, 'test' as TABLE_NAME, cn as
COLUMN_NAME, dt as
> DATA_TYPE, tn as TYPE_NAME, 20 as COLUMN_SIZE, 20 as
BUFFER_LENGTH, 10   as
> DECIMAL_DIGITS, 10   as NUM_PREC_RADIX, colnullable as NULLABLE, null as
REMARKS, null as
> COLUMN_DEF, 0as SQL_DATA_TYPE, 0as SQL_DATETIME_SUB, 20 as
CHAR_OCTET_LENGTH,
> ordpos as ORDINAL_POSITION, (case colnullable when 0 then 'N' when 1 then 'Y'
else '' end)as
> IS_NULLABLE, null as SCOPE_CATLOG, null as SCOPE_SCHEMA, null as SCOPE_TABLE,
null as
> SOURCE_DATA_TYPE from (select 0 as ordpos, 1 as colnullable, 'col1' as cn,
'double' as tn, 8 as
> dt union all select 1 as ordpos, 1 as colnullable, 'col2' as cn, 'double' as
tn, 8 as dt union
> all select 2 as ordpos, 1 as colnullable, 'col3' as cn, 'double' as tn, 8 as
dt union all select
> 3 as ordpos, 1 as colnullable, 'col4' as cn, 'double' as tn, 8 as dt union all
select 4 as
> ordpos, 1 as colnullable, 'lastcol' as cn, 'double' as tn, 8 as dt);
>
> but uses more columns.

SQLite uses recursion to generate code for SELECT UNION chains.

** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
**
** This statement is parsed up as follows:
**
** SELECT c FROM t3
**  |
**  `->  SELECT b FROM t2
**|
**`-->  SELECT a FROM t1

So for your example you will have a stack of 2000 nested calls
of sqlite3Select's (via multiSelect).

Using gcc -O2 on x86 I see that each level's stack is 480 bytes.
So for 2000 unions in a select, SQLite will consume at least
480 bytes/frame x 2000 frames = 960,000 bytes. I'm guessing that Java
limits the stack to 1M per thread in your case, hence your problem.

It is not easy to adapt SQLite's code to not use recursion for
code generation, although with enough time and effort anything is
possible. Conceivably, the processing of compound queries could
be turned into a for loop.

The SQLite authors have recently added a number of maximum limits
via #defines. Perhaps the SQLITE_MAX_EXPR_DEPTH limit could also
be applied here:

  /* If there is are a sequence of queries, do the earlier ones first.
  */
  if( p->pPrior ){
if( p->pRightmost==0 ){
  Select *pLoop;
  for(pLoop=p; pLoop; pLoop=pLoop->pPrior){
pLoop->pRightmost = p;
  }
}
return multiSelect(pParse, p, eDest, iParm, aff);
  }







It's here! Your new message!
Get new email alerts with the free Yahoo! Toolbar.
http://tools.search.yahoo.com/toolbar/features/mail/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-








-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: Re: [sqlite] Stack usage

2007-06-05 Thread Nuno Lucas

On 05 Jun 2007 14:59:40 +0200, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:

> Are you sure it is sqlite that used the stack and not the jdbc driver
> (or your application)?

yes

> What happens if you run that query from the sqlite shell?

That query I pasted works. Bigger doesn't.


Ok, I confused 2000 columns with 2000 rows.

With that many columns I'm not surprised, but also think you "get what
you deserve".

It seems the stack is used in a critical path, I mean, not using the
stack would hurt performance (in terms of cpu cache) in the general
case so, in my opinion, your case doesn't "deserve" to be fixed.

You can make the generator create a temporary table, insert the data
on it, make the select and then drop the table, even if that would
involve more coding (at least to handle the final table drop after
geting the result).


Regards,
~Nuno Lucas

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Re: baffled by dates

2007-06-05 Thread Chris Fonnesbeck

Thanks to everyone for their help and advice. I think I get it now.

Cheers,
cf

On 6/4/07, Chris Fonnesbeck <[EMAIL PROTECTED]> wrote:


I'm at a complete loss about how to work with dates in SQLite. The
documentation doesnt seem to be helping me. I have a table with some date
fields, in the proper -mm-dd format:

sqlite> select First_Capture from PIT_manatees limit 5;
1997-6-17
1998-5-6
1997-6-24
1998-5-6
2000-3-15

however, it does not respond properly to comparisons to dates:

sqlite> select First_Capture from PIT_manatees where
First_Capture<2000-1-1 limit 5;
sqlite>

this doesnt work either:

sqlite> select First_Capture from PIT_manatees where
First_Capture

note that these columns are in the 'date' format.

Yet more strangeness:


sqlite> select date(First_Capture) from PIT_manatees limit 10;







1999-10-13


sqlite>

Not sure what that is all about -- all the results are blank except one,
ewven though every record is in exactly the same format.

I'm totally perplexed by date handling in SQLite, so any help is most
appreciated.

Thanks,
--
Christopher J. Fonnesbeck

Population Ecologist, Marine Mammal Section
Fish & Wildlife Research Institute (FWC)
St. Petersburg, FL

Adjunct Assistant Professor
Warnell School of Forestry and Natural Resources
University of Georgia
Athens, GA

T: 727.235.5570
E: Chris dot Fonnesbeck at MyFWC dot com





--
Christopher J. Fonnesbeck

Population Ecologist, Marine Mammal Section
Fish & Wildlife Research Institute (FWC)
St. Petersburg, FL

Adjunct Assistant Professor
Warnell School of Forestry and Natural Resources
University of Georgia
Athens, GA

T: 727.235.5570
E: Chris dot Fonnesbeck at MyFWC dot com


Re: [sqlite] Sorted index

2007-06-05 Thread John Stanton

B-Tree indices are in sorted sequence.  Just raise an index on the column.

[EMAIL PROTECTED] wrote:



I would like to maintain a sorted list of ~3000 entries.
I know that I can create a table and the SELECT from it with the ORDER BY clause
in order to sort it.
However I do not want the overhead of doing this after adding a new entry.
It would be good if I could create an index that was sorted, and then when I
add a new entry to the table it would automatically be inserted in the index at
the correct position.
Is this possibe?
If not, can anyone suggest any other solution, baring in mind that RAM memory
needs to be kep to a minimum?

Thanks
Clive



-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Stack usage

2007-06-05 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> I'v read in change log that some stack allocted memory were moved to the 
> heap, but I think that
> there is still to much allocated memory on the stack.
> After creating a table with 2000 columns, jdbc driver created a query that 
> run out of stack.
> Default java's stack limit is low, but it wasn't hard to create simillar 
> query that crashed C
> application with default stack limit. And the fact that it crashed instead 
> repoting an error
> isn't really nice.
> The query created by the driver looks like that:
> 
> select null as TABLE_CAT, null as TABLE_SCHEM, 'test' as TABLE_NAME, cn as 
> COLUMN_NAME, dt as
> DATA_TYPE, tn as TYPE_NAME, 20 as COLUMN_SIZE, 20 as 
> BUFFER_LENGTH, 10   as
> DECIMAL_DIGITS, 10   as NUM_PREC_RADIX, colnullable as NULLABLE, null as 
> REMARKS, null as
> COLUMN_DEF, 0as SQL_DATA_TYPE, 0as SQL_DATETIME_SUB, 20 as 
> CHAR_OCTET_LENGTH,
> ordpos as ORDINAL_POSITION, (case colnullable when 0 then 'N' when 1 then 'Y' 
> else '' end)as
> IS_NULLABLE, null as SCOPE_CATLOG, null as SCOPE_SCHEMA, null as SCOPE_TABLE, 
> null as
> SOURCE_DATA_TYPE from (select 0 as ordpos, 1 as colnullable, 'col1' as cn, 
> 'double' as tn, 8 as
> dt union all select 1 as ordpos, 1 as colnullable, 'col2' as cn, 'double' as 
> tn, 8 as dt union
> all select 2 as ordpos, 1 as colnullable, 'col3' as cn, 'double' as tn, 8 as 
> dt union all select
> 3 as ordpos, 1 as colnullable, 'col4' as cn, 'double' as tn, 8 as dt union 
> all select 4 as
> ordpos, 1 as colnullable, 'lastcol' as cn, 'double' as tn, 8 as dt);
> 
> but uses more columns.

SQLite uses recursion to generate code for SELECT UNION chains.

** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
**
** This statement is parsed up as follows:
** 
** SELECT c FROM t3
**  |
**  `->  SELECT b FROM t2
**|
**`-->  SELECT a FROM t1

So for your example you will have a stack of 2000 nested calls
of sqlite3Select's (via multiSelect).

Using gcc -O2 on x86 I see that each level's stack is 480 bytes.
So for 2000 unions in a select, SQLite will consume at least 
480 bytes/frame x 2000 frames = 960,000 bytes. I'm guessing that Java
limits the stack to 1M per thread in your case, hence your problem.

It is not easy to adapt SQLite's code to not use recursion for 
code generation, although with enough time and effort anything is
possible. Conceivably, the processing of compound queries could 
be turned into a for loop.

The SQLite authors have recently added a number of maximum limits 
via #defines. Perhaps the SQLITE_MAX_EXPR_DEPTH limit could also 
be applied here:

  /* If there is are a sequence of queries, do the earlier ones first.
  */
  if( p->pPrior ){
if( p->pRightmost==0 ){
  Select *pLoop;
  for(pLoop=p; pLoop; pLoop=pLoop->pPrior){
pLoop->pRightmost = p;
  }
}
return multiSelect(pParse, p, eDest, iParm, aff);
  }




 

It's here! Your new message!  
Get new email alerts with the free Yahoo! Toolbar.
http://tools.search.yahoo.com/toolbar/features/mail/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Stack usage

2007-06-05 Thread Nuno Lucas

On 05 Jun 2007 13:35:33 +0200, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:

I'v read in change log that some stack allocted memory were moved to the heap, 
but I think that there is still to much allocated memory on the stack.
After creating a table with 2000 columns, jdbc driver created a query that run 
out of stack. Default java's stack limit is low, but it wasn't hard to create 
simillar query that crashed C application with default stack limit. And the 
fact that it crashed instead repoting an error isn't really nice.
The query created by the driver looks like that:

select null as TABLE_CAT, null as TABLE_SCHEM, 'test' as TABLE_NAME, cn as 
COLUMN_NAME, dt as DATA_TYPE, tn as TYPE_NAME, 20 as COLUMN_SIZE, 
20 as BUFFER_LENGTH, 10   as DECIMAL_DIGITS, 10   as NUM_PREC_RADIX, 
colnullable as NULLABLE, null as REMARKS, null as COLUMN_DEF, 0as 
SQL_DATA_TYPE, 0as SQL_DATETIME_SUB, 20 as CHAR_OCTET_LENGTH, 
ordpos as ORDINAL_POSITION, (case colnullable when 0 then 'N' when 1 then 'Y' 
else '' end)as IS_NULLABLE, null as SCOPE_CATLOG, null as SCOPE_SCHEMA, 
null as SCOPE_TABLE, null as SOURCE_DATA_TYPE from (select 0 as ordpos, 1 as 
colnullable, 'col1' as cn, 'double' as tn, 8 as dt union all select 1 as 
ordpos, 1 as colnullable, 'col2' as cn, 'double' as tn, 8 as dt union all 
select 2 as ordpos, 1 as colnullable, 'col3' as cn, 'double' as tn, 8 as dt 
union all select 3 as ordpos, 1 as colnullable, 'col4' as cn, 'double' as tn, 8 
as dt union all select 4 as ordpos, 1 as colnullable, 'lastcol' as cn, 'double' 
as tn, 8 as dt);

but uses more columns.


Are you sure it is sqlite that used the stack and not the jdbc driver
(or your application)?
What happens if you run that query from the sqlite shell?

The reason I ask is because I used sqlite on WinCE (but not currently)
and only 1 time I had problem with stack usage (a version that made
sqlite use the stack instead of the heap, but got corrected right away
in the next version).

I'm sure a lot of sqlite users in the embedded field would be
"ranting" about sqlite stack usage if that was true.


Regards,
~Nuno Lucas

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Re: Sorted index

2007-06-05 Thread Igor Tandetnik

Clive.Bluston-cPKiotmf5pXN/[EMAIL PROTECTED] wrote:

I would like to maintain a sorted list of ~3000 entries.
I know that I can create a table and the SELECT from it with the
ORDER BY clause 
in order to sort it.

However I do not want the overhead of doing this after adding a new
entry. 
It would be good if I could create an index that was sorted


So why don't you? See CREATE INDEX

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Stack usage

2007-06-05 Thread BardzoTajneKonto
I'v read in change log that some stack allocted memory were moved to the heap, 
but I think that there is still to much allocated memory on the stack.
After creating a table with 2000 columns, jdbc driver created a query that run 
out of stack. Default java's stack limit is low, but it wasn't hard to create 
simillar query that crashed C application with default stack limit. And the 
fact that it crashed instead repoting an error isn't really nice.
The query created by the driver looks like that:

select null as TABLE_CAT, null as TABLE_SCHEM, 'test' as TABLE_NAME, cn as 
COLUMN_NAME, dt as DATA_TYPE, tn as TYPE_NAME, 20 as COLUMN_SIZE, 
20 as BUFFER_LENGTH, 10   as DECIMAL_DIGITS, 10   as NUM_PREC_RADIX, 
colnullable as NULLABLE, null as REMARKS, null as COLUMN_DEF, 0as 
SQL_DATA_TYPE, 0as SQL_DATETIME_SUB, 20 as CHAR_OCTET_LENGTH, 
ordpos as ORDINAL_POSITION, (case colnullable when 0 then 'N' when 1 then 'Y' 
else '' end)as IS_NULLABLE, null as SCOPE_CATLOG, null as SCOPE_SCHEMA, 
null as SCOPE_TABLE, null as SOURCE_DATA_TYPE from (select 0 as ordpos, 1 as 
colnullable, 'col1' as cn, 'double' as tn, 8 as dt union all select 1 as 
ordpos, 1 as colnullable, 'col2' as cn, 'double' as tn, 8 as dt union all 
select 2 as ordpos, 1 as colnullable, 'col3' as cn, 'double' as tn, 8 as dt 
union all select 3 as ordpos, 1 as colnullable, 'col4' as cn, 'double' as tn, 8 
as dt union all select 4 as ordpos, 1 as colnullable, 'lastcol' as cn, 'double' 
as tn, 8 as dt);

but uses more columns.


--
Cywilizacje powstaja z gliny i zelaza. Travian
>>> http://link.interia.pl/f1a7e


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Copying the content of a table to another table

2007-06-05 Thread Kirrthana M
Thanks i ll check it out..

-Original Message-
From: Lloyd [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 05, 2007 1:37 PM
To: Kirrthana M
Cc: sqlite-users@sqlite.org
Subject: Re: [sqlite] Copying the content of a table to another table


 create table x(c1 number,c2 number);
 create table y(v1 number,v2 number,v3 number,v4 number);
 insert into y (v1,v2) select c1,c2 from x;

or better you can use the alter table command on your old table (here x)

 alter table x add v3 number;
 alter table x add v4 number;

the following way doesn't seem to work for me

alter table x add (v3 number,v4 number);

Regards,
 Lloyd

On Tue, 2007-06-05 at 11:25 +0530, Kirrthana M wrote:
> Hi all,
> I have created a table with two columns c1 and c2 and inserted
some
> values into,in future i may include some additional columns c3,c4. to
> the same table.In tat case i should not loose my data in the current
> table,so i should be able to copy the content of the current table(values
of
> c1 and c2) to the new table and start working with the new table and
delete
> the existing one,could anybody give me a suggestion for doing this.
>
> Regards
> Kirrthana


__
Scanned and protected by Email scanner


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] baffled by dates

2007-06-05 Thread P Kishor

On 6/5/07, John Stanton <[EMAIL PROTECTED]> wrote:

OK Sqlite doesn't have a date type, period.  Just as it doesn't have
other types, but don't let it it stop you from using dates very
successfully with Sqlite by defining a date type and using the
functionally built into the product.


Indeed. And that is what my post suggested. SQLite doesn't have a date
type. The only types are REAL, TEXT, INTEGER, BLOB, and NULL. But,
"don't let (that) stop you from using dates very successfully with
Sqlite by defining a date type and using the functionality built into
the product."

..


Joe Wilson wrote:
> --- John Stanton <[EMAIL PROTECTED]> wrote:
>
>>Sqlite does have a date format, it is physically a 64 bit floating point
>>number.  There are functions to transform in and out of that format to
>>present dates as required by the user.  The Sqlite date format uses a
>>magib epoch which matches all of the major internaional date systems.
>
>
> SQLite does not have a date type. Period.
>
> What you're describing is not a true DATE type, but your own
> programming convention when dealing with dates. Your programming
> convention relies on functions, date strings and epoch-based floating
> point numbers.
>
> If you were correct and SQLite currently supported a DATE type,
> then why was this Proposed Incompatible Changes entry added to
> the SQLite wiki by DRH?
>
>  "Support A DATE Type"
>  http://www.sqlite.org/cvstrac/wiki?p=ProposedIncompatibleChanges
>
> Because rehashing the old arguments is pointless, here's the old thread:
>
>  http://www.mail-archive.com/sqlite-users@sqlite.org/msg20589.html
>
>
>>P Kishor wrote:
>>
>>>There is no "DATE" format in SQLite. Dates are stored as strings. The
>>>only formats SQLite knows and understands are TEXT, REAL, INTEGER,
>>>BLOB, and NULL (see the link on datatypes). On the other hand, there
>>>are built-in functions that can act on your date strings and convert
>>>them back and forth, manipulate them, etc. Once again, see the link on
>>>working with dates on the SQLite wiki.
>
>
>
>
>
> 

> We won't tell. Get more on shows you hate to love
> (and love to hate): Yahoo! TV's Guilty Pleasures list.
> http://tv.yahoo.com/collections/265
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-





--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Sorted index

2007-06-05 Thread Clive . Bluston



I would like to maintain a sorted list of ~3000 entries.
I know that I can create a table and the SELECT from it with the ORDER BY clause
in order to sort it.
However I do not want the overhead of doing this after adding a new entry.
It would be good if I could create an index that was sorted, and then when I
add a new entry to the table it would automatically be inserted in the index at
the correct position.
Is this possibe?
If not, can anyone suggest any other solution, baring in mind that RAM memory
needs to be kep to a minimum?

Thanks
Clive



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Copying the content of a table to another table

2007-06-05 Thread Lloyd
 create table x(c1 number,c2 number);
 create table y(v1 number,v2 number,v3 number,v4 number);
 insert into y (v1,v2) select c1,c2 from x;

or better you can use the alter table command on your old table (here x)

 alter table x add v3 number;
 alter table x add v4 number;

the following way doesn't seem to work for me

alter table x add (v3 number,v4 number);

Regards,
 Lloyd

On Tue, 2007-06-05 at 11:25 +0530, Kirrthana M wrote:
> Hi all,
> I have created a table with two columns c1 and c2 and inserted some
> values into,in future i may include some additional columns c3,c4. to
> the same table.In tat case i should not loose my data in the current
> table,so i should be able to copy the content of the current table(values of
> c1 and c2) to the new table and start working with the new table and delete
> the existing one,could anybody give me a suggestion for doing this.
> 
> Regards
> Kirrthana


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Re: How to retrieve results in a array?

2007-06-05 Thread B V, Phanisekhar
You can use sqlite3_get_table. This will internally step through each
row.

Regards,
Phani

-Original Message-
From: Dave Furey [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 23, 2007 4:52 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Re: How to retrieve results in a array?

Ok, thanks for that quick response!

ppcinfo

-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 22, 2007 4:18 PM
To: SQLite
Subject: [sqlite] Re: How to retrieve results in a array?

Dave Furey <[EMAIL PROTECTED]> wrote:
> I've got a C routine that queries for integer data, and I'm currently
> stepping through each row to fill an integer array. Is there a more
> convenient way of doing this? For example, is there a special sqlite3
> call I can make that will fill up an integer array directly with me
> having to step through all the returned rows?

No.

Igor Tandetnik



-
To unsubscribe, send email to [EMAIL PROTECTED]


-



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-