date:20161203

Re: [sqlite] Problem with CASE in WHERE clause

2016-12-03 Thread Frank Millman

From: Simon Slavin 
Sent: Sunday, December 04, 2016 8:42 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Problem with CASE in WHERE clause

On 4 Dec 2016, at 8:42am, Simon Slavin slav...@bigfraud.org wrote:

> What are we allowed to change ?  Are you asking us to find a "SELECT" command 
> which works both in SQLite and PostgreSQL ?  Can we change the table 
> definition too ?

Er, sorry, I was not asking you to change anything.

If a column contains a ‘1’, I would expect sqlite3 to return true when testing 
for ‘1’, but in my example it returns false.

If this is a bug, then I am happy to wait for a fix.

If it is just the way sqlite3 works, a bit of an explanation would help, as 
that could assist me in finding a workaround.

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

Re: [sqlite] Problem with CASE in WHERE clause

2016-12-03 Thread Simon Slavin


On 4 Dec 2016, at 5:33am, Frank Millman  wrote:

> I really need this to work

What are we allowed to change ?  Are you asking us to find a "SELECT" command 
which works both in SQLite and PostgreSQL ?  Can we change the table definition 
too ?

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

[sqlite] Problem with CASE in WHERE clause

2016-12-03 Thread Frank Millman

Hi all

I have a problem using a CASE statement in a WHERE clause.

On testing a column which contains a ‘1’, WHERE returns true if I test for 1 
(without the quotes), but false if I test for ‘1’ (with quotes).

CREATE TABLE test_1 (tran_type TEXT, amount INTEGER, posted BOOL)

INSERT INTO test_1 (tran_type, amount, posted) values ('inv', 100.00, '1')

SELECT * FROM test_1 WHERE CASE WHEN tran_type = 'inv' THEN posted END = 1
[(‘inv’, 100, 1)]

SELECT * FROM test_1 WHERE CASE WHEN tran_type = 'inv' THEN posted END = ‘1’
[]

I really need this to work, as my app is cross-platform and cross-database, and 
PostgreSQL does not accept an integer for a BOOL column.

I am using version 3.14.2 on Windows 10.

Any suggestions?

Thanks

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

Re: [sqlite] Datatype for prices (1,500)

2016-12-03 Thread Barry Smith

Nice.

Say goodbye to transitive equality though.

> On 3 Dec 2016, at 5:02 AM, Keith Medcalf  wrote:
> 
> 
> feq (and friends) are an extension I wrote that does proper floating point 
> comparisons:
> 
> /*
> ** 2015-11-09
> **
> ** The author disclaims copyright to this source code.  In place of
> ** a legal notice, here is a blessing:
> **
> **May you do good and not evil.
> **May you find forgiveness for yourself and forgive others.
> **May you share freely, never taking more than you give.
> **
> **
> **
> ** This SQLite extension implements functions for comparison of floating point
> ** double precision numbers x and y where x is within a specified number of
> ** ULPs of y.
> **
> ** The default number of ULPs if not specified is 5.
> ** The size of an ULP is based on the scale of x.
> ** If only one value is specified it will be a y value and x will be 0.0
> **
> **   ulp(x) -> ULP precision of x (increment to next representable number)
> **   epsilon(x) -> ULP precision of x (alias of ulp)
> **   ulps(x, y) -> number of ULP of x in (x - y)
> **
> **   flt(x[, y[, u]])   -> x less than y
> **   fle(x[, y[, u]])   -> x less or equal y
> **   feq(x[, y[, u]])   -> x equal y
> **   fge(x[, y[, u]])   -> x greater or equal y
> **   fgt(x[, y[, u]])   -> x greater than y
> **   fne(x[, y[, u]])   -> x not equal y
> **
> */
> #ifdef __cplusplus
> extern "C" {
> #endif
> 
> /*
> ** If the MATH.H is already included, use the math library
> ** otherwise make sure memcpy is defined
> */
> 
> #ifndef _MATH_H_
> #include 
> #endif
> 
> #ifndef SQLITE_PRIVATE
>#define SQLITE_PRIVATE static
> #endif
> 
> #ifdef SQLITE_CORE
>#include "sqlite3.h"
> #else
>#ifdef _HAVE_SQLITE_CONFIG_H
>#include "config.h"
>#endif
>#include "sqlite3ext.h"
>SQLITE_EXTENSION_INIT1
> #endif
> 
> /*
> ** Common Functions
> */
> 
> #ifdef _MATH_H_
> 
> /*
> ** If we have the math library then use it
> */
> 
> static double epsilon(double value)
> {
>int exponent;
>double mantissa = frexp(value, );
>return ldexp(1.0, exponent - 53);
> }
> #define sqlfcmp_fabs fabs
> 
> #else
> 
> /*
> ** If we do not have the math library already, do not force it to be included
> */
> 
> static double epsilon(double value)
> {
>sqlite_int64 a;
>double r = value;
>if (r < 0)
>r = -r;
>memcpy(, , sizeof(a));
>if (a == 0)
>a = (970LL << 52);
>else
>a = ((a >> 52) - 52LL) << 52;
>memcpy(, , sizeof(a));
>return r;
> }
> 
> static double sqlfcmp_fabs(double v)
> {
>if (v < 0)
>return -v;
>return v;
> }
> #endif
> 
> static double distance(double x, double y)
> {
>return (x - y) / epsilon(x);
> }
> 
> /*
> ** Return the distance to the next representable number
> */
> 
> SQLITE_PRIVATE void _ulp(sqlite3_context *context, int argc, sqlite3_value 
> **argv)
> {
>sqlite3_result_double(context, epsilon(sqlite3_value_double(argv[0])));
> }
> 
> /*
> ** Return the number of representable numbers between x and y based on the 
> ULP of x
> ** if only one parameter, it is y and x is 0.0
> */
> 
> SQLITE_PRIVATE void _ulps(sqlite3_context *context, int argc, sqlite3_value 
> **argv)
> {
>double x = 0.0;
>double y = 0.0;
>uintptr_t flag = (uintptr_t)sqlite3_user_data(context);
>if ((argc == 0) || (argc > 2))
>return;
>if (argc == 1)
>y = sqlite3_value_double(argv[0]);
>else
>{
>x = sqlite3_value_double(argv[0]);
>y = sqlite3_value_double(argv[1]);
>}
>sqlite3_result_double(context, distance(x, y));
> }
> 
> /*
> ** Perform Floating Point (Double) Comparisons using ULP of first parameter
> **   first parameter will be 0.0 if only one parameter specified, which will 
> be y
> ** User Context defines operations
> **  flag == 0   x != y
> **  flag & 1x < y
> **  flag & 2x = y
> **  flag & 4x > y
> ** Flag values are additive
> **  flag & 3x <= y
> **  flag & 6x >= y
> ** optional third parameter is ULPs of x to consider equal, defaults to 5
> ** if only one parameter then test if x within 5 ULPs of 0.0
> */
> 
> SQLITE_PRIVATE void _fpc(sqlite3_context *context, int argc, sqlite3_value 
> **argv)
> {
>double x = 0.0;
>double y = 0.0;
>double ulps = 0.0;
>double delta = 5.0;
>uintptr_t flag = (uintptr_t)sqlite3_user_data(context);
>if ((argc == 0) || (argc > 3))
>return;
>if (argc == 1)
>y = sqlite3_value_double(argv[0]);
>else
>{
>x = sqlite3_value_double(argv[0]);
>y = sqlite3_value_double(argv[1]);
>}
>if (argc > 2)
>delta = sqlfcmp_fabs(sqlite3_value_double(argv[2]));
>ulps = distance(x, y);
>if (   ((flag & 2)  && (sqlfcmp_fabs(ulps) <= delta))   /* flag & 2 means 
> test equal */
>|| ((flag & 1)  && (ulps < -delta))

Re: [sqlite] Searching sqlite3dll for WinCE 5.2 aka Windows Embedded Handheld 6.5 Classic

2016-12-03 Thread Simon Slavin

On 3 Dec 2016, at 12:33pm, Ertan Küçükoğlu  wrote:

> Now, I have to distribute same application on Windows Embedded Systems. But;
> 1) CE OS 5.2.29354 device: Intermec CK3R, runs fine for first time after a
> device boot and then gives me error saying "Can not load SQLite client
> library "sqlite3.dll", Check your installation.".
> 2) CE OS 5.2.29040 device: Motorola MC55A, runs fine for first time after a
> device boot. Then gives same error error for odd run trials number, and runs
> fine for even run trials.
> 
> Both devices; if application is run. It runs perfectly. No issues at all.

Sprinkle a few extra copies of the DLL about.  Put one in the App folder, and 
some in some System and Library folders which already have DLLs in.  You may be 
able to find a location which fixes these problems.

The one which alternatingly works and doesn’t works suggests some sort of 
configurations or permission problem.  The above may or may not fix it.

> I am just searching for another sqlite3 DLL compiled for CE systems to
> understand if this problem is because of DLL, or Lazarus, or something else.

My guess is that this problem will occur with any DLL at all.  And that you can 
find better help in a Lazarus forum than here.  But that’s just a guess.

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

[sqlite] Searching sqlite3dll for WinCE 5.2 aka Windows Embedded Handheld 6.5 Classic

2016-12-03 Thread Ertan Küçükoğlu

Hello,

I have an application developed with Lazarus. I already have a sqlite3.dll
v3.9.2 for CE which works just fine under OS "Microsoft Windows CE Version
6.0" for several years.

Now, I have to distribute same application on Windows Embedded Systems. But;
1) CE OS 5.2.29354 device: Intermec CK3R, runs fine for first time after a
device boot and then gives me error saying "Can not load SQLite client
library "sqlite3.dll", Check your installation.".
2) CE OS 5.2.29040 device: Motorola MC55A, runs fine for first time after a
device boot. Then gives same error error for odd run trials number, and runs
fine for even run trials.

Both devices; if application is run. It runs perfectly. No issues at all.

I am just searching for another sqlite3 DLL compiled for CE systems to
understand if this problem is because of DLL, or Lazarus, or something else.

Any help is appreciated.

Thanks.

Regards,
Ertan Küçükoğlu


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

Re: [sqlite] Datatype for prices (1,500)

2016-12-03 Thread Keith Medcalf


feq (and friends) are an extension I wrote that does proper floating point 
comparisons:

/*
** 2015-11-09
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**May you do good and not evil.
**May you find forgiveness for yourself and forgive others.
**May you share freely, never taking more than you give.
**
**
**
** This SQLite extension implements functions for comparison of floating point
** double precision numbers x and y where x is within a specified number of
** ULPs of y.
**
** The default number of ULPs if not specified is 5.
** The size of an ULP is based on the scale of x.
** If only one value is specified it will be a y value and x will be 0.0
**
**   ulp(x) -> ULP precision of x (increment to next representable number)
**   epsilon(x) -> ULP precision of x (alias of ulp)
**   ulps(x, y) -> number of ULP of x in (x - y)
**
**   flt(x[, y[, u]])   -> x less than y
**   fle(x[, y[, u]])   -> x less or equal y
**   feq(x[, y[, u]])   -> x equal y
**   fge(x[, y[, u]])   -> x greater or equal y
**   fgt(x[, y[, u]])   -> x greater than y
**   fne(x[, y[, u]])   -> x not equal y
**
*/
#ifdef __cplusplus
extern "C" {
#endif

/*
** If the MATH.H is already included, use the math library
** otherwise make sure memcpy is defined
*/

#ifndef _MATH_H_
#include 
#endif

#ifndef SQLITE_PRIVATE
#define SQLITE_PRIVATE static
#endif

#ifdef SQLITE_CORE
#include "sqlite3.h"
#else
#ifdef _HAVE_SQLITE_CONFIG_H
#include "config.h"
#endif
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
#endif

/*
** Common Functions
*/

#ifdef _MATH_H_

/*
** If we have the math library then use it
*/

static double epsilon(double value)
{
int exponent;
double mantissa = frexp(value, );
return ldexp(1.0, exponent - 53);
}
#define sqlfcmp_fabs fabs

#else

/*
** If we do not have the math library already, do not force it to be included
*/

static double epsilon(double value)
{
sqlite_int64 a;
double r = value;
if (r < 0)
r = -r;
memcpy(, , sizeof(a));
if (a == 0)
a = (970LL << 52);
else
a = ((a >> 52) - 52LL) << 52;
memcpy(, , sizeof(a));
return r;
}

static double sqlfcmp_fabs(double v)
{
if (v < 0)
return -v;
return v;
}
#endif

static double distance(double x, double y)
{
return (x - y) / epsilon(x);
}

/*
** Return the distance to the next representable number
*/

SQLITE_PRIVATE void _ulp(sqlite3_context *context, int argc, sqlite3_value 
**argv)
{
sqlite3_result_double(context, epsilon(sqlite3_value_double(argv[0])));
}

/*
** Return the number of representable numbers between x and y based on the ULP 
of x
** if only one parameter, it is y and x is 0.0
*/

SQLITE_PRIVATE void _ulps(sqlite3_context *context, int argc, sqlite3_value 
**argv)
{
double x = 0.0;
double y = 0.0;
uintptr_t flag = (uintptr_t)sqlite3_user_data(context);
if ((argc == 0) || (argc > 2))
return;
if (argc == 1)
y = sqlite3_value_double(argv[0]);
else
{
x = sqlite3_value_double(argv[0]);
y = sqlite3_value_double(argv[1]);
}
sqlite3_result_double(context, distance(x, y));
}

/*
** Perform Floating Point (Double) Comparisons using ULP of first parameter
**   first parameter will be 0.0 if only one parameter specified, which will be 
y
** User Context defines operations
**  flag == 0   x != y
**  flag & 1x < y
**  flag & 2x = y
**  flag & 4x > y
** Flag values are additive
**  flag & 3x <= y
**  flag & 6x >= y
** optional third parameter is ULPs of x to consider equal, defaults to 5
** if only one parameter then test if x within 5 ULPs of 0.0
*/

SQLITE_PRIVATE void _fpc(sqlite3_context *context, int argc, sqlite3_value 
**argv)
{
double x = 0.0;
double y = 0.0;
double ulps = 0.0;
double delta = 5.0;
uintptr_t flag = (uintptr_t)sqlite3_user_data(context);
if ((argc == 0) || (argc > 3))
return;
if (argc == 1)
y = sqlite3_value_double(argv[0]);
else
{
x = sqlite3_value_double(argv[0]);
y = sqlite3_value_double(argv[1]);
}
if (argc > 2)
delta = sqlfcmp_fabs(sqlite3_value_double(argv[2]));
ulps = distance(x, y);
if (   ((flag & 2)  && (sqlfcmp_fabs(ulps) <= delta))   /* flag & 2 means 
test equal */
|| ((flag & 1)  && (ulps < -delta)) /* flag & 1 means 
test less than */
|| ((flag & 4)  && (ulps > delta))  /* flag & 4 means 
test greater than */
|| ((flag == 0) && (sqlfcmp_fabs(ulps) > delta)))   /* no flags test 
not equal */
sqlite3_result_int(context, 1);
else
sqlite3_result_int(context, 0);
}

/*
** If compiling as a builtin extension, don't export the initializer -- make it 
SQLITE_PRIVATE
** Change name of

Re: [sqlite] Problem with CASE in WHERE clause

Re: [sqlite] Problem with CASE in WHERE clause

[sqlite] Problem with CASE in WHERE clause

Re: [sqlite] Datatype for prices (1,500)

Re: [sqlite] Searching sqlite3dll for WinCE 5.2 aka Windows Embedded Handheld 6.5 Classic

[sqlite] Searching sqlite3dll for WinCE 5.2 aka Windows Embedded Handheld 6.5 Classic

Re: [sqlite] Datatype for prices (1,500)

7 matches

Site Navigation

Mail list logo

Footer information