On Thu, Jul 02, 2009 at 11:02:44AM -0400, Shaun Seckman (Firaxis) scratched on
the wall:
> Consider the following SQL Statement:
>
>
>
> create table foo(val1 integer, val2 integer, val3 integer);
>
> insert into foo values('1', '2', '3');
>
>
>
> When I call sqlite3_column_int(stmt, columnNum), must it convert the
> string to integer each time or will it perform that conversion on
> insertion?
>
> Is it faster at retrieving the values as integers if I insert them as
> integers instead of strings?
I suggest you read http://sqlite.org/datatype3.html in detail.
If the column is defined as "integer", the column affinity will be
INTEGER. In that case, an attempt to convert the string to an
integer (or, failing that, a real) is made as part of the INSERT.
If no conversion is possible, the value is stored as TEXT.
So the statement:
INSERT INTO foo VALUES (1, 1.5, '1');
should result in an integer (native), a real/float (native) and a
integer (converted). The statement:
INSERT INTO foo VALUES ('1', '1.5', '1.5abc');
should result in an integer (converted), a real/float (converted),
and a string (native) for this specific table.
You can use quote() to have SQLite show you the text representation
of the stored value. If it has single quotes, it is a string. If it
is bare, it is either an integer or real/float.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
