Is a column name of 'AS' a restricted column name

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Is a column name of 'AS' a restricted column name

Account 69
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));

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)

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)

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

Thank you.

PS, I do not have control over the incoming CSV, so I have to make any
changes during processing.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is a column name of 'AS' a restricted column name

Eric Pankoke
AS is a key word, so I believe you either have to put it in single or double
quotes to use it as a column name:

INSERT INTO [shopdata] (Date_TS, HS, "AS")...

Hopefully I'm remembering correctly what I've read in the past.

Eric Pankoke
Mobile Games Reviewer
My Site: http://www.rustysabre.com/
Reviews: http://www.technobrains.com/, http://www.touchmyapps.com
Blog: http://www.iphonelife.com/blogs/eric-pankoke
Twitter: http://www.twitter.com/RustySabre/
 

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Account 69
Sent: Sunday, December 11, 2011 5:01 AM
To: [hidden email]
Subject: [sqlite] Is a column name of 'AS' a restricted column name

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));

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)

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)

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

Thank you.

PS, I do not have control over the incoming CSV, so I have to make any
changes during processing.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is a column name of 'AS' a restricted column name

Simon Slavin-3
In reply to this post by Account 69

On 11 Dec 2011, at 10:01am, Account 69 wrote:

> 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

No, you got it right:

<http://www.sqlite.org/lang_keywords.html>

The word 'AS' is used in commands like this:

SELECT (quantity * pricePerItem) AS totalPrice FROM invoiceItems

Allowing 'AS' as a column name would make parsing very difficult !

While I'm here, although your table definition will work in SQLite, it might be worth studying up on SQLite3 datatypes so you don't trick yourself into expecting behaviour SQLite doesn't provide.

<http://www.sqlite.org/datatype3.html>

It might be better to do

CREATE TABLE shopdata (
        id INTEGER PRIMARY KEY,
        date_ts INTEGER,
        H_S REAL DEFAULT 0,
        A_S REAL DEFAULT 0);

You did get 'INTEGER PRIMARY KEY' right, though, which most people don't.

Simon.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is a column name of 'AS' a restricted column name

Kees Nuyt
In reply to this post by Account 69
On Sun, 11 Dec 2011 10:01:02 +0000, Account 69 <[hidden email]>
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is a column name of 'AS' a restricted column name

Account 69
In reply to this post by Simon Slavin-3
Thanks for the replies.

I can now work around the problem by either converting the column name
AS to A_S and easily recode the application to use A_S in the SQL
statements and data retrieval or just make sure I quote the column name.


Other aspects of my post, thanks for the REAL pointer for the data type.
I just carried over DOUBLE as it was in the dropdown of SQLExpert when I
created a few test tables.

The Date_TS is not unique, it is just the date in seconds since epoch
and relates to a calendar month.

I shall also recode to strip the single quote from around the values if
it is a number. I again just created a test table in SQLExpert and then
loaded up a few lines of data and then exported out as a sql file and
that was how the data from SQLexpert presented the data in the export
file so I just took it as read.

Thanks for the web reference to the keywords, I looked but you can
imagine how many results you get back looking for column name as in
google etc.

Simon Slavin wrote On 11/12/2011 10:47:

>
> On 11 Dec 2011, at 10:01am, Account 69 wrote:
>
>> 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
>
> No, you got it right:
>
> <http://www.sqlite.org/lang_keywords.html>
>
> The word 'AS' is used in commands like this:
>
> SELECT (quantity * pricePerItem) AS totalPrice FROM invoiceItems
>
> Allowing 'AS' as a column name would make parsing very difficult !
>
> While I'm here, although your table definition will work in SQLite, it might be worth studying up on SQLite3 datatypes so you don't trick yourself into expecting behaviour SQLite doesn't provide.
>
> <http://www.sqlite.org/datatype3.html>
>
> It might be better to do
>
> CREATE TABLE shopdata (
> id INTEGER PRIMARY KEY,
> date_ts INTEGER,
> H_S REAL DEFAULT 0,
> A_S REAL DEFAULT 0);
>
> You did get 'INTEGER PRIMARY KEY' right, though, which most people don't.
>
> Simon.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users