SQLite Forum

Insert Statement
Login

Insert Statement

(1) By anonymous on 2020-11-27 18:02:45 [link] [source]

Hello I need help with an sqlite/Qt problem. The schema for the table is

CREATE TABLE PROJECT (
"pro_id" INTEGER NOT NULL DEFAULT 1,
"app_id" INTEGER NOT NULL DEFAULT 3,
"Start" TEXT NOT NULL,
"End" TEXT NOT NULL, 
"Output" INTEGER NOT NULL,
"Log Type" INTEGER NOT NULL, 
"Errors" INTEGER NOT NULL, 
"Success" INTEGER NOT NULL, 
"NOTES" VARCHAR NOT NULL DEFAULT '',
"Duration" TEXT NOT NULL DEFAULT '00.00.00.000',
PRIMARY KEY("app_ID", "pro_ID", "Start"),
FOREIGN KEY ("Output") REFERENCES "OUTPUTS" ("ID") , 
FOREIGN KEY ("app_ID", "pro_ID") REFERENCES "PROJECTS"("A_ID", "P_ID"), 
FOREIGN KEY ("Log Type") REFERENCES "TYPE" ("ID"));

The INSERT query (inside Qt) is 

temp = "INSERT INTO PROJECT (pro_id, app_ID, 
Start, End, Output, Log Type, Errors, Success, NOTES, Duration) 
VALUES(1,1, Date & time in quotes, Date & time in quotes, '1, 1, 0, 0, '0.0.0.0.000')";

The error I get is:
QSqlError("1", "Unable to execute", "near \"Type \": syntax error")

I feel like the problem is on the sqlite side and I'm not familiar with it at all. I started it only this year. I feel like the foreign key is where the issue is

(2) By oneeyeman on 2020-11-27 19:39:35 in reply to 1 [link] [source]

Hi, Try this:

temp = "INSERT INTO PROJECT (pro_id, app_ID, Start, End, Output, "Log Type", Errors, Success, NOTES, Duration) VALUES(1,1, Date & time in quotes, Date & time in quotes, '1, 1, 0, 0, '0.0.0.0.000')";

Thank you.

(3) By Larry Brasfield (LarryBrasfield) on 2020-11-27 19:47:19 in reply to 1 [source]

I assume oneeyeman's post will point out that the identifier with a space in it cannot be used without doublequotes.

What I have yet to grasp is why such identifiers are used so often. Do people not realize that whitespacee has delimited identifiers in virtually every computer language for approximately forever? What is the motivation for inviting such problems, and forcing the use of readability degrading quotes to use such identifiers?

(4) By Scott Robison (casaderobison) on 2020-11-27 20:00:20 in reply to 3 [link] [source]

I think it is often due to tooling that automatically creates quote delimited identifiers as a way of "future proofing" the query in case new keywords are added in the future. Alternatively, it may be due to people who are accustomed to such tooling generated identifiers following the same path even though they don't have to.

(5) By Simon Slavin (slavin) on 2020-11-27 20:11:09 in reply to 3 [link] [source]

I've seen it in generators which don't sanitise their inputs. They ask for field names, then generate an appropriate CREATE TABLE statement and other code. Somewhere in the documentation it says "no punctuation" but the software doesn't enforce that. And who reads the documentation ?

(6) By Larry Brasfield (LarryBrasfield) on 2020-11-27 20:12:26 in reply to 2 [link] [source]

Of course, those inner doublequotes will need to be escaped (or quoted) in whatever manner is required by the programming environment. If that "Qt" is like C or C++ in its string literal lexical rules, that will mean preceding each inner doublequote with a '\' character.

(7) By Scott Robison (casaderobison) on 2020-11-27 20:18:51 in reply to 5 [link] [source]

Agreed. In theory, punctuation isn't a huge problem (aside from quotation mark itself) if you're quoting all your identifiers. Leading or trailing whitespace will be if you don't realize it.

(8) By Larry Brasfield (LarryBrasfield) on 2020-11-27 20:19:47 in reply to 5 [link] [source]

There is a large class of behaviors which I call "Wearing the 'Kick me!' sign." Using space-containing identifiers is one of them. For example, some dunderhead decided, for many millions of computer users, that "Program Files" would be a fine name for a directory in which subdirectories would be made to hold individual application files. And there are non-trivial applications, written by people unused to such folly, which cannot work on those machines unless installed somewhere else without any space in the whole pathname. It makes me crazy. The whole filesystem contains files for Pete's sake. Why was that stupid qualifier even needed? Yuurrgghhh.