Nat95 Nat95 - 5 months ago 13
SQL Question

Error in select record from sqlite table

I have a table in SQLITE:

CREATE TABLE "INVENTORY" (
"product" VARCHAR NOT NULL,
"version" VARCHAR NOT NULL,
"productionDate" VARCHAR NOT NULL,
"order" VARCHAR NOT NULL,
"AA" VARCHAR NOT NULL,
"quantity" INTEGER,
"location" INTEGER,
"barcode" VARCHAR,
PRIMARY KEY ("product", "version", "productionDate", "AA", "order")
);


and when I try to select a record (its exists):

SELECT * FROM INVENTORY
WHERE (product='25120171' AND version='02' AND productionDate='091014' AND AA='001' AND order='4420')


I get an error


SQLiteManager: Likely SQL syntax error: SELECT * FROM INVENTORY WHERE
(product='25120171' AND version='02' AND productionDate='091014' AND
AA='001' AND order='4420') [ near "order": syntax error ] Exception
Name: NS_ERROR_FAILURE Exception Message: Component returned failure
code: 0x80004005 (NS_ERROR_FAILURE)
[mozIStorageConnection.createStatement]


If i delete
AND order='4420'
from select query:

SELECT * FROM INVENTORY
WHERE (product='25120171' AND version='02' AND productionDate='091014' AND AA='001');


it works. I can't find where is the error...

Answer

Using order as a column name is probably what leads to the error.

As the doc says

The SQL standard specifies a huge number of keywords which may not be used as the names of tables, indices, columns, databases, user-defined functions, collations, virtual table modules, or any other named object. The list of keywords is so long that few people can remember them all. For most SQL code, your safest bet is to never use any English language word as the name of a user-defined object.

order is one of them.

You could re-write your query to escape the column name:

SELECT * FROM INVENTORY 
WHERE (product='25120171' AND version='02' AND productionDate='091014' AND AA='001' AND `order`='4420')