RE: [sqlite] Insert statement taking too long

[Robert Simpson]

You need to create an index on the columns you're joining.  Otherwise I
believe 100,000,000 rows (10k x 10k) in table 2 will be scanned while SQLite
looks for matches against the 10,000 rows in table 1.

> -Original Message-
> From: Unit 5 [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, November 08, 2006 10:47 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Insert statement taking too long
> 
> Hello,
> 
> I am seeing an interesting performance issue with
> INSERT statements.  I am using Sqlite 3.3.5 on a
> Windows XP box. Here is a brief summary of the
> situation:
> 
> Insert statements that require no join are quite fast:
> a) I create an empty table (drop it first if it
> exists).
> b) INSERT ... SELECT ... FROM another-table WHERE ...
> 
> 
> But, if the Insert statement requires a join, the
> performance degrades drastically:
> 
> a) I create an empty table (drop it first if it
> exists).
> b) INSERT ... SELECT ... FROM table-1 JOIN table-2
> WHERE ...
> 
> Even when the two joined tables have 10,000 records
> each and those records match one to one, the query
> goes from taking a second or so in the first case to
> over 30-40 minutes in the second case.  The processing
> is CPU intensive and pretty much locks down the PC
> during this process.
> 
> Is this a common experience or do I need to play
> around with the configuration options? 
> 
>  
> 
> 
> 
>  
> __
> __
> Want to start your own business?
> Learn how on Yahoo! Small Business.
> http://smallbusiness.yahoo.com/r-index
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 



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

[sqlite] Insert statement taking too long

[Unit 5]

Hello,

I am seeing an interesting performance issue with
INSERT statements.  I am using Sqlite 3.3.5 on a
Windows XP box. Here is a brief summary of the
situation:

Insert statements that require no join are quite fast:
a) I create an empty table (drop it first if it
exists).
b) INSERT ... SELECT ... FROM another-table WHERE ...


But, if the Insert statement requires a join, the
performance degrades drastically:

a) I create an empty table (drop it first if it
exists).
b) INSERT ... SELECT ... FROM table-1 JOIN table-2
WHERE ...

Even when the two joined tables have 10,000 records
each and those records match one to one, the query
goes from taking a second or so in the first case to
over 30-40 minutes in the second case.  The processing
is CPU intensive and pretty much locks down the PC
during this process.

Is this a common experience or do I need to play
around with the configuration options? 

 



 

Want to start your own business?
Learn how on Yahoo! Small Business.
http://smallbusiness.yahoo.com/r-index

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

[sqlite] Fw: Why can't I delete tmp.db after close sqlite?

[LuYanJun]

if the file is new for opening, then success for delete, in other hand, if the 
file is old for opening, then failed for delete. example as follow:
 sqlite3_open("tmp.db", ); //tmp.db is exist

- Original Message - 
From: LuYanJun 
To: sqlite-users sqlite.org 
Sent: Thursday, November 09, 2006 11:11 AM
Subject: Why can't I delete tmp.db after close sqlite?


Hi all
Why can't I delete tmp.db after close sqlite?

sqlite3* mpDB;
  int nRet = sqlite3_open("tmp.db", );
  if (nRet != SQLITE_OK)
  {
   const char* szError = sqlite3_errmsg(mpDB);
   printf("open error:%s\n",szError);
  }
  sqlite3_close(mpDB);
  printf("close!\n");
  if ( ! ::DeleteFile(tmp.db ))
  {
   printf("delete error!\n");
  }
  else
   printf("ok delete!!!\n");
  return 0; 

Best regards

[sqlite] Why can't I delete tmp.db after close sqlite?

[LuYanJun]

Hi all
Why can't I delete tmp.db after close sqlite?
 
sqlite3* mpDB;
  int nRet = sqlite3_open("tmp.db", );
  if (nRet != SQLITE_OK)
  {
   const char* szError = sqlite3_errmsg(mpDB);
   printf("open error:%s\n",szError);
  }
  sqlite3_close(mpDB);
  printf("close!\n");
  if ( ! ::DeleteFile(tmp.db ))
  {
   printf("delete error!\n");
  }
  else
   printf("ok delete!!!\n");
  return 0; 

Best regards

Re: [sqlite] Date data type

[Dennis Cote]

Lloyd wrote:


I would like to know one more thing, can I use these date and time
functions with comparison operators? Will they return the correct result
or as per the string comparison rules?

  


Lloyd,

You can use either the string comparisons with the text versions of the 
dates or numeric comparisons with the julian day numbers (floating point 
number of days since the julian epoch started). Both will give the 
correct sort orders. This is one of the best features of the ISO-8601 
format date and time strings, lexicographic order is the same as date order.


If you store text date strings in your database you can do this:

   select * from t where date < '2006-07-04 18:00:10';

If you store the julian day numbers in your database you can do this:

   select * from t where date < julianday('2006-07-04 18:00:10');

HTH
Dennis Cote

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

RE: [sqlite] Dropping Tables

[Rich Shepard]

On Wed, 8 Nov 2006, Griggs, Donald wrote:


See:
http://www.sqlite.org/lang_droptable.html
Just take the syntax link for most any syntax question:
 http://www.sqlite.org/lang.html


  Thanks, Donald.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.(TM)|Accelerator
 Voice: 503-667-4517  Fax: 503-667-8863

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

RE: [sqlite] Dropping Tables

[Griggs, Donald]

Regarding:  "Since I cannot learn how to drop a table from a database by
referring to Owens' book, I hope that someone here can point me in the
right direction."

See:
http://www.sqlite.org/lang_droptable.html 

Just take the syntax link for most any syntax question:
  http://www.sqlite.org/lang.html





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

[sqlite] Dropping Tables

[Rich Shepard]

  I need to alter a table by adding a numeric primary key field. Since I
cannot do this with sqlite3's implementation of ALTER, I want to drop the
table and re-import a corrected version with the name. But, I cannot find a
command to drop a _table_, only the entire database. This surprises me.

  Since I cannot learn how to drop a table from a database by referring to
Owens' book, I hope that someone here can point me in the right direction.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.(TM)|Accelerator
 Voice: 503-667-4517  Fax: 503-667-8863

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

Re: [sqlite] PHP5 with SQLite3

[Lloyd Thomas]

- Original Message - 
From: "Rúben Lício" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, November 08, 2006 1:03 PM
Subject: [sqlite] PHP5 with SQLite3


Are you connecting correctly to the database. Which version of sqlite3 was 
the database created in. I beleive php5.1 is version 3.2.8



Hi,

I'm trying to use PHP5 with SQLite 3, but it's not working.

I see then the native PHP only suporte SQLite 2.8, but i can compile last
version of php with SQLite 3 suport.

O compile last PHP version with this line:
make clean && ./configure --prefix=/usr/local/php5 --enable-pdo
--with-sqlite=shared --with-pdo-sqlite=shared --with-zlib
--enable-track-vars --with-apxs2=/usr/local/apache2/bin/apxs
--enable-sqlite-utf8 && make && make install

phpinfo tell-me that it is ok with SQLite 3 suport. But when I try to
execute query, i have that exception message:
'PDOException' with message 'SQLSTATE[HY000]: General error: 1 SQL logic
error or missing database' in ...

Anybody know how to correct that problem?

ty

Ruben

--
Linux user #433535
Linux because we are freedon.




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

Re: [sqlite] Date and time

[John Stanton]

Your machine has the timezone recorded and localtime uses that to make 
the appropriate correction from UTC (Greenwich or Zulu) time.


This approach makes the time correct if you are operating across time 
zones.  It is the way your Unixepoch time expects to work.  A machine 
which gives the correct time in New York gives incorrect time in Seattle 
unless you use UCT and apply the New York and Seattle time zones.


If you use a Unix machine you will see an environment variable TZ with a 
value like  TZ=EET-10EETDT-11,J298,J90 which encodes the time zone, the 
offset from UCT, the daylight savings zone and offset and the julian 
days of the switch in and out of summer time.


When a user logs in their profile can override the default time zone.

Windows does a similar thing when you set up the OS and define the time 
zone by selecting it on a map.


Lloyd wrote:

Hi,

I felt sqlite's date and time functions are very useful and suits my
needs.

But still am not clear about one thing. I gave the following query

select datetime(1162961284,'unixepoch');

2006-11-08 04:48:04

The date is correct but the time is 5 hours lagging. So for local time
correction I gave the following query

 select datetime(1162961284,'unixepoch','localtime');

 2006-11-08 10:18:04

The result is correct.

I would like to know how sqlite is performing the localtime correction. 


My problem is, I am getting these time stamps from network packets, And
I want to  display it accurately. (as in the localtime).

Which way can I prefer ?

Thanks,
  Lloyd.



__
Scanned and protected by Email scanner

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




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

Re: [sqlite] weird (and dangerous) bug in Microsoft Compiler

[John Stanton]

What would you expect 9.95 to round to?  Here are the simple rounding 
rules -


Rule 1- if the remainder beyond the last digit to be reported is less 
than 5, drop the last digit. Rounding to one decimal place, the number 
5.3467 becomes 5.3.


Rule 2-if the remainder is greater than 5, increase the final digit by 
1. The number 5.798 becomes 5.8 if rounding to 1 digit.


Rule 3- To prevent rounding bias, if the remainder is exactly 5, then 
round the last digit to the closest even number.Thus the number 3.55 
(rounded to 1 digit) would be 3.6 (rounding up) and the number 6.450 
would round to 6.4 (rounding down)if rounding to 1 decimal.


Your floating point 9.95 could therefore round to 9.9 or 10.0.  You 
would expect 9.950 to round to 9.9 and 9.956 to round to 10.0.


When using floating point numbers you must always expect them to never 
have a precise value and use inequalities not equalities in processing 
them and to be cognisant of orders of accuracy.


mike cariotoglou wrote:
hello to all. I wish to report some quirks I discovered with floating 
point and
ROUND() function, while looking into a problem reported by my 
development team wrt

to sqlite handling of above function.

first of all, let me state that I understand the issues with inexact 
floating point
representations, so let us not go into discussions about which is the 
"correct"
interpretation of 9.95. however, a given implementation should at least 
be consistent

wrt to this.

my tests have shown that, for the following statement:

select ROUND(9.95,1)

the command-line sqlite3.exe (v 3.3.8) returns 10.0
OTOH, the compiled DLL that can be downloaded from the sqlite site returns
9.9 !
(as a reference, both MS SQL and ORACLE return 10.0)

What gives ? is the result dependent on compilation options, and if so, 
which ?


trying to investigate this issue, I compiled the dll locally (3.3.8), 
using Microsoft

Visual Studio 2005, and came across a beauty :

the dll compiled with MSVC, using default options more or less, gives 
0.0 (yes, zero)


I pulled my hair out over this for some hours, and discovered that :

a. the floating point optimizer in MSVC has a bug, which is triggered 
when you use the
optimization setting /fp:precice (which is the default), and gives the 
above erroneous

result.

b. you can get the correct behavior by speifying optimization as /fp:strict

Clearly, this is a problem with the microsoft compiler. however, trying 
to avoid future
issues, I suggest that somebody which is conversant in C (which is not 
me), try to find
the sqlite3 source construct that triggers this bug, and re-writes the 
code so that it
is not optimization-sensitive. I tracked the problem down somewhere in 
the vxprintf function
in the print.c source file. It is quite difficult to pin the problem 
down, because:


the problem goes away when you build in debug mode, because 
optimizations are turned off.
Even if you force some optimizations by hand, in debug mode, the 
compiler will start
using floating-point stack and registers for variables. the debugger, 
however,does not seem
to understand about these optimizations, so, inspecting the variables 
gives you the wrong
results. (how people manage to work in this environment is beyond me, my 
good ole delphi

never does thigs like this!)

Could someone please verify my findings, especially the one about the 
correct setting for
MSVC to compile the dll ? and, assuming the above is verified, I think 
we should add
a warning in the WIKI for poor souls who will try to compile using MS 
Visual Studio 2005

in the future !

PS
when testing, pls note that the value 9.95 is a "magic" value, due to 
the way the
code is written in print.c. AFAIK it is the only value manifesting the 
compiler bug,
probably due to the way that the imput to ROUND() is compared to the 
value "10.0" in

various points in the code.



- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

RE: [sqlite] Using BLOBs in where fields

[Gabriel Cook]

Gabriel Cook wrote:
> Thanks for your reply. 
>
> However, I can't normalize the data in the blob, basically, its an
arbitrary
> frame of data. I don't know what's in it or how its formatted at design
> time. 
>
> I'm ok with the table scan, IF I there is any way I can get the query to
> work. I'll have to scan the data even if I move it back into a flat file.
>
> BTW I understand what your saying about normalizing the data, and in
general
> I agree with you. 
>
> So, is there any way to of thing with a BLOB?
>
>   
>> WHERE substr(data, 1, 1) == x'bc'
>> 
Gabe,

The substr() function doesn't work because it stops at the first 0 byte 
in the blob, but it is pretty easy to write your own user defined 
function that would return a sub blob instead of a sub string using the 
C API functions.

Define the function:

void subblob(sqlite3_context* ctx, int argc, sqlite3_value* argv[])
{
const char* blob;
int blen, start, slen, tail;

assert(argc == 3);
blob = (char*)sqlite3_value_blob(argv[0]);
blen = sqlite3_value_bytes(argv[0]);
start = sqlite3_value_int(argv[1]) - 1; /* arg is 1 based */
slen = sqlite3_value_int(argv[2]);
if (start < 0)
start = 0;
tail = blen - start;
if (tail < 0)
tail = 0;
if (tail < slen)
slen = tail;
if (start >= blen)
start = blen - 1;
sqlite3_result_blob(ctx, blob + start, slen, SQLITE_TRANSIENT);
}

Register the function:

int rc = sqlite3_create_function(db, "subblob", 3,
SQLITE_ANY, NULL, subblob, NULL, NULL);
   
Now you can use the function:
  
create table t (
id integer primary key,
data blob
);
select id from t where subblob(data, 100, 4) = X'DEADBEEF';

HTH
Dennis Cote




-
To unsubscribe, send email to [EMAIL PROTECTED]

-


Dennis,

Thanks very much for your help. Works great and is very powerful. That is
exactly what I needed to know.

-Gabe




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

RE: [sqlite] weird (and dangerous) bug in Microsoft Compiler

[Robert Simpson]

I compiled everything from bare source myself.  I maintain the ADO.NET 2.0
wrapper.

> -Original Message-
> From: mike cariotoglou [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, November 08, 2006 9:18 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] weird (and dangerous) bug in Microsoft Compiler
> 
> did you also compile the ado .net wrapper ?
> 
> - Original Message - 
> From: "Robert Simpson" <[EMAIL PROTECTED]>
> To: 
> Sent: Wednesday, November 08, 2006 4:57 PM
> Subject: RE: [sqlite] weird (and dangerous) bug in Microsoft Compiler
> 
> 
> >I tried that same SELECT statement in 3 builds of SQLite on 
> VS2005.  The
> > code was called from C# using the ADO.NET 2.0 wrapper, and 
> I got 10 all 3
> > times.  I tried /fp:fast, /fp:strict and /fp:precise
> >
> > The rest of the build options were:
> >
> >AdditionalOptions="/GS-"
> >Optimization="2"
> >FavorSizeOrSpeed="1"
> >
> > 
> PreprocessorDefinitions="WIN32;NDEBUG;_WINDOWS;_USRDLL;_CRT_SE
> CURE_NO_DEPREC
> > 
> ATE;NO_TCL;THREADSAFE;SQLITE_HAS_CODEC;SQLITE_ENABLE_COLUMN_ME
> TADATA;SQLITE_
> > ENABLE_FTS1"
> >StringPooling="true"
> >ExceptionHandling="0"
> >BufferSecurityCheck="false"
> >EnableFunctionLevelLinking="true"
> >RuntimeTypeInfo="false"
> >
> >
> >> -Original Message-
> >> From: mike cariotoglou [mailto:[EMAIL PROTECTED]
> >> Sent: Wednesday, November 08, 2006 5:27 AM
> >> To: sqlite-users@sqlite.org
> >> Subject: [sqlite] weird (and dangerous) bug in Microsoft Compiler
> >>
> >> hello to all. I wish to report some quirks I discovered with
> >> floating point
> >> and
> >> ROUND() function, while looking into a problem reported by my
> >> development
> >> team wrt
> >> to sqlite handling of above function.
> >>
> >> first of all, let me state that I understand the issues 
> with inexact
> >> floating point
> >> representations, so let us not go into discussions about 
> which is the
> >> "correct"
> >> interpretation of 9.95. however, a given implementation
> >> should at least be
> >> consistent
> >> wrt to this.
> >>
> >> my tests have shown that, for the following statement:
> >>
> >>  select ROUND(9.95,1)
> >>
> >> the command-line sqlite3.exe (v 3.3.8) returns 10.0
> >> OTOH, the compiled DLL that can be downloaded from the sqlite
> >> site returns
> >> 9.9 !
> >> (as a reference, both MS SQL and ORACLE return 10.0)
> >>
> >> What gives ? is the result dependent on compilation options,
> >> and if so,
> >> which ?
> >>
> >> trying to investigate this issue, I compiled the dll locally
> >> (3.3.8), using
> >> Microsoft
> >> Visual Studio 2005, and came across a beauty :
> >>
> >> the dll compiled with MSVC, using default options more or
> >> less, gives 0.0
> >> (yes, zero)
> >>
> >> I pulled my hair out over this for some hours, and 
> discovered that :
> >>
> >> a. the floating point optimizer in MSVC has a bug, which is
> >> triggered when
> >> you use the
> >> optimization setting /fp:precice (which is the default), and
> >> gives the above
> >> erroneous
> >> result.
> >>
> >> b. you can get the correct behavior by speifying optimization
> >> as /fp:strict
> >>
> >> Clearly, this is a problem with the microsoft compiler.
> >> however, trying to
> >> avoid future
> >> issues, I suggest that somebody which is conversant in C
> >> (which is not me),
> >> try to find
> >> the sqlite3 source construct that triggers this bug, and
> >> re-writes the code
> >> so that it
> >> is not optimization-sensitive. I tracked the problem down
> >> somewhere in the
> >> vxprintf function
> >> in the print.c source file. It is quite difficult to pin the
> >> problem down,
> >> because:
> >>
> >> the problem goes away when you build in debug mode, because
> >> optimizations
> >> are turned off.
> >> Even if you force some optimizations by hand, in debug mode,
> >> the compiler
> >> will start
> >> using floating-point stack and registers for variables. 
> the debugger,
> >> however,does not seem
> >> to understand about these optimizations, so, inspecting the
> >> variables gives
> >> you the wrong
> >> results. (how people manage to work in this environment is
> >> beyond me, my
> >> good ole delphi
> >> never does thigs like this!)
> >>
> >> Could someone please verify my findings, especially the 
> one about the
> >> correct setting for
> >> MSVC to compile the dll ? and, assuming the above is
> >> verified, I think we
> >> should add
> >> a warning in the WIKI for poor souls who will try to compile
> >> using MS Visual
> >> Studio 2005
> >> in the future !
> >>
> >> PS
> >> when testing, pls note that the value 9.95 is a "magic"
> >> value, due to the
> >> way the
> >> code is written in print.c. AFAIK it is the only value
> >> 

Re: [sqlite] weird (and dangerous) bug in Microsoft Compiler

[mike cariotoglou]

did you also compile the ado .net wrapper ?

- Original Message - 
From: "Robert Simpson" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, November 08, 2006 4:57 PM
Subject: RE: [sqlite] weird (and dangerous) bug in Microsoft Compiler



I tried that same SELECT statement in 3 builds of SQLite on VS2005.  The
code was called from C# using the ADO.NET 2.0 wrapper, and I got 10 all 3
times.  I tried /fp:fast, /fp:strict and /fp:precise

The rest of the build options were:

   AdditionalOptions="/GS-"
   Optimization="2"
   FavorSizeOrSpeed="1"

PreprocessorDefinitions="WIN32;NDEBUG;_WINDOWS;_USRDLL;_CRT_SECURE_NO_DEPREC
ATE;NO_TCL;THREADSAFE;SQLITE_HAS_CODEC;SQLITE_ENABLE_COLUMN_METADATA;SQLITE_
ENABLE_FTS1"
   StringPooling="true"
   ExceptionHandling="0"
   BufferSecurityCheck="false"
   EnableFunctionLevelLinking="true"
   RuntimeTypeInfo="false"



-Original Message-
From: mike cariotoglou [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 08, 2006 5:27 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] weird (and dangerous) bug in Microsoft Compiler

hello to all. I wish to report some quirks I discovered with
floating point
and
ROUND() function, while looking into a problem reported by my
development
team wrt
to sqlite handling of above function.

first of all, let me state that I understand the issues with inexact
floating point
representations, so let us not go into discussions about which is the
"correct"
interpretation of 9.95. however, a given implementation
should at least be
consistent
wrt to this.

my tests have shown that, for the following statement:

 select ROUND(9.95,1)

the command-line sqlite3.exe (v 3.3.8) returns 10.0
OTOH, the compiled DLL that can be downloaded from the sqlite
site returns
9.9 !
(as a reference, both MS SQL and ORACLE return 10.0)

What gives ? is the result dependent on compilation options,
and if so,
which ?

trying to investigate this issue, I compiled the dll locally
(3.3.8), using
Microsoft
Visual Studio 2005, and came across a beauty :

the dll compiled with MSVC, using default options more or
less, gives 0.0
(yes, zero)

I pulled my hair out over this for some hours, and discovered that :

a. the floating point optimizer in MSVC has a bug, which is
triggered when
you use the
optimization setting /fp:precice (which is the default), and
gives the above
erroneous
result.

b. you can get the correct behavior by speifying optimization
as /fp:strict

Clearly, this is a problem with the microsoft compiler.
however, trying to
avoid future
issues, I suggest that somebody which is conversant in C
(which is not me),
try to find
the sqlite3 source construct that triggers this bug, and
re-writes the code
so that it
is not optimization-sensitive. I tracked the problem down
somewhere in the
vxprintf function
in the print.c source file. It is quite difficult to pin the
problem down,
because:

the problem goes away when you build in debug mode, because
optimizations
are turned off.
Even if you force some optimizations by hand, in debug mode,
the compiler
will start
using floating-point stack and registers for variables. the debugger,
however,does not seem
to understand about these optimizations, so, inspecting the
variables gives
you the wrong
results. (how people manage to work in this environment is
beyond me, my
good ole delphi
never does thigs like this!)

Could someone please verify my findings, especially the one about the
correct setting for
MSVC to compile the dll ? and, assuming the above is
verified, I think we
should add
a warning in the WIKI for poor souls who will try to compile
using MS Visual
Studio 2005
in the future !

PS
when testing, pls note that the value 9.95 is a "magic"
value, due to the
way the
code is written in print.c. AFAIK it is the only value
manifesting the
compiler bug,
probably due to the way that the imput to ROUND() is compared
to the value
"10.0" in
various points in the code.



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






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







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

RE: [sqlite] weird (and dangerous) bug in Microsoft Compiler

[Robert Simpson]

I tried that same SELECT statement in 3 builds of SQLite on VS2005.  The
code was called from C# using the ADO.NET 2.0 wrapper, and I got 10 all 3
times.  I tried /fp:fast, /fp:strict and /fp:precise

The rest of the build options were:

AdditionalOptions="/GS-"
Optimization="2"
FavorSizeOrSpeed="1"
 
PreprocessorDefinitions="WIN32;NDEBUG;_WINDOWS;_USRDLL;_CRT_SECURE_NO_DEPREC
ATE;NO_TCL;THREADSAFE;SQLITE_HAS_CODEC;SQLITE_ENABLE_COLUMN_METADATA;SQLITE_
ENABLE_FTS1"
StringPooling="true"
ExceptionHandling="0"
BufferSecurityCheck="false"
EnableFunctionLevelLinking="true"
RuntimeTypeInfo="false"


> -Original Message-
> From: mike cariotoglou [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, November 08, 2006 5:27 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] weird (and dangerous) bug in Microsoft Compiler
>
> hello to all. I wish to report some quirks I discovered with
> floating point
> and
> ROUND() function, while looking into a problem reported by my
> development
> team wrt
> to sqlite handling of above function.
>
> first of all, let me state that I understand the issues with inexact
> floating point
> representations, so let us not go into discussions about which is the
> "correct"
> interpretation of 9.95. however, a given implementation
> should at least be
> consistent
> wrt to this.
>
> my tests have shown that, for the following statement:
>
>  select ROUND(9.95,1)
>
> the command-line sqlite3.exe (v 3.3.8) returns 10.0
> OTOH, the compiled DLL that can be downloaded from the sqlite
> site returns
> 9.9 !
> (as a reference, both MS SQL and ORACLE return 10.0)
>
> What gives ? is the result dependent on compilation options,
> and if so,
> which ?
>
> trying to investigate this issue, I compiled the dll locally
> (3.3.8), using
> Microsoft
> Visual Studio 2005, and came across a beauty :
>
> the dll compiled with MSVC, using default options more or
> less, gives 0.0
> (yes, zero)
>
> I pulled my hair out over this for some hours, and discovered that :
>
> a. the floating point optimizer in MSVC has a bug, which is
> triggered when
> you use the
> optimization setting /fp:precice (which is the default), and
> gives the above
> erroneous
> result.
>
> b. you can get the correct behavior by speifying optimization
> as /fp:strict
>
> Clearly, this is a problem with the microsoft compiler.
> however, trying to
> avoid future
> issues, I suggest that somebody which is conversant in C
> (which is not me),
> try to find
> the sqlite3 source construct that triggers this bug, and
> re-writes the code
> so that it
> is not optimization-sensitive. I tracked the problem down
> somewhere in the
> vxprintf function
> in the print.c source file. It is quite difficult to pin the
> problem down,
> because:
>
> the problem goes away when you build in debug mode, because
> optimizations
> are turned off.
> Even if you force some optimizations by hand, in debug mode,
> the compiler
> will start
> using floating-point stack and registers for variables. the debugger,
> however,does not seem
> to understand about these optimizations, so, inspecting the
> variables gives
> you the wrong
> results. (how people manage to work in this environment is
> beyond me, my
> good ole delphi
> never does thigs like this!)
>
> Could someone please verify my findings, especially the one about the
> correct setting for
> MSVC to compile the dll ? and, assuming the above is
> verified, I think we
> should add
> a warning in the WIKI for poor souls who will try to compile
> using MS Visual
> Studio 2005
> in the future !
>
> PS
> when testing, pls note that the value 9.95 is a "magic"
> value, due to the
> way the
> code is written in print.c. AFAIK it is the only value
> manifesting the
> compiler bug,
> probably due to the way that the imput to ROUND() is compared
> to the value
> "10.0" in
> various points in the code.
>
>
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 




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

[sqlite] PHP5 with SQLite3

[Rúben Lício]

Hi,

I'm trying to use PHP5 with SQLite 3, but it's not working.

I see then the native PHP only suporte SQLite 2.8, but i can compile last
version of php with SQLite 3 suport.

O compile last PHP version with this line:
make clean && ./configure --prefix=/usr/local/php5 --enable-pdo
--with-sqlite=shared --with-pdo-sqlite=shared --with-zlib
--enable-track-vars --with-apxs2=/usr/local/apache2/bin/apxs
--enable-sqlite-utf8 && make && make install

phpinfo tell-me that it is ok with SQLite 3 suport. But when I try to
execute query, i have that exception message:
'PDOException' with message 'SQLSTATE[HY000]: General error: 1 SQL logic
error or missing database' in ...

Anybody know how to correct that problem?

ty

Ruben

--
Linux user #433535
Linux because we are freedon.

Re: [sqlite] Q about new SQLite API

[Christian Smith]

[EMAIL PROTECTED] uttered:


I'm working on a new API routine for SQLite and I have
questions for the community.

The working name of the new api is sqlite3_prepare_v2().
sqlite3_prepare_v2() works like sqlite3_prepare() in that
it generates a prepared statement in an sqlite3_stmt
structure.  The differences is in the behavior of the
resulting sqlite3_stmt and in particular a difference in
the way sqlite3_step() responds to the sqlite3_stmt.  The
differences are these:

 * You never get an SQLITE_SCHEMA error.  sqlite3_prepare_v2
   retains the original SQL and automatically reprepares and
   rebinds it following a schema change.



Good.




 * sqlite3_step() returns the correct error code right
   away, rather than just returning SQLITE_ERROR and making
   you call sqlite3_reset() to find the true reason for the
   error.








In this way, I am hoping that sqlite3_prepare_v2() will work
around two of the most visible warts in the current API.

QUESTION 1: sqlite3_prepare_v2 is the merely the working name
for the new function.  What should the official name be?
Some possibilities include:

   sqlite3_prepare_ex1
   sqlite3_prepare_ng
   sqlite3_new_prepare
   sqlite3_compile



I'd leave it as sqlite3_prepare, with default as the old behaviour, then 
add a new function to switch an existing sqlite3_stmt to the new 
behaviour, such as:


 #define SQLITE3_STMT_RECOMPILE   (1<<0)
 #define SQLITE3_STMT_STEP_WITH_ERRORCODE (1<<1)
 int sqlite3_stmt_setflags( int flags );

My personal opinion is that functions appended with _ex or 2 or obviously 
extending an older version function looks sloppy. You just have to look at 
Win32 for examples of bad APIs extended. It's not an entirely rationale 
argument, I admit.





QUESTION 2: Are there any other API warts that need to be
worked around that can be fixed by this same change?



I trust that the return codes from sqlite3_step will now be able to be 
arbitrarily extended, to cover such cases as:

http://www.sqlite.org/cvstrac/tktview?tn=1837,2




QUESTION 3: Suppose there is a schema change and the SQL
statement is automatically reprepared. But the schema change
is such that the SQL is no longer valid.  (Perhaps one of the
tables mentioned in a SELECT statement was dropped.) What
error code should sqlite3_step() return in that case?



How about the error code that would be returned from sqlite3_prepare with 
the given SQL.





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



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

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

[sqlite] weird (and dangerous) bug in Microsoft Compiler

[mike cariotoglou]

hello to all. I wish to report some quirks I discovered with floating point 
and
ROUND() function, while looking into a problem reported by my development 
team wrt

to sqlite handling of above function.

first of all, let me state that I understand the issues with inexact 
floating point
representations, so let us not go into discussions about which is the 
"correct"
interpretation of 9.95. however, a given implementation should at least be 
consistent

wrt to this.

my tests have shown that, for the following statement:

select ROUND(9.95,1)

the command-line sqlite3.exe (v 3.3.8) returns 10.0
OTOH, the compiled DLL that can be downloaded from the sqlite site returns
9.9 !
(as a reference, both MS SQL and ORACLE return 10.0)

What gives ? is the result dependent on compilation options, and if so, 
which ?


trying to investigate this issue, I compiled the dll locally (3.3.8), using 
Microsoft

Visual Studio 2005, and came across a beauty :

the dll compiled with MSVC, using default options more or less, gives 0.0 
(yes, zero)


I pulled my hair out over this for some hours, and discovered that :

a. the floating point optimizer in MSVC has a bug, which is triggered when 
you use the
optimization setting /fp:precice (which is the default), and gives the above 
erroneous

result.

b. you can get the correct behavior by speifying optimization as /fp:strict

Clearly, this is a problem with the microsoft compiler. however, trying to 
avoid future
issues, I suggest that somebody which is conversant in C (which is not me), 
try to find
the sqlite3 source construct that triggers this bug, and re-writes the code 
so that it
is not optimization-sensitive. I tracked the problem down somewhere in the 
vxprintf function
in the print.c source file. It is quite difficult to pin the problem down, 
because:


the problem goes away when you build in debug mode, because optimizations 
are turned off.
Even if you force some optimizations by hand, in debug mode, the compiler 
will start
using floating-point stack and registers for variables. the debugger, 
however,does not seem
to understand about these optimizations, so, inspecting the variables gives 
you the wrong
results. (how people manage to work in this environment is beyond me, my 
good ole delphi

never does thigs like this!)

Could someone please verify my findings, especially the one about the 
correct setting for
MSVC to compile the dll ? and, assuming the above is verified, I think we 
should add
a warning in the WIKI for poor souls who will try to compile using MS Visual 
Studio 2005

in the future !

PS
when testing, pls note that the value 9.95 is a "magic" value, due to the 
way the
code is written in print.c. AFAIK it is the only value manifesting the 
compiler bug,
probably due to the way that the imput to ROUND() is compared to the value 
"10.0" in

various points in the code.



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

[sqlite] More recent version with php

[VIGNY Cecilia]

Hi,

Does anybody know if a more recent version of SQLite will be integrated 
soon with php ?


Thanks.


Ce message est protégé par les règles relatives au secret des correspondances. 
Il est donc établi à destination exclusive de son destinataire. Celui-ci peut 
donc contenir des informations confidentielles. La divulgation de ces 
informations est à ce titre rigoureusement interdite. Si vous avez reçu ce 
message par erreur, merci de le renvoyer à l'expéditeur dont l'adresse e-mail 
figure ci-dessus et de détruire le message ainsi que toute pièce jointe.

This message is protected by the secrecy of correspondence rules. Therefore, 
this message is intended solely for the attention of the addressee. This 
message may contain privileged or confidential information, as such the 
disclosure of these informations is strictly forbidden. If, by mistake, you 
have received this message, please return this message to the addressser whose 
e-mail address is written above and destroy this message and all files attached.

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