On Sun, 11 Dec 2011 10:01:02 +0000, Account 69 <accoun...@gmail.com>
wrote:
> Hello,
>
> Just started to use SQLite3 instead of MSAccess within my programming
> environment can hit a problem. I have searched the mailing list and also
> other internet sites for help but could not find any.
>
> In my table I have (for simplicity) 4 columns (ID, Date, HS, AS)
> My data comes from an established hourly CSV file that I receive for the
> last 7 years. In the past I would use VB6 to process the CSV and then
> via a SQL Insert statement I would insert into an MSAccess database (for
> local PC usage) and also a MySQL database (for internet usage)
>
> The table is..
> CREATE TABLE [shopdata] (
> [ID] INTEGER PRIMARY KEY,
> [Date_TS] INTEGER,
> [HS] DOUBLE Default (0),
> [AS] DOUBLE Default (0));
If Date_TS is unique over all imported values, it is a candidate key and
can be used as the primary key, and you wouldn't need the ID column:
CREATE TABLE [shopdata] (
[Date_TS] INTEGER PRIMARY KEY,
[HS] DOUBLE default 0.0,
[AS] DOUBLE default 0.0
);
> When I use the following SQL in my application
>
> INSERT INTO [shopdata] (Date_TS, HS, AS) VALUES ('1012003200','15','9')
> The insert fails with an error of (near "AS": syntax error)
AS is a keyword.
Do not use quotes for numeric values in the VALUES clause to prevent
unnecessary conversions (although they will be stored as integer
anyway).
http://www.sqlite.org/datatype3.html ยง 2.3
INSERT INTO [shopdata]
([Date_TS], [HS], [AS])
VALUES
(1012003200,15,9)
> This has had me stumped for a few days especially as I am getting to
> grips with SQLlite and kept thinking I am doing something wrong.
>
> I messed around late last night and I changed the column name of "AS" to
> "A_S" and the inset works fine and the data is stored in the database.
> To recheck I had not changed the table structure in an other way I
> reverted back to using a column name of "AS" and again I got the SQL
> errors of (near "AS": syntax error)
>
> I tried this also in SQLExpert and the exact same happens. In MSAccess
> and MySQL this column name of "AS" works fine (as has been for 7 years).
Every SQL implementation has its own quirks.
> Is a column name of "AS" a restricted column name in SQLlite? Can anyone
> point me to a webpage that species these restricted column names or is
> this an error on my part somewhere
Indeed, as pointed out by others, AS is a keyword.
You can use it if you quote it with double quotes "" or backticks `` or
brackets [] in _all_ of your statements, as you did in the CREATE TABLE
statements. Avoiding keywords is easier, using A_S is a good solution.
> Thank you.
>
> PS, I do not have control over the incoming CSV, so I have to make any
> changes during processing.
--
Regards,
Kees Nuyt
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
