SEO

Trying to do some very basic SQL on an Informix 7.x box. What I know works
is this for either a telnet session or an ODBC connection.

Select size from item
Select size as bob from item

What doesn't work is

Select 0 as size from item
Select 0 as 'size' from item
Select 0 as "size" from item

I didn't create the table using a reserved word as a column name
I know size is a reserved word.
I'm a contractor in a shop that appears to have very limited knowledge and
no desire to share information.
I know the putz who created the table is long gone.
I am a MS-SQL Server/VB developer so I know enough SQL to be dangerous in
an Informix environment.
Per the Informix documentation and my experience, I should be able to
surround a reserved word with some sort of delimiter to use it as a column
name.
While I can code around this issue at the client, it would be a heck of a
lot easier to get the recordset returned to me with a column named as I
need it.
I know that there are a ton of people out there a lot smarter than I am.

Any thoughts?

Tom wrote:

> Trying to do some very basic SQL on an Informix 7.x box. What I
> know works is this for either a telnet session or an ODBC
> connection.

telnet? Using DB-Access to execute the SQL?

> Select size from item
> Select size as bob from item


I'd expect those to work, of course.

> What doesn't work is
>
> Select 0 as size from item
> Select 0 as 'size' from item
> Select 0 as "size" from item

And I'd also expect all of those to work. What is the error you are
getting? You should also be able to write 'SELECT 0 size FROM' item
and variants on that theme.

> I didn't create the table using a reserved word as a column name
> I know size is a reserved word.

The concept 'reserved word' has little significance in Informix's
dialect of SQL. You can write:

CREATE TABLE CREATE(null INTEGER NOT NULL, not INTEGER NOT NULL,
integer DATE);

This won't confuse the server; it will confuse everyone else.

> I'm a contractor in a shop that appears to have very limited
> knowledge and no desire to share information.
> I know the putz who created the table is long gone.

Bad luck.

> I am a MS-SQL Server/VB developer so I know enough SQL to be
> dangerous in an Informix environment.
> Per the Informix documentation and my experience, I should be able
> to surround a reserved word with some sort of delimiter to use it
> as a column name.

Yes, you can do that (use a delimited identifier) - by ensuring that
the DELIMIDENT environment variable is set - the value doesn't matter
(hence export DELIMIDENT=1 or equivalent is fine). Or you set it via
SETNET32. Not recommended; usually not necessary either. However, it
certainly works and enforces a discipline on you which is, in some
respsects, good. Specifically, it forces you to use single quotes
around strings and double quotes around delimited identifiers --
Informix normally permits both single and double quotes to surround
strings.

> While I can code around this issue at the client, it would be a
> heck of a lot easier to get the recordset returned to me with a
> column named as I need it.

I wonder if the problem is in the upper-levels of the client-side
code. You mention VB, so you're probably working on Windows. Which
Informix driver are you using? [I'm treading on thin ice here;
Windows is not my area of expertise.] Could it be that ADO or OLEDB
or whatever places restrictions on the names of columns? I'm 99.9%
sure that the ESQL/C and server don't care whether you use a column
called size or not - but there might be a constraint being enforced
higher up the call chain.

Could you create a column called size in MS SQL Server? What notation
would you need to use?

> I know that there are a ton of people out there a lot smarter than I am.
>
> Any thoughts?

Include more precise version information and platform information.
Informix 7.x covers nearly a decade of software versions (8 years or
more), so you could be using something incredibly archaic or something
that was released in the last six months.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

On Mon, 13 Sep 2004 23:58:38 -0400, Tom wrote:

As Jonathan suspects, the problem's not the database. I've tried all of your
versions on IDS 9.4 and 7.31 and they all work just fine. There's something
going on in your front-end.

Art S. Kagel

> Trying to do some very basic SQL on an Informix 7.x box. What I know works
> is this for either a telnet session or an ODBC connection.
>
> Select size from item
> Select size as bob from item
>
> What doesn't work is
>
> Select 0 as size from item
> Select 0 as 'size' from item
> Select 0 as "size" from item
>
> I didn't create the table using a reserved word as a column name I know size
> is a reserved word.
> I'm a contractor in a shop that appears to have very limited knowledge and
> no desire to share information.
> I know the putz who created the table is long gone. I am a MS-SQL Server/VB
> developer so I know enough SQL to be dangerous in an Informix environment.
> Per the Informix documentation and my experience, I should be able to
> surround a reserved word with some sort of delimiter to use it as a column
> name.
> While I can code around this issue at the client, it would be a heck of a
> lot easier to get the recordset returned to me with a column named as I need
> it.
> I know that there are a ton of people out there a lot smarter than I am.
>
> Any thoughts?

"Art S. Kagel" <kagel@bloomberg.net> wrote in
newsan.2004.09.14.10.42.42.766059.1355@bloomberg .net:

> On Mon, 13 Sep 2004 23:58:38 -0400, Tom wrote:
>
> As Jonathan suspects, the problem's not the database. I've tried all
> of your versions on IDS 9.4 and 7.31 and they all work just fine.
> There's something going on in your front-end.
>
> Art S. Kagel

Art,
My knee-jerk reaction was the same as yours, there's something going on in
the front-end, maybe with the client ODBC connection, configuration or
something else along that line. To take the front-end out of the picture,
telnet was used to run an editor, VI I believe, and ran the queries
directly against the DB, thus eliminating the client connection, VB,
Windows, etc and it still crapped out.

Life's a learning experience and this Informix is sure taking me to school
<s>. The one thing I can say about the database I'm pushing against is
that it is quick, cumbersome for me to work with, but quick.

Thanks so much for at checking this out, you confirmed my suspicion that
the queries will run in a different environment.