Is "From" a legal col name.

Thread view:

aaronfude@gmail.com - 17 Apr 2006 02:54 GMT

Hi,

This is not really a java question, but java is my platform so I'm
here.

Is "From" a legal column name? Doesn't seem so... I get a syntax error
exception. But I've inherited a database with a From column. What can I
do?

Thanks!

Aaron Fude

Reply to this Message

Thomas Kellerer - 17 Apr 2006 08:49 GMT

aaronfude@gmail.com wrote on 17.04.2006 03:54:

> Hi,
>
[quoted text clipped - 4 lines]
> exception. But I've inherited a database with a From column. What can I
> do?

FROM is a reserved word in SQL. According to the standard, if you use a reserved
word as an identifier (be it a table or a column) you have to quote it with
double quotes:

SELECT "FROM" FROM the_table;

Note that most DBMS are case-sensitiv when using double quotes (I think this is
also part of the ANSI definition), so maybe in your case it should read:

SELECT "From" FROM the_table;

Thomas

Reply to this Message

Jan M. Nelken - 17 Apr 2006 18:16 GMT

> Hi,
>
[quoted text clipped - 8 lines]
>
> Aaron Fude

It depends - mostly on database vendor. For example in DB2 it is OK to:

D:\Working>db2 create table table (select char(1), from char(1))
DB20000I The SQL command completed successfully.

D:\Working>db2 insert into table values('S','F')
DB20000I The SQL command completed successfully.
D:\Working>db2 select select,from from table table

SELECT FROM
------ ----
S F

1 record(s) selected.

Question is whether "From" column name is stored in your catalog as Uppercase F
followed by lowercase r o m. If so - try quoting exactly like it is spelled:

select "From" from ....

as suggested before.

Jan M. Nelken

Reply to this Message

Lee Fesperman - 18 Apr 2006 00:30 GMT

> > Is "From" a legal column name? Doesn't seem so... I get a syntax error
> > exception. But I've inherited a database with a From column. What can I
[quoted text clipped - 14 lines]
>
> 1 record(s) selected.

Interesting ... and very weird. However, it is definitely illegal for SQL92.

> Question is whether "From" column name is stored in your catalog as Uppercase F
> followed by lowercase r o m. If so - try quoting exactly like it is spelled:
>
> select "From" from ....
>
> as suggested before.

Yes, that is correct for SQL92, which is case-sensitive for quoted names.

Signature

Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

Reply to this Message

aaronfude@gmail.com - 19 Apr 2006 05:30 GMT

Hi,

Thanks for all the responses.

So would it be a good idea to write all column names in quotes. (All of
my SQL is computer generated so this would be a trivial change.)

Thanks again!

Aaron

Reply to this Message

Lee Fesperman - 19 Apr 2006 21:40 GMT

> Hi,
>
> Thanks for all the responses.
>
> So would it be a good idea to write all column names in quotes. (All of
> my SQL is computer generated so this would be a trivial change.)

That's a tough question! Not all SQL-DBMSs support using double-quotes to quote names.
Support for ANSI SQL is very spotty. IOW, establising a convention like that won't be
portable. I think your SQL generator needs to accept properties to control generation so
the generation can be tailored to specific SQL backends.

Signature

Reply to this Message

Lee Fesperman - 21 Apr 2006 00:49 GMT

> > Hi,
> >
[quoted text clipped - 7 lines]
> portable. I think your SQL generator needs to accept properties to control
> generation so the generation can be tailored to specific SQL backends.

Oops, not as tough as I said! I had forgotten the getIdentifierQuoteString() method in
java.sql.DatabaseMetaData. With luck, that will enable you to properly quote names in a
portable manner.

For full generality, a SQL generator I wrote allowed you to override the
getIdentifierQuoteString() result on option. It also scanned the name to double any
quote character when the quote string was a single character and not a space. Note: I
just checked the code, since my memory was obviously dim ;^)

Signature

Reply to this Message

Is "From" a legal col name.

Free Magazines