sqlite and dealing with variables starting with 0

[sd]

Greetings All,
I'm using SQLite with TCL and had problems inserting numbers with leading 0's into SQLite tables, such as:

db eval {INSERT INTO tablename VALUES($v1,$v2)}

if v1 or v2 looks like an octal (ie. 064530) it is unfortunately inserted into SQLite as 176022. A while back I read somewhere on this forum that somehow using @ instead of $ works:

db eval {INSERT INTO tablename VALUES(@v1,@v2)}

It worked for me although I couldn't find any rationale behind it. Now I found out my variables are interpreted as octal in other parts of my script when using UPDATE and WHERE commands in SQLite:

db eval {UPDATE tablename SET col_1= $v1,
col_2= $v2',
WHERE col_3 = $v3}

when any of the variables has the potential to have a leading 0, I have been using @v instead of $v.

Anyone knows why @ works? Also, wouldn't it be a good idea to always use @ instead of "$" even if some variables will never have a leading 0? After all, in TCL everything's a string.

Thanks,
/sd

[Rich]

sd <sha...@hotmail.com> wrote:
> Greetings All,
> I'm using SQLite with TCL and had problems inserting numbers with
> leading 0's into SQLite tables, such as:

> db eval {INSERT INTO tablename VALUES($v1,$v2)}

> if v1 or v2 looks like an octal (ie. 064530) it is unfortunately
> inserted into SQLite as 176022. A while back I read somewhere on this
> forum that somehow using @ instead of $ works:

I suspect you may be blaming the wrong spot. I'm not seeing this
behavior here:

$ rlwrap tclsh
% package require sqlite3
3.8.0
% db eval {create table int(a integer);}
invalid command name "db"
% sqlite3 db :memory
% exit
rellis@d820:~$ rm \:memory
rellis@d820:~$ rlwrap tclsh
% package require sqlite3
3.8.0
% sqlite3 db :memory
% db eval {create table int(a integer);}
% set x 064530
064530
% db eval {insert into int(a) values ($x);}
% db eval {select * from int;};
64530

The Tcl parser, however, does interpret leading zero decimal numbers as
octal. Therefore I suspect that what you have happening is somewhere
prior to your insert into Sqlite you have asking Tcl to interpret the
leading zero string as an integer value, and it is at that point where
the octal conversion is happening. note:

% set x 064530
064530
% expr {$x}
26968
% set x
064530
% set y [expr {$x + 1}]
26969

> It worked for me although I couldn't find any rationale behind it.
> Now I found out my variables are interpreted as octal in other parts
> of my script when using UPDATE and WHERE commands in SQLite:

...

> Anyone knows why @ works? Also, wouldn't it be a good idea to always
> use @ instead of "$" even if some variables will never have a leading
> 0? After all, in TCL everything's a string.

It is explained in the Sqlite docs:

https://www.sqlite.org/tclsqlite.html

To force a value to be inserted as a BLOB even if it also has a text
representation, us a "@" character to in place of the "$". Like this:

db1 eval {INSERT INTO t1 VALUES(5,@bigstring)}

If the variable does not have a bytearray representation, then "@"
works just like "$". Note that ":" works like "$" in all cases so the
following is another way to express the same statement:

By inserting as a "byte array" you'll _usually_ get the original 8-bit
ascii values you put in to the variable. I say _usually_ because if
you do something that causes the string you put in to be invalidated
(by updating the variable) and then regenerated, the string you get
back will be created from the alternate representation (integer, float,
etc.) and may no longer look like the original string that went in:

% set x ; # x contains just a string
064530
% incr x 0 ; # this invalidates the string representation
26968
% set x ; # this regenerates a new string rep, from the integer value
26968

[sd]

Thank you for the detailed response. I suppose I can use @ to insert into SQLite integers and floats too?

/sd

[Rich]

sd <sha...@hotmail.com> wrote:
> Thank you for the detailed response. I suppose I can use @ to insert
> into SQLite integers and floats too?

Test it. It may work, it may not, until you test you will not know.

However, did you read the whole response? I suspect you are performing
the octal conversion somewhere in your Tcl code, prior to the insert
into Sqlite. To properly fix the root cause, you have to find where in
your Tcl code you are accidentally converting the leading zero strings
into integers. The reason is that one day, you'll end up handling a
leading zero number string containing one or more of the digits 8 or 9
(i.e., 0213893) and when that happens your Tcl script will abort with a
"can not convert to octal" error message.

Best to find the cause now, and fix it.

Hit on how to fix, use the [scan] command to convert leading zero
strings to base-10 values.

[Peter Dean]

On 16/04/15 22:10, sd wrote:
> Thank you for the detailed response. I suppose I can use @ to insert into SQLite integers and floats too?
>
> /sd
>

I've found the only safe way is to explicitly 'cast' data types into
sqlite and spatialite. See http://wiki.tcl.tk/19627 and also
https://sqlite.org/lang_expr.html#castexpr

[Peter Dean]

perhaps you could use [string trimleft $v1 0] if you just want the
decimal integer and not the exact string to be inserted?

[Rich]

Which works perfectly, until the day you try to insert zero ("0") and
string trimleft converts "0" into "".

The safe Tcl way is to use [scan] to convert to an integer.

[Peter Dean]

On 17/04/2015 9:31 AM, Rich wrote:

> Peter Dean <pe...@example.com> wrote:
>
>> perhaps you could use [string trimleft $v1 0] if you just want the
>> decimal integer and not the exact string to be inserted?
>
> Which works perfectly, until the day you try to insert zero ("0") and
> string trimleft converts "0" into "".
>
> The safe Tcl way is to use [scan] to convert to an integer.
>

I should have remembered, I've done that. I'll get me coat.

[sd]

Thank you all for your responses. I don't want to convert the string to integer. Any leading zeros need to remain in place. In fact I'm also dealing with other types of strings too...in some cases I need to look up date-time combination such as "2015-04-17 23:15:30", which I'm having problems with as well. It seems when I do:

set date "2015-04-17 23:15:30"

UPDate t1 SET c1 = v1

WHERE c1 = @date

it seems SQLite does is not finding a matching the value of "date" with those in c1 even though c1 does in fact contains the exact date-time string.

[Eric]

eric@bruno [ ~ ]$ tclsh

% package require sqlite3
3.8.0

% sqlite3 db1 :memory:
% db1 eval {create table t1 (c1 date, v1 date);}
% db1 eval {insert into t1 (c1, v1) values ('2015-04-17 23:15:30',
% '2015-04-17 23:15:50');}
% db1 eval {select * from t1;}
{2015-04-17 23:15:30} {2015-04-17 23:15:50}
% set date "2015-04-17 23:15:30"
2015-04-17 23:15:30
% db1 eval {UPDate t1 SET c1 = v1 WHERE c1 = @date;}
% db1 eval {select * from t1;}
{2015-04-17 23:15:30} {2015-04-17 23:15:50}
% db1 eval {UPDate t1 SET c1 = v1 WHERE c1 = $date;}
% db1 eval {select * from t1;}
{2015-04-17 23:15:50} {2015-04-17 23:15:50}
%

But "@" is documented to be about inserting values, is there anything
documented about using it in the WHERE clause?

Eric
--
ms fnd in a lbry

[Rich]

sd <sha...@hotmail.com> wrote:
> Thank you all for your responses. I don't want to convert the string
> to integer. Any leading zeros need to remain in place.

What "type" did you tell Sqlite to use for the column you are inserting
the leading zero value into? If the database needs to store the
leading zero as is, then you have to tell sqlite that the column is
"text" or "blob".

> In fact I'm also dealing with other types of strings too...in some
> cases I need to look up date-time combination such as "2015-04-17
> 23:15:30", which I'm having problems with as well. It seems when I
> do:

> set date "2015-04-17 23:15:30"

> UPDate t1 SET c1 = v1

> WHERE c1 = @date

> it seems SQLite does is not finding a matching the value of "date"
> with those in c1 even though c1 does in fact contains the exact
> date-time string.

Show a demo. Show the definition of the table, Show what data is in
the table (short list, please), show a select not finding it, or an
update not updating it.

Show the exact code that exhibits the issue (i.e., run the code on your
side first before posting it).

The only way we can really help is if you give us all the information.
So far, you've not given _all_ the information.

[Rich]

Actually, you are correct, "@" is only documented as special for
inserting data, not for querying data.

[sd]

tried to recreate my problem with a simply example, but unable to do so. Everything works fine in below example where I'm able to UPDATE the table using WHERE command. In my other large code WHERE works correctly when I'm trying to match 109330325 (but not 0641037625) using:
db eval {UPDATE t1 SET c1 = @v1 WHERE c1 = '$v2'}}
and only works with 0641037625 (but not 109330325)
dba eval "UPDATE thpt SET ndc = @v1 WHERE ndc = @ND"
I know @ is not defined for WHERE, but it does work!
I'll just have to keep digging further to get to the bottom of this.

# ------------------- this below simple script works ----------------
package require sqlite

proc updateTable {v1 v2} {
db eval {UPDATE t1 SET c1 = @v1 WHERE c1 = $v2}
}

sqlite db c:/sqltest.sql
db eval {CREATE TABLE t1(c1 TEXTVAR)}
foreach v [list 109330325 0641037625] {db eval {INSERT INTO t1 VALUES($v)}}
db close

#---- now update some values
set v F037625
# set v1 109330325 ; set v2 0641037625
sqlite db c:/sqltest.sql
foreach var [db eval {SELECT c1 FROM t1}] {
set x $var
updateTable $v $x
}

db close
# ---------------- end of script ---------------------