Re: [sqlite] DeviceSQL

[John Stanton]

Testing only finds bugs, it does not guarantee accuracy.  Careful design 
however can establish accuracy, and to verify that methodology requires 
examination of the source code.


James Steward wrote:

steveweick wrote:

Do you need to read the code to verify reliability as your next few
sentences seems to imply? For that to be true, the reader would have 
to be

able to spot bugs through inspection.  While that is certainly one way to
spot bugs, I seriously doubt that any shop would rely on code inspection,
when millions of dollars of potential recall costs are on the line.
  
I think many would agree that code inspections do find (serious) bugs, 
that may not show up from testing.  I'm sure your company conducts code 
inspection meetings as a part of all code development.  We (the company 
I work for) certainly do.  I know I've seen change logs that read 
something like "Fixed possible buffer overflow in foo..." for open 
source projects here and there as well.



In fact the SQLite marketing does not rely on code inspection as its
argument for why the code is reliable. Check it out.   
That would be bad if they did, I agree.  But all the testing in the 
world won't uncover all the bugs either, in a complex piece of code.  
See http://www.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf .
"The Ptolemy II system itself began to be widely used, and every use of 
the system exercised this
code. No problems were observed until the code deadlocked on April 26, 
2004, four years later."  And that was after code inspections, 
regression tests and belt and braces programming techniques!

All of that said, I do admire the elegance of the SQLite code.  It makes
entertaining reading.  Unfortunately elegance does not translate into
performance or reliability.
  
Not necessarily, but it often does, and can make for better 
maintainability too.  I've not trawled through to SQLite code myself, so 
couldn't comment.  But it does have quite a few big name users, and an 
active and helpful user forum, which gives me good vibes at least.


Cheerio,
James.

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

[sqlite] Problems Insert with Date and Time values

[Giuliano]

Hello,

is it possible (how?) to insert into 2 different fields (date) the following 
values:


09:30:00(only a time value...)
14/07/07   (only year value, 14th of december 2007)

sorry, I am not be able at all to do that. Many thanks in advance for 
helping,


Giuliano 



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

Re: [sqlite] DeviceSQL

[James Steward]

steveweick wrote:

Do you need to read the code to verify reliability as your next few
sentences seems to imply? For that to be true, the reader would have to be
able to spot bugs through inspection.  While that is certainly one way to
spot bugs, I seriously doubt that any shop would rely on code inspection,
when millions of dollars of potential recall costs are on the line.
  
I think many would agree that code inspections do find (serious) bugs, 
that may not show up from testing.  I'm sure your company conducts code 
inspection meetings as a part of all code development.  We (the company 
I work for) certainly do.  I know I've seen change logs that read 
something like "Fixed possible buffer overflow in foo..." for open 
source projects here and there as well.



In fact the SQLite marketing does not rely on code inspection as its
argument for why the code is reliable. Check it out. 
  
That would be bad if they did, I agree.  But all the testing in the 
world won't uncover all the bugs either, in a complex piece of code.  
See http://www.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf .
"The Ptolemy II system itself began to be widely used, and every use of 
the system exercised this
code. No problems were observed until the code deadlocked on April 26, 
2004, four years later."  And that was after code inspections, 
regression tests and belt and braces programming techniques!

All of that said, I do admire the elegance of the SQLite code.  It makes
entertaining reading.  Unfortunately elegance does not translate into
performance or reliability.
  
Not necessarily, but it often does, and can make for better 
maintainability too.  I've not trawled through to SQLite code myself, so 
couldn't comment.  But it does have quite a few big name users, and an 
active and helpful user forum, which gives me good vibes at least.


Cheerio,
James.

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

Re: [sqlite] DeviceSQL

[John Stanton]

I unfortunately missed the Encirq webinar thanks to a project commitment 
but have taken the time to download the Encirq demo and try to make good 
the loss.  It has some user examples in source code which give an idea 
of how it functions, but the information on the product is sparse so it 
was not possible to get an idea of the mechanics of indices, paging etc.


What I saw was a well conceived product to build embedded software.  It 
seems to be a compiler which transforms Encirq's version of PL/SQL into 
C statements which are then compiled into a library of data manipulation 
functions for use in the application.  The demo uses gcc.  Encirq has a 
means of including "storage modules" to handle different forms of 
persistent storage.  DeviceSQL appears to handle transactions and 
rollbacks.  There is no information I could find about ACID functionality.


I shall prepare some benchmarks against Sqlite once I figure out a 
suitable method.  Since DeviceSQl has no SQL compiler the Sqlite will 
need to have prepared statements and binding to provide an apples to 
apples comparison.  The Encirq introductory application example is by 
necessity trivial and small and not suited to a benchmark.


DeviceSQL is not suitable for general purpose SQL processing, unlike 
Sqlite, and should only be compared as an alternative in deeply embedded 
applications so the only useful comparison is one which looks like a 
cell phone, microwave oven or a TV set top box.


I can imagine that a version of Sqlite which does not include its SQL 
compiler and which uses precompiled VDBE code would provide similar 
functionality to DeviceSQL, particularly if the Sqlite compiler were 
extended to generate VDBE from PL/SQl.  I can imagine that the higher 
information density of the VDBE code could deliver the advantage =of a 
smaller memory footprint.


For Steve Weick - I note your very strong resume and would imagine that 
your comprehensive experience would lead you to introduce a less 
secretive policy as to revealing the capabilities of your product.


steveweick wrote:

Good  idea... I'll pass it along to the right folks. Meanwhile, if anyone has
further questions or comments, please feel free to write me here (if they
think the group would be interested) or at [EMAIL PROTECTED]

Steve

I would like to recommend that Encriq create a forum or mailing list of 
their own for those who are interesting in learning more.  For me, what 
might be an interesting product is quickly being overshadowed by this 
thread.






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

Re: [sqlite] SQLite and Columnar Databases

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> The reason why I asked is that I haven't had much luck with sqlite3 
> performance for databases larger than the size of RAM on my machine
> regardless of PRAGMA settings.
> 

This is probably do to the cache locality problem.  We know how
to fix this, Joe.  Would you like to have a go at it?
--
D. Richard Hipp <[EMAIL PROTECTED]>


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

Re: [sqlite] Should the next release be 3.5.4 or 3.6.0?

[Rick Langschultz]

My question first is can this fix be rolled into a group of related  
fixes thus creating a service patch? If not then my opinion is 3.5.4.  
If you can roll it in with 3 - 10 easy fixes call it 3.6.0.


Just an opinion though. I am waiting for 4.0, like it is Xmas Eve...


On Dec 13, 2007, at 10:40 AM, [EMAIL PROTECTED] wrote:


Ticket number #2822

 http://www.sqlite.org/cvstrac/tktview?tn=2822

has provoked extensive changes to the way SQLite handles
ORDER BY clauses.  The current algorithm goes like this:


  (1)  If an ORDER BY term is a constant integer k
   then sort by the k-th column of the result set.

  (2)  If an ORDER BY term is a simple identifer
   (like "x", not "x.y" and not "x.y.z") and if
   there if the k-th column uses that same identifer
   as an AS alias, the sort by the k-th column.

  (3)  Otherwise, evaluate the expression which is
   in the ORDER BY term and sort by the resulting
   value.

For a compound query, the expression in step (3) must
exactly match one of the result columns.  Also, the
three steps are attempted first on the left-most SELECT.
If there is a match, the process stops there.  If no
match is found, the next SELECT to the right is tried.
This repeats as necessary until a match is found or
until you run out of SELECT statement in which case there
is an error.

This algorithm differs from all prior versions of SQLite
(1.0.0 through 3.5.3) by the addition of step (2).
Adding step (2) brings SQLite much closer to the SQL
standard.  I believe that SQLite is now a superset of
the SQL standard.  SQL has no concept of step (3).  And
in a compound query, SQL only looks at the left-most
SELECT and does not fail over to SELECT statements to
the right looking for a match.  But these changes can
be considered extensions.

The revised algorithm is mostly compatible with the
way SQLite has always operated before.  But there
are a few obscure corner cases where there is a difference.
An example of the difference is the following:

   CREATE TABLE a(x,y);
   INSERT INTO a VALUES(1,8);
   INSERT INTO a VALUES(9,2);

   SELECT x AS y FROM a ORDER BY y;

In older versions of SQLite, the SELECT statement above
would return 9, 1 since the ORDER BY term evaluated to
the expression a.y by rule (3)  In the next release,
because of the addition of rule (2) above, the result
will be 1, 9.

My question to the community is this:  Are these
differences sufficient to justify going with version
3.6.0 in the next release?  Or can we call the change
a "bug fix" and number the next release 3.5.4?

Other information to consider:

  *  We do not have a lot of time to debate the merits
 of this change since we need to get out a release
 to fix critical bug #2832.

  *  We have taken no steps toward fixing GROUP BY.
 If I got ORDER BY wrong, I'm guessing GROUP BY
 is wrong too.

Thanks for your input.

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


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




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

Re: [sqlite] DeviceSQL

[steveweick]

Good  idea... I'll pass it along to the right folks. Meanwhile, if anyone has
further questions or comments, please feel free to write me here (if they
think the group would be interested) or at [EMAIL PROTECTED]

Steve

I would like to recommend that Encriq create a forum or mailing list of 
their own for those who are interesting in learning more.  For me, what 
might be an interesting product is quickly being overshadowed by this 
thread.


-- 
View this message in context: 
http://www.nabble.com/DeviceSQL-tp14297970p14329799.html
Sent from the SQLite mailing list archive at Nabble.com.


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

Re: [sqlite] Should the next release be 3.5.4 or 3.6.0?

[Robert L Cochran]

Trey Mack wrote:



3.6.0 in the next release?  Or can we call the change
a "bug fix" and number the next release 3.5.4?


I guess I'm in the minority, but I'd find a change in the meaning of  
my queries surprising in a bug fix release. That sounds like a 3.6 
to  me.


You may be in the minority, but you're not alone. +1.


And I agree, this is a 3.6.0 kind of release.

Bob


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

Re: [sqlite] Should the next release be 3.5.4 or 3.6.0?

[Trey Mack]

3.6.0 in the next release?  Or can we call the change
a "bug fix" and number the next release 3.5.4?


I guess I'm in the minority, but I'd find a change in the meaning of  
my queries surprising in a bug fix release. That sounds like a 3.6 to  
me.


You may be in the minority, but you're not alone. +1.

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

RE: [sqlite] DeviceSQL

[Birt, Jeffrey]

I concur. Quite an interesting marketing strategy; Join you competitors'
mailing lists and trash talk them. LOL

-Original Message-
From: dcharno [mailto:[EMAIL PROTECTED] 
Sent: Thursday, December 13, 2007 8:30 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DeviceSQL

I would like to recommend that Encriq create a forum or mailing list of 
their own for those who are interesting in learning more.  For me, what 
might be an interesting product is quickly being overshadowed by this 
thread.


> 
> You raise some interesting points.  There is nothing secret about the
> benchmarks.  We will make the code that was used to run benchmarks
available
> to anyone who wants to see it and verify results. If you want to find
a
> third party to verify, be my guest. The benchmark report goes into
some
> depth on the design and rationale for the benchmark.  Frankly, as much
as I
> like the idea about taking DeviceSQL open source, you don't need to do
so,
> just to verify performance claims.  
> 
> Do you need to read the code to verify reliability as your next few
> sentences seems to imply? For that to be true, the reader would have
to be
> able to spot bugs through inspection.  While that is certainly one way
to
> spot bugs, I seriously doubt that any shop would rely on code
inspection,
> when millions of dollars of potential recall costs are on the line.
> 
> In fact the SQLite marketing does not rely on code inspection as its
> argument for why the code is reliable. Check it out. 
> 
> All of that said, I do admire the elegance of the SQLite code.  It
makes
> entertaining reading.  Unfortunately elegance does not translate into
> performance or reliability.
> 
> Regards,
> 
> Steve
> 
> James Steward-2 wrote:
>> steveweick wrote:
>>> Richard has it right this time.  Today DeviceSQL uses no SQLite
code. One
>>> of
>>> the things we might consider is bolting the SQLite parser/front end
to
>>> our
>>> table engine, in theory to get the both worlds.  Just an idea at the
>>> moment.
>>>   
>> Such an interesting discussion to be following.  I must say though,
it 
>> seems DeviceSQL has opened the door to speculation due to 
>> unsubstantiated claims in advertising, as far as I see it.  IMHO, so 
>> long as there is no independent, unbiased, side by side test results 
>> presented somewhere by some reliable source, there will always be
some 
>> room for "ifs" and "buts" by both sides.
>>
>> Maybe DeviceSQL should go open source, so the public can judge for
them 
>> selves the qualities of the two products.  There would still be money
to 
>> be made from paid support.  Who knows, both parties could benefit,
and 
>> customers too.  At least there'd be a clearer view of the pros and
cons. 
>>
>> There is something to be said for a product being open source, that
is 
>> the code is scrutinized by the world.  Closed shop code can possibly 
>> still be very good, but without seeing it, how would we know?
Reminds 
>> me of a story about a cat: dead or alive, we won't know until we open

>> the box it's in, and prior to that, is it only half dead?
>>
>> One only has to look at the MSDN code examples to see the ugliness of

>> closed source  code development...(sorry Bill)
>>
>> JS.
>>
>>

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

-
>>
>>
>>
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

[sqlite] Re: Re: Update current record

[Igor Tandetnik]

Tom Parke <[EMAIL PROTECTED]> wrote:

By a "regular update statement", I assume you mean a SQL update tab
set 
col = value, but I don't understand how to apply a SQL update
statement 
to a ROWID.


update tableName set col = value where ROWID = ?;


 How can I get the ROWID in the call back?


You list it in the SELECT in the first place:

select ROWID, ... from myTable;

Igor Tandetnik

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

Re: [sqlite] Update current record

[drh]

"Tom Parke" <[EMAIL PROTECTED]> wrote:
> Using sqlite3_prepare and then sqlite3_step() ing through the records,
> is there a way to update or replace the current record in a table while
> sqlite3_step() is on that record without a reselecting that record and
> sqlite3_exec() update table where?  Something like a dynamic cursor in
> MS SQL?
> 

The "current record" might not (often does not) correspond to
any row of any table.  What if, for example, your query is a
join or especially a join with subqueries in the FROM clause?

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


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

RE: [sqlite] Re: Update current record

[Tom Parke]

By a "regular update statement", I assume you mean a SQL update tab set
col = value, but I don't understand how to apply a SQL update statement
to a ROWID.  How can I get the ROWID in the call back?

Can you point me to some sample code that could help me understand?
Tom

-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: Thursday, December 13, 2007 5:50 PM
To: SQLite
Subject: [sqlite] Re: Update current record

Tom Parke <[EMAIL PROTECTED]> wrote:
> Using sqlite3_prepare and then sqlite3_step() ing through the records,
> is there a way to update or replace the current record in a table
> while sqlite3_step() is on that record without a reselecting that
> record and sqlite3_exec() update table where?  Something like a
> dynamic cursor in MS SQL?

No. You have to issue a regular update statement using current record's 
ROWID in the WHERE clause.

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

Re: [sqlite] DeviceSQL

[dcharno]

I would like to recommend that Encriq create a forum or mailing list of 
their own for those who are interesting in learning more.  For me, what 
might be an interesting product is quickly being overshadowed by this 
thread.





You raise some interesting points.  There is nothing secret about the
benchmarks.  We will make the code that was used to run benchmarks available
to anyone who wants to see it and verify results. If you want to find a
third party to verify, be my guest. The benchmark report goes into some
depth on the design and rationale for the benchmark.  Frankly, as much as I
like the idea about taking DeviceSQL open source, you don't need to do so,
just to verify performance claims.  


Do you need to read the code to verify reliability as your next few
sentences seems to imply? For that to be true, the reader would have to be
able to spot bugs through inspection.  While that is certainly one way to
spot bugs, I seriously doubt that any shop would rely on code inspection,
when millions of dollars of potential recall costs are on the line.

In fact the SQLite marketing does not rely on code inspection as its
argument for why the code is reliable. Check it out. 


All of that said, I do admire the elegance of the SQLite code.  It makes
entertaining reading.  Unfortunately elegance does not translate into
performance or reliability.

Regards,

Steve

James Steward-2 wrote:

steveweick wrote:

Richard has it right this time.  Today DeviceSQL uses no SQLite code. One
of
the things we might consider is bolting the SQLite parser/front end to
our
table engine, in theory to get the both worlds.  Just an idea at the
moment.
  
Such an interesting discussion to be following.  I must say though, it 
seems DeviceSQL has opened the door to speculation due to 
unsubstantiated claims in advertising, as far as I see it.  IMHO, so 
long as there is no independent, unbiased, side by side test results 
presented somewhere by some reliable source, there will always be some 
room for "ifs" and "buts" by both sides.


Maybe DeviceSQL should go open source, so the public can judge for them 
selves the qualities of the two products.  There would still be money to 
be made from paid support.  Who knows, both parties could benefit, and 
customers too.  At least there'd be a clearer view of the pros and cons. 

There is something to be said for a product being open source, that is 
the code is scrutinized by the world.  Closed shop code can possibly 
still be very good, but without seeing it, how would we know?  Reminds 
me of a story about a cat: dead or alive, we won't know until we open 
the box it's in, and prior to that, is it only half dead?


One only has to look at the MSDN code examples to see the ugliness of 
closed source  code development...(sorry Bill)


JS.

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








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

[sqlite] Re: [Linux + PHP] Recommended way to access SQLite?

[Gilles Ganault]

On Mon, 10 Dec 2007 16:35:48 +0100, Kees Nuyt  <[EMAIL PROTECTED]> wrote:
>So I activated php_pdo_sqlite_external instead and copied
>sqlite3.dll v3.5.3 to %serverroot%/bin .

Thanks for the infos.

So the options are:
1. use the old SQLite2 sqlite_() functions (or some class that turns this 
into OO)

2. PDO to use the SQLite3 linked-in library
3. PDO to access the SQLite3 DLL

... with 2 being the recommended choice.


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

Re: [sqlite] DeviceSQL

[steveweick]

Hi James,

You raise some interesting points.  There is nothing secret about the
benchmarks.  We will make the code that was used to run benchmarks available
to anyone who wants to see it and verify results. If you want to find a
third party to verify, be my guest. The benchmark report goes into some
depth on the design and rationale for the benchmark.  Frankly, as much as I
like the idea about taking DeviceSQL open source, you don't need to do so,
just to verify performance claims.  

Do you need to read the code to verify reliability as your next few
sentences seems to imply? For that to be true, the reader would have to be
able to spot bugs through inspection.  While that is certainly one way to
spot bugs, I seriously doubt that any shop would rely on code inspection,
when millions of dollars of potential recall costs are on the line.

In fact the SQLite marketing does not rely on code inspection as its
argument for why the code is reliable. Check it out. 

All of that said, I do admire the elegance of the SQLite code.  It makes
entertaining reading.  Unfortunately elegance does not translate into
performance or reliability.

Regards,

Steve

James Steward-2 wrote:
> 
> steveweick wrote:
>> Richard has it right this time.  Today DeviceSQL uses no SQLite code. One
>> of
>> the things we might consider is bolting the SQLite parser/front end to
>> our
>> table engine, in theory to get the both worlds.  Just an idea at the
>> moment.
>>   
> Such an interesting discussion to be following.  I must say though, it 
> seems DeviceSQL has opened the door to speculation due to 
> unsubstantiated claims in advertising, as far as I see it.  IMHO, so 
> long as there is no independent, unbiased, side by side test results 
> presented somewhere by some reliable source, there will always be some 
> room for "ifs" and "buts" by both sides.
> 
> Maybe DeviceSQL should go open source, so the public can judge for them 
> selves the qualities of the two products.  There would still be money to 
> be made from paid support.  Who knows, both parties could benefit, and 
> customers too.  At least there'd be a clearer view of the pros and cons. 
> 
> There is something to be said for a product being open source, that is 
> the code is scrutinized by the world.  Closed shop code can possibly 
> still be very good, but without seeing it, how would we know?  Reminds 
> me of a story about a cat: dead or alive, we won't know until we open 
> the box it's in, and prior to that, is it only half dead?
> 
> One only has to look at the MSDN code examples to see the ugliness of 
> closed source  code development...(sorry Bill)
> 
> JS.
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/DeviceSQL-tp14297970p14328763.html
Sent from the SQLite mailing list archive at Nabble.com.


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

[sqlite] Re: Re: Insert chinese characters in SQLite database

[Igor Tandetnik]

Kalyani Phadke
 wrote:

Yes , It worked.
Could you pls tell me whats the difference between adLongVarWChar and
adLongVarChar? Does SQLite support Unicode encoding(UTF-8)?


SQLite does support UTF-8. But I suspect ADO actually converts the data 
to Windows current code page when adLongVarChar is specified, not to 
UTF-8. You know - Control Panel | Regional and Language Options | 
Advanced | Language for non-Unicode Programs.


Igor Tandetnik 



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

[sqlite] Re: Update current record

[Igor Tandetnik]

Tom Parke <[EMAIL PROTECTED]> wrote:

Using sqlite3_prepare and then sqlite3_step() ing through the records,
is there a way to update or replace the current record in a table
while sqlite3_step() is on that record without a reselecting that
record and sqlite3_exec() update table where?  Something like a
dynamic cursor in MS SQL?


No. You have to issue a regular update statement using current record's 
ROWID in the WHERE clause.


Igor Tandetnik 



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

Re: [sqlite] DeviceSQL

[James Steward]

steveweick wrote:

Richard has it right this time.  Today DeviceSQL uses no SQLite code. One of
the things we might consider is bolting the SQLite parser/front end to our
table engine, in theory to get the both worlds.  Just an idea at the moment.
  
Such an interesting discussion to be following.  I must say though, it 
seems DeviceSQL has opened the door to speculation due to 
unsubstantiated claims in advertising, as far as I see it.  IMHO, so 
long as there is no independent, unbiased, side by side test results 
presented somewhere by some reliable source, there will always be some 
room for "ifs" and "buts" by both sides.


Maybe DeviceSQL should go open source, so the public can judge for them 
selves the qualities of the two products.  There would still be money to 
be made from paid support.  Who knows, both parties could benefit, and 
customers too.  At least there'd be a clearer view of the pros and cons. 

There is something to be said for a product being open source, that is 
the code is scrutinized by the world.  Closed shop code can possibly 
still be very good, but without seeing it, how would we know?  Reminds 
me of a story about a cat: dead or alive, we won't know until we open 
the box it's in, and prior to that, is it only half dead?


One only has to look at the MSDN code examples to see the ugliness of 
closed source  code development...(sorry Bill)


JS.

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

Re: [sqlite] Should the next release be 3.5.4 or 3.6.0?

[Nuno Lucas]

On Dec 14, 2007 12:35 AM, Steven Fisher <[EMAIL PROTECTED]> wrote:
> On 13-Dec-2007, at 8:40 AM, [EMAIL PROTECTED] wrote:
>
> > My question to the community is this:  Are these
> > differences sufficient to justify going with version
> > 3.6.0 in the next release?  Or can we call the change
> > a "bug fix" and number the next release 3.5.4?
>
> I guess I'm in the minority, but I'd find a change in the meaning of
> my queries surprising in a bug fix release. That sounds like a 3.6 to
> me.

Well, I agree with you. It just seems the right thing to do, and
version numbers are cheap.


Regards,
~Nuno Lucas

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

RE: [sqlite] SQLite and Columnar Databases

[Darren Duncan]

At 7:59 AM -0500 12/13/07, Tom Briggs wrote:

 > Something I will say about this, for people who don't know, is that

 this columnar thing is strictly an implementation detail.  While


   I think that this is an oversimplification.  That's somewhat like
saying that the way you use a sledge hammer is no different than how you
use a claw hammer, because they're both hammers.  Anyone who tries to
hang a picture with a sledge hammer will be rather unhappy with your
advice.

   Though at some level how the data is stored is indeed an
implementation detail, to take full advantage of the fact that it is
requires re-thinking schema design and in some cases even query design.
See
http://www.fulltablescan.com/index.php?/archives/44-Compression-as-Normalization...-Kinda.html 
for more info.


If you read further into your own link, including the comments, 
you'll see my case also being made.


I don't see your claw hammer / sledge hammer argument being applicable here.

The relational model of data is all about letting users defining the 
logical/possible structure of their data, with a focus on the meaning 
of the data relative to other data.  It is intended to make data 
manipulation and queries both easy to use and logically rigorous. 
The schemas that users design, including their so-called 
normalization traits, are just for reflecting the meaning of their 
data in a logical manner.


This model is completely removed from how the data is physically 
stored, eg whether in rows first or in columns first, and the 
physical store is determined just by the DBMS behind the scenes, and 
hence is an implementation detail.  The DBMS can arrange how it likes 
in order to satisfy the logical model in a way that performs well. 
The users do not contort their schemas beyond what is logically clean 
in order to gain performance; having a clean schema will let the DBMS 
infer this automatically.


According to some comments, Vertica (a column-store maker) is making 
the same case that I am, which is just to have a logical clean 
schema, and performance benefits will automatically follow from that.


I will also note that the highest level of schema normalization, 6th 
Normal Form, essentially puts every non-key column in its own table.


I will also note that a column-based store essentially works like a 
heavily indexed row-based store, in which there is an index on every 
key or every column, and so all searches, which includes those on 
which joins are performed, can/do look in what is otherwise indexes. 
This is potentially slower for updates (or maybe not), but can be 
faster for queries, depending on circumstances.


-- Darren Duncan

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

RE: [sqlite] Re: Insert chinese characters in SQLite database

[Kalyani Phadke]

 Yes , It worked. 
Could you pls tell me whats the difference between adLongVarWChar and
adLongVarChar? Does SQLite support Unicode encoding(UTF-8)? 

-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: Thursday, December 13, 2007 4:13 PM
To: SQLite
Subject: [sqlite] Re: Insert chinese characters in SQLite database

Kalyani Phadke
 wrote: 
> The following query inserts  into the database
> 
> Cmd1 .Parameters.Append(Cmd1 .CreateParameter("ipaddress", 
> adLongVarChar, adParamInput, 50, ipaddress))

Try adLongVarWChar for parameter type.

Igor Tandetnik


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

Re: [sqlite] Should the next release be 3.5.4 or 3.6.0?

[Dr Gerard Hammond]

On 14/12/2007, at 3:40 AM, [EMAIL PROTECTED] wrote:


Ticket number #2822

http://www.sqlite.org/cvstrac/tktview?tn=2822




(2)  If an ORDER BY term is a simple identifer
 (like "x", not "x.y" and not "x.y.z") and if
 there if the k-th column uses that same identifer
 as an AS alias, the sort by the k-th column.





CREATE TABLE a(x,y);
 INSERT INTO a VALUES(1,8);
 INSERT INTO a VALUES(9,2);

 SELECT x AS y FROM a ORDER BY y;

In older versions of SQLite, the SELECT statement above
would return 9, 1 since the ORDER BY term evaluated to
the expression a.y by rule (3)  In the next release,
because of the addition of rule (2) above, the result
will be 1, 9.



I don't understand.  If I say "ORDER BY y" aren't I saying sort the  
result set based on the column " as y"  of the result set, not the  
table "a.y"?

ie they should come out

y
9
1



That's what (2) says to me

Cheers.

--

Dr Gerard Hammond
http://www.macsos.com.au









Cheers.

--

Dr Gerard Hammond
http://www.macsos.com.au









Cheers.

--

Dr Gerard Hammond
Garvan Institute of Medical Research






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

[sqlite] Update current record

[Tom Parke]

Using sqlite3_prepare and then sqlite3_step() ing through the records,
is there a way to update or replace the current record in a table while
sqlite3_step() is on that record without a reselecting that record and
sqlite3_exec() update table where?  Something like a dynamic cursor in
MS SQL?

 

Tom

[sqlite] Re: Insert chinese characters in SQLite database

[Igor Tandetnik]

Kalyani Phadke
 wrote: 

The following query inserts  into the database

Cmd1 .Parameters.Append(Cmd1 .CreateParameter("ipaddress",
adLongVarChar, adParamInput, 50, ipaddress)) 


Try adLongVarWChar for parameter type.

Igor Tandetnik

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

[sqlite] Insert chinese characters in SQLite database

[Kalyani Phadke]

 Table 

CREATE TABLE [Test2] 
(
[ipaddress] INTEGER,
[Name] text
)

The following query works fine 

Set Cmd1 = Server.CreateObject("ADODB.Command")

Cmd1 .ActiveConnection = AuditLogConnection

Cmd1 .CommandText = "INSERT INTO Test2(ipaddress, name) VALUES ('192.168.1.1' , 
'曹长庆从三个方面来分析为什么说是结构性上涨:首先,从价格指数看,今年1-11月份,在构成居民消费价格指数的八大类商品中,上涨幅度较大的主要是食品类和居住类价格。在1-11月价格总水平上升4.6%中,食品类价格上涨11.9%,影响价格总水平上升4个百分点,占涨价因素的87%。其中仅猪肉价格上涨就影响价格总水平上升1.4个百分点。居住类价格上涨4.3%,影响价格总水平上升0.6个百分点。其余六大类商品价格基本保持平稳,升降相抵,对价格总水平基本没有影响。从这个角度来看,当前的价格上涨结构性特征非常明显。')
 "

ret = Cmd1 .Execute

The following query inserts  into the database

Dim Cmd1 , ret,namestr,id

ip  ='192.168.1.1' 

namestr =  
'曹长庆从三个方面来分析为什么说是结构性上涨:首先,从价格指数看,今年1-11月份,在构成居民消费价格指数的八大类商品中,上涨幅度较大的主要是食品类和居住类价格。在1-11月价格总水平上升4.6%中,食品类价格上涨11.9%,影响价格总水平上升4个百分点,占涨价因素的87%。其中仅猪肉价格上涨就影响价格总水平上升1.4个百分点。居住类价格上涨4.3%,影响价格总水平上升0.6个百分点。其余六大类商品价格基本保持平稳,升降相抵,对价格总水平基本没有影响。从这个角度来看,当前的价格上涨结构性特征非常明显。'

Set Cmd1 = Server.CreateObject("ADODB.Command")

Cmd1 .ActiveConnection = AuditLogConnection

Cmd1 .CommandText = "INSERT INTO Test2(ipaddress, name) VALUES (?, ?)" 

Cmd1 .Prepared = true

Cmd1 .Parameters.Append(Cmd1 .CreateParameter("ipaddress", adLongVarChar, 
adParamInput, 50, ipaddress))

Cmd1 .Parameters.Append(Cmd1 .CreateParameter("name", adLongVarChar, 
adParamInput, 50, namestr))

ret = Cmd1 .Execute

AuditLogConnection contains connection string as 

"Provider=MSDASQL.1;Extended Properties=""DSN=Test;Database=C:\Program 
Files\Test.db;StepAPI=0;SyncPragma=NORMAL;NoTXN=;Timeout=10;ShortNames=;LongNames=;NoCreat=;NoWCHAR=;LoadExt=;"""

 

Re: [sqlite] DeviceSQL

[steveweick]

Richard has it right this time.  Today DeviceSQL uses no SQLite code. One of
the things we might consider is bolting the SQLite parser/front end to our
table engine, in theory to get the both worlds.  Just an idea at the moment.

Steve



D. Richard Hipp wrote:
> 
> Joe Wilson <[EMAIL PROTECTED]> wrote:
>> Be careful about speculative comments.
>> 
>> For all anyone knows, said product could use SQLite internally with 
>> a couple of proprietary optimizations here and there that may make it
>> faster in specific cases. 
>> 
>> The sqlite public domain license would allow that sort of thing.
>> 
> 
> Because of the radically different architectures of SQLite
> and DeviceSQL, it seems unlikely that they share a common
> core.  Though, I suppose anything is possible...
> 
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/DeviceSQL-tp14297970p14327325.html
Sent from the SQLite mailing list archive at Nabble.com.


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

Re: [sqlite] DeviceSQL

[steveweick]

As shown another thread, Richard has his facts wrong.  See
http://www.nabble.com/Improving-performance-of-SQLite.-Anyone-heard-of-DeviceSQL--to14280006.html#a14317195

Steve

A. Pagaltzis wrote:
> 
> * John Stanton <[EMAIL PROTECTED]> [2007-12-12 17:55]:
>> In general claims of "20x" or even "5x" imply either serious
>> deficiencies in the compared product or a generous dose of
>> snake oil in the challenger.
> 
> Depends. The outline given by Dr. Hipp about the product’s
> features may the claim quite plausible, because you pay a hefty
> cut in features and reliability in exchange for a very large
> increase in speed; a price that many may well find unacceptable.
> (It is, after all, easy, as they say, to compute the wrong answer
> in constant time.)
> 
> Regards,
> -- 
> Aristotle Pagaltzis // 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/DeviceSQL-tp14297970p14327322.html
Sent from the SQLite mailing list archive at Nabble.com.


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

Re: [sqlite] DeviceSQL

[steveweick]

I hope this is not a double post, but this was answered in another thread. 
See 
http://www.nabble.com/Improving-performance-of-SQLite.-Anyone-heard-of-DeviceSQL--to14280006.html#a14317195



D. Richard Hipp wrote:
> 
> John Stanton <[EMAIL PROTECTED]> wrote:
>> I received an email promoting a DeviceSQL web presentation.  It 
>> specifically targets Sqlite and promises 5X performance.
>> 
> 
> If you view their web presentation and/or try out Encirq's
> products, I would be very interested to hear your impressions.
> Even better would be if you could blog about it.
> 
> Encirq has for years been running Google Adsense ads claiming
> to be 20x faster than SQLite.  (Dunno why they have now reduced
> that claim to 5x faster.)  But I have never yet seen an
> independent confirmation of this.  Nor even have I been able
> to find anybody who is actually using DeviceSQL in a product.
> Web searches turn up nothing but marketing literature coming
> directly or indirectly from Encirq.  Some independent analysis
> (regardless of whether it is favorable or unfavorable to SQLite)
> would be appreciated.
> 
> My understanding of DeviceSQL is:
> 
>*  It is NOT transactional.  There is no such thing as ROLLBACK.
>*  If you lose power during a write, your database is toast.
>*  If your database schema changes, you have to recompile
>   your application.
>*  The database file format changes depending on the schema.
>*  DeviceSQL is not a general-purpose database engine.  You
>   compile SQL statements into C code on a development
>   workstation, then compile the C code for your embedded
>   device.
> 
> I can imagine circumstances where the DeviceSQL approach,
> while much less flexible and forgiving than SQLite, might
> be a better way to go, depending on what you are trying to
> do.  But I have not gotten good vibes from Encirq as a 
> company.  And I have no idea how reliable the DeviceSQL 
> product is.  I would really appreciate your thoughts on 
> that subject.
> 
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/DeviceSQL-tp14297970p14327299.html
Sent from the SQLite mailing list archive at Nabble.com.


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

Re: [sqlite] create table with datatype = DATE

[John Stanton]

The type DATE is a declared type, not an actual type and has no effect u 
nless your code specifically picks it out as a declared type.


To do what you want use a trigger on insert and update the date field 
with datetime('now');


Joanne Pham wrote:

Hi All,
I create the table as :
create table test (name varchar(30), createDate DATE default 
DATETIME('NOW'));
but I got the error message. I want to have the default as now if it is not 
specify.
Thanks in advance,
Joanne


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs



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

[sqlite] create table with datatype = DATE

[Joanne Pham]

Hi All,
I create the table as :
create table test (name varchar(30), createDate DATE default 
DATETIME('NOW'));
but I got the error message. I want to have the default as now if it is not 
specify.
Thanks in advance,
Joanne


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

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

[steveweick]

oops, fingers are moving faster than the brain :-)  of course, you are right,
Dennis.

Steve

Dennis Cote wrote:
> 
> steveweick wrote:
>> the tests were done
>> using Windows XP SP2 and Linux FC5 on a 3GHz P4 with 1MB
>>   
> 
> That must be very slow. ;-)
> 
> I'm sure you meant 1GB for windows XP.
> 
> Dennis Cote
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Improving-performance-of-SQLite.-Anyone-heard-of-DeviceSQL--tp14280006p14325082.html
Sent from the SQLite mailing list archive at Nabble.com.


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

RE: [sqlite] Testing SQLite

[Brown, Daniel]

Ah, I just figured out why I thought I've not got the tests.  I've been
using the pre-processed C code version of SQLite (as I'm working in
Visual Studio on Windows), I guess I'll need to get the full SQLite
package building with make then to get the test functionality instead of
using the pre-processed source?

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: 13 December 2007 10:14
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Testing SQLite

"Brown, Daniel" <[EMAIL PROTECTED]> wrote:
> Morning List,
> 
> I've just started experimenting with SQLite to see if I can replace 
> our current custom embedded database solution with it and trying to 
> port SQLite to some of our embedded platforms. Are there are any 
> testing frameworks (unit tests would be great) for SQLite?  I'd like 
> to be able to automatically verify I've not broken anything in my 
> porting or tinkering,
> 
> I've had a look round the website but I've not managed to find 
> anything there.
> 

Something like 65% of the SQLite source code in the standard download is
devoted exclusively to testing.  Only 35% of the code actually becomes
part of a production build.

The test scripts are written in TCL.  You'll need to have a TCL
implementation available for your target in order to run the tests.
Assuming you have TCL installed, just do:

 make fulltest

and a bunch of tests will run that given on the order of 98% test
coverage.

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



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

Re: [sqlite] SQLite and Columnar Databases

[drh]

Dennis Cote <[EMAIL PROTECTED]> wrote:
> 
> Richard,
> 
> How was the current default page size of 1K determined? Was there any 
> testing to see if larger page sizes would be beneficial for general use? 
> Or is it just a historical circumstance (i.e. an arbitrarily chosen 
> value that has been maintained for backwards compatability)?
> 
> I'm thinking the "usual" case may be changing as available storage gets 
> larger, and users start storing more and larger BLOBs  in the database.
> 

Experiments with version 2.0.0 shows that 1KiB was fastest
on Linux.

Since version 3.5.0, SQLite will sometimes choose a larger
default page size.  The size choosen is the largest of:

* 1024 bytes
* The "sector size" as reported by the VFS layer
* The largest size that can be written atomically

In the current implementations for win32 and unix, the
sector size is hard-coded to 512.  (This may need to change
in the future with newer 2048-byte sector disk drives.)
And both interfaces assume writes are never atomic.  So
the default still ends up being 1024.  But the mechanism
is in place to choose different page sizes automatically.
Some embedded device makers with custom VFS layers use
this mechanism already.

Note that a page is the minimum unit of I/O for SQLite.
So increasing the page size from 1024 to 4096 means that
4 times as many bytes have to be written in order to make
a 1-byte change to a single record.

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



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

[sqlite] sqlite3_exec() and callback

[Tom Parke]

>From what I think I have learned so far, if I call sqlite3_exec() giving
it a call back function and a select * from tbl, I do not have any
direct access to the column values other that the text representation in
the values char array from within the callback()?

 

This leads me to the question, how is the data actually stored in the
file?  

Or

If I use a sqlite3_prepare_v2() and sqlite3_step() and int myvar =
sqlite3_column_int(), am I getting a converted text string?

 

Thanks, I am still trying to get my arms around SQLite.

Tom

 

[sqlite] Convert readonly database file to :memory: ?

[Thomas Heller]

[Has also been posted to the pysqlite-users mailing list]

I have a sqlite database in a readonly file.
Is it possible to create a :memory: database and quickly initialize it
from the file?

Thanks,
Thomas


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

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

[Dennis Cote]

steveweick wrote:

the tests were done
using Windows XP SP2 and Linux FC5 on a 3GHz P4 with 1MB
  


That must be very slow. ;-)

I'm sure you meant 1GB for windows XP.

Dennis Cote

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

Re: [sqlite] SQLite and Columnar Databases

[Dennis Cote]

[EMAIL PROTECTED] wrote:


Whenever you start a new transaction, SQLite has to
allocate and clear a bitmap used to record which
pages have changes in the database file.  The size
of this bitmap is proportional to the size of the
database file.  The size of the bitmap is 256 bytes
per megabyte of database file assuming a 1KiB page
size.  That translates into about 52MB for a 200GiB
database.  This is doable, but probably not optimal.

Methods for reducing the memory requirements of this
bitmap (so that it is proportional to the size of the
change rather than the size of the database) have been
proposed, but we have not taken any steps toward
implementing them since the current approach works
well enough.  But as people start to push SQLite 
into the 100GiB size and beyond, we will likely revisit

that decision.

  


Richard,

How was the current default page size of 1K determined? Was there any 
testing to see if larger page sizes would be beneficial for general use? 
Or is it just a historical circumstance (i.e. an arbitrarily chosen 
value that has been maintained for backwards compatability)?


I'm thinking the "usual" case may be changing as available storage gets 
larger, and users start storing more and larger BLOBs  in the database.


Dennis Cote


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

Re: [sqlite] SQLite and Columnar Databases

[Dennis Cote]

Samuel R. Neff wrote:

That also brings up the simple solution in that if you know you're db is
going to be in the 200GiB range, declare a larger page size before creating
the DB.

  


My thoughts exactly.

Dennis Cote






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

Re: [sqlite] Casting bug

[Dennis Cote]

[EMAIL PROTECTED] wrote:

T <[EMAIL PROTECTED]> wrote:
  

select cast( 141.70 * 100 as integer)


gives the incorrect 14169



14169 is the correct answer, believe it or not.  There is
no such thing as the number 141.70 in a 64-bit IEEE float.  
The closest you can get is


  141.68863131622783839702606201171875

which is what SQLite uses internally.  If you multiple
that value by 100 and take the integer part, you are
left with 14169.

 
  

but this workaround:

select cast( round(141.70 * 100, 0) as integer)

gives the correct: 14170

Now the question is, does this fix the problem, or only for some  
numbers?





This should fix your problem.  The round() function takes
the nearest integer rather than simply truncating the fractional
part.

  


So will the simpler alternative I suggested earlier without the all the 
overhead of the round function.


select cast(141.70 * 100 + 0.5 as integer)

By adding 0.5 (which can be expressed exactly as a floating point 
number) to the value you change the value so that any original value 
with a fractional part greater than or equal to 0.5 will have its 
integer part increased to the correct value before the conversion to 
integer truncates the unwanted fractional part.


141.70  is actually 141.6999... which is multiplied by 100 to give 
14169.999..., after adding 0.5 you have 14170.4999...

which is truncated to 14170 by the cast.

HTH
Dennis Cote







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

Re: [sqlite] Should the next release be 3.5.4 or 3.6.0?

[Dennis Cote]

[EMAIL PROTECTED] wrote:

The current algorithm goes like this:


   (1)  If an ORDER BY term is a constant integer k
then sort by the k-th column of the result set.

   (2)  If an ORDER BY term is a simple identifer
(like "x", not "x.y" and not "x.y.z") and if
there if the k-th column uses that same identifer
as an AS alias, the sort by the k-th column.

   (3)  Otherwise, evaluate the expression which is
in the ORDER BY term and sort by the resulting
value.

For a compound query, the expression in step (3) must
exactly match one of the result columns.  Also, the
three steps are attempted first on the left-most SELECT.
If there is a match, the process stops there.  If no
match is found, the next SELECT to the right is tried.
This repeats as necessary until a match is found or
until you run out of SELECT statement in which case there
is an error.

This algorithm differs from all prior versions of SQLite
(1.0.0 through 3.5.3) by the addition of step (2).
Adding step (2) brings SQLite much closer to the SQL
standard.  I believe that SQLite is now a superset of
the SQL standard.  


SQL has no concept of step (3).  
I believe it does. It calls such columns extended sort keys. The users 
result table is extended by adding these columns to produce an 
intermediate result table which is sorted as usual, and then it removes 
those columns from the result table that is returned.

And
in a compound query, SQL only looks at the left-most
SELECT and does not fail over to SELECT statements to
the right looking for a match.  But these changes can
be considered extensions.

  

Or failures to produce the required diagnostics. :-)


The revised algorithm is mostly compatible with the
way SQLite has always operated before.  But there
are a few obscure corner cases where there is a difference.
An example of the difference is the following:

CREATE TABLE a(x,y);
INSERT INTO a VALUES(1,8);
INSERT INTO a VALUES(9,2);

SELECT x AS y FROM a ORDER BY y;

In older versions of SQLite, the SELECT statement above
would return 9, 1 since the ORDER BY term evaluated to
the expression a.y by rule (3)  In the next release, 
because of the addition of rule (2) above, the result 
will be 1, 9.


My question to the community is this:  Are these
differences sufficient to justify going with version
3.6.0 in the next release?  Or can we call the change
a "bug fix" and number the next release 3.5.4?

Other information to consider:

   *  We do not have a lot of time to debate the merits
  of this change since we need to get out a release
  to fix critical bug #2832.
  
I would say it is bug fix. The previous versions executed the queries 
incorrectly.

   *  We have taken no steps toward fixing GROUP BY.
  If I got ORDER BY wrong, I'm guessing GROUP BY
  is wrong too.

  
And you should probably have another bug fix version release when these 
bugs are fixed as well.


That's my two cents.
Dennis Cote

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

RE: [sqlite] Should the next release be 3.5.4 or 3.6.0?

[Samuel R. Neff]

3.5.4

---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Thursday, December 13, 2007 11:41 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Should the next release be 3.5.4 or 3.6.0?

Ticket number #2822

  http://www.sqlite.org/cvstrac/tktview?tn=2822

has provoked extensive changes to the way SQLite handles
ORDER BY clauses.  The current algorithm goes like this:


...

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


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

RE: [sqlite] Should the next release be 3.5.4 or 3.6.0?

[Mike Marshall]

Seems like a 3.5.4 to me

Mike


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

RE: [sqlite] SQLite and Columnar Databases

[Samuel R. Neff]

That also brings up the simple solution in that if you know you're db is
going to be in the 200GiB range, declare a larger page size before creating
the DB.

Sam


---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Thursday, December 13, 2007 12:21 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite and Columnar Databases

Joe Wilson <[EMAIL PROTECTED]> wrote:

... The size of the bitmap is 256 bytes
per megabyte of database file assuming a 1KiB page
size.  That translates into about 52MB for a 200GiB
database.  ...

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




-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

Re: [sqlite] Testing SQLite

[drh]

"Brown, Daniel" <[EMAIL PROTECTED]> wrote:
> Morning List,
> 
> I've just started experimenting with SQLite to see if I can replace our
> current custom embedded database solution with it and trying to port
> SQLite to some of our embedded platforms. Are there are any testing
> frameworks (unit tests would be great) for SQLite?  I'd like to be able
> to automatically verify I've not broken anything in my porting or
> tinkering, 
> 
> I've had a look round the website but I've not managed to find anything
> there.
> 

Something like 65% of the SQLite source code in the standard
download is devoted exclusively to testing.  Only 35% of the
code actually becomes part of a production build.

The test scripts are written in TCL.  You'll need to have a
TCL implementation available for your target in order to run
the tests.  Assuming you have TCL installed, just do:

 make fulltest

and a bunch of tests will run that given on the order of 98%
test coverage.

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


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

RE: [sqlite] SQLite and Columnar Databases

[Joe Wilson]

The reason why I asked is that I haven't had much luck with sqlite3 
performance for databases larger than the size of RAM on my machine
regardless of PRAGMA settings.

Maybe querying speed is fine for multi gigabyte database files, but 
INSERT speed into tables with multiple indexes is slow when you exceed 
the RAM threshold, so I did not pursue it.

I guess the answer is to get more RAM - it's cheap.
...and use a 64 bit OS.

High speed non-volatile RAM drives will be commonplace soon enough 
and alleviate this problem.

--- Tom Briggs <[EMAIL PROTECTED]> wrote:
>Heh, no, I've never tried, but I don't see much reason why I
> couldn't.  I was just trying to make the point that labeling SQLite as
> "good ... for smaller databases" was not a slight. 
> 
>-T
> 
> > -Original Message-
> > From: Joe Wilson [mailto:[EMAIL PROTECTED] 
> > Sent: Thursday, December 13, 2007 11:51 AM
> > To: sqlite-users@sqlite.org
> > Subject: RE: [sqlite] SQLite and Columnar Databases
> > 
> > --- Tom Briggs <[EMAIL PROTECTED]> wrote:
> > >For clarity, my definition of small is about 200GB, so I'm not
> > > selling SQLite short here... 
> > 
> > Are you able to get decent performance out of sqlite3 for a 
> > 200GB database?
> > 
> > How much RAM do you have on such a machine?




  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


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

[sqlite] Testing SQLite

[Brown, Daniel]

Morning List,

I've just started experimenting with SQLite to see if I can replace our
current custom embedded database solution with it and trying to port
SQLite to some of our embedded platforms. Are there are any testing
frameworks (unit tests would be great) for SQLite?  I'd like to be able
to automatically verify I've not broken anything in my porting or
tinkering, 

I've had a look round the website but I've not managed to find anything
there.

Thanks in advance,

Daniel Brown 
Software Engineer

"The best laid schemes o' mice an' men, gang aft agley"

Re: [sqlite] SQLite and Columnar Databases

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> --- Tom Briggs <[EMAIL PROTECTED]> wrote:
> >For clarity, my definition of small is about 200GB, so I'm not
> > selling SQLite short here... 
> 
> Are you able to get decent performance out of sqlite3 for a 200GB database?
> 
> How much RAM do you have on such a machine?
> 

Whenever you start a new transaction, SQLite has to
allocate and clear a bitmap used to record which
pages have changes in the database file.  The size
of this bitmap is proportional to the size of the
database file.  The size of the bitmap is 256 bytes
per megabyte of database file assuming a 1KiB page
size.  That translates into about 52MB for a 200GiB
database.  This is doable, but probably not optimal.

Methods for reducing the memory requirements of this
bitmap (so that it is proportional to the size of the
change rather than the size of the database) have been
proposed, but we have not taken any steps toward
implementing them since the current approach works
well enough.  But as people start to push SQLite 
into the 100GiB size and beyond, we will likely revisit
that decision.

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



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

RE: [sqlite] SQLite and Columnar Databases

[Tom Briggs]

   Heh, no, I've never tried, but I don't see much reason why I
couldn't.  I was just trying to make the point that labeling SQLite as
"good ... for smaller databases" was not a slight. 

   -T

> -Original Message-
> From: Joe Wilson [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, December 13, 2007 11:51 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] SQLite and Columnar Databases
> 
> --- Tom Briggs <[EMAIL PROTECTED]> wrote:
> >For clarity, my definition of small is about 200GB, so I'm not
> > selling SQLite short here... 
> 
> Are you able to get decent performance out of sqlite3 for a 
> 200GB database?
> 
> How much RAM do you have on such a machine?
> 
> 
>   
> __
> __
> Looking for last minute shopping deals?  
> Find them fast with Yahoo! Search.  
> http://tools.search.yahoo.com/newsearch/category.php?category=shopping
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 
> 

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

[sqlite] Running multiple SQLite processes

[kenmor]

Hi Everyone,

We are seeing the following strange behaviour with SQLite.

Our environment is as follows


1. SQLite 3.5.3
2. Using MEMORY DB
3. Many databases
4. Sun Solaris machine T2000 (32 cores and 16G memory)
5. A single DB table with only 2 entries (so size is not an issue here!)
6. A production - NOT a debug version of the library.


TEST 1

A single process. We continually hammer the database with the same query.
We find a row entry in an average of 3ms.
The max response time is approx. 4ms to 5ms.

TEST 2

We run 8 processes (each has its own DB). 
The average response time on ALL of the databases increases to 5ms
However the max reponse time has increased to 30ms to 40ms.


It appears that some resource is being shared between the processes.

Can anyone advise us on what resources could be shared and how the problem
could be avoided.

Any help would be gratefully appreciated



-- 
View this message in context: 
http://www.nabble.com/Running-multiple-SQLite-processes-tp14320370p14320370.html
Sent from the SQLite mailing list archive at Nabble.com.


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

RE: [sqlite] Should the next release be 3.5.4 or 3.6.0?

[Fred Williams]

If you suspect "Group By" also may be broken, why not to an interim "bug
fix" release and then do the version number change when both "Order By"
and "Group By" are fixed?  I seem to remember instances where both Order
BY and Group By have given me "unexpected" results.  But then again, my
logical thinking is not on Mr. Spock's level, unfortunately.

Fred

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Thursday, December 13, 2007 10:41 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Should the next release be 3.5.4 or 3.6.0?
>
>
> Ticket number #2822
>
>   http://www.sqlite.org/cvstrac/tktview?tn=2822
>
> has provoked extensive changes to the way SQLite handles
> ORDER BY clauses.  The current algorithm goes like this:
>
>
>(1)  If an ORDER BY term is a constant integer k
> then sort by the k-th column of the result set.
>
>(2)  If an ORDER BY term is a simple identifer
> (like "x", not "x.y" and not "x.y.z") and if
> there if the k-th column uses that same identifer
> as an AS alias, the sort by the k-th column.
>
>(3)  Otherwise, evaluate the expression which is
> in the ORDER BY term and sort by the resulting
> value.
>
> For a compound query, the expression in step (3) must
> exactly match one of the result columns.  Also, the
> three steps are attempted first on the left-most SELECT.
> If there is a match, the process stops there.  If no
> match is found, the next SELECT to the right is tried.
> This repeats as necessary until a match is found or
> until you run out of SELECT statement in which case there
> is an error.
>
> This algorithm differs from all prior versions of SQLite
> (1.0.0 through 3.5.3) by the addition of step (2).
> Adding step (2) brings SQLite much closer to the SQL
> standard.  I believe that SQLite is now a superset of
> the SQL standard.  SQL has no concept of step (3).  And
> in a compound query, SQL only looks at the left-most
> SELECT and does not fail over to SELECT statements to
> the right looking for a match.  But these changes can
> be considered extensions.
>
> The revised algorithm is mostly compatible with the
> way SQLite has always operated before.  But there
> are a few obscure corner cases where there is a difference.
> An example of the difference is the following:
>
> CREATE TABLE a(x,y);
> INSERT INTO a VALUES(1,8);
> INSERT INTO a VALUES(9,2);
>
> SELECT x AS y FROM a ORDER BY y;
>
> In older versions of SQLite, the SELECT statement above
> would return 9, 1 since the ORDER BY term evaluated to
> the expression a.y by rule (3)  In the next release,
> because of the addition of rule (2) above, the result
> will be 1, 9.
>
> My question to the community is this:  Are these
> differences sufficient to justify going with version
> 3.6.0 in the next release?  Or can we call the change
> a "bug fix" and number the next release 3.5.4?
>
> Other information to consider:
>
>*  We do not have a lot of time to debate the merits
>   of this change since we need to get out a release
>   to fix critical bug #2832.
>
>*  We have taken no steps toward fixing GROUP BY.
>   If I got ORDER BY wrong, I'm guessing GROUP BY
>   is wrong too.
>
> Thanks for your input.
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
>


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

Re: [sqlite] Should the next release be 3.5.4 or 3.6.0?

[Dustin Bruzenak]

I'd vote 3.5.4 as well.

[EMAIL PROTECTED] wrote:

Ticket number #2822

  http://www.sqlite.org/cvstrac/tktview?tn=2822

has provoked extensive changes to the way SQLite handles
ORDER BY clauses.  The current algorithm goes like this:


   (1)  If an ORDER BY term is a constant integer k
then sort by the k-th column of the result set.

   (2)  If an ORDER BY term is a simple identifer
(like "x", not "x.y" and not "x.y.z") and if
there if the k-th column uses that same identifer
as an AS alias, the sort by the k-th column.

   (3)  Otherwise, evaluate the expression which is
in the ORDER BY term and sort by the resulting
value.

For a compound query, the expression in step (3) must
exactly match one of the result columns.  Also, the
three steps are attempted first on the left-most SELECT.
If there is a match, the process stops there.  If no
match is found, the next SELECT to the right is tried.
This repeats as necessary until a match is found or
until you run out of SELECT statement in which case there
is an error.

This algorithm differs from all prior versions of SQLite
(1.0.0 through 3.5.3) by the addition of step (2).
Adding step (2) brings SQLite much closer to the SQL
standard.  I believe that SQLite is now a superset of
the SQL standard.  SQL has no concept of step (3).  And
in a compound query, SQL only looks at the left-most
SELECT and does not fail over to SELECT statements to
the right looking for a match.  But these changes can
be considered extensions.

The revised algorithm is mostly compatible with the
way SQLite has always operated before.  But there
are a few obscure corner cases where there is a difference.
An example of the difference is the following:

CREATE TABLE a(x,y);
INSERT INTO a VALUES(1,8);
INSERT INTO a VALUES(9,2);

SELECT x AS y FROM a ORDER BY y;

In older versions of SQLite, the SELECT statement above
would return 9, 1 since the ORDER BY term evaluated to
the expression a.y by rule (3)  In the next release, 
because of the addition of rule (2) above, the result 
will be 1, 9.


My question to the community is this:  Are these
differences sufficient to justify going with version
3.6.0 in the next release?  Or can we call the change
a "bug fix" and number the next release 3.5.4?

Other information to consider:

   *  We do not have a lot of time to debate the merits
  of this change since we need to get out a release
  to fix critical bug #2832.

   *  We have taken no steps toward fixing GROUP BY.
  If I got ORDER BY wrong, I'm guessing GROUP BY
  is wrong too.

Thanks for your input.

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


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


  



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

RE: [sqlite] SQLite and Columnar Databases

[Joe Wilson]

--- Tom Briggs <[EMAIL PROTECTED]> wrote:
>For clarity, my definition of small is about 200GB, so I'm not
> selling SQLite short here... 

Are you able to get decent performance out of sqlite3 for a 200GB database?

How much RAM do you have on such a machine?


  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

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

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

[John Stanton]

[EMAIL PROTECTED] wrote:

Ion Silvestru <[EMAIL PROTECTED]> wrote:

SW: Richard,  We have written to you directly before to ask you to stop the
FUD and incorrect statements, and you have chosen to continue. I suggest you
not waste everyone's time by circulating deliberately misleading
information.

I think you are very aggressive and I think you must apologise to, not
only Richard, but to us (just see previous messages about DeviceSQL,
full of suppositions).



Thanks for posting, Ion.  I too found Steve's remarks to be
rather insolent.  But I was just going to let it go.  Seeing
your response was an encouragement to me since it shows me
that I am not the only one who feels that way.  Thanks!

Unfortunately, Steve Weick might not see your comment
since he appears to have unsubscribed from the mailing list
immediately after sending his inflammatory missive.


These were no "FUD and incorrect statements", nor "misleading
information", these were only suppositions, and this is because it's
hard to find real technical information or specifications on DeviceSQL, only
marketing information. Maybe DeviceSQL is a good product, but absence
of real info and abundance of marketing make us think and suppose
various things (just see previous messages).

All of us are waiting for what Richard stated:
"If you view their web presentation and/or try out Encirq's
products, I would be very interested to hear your impressions.
Even better would be if you could blog about it."

Even better if all of us can have access to this web presentation, to
find out maybe more technical info about DeviceSQL.

Any way, thank you.



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


Methinks he doth protest too much.

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

Re: [sqlite] Casting bug

[drh]

T <[EMAIL PROTECTED]> wrote:
> 
> >> select cast( 141.70 * 100 as integer)
> 
> gives the incorrect 14169

14169 is the correct answer, believe it or not.  There is
no such thing as the number 141.70 in a 64-bit IEEE float.  
The closest you can get is

  141.68863131622783839702606201171875

which is what SQLite uses internally.  If you multiple
that value by 100 and take the integer part, you are
left with 14169.

 
> but this workaround:
> 
> select cast( round(141.70 * 100, 0) as integer)
> 
> gives the correct: 14170
> 
> Now the question is, does this fix the problem, or only for some  
> numbers?
> 

This should fix your problem.  The round() function takes
the nearest integer rather than simply truncating the fractional
part.

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


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

[sqlite] Re: Casting bug

[Igor Tandetnik]

T  wrote:

I shouldn't have complicated my question by using the word "round".
It's not the rounding that I'm trying to do. The input values are
already rounded to two decimal places (cents of the dollar).


Realize that floating point numbers are stored by computers in binary, 
not in decimal. 141.7 cannot be exactly represented in binary (just like 
1/3 cannot be exactly represented in decimal). So instead, a nearest 
representable number is stored, and this number happens to be slightly 
less than 141.7 - say 141.698. Hence your rounding problems.


For this reason, it is unwise to manipulate monetary values as floating 
point numbers. It is much better to keep them as integers expressed in 
pennies, or even finer-grained (many financial calculations are required 
to be accurate to 0.0001 of a dollar, so it is common to scale by a 
factor of 1).



I am
trying to output fixed decimal places. So 141.7 should output as
141.70, and 23 should output as 23.00 etc.


Do it in your application, after retrieving the raw values from SQL 
database. SQL is ill-suited for string formatting.



I think I actually found a fix to the apparent bug in cast


There is no bug in CAST, just your misunderstanding of how it works.


select cast( round(141.70 * 100, 0) as integer)

gives the correct: 14170

Now the question is, does this fix the problem, or only for some
numbers?


This would work for all but very large numbers - those approaching or 
greater than 10^13 or so. For such large numbers, there are not enough 
bits in the double's mantissa to keep absolute error below 0.01.


Igor Tandetnik 



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

Re: [sqlite] Should the next release be 3.5.4 or 3.6.0?

[Jeff Hamilton]

My vote is for 3.5.4.

-Jeff

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

RE: [sqlite] How to get number of records in record set

[Tom Parke]

Thanks, that makes sense. 
Tom

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Thursday, December 13, 2007 7:50 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to get number of records in record set

"Tom Parke" <[EMAIL PROTECTED]> wrote:
> If I prepare() a sql stmt  SELECT * FROM tbl WHERE col = n:
> 
> How do I find out how many records were selected before calling
step()?
> 

The technically correct response to your question is that the
answer is always zero.  Nothing gets selected - the database
file remains unread - until you call sqlite3_step().  But
that probably is not what you were asking, huh.  :-)

If you want to find out how many records would be selected
by such a query, you can do:

SELECT count(*) FROM tbl WHERE col=n;

Or you can do:

cnt = 1;
while( sqlite3_step()==SQLITE_ROW ) cnt++;

There is no way for the database engine to determine
how many records are going to match your search
condition without actually doing the search and
counting the hits.

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



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

[sqlite] Should the next release be 3.5.4 or 3.6.0?

[drh]

Ticket number #2822

  http://www.sqlite.org/cvstrac/tktview?tn=2822

has provoked extensive changes to the way SQLite handles
ORDER BY clauses.  The current algorithm goes like this:


   (1)  If an ORDER BY term is a constant integer k
then sort by the k-th column of the result set.

   (2)  If an ORDER BY term is a simple identifer
(like "x", not "x.y" and not "x.y.z") and if
there if the k-th column uses that same identifer
as an AS alias, the sort by the k-th column.

   (3)  Otherwise, evaluate the expression which is
in the ORDER BY term and sort by the resulting
value.

For a compound query, the expression in step (3) must
exactly match one of the result columns.  Also, the
three steps are attempted first on the left-most SELECT.
If there is a match, the process stops there.  If no
match is found, the next SELECT to the right is tried.
This repeats as necessary until a match is found or
until you run out of SELECT statement in which case there
is an error.

This algorithm differs from all prior versions of SQLite
(1.0.0 through 3.5.3) by the addition of step (2).
Adding step (2) brings SQLite much closer to the SQL
standard.  I believe that SQLite is now a superset of
the SQL standard.  SQL has no concept of step (3).  And
in a compound query, SQL only looks at the left-most
SELECT and does not fail over to SELECT statements to
the right looking for a match.  But these changes can
be considered extensions.

The revised algorithm is mostly compatible with the
way SQLite has always operated before.  But there
are a few obscure corner cases where there is a difference.
An example of the difference is the following:

CREATE TABLE a(x,y);
INSERT INTO a VALUES(1,8);
INSERT INTO a VALUES(9,2);

SELECT x AS y FROM a ORDER BY y;

In older versions of SQLite, the SELECT statement above
would return 9, 1 since the ORDER BY term evaluated to
the expression a.y by rule (3)  In the next release, 
because of the addition of rule (2) above, the result 
will be 1, 9.

My question to the community is this:  Are these
differences sufficient to justify going with version
3.6.0 in the next release?  Or can we call the change
a "bug fix" and number the next release 3.5.4?

Other information to consider:

   *  We do not have a lot of time to debate the merits
  of this change since we need to get out a release
  to fix critical bug #2832.

   *  We have taken no steps toward fixing GROUP BY.
  If I got ORDER BY wrong, I'm guessing GROUP BY
  is wrong too.

Thanks for your input.

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


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

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

[steveweick]

Hi Sam,

re your points below:

1. I think I said "innovative", not "revolutionary". The scheme involves
using "dirty bits" rather than a log to record the transactional state of a
page.

2. We plan on publishing all the details of the benchmarks in a few days.
But to answer your question about platforms and tests, the tests were done
using Windows XP SP2 and Linux FC5 on a 3GHz P4 with 1MB, Linux 2.4.31-a9-3
on a 200MHz ARM9 with 64MB, and Freescale Embedded Linux 2.6.16.11 on a 466
MHz 5200 with 256MB. The tests were done with relatively simple tables that
ranged in size from 5000 to 1M records. Inserts, deletes, updates, and
various selects were tested against the SQLite prepare/execute interface and
the DeviceSQL compiled and interpreted interfaces.

3. I'm not surprised to hear that SQLite is substantially faster than MSSQL.
We haven't tested MSSQL, but it makes sense, because both SQLite and
DeviceSQL do not pay the MSSQL price of client server interfaces. That said,
the real question comes down whether SQLite will meet your application
performance needs.  If it does, great. By contrast, DeviceSQL customers have
very stringent performance requirements (some even have a "performance
budget") and often view performance as a critical element in achieving
competitive advantage. If your application doesn't fit that mold, then
SQLite is the right choice for you. SQLite performance is poor compared with
that of DeviceSQL, not poor in general. Our customers have confirmed that  a
number of times.

4. I'm not a big fan of DeviceSQL marketing to date either. I think that's
going to change soon... watch this space.


Best regards,

Steve


Steve,

I found the information you posted to be a good contrast and would love to
learn more, but you didn't include any technical details.  You said you have
atomic commits without a rollback journal and instead use some revolutionary
new way of doing commits.  You said DeviceSQL performs significantly faster
than SQLite, can you show what tests you ran, on what platforms, and your
exact results?  I was particularly skeptical when you said "SQLite
performance, while poor on larger PCs" because in our own testing we've
found SQLite to be 4 times faster than MSSQL after we migrated.  If you're
finding SQLite performance to be poor at all, then most likely your
developers are doing something wrong in testing SQLite which of course would
invalidate your comparison to DeviceSQL.

In short, can you provide more details?  Personally I don't install demo
software just to learn what I should be able to get from the company website
(which I would hope is truly technical details, not just marketing fluff).

I tried searching online for information about DeviceSQL but pretty much
everything I found was regurgitation of marketing data from your company.
The only really compelling thing I found was this.

http://www.google.com/trends?q=sqlite%2C+devicesql

Best regards,

Sam

-
-- 
View this message in context: 
http://www.nabble.com/Improving-performance-of-SQLite.-Anyone-heard-of-DeviceSQL--tp14280006p14319009.html
Sent from the SQLite mailing list archive at Nabble.com.


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

Re: [sqlite] Casting bug

[T]

Hi Mike,


well, first of all you must have a typo, since the sql you show will
return 14170, not 0.69 or anything like it.


Yes, typo, sorry. The short version returns 14169 but should give  
14170, as per my correction a minute ago.



however, one thing springs out:

Total * 100 + 100 is wrong IMHO, unless you are looking for CEILING  
functionallity.
"round" would need : total * 100 +50 (which rounds to nearest  
integer at

two decimal points, not to the nearest LARGER integer,
which is what your sample does)


I shouldn't have complicated my question by using the word "round".  
It's not the rounding that I'm trying to do. The input values are  
already rounded to two decimal places (cents of the dollar). I am  
trying to output fixed decimal places. So 141.7 should output as  
141.70, and 23 should output as 23.00 etc.


I think I actually found a fix to the apparent bug in cast, by  
rounding the product to zero decimal places, even though the result  
should be the same:



select cast( 141.70 * 100 as integer)


gives the incorrect 14169

but this workaround:

select cast( round(141.70 * 100, 0) as integer)

gives the correct: 14170

Now the question is, does this fix the problem, or only for some  
numbers?


Tom


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

Re: [sqlite] Casting bug

[T]

oops, sorry, slight correction:


When I try:

select cast( 141.70 * 100 as integer)

I get 14169, but should get 14170


Tom


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

Re: [sqlite] sqlite3_prepare / sqlite3_step not working

[CAVALO SCHMIDT]

Thank you, it now works.

2007/12/13, Dennis Cote <[EMAIL PROTECTED]>:
>
> CAVALO SCHMIDT wrote:
> > In the following simple C code (in Console program):
> >
> > sqlite3 *db;
> > int ret = sqlite3_open("dict.db", );
> > sqlite3_stmt *stmt;
> > char sql[256];
> > sprintf(sql, "%s", "select * from a where a = 'key1'");
> > int rc = sqlite3_prepare(db, sql, 0, , 0);
> > while(sqlite3_step(stmt) == SQLITE_ROW) {
> >printf( (char *)sqlite3_column_text(stmt, 1) );
> >   };
> > sqlite3_finalize(stmt);
> > sqlite3_close(db);
> >
> > I must have made a very simple beginner mistake, because an error
> happens.
> > sqlite3_prepare returns SQLITE_OK, but sqlite3_step returns 21
> > = SQLITE_MISUSE.
> > Can anyone tell me what I did wrong? It happens with any database I test
> it
> > with. Just for information, sqlite3_exec works perfectly.
> >
> >
> >
>
> You have called sqlite3_prepare incorrectly. The third argument is the
> length of the sql string if it is positive. To have sqlite scan until
> the end of the string you need to pass a negative number. Tyr this
> instead.
>
>int rc = sqlite3_prepare(db, sql, -1, , 0);
>
> HTH
> Dennis Cote
>
>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

Re: [sqlite] How to get number of records in record set

[drh]

"Tom Parke" <[EMAIL PROTECTED]> wrote:
> If I prepare() a sql stmt  SELECT * FROM tbl WHERE col = n:
> 
> How do I find out how many records were selected before calling step()?
> 

The technically correct response to your question is that the
answer is always zero.  Nothing gets selected - the database
file remains unread - until you call sqlite3_step().  But
that probably is not what you were asking, huh.  :-)

If you want to find out how many records would be selected
by such a query, you can do:

SELECT count(*) FROM tbl WHERE col=n;

Or you can do:

cnt = 1;
while( sqlite3_step()==SQLITE_ROW ) cnt++;

There is no way for the database engine to determine
how many records are going to match your search
condition without actually doing the search and
counting the hits.

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


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

Re: [sqlite] Casting bug

[Dennis Cote]

Cariotoglou Mike wrote:
 
 Total * 100 + 100 is wrong IMHO, unless you are looking for CEILING
functionallity. 
"round" would need : total * 100 +50 (which rounds to nearest integer at

two decimal points, not to the nearest LARGER integer,
which is what your sample does)

  

Shouldn't that be

total * 100 + 0.5

He is trying to round the last digit of the result by casting to integer 
before he extracts those digits using substr.


HTH
Dennis Cote

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

[sqlite] How to get number of records in record set

[Tom Parke]

If I prepare() a sql stmt  SELECT * FROM tbl WHERE col = n:

How do I find out how many records were selected before calling step()?


Tom

 

 

Re: [sqlite] sqlite3_prepare / sqlite3_step not working

[Dennis Cote]

CAVALO SCHMIDT wrote:

In the following simple C code (in Console program):

sqlite3 *db;
int ret = sqlite3_open("dict.db", );
sqlite3_stmt *stmt;
char sql[256];
sprintf(sql, "%s", "select * from a where a = 'key1'");
int rc = sqlite3_prepare(db, sql, 0, , 0);
while(sqlite3_step(stmt) == SQLITE_ROW) {
   printf( (char *)sqlite3_column_text(stmt, 1) );
  };
sqlite3_finalize(stmt);
sqlite3_close(db);

I must have made a very simple beginner mistake, because an error happens.
sqlite3_prepare returns SQLITE_OK, but sqlite3_step returns 21
= SQLITE_MISUSE.
Can anyone tell me what I did wrong? It happens with any database I test it
with. Just for information, sqlite3_exec works perfectly.


  


You have called sqlite3_prepare incorrectly. The third argument is the 
length of the sql string if it is positive. To have sqlite scan until 
the end of the string you need to pass a negative number. Tyr this instead.


   int rc = sqlite3_prepare(db, sql, -1, , 0);

HTH
Dennis Cote




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

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

[drh]

Ion Silvestru <[EMAIL PROTECTED]> wrote:
> >We wrote Richard back in August to correct his misstatements then. He chose
> >to ignore the letter.
>
> August? We start to discuss about DeviceSQL some days ago, or
> I am wrong?
> 

I have several support customer in Europe who have been
visited by the Encirq sales rep there, trying to get them
to abandon SQLite in favor of DeviceSQL.  The way this
normally happens is that a sales talk is given to the
management.  Then the management goes to their engineers
asking for a comparison of DeviceSQL and SQLite.  The
engineers then come to me for help in defending SQLite.
I respond with a letter outlining the strengths and
weaknesses of each product as known to me.  I am always
very careful to outline the limitations of my knowledge
in these cases and to attempt to give as fair and as
balanced of a comparison as I can.

In one recent episode (prehaps the one that Steve is
referring to) my reply was forwarded to the Encirq sales
rep.  This provoked a vigorous response from Encirq in which
they attempted a point-by-point rebuttal of my letter.

Well, maybe it wasn't quite point-by-point.  They 
did attempted to rebut every good thing I said about
SQLite and every bad thing I said about DeviceSQL,
But they let stand all of the limitations of SQLite 
that I mentioned, as well as those factors I said
were favorable to DeviceSQL.

Did I ignore this letter?  Yes and no. I did read it. But
the overall impression I got from reading it was that the
customer can cure cancer and bring about world peace if
only they would switch to using DeviceSQL.  I tend to 
discount such information heavily. So, I suppose Steve
is correct, in a manner of speaking, in saying that I
ignored the letter.

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


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

RE: [sqlite] Casting bug

[Cariotoglou Mike]

well, first of all you must have a typo, since the sql you show will
return 14170, not 0.69 or anything like it.
however, one thing springs out:
 
 Total * 100 + 100 is wrong IMHO, unless you are looking for CEILING
functionallity. 
"round" would need : total * 100 +50 (which rounds to nearest integer at
two decimal points, not to the nearest LARGER integer,
which is what your sample does)


> -Original Message-
> From: T [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, December 13, 2007 4:27 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Casting bug
> 
> When I try:
> 
> select cast( 141.70 * 100 as integer)
> 
> I get 0.69, but should get 0.70
> 
> What's the problem? Seems like a bug.
> 
> I tried some other numbers in place of 141.70, and they 
> worked OK, though I imagine there are others that have the 
> bug that I just haven't tried.
> 
> The above is the isolated buggy part of a formula I use to 
> round of real amounts to two fixed decimal places:
> 
> select
>   substr(
>   '  $' || cast( Total as integer ) || '.'
|| substr( cast( Total * 100 + 100 as 
> integer ), -2, 2 )
>   , -10, 10
>   )
> from ( select 141.70 as Total )
> ;
> 
> which gives $141.69 but should give $141.70
> 
> Is there a more reliable way, using only SQLite?
> 
> Thanks,
> Tom
> 
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 
> 
> 


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

Re: [sqlite] Casting bug

[Gerry Snyder]

T wrote:

When I try:

select cast( 141.70 * 100 as integer)

I get 0.69, but should get 0.70



Assuming you mean you got 14169, maybe you should look at 
http://sqlite.org/faq.html#q16


HTH,

Gerry

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

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

[Ion Silvestru]

>We wrote Richard back in August to correct his misstatements then. He chose
>to ignore the letter.
August? We start to discuss about DeviceSQL some days ago, or
I am wrong?


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

[sqlite] Casting bug

[T]

When I try:

select cast( 141.70 * 100 as integer)

I get 0.69, but should get 0.70

What's the problem? Seems like a bug.

I tried some other numbers in place of 141.70, and they worked OK,  
though I imagine there are others that have the bug that I just  
haven't tried.


The above is the isolated buggy part of a formula I use to round of  
real amounts to two fixed decimal places:


select
substr(
'  $' || cast( Total as integer ) || '.'
|| substr( cast( Total * 100 + 100 as integer ), -2, 2 )
, -10, 10
)
from ( select 141.70 as Total )
;

which gives $141.69 but should give $141.70

Is there a more reliable way, using only SQLite?

Thanks,
Tom


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

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

[Samuel R. Neff]

Steve,

I found the information you posted to be a good contrast and would love to
learn more, but you didn't include any technical details.  You said you have
atomic commits without a rollback journal and instead use some revolutionary
new way of doing commits.  You said DeviceSQL performs significantly faster
than SQLite, can you show what tests you ran, on what platforms, and your
exact results?  I was particularly skeptical when you said "SQLite
performance, while poor on larger PCs" because in our own testing we've
found SQLite to be 4 times faster than MSSQL after we migrated.  If you're
finding SQLite performance to be poor at all, then most likely your
developers are doing something wrong in testing SQLite which of course would
invalidate your comparison to DeviceSQL.

In short, can you provide more details?  Personally I don't install demo
software just to learn what I should be able to get from the company website
(which I would hope is truly technical details, not just marketing fluff).

I tried searching online for information about DeviceSQL but pretty much
everything I found was regurgitation of marketing data from your company.
The only really compelling thing I found was this.

http://www.google.com/trends?q=sqlite%2C+devicesql

Best regards,

Sam

---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: steveweick [mailto:[EMAIL PROTECTED] 
Sent: Thursday, December 13, 2007 8:59 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Improving performance of SQLite. Anyone heard of
DeviceSQL?


oops, I guess I need to get used to this message list protocol.

First let me apologize for letting Richard get me mad. Most of my friends
would describe me as one of the most laid back people they know. Why am I
mad you ask?

We wrote Richard back in August to correct his misstatements then. He chose
to ignore the letter. Moreover he (or anyone) has been able to download our
product with all of its documentation since February or March of this year.
We encourage people to do so, because using the product is far more
convincing and informative than trying to plow through a bunch of marketing
blather.

By the way, I don't know where Richard got the stuff about me leaving the
mailing list... it never happened.

Steve



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

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

[drh]

steveweick <[EMAIL PROTECTED]> wrote:
> D. Richard Hipp wrote:
> > 
> > Unfortunately, Steve Weick might not see your comment
> > since he appears to have unsubscribed from the mailing list
> > immediately after sending his inflammatory missive.
> > 

Hmmm...  Further digging prompted by the quoted surprise
reply shows Steve's post coming through nabble.com.  So
Steve didn't unsubscribe, he never subscribed in the first
place.  [EMAIL PROTECTED] is on the mailing list, not
[EMAIL PROTECTED]  And apparently Steve is viewing through
nabble.

Never heard of nabble.com before  Good to know.

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


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

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

[steveweick]

oops, I guess I need to get used to this message list protocol.

First let me apologize for letting Richard get me mad. Most of my friends
would describe me as one of the most laid back people they know. Why am I
mad you ask?

We wrote Richard back in August to correct his misstatements then. He chose
to ignore the letter. Moreover he (or anyone) has been able to download our
product with all of its documentation since February or March of this year.
We encourage people to do so, because using the product is far more
convincing and informative than trying to plow through a bunch of marketing
blather.

By the way, I don't know where Richard got the stuff about me leaving the
mailing list... it never happened.

Steve



steveweick wrote:
> 
> 
> 
> D. Richard Hipp wrote:
>> 
>> Ion Silvestru <[EMAIL PROTECTED]> wrote:
>>> >SW: Richard,  We have written to you directly before to ask you to stop
>>> the
>>> >FUD and incorrect statements, and you have chosen to continue. I
>>> suggest you
>>> >not waste everyone's time by circulating deliberately misleading
>>> >information.
>>>
>>> I think you are very aggressive and I think you must apologise to, not
>>> only Richard, but to us (just see previous messages about DeviceSQL,
>>> full of suppositions).
>>>
>> 
>> Thanks for posting, Ion.  I too found Steve's remarks to be
>> rather insolent.  But I was just going to let it go.  Seeing
>> your response was an encouragement to me since it shows me
>> that I am not the only one who feels that way.  Thanks!
>> 
>> Unfortunately, Steve Weick might not see your comment
>> since he appears to have unsubscribed from the mailing list
>> immediately after sending his inflammatory missive.
>> 
>>> 
>>> These were no "FUD and incorrect statements", nor "misleading
>>> information", these were only suppositions, and this is because it's
>>> hard to find real technical information or specifications on DeviceSQL,
>>> only
>>> marketing information. Maybe DeviceSQL is a good product, but absence
>>> of real info and abundance of marketing make us think and suppose
>>> various things (just see previous messages).
>>> 
>>> All of us are waiting for what Richard stated:
>>> "If you view their web presentation and/or try out Encirq's
>>> products, I would be very interested to hear your impressions.
>>> Even better would be if you could blog about it."
>>> 
>>> Even better if all of us can have access to this web presentation, to
>>> find out maybe more technical info about DeviceSQL.
>>> 
>>> Any way, thank you.
>>> 
>> 
>> --
>> D. Richard Hipp <[EMAIL PROTECTED]>
>> 
>> 
>> -
>> To unsubscribe, send email to [EMAIL PROTECTED]
>> -
>> 
>> 
>> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Improving-performance-of-SQLite.-Anyone-heard-of-DeviceSQL--tp14280006p14316335.html
Sent from the SQLite mailing list archive at Nabble.com.


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

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

[steveweick]

D. Richard Hipp wrote:
> 
> Ion Silvestru <[EMAIL PROTECTED]> wrote:
>> >SW: Richard,  We have written to you directly before to ask you to stop
>> the
>> >FUD and incorrect statements, and you have chosen to continue. I suggest
>> you
>> >not waste everyone's time by circulating deliberately misleading
>> >information.
>>
>> I think you are very aggressive and I think you must apologise to, not
>> only Richard, but to us (just see previous messages about DeviceSQL,
>> full of suppositions).
>>
> 
> Thanks for posting, Ion.  I too found Steve's remarks to be
> rather insolent.  But I was just going to let it go.  Seeing
> your response was an encouragement to me since it shows me
> that I am not the only one who feels that way.  Thanks!
> 
> Unfortunately, Steve Weick might not see your comment
> since he appears to have unsubscribed from the mailing list
> immediately after sending his inflammatory missive.
> 
>> 
>> These were no "FUD and incorrect statements", nor "misleading
>> information", these were only suppositions, and this is because it's
>> hard to find real technical information or specifications on DeviceSQL,
>> only
>> marketing information. Maybe DeviceSQL is a good product, but absence
>> of real info and abundance of marketing make us think and suppose
>> various things (just see previous messages).
>> 
>> All of us are waiting for what Richard stated:
>> "If you view their web presentation and/or try out Encirq's
>> products, I would be very interested to hear your impressions.
>> Even better would be if you could blog about it."
>> 
>> Even better if all of us can have access to this web presentation, to
>> find out maybe more technical info about DeviceSQL.
>> 
>> Any way, thank you.
>> 
> 
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Improving-performance-of-SQLite.-Anyone-heard-of-DeviceSQL--tp14280006p14316330.html
Sent from the SQLite mailing list archive at Nabble.com.


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

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

[drh]

Ion Silvestru <[EMAIL PROTECTED]> wrote:
> >SW: Richard,  We have written to you directly before to ask you to stop the
> >FUD and incorrect statements, and you have chosen to continue. I suggest you
> >not waste everyone's time by circulating deliberately misleading
> >information.
>
> I think you are very aggressive and I think you must apologise to, not
> only Richard, but to us (just see previous messages about DeviceSQL,
> full of suppositions).
>

Thanks for posting, Ion.  I too found Steve's remarks to be
rather insolent.  But I was just going to let it go.  Seeing
your response was an encouragement to me since it shows me
that I am not the only one who feels that way.  Thanks!

Unfortunately, Steve Weick might not see your comment
since he appears to have unsubscribed from the mailing list
immediately after sending his inflammatory missive.

> 
> These were no "FUD and incorrect statements", nor "misleading
> information", these were only suppositions, and this is because it's
> hard to find real technical information or specifications on DeviceSQL, only
> marketing information. Maybe DeviceSQL is a good product, but absence
> of real info and abundance of marketing make us think and suppose
> various things (just see previous messages).
> 
> All of us are waiting for what Richard stated:
> "If you view their web presentation and/or try out Encirq's
> products, I would be very interested to hear your impressions.
> Even better would be if you could blog about it."
> 
> Even better if all of us can have access to this web presentation, to
> find out maybe more technical info about DeviceSQL.
> 
> Any way, thank you.
> 

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


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

RE: [sqlite] SQLite and Columnar Databases

[Tom Briggs]

   Based on my experience with SQLite, it would be a huge undertaking to
re-work it to use column-oriented storage.  And I don't think it would
really fit with SQLite's goal, either; column oriented databases are
best suited to aggregate queries against large amounts of data, while
SQLite is best at transactional operations against smaller amounts of
data.

   For clarity, my definition of small is about 200GB, so I'm not
selling SQLite short here... Don't everyone get all upset at me. :)

   -T

> -Original Message-
> From: Yuvaraj Athur Raghuvir [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, December 12, 2007 6:12 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] SQLite and Columnar Databases
> 
> Hello,
> 
> There seems to be a high interest in columnar databases recently.
> 
> Is there any plan of supporting data organization as a 
> columnar database in
> SQLite? What are the challenges here?
> 
> Regards,
> Yuva
> 

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

RE: [sqlite] SQLite and Columnar Databases

[Tom Briggs]

 

> Something I will say about this, for people who don't know, is that 
> this columnar thing is strictly an implementation detail.  While 

   I think that this is an oversimplification.  That's somewhat like
saying that the way you use a sledge hammer is no different than how you
use a claw hammer, because they're both hammers.  Anyone who tries to
hang a picture with a sledge hammer will be rather unhappy with your
advice.

   Though at some level how the data is stored is indeed an
implementation detail, to take full advantage of the fact that it is
requires re-thinking schema design and in some cases even query design.
See
http://www.fulltablescan.com/index.php?/archives/44-Compression-as-Norma
lization...-Kinda.html for more info.

   -T

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

[sqlite] sqlite3_prepare / sqlite3_step not working

[CAVALO SCHMIDT]

Salutations,

In the following simple C code (in Console program):

sqlite3 *db;
int ret = sqlite3_open("dict.db", );
sqlite3_stmt *stmt;
char sql[256];
sprintf(sql, "%s", "select * from a where a = 'key1'");
int rc = sqlite3_prepare(db, sql, 0, , 0);
while(sqlite3_step(stmt) == SQLITE_ROW) {
   printf( (char *)sqlite3_column_text(stmt, 1) );
  };
sqlite3_finalize(stmt);
sqlite3_close(db);

I must have made a very simple beginner mistake, because an error happens.
sqlite3_prepare returns SQLITE_OK, but sqlite3_step returns 21
= SQLITE_MISUSE.
Can anyone tell me what I did wrong? It happens with any database I test it
with. Just for information, sqlite3_exec works perfectly.

Thank you in advance.

[sqlite] ANN: SQLite Maestro 7.12 released

[SQL Maestro Group]

Hi!

SQL Maestro Group announces the release of SQLite Maestro 7.12, a powerful 
Windows GUI solution for SQLite administration and database development.


http://www.sqlmaestro.com/products/sqlite/maestro/

Please note that before December 31 you can purchase SQLite Maestro and our 
other products and bundles with a 25% discount. Merry Christmas!


New features
=

1. SQLite Maestro has been successfully tested with the latest SQLite server 
versions. The latest available server library (3.5.3) is now included into 
the installation package.


2. Support for attached databases has been implemented: now you can specify 
such databases in the Database Profile Editor to use their data in your SQL 
queries.


3. Reindex and Vacuum commands have been added for tables and indexes.

4. Graphical Database Designer - a case tool for managing your database in a 
few mouse clicks. This piece of our software is intended for creation of 
physical Entity Relationship Diagrams that represent tables in your SQLite 
databases.


5. Database explorer: now it is possible to group database profiles 
according to your needs.


6. The SQL Generator tool has been implemented. Use this feature to produce 
various SQL scripts (CREATE, DROP, SELECT, INSERT, etc) for the selected 
object. The result script can be saved to a file, copied to clipboard, and 
opened in SQL Editor or SQL Script Editor.


7. Data Grid: the notification pane is now displayed after simple mode 
activation (filtering, grouping and sorting features are not supported in 
this mode). All the related settings can be customized in the Options 
dialog.


8. Data Grid: the Generate Query command has been added to the Table / View 
Editor. Use this command to copy current filter conditions to the SQL Editor 
window as an SQL query. It is also possible to copy filter condition to 
clipboard as WHERE clause using grid's popup menu.


9. Data Grid: now it is possible to encrypt the contents of the selected 
cell by the md5 algorithm (the corresponding item has been added to grid's 
popup menu).


10. Object editors, SQL Editor, Database Designer, BLOB Viewer and other 
database-dependent windows: only connected databases are now displayed in 
the drop-down list.


11. Some other minor improvements and corrections.

Full press release:
http://www.sqlmaestro.com/news/company/4698/

Background information:

SQL Maestro Group is engaged in developing complete database admin and 
management tools for MySQL, Oracle, MS SQL Server, PostgreSQL, SQLite, 
Firebird and MaxDB providing the highest performance, scalability and 
reliability to meet the requirements of today's database applications.


Sincerely yours,
The SQL Maestro Group Team
http://www.sqlmaestro.com 



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

Re: [sqlite] Re: How to check if the table has some specific values

[Trevor Talbot]

I think Joanne's example may be simplified, and the question was
really more general...


On 12/12/07, Joanne Pham <[EMAIL PROTECTED]> wrote:

> Basiclly there is no SQL logic in SQLite.

Right, there's nothing like T-SQL. Even in other databases, most
procedural logic along IF..THEN lines is done with a dedicated
language used for functions or stored procedures, not part of the
primary SQL interface. SQLite does have a limited form of procedural
logic in its trigger statements.

Keep in mind that because SQLite is an embedded database, it's
considered normal to do such logic within the application as
necessary. There isn't any overhead for a network protocol or similar,
and you generally don't need to deal with arbitrary applications
accessing your database, so there's less gain for pushing things into
the database interface itself. After all, the database is inside your
application already.

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

Re: [sqlite] Re: How to check if the table has some specific values

[Kees Nuyt]

Hi Joanne,

On Wed, 12 Dec 2007 16:20:17 -0800 (PST), Joanne Pham
<[EMAIL PROTECTED]> wrote:

>Hi Igor,
>Thanks for the response.
>Basiclly there is no SQL logic in SQLite.

I'm not sure what you mean here.

>I would like to check if the database version is xyz
>then I will have different action and if then database
>version is abc then I will have different action. 
>So SQLite doesn't allow this luxury.

Sure it does, it even gives you a choice:
- use the schema version SQLite automatically maintains.
- set and use your own version number;
The only thing you can't do is use the version directly in SQL,
that is:
SELECT * FROM test1 WHERE ID = (PRAGMA schema_version);
is not a valid statement.

Both techniques are demonstrated below.

sqlite_version():3.5.3

CREATE TABLE test1 (
  ID INTEGER PRIMARY KEY NOT NULL,
  t1_name text
);
PRAGMA schema_version;
: 1
PRAGMA user_version;
: 0

PRAGMA user_version = 6001;

CREATE INDEX idx_t1_name ON test1 (t1_name);

PRAGMA schema_version;
: 2
PRAGMA user_version;
: 6001

CREATE TABLE test2 (
  ID INTEGER PRIMARY KEY NOT NULL,
  t2_name text
);

PRAGMA schema_version;
: 3
PRAGMA user_version;
: 6001

PRAGMA user_version = 6002;
INSERT INTO test1 VALUES (1,'alpha');
INSERT INTO test2 VALUES (2,'beta');

PRAGMA schema_version;
: 3
PRAGMA user_version;
: 6002

>Thanks,
>JP

I hope this helps,
-- 
  (  Kees Nuyt
  )
c[_]

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