SQL Problem

Hello, the following script run´s under user informix 7.31 TC2 on Windows NT (SP4) to generate a sample database : CREATE DATABASE sadctrl in datadbs with LOG MODE ANSI; GRANT DBA to 'sadcli';COMMIT; database sadctrl; CREATE TABLE 'sadctrl'.SYS_BASICS I_FIELD INTEGER, D_FIELD DATETIME YEAR TO DAY, C_FIELD CHARACTER(10), PRIMARY KEY (I_FIELD) ); GRANT ALL ON 'sadctrl'.SYS_BASICS TO 'sadcli' AS 'sadctrl';COMMIT; If i use the Informix SQL-Editor (user xyz) to select rows from table sys_basics there´s the following effect : 1. Select * from 'sadctrl'.sys_basics; - everything works fine. 2. Select * from sadctrl.sys_basics; - SQL error (unknown table SADCTRL.sys_basics) Yes, i know that in an ansi database the table name must be fully quallified such as owner.tablename and if i want to use a case sensitive user i have to use quotes 'owner'.tablename. But what drives informix to uppercase an owner if no qoutes are used. Same thing happens if i use query tools like MSQuery and CrystalReports via ODBC. Can anybody tell me what´s going wrong.

Stephan Simon wrote: > the following script run´s under user informix 7.31 TC2 on Windows NT (SP4) > to generate a sample database : > > CREATE DATABASE sadctrl in datadbs with LOG MODE ANSI; > GRANT DBA to 'sadcli';> COMMIT; > > database sadctrl;> > CREATE TABLE 'sadctrl'.SYS_BASICS > > I_FIELD INTEGER, > D_FIELD DATETIME YEAR TO DAY, > C_FIELD CHARACTER(10), > PRIMARY KEY (I_FIELD) > ); > GRANT ALL ON 'sadctrl'.SYS_BASICS TO 'sadcli' AS 'sadctrl';> COMMIT; > > If i use the Informix SQL-Editor (user xyz) to select rows from table > sys_basics there´s the following effect : > > 1. Select * from 'sadctrl'.sys_basics; > - everything works fine. > 2. Select * from sadctrl.sys_basics; > - SQL error (unknown table SADCTRL.sys_basics) > > Yes, i know that in an ansi database the table name must be fully quallified > such as owner.tablename > and if i want to use a case sensitive user i have to use quotes > 'owner'.tablename. > But what drives informix to uppercase an owner if no qoutes are used. > ANSI SQL-89 and ANSI SQL-92. > > Same thing happens if i use query tools like MSQuery and CrystalReports via > ODBC. > > Can anybody tell me what´s going wrong. Nothing; your expectations simply need to be changed. (Yes, it's one of those things you learn the hard way). -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN #include <disclaimer.h>