View Full Version : Get around reserved words in SQL statement
01-16-2003, 07:25 PM
I have an ASP page that use ODBC to connect to a SQL table in Tandem. One of the fields of the SQL table is MONTH which I think is a reserved word in ASP. So when I run the page that have a SQL satement "select MONTH .....", it give me the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Tandem][ODBC Driver][NonStop SQL] ODBC Server message (32010) : Syntax error near MONTH.
Is there a way to get around it?
01-16-2003, 07:26 PM
Oh, forgot one thing, the SQL table is being used in many other programs and so the column name cannot be changed.
01-16-2003, 07:40 PM
One way to confuse.... Uh, use reserved words in SQL is to bracket the field names
SQL = "INSET INTO Accounts (username,[password]) Values('" & myVar & "','" & myVar1 & "')"
This also works for field names/aliases with spaces
SELECT First+' '+Last AS [User Name] FROM Users
The other "prefferred" is tu use a fully qualified id
Hope this helps :cool:
01-16-2003, 07:41 PM
Both methods tried, did not work. Any other ideas?
01-16-2003, 08:02 PM
Did you try aliasing the Field
Select MONTH As 'myMonth'
What is your query?
01-16-2003, 08:10 PM
I just try it with a simple "SELECT MONTH FROM TABLE1 WHERE COLUMN1 = ....", and asp just doesn't like to see the word MONTH. No matter it enclosed it with  or use alias or owner.table.columnname.
I am thinking this may be caused by the ODBC connection to Tandem.
01-16-2003, 08:27 PM
Another solution would be to have your DBA create a "View" or stored prcedure for this query.
01-16-2003, 08:29 PM
This sounds pretty promising, l will give it a try. Thanks aCcodeMonkey.
01-16-2003, 08:58 PM
do SELECT * FROM Table, then use only rs.Fields("MONTH")
01-16-2003, 09:05 PM
Can't use SELECT * because the actual SQL statement is a very complicated one that left joins 2 other tables and have to specified the MONTH and some other columns. The good news is I finally got it done. There is a tandem command that I can use to wrap around the SQL statement in another SQL statement.
08-24-2012, 03:39 PM
What are the chances pizzaguy is still around and will actually share the answer? It's 9 years later, and I could really use the help. Same exact problem. I'm interacting with a Tandem table via Microsoft Query/ODBC. My table has YEAR and MONTH as columns. I get "syntax error near YEAR", no matter what I put around the thing. Thanks in advance.
08-24-2012, 09:41 PM
All databases that I know of have some way of escaping keywords so they can be used as field or table names.
Access uses [...]
SQL Server allows both [...] and "..." (the latter only if ANSI QUOTES are specified)
MySQL uses `...`
So look in your Tandem DB manual for "escape" or "reserved" and I'll bet you will find it.
I would note that the ANSI standard is "..." (because strings are *supposed* to use '...'). So you could certainly try that. But looking in the manual is the best idea.
08-25-2012, 01:57 AM
Well, I read everything I could in the HP Non-Stop DB manual (which is what happend to Tandem DB, by the by) and they mention no way to get around this.
MONTH and YEAR are *NOT* listed as keywords in that manual.
So I'd guess that the culprit is the ODBC driver.
I think the "VIEW" answer is your best bet.
Say you have table TBL and columns YEAR, MONTH, NAME, ADDRESS, PHONE.
Just create a VIEW that does
SELECT year AS theYear, month AS theMonth, name, address, phone
Name the view something obvious, maybe V_TBL.
And then every place you are now using TBL in your code, instead use V_TBL, and just change your code to look for theYear and theMonth.
08-27-2012, 02:32 PM
Can't believe after 9 years I can still remember what I did and still have the user id and password to log in to answer your question. My solution is like this:
1. Set up your odbc connection, command, etc like you would in a normal odbc query.
2. Write up your sql query as if you are working in Tandem, say you name it as mQuery.
3. Wrap around your Tandem sql query mQuery with this:
"SELECT " & Chr(34) & "TDM: SQL " & mQuery & Chr(34)
Hope this helps.
08-27-2012, 09:29 PM
Or you can just do
odbcSql = "SELECT ""TDM: SQL " & mQuery & """"
To get a quote in a string in VBScript, you can use CHR(34), but you can also just use "" as an escape to get a single quote.
Fascinating. What a weird way to do it! Would never have stumbled on that one. I'll be the VIEW would work, too, but who cares.
NonStop DB is really showing its age though, in not having any way to "escape" keywords as fields.