Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

[ibrahim]

On 16.07.2013 13:26, Bernd Lehmkuhl wrote:

On 16 Jul 2013, at 4:39am, Bernd Lehmkuhl  wrote:


Am 15.07.2013 22:26, schrieb Simon Slavin:

The following two statements do different things.

INSERT INTO myTable VALUES (01)
INSERT INTO myTable VALUES ('01')

Can you tell what's being done in your setup ? Is there a way using your API 
that you can stress that the value you're binding or inserting is text, not a 
number ?

As I use a parameterized query, I'm pretty certain that it should be '01' - the 
second case. Stepping through the code in VS Debugger also shows that DbType of 
that parameter is String and Value is '01'.

If the column in the table really is defined as TEXT, and the INSERT commands 
do have apostrophes around the values, then SQLite3 should not be losing that 
zero.

Can you open the database in some other tool (e.g. the sqlite3 command-line 
tool, available from the SQLite site) and see what the table schema says ? Your 
commands should be something like

sqlite3 myDatabaseFile
.schema
.quit

If you have lots of tables you can do ".schema mytable" instead of just 
".schema".

If you want to experiment you can manually type in an INSERT yourself, then do 
a SELECT and see whether the zeros were preserved.

Simon.

Hi Simon,

The actual table definition is :
CREATE TABLE T_VWG_ABFALLVERZEICHNIS (SCHLUESSEL String, BESCHREIBUNG String, 
BEMERKUNG String, ID_GUID String, IST_PAUSCHALIERT String, 
IST_KATEGORIE_ESK_BETRIEBSSTOF String);

SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select schluessel, typeof(schluessel)
...> from t_vwg_abfallverzeichnis
...> where id_guid in ('BEE7F6108F02416FA0C9D5DC777DB172', 
'6061A3864C2546C4A7DDA9FDB321459F');
1|integer
01 01|text
sqlite>

I *expected* to gain TEXT affinity through the use of System.Data.SQLite and 
it's strongly typed types (doppelt gemoppelt?), but apparently this is not 
totally effective. Maybe Joe Mistachkin can say something about that?

Bernd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
String is not a valid type specifier for TEXT colums see 
http://www.sqlite.org/datatype3.html type names which result in TEXT 
affinity.


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

Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

[Joe Mistachkin]

Bernd Lehmkuhl wrote:
> 
> The actual table definition is :
> CREATE TABLE T_VWG_ABFALLVERZEICHNIS (SCHLUESSEL String, BESCHREIBUNG
String, BEMERKUNG String, ID_GUID String,
> IST_PAUSCHALIERT String, IST_KATEGORIE_ESK_BETRIEBSSTOF String);
> 
> SQLite version 3.7.16.2 2013-04-12 11:52:43
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select schluessel, typeof(schluessel)
>...> from t_vwg_abfallverzeichnis
>...> where id_guid in ('BEE7F6108F02416FA0C9D5DC777DB172',
'6061A3864C2546C4A7DDA9FDB321459F');
> 1|integer
> 01 01|text
> sqlite>
> 
> I *expected* to gain TEXT affinity through the use of System.Data.SQLite
and it's strongly typed types (doppelt
> gemoppelt?), but apparently this is not totally effective. Maybe Joe
Mistachkin can say something about that?
> 

Using test suite infrastructure here (i.e. via Eagle), I get the following
results with a column of type "String":

set db1 [sql open -type SQLite {Data Source=test1.db;}]
sql execute -verbatim $db1 "CREATE TABLE t1 (x String);"
sql execute -verbatim $db1 "INSERT INTO t1 (x) VALUES (1);"
sql execute -verbatim $db1 "INSERT INTO t1 (x) VALUES ('02');"
sql execute -verbatim $db1 "INSERT INTO t1 (x) VALUES (?);" [list param1
Int32 3]
sql execute -verbatim $db1 "INSERT INTO t1 (x) VALUES (?);" [list param1
String 04]
sql execute -execute reader -format list $db1 "SELECT * FROM t1;"

1 2 3 4

The leading zeros are being stripped from the '02' and '04' values by the
SQLite core native library itself, due
to the underlying type affinity of that column.  Declaring the columns to be
of type TEXT should make everything
work in the way you expect.

Repeating the same steps with a column type of "TEXT":

set db2 [sql open -type SQLite {Data Source=test1.db;}]
sql execute -verbatim $db2 "CREATE TABLE t2 (x TEXT);"
sql execute -verbatim $db2 "INSERT INTO t2 (x) VALUES (1);"
sql execute -verbatim $db2 "INSERT INTO t2 (x) VALUES ('02');"
sql execute -verbatim $db2 "INSERT INTO t2 (x) VALUES (?);" [list param1
Int32 3]
sql execute -verbatim $db2 "INSERT INTO t2 (x) VALUES (?);" [list param1
String 04]
sql execute -execute reader -format list $db2 "SELECT * FROM t2;"

1 02 3 04

--
Joe Mistachkin

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

Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

[Simon Slavin]

On 16 Jul 2013, at 12:26pm, Bernd Lehmkuhl  wrote:

> The actual table definition is :
> CREATE TABLE T_VWG_ABFALLVERZEICHNIS (SCHLUESSEL String, BESCHREIBUNG String, 
> BEMERKUNG String, ID_GUID String, IST_PAUSCHALIERT String, 
> IST_KATEGORIE_ESK_BETRIEBSSTOF String);

You have found your problem.  The type 'String' is not supported by SQLite.  It 
interprets the word according to some rules which result with INTEGER columns.  
And since "01" can be interpreted as an integer it accepts that it is an 
integer.

If you can arrange for this table to be defined like this:

> CREATE TABLE T_VWG_ABFALLVERZEICHNIS (SCHLUESSEL TEXT, BESCHREIBUNG TEXT, 
> BEMERKUNG TEXT, ID_GUID TEXT, IST_PAUSCHALIERT TEXT, 
> IST_KATEGORIE_ESK_BETRIEBSSTOF TEXT);

the problem you reported will not happen.  Unfortunately there is no easy way 
in SQLite to change the affinity of a column.  You can only drop the table and 
create a new one.

I agree with you that Joe Mistachkin is probably going to be the best provider 
of a proper official solution.

Viel Glück mit ihm.

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

Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

[Tony Papadimitriou]

This is documented, I think.

Based on 2.1 (bullet 5) of http://www.sqlite.org/datatype3.html the default 
affinity is numeric.  Since STRING is not understood, it has numeric 
affinity.


-Original Message- 
The actual table definition is :
CREATE TABLE T_VWG_ABFALLVERZEICHNIS (SCHLUESSEL String, BESCHREIBUNG 
String, BEMERKUNG String, ID_GUID String, IST_PAUSCHALIERT String, 
IST_KATEGORIE_ESK_BETRIEBSSTOF String);


SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select schluessel, typeof(schluessel)
  ...> from t_vwg_abfallverzeichnis
  ...> where id_guid in ('BEE7F6108F02416FA0C9D5DC777DB172', 
'6061A3864C2546C4A7DDA9FDB321459F');

1|integer
01 01|text
sqlite>

I *expected* to gain TEXT affinity through the use of System.Data.SQLite and 
it's strongly typed types (doppelt gemoppelt?), but apparently this is not 
totally effective. Maybe Joe Mistachkin can say something about that?


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


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

Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

[Bernd Lehmkuhl]

> On 16 Jul 2013, at 4:39am, Bernd Lehmkuhl  wrote:
> 
>> Am 15.07.2013 22:26, schrieb Simon Slavin:
>>>
>>> The following two statements do different things.
>>>
>>> INSERT INTO myTable VALUES (01)
>>> INSERT INTO myTable VALUES ('01')
>>>
>>> Can you tell what's being done in your setup ? Is there a way using your 
>>> API that you can stress that the value you're binding or inserting is text, 
>>> not a number ?
>>
>> As I use a parameterized query, I'm pretty certain that it should be '01' - 
>> the second case. Stepping through the code in VS Debugger also shows that 
>> DbType of that parameter is String and Value is '01'.
> 
> If the column in the table really is defined as TEXT, and the INSERT commands 
> do have apostrophes around the values, then SQLite3 should not be losing that 
> zero.
> 
> Can you open the database in some other tool (e.g. the sqlite3 command-line 
> tool, available from the SQLite site) and see what the table schema says ? 
> Your commands should be something like
> 
> sqlite3 myDatabaseFile
> .schema
> .quit
> 
> If you have lots of tables you can do ".schema mytable" instead of just 
> ".schema".
> 
> If you want to experiment you can manually type in an INSERT yourself, then 
> do a SELECT and see whether the zeros were preserved.
> 
> Simon.

Hi Simon, 

The actual table definition is :
CREATE TABLE T_VWG_ABFALLVERZEICHNIS (SCHLUESSEL String, BESCHREIBUNG String, 
BEMERKUNG String, ID_GUID String, IST_PAUSCHALIERT String, 
IST_KATEGORIE_ESK_BETRIEBSSTOF String);

SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select schluessel, typeof(schluessel)
   ...> from t_vwg_abfallverzeichnis
   ...> where id_guid in ('BEE7F6108F02416FA0C9D5DC777DB172', 
'6061A3864C2546C4A7DDA9FDB321459F');
1|integer
01 01|text
sqlite>

I *expected* to gain TEXT affinity through the use of System.Data.SQLite and 
it's strongly typed types (doppelt gemoppelt?), but apparently this is not 
totally effective. Maybe Joe Mistachkin can say something about that?

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

Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

[Simon Slavin]

On 16 Jul 2013, at 4:39am, Bernd Lehmkuhl  wrote:

> Am 15.07.2013 22:26, schrieb Simon Slavin:
>> 
>> The following two statements do different things.
>> 
>> INSERT INTO myTable VALUES (01)
>> INSERT INTO myTable VALUES ('01')
>> 
>> Can you tell what's being done in your setup ?  Is there a way using your 
>> API that you can stress that the value you're binding or inserting is text, 
>> not a number ?
> 
> As I use a parameterized query, I'm pretty certain that it should be '01' - 
> the second case. Stepping through the code in VS Debugger also shows that 
> DbType of that parameter is String and Value is '01'.

If the column in the table really is defined as TEXT, and the INSERT commands 
do have apostrophes around the values, then SQLite3 should not be losing that 
zero.

Can you open the database in some other tool (e.g. the sqlite3 command-line 
tool, available from the SQLite site) and see what the table schema says ?  
Your commands should be something like

sqlite3 myDatabaseFile
.schema
.quit

If you have lots of tables you can do ".schema mytable" instead of just 
".schema".

If you want to experiment you can manually type in an INSERT yourself, then do 
a SELECT and see whether the zeros were preserved.

Simon.

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

Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

[Bernd Lehmkuhl]

Am 15.07.2013 22:30, schrieb Gerry Snyder:

On 7/15/2013 1:18 PM, Bernd wrote:

 I'm reading that text out of an Oracle-DB into a SQLite table
which has the affected column defined as 'String' - which maps to TEXT
in native SQLite


No. Look at section 2.1 of http://sqlite.org/datatype3.html   Only CHAR,
CLOB, or TEXT cause the column to have TEXT affinity.

HTH,

Gerry Snyder



If I understand correctly, this is right for the C API or rather SQL 
queries without any interpreting layer in between, but the .NET wrapper 
explicitly maps type String to affinity TEXT in SQLiteConvert.cs.


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

Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

[Bernd Lehmkuhl]

Am 15.07.2013 22:26, schrieb Simon Slavin:


On 15 Jul 2013, at 9:18pm, Bernd  wrote:


I know that SQLite is inherently type-less, but I'm using System.Data.SQLite 
which tries it very best to force that into the common ADO.NET schema.
I'm having troubles with some text that has leading zeros, like '01', '02' and 
so on. I'm reading that text out of an Oracle-DB into a SQLite table which has 
the affected column defined as 'String' - which maps to TEXT in native SQLite - 
via a parameterized insert query and a parameter DbType of 'String' as well. 
Nonetheless the leading zero gets stripped off the text. Any ideas how I could 
preserve those leading zeros?


The following two statements do different things.

INSERT INTO myTable VALUES (01)
INSERT INTO myTable VALUES ('01')

Can you tell what's being done in your setup ?  Is there a way using your API 
that you can stress that the value you're binding or inserting is text, not a 
number ?

Simon.


As I use a parameterized query, I'm pretty certain that it should be 
'01' - the second case. Stepping through the code in VS Debugger also 
shows that DbType of that parameter is String and Value is '01'.


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

Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

[Gerry Snyder]

On 7/15/2013 1:18 PM, Bernd wrote:
 I'm reading that text out of an Oracle-DB into a SQLite table 
which has the affected column defined as 'String' - which maps to TEXT 
in native SQLite


No. Look at section 2.1 of http://sqlite.org/datatype3.html   Only CHAR, 
CLOB, or TEXT cause the column to have TEXT affinity.


HTH,

Gerry Snyder


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

Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

[Simon Slavin]

On 15 Jul 2013, at 9:18pm, Bernd  wrote:

> I know that SQLite is inherently type-less, but I'm using System.Data.SQLite 
> which tries it very best to force that into the common ADO.NET schema.
> I'm having troubles with some text that has leading zeros, like '01', '02' 
> and so on. I'm reading that text out of an Oracle-DB into a SQLite table 
> which has the affected column defined as 'String' - which maps to TEXT in 
> native SQLite - via a parameterized insert query and a parameter DbType of 
> 'String' as well. Nonetheless the leading zero gets stripped off the text. 
> Any ideas how I could preserve those leading zeros?

The following two statements do different things.

INSERT INTO myTable VALUES (01)
INSERT INTO myTable VALUES ('01')

Can you tell what's being done in your setup ?  Is there a way using your API 
that you can stress that the value you're binding or inserting is text, not a 
number ?

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

[sqlite] System.Data.SQLite: Leading zeros being stripped off

[Bernd]

I know that SQLite is inherently type-less, but I'm using 
System.Data.SQLite which tries it very best to force that into the 
common ADO.NET schema.
I'm having troubles with some text that has leading zeros, like '01', 
'02' and so on. I'm reading that text out of an Oracle-DB into a SQLite 
table which has the affected column defined as 'String' - which maps to 
TEXT in native SQLite - via a parameterized insert query and a parameter 
DbType of 'String' as well. Nonetheless the leading zero gets stripped 
off the text. Any ideas how I could preserve those leading zeros?

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