[sqlite] Alexa Phishing

[Nicolas Jäger]

Le Mon, 19 Oct 2015 08:41:28 +0200,
"Gert Venter"  a ?crit :

> I also received an eMail from alexa within 15 minutes after registering.
> 
> GAV 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

I didn't read so far this discussion... now I don't need to do it! I got the 
pictures... sent for
her iPhone... I blame steve jobs for that!

[sqlite] Simple Math Question

[Tim Streater]

On 22 Oct 2015 at 21:25, Richard A Rousselot  wrote: 

> I fully expected something like this from a fractional number like 1/3 but
> didn't realize that this would be the case if when the digits were explicitly
> stated and they summed to a whole number. I will use a Cast statement to
> resolve.

> On Thu, Oct 22, 2015 at 9:45 PM, Rousselot, Richard A <
> Richard.A.Rousselot at centurylink.com> wrote:
>
>> Doing the following math, why is it that the results are not all
>> returning "yes"?
>>
>> SELECT
>> (9.2+7.9+0+4.0+2.6+1.3),
>> case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else
>> "no" end,
>> (9.2+7.8+0+3.0+1.3+1.7),
>> case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else
>> "no" end,
>> (9.2+7.9+0+1.0+1.3+1.6),
>> case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else

These numbers may be specifiable exactly in decimal to one place of decimals, 
but can they be expressed exactly in binary floating point? If not, then it's 
not gonna work. I first came across this issue 50 years ago when I wrote my 
first program - to calculate square roots using Newton's Method.

--
Cheers  --  Tim

[sqlite] Simple Math Question

[Nicolas Jäger]

Le Thu, 22 Oct 2015 19:45:09 +,
"Rousselot, Richard A"  a ?crit :

> Doing the following math, why is it that the results are not all returning 
> "yes"?
> 
> SELECT
> (9.2+7.9+0+4.0+2.6+1.3),
> case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else "no" 
> end,
> (9.2+7.8+0+3.0+1.3+1.7),
> case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else "no" 
> end,
> (9.2+7.9+0+1.0+1.3+1.6),
> case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else "no" 
> end
> FROM
> sometable;
> 
> Result
> 25.0 no 23.0 yes 21.0 no
> 
> I'm sure this has an obvious answer but it isn't jumping out at me.
> 
> Richard
> 
> This communication is the property of CenturyLink and may contain 
> confidential or privileged
> information. Unauthorized use of this communication is strictly prohibited 
> and may be unlawful.
> If you have received this communication in error, please immediately notify 
> the sender by reply
> e-mail and destroy all copies of the communication and any attachments.
> ___ sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

just for the fun: round-off error...

1/3 has an infinite number of decimal number. meanwhile a computer can only 
store a finite
number of decimal...

this is the story who leads Lorenz to talk about his butterfly and the chaos... 
I do not remember
the whole story but iirc that was when he worked on some numerical weather 
prediction. Data
stored in some magnetic device (sound like science-fiction for someone young 
like me...) and the
data in the RAM haven't the same number of decimal, so the calculus after 
several iterations
gave totaly different results...

regards,
Nicolas J.

n.b. : "meanwhile a computer can only store a finite number of decimal..." same 
in groceries when
you see 98c and you have to pay 1$... thiefs!

[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00

[Marco Turco]

Solved.
At the end I have added the parameter -DSQLITE_THREADSAFE=0 to exclude the 
multithread code from the library 

 Thank you.

Marco

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Clemens 
Ladisch
Sent: Thursday, October 22, 2015 8:51 PM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00

Marco Turco wrote:
> could you please give me the full Bcc32 string you are using ?

That would not be useful for you; I'm using an incompatible calling convention.

Just replace "-tW" with "-tWM".


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Simple Math Question

[Marc L. Allen]

If I recall, he had a printout of the state at each time unit. The state was 
represented as a set of numbers out to 6 decimal points. 

He wanted to rerun part of the simulation, so he entered in those super-precise 
numbers and let them run, but the model quick diverged because those numbers 
just weren't precise enough. 

> On Oct 22, 2015, at 10:56 PM, Nicolas J?ger  
> wrote:
> 
> Le Thu, 22 Oct 2015 19:45:09 +,
> "Rousselot, Richard A"  a ?crit :
> 
>> Doing the following math, why is it that the results are not all returning 
>> "yes"?
>> 
>> SELECT
>>(9.2+7.9+0+4.0+2.6+1.3),
>>case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else "no" 
>> end,
>>(9.2+7.8+0+3.0+1.3+1.7),
>>case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else "no" 
>> end,
>>(9.2+7.9+0+1.0+1.3+1.6),
>>case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else "no" 
>> end
>> FROM
>>sometable;
>> 
>> Result
>> 25.0 no 23.0 yes 21.0 no
>> 
>> I'm sure this has an obvious answer but it isn't jumping out at me.
>> 
>> Richard
>> 
>> This communication is the property of CenturyLink and may contain 
>> confidential or privileged
>> information. Unauthorized use of this communication is strictly prohibited 
>> and may be unlawful.
>> If you have received this communication in error, please immediately notify 
>> the sender by reply
>> e-mail and destroy all copies of the communication and any attachments.
>> ___ sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> just for the fun: round-off error...
> 
> 1/3 has an infinite number of decimal number. meanwhile a computer can only 
> store a finite
> number of decimal...
> 
> this is the story who leads Lorenz to talk about his butterfly and the 
> chaos... I do not remember
> the whole story but iirc that was when he worked on some numerical weather 
> prediction. Data
> stored in some magnetic device (sound like science-fiction for someone young 
> like me...) and the
> data in the RAM haven't the same number of decimal, so the calculus after 
> several iterations
> gave totaly different results...
> 
> regards,
> Nicolas J.
> 
> n.b. : "meanwhile a computer can only store a finite number of decimal..." 
> same in groceries when
> you see 98c and you have to pay 1$... thiefs!
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] Simple Math Question

[Stephan Beal]

On Thu, Oct 22, 2015 at 9:45 PM, Rousselot, Richard A <
Richard.A.Rousselot at centurylink.com> wrote:

> Doing the following math, why is it that the results are not all returning
> "yes"?
>
> SELECT
> (9.2+7.9+0+4.0+2.6+1.3),
> case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else
> "no" end,
> (9.2+7.8+0+3.0+1.3+1.7),
> case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else
> "no" end,
> (9.2+7.9+0+1.0+1.3+1.6),
> case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else
> "no" end
> FROM
> sometable;
>

Try the same with integers as you'll likely see different results.
Floating-point math is fraught with problems when it comes to expecting
exact results at a specific precision.

See: http://floating-point-gui.de/

the first example of which demonstrates the problem you are seeing.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[sqlite] Simple Math Question

[Simon Slavin]

On 22 Oct 2015, at 8:45pm, Rousselot, Richard A  wrote:

>(9.2+7.9+0+1.0+1.3+1.6),

If you really are handling a lot of numbers with exactly one decimal place, the 
answer is to multiply them all by 10 are part of your input routine, and store 
them all as integers.  On the other hand, you may have just come up with good 
examples to demonstrate your problem and this may not reflect your real data at 
all.

Financial software frequently handles all currency amounts as pence or cents 
for the reasons you've just found out.  Annoys the heck out of bankers until 
you have them work problems by hand and seen just how stupid computers really 
are.

Simon.

[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00

[Clemens Ladisch]

Marco Turco wrote:
> could you please give me the full Bcc32 string you are using ?

That would not be useful for you; I'm using an incompatible calling convention.

Just replace "-tW" with "-tWM".


Regards,
Clemens

[sqlite] Result of sqlite3_errmsg when there is no error

[Bart Smissaert]

I did read the documentation and that says:
If the most recent API call was successful, then the return value from
sqlite3_errcode is undefined. It is a bit awkward though that after doing
this after sqlite3_step gives 100 or 101
for sqlite3_errcode and unknown error for sqlite3_errmsg.
Why can't it give 0 and not an error respectively?
Or should this indeed be the case?

RBS

[sqlite] Simple Math Question

[Rousselot, Richard A]

I fully expected something like this from a fractional number like 1/3 but 
didn't realize that this would be the case if when the digits were explicitly 
stated and they summed to a whole number. I will use a Cast statement to 
resolve.

FWIW, MySQL and Oracle both return all yes for that query.

Thanks to everyone that replied.

Richard

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Stephan Beal
Sent: Thursday, October 22, 2015 2:57 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Simple Math Question

On Thu, Oct 22, 2015 at 9:45 PM, Rousselot, Richard A < Richard.A.Rousselot at 
centurylink.com> wrote:

> Doing the following math, why is it that the results are not all
> returning "yes"?
>
> SELECT
> (9.2+7.9+0+4.0+2.6+1.3),
> case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else
> "no" end,
> (9.2+7.8+0+3.0+1.3+1.7),
> case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else
> "no" end,
> (9.2+7.9+0+1.0+1.3+1.6),
> case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else
> "no" end FROM
> sometable;
>

Try the same with integers as you'll likely see different results.
Floating-point math is fraught with problems when it comes to expecting exact 
results at a specific precision.

See: http://floating-point-gui.de/

the first example of which demonstrates the problem you are seeing.


--
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those 
who insist on a perfect world, freedom will have to do." -- Bigby Wolf 
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
This communication is the property of CenturyLink and may contain confidential 
or privileged information. Unauthorized use of this communication is strictly 
prohibited and may be unlawful. If you have received this communication in 
error, please immediately notify the sender by reply e-mail and destroy all 
copies of the communication and any attachments.

[sqlite] from hfiandor about a unit in spanish

[R.Smith]

Hi Hector,

You can just copy-paste the unit code here - we'll have a look, but it's 
best to write a set of SQL statements that can be run in any SQLite 
application (such as the sqlite3.exe command line interface) and see 
whether that too doesn't work right, and if it doesn't, then we have a 
definite case to look into it and try to fix the problem.

If you don't do that, people may just think the problem is somewhere in 
your code (which unfortunately is often the case).

Cheers,
Ryan


On 2015-10-22 07:51 PM, H?ctor Fiandor wrote:
> Dear Mr. Simon:
>
> First, thanks for learn to me that the list don?t admits attachments.
>
> Second, I am not sure that my problem if only with lazarus, the error
> messages appears when I try to copy a table to a transistory one, drop, and
> recreate the same table, and after that copy from the transistory one to de
> recent recreated table.
>
> I do the routines up to the error message appear. Then I close the table,
> and do an application.terminate. Then I start again but go to a phase2
> routines, following the logical plan, generally up to the end of process.
>
> I would try to do all this (copy, drop, create, re-copy and drop the
> temporary table) in a singles procedure (phase).
>
> Thanks in advance for your help,
>
> Yours,
> Ing. H?ctor Fiandor
> hfiandor at ceniai.inf.cu
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Simple Math Question

[Rousselot, Richard A]

Doing the following math, why is it that the results are not all returning 
"yes"?

SELECT
(9.2+7.9+0+4.0+2.6+1.3),
case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else "no" end,
(9.2+7.8+0+3.0+1.3+1.7),
case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else "no" end,
(9.2+7.9+0+1.0+1.3+1.6),
case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else "no" end
FROM
sometable;

Result
25.0 no 23.0 yes 21.0 no

I'm sure this has an obvious answer but it isn't jumping out at me.

Richard

This communication is the property of CenturyLink and may contain confidential 
or privileged information. Unauthorized use of this communication is strictly 
prohibited and may be unlawful. If you have received this communication in 
error, please immediately notify the sender by reply e-mail and destroy all 
copies of the communication and any attachments.

[sqlite] Simple Math Question

[Keith Medcalf]

> Financial software frequently handles all currency amounts as pence or
> cents for the reasons you've just found out.  Annoys the heck out of
> bankers until you have them work problems by hand and seen just how stupid
> computers really are.

Usually as hundredths (1/100) of a cent -- that is, the amount multiplied by 
1000, stored as an integer.  "Bankers Rounding" is then done on any results.  
Usually Bankers Rounding is done as round-half-even (so 3.5 becomes 4 and 2.5 
becomes 2).  While they tend to be more accurate, stochastic rounding of halfs 
or alternating rounding are often not reproducible and therefore are not 
usually used where reproducibility is required.

[sqlite] Simple Math Question

[Keith Medcalf]

Because there are differences.  Double precision floating point is only 
accurate to 14 digits and there is a difference in the 15th decimal place.

sqlite> SELECT
   ...> (9.2+7.9+0+4.0+2.6+1.3) - 25.0,
   ...> (9.2+7.8+0+3.0+1.3+1.7) - 23.0,
   ...> (9.2+7.9+0+1.0+1.3+1.6) - 21.0;
3.5527136788005e-15|0.0|3.5527136788005e-15

When comparing floating point numbers you should be doing something like this:

sqlite> SELECT
   ...> (9.2+7.9+0+4.0+2.6+1.3),
   ...> case when abs((9.2+7.9+0+4.0+2.6+1.3) - 25.0) < 1e-14 
then "yes" else "no" end,
   ...> (9.2+7.8+0+3.0+1.3+1.7),
   ...> case when abs((9.2+7.8+0+3.0+1.3+1.7) - 23.0) < 1e-14 
then "yes" else "no" end,
   ...> (9.2+7.9+0+1.0+1.3+1.6),
   ...> case when abs((9.2+7.9+0+1.0+1.3+1.6) - 21.0) < 1e-14 
then "yes" else "no" end;
25.0|yes|23.0|yes|21.0|yes

The number "1e-14" is the "fuzz factor" used to determine the "closeness" of 
the numbers.  See
http://en.wikipedia.org/wiki/Floating_point
for some information on floating point numbers used by computers.  For 
comparing them see
http://floating-point-gui.de/errors/comparison/
and in particular the paper referenced at the end of the page -- which links to 
a more correct article.




> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Rousselot, Richard A
> Sent: Thursday, 22 October, 2015 13:45
> To: 'sqlite-users at mailinglists.sqlite.org'
> Subject: [sqlite] Simple Math Question
> 
> Doing the following math, why is it that the results are not all returning
> "yes"?
> 
> SELECT
> (9.2+7.9+0+4.0+2.6+1.3),
> case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else
> "no" end,
> (9.2+7.8+0+3.0+1.3+1.7),
> case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else
> "no" end,
> (9.2+7.9+0+1.0+1.3+1.6),
> case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else
> "no" end
> FROM
> sometable;
> 
> Result
> 25.0 no 23.0 yes 21.0 no
> 
> I'm sure this has an obvious answer but it isn't jumping out at me.
> 
> Richard
> 
> This communication is the property of CenturyLink and may contain
> confidential or privileged information. Unauthorized use of this
> communication is strictly prohibited and may be unlawful. If you have
> received this communication in error, please immediately notify the sender
> by reply e-mail and destroy all copies of the communication and any
> attachments.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Simple Math Question

[Igor Tandetnik]

On 10/22/2015 4:25 PM, Rousselot, Richard A wrote:
> I fully expected something like this from a fractional number like 1/3 but 
> didn't realize that this would be the case if when the digits were explicitly 
> stated and they summed to a whole number. I will use a Cast statement to 
> resolve.

Just like 1/3 cannot be precisely represented as a finite decimal 
fraction, 1/10 aka 0.1 cannot be precisely represented as a finite 
binary fraction - which is what a floating point value really is.

> FWIW, MySQL and Oracle both return all yes for that query.

In MySQL, 9.2 is a literal of DECIMAL type, which is in fact represented 
as a finite decimal fraction. SQLite doesn't have such a type. You would 
likely observe similar results in MySQL if you write your constants like 
this: 9.2e0 (scientific notation makes them have FLOAT or DOUBLE type).

See also: https://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html
-- 
Igor Tandetnik

[sqlite] Database corrupt after hard reboot. Can it be repaired?

[gunnar]

Thanks Simon!
We also store our critical data synchronously in flat (csv) files before 
sending it in the sqlite database. When the hard reboot occured only one 
of these was corrupted and only the last line.
We can always refill sqlite from these files, we use sqlite to allow us 
to easily offer a bit more sophisticated interface.


On 10/22/2015 03:29 PM, Simon Slavin wrote:
> On 22 Oct 2015, at 11:16am, gunnar  wrote:
>
>> It's good to know there are so many possibilities to repair when it is 
>> really needed!
> But it's worth noting that these are only possibilities.  There is no 
> guarentee of repair.  And trying out one possibility can corrupt your 
> database further so that the other won't work.  So always take a copy.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00

[Marco Turco]

Hi,
could you please give me the full Bcc32 string you are using ?

thank you

Marco

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Clemens 
Ladisch
Sent: Thursday, October 22, 2015 4:22 PM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00

Marco Turco wrote:
> I'm trying to generate the sqlite3 library but there is no way with 
> Embercadero C++ 7.00.
>
> I always receive some warnings and the first two related to the 
> _endthreadex' and '_beginthreadex' cannot permit to link me the library.
>
> k:\BCC70\Bin\Bcc32.Exe -DHB_NO_DEFAULT_API_MACROS -M -c -O2 -e  -tW 
> -DHB_NO_DEFAULT_STACK_MACROS -DHB_OS_WIN_USED -DHB_FM_STATISTICS_OFF 
> -D__HARBOUR__ -DSQLITE_HAS_CODEC=1  
> -Ik:\BCC70\Include;k:\XHARBOUR\Include
> -nK:\sqlite_see_2013\Obj K:\sqlite_see_2013\sqlite3.c

My Borland C++ 5.5.1 (the old, free version) needs -tWM for a multithreaded 
program; you're using -tW.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00

[Clemens Ladisch]

Marco Turco wrote:
> I'm trying to generate the sqlite3 library but there is no way with
> Embercadero C++ 7.00.
>
> I always receive some warnings and the first two related to the
> _endthreadex' and '_beginthreadex' cannot permit to link me the library.
>
> k:\BCC70\Bin\Bcc32.Exe -DHB_NO_DEFAULT_API_MACROS -M -c -O2 -e  -tW
> -DHB_NO_DEFAULT_STACK_MACROS -DHB_OS_WIN_USED -DHB_FM_STATISTICS_OFF
> -D__HARBOUR__ -DSQLITE_HAS_CODEC=1  -Ik:\BCC70\Include;k:\XHARBOUR\Include
> -nK:\sqlite_see_2013\Obj K:\sqlite_see_2013\sqlite3.c

My Borland C++ 5.5.1 (the old, free version) needs -tWM for a multithreaded
program; you're using -tW.


Regards,
Clemens

[sqlite] FTS5 issue on OS X

[Gergely Lukacsy (glukacsy)]

Oh yeah, bingo :)

Thanks?

Gergely

On 22/10/2015 17:05, "Scott Hess"  wrote:

>Dollars to donuts you're compiling SQLite but then linking against the
>system version.
>
>-scott
>
>
>On Thu, Oct 22, 2015 at 7:51 AM, Gergely Lukacsy (glukacsy) <
>glukacsy at cisco.com> wrote:
>
>> Hi Simon,
>>
>> Thanks for coming back to me.
>>
>> I ran sqlite3_compileoption_get in a loop to enumerate all options (I
>> assume this is the same as the pragma query suggested below) and I got
>>the
>> following:
>>
>> 2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
>> DBInit:OptiomENABLE_FTS3
>> 2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
>> DBInit:OptiomENABLE_FTS3_PARENTHESIS
>> 2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
>> DBInit:OptiomENABLE_LOCKING_STYLE=1
>> 2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
>> DBInit:OptiomENABLE_RTREE
>> 2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
>> DBInit:OptiomOMIT_AUTORESET
>> 2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
>> DBInit:OptiomOMIT_BUILTIN_TEST
>> 2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
>> DBInit:OptiomOMIT_LOAD_EXTENSION
>> 2015-10-22T14:46:26.000Z  [0x7fff7679d300] DBWrapper.cpp:33
>> DBInit:OptiomSYSTEM_MALLOC
>> 2015-10-22T14:46:26.000Z  [0x7fff7679d300] DBWrapper.cpp:33
>> DBInit:OptiomTHREADSAFE=2
>>
>>
>> I am really puzzled by this as its not that FTS is set to version 3, but
>> THREADSAFE=2 which is not what I am giving clang as an option. If I omit
>> -DSQLITE_ENABLE_FTS5 I see I can put garbage in the FTS5 specific parts
>>of
>> the amalgamation and it still compiles, with -DSQLITE_ENABLE_FTS5 it
>>fails
>> - indicating that the FTS5 specific parts are compiled and the
>> preprocessof flags honoured.
>>
>>
>> Regards,
>> Gergely
>>
>>
>> On 22/10/2015 14:51, "Simon Slavin"  wrote:
>>
>> >
>> >On 22 Oct 2015, at 2:38pm, Gergely Lukacsy (glukacsy)
>> > wrote:
>> >
>> >> ... I get an "error: no module: fts5" error message. This indicates
>> >>that I didn't compile SQLite with FTS5 support, but I am pretty sure
>> >>that I am using the right preprocessor flags
>> >
>> >Using that version of SQLite, can you execute the command
>> >
>> >PRAGMA compile_options;
>> >
>> >and tell us what it reports back ?  It will reply with a series of rows
>> >just like a SELECT command would.
>> >
>> >See  for
>> >further details.
>> >
>> >Simon.
>> >___
>> >sqlite-users mailing list
>> >sqlite-users at mailinglists.sqlite.org
>> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>___
>sqlite-users mailing list
>sqlite-users at mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Simple Math Question

[Igor Tandetnik]

On 10/22/2015 3:45 PM, Rousselot, Richard A wrote:
> Doing the following math, why is it that the results are not all returning 
> "yes"?

For the same reason that 0.33 + 0.33 + 0.33 != 
1.   0.33 is only approximately equal to 1/3, and the error 
tends to accumulate.

See also: https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html
-- 
Igor Tandetnik

[sqlite] SQLite C# Windows Store Apps

[Martin Křížek]

Hi,
can any one write some suggestion (I mean nuget package(s)) for creating
Windows Store App in C# with SQLite? In desktop apps I use simply
"System.Data.SQLite (x86/x64)" which I love because it allows me to write
SQL statements directly. Are there any good (ideally with examples or
documentation) packages that really works with Windows Store Apps posted to
Store?
Thanks in advance.

Best regards

Martin Krizek

[sqlite] SELECT SUM(...) FROM ... WHERE regression in 3.8.11

[Catalin Ionescu]

I will prepare a version of my DB file with dummy customers and 
suppliers names (not too many) and I can pass it as it is.

On 22.10.2015 15:19, Richard Hipp wrote:
> Thanks for the report.
>
> Can you also provide us with a test case that demonstrates the malfunction?
>
> On 10/22/15, Catalin Ionescu  wrote:
>> I have an internal stocks management application that I rebuild from
>> time to time, usually against the latest SQLite version. After moving to
>> SQLite 3.9.1 it started reporting crazy stock values. Previously it was
>> compiled with SQLite 3.8.8.3. Further investigation showed that the last
>> usable SQLite version is 3.8.10.2. The application is compiled with
>> Visual Studio 2013 Express.
>>
>> The SQL statement creating the database and the temporary views is:
>>
>> *
>> PRAGMA case_sensitive_like = ON;
>> PRAGMA foreign_keys = ON;
>> VACUUM;
>>
>> CREATE TABLE IF NOT EXISTS Categories(CatID INTEGER PRIMARY KEY
>> AUTOINCREMENT,
>> Name TEXT);
>> CREATE TABLE IF NOT EXISTS Components(CompID INTEGER PRIMARY KEY
>> AUTOINCREMENT,
>> CatID INTEGER REFERENCES
>> Categories(CatID),
>> Name TEXT);
>> CREATE TABLE IF NOT EXISTS Suppliers(SupID INTEGER PRIMARY KEY
>> AUTOINCREMENT,
>>Name TEXT);
>> CREATE TABLE IF NOT EXISTS Codes(CodeID INTEGER PRIMARY KEY AUTOINCREMENT,
>>CompID INTEGER REFERENCES
>> Components(CompID),
>>SupID INTEGER REFERENCES
>> Suppliers(SupID),
>>Name TEXT);
>> CREATE TABLE IF NOT EXISTS Inputs(InvID INTEGER PRIMARY KEY AUTOINCREMENT,
>> SupID INTEGER REFERENCES
>> Suppliers(SupID),
>> Date INTEGER,
>> Total REAL,
>> CompTotal REAL,
>> ExchgRate REAL,
>> Name TEXT);
>> CREATE TABLE IF NOT EXISTS InputsComp(InCompID INTEGER PRIMARY KEY
>> AUTOINCREMENT,
>> InvID INTEGER REFERENCES
>> Inputs(InvID),
>> CodeID INTEGER REFERENCES
>> Codes(CodeID),
>> Total REAL,
>> Quantity REAL);
>> CREATE TABLE IF NOT EXISTS Products(ProdID INTEGER PRIMARY KEY
>> AUTOINCREMENT,
>>   Code TEXT,
>>   Name TEXT);
>> CREATE TABLE IF NOT EXISTS ProductsComp(ProdCompID INTEGER PRIMARY KEY
>> AUTOINCREMENT,
>>   ProdID INTEGER REFERENCES
>> Products(ProdID),
>>   CompID INTEGER REFERENCES
>> Components(CompID),
>>   Quantity REAL);
>> CREATE TABLE IF NOT EXISTS Outputs(OutID INTEGER PRIMARY KEY AUTOINCREMENT,
>>  Date INTEGER,
>>  Name TEXT);
>> CREATE TABLE IF NOT EXISTS OutputsProd(OutProdID INTEGER PRIMARY KEY
>> AUTOINCREMENT,
>>  OutID INTEGER REFERENCES
>> Outputs(OutID),
>>  ProdID INTEGER REFERENCES
>> Products(ProdID),
>>  Quantity REAL);
>> CREATE TABLE IF NOT EXISTS OutputsComp(OutCompID INTEGER PRIMARY KEY
>> AUTOINCREMENT,
>>  OutID INTEGER REFERENCES
>> Outputs(OutID),
>>  CompID INTEGER REFERENCES
>> Components(CompID),
>>  Quantity REAL);
>>
>> CREATE TEMP VIEW Codes_View AS
>> SELECT CodeID,CompID,SupID,Name,
>>(SELECT Name FROM Suppliers WHERE (Suppliers.SupID =
>> Codes.SupID)) || ' - ' || Name AS SupplierCode,
>>(SELECT CatID FROM Components WHERE (Components.CompID =
>> Codes.CompID)) AS CatID,
>>(SELECT Name FROM Components WHERE (Components.CompID =
>> Codes.CompID)) AS Component
>>  FROM Codes;
>>
>> CREATE TEMP VIEW Inputs_View AS
>> SELECT InvID,SupID,Date,Total,CompTotal,ExchgRate,Name,
>>Total/ExchgRate/CompTotal AS PriceRatio,
>>(SELECT Name FROM Suppliers WHERE (Suppliers.SupID =
>> Inputs.SupID)) AS Supplier,
>>(SELECT Name FROM Suppliers WHERE (Suppliers.SupID =
>> Inputs.SupID)) || ' - ' || Name AS FullName
>>  FROM Inputs;
>>
>> CREATE TEMP VIEW InputsComp_View AS
>> SELECT InCompID,InvID,CodeID,Total,Quantity,
>>Total*(SELECT PriceRatio FROM Inputs_View WHERE
>> (Inputs_View.InvID = InputsComp.InvID)) AS EffPrice,
>>Total*(SELECT PriceRatio FROM Inputs_View WHERE
>> (Inputs_View.InvID = InputsComp.InvID))/Quantity AS Unit

[sqlite] Problem with sqlite3_create_function

[Bart Smissaert]

> Maybe running under control of a debugger would help to locate the exact
error position

I am not familiar with C code or C debugging, but will look into that.

RBS

On Thu, Oct 22, 2015 at 3:11 PM, Hick Gunter  wrote:

>
>
> >> Can you reproduce the problem using the sqlite shell?
> >This won't be easy as the UDF is in an ActiveX dll, not in sqlite3.dlll
> >
> >This is the output from explain, run on this SQL:
> >SELECT XXX(F1, F2) as A, XXX(F1, F2) as B FROM UDF_TEST limit 3 XXX will
> just add the results of the integer values of fields F1 and F2.
> >I put the underscore in the below table to indicate empty values as it is
> a bit tricky to get this nicely lined up in the e-mail.
> >addr opcode p1 p2 p3 p4 p5
> >0 Init 0 15 0 _ 0
> >1 Integer 3 1 0 _ 0
> >2 OpenRead 0 664359 0 _ 0
> >3 Rewind 0 13 0 _ 0
> >4 Column 0 0 4 _ 0
> >5 Column 0 1 5 _ 0
> >6 Function0 0 4 2 XXX(2) 2
> >7 Column 0 0 4 _ 0
> >8 Column 0 1 5 _ 0
> >9 Function0 0 4 3 XXX(2) 2
> >10 ResultRow 2 2 0 _ 0
> >11 DecrJumpZero 1 13 0 _ 0
> >12 Next 0 4 0 _ 1
> >13 Close 0 0 0 _ 0
> >14 Halt 0 0 0 _ 0
> >15 Transaction 0 0 867 0 1
> >16 TableLock 0 664359 0 UDF_TEST 0
> >17 Goto 0 1 0 _ 0
> >
> >UDF_TEST is the name of the callback function that resides in the ActiveX
> dll.
> >
> >> Also, your function needs to call exactly one sqlite3_result_xxx()
> >function, specifying the context received as the first parameter.
> >Yes, it does that.
> >
> >I also tried simply just setting the result and nil else:
> >
> >Public Sub Sum2Fields3(ByVal lPtr_ObjContext As Long, ByVal lArgCount As
> Long, ByVal lPtr_ObjSQLite3_Value As Long)
> >  sqlite3_result_int lPtr_ObjContext, 123 End Sub
> >
> >And still the same crash, so that is if the UDF is called twice in the
> same statement.
> >The crash happens on the first sqlite3_step.
> >All result codes prior to this are fine.
>
> The generated code looks good. It is loading the fields into contigous
> registers and then calling the UDF with a parameter count of 2.
>
> Maybe running under control of a debugger would help to locate the exact
> error position
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00

[Marco Turco]

The problem is when I link the generated library. I receive the following error 
related to the first two warnings so I'm unable to generate the executable file.

Embarcadero C++ 7.00 for Win32 Copyright (c) 1993-2015 Embarcadero 
Technologies, Inc.
K:\RENTMNG\Obj\mod306f.c:
k:\BCC70\Bin\iLink32.Exe -Gn -aa -Tpe -s @K:\RENTMNG\RENTMNG.bcl

Turbo Incremental Link 6.70 Copyright (c) 1997-2014 Embarcadero Technologies, 
Inc.
Error: Unresolved external '__endthreadex' referenced from 
K:\ACTIVEXP\SOURCE\SQLITE.SEE\SQLITE3.LIB|sqlite3
Error: Unresolved external '__beginthreadex' referenced from 
K:\ACTIVEXP\SOURCE\SQLITE.SEE\SQLITE3.LIB|sqlite3
Error: Unable to perform link

Marco

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R.Smith
Sent: Thursday, October 22, 2015 12:12 PM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00

On 2015-10-22 11:01 AM, Marco Turco wrote:
> Hi all,
>
> I'm trying to generate the sqlite3 library but there is no way with 
> Embercadero C++ 7.00.
>
> I always receive some warnings and the first two related to the 
> _endthreadex' and '_beginthreadex' cannot permit to link me the library.
>
>   
>
> See below.
>
>   
>
> k:\BCC70\Bin\Bcc32.Exe -DHB_NO_DEFAULT_API_MACROS -M -c -O2 -e  -tW 
> -DHB_NO_DEFAULT_STACK_MACROS -DHB_OS_WIN_USED -DHB_FM_STATISTICS_OFF 
> -D__HARBOUR__ -DSQLITE_HAS_CODEC=1  
> -Ik:\BCC70\Include;k:\XHARBOUR\Include
> -nK:\sqlite_see_2013\Obj K:\sqlite_see_2013\sqlite3.c
>
>   
>
> Embarcadero C++ 7.00 for Win32 Copyright (c) 1993-2015 Embarcadero 
> Technologies, Inc.
>
> K:\sqlite_see_2013\sqlite3.c:
>
> Warning W8065 K:\sqlite_see_2013\sqlite3.c 23909: Call to function 
> '_endthreadex' with no prototype in function sqlite3ThreadProc
>
> Warning W8065 K:\sqlite_see_2013\sqlite3.c 23936: Call to function 
> '_beginthreadex' with no prototype in function sqlite3ThreadCreate
>
> Warning W8057 K:\sqlite_see_2013\sqlite3.c 38624: Parameter 'iOff' is 
> never used in function winUnfetch
>
> Warning W8060 K:\sqlite_see_2013\sqlite3.c 80952: Possibly incorrect 
> assignment in function vdbeSorterCompareInt
>
> Warning W8060 K:\sqlite_see_2013\sqlite3.c 82583: Possibly incorrect 
> assignment in function vdbeSorterSetupMerge
>
> Warning W8057 K:\sqlite_see_2013\sqlite3.c 185433: Parameter 'iDb' is 
> never used in function sqlite3CodecGetKey
>
> k:\BCC70\Bin\TLib.Exe K:\sqlite_see_2013\sqlite_see_2013.Lib /P512 
> @K:\sqlite_see_2013\sqlite_see_2013.bcl , 
> K:\sqlite_see_2013\Debug\sqlite_see_2013.Lst

These are just warnings... They are not important and it should still compile.

If it fails to compile you should get a message like this:
"Error : Failed to compile K:\sqlite_see_2013\sqlite3.c 185433: Some 
description of what failed"

The warnings doesn't mean it did not compile. What makes you think the file 
failed to compile, is the actual compiled file missing or such?


>
>   
>
> TLIB 6.4 Copyright (c) 1987-2014 Embarcadero Technologies, Inc.
>
> +K:\sqlite_see_2013\Obj\sqlite3.Obj
>
>   
>
> Any help ? Thank you in advance
>
>   
>
> Marco
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS5 issue on OS X

[Gergely Lukacsy (glukacsy)]

Hi Simon, 

Thanks for coming back to me.

I ran sqlite3_compileoption_get in a loop to enumerate all options (I
assume this is the same as the pragma query suggested below) and I got the
following:

2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
DBInit:OptiomENABLE_FTS3
2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
DBInit:OptiomENABLE_FTS3_PARENTHESIS
2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
DBInit:OptiomENABLE_LOCKING_STYLE=1
2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
DBInit:OptiomENABLE_RTREE
2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
DBInit:OptiomOMIT_AUTORESET
2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
DBInit:OptiomOMIT_BUILTIN_TEST
2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
DBInit:OptiomOMIT_LOAD_EXTENSION
2015-10-22T14:46:26.000Z  [0x7fff7679d300] DBWrapper.cpp:33
DBInit:OptiomSYSTEM_MALLOC
2015-10-22T14:46:26.000Z  [0x7fff7679d300] DBWrapper.cpp:33
DBInit:OptiomTHREADSAFE=2


I am really puzzled by this as its not that FTS is set to version 3, but
THREADSAFE=2 which is not what I am giving clang as an option. If I omit
-DSQLITE_ENABLE_FTS5 I see I can put garbage in the FTS5 specific parts of
the amalgamation and it still compiles, with -DSQLITE_ENABLE_FTS5 it fails
- indicating that the FTS5 specific parts are compiled and the
preprocessof flags honoured.


Regards,
Gergely


On 22/10/2015 14:51, "Simon Slavin"  wrote:

>
>On 22 Oct 2015, at 2:38pm, Gergely Lukacsy (glukacsy)
> wrote:
>
>> ... I get an "error: no module: fts5" error message. This indicates
>>that I didn't compile SQLite with FTS5 support, but I am pretty sure
>>that I am using the right preprocessor flags
>
>Using that version of SQLite, can you execute the command
>
>PRAGMA compile_options;
>
>and tell us what it reports back ?  It will reply with a series of rows
>just like a SELECT command would.
>
>See  for
>further details.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users at mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS5 issue on OS X

[Simon Slavin]

On 22 Oct 2015, at 2:38pm, Gergely Lukacsy (glukacsy)  
wrote:

> ... I get an "error: no module: fts5" error message. This indicates that I 
> didn't compile SQLite with FTS5 support, but I am pretty sure that I am using 
> the right preprocessor flags 

Using that version of SQLite, can you execute the command

PRAGMA compile_options;

and tell us what it reports back ?  It will reply with a series of rows just 
like a SELECT command would.

See  for further 
details.

Simon.

[sqlite] from hfiandor about a unit in spanish

[Simon Slavin]

On 22 Oct 2015, at 2:02pm, H?ctor Fiandor  wrote:

> I have attached the unit because I don?t know what is the rules for this
> case.

Hector,

First, this mailing list doesn't allow attachments.

Second, since your problem is mostly to with Lazarus you would be better off 
asking in a Lazarus forum.

Good luck with it.

Simon.

[sqlite] Database corrupt after hard reboot. Can it be repaired?

[Simon Slavin]

On 22 Oct 2015, at 11:16am, gunnar  wrote:

> It's good to know there are so many possibilities to repair when it is really 
> needed!

But it's worth noting that these are only possibilities.  There is no guarentee 
of repair.  And trying out one possibility can corrupt your database further so 
that the other won't work.  So always take a copy.

Simon.

[sqlite] sqlite 3.8.11 - binary size

[Shuhrat Rahimov]

Hi,
I am running sqlite3 v3.8.10 on an ARM Cortex-M4 based embedded system. The
minimal size I could achieve was 184 K. Do you really have 44.8 K? And how
much runtime RAM is used by your binary? My RAM usage is 72-74 K.

2015-10-22 10:51 GMT+02:00 Romain Crausaz :

> Dear,
>
> We have an embedded system using sqlite. I performed an update from
> version 3.7.11 to 3.8.11 and was surprized that the binary file sqlite3 has
> grown from 44.8 KB to 627.4 KB. I used the same configuration (default) for
> both cross-compilation.
>
> The system has a limited amount of NAND memory and this make it unusable
> in our case.
>
> Is this memory increase normal or am I doing something wrong ?
>
> The library size didn't increase. It is only the sqlite3 binary file.
>
> Best regards
> Romain Crausaz
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite 3.8.11 - binary size

[Stephan Beal]

> We carefully monitor the size of the compiled SQLite binary.  A graph
> of that size is shown at
>
>  https://www.sqlite.org/binary-size.jpg
>
> The 627.4KB for 3.8.11 is within reason, depending on what compiler
> you are using.  But SQLite has *never* been as small as 44.8 KB.  Is
> that a typo?

Dynamically linked, perhaps?

- stephan
Sent from a mobile device, possibly from bed. Please excuse brevity, typos,
and top-posting.

[sqlite] Problem with sqlite3_create_function

[Bart Smissaert]

> Can you reproduce the problem using the sqlite shell?
This won't be easy as the UDF is in an ActiveX dll, not in sqlite3.dlll

This is the output from explain, run on this SQL:
SELECT XXX(F1, F2) as A, XXX(F1, F2) as B FROM UDF_TEST limit 3
XXX will just add the results of the integer values of fields F1 and F2.
I put the underscore in the below table to indicate empty values as it
is a bit tricky to get this nicely lined up in the e-mail.
addr opcode p1 p2 p3 p4 p5
0 Init 0 15 0 _ 0
1 Integer 3 1 0 _ 0
2 OpenRead 0 664359 0 _ 0
3 Rewind 0 13 0 _ 0
4 Column 0 0 4 _ 0
5 Column 0 1 5 _ 0
6 Function0 0 4 2 XXX(2) 2
7 Column 0 0 4 _ 0
8 Column 0 1 5 _ 0
9 Function0 0 4 3 XXX(2) 2
10 ResultRow 2 2 0 _ 0
11 DecrJumpZero 1 13 0 _ 0
12 Next 0 4 0 _ 1
13 Close 0 0 0 _ 0
14 Halt 0 0 0 _ 0
15 Transaction 0 0 867 0 1
16 TableLock 0 664359 0 UDF_TEST 0
17 Goto 0 1 0 _ 0

UDF_TEST is the name of the callback function that resides in the ActiveX
dll.

> Also, your function needs to call exactly one sqlite3_result_xxx()
function, specifying the context received as the first parameter.
Yes, it does that.

I also tried simply just setting the result and nil else:

Public Sub Sum2Fields3(ByVal lPtr_ObjContext As Long, ByVal lArgCount As
Long, ByVal lPtr_ObjSQLite3_Value As Long)
  sqlite3_result_int lPtr_ObjContext, 123
End Sub

And still the same crash, so that is if the UDF is called twice in the same
statement.
The crash happens on the first sqlite3_step.
All result codes prior to this are fine.

Thanks.


RBS

On Wed, Oct 21, 2015 at 7:16 AM, Hick Gunter  wrote:

> Can you reproduce the problem using the sqlite shell?
>
> You can access the SQLite machine instructions by issuing
>
> .explain
> Explain 
>
> You should see something similar to:
>
> asql> explain select XXX(Field1), XXX(Field2) from table1;
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> ?
> 2 VOpen  0 0 0 vtab:4B2B328:2B0BE53349D0  00  NULL
> 3 Integer0 1 000  NULL
> 4 Integer0 2 000  NULL
> 5 VFilter0 121   00  NULL
> 6 VColumn0 8 100  table1.Field1
> 7 Function   0 1 3 XXX(1) 01  NULL
> 8 VColumn0 9 200  table1.Field2
> 9 Function   0 2 4 XXX(1) 01  NULL
> 10ResultRow  3 2 000  NULL
> 11VNext  0 6 000  NULL
> 12Close  0 0 000  NULL
> 13Halt   0 0 000  NULL
> ?
>
>
> Also, your function needs to call exactly one sqlite3_result_xxx()
> function, specifying the context received as the first parameter.
>
> -Urspr?ngliche Nachricht-
> Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
> sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Bart
> Smissaert
> Gesendet: Mittwoch, 21. Oktober 2015 00:31
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Problem with sqlite3_create_function
>
> Could somebody tell me what happens after the callback function (to do with
> sqlite3_create_function) has run, so after it has finished processing one
> row?
> Does it return to the code line (in my case in an AciveX dll) directly
> after the line with sqlite3_step?
> Or are there other things going on first in sqlite3.dll?
> The problems is that if I call the user function more that once in the
> same SQL, eg:
> select XXX(field1), XXX(field2) from table1 I get a crash directly after
> the last run of the UDF to do with the first row.
> The 2 calls to the UDF are running fine, so no error message.
>
> RBS
>
> On Mon, Oct 19, 2015 at 2:19 PM, Bart Smissaert  >
> wrote:
>
> > > The "context" passed to a user defined function is not and cannot be
> > > a
> > "statement handle".
> >
> > Yes, you are right there. Not sure now how I thought it was a
> > statement handle, but this doesn't alter the problem.
> > Thanks in any case for correcting this mis-conception.
> >
> > RBS
> >
> > On Mon, Oct 19, 2015 at 1:40 PM, Hick Gunter  hick at scigames.at>> wrote:
> >
> >> The "context" passed to a user defined function is not and cannot be
> >> a "statement handle". How would an aggregate function tell the
> >> difference between
> >>
> >> Select SUM(x) ...
> >>
> >> and
> >>
> >> Select SUM(x), SUM(y) ...
> >>
> >> if not by virtue of sqlite3_aggregate_context() returning different
> >> adresses?
> >>
> >> -Urspr?ngliche Nachricht-
> >> Von: sqlite-users-bounces at mailinglists.sqlite.org sqlite-users-bounces at mailinglists.sqlite.org> [mailto:
> >> sqlite-users-bounces at mailinglists.sqlite.org sqlite-users-bounces at mailinglists.sqlite.org>] Im Auftrag von Bart
> >> Sm

[sqlite] Problem with sqlite3_create_function

[Hick Gunter]

>> Can you reproduce the problem using the sqlite shell?
>This won't be easy as the UDF is in an ActiveX dll, not in sqlite3.dlll
>
>This is the output from explain, run on this SQL:
>SELECT XXX(F1, F2) as A, XXX(F1, F2) as B FROM UDF_TEST limit 3 XXX will just 
>add the results of the integer values of fields F1 and F2.
>I put the underscore in the below table to indicate empty values as it is a 
>bit tricky to get this nicely lined up in the e-mail.
>addr opcode p1 p2 p3 p4 p5
>0 Init 0 15 0 _ 0
>1 Integer 3 1 0 _ 0
>2 OpenRead 0 664359 0 _ 0
>3 Rewind 0 13 0 _ 0
>4 Column 0 0 4 _ 0
>5 Column 0 1 5 _ 0
>6 Function0 0 4 2 XXX(2) 2
>7 Column 0 0 4 _ 0
>8 Column 0 1 5 _ 0
>9 Function0 0 4 3 XXX(2) 2
>10 ResultRow 2 2 0 _ 0
>11 DecrJumpZero 1 13 0 _ 0
>12 Next 0 4 0 _ 1
>13 Close 0 0 0 _ 0
>14 Halt 0 0 0 _ 0
>15 Transaction 0 0 867 0 1
>16 TableLock 0 664359 0 UDF_TEST 0
>17 Goto 0 1 0 _ 0
>
>UDF_TEST is the name of the callback function that resides in the ActiveX dll.
>
>> Also, your function needs to call exactly one sqlite3_result_xxx()
>function, specifying the context received as the first parameter.
>Yes, it does that.
>
>I also tried simply just setting the result and nil else:
>
>Public Sub Sum2Fields3(ByVal lPtr_ObjContext As Long, ByVal lArgCount As Long, 
>ByVal lPtr_ObjSQLite3_Value As Long)
>  sqlite3_result_int lPtr_ObjContext, 123 End Sub
>
>And still the same crash, so that is if the UDF is called twice in the same 
>statement.
>The crash happens on the first sqlite3_step.
>All result codes prior to this are fine.

The generated code looks good. It is loading the fields into contigous 
registers and then calling the UDF with a parameter count of 2.

Maybe running under control of a debugger would help to locate the exact error 
position


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] from hfiandor about a unit in spanish

[Héctor Fiandor]

Dear Mr. Simon:

First, thanks for learn to me that the list don?t admits attachments.

Second, I am not sure that my problem if only with lazarus, the error
messages appears when I try to copy a table to a transistory one, drop, and
recreate the same table, and after that copy from the transistory one to de
recent recreated table.

I do the routines up to the error message appear. Then I close the table,
and do an application.terminate. Then I start again but go to a phase2
routines, following the logical plan, generally up to the end of process.

I would try to do all this (copy, drop, create, re-copy and drop the
temporary table) in a singles procedure (phase).

Thanks in advance for your help,

Yours,
Ing. H?ctor Fiandor
hfiandor at ceniai.inf.cu

[sqlite] FTS5 issue on OS X

[Gergely Lukacsy (glukacsy)]

Hi,

I am using Sqlite in a codebase shared between Windows and OS X and 
specifically I am using FTS4 for searching our DB for free text strings. I have 
just upgraded to the latest 3.9.1 and thought to give a try to FTS5, but 
encountered an issue. It works fine on Windows, but on OS X the moment I try to 
create the FTS5 virtual table...

"CREATE VIRTUAL TABLE IF NOT EXISTS MessageSearch USING fts5(MESSAGE_ID 
UNINDEXED,CONVERSATION_ID UNINDEXED,MESSAGE_PUBLISHED_TIME 
UNINDEXED,MESSAGE_DATA, prefix='3,4,5')"

... I get an "error: no module: fts5" error message. This indicates that I 
didn't compile SQLite with FTS5 support, but I am pretty sure that I am using 
the right preprocessor flags (I am using the single C file amalgamation). For 
reference here is the entire build script I am using:

clang -c -O2 -DSQLITE_ENABLE_FTS5 -DSQLITE_THREADSAFE=0 -o sqlite3.o sqlite3.c

ar rcs darwin/libsqlite3.a sqlite3.o


I confirmed that the fts5.c part and the fts5 initialisation functions are all 
compiled into the .o.


Any idea what am I doing wrong? The main thing why I am puzzled is that it 
works without issues on Windows.


Regards,

Gergely

[sqlite] Uninformative error message on foreign key violation

[Sergei Dorogin]

Hi.
There was a ticket about uninformative error message on check constraint
failures -
http://www.sqlite.org/src/tktview/23b212820161c6599cbf414aa99bf8a5bfa5e7a3
(It's market as Fixed on 2014-03-11  in version 3.3.2).
But for foreign key we still have uninformative error messages - "FOREIGN
KEY constraint failed".

Is there any chance to see it's implemented in SQLite some time? It's
absolute a must have feature. I understand that collecting additional info
on FK violation can slow down execution. So it could be disable by default
and enabled with some Pragma.
Currently It's impossible to correctly process such messages in libraries
and apps to report some meaningfull info to user. As it's unclear what is
going on.

[sqlite] Database corrupt after hard reboot. Can it be repaired?

[gunnar]

Hi Paul, thanks!
It's good to know there are so many possibilities to repair when it is 
really needed!


On 10/19/2015 10:21 PM, Paul Sanderson wrote:
> Gunnar
>
> If the data is very important then don't do anything that will change
> the computer.
>
> If you have sometools that can take an image copy of the disk (DD to
> another drive) or maybe the free accessdata imaging software then I
> would recommend doing this first - it will leave all options open
> should you wish to try something complex later.
>
> I have tools that can recover tables and rows from a damaged database,
> all they need is the schema of the original DB (the more complicated
> the bettter). The data can be recovered from the corrupt DB, from any
> journal files and also potentially from the swap file (or equivalent)
> or unused space on the hard disk. Records can also potentially be
> recovered from RAM - although in your case as the power has been off
> this is not going to work.
>
> The success of the operation depends on many factors but there is a
> fair chance that it won't be 100% acceptable - depending on the nature
> of the data (and any table constraints) this may be acceptable. It may
> also require some manual "filtering" of the recovered data as more
> simple tables are prone to include what can be best described as
> suprious results.
>
> Any old backups of the database can potentially be used to create a
> working solution.
>
> More information - the DB schema particularly - and what you need
> recovered for it to be a success (even a partial success) would be
> good.
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
>
> On 19 October 2015 at 18:51, gunnar  wrote:
>> Thanks a lot Simon!
>> We'll try it.
>>
>>
>> (BTW I also got an email from alexa, but looking in the sqlite mailing list
>> I can see that I'm not the only one)
>>
>>
>>
>>
>> On 10/19/2015 06:20 PM, Simon Slavin wrote:
>>> On 19 Oct 2015, at 5:13pm, gunnar  wrote:
>>>
 We run sqlite with PRAGMA synchronous=OFF.

 Now we suffered from a 'hard reboot' because a sysadmin unplugged the
 power cable :(

 Is it possible to repair
>>> First take a copy of the database file and any other file from the same
>>> directory with a similar name.  This is just in case you decide to use
>>> heroic (i.e. expensive) means to rescue the database later.
>>>
>>> Open the proper (not the copy) database using the SQLite Shell Tool
>>> downloadable from the SQLite site.  Use the '.dump' command to dump the
>>> database as a text file of SQL commands.
>>>
>>> Read through that file and see if it looks like it has captured all your
>>> data in it.  If it has ...
>>>
>>> Create a new blank database using the SQLite Shell Tool.
>>> Use the '.read' command to execute the commands in the text file.
>>> Use various commands in the SQLite shell tool to explore the resulting
>>> database and see if it looks like it's worth using.
>>>
>>> Simon.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00

[R.Smith]

On 2015-10-22 11:01 AM, Marco Turco wrote:
> Hi all,
>
> I'm trying to generate the sqlite3 library but there is no way with
> Embercadero C++ 7.00.
>
> I always receive some warnings and the first two related to the
> _endthreadex' and '_beginthreadex' cannot permit to link me the library.
>
>   
>
> See below.
>
>   
>
> k:\BCC70\Bin\Bcc32.Exe -DHB_NO_DEFAULT_API_MACROS -M -c -O2 -e  -tW
> -DHB_NO_DEFAULT_STACK_MACROS -DHB_OS_WIN_USED -DHB_FM_STATISTICS_OFF
> -D__HARBOUR__ -DSQLITE_HAS_CODEC=1  -Ik:\BCC70\Include;k:\XHARBOUR\Include
> -nK:\sqlite_see_2013\Obj K:\sqlite_see_2013\sqlite3.c
>
>   
>
> Embarcadero C++ 7.00 for Win32 Copyright (c) 1993-2015 Embarcadero
> Technologies, Inc.
>
> K:\sqlite_see_2013\sqlite3.c:
>
> Warning W8065 K:\sqlite_see_2013\sqlite3.c 23909: Call to function
> '_endthreadex' with no prototype in function sqlite3ThreadProc
>
> Warning W8065 K:\sqlite_see_2013\sqlite3.c 23936: Call to function
> '_beginthreadex' with no prototype in function sqlite3ThreadCreate
>
> Warning W8057 K:\sqlite_see_2013\sqlite3.c 38624: Parameter 'iOff' is never
> used in function winUnfetch
>
> Warning W8060 K:\sqlite_see_2013\sqlite3.c 80952: Possibly incorrect
> assignment in function vdbeSorterCompareInt
>
> Warning W8060 K:\sqlite_see_2013\sqlite3.c 82583: Possibly incorrect
> assignment in function vdbeSorterSetupMerge
>
> Warning W8057 K:\sqlite_see_2013\sqlite3.c 185433: Parameter 'iDb' is never
> used in function sqlite3CodecGetKey
>
> k:\BCC70\Bin\TLib.Exe K:\sqlite_see_2013\sqlite_see_2013.Lib /P512
> @K:\sqlite_see_2013\sqlite_see_2013.bcl ,
> K:\sqlite_see_2013\Debug\sqlite_see_2013.Lst

These are just warnings... They are not important and it should still 
compile.

If it fails to compile you should get a message like this:
"Error : Failed to compile K:\sqlite_see_2013\sqlite3.c 185433: Some 
description of what failed"

The warnings doesn't mean it did not compile. What makes you think the 
file failed to compile, is the actual compiled file missing or such?


>
>   
>
> TLIB 6.4 Copyright (c) 1987-2014 Embarcadero Technologies, Inc.
>
> +K:\sqlite_see_2013\Obj\sqlite3.Obj
>
>   
>
> Any help ? Thank you in advance
>
>   
>
> Marco
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00

[Marco Turco]

Hi all,

I'm trying to generate the sqlite3 library but there is no way with
Embercadero C++ 7.00.

I always receive some warnings and the first two related to the
_endthreadex' and '_beginthreadex' cannot permit to link me the library.



See below.



k:\BCC70\Bin\Bcc32.Exe -DHB_NO_DEFAULT_API_MACROS -M -c -O2 -e  -tW
-DHB_NO_DEFAULT_STACK_MACROS -DHB_OS_WIN_USED -DHB_FM_STATISTICS_OFF
-D__HARBOUR__ -DSQLITE_HAS_CODEC=1  -Ik:\BCC70\Include;k:\XHARBOUR\Include
-nK:\sqlite_see_2013\Obj K:\sqlite_see_2013\sqlite3.c



Embarcadero C++ 7.00 for Win32 Copyright (c) 1993-2015 Embarcadero
Technologies, Inc.

K:\sqlite_see_2013\sqlite3.c:

Warning W8065 K:\sqlite_see_2013\sqlite3.c 23909: Call to function
'_endthreadex' with no prototype in function sqlite3ThreadProc

Warning W8065 K:\sqlite_see_2013\sqlite3.c 23936: Call to function
'_beginthreadex' with no prototype in function sqlite3ThreadCreate

Warning W8057 K:\sqlite_see_2013\sqlite3.c 38624: Parameter 'iOff' is never
used in function winUnfetch

Warning W8060 K:\sqlite_see_2013\sqlite3.c 80952: Possibly incorrect
assignment in function vdbeSorterCompareInt

Warning W8060 K:\sqlite_see_2013\sqlite3.c 82583: Possibly incorrect
assignment in function vdbeSorterSetupMerge

Warning W8057 K:\sqlite_see_2013\sqlite3.c 185433: Parameter 'iDb' is never
used in function sqlite3CodecGetKey

k:\BCC70\Bin\TLib.Exe K:\sqlite_see_2013\sqlite_see_2013.Lib /P512
@K:\sqlite_see_2013\sqlite_see_2013.bcl ,
K:\sqlite_see_2013\Debug\sqlite_see_2013.Lst



TLIB 6.4 Copyright (c) 1987-2014 Embarcadero Technologies, Inc.

+K:\sqlite_see_2013\Obj\sqlite3.Obj



Any help ? Thank you in advance



Marco

[sqlite] sqlite 3.8.11 - binary size

[Romain Crausaz]

Dear,

We have an embedded system using sqlite. I performed an update from 
version 3.7.11 to 3.8.11 and was surprized that the binary file sqlite3 
has grown from 44.8 KB to 627.4 KB. I used the same configuration 
(default) for both cross-compilation.

The system has a limited amount of NAND memory and this make it unusable 
in our case.

Is this memory increase normal or am I doing something wrong ?

The library size didn't increase. It is only the sqlite3 binary file.

Best regards
Romain Crausaz

[sqlite] FTS5 issue on OS X

[Scott Hess]

Dollars to donuts you're compiling SQLite but then linking against the
system version.

-scott


On Thu, Oct 22, 2015 at 7:51 AM, Gergely Lukacsy (glukacsy) <
glukacsy at cisco.com> wrote:

> Hi Simon,
>
> Thanks for coming back to me.
>
> I ran sqlite3_compileoption_get in a loop to enumerate all options (I
> assume this is the same as the pragma query suggested below) and I got the
> following:
>
> 2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
> DBInit:OptiomENABLE_FTS3
> 2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
> DBInit:OptiomENABLE_FTS3_PARENTHESIS
> 2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
> DBInit:OptiomENABLE_LOCKING_STYLE=1
> 2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
> DBInit:OptiomENABLE_RTREE
> 2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
> DBInit:OptiomOMIT_AUTORESET
> 2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
> DBInit:OptiomOMIT_BUILTIN_TEST
> 2015-10-22T14:46:24.000Z  [0x7fff7679d300] DBWrapper.cpp:33
> DBInit:OptiomOMIT_LOAD_EXTENSION
> 2015-10-22T14:46:26.000Z  [0x7fff7679d300] DBWrapper.cpp:33
> DBInit:OptiomSYSTEM_MALLOC
> 2015-10-22T14:46:26.000Z  [0x7fff7679d300] DBWrapper.cpp:33
> DBInit:OptiomTHREADSAFE=2
>
>
> I am really puzzled by this as its not that FTS is set to version 3, but
> THREADSAFE=2 which is not what I am giving clang as an option. If I omit
> -DSQLITE_ENABLE_FTS5 I see I can put garbage in the FTS5 specific parts of
> the amalgamation and it still compiles, with -DSQLITE_ENABLE_FTS5 it fails
> - indicating that the FTS5 specific parts are compiled and the
> preprocessof flags honoured.
>
>
> Regards,
> Gergely
>
>
> On 22/10/2015 14:51, "Simon Slavin"  wrote:
>
> >
> >On 22 Oct 2015, at 2:38pm, Gergely Lukacsy (glukacsy)
> > wrote:
> >
> >> ... I get an "error: no module: fts5" error message. This indicates
> >>that I didn't compile SQLite with FTS5 support, but I am pretty sure
> >>that I am using the right preprocessor flags
> >
> >Using that version of SQLite, can you execute the command
> >
> >PRAGMA compile_options;
> >
> >and tell us what it reports back ?  It will reply with a series of rows
> >just like a SELECT command would.
> >
> >See  for
> >further details.
> >
> >Simon.
> >___
> >sqlite-users mailing list
> >sqlite-users at mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] from hfiandor about a unit in spanish

[Héctor Fiandor]

Dear members:



I use Lazarus for applications and SQLite for BD?motors, in spanish
language.



I  have the attached unit to re-build a BD table that fulfill some
conditions. I have found the way of a 2-phase to avoid an error message.



Please, if you have a solution to do this task in a 1 phase only, I will
appreciate very much. I have similar tasks when ending a year, to rebuild
the old BD to the new year BD.



I have attached the unit because I don?t know what is the rules for this
case.



Thanks in advance,



Yours,

Ing. H?ctor Fiandor

hfiandor at ceniai.inf.cu

[sqlite] SELECT SUM(...) FROM ... WHERE regression in 3.8.11

[Catalin Ionescu]

I have an internal stocks management application that I rebuild from 
time to time, usually against the latest SQLite version. After moving to 
SQLite 3.9.1 it started reporting crazy stock values. Previously it was 
compiled with SQLite 3.8.8.3. Further investigation showed that the last 
usable SQLite version is 3.8.10.2. The application is compiled with 
Visual Studio 2013 Express.

The SQL statement creating the database and the temporary views is:

*
PRAGMA case_sensitive_like = ON;
PRAGMA foreign_keys = ON;
VACUUM;

CREATE TABLE IF NOT EXISTS Categories(CatID INTEGER PRIMARY KEY 
AUTOINCREMENT,
   Name TEXT);
CREATE TABLE IF NOT EXISTS Components(CompID INTEGER PRIMARY KEY 
AUTOINCREMENT,
   CatID INTEGER REFERENCES 
Categories(CatID),
   Name TEXT);
CREATE TABLE IF NOT EXISTS Suppliers(SupID INTEGER PRIMARY KEY 
AUTOINCREMENT,
  Name TEXT);
CREATE TABLE IF NOT EXISTS Codes(CodeID INTEGER PRIMARY KEY AUTOINCREMENT,
  CompID INTEGER REFERENCES 
Components(CompID),
  SupID INTEGER REFERENCES Suppliers(SupID),
  Name TEXT);
CREATE TABLE IF NOT EXISTS Inputs(InvID INTEGER PRIMARY KEY AUTOINCREMENT,
   SupID INTEGER REFERENCES 
Suppliers(SupID),
   Date INTEGER,
   Total REAL,
   CompTotal REAL,
   ExchgRate REAL,
   Name TEXT);
CREATE TABLE IF NOT EXISTS InputsComp(InCompID INTEGER PRIMARY KEY 
AUTOINCREMENT,
   InvID INTEGER REFERENCES 
Inputs(InvID),
   CodeID INTEGER REFERENCES 
Codes(CodeID),
   Total REAL,
   Quantity REAL);
CREATE TABLE IF NOT EXISTS Products(ProdID INTEGER PRIMARY KEY 
AUTOINCREMENT,
 Code TEXT,
 Name TEXT);
CREATE TABLE IF NOT EXISTS ProductsComp(ProdCompID INTEGER PRIMARY KEY 
AUTOINCREMENT,
 ProdID INTEGER REFERENCES 
Products(ProdID),
 CompID INTEGER REFERENCES 
Components(CompID),
 Quantity REAL);
CREATE TABLE IF NOT EXISTS Outputs(OutID INTEGER PRIMARY KEY AUTOINCREMENT,
Date INTEGER,
Name TEXT);
CREATE TABLE IF NOT EXISTS OutputsProd(OutProdID INTEGER PRIMARY KEY 
AUTOINCREMENT,
OutID INTEGER REFERENCES 
Outputs(OutID),
ProdID INTEGER REFERENCES 
Products(ProdID),
Quantity REAL);
CREATE TABLE IF NOT EXISTS OutputsComp(OutCompID INTEGER PRIMARY KEY 
AUTOINCREMENT,
OutID INTEGER REFERENCES 
Outputs(OutID),
CompID INTEGER REFERENCES 
Components(CompID),
Quantity REAL);

CREATE TEMP VIEW Codes_View AS
   SELECT CodeID,CompID,SupID,Name,
  (SELECT Name FROM Suppliers WHERE (Suppliers.SupID = 
Codes.SupID)) || ' - ' || Name AS SupplierCode,
  (SELECT CatID FROM Components WHERE (Components.CompID = 
Codes.CompID)) AS CatID,
  (SELECT Name FROM Components WHERE (Components.CompID = 
Codes.CompID)) AS Component
FROM Codes;

CREATE TEMP VIEW Inputs_View AS
   SELECT InvID,SupID,Date,Total,CompTotal,ExchgRate,Name,
  Total/ExchgRate/CompTotal AS PriceRatio,
  (SELECT Name FROM Suppliers WHERE (Suppliers.SupID = 
Inputs.SupID)) AS Supplier,
  (SELECT Name FROM Suppliers WHERE (Suppliers.SupID = 
Inputs.SupID)) || ' - ' || Name AS FullName
FROM Inputs;

CREATE TEMP VIEW InputsComp_View AS
   SELECT InCompID,InvID,CodeID,Total,Quantity,
  Total*(SELECT PriceRatio FROM Inputs_View WHERE 
(Inputs_View.InvID = InputsComp.InvID)) AS EffPrice,
  Total*(SELECT PriceRatio FROM Inputs_View WHERE 
(Inputs_View.InvID = InputsComp.InvID))/Quantity AS UnitPrice,
  (SELECT CompID FROM Codes WHERE (Codes.CodeID = 
InputsComp.CodeID)) AS CompID,
  (SELECT SupplierCode FROM Codes_View WHERE (Codes_View.CodeID 
= InputsComp.CodeID)) AS SupplierCode,
  (SELECT Component FROM Codes_View WHERE (Codes_View.CodeID = 
InputsComp.CodeID)) AS Component,
  (SELECT FullName FROM Inputs_View WHERE (Inputs_View.InvID = 
InputsComp.InvID)) AS Invoice,
  (SELECT Date FROM Inputs_View WHERE (Inputs_View.InvID = 
InputsComp.InvID)) AS Date
FROM InputsComp;

CREATE TEMP VIEW ProductsComp_View AS
   SELECT ProdCompID,ProdID,CompID,Quantity,
   

[sqlite] SELECT SUM(...) FROM ... WHERE regression in 3.8.11

[Richard Hipp]

Thanks for the report.

Can you also provide us with a test case that demonstrates the malfunction?

On 10/22/15, Catalin Ionescu  wrote:
> I have an internal stocks management application that I rebuild from
> time to time, usually against the latest SQLite version. After moving to
> SQLite 3.9.1 it started reporting crazy stock values. Previously it was
> compiled with SQLite 3.8.8.3. Further investigation showed that the last
> usable SQLite version is 3.8.10.2. The application is compiled with
> Visual Studio 2013 Express.
>
> The SQL statement creating the database and the temporary views is:
>
> *
> PRAGMA case_sensitive_like = ON;
> PRAGMA foreign_keys = ON;
> VACUUM;
>
> CREATE TABLE IF NOT EXISTS Categories(CatID INTEGER PRIMARY KEY
> AUTOINCREMENT,
>Name TEXT);
> CREATE TABLE IF NOT EXISTS Components(CompID INTEGER PRIMARY KEY
> AUTOINCREMENT,
>CatID INTEGER REFERENCES
> Categories(CatID),
>Name TEXT);
> CREATE TABLE IF NOT EXISTS Suppliers(SupID INTEGER PRIMARY KEY
> AUTOINCREMENT,
>   Name TEXT);
> CREATE TABLE IF NOT EXISTS Codes(CodeID INTEGER PRIMARY KEY AUTOINCREMENT,
>   CompID INTEGER REFERENCES
> Components(CompID),
>   SupID INTEGER REFERENCES
> Suppliers(SupID),
>   Name TEXT);
> CREATE TABLE IF NOT EXISTS Inputs(InvID INTEGER PRIMARY KEY AUTOINCREMENT,
>SupID INTEGER REFERENCES
> Suppliers(SupID),
>Date INTEGER,
>Total REAL,
>CompTotal REAL,
>ExchgRate REAL,
>Name TEXT);
> CREATE TABLE IF NOT EXISTS InputsComp(InCompID INTEGER PRIMARY KEY
> AUTOINCREMENT,
>InvID INTEGER REFERENCES
> Inputs(InvID),
>CodeID INTEGER REFERENCES
> Codes(CodeID),
>Total REAL,
>Quantity REAL);
> CREATE TABLE IF NOT EXISTS Products(ProdID INTEGER PRIMARY KEY
> AUTOINCREMENT,
>  Code TEXT,
>  Name TEXT);
> CREATE TABLE IF NOT EXISTS ProductsComp(ProdCompID INTEGER PRIMARY KEY
> AUTOINCREMENT,
>  ProdID INTEGER REFERENCES
> Products(ProdID),
>  CompID INTEGER REFERENCES
> Components(CompID),
>  Quantity REAL);
> CREATE TABLE IF NOT EXISTS Outputs(OutID INTEGER PRIMARY KEY AUTOINCREMENT,
> Date INTEGER,
> Name TEXT);
> CREATE TABLE IF NOT EXISTS OutputsProd(OutProdID INTEGER PRIMARY KEY
> AUTOINCREMENT,
> OutID INTEGER REFERENCES
> Outputs(OutID),
> ProdID INTEGER REFERENCES
> Products(ProdID),
> Quantity REAL);
> CREATE TABLE IF NOT EXISTS OutputsComp(OutCompID INTEGER PRIMARY KEY
> AUTOINCREMENT,
> OutID INTEGER REFERENCES
> Outputs(OutID),
> CompID INTEGER REFERENCES
> Components(CompID),
> Quantity REAL);
>
> CREATE TEMP VIEW Codes_View AS
>SELECT CodeID,CompID,SupID,Name,
>   (SELECT Name FROM Suppliers WHERE (Suppliers.SupID =
> Codes.SupID)) || ' - ' || Name AS SupplierCode,
>   (SELECT CatID FROM Components WHERE (Components.CompID =
> Codes.CompID)) AS CatID,
>   (SELECT Name FROM Components WHERE (Components.CompID =
> Codes.CompID)) AS Component
> FROM Codes;
>
> CREATE TEMP VIEW Inputs_View AS
>SELECT InvID,SupID,Date,Total,CompTotal,ExchgRate,Name,
>   Total/ExchgRate/CompTotal AS PriceRatio,
>   (SELECT Name FROM Suppliers WHERE (Suppliers.SupID =
> Inputs.SupID)) AS Supplier,
>   (SELECT Name FROM Suppliers WHERE (Suppliers.SupID =
> Inputs.SupID)) || ' - ' || Name AS FullName
> FROM Inputs;
>
> CREATE TEMP VIEW InputsComp_View AS
>SELECT InCompID,InvID,CodeID,Total,Quantity,
>   Total*(SELECT PriceRatio FROM Inputs_View WHERE
> (Inputs_View.InvID = InputsComp.InvID)) AS EffPrice,
>   Total*(SELECT PriceRatio FROM Inputs_View WHERE
> (Inputs_View.InvID = InputsComp.InvID))/Quantity AS UnitPrice,
>   (SELECT CompID FROM Codes WHERE (Codes.CodeID =
> InputsComp.CodeID)) AS CompID,
>   (SELECT SupplierCode FROM Codes_View WHERE (Codes_View.CodeID
> = InputsComp.CodeID)) AS SupplierCode,
>   (SELECT Component FROM Codes_View WHERE (Codes_View.CodeID =
> InputsComp.CodeID)) AS Compo

[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00

[Scott Doctor]

I am using Embarcadero's development studio and compiling in the sqlite 
amalgamation. You can just turn off those two warnings.

To turn it off for whole project:
Select the Projects menu Options
Under the C++ Compiler options select the warnings
Open the options for selected warnings.
scroll down to those two warnings and disable them.

To turn off the warnings just for sqlite, just right click on the 
sqlite.c file name, select the local options. the same project options 
menu opens, but will apply only to that file.

If you try to compile the SQLite amalgamation with all warnings on, you 
will get over 600 of them. Most warnings are for unused variables, and 
variables that are assigned a value but never used

I examined those particular ones. It is due to assigning a variable 
within an 'if'. Although it has double parenthesis around it, which is 
supposed to indicate that the assignment is intended, it still gives the 
warning for some reason on some of them. That is an important warning as 
that is usually  a nasty bug where a single equals is used instead of a 
double equals for an equivalence comparison.

Although that style makes for more compact code by doing the assignment 
at the same time as the logic test, I find that it is a safer practice 
to do the assignment just before the 'if' then test the variable. From a 
debugging point of view it is much easier to set a break point on the if 
statement and see the actual result being checked. I did a few compile 
tests. with the optimizer, the resulting compiled code was identical 
either way..


Scott Doctor
scott at scottdoctor.com

On 10/22/2015 2:01 AM, Marco Turco wrote:
> Hi all,
>
> I'm trying to generate the sqlite3 library but there is no way with
> Embercadero C++ 7.00.
>
> I always receive some warnings and the first two related to the
> _endthreadex' and '_beginthreadex' cannot permit to link me the library.
>
>   
>
> See below.
>
>   
>
> k:\BCC70\Bin\Bcc32.Exe -DHB_NO_DEFAULT_API_MACROS -M -c -O2 -e  -tW
> -DHB_NO_DEFAULT_STACK_MACROS -DHB_OS_WIN_USED -DHB_FM_STATISTICS_OFF
> -D__HARBOUR__ -DSQLITE_HAS_CODEC=1  -Ik:\BCC70\Include;k:\XHARBOUR\Include
> -nK:\sqlite_see_2013\Obj K:\sqlite_see_2013\sqlite3.c
>
>   
>
> Embarcadero C++ 7.00 for Win32 Copyright (c) 1993-2015 Embarcadero
> Technologies, Inc.
>
> K:\sqlite_see_2013\sqlite3.c:
>
> Warning W8065 K:\sqlite_see_2013\sqlite3.c 23909: Call to function
> '_endthreadex' with no prototype in function sqlite3ThreadProc
>
> Warning W8065 K:\sqlite_see_2013\sqlite3.c 23936: Call to function
> '_beginthreadex' with no prototype in function sqlite3ThreadCreate
>
> Warning W8057 K:\sqlite_see_2013\sqlite3.c 38624: Parameter 'iOff' is never
> used in function winUnfetch
>
> Warning W8060 K:\sqlite_see_2013\sqlite3.c 80952: Possibly incorrect
> assignment in function vdbeSorterCompareInt
>
> Warning W8060 K:\sqlite_see_2013\sqlite3.c 82583: Possibly incorrect
> assignment in function vdbeSorterSetupMerge
>
> Warning W8057 K:\sqlite_see_2013\sqlite3.c 185433: Parameter 'iDb' is never
> used in function sqlite3CodecGetKey
>
> k:\BCC70\Bin\TLib.Exe K:\sqlite_see_2013\sqlite_see_2013.Lib /P512
> @K:\sqlite_see_2013\sqlite_see_2013.bcl ,
> K:\sqlite_see_2013\Debug\sqlite_see_2013.Lst
>
>   
>
> TLIB 6.4 Copyright (c) 1987-2014 Embarcadero Technologies, Inc.
>
> +K:\sqlite_see_2013\Obj\sqlite3.Obj
>
>   
>
> Any help ? Thank you in advance
>
>   
>
> Marco
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>

[sqlite] sqlite 3.8.11 - binary size

[Richard Hipp]

On 10/22/15, Romain Crausaz  wrote:
> Dear,
>
> We have an embedded system using sqlite. I performed an update from
> version 3.7.11 to 3.8.11 and was surprized that the binary file sqlite3
> has grown from 44.8 KB to 627.4 KB. I used the same configuration
> (default) for both cross-compilation.

We carefully monitor the size of the compiled SQLite binary.  A graph
of that size is shown at

 https://www.sqlite.org/binary-size.jpg

The 627.4KB for 3.8.11 is within reason, depending on what compiler
you are using.  But SQLite has *never* been as small as 44.8 KB.  Is
that a typo?

As you can see, the binary size has increased some, but not a lot,
over the past 8 years.

>
> The system has a limited amount of NAND memory and this make it unusable
> in our case.
>
> Is this memory increase normal or am I doing something wrong ?
>
> The library size didn't increase. It is only the sqlite3 binary file.
>
> Best regards
> Romain Crausaz
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org