How to query a table with a hyphen in the name?

I have a database that has a table with a hyphen in the name (don't look at me, I didn't do it!).   Let's call the table E-Mail_Addresses.   I need to query that table.  The database is Intersystems Cache, but it accepts standard SQL queries.

I need something that looks like

SELECT * FROM [E-Mail_Addresses] WHERE Name='JohnDoe'

Except that I need it to work.  If it matters to anyone, I'm coding in VBS using ADODB and the InterSystems 5.1 ODBC driver.


Thanks!!
LVL 18
exx1976Asked:
Who is Participating?
 
Justin EllenbeckerConnect With a Mentor IT DirectorCommented:
Perhaps wherever you are getting the table name from is wrong.  Perhaps it is just and underscore and someone forgot to hold shift.
0
 
Patrick MatthewsCommented:
Are you saying that that does not work?  Generally, you would encase "non-standard" database object names in square brackets...
0
 
AmmarRCommented:
hi

if you have a hyphen in the data then you need to double it in your script

check code below

SELECT * FROM [E-Mail_Addresses] WHERE Name='''JohnDoe'''

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
mustaccioCommented:
Try enclosing the table name in double quotes (this is standard SQL):

SELECT * FROM "E-Mail_Addresses" WHERE Name='JohnDoe'

Note that this makes the table name case-sensitive; if its name is in fact "e-mail_addresses" or "E-MAIL_ADDRESSES" it won't match.
0
 
exx1976Author Commented:
Below is the code (VBS) that I'm using.

This is the error that I get:

[SQLCODE: <-1>:<Invalid SQL statement>]
[Cache Error: <<SYNTAX>errdone+1^%qaqqt>]
[Details: <Prepare>]
[%msg: < SQL ERROR #1: `ID' expected, `[' found^SELECT * FROM [>]
Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.RecordSet")
oConn.Open "DRIVER={InterSystems ODBC};SERVER=FQDN.domain.local;DATABASE=database_name;UID=user;PWD=pwd"
If oConn.State <> 1 Then MsgBox "connect failed"
SQLCmd = "SELECT * FROM [E-Mail_Addresses] WHERE Name='JohnDoe'"
oRS.Open SQLCmd, oConn

Open in new window

0
 
exx1976Author Commented:
Mustaccio - unfortunately, no, the double quotes doesn't work.  The error is:

[SQLCODE: <-1>:<Invalid SQL statement>]
[Cache Error: <<SYNTAX>errdone+1^%qaqqt>]
[Details: <Prepare>]
[%msg: < SQL ERROR #1: `ID' expected, `:' found^SELECT * FROM :%qpar>]
0
 
mustaccioCommented:
Did you escape double quotes in your double-quoted SQLCmd string?
0
 
exx1976Author Commented:
Yes.  The actual code used was

SQLCmd = "SELECT * FROM ""Co-Signer_Route_Bal"" WHERE ACCOUNT_NUM='36'"

I also tried

SQLCmd = "SELECT * FROM """"Co-Signer_Route_Bal"""" WHERE ACCOUNT_NUM='36'"

with the same result.
0
 
exx1976Author Commented:
Ok..  So now we know it's not email addresses..   LOL   Oops!  I feel like such a n00b
0
 
exx1976Author Commented:
That's the most ridiculous thing I've ever heard...  Let me go try it!
0
 
exx1976Author Commented:
Oops  no!!  I wanted to accept 33704676
0
 
exx1976Author Commented:
Go figure, typo in the FM..
0
All Courses

From novice to tech pro — start learning today.