Re: [sqlite] encrypted file

[Lloyd]

Thanks for your informative reply. 

I would like to know, whether I can open a database which is created
using the sqlite API from a program, using the sqlite command line
client?

[I tried it using sqlite   and it shown the
following error-

Unable to open database "/root/userdb": file is encrypted or is not a
database .

How can I open the database using sqlite command line? ]

Thank you very much.

Lloyd.




On Tue, 2006-10-24 at 11:02 +, [EMAIL PROTECTED] wrote:
> Lloyd <[EMAIL PROTECTED]> wrote:
> > Hi List,
> >  Whether the data base which we create through the program will be in
> > encrypted form? 
> 
> The public-domain version of SQLite does not encrypt the data.
> But commerical extensions that encrypt the data are available.
> See, for example, http://www.hwaci.com/sw/sqlite/prosupport.html#crypto
> 
> > 
> > If yes, how can I see the result in sqlite? (How can use select
> > statements on this database?)
> > 
> > My application creates a database and to analyze the results in the
> > database, it will be easy for me to use the interface provided by the
> > sqlite. How can I open the database in SQLite ?
> > 
> 
> When a database is encrypted you must supply the encryption key
> before reading or modifying the database.  You can do this in
> several ways.  There is a pragma:
> 
>PRAGMA key=?
> 
> If the database is being loaded using an ATTACH statement then
> you specify the key in the USING clause of the ATTACH:
> 
>ATTACH DATABASE 'encrypted.db' AS e2 USING :key
> 
> Or you can use the C/C++ API:
> 
>sqlite3_key(db, zKey);
> 
> Once the key is established, the database content is automatically
> decrypted as it is read from the disk and reencrypted as it is
> written back to the disk.
> 
> If you use the command-line client, there is a special option
> to specify the key:
> 
>sqlite3 -key 'hello' database.db
>
> 
> After that the command-line client works just like it normally
> does.
> 
> None of the above works in the public domain version of SQLite.
> You have to purchase a license for a version of SQLite that 
> supports encryption in order to use these features.
> 
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


__
Scanned and protected by Email scanner

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

Re: [sqlite] Sqlite & Matlab

[victor . camus]

Dennis,

Fine! In that case, I wonder whether it will be of some use to add the
possibility to access sqlite from Matlab on the official Sqlite web site, more
precisely at http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

It would avoid the need to search on Google...

Selon Dennis Cote <[EMAIL PROTECTED]>:

> [EMAIL PROTECTED] wrote:
> > There seems to be a possible connection between Sqlite and Matlab available
> at
> > http://sourceforge.net/project/showfiles.php?group_id=139986
> >
> > However it is not refered to on the sqlite website. Does that mean it does
> not
> > really work?
> >
> >
> >
> Victor,
>
> I took a quick look at the code there. It hasn't been updated for a year
> or so, but it seems like it should be functional as is.
>
> It should work OK for small result sets, since it loads the entire
> result set into memory. Actually it ends up with two copies in memory
> while it is converting the data from sqlite to matlab formats.
>
> It also opens and closes the database file for each query which can be
> expensive.
>
> HTH
> Dennis Cote
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>



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

[sqlite] Compiling Sqlite with Openwatcom

[victor . camus]

Hi All,

I wonder whether anyone has already compiled Sqlite with Openwatcom (a free
compiler suite available at www.openwatcom.org including a C/C++/Fortran
compiler).

Since when I tried, I got the following compiling error on line 95 of the file
sqlite3ext.h: invalid declarator i.e. corresponding to the line
  void  (*interrupt)(sqlite3*);

This is the first step to generate a library that I will use to access Sqlite
from Fortran following the instructions given by Danial at
http://danial.org/sqlite/fortran/(my next step)

Any help is welcome

Victor

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

Re: [sqlite] Compiling Sqlite with Openwatcom

[Arjen Markus]

[EMAIL PROTECTED] wrote:


Hi All,

I wonder whether anyone has already compiled Sqlite with Openwatcom (a free
compiler suite available at www.openwatcom.org including a C/C++/Fortran
compiler).

Since when I tried, I got the following compiling error on line 95 of the file
sqlite3ext.h: invalid declarator i.e. corresponding to the line
 void  (*interrupt)(sqlite3*);

This is the first step to generate a library that I will use to access Sqlite
from Fortran following the instructions given by Danial at
http://danial.org/sqlite/fortran/(my next step)

 


Hello Victor,

I know almost nothing about the Open Watcom compiler, but if you are
interested in using Sqlite from Fortran, see my flibs project on 
SourceForge:
http://flibs.sf.net - I created the interface library you will find 
there (insufficient

documentation, I am afraid, and some loose ends) after being inspired by
that same example.

Regards,

Arjen

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

Re: [sqlite] Re: Regarding sqlite3_exec

[Da Martian]

What prevents you from doing the same yourself? Just collect the
data in a memory buffer as you get it, before processing it.


Nothing but effiency as discussed. Basically, as Mr Cote has said, its
either a count(*) query or O(n) step calls. The former means two statements
have to be run, if this is a heavy statement its not that great. The latter
is best option available, because as Mr Cote points out step isnt that
heavy. The idea behind me prompting of this discussion is to get the best of
both worlds. ie. the effiency of count(*) query without the need to execute
two queries. At the end of the day its not serious as many work
arounds/solutions are available.


That doesn't seem to make sense – after all, the count can only
be returned *after* all the rows have been collected. By then you
know the count yourself anyway.


But to return all the rows just to count them requires N calls to step. If
the data set is large you only want to return a subset to start with. So you
wouldnt know the count. If you dont know the count, you cant update GUI type
things etc..

S

Re: [sqlite] Regarding sqlite3_exec

[Da Martian]

Hi

Thanks for your resposne.

In the end its not important as you point out as many options are available,
I guess I allowed myself to indulge in "idealic" fantasy for a moment :-)

S

On 10/24/06, Dennis Cote <[EMAIL PROTECTED]> wrote:


Da Martian wrote:
> Hi
>
> I understand the problem in thoery and you are saying that sqlite is
> using
> an iterative algorithm which returns data as it finds it, is this
> correct?
>
> It appears that DBs like oracle etc.. get the whole resultset into
> temporary
> memory/space and then return the query (at least for unions this appears
> true), although this is just based off assumptions based on
observations.
>
> It seems to me that the DB (ie sqlite) can determine the number of
> results
> far more effiently than a client application. The reason is, the client
> application has to call step (n) times and a column extract (n x no
> columns). While the db could just move through the results set and count
> them up without every doing any processing on the data being counted.
> Perhaps this could done as a seperate api, like preparewithcount() which
> returns the count as well. With carefull design most of the work
> needed to
> prepare the statement etc.. could avoid being repeated as would happen
> with
> a count(*) query.
>
> This is just an idea, and I am speaking simply as a developer, but one
> who
> has not looked at sqlite implentation at all.
>
Yes, sqlite iterates and returns each result row as it is located.

SQLite also has a legacy sqlite3_get_table API that will return the
entire result set in a table in ram. It can fail if there is not enough
memory to hold the result set though.

Your idea can (almost) be implemented in your application like this.

int prepare_and_count(sqlite3* db, const char* sql, int len,
sqlite3_stmt** s, const char** tail, int* count)
{
int rc = sqlite3_prepare(db, sql, len, s, tail);

*count = 0;
if (rc == SQLITE_OK) {
while (sqlite3_step(*s) == SQLITE_ROW)
++(*count);
sqlite3_reset(*s);
}

return rc;
}

This will avoid the need to prepare two queries by using the same query
twice, once to count the result rows and a second time to collect the
results. It does require N extra calls to sqlite3_step (which are very
low overhead compared to the execution of a step).

The extra calls to step are eliminated if you use a "select count(*)
" query instead. With a count query SQLite will scan through the
table as quickly as possible and count the results without stopping and
returning to the caller after each row. But this requires a second query
to be prepared.

When you look at the big picture though, optimizing the count query
isn't likely worth the effort. The count is usually only needed to
implement GUI controls like scrollbars. The time is takes to collect the
results and present them in the GUI will dominate the time it takes to
prepare and execute a second count query unless the result set is very
large. With large results the execution time of the count query
dominates, and the overhead time to prepare the query becomes
insignificant. It really doesn't take that long to prepare a count query.

HTH
Dennis Cote




-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Re: Regarding sqlite3_exec

[Martin Jenkins]

Da Martian wrote:


But to return all the rows just to count them requires N calls to
step. If the data set is large you only want to return a subset to
start with. So you wouldnt know the count. If you dont know the
count, you cant update GUI type things etc..


I haven't been following this thread closely, but isn't this exactly the 
problem that Google "solves" by returning


 "Results 1 - 10 of about 9,940,000 for sqlite. (0.11 seconds)"

for a query with a very large result set? If Google can't do it with all 
the resources they have...


Martin

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

Re: [sqlite] Re: Regarding sqlite3_exec

[Da Martian]

Yes but google doesnt us an RDMS, its all propriatary to support there speed
and huge volumes. Its anyones guess (excpet google themselves) what exactly
they do, and rumours abound, but I have done many apps which require custom
data handling to achieve some end that doesnt fit with RDBM Systems.

But yes paging and using LIMIT and OFFSET is also a solution. Again not as
efficent though, cause of all the repeated queris :-)


On 10/25/06, Martin Jenkins <[EMAIL PROTECTED]> wrote:


Da Martian wrote:

> But to return all the rows just to count them requires N calls to
> step. If the data set is large you only want to return a subset to
> start with. So you wouldnt know the count. If you dont know the
> count, you cant update GUI type things etc..

I haven't been following this thread closely, but isn't this exactly the
problem that Google "solves" by returning

  "Results 1 - 10 of about 9,940,000 for sqlite. (0.11 seconds)"

for a query with a very large result set? If Google can't do it with all
the resources they have...

Martin


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Re: Regarding sqlite3_exec

[Martin Jenkins]

Da Martian wrote:

Yes but google doesnt us an RDMS, its all propriatary to support
there speed and huge volumes.


I know, but that was my point. If they can't/don't do it (with their 
custom kit) then surely it's non-trivial?


Martin

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

[sqlite] Re: Regarding sqlite3_exec

[A. Pagaltzis]

* Da Martian <[EMAIL PROTECTED]> [2006-10-25 11:35]:
> >What prevents you from doing the same yourself? Just collect
> >the data in a memory buffer as you get it, before processing
> >it.
> 
> Nothing but effiency as discussed. Basically, as Mr Cote has
> said, its either a count(*) query or O(n) step calls. The
> former means two statements have to be run, if this is a heavy
> statement its not that great. The latter is best option
> available, because as Mr Cote points out step isnt that heavy.
> The idea behind me prompting of this discussion is to get the
> best of both worlds. ie. the effiency of count(*) query without
> the need to execute two queries. At the end of the day its not
> serious as many work arounds/solutions are available.
> 
> >That doesn't seem to make sense – after all, the count can
> >only be returned *after* all the rows have been collected. By
> >then you know the count yourself anyway.
> 
> But to return all the rows just to count them requires N calls
> to step. If the data set is large you only want to return
> a subset to start with. So you wouldnt know the count. If you
> dont know the count, you cant update GUI type things etc..

I still fail to understand the problem. Either you want the data
from the query, or you don’t. If you want it, then you run the
query once and buffer the results in memory before you process
them, so you know the count before you start processing. Or you
don’t want the data, then you use a COUNT(*) query. In either
case, it is only one query you need to run.

Regards,
-- 
Aristotle Pagaltzis // 

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

Re: [sqlite] encrypted file

[Lloyd]

Sorry for my mistake.

I was creating database using sqlite3 API and trying to open it with
sqlite command line client version 2.7

Thanks a lot.
Lloyd.

On Wed, 2006-10-25 at 11:51 +0530, Lloyd wrote:
> Thanks for your informative reply. 
> 
> I would like to know, whether I can open a database which is created
> using the sqlite API from a program, using the sqlite command line
> client?
> 
> [I tried it using sqlite   and it shown the
> following error-
> 
> Unable to open database "/root/userdb": file is encrypted or is not a
> database .
> 
> How can I open the database using sqlite command line? ]
> 
> Thank you very much.
> 
> Lloyd.
> 
> 
> 
> 
> On Tue, 2006-10-24 at 11:02 +, [EMAIL PROTECTED] wrote:
> > Lloyd <[EMAIL PROTECTED]> wrote:
> > > Hi List,
> > >  Whether the data base which we create through the program will be in
> > > encrypted form? 
> > 
> > The public-domain version of SQLite does not encrypt the data.
> > But commerical extensions that encrypt the data are available.
> > See, for example, http://www.hwaci.com/sw/sqlite/prosupport.html#crypto
> > 
> > > 
> > > If yes, how can I see the result in sqlite? (How can use select
> > > statements on this database?)
> > > 
> > > My application creates a database and to analyze the results in the
> > > database, it will be easy for me to use the interface provided by the
> > > sqlite. How can I open the database in SQLite ?
> > > 
> > 
> > When a database is encrypted you must supply the encryption key
> > before reading or modifying the database.  You can do this in
> > several ways.  There is a pragma:
> > 
> >PRAGMA key=?
> > 
> > If the database is being loaded using an ATTACH statement then
> > you specify the key in the USING clause of the ATTACH:
> > 
> >ATTACH DATABASE 'encrypted.db' AS e2 USING :key
> > 
> > Or you can use the C/C++ API:
> > 
> >sqlite3_key(db, zKey);
> > 
> > Once the key is established, the database content is automatically
> > decrypted as it is read from the disk and reencrypted as it is
> > written back to the disk.
> > 
> > If you use the command-line client, there is a special option
> > to specify the key:
> > 
> >sqlite3 -key 'hello' database.db
> >
> > 
> > After that the command-line client works just like it normally
> > does.
> > 
> > None of the above works in the public domain version of SQLite.
> > You have to purchase a license for a version of SQLite that 
> > supports encryption in order to use these features.
> > 
> > --
> > D. Richard Hipp  <[EMAIL PROTECTED]>
> > 
> > 
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> > 


__
Scanned and protected by Email scanner

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

Re: Re: [sqlite] database corrouption during power failure

[jayanth KP]

Thanks Dennis Cote. Now even though synchrounous is set to FULL. But Database 
Corrouption is happening.  Here are my observations in our sqlite 2.8.13.

1. This corrouption is not detected by the sqlite_open().

2. After the crash/power failure. I see a journal file in the same directory as 
the database file. This means the database can be rolled back. I analyed the 
journal header and checksum, it looks fine.

3. Now before the rollback if i copy the database file to a different directory 
and open using sqlite. None of the tables are seen.

4. Now if i do a sqlite_open(), the rollback of the orginal data does not 
happen. But the journal file is removed.

I am suprised how can journal be be present and still database corruption 
happen.. 

Please help me resolving this issue.

Bye
Jayanth


On Mon, 23 Oct 2006 Dennis Cote wrote :
>jayanth KP wrote:
>>But how do we read the current  synchronous value using C interface. Plz 
>> reply.
>>
>> 
>Jayanth,
>
>Try this:
>
>sqlite3_stmt* s;
>sqlite3_prepare(db, "PRAGMA SYNCHRONOUS", -1, , NULL);
>sqlite3_step(s);
>int sync = sqlite3_column_int(s, 1);
>sqlite3_finalize(s);
>
>switch (sync) {
>case 0: // off
>case 1: // normal
>case 2: // full
>}
>
>
>It simply executes the PRAGMA and collects the result.
>
>HTH
>Dennis Cote
>
>-
>To unsubscribe, send email to [EMAIL PROTECTED]
>-
>

Re: [sqlite] Compiling Sqlite with Openwatcom

[victor . camus]

Hi Arjen

Unfortunately I am still using f77 not f90...

Selon Arjen Markus <[EMAIL PROTECTED]>:

> [EMAIL PROTECTED] wrote:
>
> >Hi All,
> >
> >I wonder whether anyone has already compiled Sqlite with Openwatcom (a free
> >compiler suite available at www.openwatcom.org including a C/C++/Fortran
> >compiler).
> >
> >Since when I tried, I got the following compiling error on line 95 of the
> file
> >sqlite3ext.h: invalid declarator i.e. corresponding to the line
> >  void  (*interrupt)(sqlite3*);
> >
> >This is the first step to generate a library that I will use to access
> Sqlite
> >from Fortran following the instructions given by Danial at
> >http://danial.org/sqlite/fortran/(my next step)
> >
> >
> >
> Hello Victor,
>
> I know almost nothing about the Open Watcom compiler, but if you are
> interested in using Sqlite from Fortran, see my flibs project on
> SourceForge:
> http://flibs.sf.net - I created the interface library you will find
> there (insufficient
> documentation, I am afraid, and some loose ends) after being inspired by
> that same example.
>
> Regards,
>
> Arjen
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>



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

Re: [sqlite] Compiling Sqlite with Openwatcom

[Arjen Markus]

[EMAIL PROTECTED] wrote:


Hi Arjen

Unfortunately I am still using f77 not f90...

 


Hello Victor,

any specific reason? There are lots of (free) Fortran 90 compilers around,
and unless I am mistaken the Openwatcom compiler supports Fortran 90
(after all, the standard is more than 20 years old).

The good thing is that any FORTRAN 77 that complies to that standard
is also a valid Fortran 90 program, so if you decide to move to Fortran 90
you can do the transition in small steps.

The sample code by Danial is a good attempt to do it in FORTRAN 77,
but it is rather involved due to the limitations of that standard (computers
30 years ago were very different beasts).

Regards,

Arjen

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

Re: [sqlite] Re: Regarding sqlite3_exec

[Da Martian]

Its quiet simple:

1) If there are too many results the user will have to wait a long time
before they see anything because we will be buffering away results. The
application will appear slugish. The user would get bored. I can point you
to dozens of studies which show the correlation between response time and
productivity where humans are concerned.
2) Often users will find what they want in the first 50 results. This means
you would have wasted a lot of time brinigng back data you dont need.
However they wont always find what they want in the first 50. So the option
for more must be there. So why not use "web" like paging I hear you say.
Well because the query is heavy. To re-run it each with a different limit
and offset still requires re-running it. One of the solutions (there are
many none ideal) is to have a growing scroll bar. Ie it grows each time you
fetch a batch of results. But this like most of the solutions looks a little
tardy to a user (me being one of them). Perosnally I hate it when a scroll
bar keeps growing when you reach the bottom.

The few other approaches have been mentioned in the previos post to this
thread.

Your extremly simplistic view on this is a result of never dealing in
volumous data and result sets and quick running queries. Once you put
volumes into your thinking cap you will begin to see why you dont just read
everything into memory for the hell of it.

Think about it.


On 10/25/06, A. Pagaltzis <[EMAIL PROTECTED]> wrote:


I still fail to understand the problem. Either you want the data
from the query, or you don't. If you want it, then you run the
query once and buffer the results in memory before you process
them, so you know the count before you start processing. Or you
don't want the data, then you use a COUNT(*) query. In either
case, it is only one query you need to run.

Re: [sqlite] Re: Regarding sqlite3_exec

[Da Martian]

Your extremly simplistic view on this is a result of never dealing in

volumous data and result sets and quick running queries. Once you >put
volumes into your thinking cap you will begin to see why you dont just read
everything into memory for the hell of it.

Just to complete the above thought. If I wanted everything in memory I would
not have any need for sqlite. Standard Data Structures list BST, Hash etc..
will be far more effient for in memory use.



>

[sqlite] Re: Regarding sqlite3_exec

[A. Pagaltzis]

* Da Martian <[EMAIL PROTECTED]> [2006-10-25 14:15]:
> 1) If there are too many results the user will have to wait
> a long time before they see anything because we will be
> buffering away results. The application will appear slugish.
> The user would get bored. I can point you to dozens of studies
> which show the correlation between response time and
> productivity where humans are concerned.
> 2) Often users will find what they want in the first 50
> results. This means you would have wasted a lot of time
> brinigng back data you dont need. However they wont always find
> what they want in the first 50. So the option for more must be
> there. So why not use "web" like paging I hear you say. Well
> because the query is heavy. To re-run it each with a different
> limit and offset still requires re-running it. One of the
> solutions (there are many none ideal) is to have a growing
> scroll bar. Ie it grows each time you fetch a batch of results.
> But this like most of the solutions looks a little tardy to
> a user (me being one of them). Perosnally I hate it when
> a scroll bar keeps growing when you reach the bottom.
> 
> The few other approaches have been mentioned in the previos
> post to this thread.
> 
> Your extremly simplistic view on this is a result of never
> dealing in volumous data and result sets and quick running
> queries. Once you put volumes into your thinking cap you will
> begin to see why you dont just read everything into memory for
> the hell of it.
> 
> Think about it.

Thanks for your vote of confidence in my intelligence. Clearly,
you are smart enough to figure out a solution without assistance.

Nevermind,
-- 
Aristotle Pagaltzis // 

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

Re: [sqlite] Re: Regarding sqlite3_exec

[Da Martian]

Its was not meant as an insult, however you did set the tone with your post
(ala: Either you want the data
from the query, or you don't.). I mearly responded in kind. If you live in
glass houses dont throw stones and all that. I mean its not hard to see that
loading 20 million records into memory isnt the most effient approach to
showing a list box on the screen.


Thanks for your vote of confidence in my intelligence. Clearly,

you are smart enough to figure out a solution without assistance.

Nevermind,
--
Aristotle Pagaltzis // 


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

[sqlite] Re: Regarding sqlite3_exec

[A. Pagaltzis]

* Da Martian <[EMAIL PROTECTED]> [2006-10-25 15:05]:
> Its was not meant as an insult, however you did set the tone
> with your post (ala: Either you want the data from the query,
> or you don't.). I mearly responded in kind. If you live in
> glass houses dont throw stones and all that. I mean its not
> hard to see that loading 20 million records into memory isnt
> the most effient approach to showing a list box on the screen.

I suggested that after you said that Oracle collects results in
memory before returning them; you seemed to hint that this
wouldn’t be a problem, in which case whether you do it yourself
or the database does it for you doesn’t make a difference.

Solutions that come to mind are  a) to populate the UI from an
idle callback, where the scollbar would simply keep growing
independently from any user interaction until all the results are
fetched; or if that’s unacceptable,  b) run a separate COUNT(*)
query, since preparing a extra query is cheap, but using COUNT(*)
tells SQLite that it can forgo a lot of processing, which makes
the up-front counting query quicker.

There are other options as well. A lot depends on your
circumstances. F.ex. paged queries can be made cheaper by
selecting results into a temporary table so that you can
re-retrieve them with a much cheaper query.

Regards,
-- 
Aristotle Pagaltzis // 

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

[sqlite] hexadecimal

[Lloyd]

Hi list,
  can I insert a hexadecimal value to an integer field? if yes How can
do that?

Thanks,
  Lloyd


__
Scanned and protected by Email scanner

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

Re: [sqlite] Trouble with ALTER TABLE/ADD

[Christian Smith]

Isaac Raway uttered:


Thank, I will look at that (away from my dev machine for the day).

One other related question, are there plans to expand the functionality of
ALTER TABLE? I am working on a feature that could benefit greatly from
REMOVE/RENAME COLUMN. As it stands, I am going to have to simulate this by
using generic column names and mapping them to a list of the "actual" names.
It would be *very* nice to see these features added to sqlite before I
finish this feature, but I imagine this has been requested before...



A better solution would be to transfer the contents of the table being 
updated to a temporary table, then recreate the original tables sans the 
surplus columnn:


  BEGIN;
  CREATE TABLE temp_topic AS SELECT  FROM topic;
  DROP TABLE topic;
  CREATE TABLE topic ...;
  INSERT INTO topic SELECT * FROM temp_topic;
  DROP TABLE temp_topic;
  COMMIT;

It may not be quick for large tables, but how often are you going to be 
updating the table definition? If often, then you probably have a more 
fundamental problem on your hands.


If your data format is by definition user defined, then you might be best 
off with an  table instead, and use views to map 
instances into a virtual table.





Isaac




Christian




On 10/23/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


"Isaac Raway" <[EMAIL PROTECTED]> wrote:
>
> ALTER TABLE topic ADD COLUMN type_id integer;
>
> This works fine when I run it on the sqlite3 command line, but fails in
the
> Delphi units. Any thoughts? Has anyone ahd trouble running ALTER TABLE
from
> the Delphi bindings?
>

Perhaps the delphi code is statically linked against an
older version of SQLite.  ADD COLUMN was added in version
3.2.0.  You can find what version delphi uses by executing

   SELECT sqlite_version();

--
D. Richard Hipp  <[EMAIL PROTECTED]>




-
To unsubscribe, send email to [EMAIL PROTECTED]


-








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

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

Re: [sqlite] any data access and retrieval engine?

[Christian Smith]

Sarah uttered:


Hi, John Stanton

I really really appreciate your warm help.
That's great if you can send me the codes of B tree and B+ tree.
Many thanks in advance.

My requirements for data access are as follows:
-all the data are stored in non-volatile memory instead of volatile memory
-the footprint of the DARE should be better less than 100KB
-when executing, the memory occupation should be better less than 20KB
-no need for relational access, just key-value retrieval is ok
-all the create, insert, update work can be done outside, however, pretty fast 
retrieval is needed

If there is some open-source DARE(as excellent as SQLite) suitable for my 
platform, that will be great.
Orelse, I would try to write a simple one.



There is, of course, the BTree code in SQLite itself. It is small and 
fast. btree.h is pretty self documenting.


Not to knock John's work, mind:)




Sarah


- Original Message -
From: "John Stanton" <[EMAIL PROTECTED]>
To: 
Sent: Monday, October 23, 2006 5:54 AM
Subject: Re: [sqlite] any data access and retrieval engine?



Clay Dowling wrote:

Sarah wrote:


Hi,all

First of all, I want to thank all the guys on this mailing list for their warm 
help.

After 1 more month of work, I finally make SQLite work on my embedded 
environment. SQLite is really great! Many thanks,Drh.

But, due to the extremely heavy hardware constraints, I have to give up SQLite 
finally.

So I'm trying to find a much simpler data access and retrieval engine.

Could anyone give me some help on this issue?(some guidance on how to make a 
DARE or is there any open-source one available?)

thanks in advance.



The Berkeley DB engine and it's related engines might be suitable for
your situation.  They don't give relational access, but they do give
fast key=>value retreival and that might be suitable.  The SleepyCat DB
engine from SleepyCat Software is probably the best, but for a
commercial application the licensing fees mean that you have to be well
funded and expect a good return on the product.

Clay Dowling

Berkely DB is still quite bloated.  What do you require for data access?
For an embedded system you might find something which matches your
needs very well and has a tiny footprint.

I can give you some B* Tree code which is suitable for a high
performance simple and lightweight embedded application or some AVL tree
code which would suit a simpler smaller scale memory resident embedded
data access application.  You would have to adapt it to your
application, but could expect to get your database access in 20K or less
of executable image.  Of course you have no SQL.

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



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

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

Re: [sqlite] Re: Regarding sqlite3_exec

[Nuno Lucas]

On 10/25/06, Da Martian <[EMAIL PROTECTED]> wrote:
[...]

But to return all the rows just to count them requires N calls to step. If
the data set is large you only want to return a subset to start with. So you
wouldnt know the count. If you dont know the count, you cant update GUI type
things etc..


There is another alternative if you don't mind to have the overhead of
having an automatic row count (which sqlite avoids by design). It's by
having a trigger that will update the table row count on each
insert/delete.

I'm not good with triggers, but this was already mentioned on the list
and a quick google showed this sample SQL code here:
http://www.northcode.com/forums/archive/index.php?t-6852.html



CREATE TABLE rowcount ( name TEXT, rows INTEGER);
insert into rowcount VALUES ('myTable',0);
UPDATE rowcount SET rows = (SELECT count(myNum) from myTable) WHERE
name = 'myTable';

CREATE TRIGGER incrows AFTER INSERT ON myTable
BEGIN
UPDATE rowcount SET rows = rows+1 WHERE name = 'myTable';
END;

CREATE TRIGGER decrows AFTER DELETE ON myTable
BEGIN
UPDATE rowcount SET rows = rows-1 WHERE name = 'myTable';
END;



After this you can easily access your row count with a

SELECT rows FROM rowcount WHERE name = 'myTable';


Hope this helps,
~Nuno Lucas

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

Re: [sqlite] Re: Regarding sqlite3_exec

[Da Martian]

Hi

All these options are good, and the discussion was interesting. I mearly
wanted to see what peoples thoughts on the sqlite providing this were. There
are many ways to skin a cat as they say. Since this is probably drifting off
topic, I suggest we discontinue the thread.

Thanks for your cander, discussing is whats its all about.

Stephen


On 10/25/06, A. Pagaltzis <[EMAIL PROTECTED]> wrote:


* Da Martian <[EMAIL PROTECTED]> [2006-10-25 15:05]:
> Its was not meant as an insult, however you did set the tone
> with your post (ala: Either you want the data from the query,
> or you don't.). I mearly responded in kind. If you live in
> glass houses dont throw stones and all that. I mean its not
> hard to see that loading 20 million records into memory isnt
> the most effient approach to showing a list box on the screen.

I suggested that after you said that Oracle collects results in
memory before returning them; you seemed to hint that this
wouldn't be a problem, in which case whether you do it yourself
or the database does it for you doesn't make a difference.

Solutions that come to mind are  a) to populate the UI from an
idle callback, where the scollbar would simply keep growing
independently from any user interaction until all the results are
fetched; or if that's unacceptable,  b) run a separate COUNT(*)
query, since preparing a extra query is cheap, but using COUNT(*)
tells SQLite that it can forgo a lot of processing, which makes
the up-front counting query quicker.

There are other options as well. A lot depends on your
circumstances. F.ex. paged queries can be made cheaper by
selecting results into a temporary table so that you can
re-retrieve them with a much cheaper query.

Regards,
--
Aristotle Pagaltzis // 


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] any data access and retrieval engine?

[drh]

"Sarah" <[EMAIL PROTECTED]> wrote:
> Hi, John Stanton
> 
> I really really appreciate your warm help.
> That's great if you can send me the codes of B tree and B+ tree. 
> Many thanks in advance.
> 
> My requirements for data access are as follows:
> -all the data are stored in non-volatile memory instead of volatile memory
> -the footprint of the DARE should be better less than 100KB
> -when executing, the memory occupation should be better less than 20KB
> -no need for relational access, just key-value retrieval is ok
> -all the create, insert, update work can be done outside, however, pretty 
> fast retrieval is needed
> 
> If there is some open-source DARE(as excellent as SQLite) suitable for my 
> platform, that will be great.
> Orelse, I would try to write a simple one.
> 

There is a proprietary version of SQLite available which
meets these requirements.  The "SQLite-SSE" product resides
in as little as 70KiB of code space and uses less than 20KiB
of ram.  

SQLite-SSE is basically just SQLite without sqlite3_prepare().
Sqlite3_prepare() is what takes up most of the space in the
library.  What you do is prepare your SQL statements ahead
of time on a workstation and then serialize them into a binary
format.  You can store the serialized prepared statements in
the database, if you like, or you can compile them into your
C code.  Your embedded device deserializes the prepared statements
then uses bind() and step() to run your queries as you normally
would.

The SQLite-SSE is used on smart-cards with extremely tight
memory and power constraints.  It is available under license
only.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] Re: Regarding sqlite3_exec

[Dennis Cote]

Nuno Lucas wrote:


There is another alternative if you don't mind to have the overhead of
having an automatic row count (which sqlite avoids by design). It's by
having a trigger that will update the table row count on each
insert/delete.


Nuno,

This technique only works if you never use conditions on your queries. 
If your query returns a subset of the rows in a table this carefully 
maintained count of all the rows in the table is useless.


Dennis Cote.

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

Re: [sqlite] INSERT Abilities?

[G. Roderick Singleton]

On Wed, 2006-10-25 at 11:47 -0500, Jarvis Stubblefield wrote:
> I am very sorry if this seems to be a newb question, and on the FAQ's
> or otherwise elsewhere. I couldn't find an answer to my question.
> 
> These INSERT statements work.
> 
> INSERT INTO fIndex(fPrice) VALUES(20);
> INSERT INTO fIndex(fPrice) VALUES(21);
> 
> I would be interested in as why the following doesnt work.
> 
> INSERT INTO fIndex(fPrice) VALUES(20), (21);

See http://www.1keydata.com/sql/sqlinsert.html I believe your statement
is incorrect. You could also try using EXPLAIN.

> 
> I know something that is possibly similar in nature being in the
> Unsupported SQL features.
> 
> *quote* Nested transactions.The current implementation only allows
> a single active transaction. */quote*
> 
> I am doing a database conversion, and I am trying to limit the amount
> of code I have to type out. Any help would be greatly appreciated.
> 
> Thanks,
> 
> -- 
> Jarvis J. Stubblefield
> Vortex Revolutions
> P.O. Box 716
> White House, TN 37188
> (C) 615.972.5415
> (E) [EMAIL PROTECTED]
> Website: http://www.vortexrevolutions.com/
> plain text document attachment (MDBtoSQLite.vbs)
> Option Explicit
> Const dbUseJet = 2
> 
> 'dao datatypes
> Const dbBoolean= 1   
> Const dbByte   = 2   
> Const dbInteger= 3   
> Const dbLong   = 4   
> Const dbCurrency   = 5   
> Const dbSingle = 6   
> Const dbDouble = 7   
> Const dbDate   = 8   
> Const dbBinary = 9   
> Const dbText   = 10  
> Const dbLongBinary = 11   
> Const dbMemo   = 12  
> Const dbGUID   = 15  
> Const dbBigInt = 16  
> Const dbVarBinary  = 17  
> Const dbChar   = 18  
> Const dbNumeric= 19  
> Const dbDecimal= 20  
> Const dbFloat  = 21  
> Const dbTime   = 22  
> Const dbTimeStamp  = 23  
> 
> Dim sql_keywords1, sql_keywords2
> 
> Private Function pad(ByVal valor,ByVal longitud)
> dim ret
> ret="" & valor
> while len(ret) ret="0" & ret
> wend
> pad=ret
> End Function
> 
> Private Function fechaCadena(ByVal valor)
> dim ret
> 'WScript.Echo "fechaCadena",valor
> ret=pad(year(valor),4) & "-" & pad(month(valor),2) & "-" & 
> pad(day(valor),2)
> ret=ret & " " & pad(hour(valor),2) & ":" & pad(minute(valor),2) & ":" & 
> pad(second(valor),2)
> fechaCadena = ret
> End Function
> 
> Private Function isSQLiteKeyword(ByVal fieldname ) 
> 
> Dim ucase_fieldname 
> Dim reservada 
> 
> ucase_fieldname = UCase(fieldname)
> isSQLiteKeyword = False
> For each reservada in sql_keywords1
> If ucase_fieldname = reservada Then
> isSQLiteKeyword = True
> Exit Function
> End If
> Next 
> For each reservada in sql_keywords2
> If ucase_fieldname = reservada Then
> isSQLiteKeyword = True
> Exit Function
> End If
> Next 
> End Function
> 
> Private Function sql_name(ByVal name )
> If isSQLiteKeyword(name) Or InStr(name, " ") > 0 Then
> sql_name = "[" & name & "]"
> Else
> sql_name = name
> End If
> End Function
> 
> Function getSQLiteFieldType(db_field , errtype )
> Select Case db_field.Type
> Case dbBoolean 'Yes/No
> getSQLiteFieldType = "BOOLEAN"
> Case dbByte, dbInteger,dbSingle, dbDouble,dbLong
> getSQLiteFieldType = "INTEGER"
> Case dbNumeric, dbBigInt
> getSQLiteFieldType = "NUMBER"
> Case dbDecimal
> getSQLiteFieldType = "NUMBER(" & db_field.Size & ")"
> 
> Case dbGUID 
> getSQLiteFieldType = "VARCHAR2"
> 
> Case dbFloat
> getSQLiteFieldType = "FLOAT"
> Case dbCurrency
> getSQLiteFieldType = "NUMBER(32,2)"
> Case dbDate, dbTime, dbTimeStamp
> getSQLiteFieldType = "DATE"
> Case dbText
> getSQLiteFieldType = "VARCHAR2(" & db_field.Size & ")"
> Case dbMemo
> getSQLiteFieldType = "TEXT"
> Case dbChar
> getSQLiteFieldType = "CHAR2(" & db_field.Size & ")"
> 
> Case dbBinary, dbVarBinary, dbLongBinary
> If errtype Then
> getSQLiteFieldType = "-- error: Field " & db_field.name & " 
> in table " & db_field.SourceTable & " has field type " & db_field.Type _
> & ". Type has been defined as BINARY, and it's data set NULL."
> Else
> getSQLiteFieldType = "BINARY" 'yet unsupported
> End If
> Case Else 'dont know this one
> if errtype Then
> getSQLiteFieldType = "-- error: Field " & db_field.name & " 
> in table " & db_field.SourceTable & " has field type " & db_field.Type _
> & ". Type is UNKNOWN, set to BINARY, and it's data set NULL."
> Else
> getSQLiteFieldType = "BINARY" 'yet unsupported
> End If
> End Select 
> End Function

Re: [sqlite] Re: Regarding sqlite3_exec

[Nuno Lucas]

On 10/25/06, Dennis Cote <[EMAIL PROTECTED]> wrote:

Nuno Lucas wrote:
>
> There is another alternative if you don't mind to have the overhead of
> having an automatic row count (which sqlite avoids by design). It's by
> having a trigger that will update the table row count on each
> insert/delete.
>
Nuno,

This technique only works if you never use conditions on your queries.
If your query returns a subset of the rows in a table this carefully
maintained count of all the rows in the table is useless.


Sure, but I wasn't trying to solve the general issue. The only
solution for that is to run the query to full length, whatever the SQL
engine is (even if they hide it from the user/programmer).

The trigger(s) could be elaborated to specific queries, off course,
and that would solve the GUI issue for fixed queries, but the generic
case will never have a solution other than the full scan.

Anyway, I never used this solution, just trying to show it can be
solved for the specific cases of most GUI's, if the trigger overhead
is not a problem.

In my modest opinion, if one really needs to have an accurate
scrollbar, one should show the rows by pages, with buttons to go to
the next/prev page (and the scrollbar will be correct per page). No
need to know the total rows in the view (unless we know the table
doesn't grow that much that a "select count(*)", or a full select into
memory, doesn't add much to the window rendering, which is most
times).


Regards,
~Nuno Lucas




Dennis Cote.


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

Re: [sqlite] hexadecimal

[Kees Nuyt]

Hi Lloyd,

On Wed, 25 Oct 2006 20:11:49 +0530, you wrote:

> Hi list,
>  can I insert a hexadecimal value to an integer field? 

Yes.

> if yes How can do that?

Convert it to an integer in your host language first.
The X'hexstring' syntax is only for BLOBs.

> Thanks,
>   Lloyd
-- 
  (  Kees Nuyt
  )
c[_]

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

Re: [sqlite] Re: Regarding sqlite3_exec

[Da Martian]

Indeed,

Thanks to all for the responses. Currently I use the seperate count(*), but
think I will change to the "prepare, step(n), reset" option only because the
query is a little slow.

Utlimatly sqlite is brilliant, hands down the best embeddable and standalone
db I have yet to encounter. Thanks to all involved in it.

S


On 10/25/06, Nuno Lucas <[EMAIL PROTECTED]> wrote:


On 10/25/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Nuno Lucas wrote:
> >
> > There is another alternative if you don't mind to have the overhead of
> > having an automatic row count (which sqlite avoids by design). It's by
> > having a trigger that will update the table row count on each
> > insert/delete.
> >
> Nuno,
>
> This technique only works if you never use conditions on your queries.
> If your query returns a subset of the rows in a table this carefully
> maintained count of all the rows in the table is useless.

Sure, but I wasn't trying to solve the general issue. The only
solution for that is to run the query to full length, whatever the SQL
engine is (even if they hide it from the user/programmer).

The trigger(s) could be elaborated to specific queries, off course,
and that would solve the GUI issue for fixed queries, but the generic
case will never have a solution other than the full scan.

Anyway, I never used this solution, just trying to show it can be
solved for the specific cases of most GUI's, if the trigger overhead
is not a problem.

In my modest opinion, if one really needs to have an accurate
scrollbar, one should show the rows by pages, with buttons to go to
the next/prev page (and the scrollbar will be correct per page). No
need to know the total rows in the view (unless we know the table
doesn't grow that much that a "select count(*)", or a full select into
memory, doesn't add much to the window rendering, which is most
times).


Regards,
~Nuno Lucas


>
> Dennis Cote.


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Re: Regarding sqlite3_exec

[John Stanton]

There is no magic in data retrieval.  Google use the same physical laws 
as us ordinary mortals.


I see no reason to ever perform a dataabase search twice.

Da Martian wrote:
Yes but google doesnt us an RDMS, its all propriatary to support there 
speed

and huge volumes. Its anyones guess (excpet google themselves) what exactly
they do, and rumours abound, but I have done many apps which require custom
data handling to achieve some end that doesnt fit with RDBM Systems.

But yes paging and using LIMIT and OFFSET is also a solution. Again not as
efficent though, cause of all the repeated queris :-)


On 10/25/06, Martin Jenkins <[EMAIL PROTECTED]> wrote:



Da Martian wrote:

> But to return all the rows just to count them requires N calls to
> step. If the data set is large you only want to return a subset to
> start with. So you wouldnt know the count. If you dont know the
> count, you cant update GUI type things etc..

I haven't been following this thread closely, but isn't this exactly the
problem that Google "solves" by returning

  "Results 1 - 10 of about 9,940,000 for sqlite. (0.11 seconds)"

for a query with a very large result set? If Google can't do it with all
the resources they have...

Martin


- 


To unsubscribe, send email to [EMAIL PROTECTED]

- 









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

Re: [sqlite] Re: Regarding sqlite3_exec

[John Stanton]

Nuno Lucas wrote:

On 10/25/06, Dennis Cote <[EMAIL PROTECTED]> wrote:


Nuno Lucas wrote:
>
> There is another alternative if you don't mind to have the overhead of
> having an automatic row count (which sqlite avoids by design). It's by
> having a trigger that will update the table row count on each
> insert/delete.
>
Nuno,

This technique only works if you never use conditions on your queries.
If your query returns a subset of the rows in a table this carefully
maintained count of all the rows in the table is useless.



Sure, but I wasn't trying to solve the general issue. The only
solution for that is to run the query to full length, whatever the SQL
engine is (even if they hide it from the user/programmer).

The trigger(s) could be elaborated to specific queries, off course,
and that would solve the GUI issue for fixed queries, but the generic
case will never have a solution other than the full scan.

Anyway, I never used this solution, just trying to show it can be
solved for the specific cases of most GUI's, if the trigger overhead
is not a problem.

In my modest opinion, if one really needs to have an accurate
scrollbar, one should show the rows by pages, with buttons to go to
the next/prev page (and the scrollbar will be correct per page). No
need to know the total rows in the view (unless we know the table
doesn't grow that much that a "select count(*)", or a full select into
memory, doesn't add much to the window rendering, which is most
times).


Regards,
~Nuno Lucas




Dennis Cote.



- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 



Some possible solutions to the problem of defining a result set size 
without using much extra memory, disk space or machine overhead.


If you want to know the size of your result set in advance and then 
select pages from that set an efficient way you could execute the query 
and build some form of index to the returned rows, using the rowid as 
the unique ID.  Then you can traverse that index at leisure, reading 
data columns as required.  You could organize your index so that a 
pre-order traversal gives you the sequence you want later.  That avoids 
the need for an ORDER BY.


The effect would be like having a CURSOR.  It involves a little 
programming, but then nothing is free.


A somewhat heavier duty, but simpler, alternative is just to write the 
result set to a temporary table, index it on the access key then use it 
for output.


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

[sqlite] number problem with 3.2.8

[Lloyd Thomas]

I am using sqlite 3.2.8 which is included in PHP5.1. I seem to be having a 
problem doing queries where with '>' to search a number.
for instance if I do the following

select  ring_time fron calls where ring_time > '10';
I get the following results
3
6
3
6
3
6
2
3
3
3
2
etc.
Why? 
This row is varchar. Is 3.2.8 not able to work with numbers stored as varchar? 

[sqlite] Re: number problem with 3.2.8

[Igor Tandetnik]

Lloyd Thomas
 wrote:

I am using sqlite 3.2.8 which is included in PHP5.1. I seem to be
having a problem doing queries where with '>' to search a number.
for instance if I do the following

select  ring_time fron calls where ring_time > '10';
I get the following results
3
6
3
6
3
6
2
3
3
3
2
etc.
Why?
This row is varchar. Is 3.2.8 not able to work with numbers stored as
varchar?


I don't see any problem. '3' is indeed greater than '10' in alphabetic 
order. What did you expect?


If you want the values to compare as numbers, why do you store them in a 
string field, and why do you compare against a string literal '10' 
rather than numeric literal 10 ?


Igor Tandetnik 



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

Re: [sqlite] number problem with 3.2.8

[Vitali Lovich]

I'm fairly certain that it's because when you're saying ring_time > 
'10', it's asking sqlite to take all strings that are greater than the 
string '10'.  So all the numbers you presented are greater than 1.  What 
you want is "where ring_time > 10".  Notice the lack of quotes which 
means treat 10 as a number instead of a string.




Lloyd Thomas wrote:

I am using sqlite 3.2.8 which is included in PHP5.1. I seem to be having a problem 
doing queries where with '>' to search a number.
for instance if I do the following

select  ring_time fron calls where ring_time > '10';
I get the following results
3
6
3
6
3
6
2
3
3
3
2
etc.
Why? 
This row is varchar. Is 3.2.8 not able to work with numbers stored as varchar? 

  


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

Re: [sqlite] Re: number problem with 3.2.8

[Lloyd Thomas]

I did try number literal  >10 but mad no difference. I will rebuild the 
database row as an integer.

Lloyd
- Original Message - 
From: "Igor Tandetnik" <[EMAIL PROTECTED]>

To: "SQLite" 
Sent: Thursday, October 26, 2006 12:21 AM
Subject: [sqlite] Re: number problem with 3.2.8



Lloyd Thomas
 wrote:

I am using sqlite 3.2.8 which is included in PHP5.1. I seem to be
having a problem doing queries where with '>' to search a number.
for instance if I do the following

select  ring_time fron calls where ring_time > '10';
I get the following results
3
6
3
6
3
6
2
3
3
3
2
etc.
Why?
This row is varchar. Is 3.2.8 not able to work with numbers stored as
varchar?


I don't see any problem. '3' is indeed greater than '10' in alphabetic 
order. What did you expect?


If you want the values to compare as numbers, why do you store them in a 
string field, and why do you compare against a string literal '10' rather 
than numeric literal 10 ?


Igor Tandetnik

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




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

RE: [sqlite] Re: number problem with 3.2.8

[Griggs, Donald]

-Original Message-
From: Lloyd Thomas [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 25, 2006 7:49 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: number problem with 3.2.8

I did try number literal  >10 but mad no difference. I will rebuild the
database row as an integer.
Lloyd


---

Or, if you have some reason for preferring varchar, you can choose to
force a numeric comparison at query time by adding "+0" as in:
   select  ring_time from calls where ring_time+0 > 10;

 

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

Re: [sqlite] Re: number problem with 3.2.8

[Derrell . Lipman]

"Lloyd Thomas" <[EMAIL PROTECTED]> writes:

> I did try number literal  >10 but mad no difference. I will rebuild the
> database row as an integer.

As an interim solution (prior to changing your database schema), you should be
able to use your existing schema with this query:

  select ring_time from calls where (ring_time + 0) > 10;

That should force the left side to be evaluated as an integer, and the right
side already is an integer.  You should get an integer comparison.

Note that if ring_time was indexed, your index will be ignored since you're
using a calculated value instead of the field.  Changing the schema is
certainly the proper solution if the value is supposed to be interpreted as an
integer.

Derrell

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