Thanks Simon and David for your attention,
Markos
------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert the usual "Bobby Tables" reply here
https://xkcd.com/327/
You really want to just bind the values to the insert statement, both to
prevent issues and make things simpler. It'll look slightly different in each
language, but it's basically 1 SQL statement text which contains placeholders.
Then for each record you want to insert/update, etc you give it the raw values
separate from the SQL text, and your language of choice will do the magic of
inserting that exact value, whatever type of value that is.
So it might look something like:
myInsertStatement = "insert into foods (name, type_id) values (?, ?);"
for some looping construct:
nameVariable = some name
type_idVariable = some ID thing of whatever type
databaseCursor.execute(myInsertStatement, nameVariable, type_idVariable)
databaseConnection.commit()
Em 08-04-2018 14:03, Simon Slavin escreveu:
On 8 Apr 2018, at 11:54am, Markos <mar...@c2o.pro.br> wrote:
CREATE TABLE foods(
id integer PRIMARY KEY,
type_id integer,
name text );
I can insert type_id without single quote:
INSERT INTO foods (name, type_id) VALUES ('Rice', 16);
And also with single quote:
INSERT INTO foods (name, type_id) VALUES ('Bean', '17');
select * FROM foods;
...
423 16 Rice
424 17 Bean
What are the consequences of inserting values in fields of datatype integer
with single quotes?
Well done for testing what happens rather than just assuming things worked the
way you thought.
When you created the table you specified the affinity of each column. If you
define a column as INTEGER but supply a string, SQLite checks to see whether
that string represents an integer. If it does, then it stores the integer,
effectively doing the translation for you. The actual value stored is integer,
and there is no trace that it was originally specified as a string.
A similar thing happens when you specify that a column has affinity of REAL.
In both cases, SQLite considers that the CREATE command knows better than
whatever specifies the value, and does the conversion. However for the number
to be stored the conversion has to be reversible. If SQLite reverses the
conversion and doesn't get the original string back it stores the string
instead.
You can test to see what SQLite did using
SELECT id, type_id, typeof(type_id), name FROM foods
See section 3 (more usefully, the entire page) of
<https://www.sqlite.org/datatype3.html>
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users