[sqlite] sqlite3_table_column_metadata with nil parameters bug ?

[LacaK]

Hi *,

when I pass to sqlite3_table_column_metadata() function 2,3,4 parameters 
as null i receive SIGSEGV.

Is this expected behavior ?

I understand that it does not give big sense call this function with all 
null parameters, but when I use in generic case as 2,3,4 parameters:


sqlite3_column_database_name(),
sqlite3_column_table_name(),
sqlite3_column_origin_name()

and I have in SQL SELECT function for example : "lower(a) AS a", then 
all 3 above mentioned function returns null and call to 
sqlite3_table_column_metadata() generates SIGSEGV.


IMO such situation should be handled?

Thanks

-Laco.

P.S. I use SQLite 3.14.2
(resend to sqlite-users@mailinglists.sqlite.org)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] PRAGMA table_info(second.table)

[LacaK]

Hi,
when I attach database using f.e ATTACH DATABASE 'test.db' AS tets;
and in attached database is f.e. table "tab1".
Then when I try PRAGMA table_info(test.tab1) , I get error: near ".": 
syntax error.

Is it expected ?
(of course SELECT * FROM test.tab1  works as expected ...)
Thanks
-Laco.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [Fwd: [FEATURE REQUEST] support of %y in strftime()]

[LacaK]

Yes I know, that there is workaround using: 
 substr(strftime('%Y',d),3)


But why not add %y, when it is only 5 lines of source code, take into account 
that %y is supported also by
C strftime() or PHP etc. and it is really useful.

-Laco.


Seriously, what your patch is actually about is to extract the last two
digits out of a four-digit year.  Apart from all the obvious Y2K
problems, there already is a function for that: it's called substr().





Please can any developer do any comment on this request?
Would it be possible to add such support?
Thanks
-Laco.

- Pôvodná správa -
Predmet:[FEATURE REQUEST] support of %y in strftime()
Dátum:  Fri, 21 Sep 2012 14:15:45 +0200
Od: LacaK <la...@zoznam.sk>
Pre:sqlite-users@sqlite.org



Hi *,
some month ago I wrote question about possibility to add support of %y 
(2 digit year) to strftime() function.
Patch is very simple (only few lines of code) and I hope, that will be 
useful for many users.
Patch by Alexey is here: 
http://sqlite.mobigroup.ru/wiki?name=2-digit+year+patch
(although I think, that it can be done in a simpler way ... see my 
comment later)
Can you consider please adding such support ? Is this proper place for 
post such requests ? Can I do something more to add such support ?

TIA
-Laco.

Looking at patch I do not understand, why we add

n += 4;
in case of 'y' ?

I would do:
   case 'W':
+   case 'y':
 n++; 
...


+case 'y': {
+  sqlite3_snprintf(3,[j],"%02d",x.Y % 100); j+=2;
+  break;
+}


  

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

[sqlite] [Fwd: [FEATURE REQUEST] support of %y in strftime()]

[LacaK]

Please can any developer do any comment on this request?
Would it be possible to add such support?
Thanks
-Laco.

- Pôvodná správa -
Predmet:[FEATURE REQUEST] support of %y in strftime()
Dátum:  Fri, 21 Sep 2012 14:15:45 +0200
Od: LacaK <la...@zoznam.sk>
Pre:sqlite-users@sqlite.org



Hi *,
some month ago I wrote question about possibility to add support of %y 
(2 digit year) to strftime() function.
Patch is very simple (only few lines of code) and I hope, that will be 
useful for many users.
Patch by Alexey is here: 
http://sqlite.mobigroup.ru/wiki?name=2-digit+year+patch
(although I think, that it can be done in a simpler way ... see my 
comment later)
Can you consider please adding such support ? Is this proper place for 
post such requests ? Can I do something more to add such support ?

TIA
-Laco.

Looking at patch I do not understand, why we add

n += 4;
in case of 'y' ?

I would do:
  case 'W':
+   case 'y':
n++; 
...


+case 'y': {
+  sqlite3_snprintf(3,[j],"%02d",x.Y % 100); j+=2;
+  break;
+}



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

[sqlite] [FEATURE REQUEST] support of %y in strftime()

[LacaK]

Hi *,
some month ago I wrote question about possibility to add support of %y 
(2 digit year) to strftime() function.
Patch is very simple (only few lines of code) and I hope, that will be 
useful for many users.
Patch by Alexey is here: 
http://sqlite.mobigroup.ru/wiki?name=2-digit+year+patch
(although I think, that it can be done in a simpler way ... see my 
comment later)
Can you consider please adding such support ? Is this proper place for 
post such requests ? Can I do something more to add such support ?

TIA
-Laco.

Looking at patch I do not understand, why we add

n += 4;
in case of 'y' ?

I would do:
   case 'W':
+   case 'y':
 n++; 
...


+case 'y': {
+  sqlite3_snprintf(3,[j],"%02d",x.Y % 100); j+=2;
+  break;
+}

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

Re: [sqlite] support of %y in strftime()

[LacaK]

Hi *,
month ago I wrote question about possibility to add support of %y (2 
digit year) to strftime() function.

Patch is very simple and I hope, that will be useful for many users.
Patch by Alexey is here: 
http://sqlite.mobigroup.ru/wiki?name=2-digit+year+patch
(although I think, that it can be done in a simpler way ... see my 
comment later)

Can you consider please adding such support ?
TIA
-Laco.

Looking at patch I do not understand, why we add

n += 4;
in case of 'y' ?

I would do:
   case 'W':
+   case 'y':
 n++; 
...


+case 'y': {
+  sqlite3_snprintf(3,[j],"%02d",x.Y % 100); j+=2;
+  break;
+}




> See "2-digit year patch" here: http://sqlite.mobigroup.ru/wiki?name=patches

> I did write this becouse 2-digit year number is needed very often for me.

  

Hi *,
there is date-time formating function strftime(), which supports some 
(not all) string formating parameters (like %d, %m, %Y)

There is %Y for 4-digit year.
Is possible add also %y for 2-digit year ? (like in strftime() in 
standard C library)
If it is not a big problem (I hope, that isn't), I would be very 
happy if it will be implemented.

(can I post somewhere bug report, feature request)
TIA
-Laco.

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

Re: [sqlite] support of %y in strftime()

[LacaK]

Looking at patch I do not understand, why we add

n += 4;
in case of 'y' ?

I would do:
   case 'W':
+   case 'y':
 n++; 
...


+case 'y': {
+  sqlite3_snprintf(3,[j],"%02d",x.Y % 100); j+=2;
+  break;
+}




> See "2-digit year patch" here: http://sqlite.mobigroup.ru/wiki?name=patches

> I did write this becouse 2-digit year number is needed very often for me.

  

Hi *,
there is date-time formating function strftime(), which supports some 
(not all) string formating parameters (like %d, %m, %Y)

There is %Y for 4-digit year.
Is possible add also %y for 2-digit year ? (like in strftime() in 
standard C library)
If it is not a big problem (I hope, that isn't), I would be very 
happy if it will be implemented.

(can I post somewhere bug report, feature request)
TIA
-Laco.

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

Re: [sqlite] support of %y in strftime()

[LacaK]

See "2-digit year patch" here: http://sqlite.mobigroup.ru/wiki?name=patches



I did write this becouse 2-digit year number is needed very often for me.


Thanks Alexey,
As this patch is easy and adds only few lines of source code (but as I think 
very useful),
can it be accepted (merged) into main branch?
TIA
-Laco.



Hi *,
there is date-time formating function strftime(), which supports some 
(not all) string formating parameters (like %d, %m, %Y)

There is %Y for 4-digit year.
Is possible add also %y for 2-digit year ? (like in strftime() in 
standard C library)
If it is not a big problem (I hope, that isn't), I would be very happy 
if it will be implemented.

(can I post somewhere bug report, feature request)
TIA
-Laco.

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

[sqlite] support of %y in strftime()

[LacaK]

Hi *,
there is date-time formating function strftime(), which supports some 
(not all) string formating parameters (like %d, %m, %Y)

There is %Y for 4-digit year.
Is possible add also %y for 2-digit year ? (like in strftime() in 
standard C library)
If it is not a big problem (I hope, that isn't), I would be very happy 
if it will be implemented.

(can I post somewhere bug report, feature request)
TIA
-Laco.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

> User also will probably execute some
> queries that do some arithmetic operations on values in that column.
> And they will be really surprised to see that not all the data is
> numbers there.
>
>   
My test shows, that I can successfuly execute queries like (c is NUMERIC 
column):
select c, c+100 from tab;
or
select sum(c) from tab;

It seems, that SQLite converts BLOB->TEXT->REAL when doing such queries
(of course precision is lost, but it works when storage class of value 
is BLOB)

> So you better insert everything as numbers, let SQLite transform
> everything to REAL and lose precision. And tell your users that it's
> limitation of SQLite and you cannot do anything with it, only suggest
> to insert big numbers as text.
>
>   
May be, I will summarize it and I will post it into FreePascal devel 
mailing list ...

Thanks
Laco.

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

And...no conversion is performed if you declare the field as text and insert as 
text.


Yes, but I can not affect column type ... FreePascal SQLite3 connector must be 
able to work with any user database.

And when user defines column like NUMERIC or DECIMAL ... so with NUMERIC column 
affinity,
then it does not help when I write/bind numbers like TEXT (sqlite3_bind_text) 
'123456789123456789.123456789'
because SQLite ALWAYS convert such "text number" to native floating-point or 
integer number, which leads to lost of precision.

So probably before bind I will test if number of significant digits > 15 then I 
will use sqlite3_bind_blob else sqlite3_bind_double

-Laco.

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

Here are two options which will let you get the contents back to the original 
precision:



A) Store the values as BLOBs.
B) Store the value as TEXT, but add a non-digit to the beginning of each number 
value, for example



X24395734857634756.92384729847239842398423964294298473927



Both methods will prevent SQLite from trying to see the value as a number.  Oh 
and since nobody seems to have pointed it out yet, SQLite doesn't have a 
NUMERIC or a DECIMAL column type.  The types can be found here:



Hi all,
thank you all for your answers, advices.

So conclusion is:
A) use sqlite3_bind_blob() then no conversion is performed
B) use sqlite3_bind_text() but with some hack, which "invalidates" numbers

-Laco.

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

/ Problem will be solved if SQLite will store such values as text ... so

/>>/ will behave like this:
/>>/ 1. is supplied value in TEXT (sqlite3_bind_text)
/>>/ 2. if yes then try convert this text value into INTEGER or REAL
/>>/ 3. convert back to text and compare with original value
/>>/ 4. if equal then store it as INTEGER or REAL, if not then store it as is
/>>/ as supplied in (1).
/

Does this mean that, say, '042' or '42.00' are stored as text?


no


Do you think that would be desirable?


no, of course

Base idea is store as TEXT when :
1. column value is supplied as TEXT (only in case sqlite3_bind_text)
2. conversion to REAL or INTEGER leads to loose of precision (digits)

I do not know details how to implement it ;-)
May be, 
1. strip out leading and trailing spaces and zeroes 
2. and then analyze string if contains only digits and decimal separator

3. count number of digits and if > than max precision for REAL or INTEGER then 
do not convert, but store as is

-Laco.

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

If you already have an arbitrary precision number, just encode it to 
text, save it in sqlite and then decode on the way out.


Yes it is possible, but such values (and databases) will not be readable 
by other database connectors

(like for example in PHP etc.)
Problem will be solved if SQLite will store such values as text ... so 
will behave like this:

1. is supplied value in TEXT (sqlite3_bind_text)
2. if yes then try convert this text value into INTEGER or REAL
3. convert back to text and compare with original value
4. if equal then store it as INTEGER or REAL, if not then store it as is 
as supplied in (1).


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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

You still don't say what you're planning on doing with these number...just 
displaying them?


Yes may be ...
I am working on modification of database component for accessing SQLite3 
databases for FreePascal project.
We map declared column's types to native freepascal internal field types.
So DECIMAL and NUMERIC is mapped to TFmtBCDFieldType (which is able to hold up 
to 64 digits)
We read column value using sqlite3_column_text and then convert string representation 
into TBCD (which is internal structure for "arbitrary" precision numbers)
But problem arrives when we want write back values.
We use sqlite3_bind_text and as I wrote a this point we loose precision 
(because SQLite3 forces conversion to floating point values).

TIA
-Laco.

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

/ So only solution is use TEXT columns (with TEXT affinity) ? There is no

/>>/ way how to use DECIMAL columns (with NUMERIC affinity) ?
/>>/ My goal is store numeric values with big precision (as declared per
/>>/ column DECIMAL(30,7)).
/

SQLite happily ignores those numbers in parentheses. There is no arbitrary 
precision floating point data type in SQLite. You get to choose between text, 
64-bit integers, 64-bit IEEE doubles, and blobs.



/ I do not want any conversion to floating-point values ... because such

/>>/ conversion loses digits and is not reversible to original value.
/>>/ What I will expect is: If supplied value can not be "reversibly"
/>>/ converted to floating-point representation (REAL storage class), then
/>>/ store it as text with TEXT storage class
/

Use affinity of NONE (don't specify any type), and figure out in your program 
for each value whether to store as a floating point number or as text. Use 
sqlite3_bind_double or sqlite3_bind_text accordingly.



/ But on this page http://www.sqlite.org/datatype3.html is written:

/>>/ "When text data is inserted into a NUMERIC column, the storage class of
/>>/ the text is converted to INTEGER or REAL (in order of preference) if
/>>/ such conversion is lossless and reversible. For conversions between TEXT
/>>/ and REAL storage classes, SQLite considers the conversion to be lossless
/>>/ and reversible if the first 15 significant decimal digits of the number
/>>/ are preserved. *If the lossless conversion of TEXT to INTEGER or REAL is
/>>/ not possible then the value is stored using the TEXT storage class*."
/

In the examples you've shown, the first 15 significant digits are indeed 
preserved. SQLite appears to behave as documented. What again seems to be the 
problem?


Yes you are right , it seemes so.
So once if I define column as DECIMAL,NUMERIC then there is no chance store in 
such column numeric values out of range of 64bit integers or 64bit floating 
point values, right ?
My guess, hope was, that if I use sqlite3_bind_text with for example 
'123456789123456789.12345' then sqlite3 stores such value as string and do not 
convert them to floating point.
(equal as when I insert non numeric value for example 'abcd' then 'abcd' is 
stored)
Thanks for your assistance
Laco.



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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

Hi Michael,
thank you for response.
So only solution is use TEXT columns (with TEXT affinity) ? There is no 
way how to use DECIMAL columns (with NUMERIC affinity) ?
My goal is store numeric values with big precision (as declared per 
column DECIMAL(30,7)).
I do not want any conversion to floating-point values ... because such 
conversion loses digits and is not reversible to original value.
What I will expect is: If supplied value can not be "reversibly" 
converted to floating-point representation (REAL storage class), then 
store it as text with TEXT storage class ... but this does not happen 
(SQLite converts to floating-point and stores it and looses digits).


But on this page http://www.sqlite.org/datatype3.html is written:
"When text data is inserted into a NUMERIC column, the storage class of 
the text is converted to INTEGER or REAL (in order of preference) if 
such conversion is lossless and reversible. For conversions between TEXT 
and REAL storage classes, SQLite considers the conversion to be lossless 
and reversible if the first 15 significant decimal digits of the number 
are preserved. *If the lossless conversion of TEXT to INTEGER or REAL is 
not possible then the value is stored using the TEXT storage class*."


Laco.


Hi,
I have table like this:
CREATE TABLE tab1 (
a INTEGER,
c DECIMAL(30,7),
...
);

When I am trying insert values like:
INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456);
INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456');

values for c column are always rounded or cast to :
1.23456789012346e+19

If I understand correctly column c has NUMERIC affinity, but when 
storing values, they are stored using REAL storage class.
But why, when conversion from TEXT is NOT lossless ? (AFAIU first 15 
significant digits are not preserved)

Is there way how to store numeric values, which are out of REAL range ?

TIA
-Laco.



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

[sqlite] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

Hi,
I have table like this:
CREATE TABLE tab1 (
 a INTEGER,
 c DECIMAL(30,7),
 ...
);

When I am trying insert values like:
INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456);
INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456');

values for c column are always rounded or cast to :
1.23456789012346e+19

If I understand correctly column c has NUMERIC affinity, but when 
storing values, they are stored using REAL storage class.
But why, when conversion from TEXT is NOT lossless ? (AFAIU first 15 
significant digits are not preserved)

Is there way how to store numeric values, which are out of REAL range ?

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

[sqlite] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

Hi,
I have table like this:
CREATE TABLE tab1 (
a INTEGER,
c DECIMAL(30,7),
...
);

When I am trying insert values like:
INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456);
INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456');

values for c column are always rounded or cast to :
1.23456789012346e+19

If I understand correctly column c has NUMERIC affinity, but when 
storing values, they are stored using REAL storage class.
But why, when conversion from TEXT is NOT lossless ? (AFAIU first 15 
significant digits are not preserved)

Is there way how to store numeric values, which are out of REAL range ?

TIA
-Laco.


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

Re: [sqlite] Delphi dbExpress driver for SQLite3 ?

[LacaK]

Thank you Fred,
but I need multi-DB connectivity. User can set up in ini file, which driver use.
Hm.

-Laco.

> Try this:

> http://www.aducom.com/sqlite/

> You can dump DBExpress completely.

> Fred

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

[sqlite] Delphi dbExpress driver for SQLite3 ?

[LacaK]

Hi All,
I have downloaded dbExpress driver  for SQLite3 from
http://www.bcp-software.nl/artikelen/sqlite.html

When I am using it under Delphi6 (also in Turbo Delphi Explorer)
I receive often error "Library used incorrectly" ...

I have uploaded reproducible test case (+sources - very simple) at 
http://www.mint.sk/download/sqlite_problem.zip

Can anyone confirm that problem or provide some feedback , solution or 
link to other
(free or LGPL) dbExpress (Delphi6) driver for SQLite3?

TIA

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