ASE, SQL, OLE, ADE

ASE, SQL, OLE, ADE

Reply
*Swarbrick, Alan
Message 1 of 3 (391 Views)

SQL statement fails with hyphenated column names

391 Views, 2 Replies
01-13-2000 03:09 AM
Does anyone know who I can retrieve values from column names that have
hyphens in them eg
select * from .... works fine in R14 sqled
select item, u-m from... does not.

Alan
*Fleming, Jon
Message 2 of 3 (391 Views)

Re: SQL statement fails with hyphenated column names

01-13-2000 04:35 PM in reply to: *Swarbrick, Alan
You're asking for trouble.

Here's the theory, from the SQL standard.

SQL recognizes two classes of identifiers (table names, column names,
and the like). "Regular" identifiers do not contain any delimiter
characters (such as spaces). "Delimited" identifiers contain delimiter
characters or are SQL keywords. Delimited identifiers _must_ be
surrounded with double quotes in the SQL statement. Regular identifiers
_may_ be surrounded with double quotes in the SQL statement.

Piece o' cake, huh? You might think that you can just surround all your
identifiers with double quotes and you're home free. But here's the
unfortunate facts.

AutoCAD R14 requires you to use double quotes around identifiers when it
wants you to, and will not allow you to use double quotes around
identifiers when it doesn't want you to. When it wants double quotes
and when it doesn't is not documented. AutoCAD never requires double
quotes around an identifier that is not an SQL keyword, is upper case,
and contains only characters from the set A-Z, 0-9, and _ (underscore).
Use anything that doesn't fit those constraints and you're on your own.
I have seen supposedly identical systems running identical versions of
AutoCAD and identical LISP programs, and on one system AutoCAD wanted
double quotes and on the other it wouldn't accept them.

So the following might work:

(setq SQLStatement "SELECT ITEM, \"U-M\" FROM ...")

but it might fail on some machines. If you do see it work on some and
fail on others, and find out why, I'd love to know.

My free library, DBL202.ZIP from http://www.cadalog.com, contains a few
functions that allow you to dynamically find out what form AutoCAD
wants. But they're not 100% guaranteed to work with table names (they
work 100% with column names) and they are s l o w. The code required
to use them gets pretty convoluted. Feel free to download DBL and look
at them.

jrf
Member of the Autodesk Discussion Forum Moderator Program

In article <85livk$kcg31@adesknews2.autodesk.com>, Alan Swarbrick wrote:
> Does anyone know who I can retrieve values from column names that have
> hyphens in them eg
> select * from .... works fine in R14 sqled
> select item, u-m from... does not.
*Fleming, Jon
Message 3 of 3 (391 Views)

Re: SQL statement fails with hyphenated column names

01-14-2000 04:12 AM in reply to: *Swarbrick, Alan
One thing I forgot to say about R14's requirements on double quotes;
they are, of course, required wherever the SQL standard requires them
(when the identifier contains delimiters or is an SQL keyword). The
example you gave does not require quotes in SQL, but AutoCAD may require
them.

jrf
Member of the Autodesk Discussion Forum Moderator Program

In article <85livk$kcg31@adesknews2.autodesk.com>, Alan Swarbrick wrote:
> Does anyone know who I can retrieve values from column names that have
> hyphens in them eg
> select * from .... works fine in R14 sqled
> select item, u-m from... does not.
Post to the Community

Have questions about Autodesk products? Ask the community.

New Post
Need installation help?

Start with some of our most frequented solutions or visit the Installation and Licensing Forum to get help installing your software.