Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 15
  1. #1
    New Coder
    Join Date
    Oct 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Get around reserved words in SQL statement

    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?

  • #2
    New Coder
    Join Date
    Oct 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oh, forgot one thing, the SQL table is being used in many other programs and so the column name cannot be changed.

  • #3
    New Coder
    Join Date
    Sep 2002
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Owner.TableName.FieldName

    dbo.mytable.name

    Hope this helps
    a credit card for a newbie from Amex: The Amex Blue card would like to start developing a credit history.

  • #4
    New Coder
    Join Date
    Oct 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Both methods tried, did not work. Any other ideas?

  • #5
    New Coder
    Join Date
    Sep 2002
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Did you try aliasing the Field
    Select MONTH As 'myMonth'

    What is your query?
    a credit card for a newbie from Amex: The Amex Blue card would like to start developing a credit history.

  • #6
    New Coder
    Join Date
    Oct 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #7
    New Coder
    Join Date
    Sep 2002
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Another solution would be to have your DBA create a "View" or stored prcedure for this query.
    a credit card for a newbie from Amex: The Amex Blue card would like to start developing a credit history.

  • #8
    New Coder
    Join Date
    Oct 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This sounds pretty promising, l will give it a try. Thanks aCcodeMonkey.

  • #9
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    do SELECT * FROM Table, then use only rs.Fields("MONTH")
    I am the luckiest man in the world

  • #10
    New Coder
    Join Date
    Oct 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Thanks everybody.

  • #11
    New to the CF scene
    Join Date
    Aug 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,695
    Thanks
    80
    Thanked 4,511 Times in 4,475 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,695
    Thanks
    80
    Thanked 4,511 Times in 4,475 Posts
    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.

    But...

    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
    Code:
    SELECT year AS theYear, month AS theMonth, name, address, phone
    FROM tbl
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #14
    New Coder
    Join Date
    Oct 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #15
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,695
    Thanks
    80
    Thanked 4,511 Times in 4,475 Posts
    Or you can just do
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •