This is a real problem with system procedures that build a query and
also do a metadata calls.
System procedures seems to state that , they treat names
case-insensitive form and all non-delimited names
should be passed in upper case.
CheckTable doc:
Note: Both /SchemaName/ and /TableName/ must be any expression that
evaluates to a string data type.
If you created a schema or table name as a non-delimited identifier,
you must present their names in all upper case.
1) checkTable(..) and SYSCS_INPLACE_COMPRESS_TABLE(...) will work in
case-sensiteve form.
eg: create table "Order"(a int ) ;
call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'Order' , 1
, 1, 1) ;
call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1' , 1 ,
1, 1) ; -- Note T1 is in upper case
2) COMPRESS_TABLE procedure form a query, so if the user does not
pass quoted names , it will fail for quoted names.
eg: create table "Order"(a int ) ;
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP' , 'Order' ,1) ;
ERROR 38000: The exception 'SQL Exception: Syntax error: Encountered
"Order" at
line 1, column 17.' was thrown while evaluating an expression.
ERROR 42X01: Syntax error: Encountered "Order" at line 1, column 17.
With quoted names it works fine.
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP' , '"Order"' ,1) ;
0 rows inserted/updated/deleted
If it is expected that user to pass quoted names for
SYSCS_UTIL.SYSCS_COMPRESS_TABLE, then it is ok.
But doc is not clear:
COMPRESS_TABLE doc in the reference manual:
TABLENAME
An input argument of type VARCHAR(128) that specifies the table name
of the table. The string must exactly match the case of the table
name, and the argument of "Fred" will be passed to SQL as the
delimited identifier 'Fred'. Passing a null will result in an error.
So either doc has to be fixed or code needs to be fixed to handle
quoted names for compress table.
Thanks
-suresht
Mike Matrigali wrote:
suresh, do you know yet if this is a problem with all the
system procedures which take a table name, or only those
that take the table name and build a query which it then
sends back to the parser?
Suresh Thalamati wrote:
Thanks Francois, your explanation was realy helpful.
-suresh
Francois Orsini wrote:
The quotes are there for the parser to treat the table object
(ansi-92) identifier as case sensitive and for the reason you
mentioned when dealing with objects that have reserved words as
identifiers...
The metadata DatabaseMetaData.getColumns() method does not really take
a table name per-se (to be picky), but more like a table name
"pattern" following the JDBC pattern-matching format convention ('%'
and '_' characters can be used).
I believe the way it is expressed currently in the getColumns() or
getTables() metadata calls is correct as underneath, system catalogs
get queried via an (internal) SQL statement which takes a
case-sensitive string as search predicate to select the matching
tuples - in your case matching the table name expressed in uppercase
will match the table you created (obviously).
One thing you could also do is new'ing a DbTable object for the
qualifying resultset row that matches the table you are looking for
with a getTables() call (using a qualifying/restrictive pattern) and
then specificy the table name to use in the getColumns() call via a
DbTable.getTableName() call...you might want to do this if you want to
ensure you are dealing with a valid table satisfying your search
before returning the columns...
Just some thoughts...
--francois
On 6/22/05, Suresh Thalamati <[EMAIL PROTECTED]> wrote:
I have a table that uses reserved word as name , so it is quoted like :
create table "ORDER"(a int ) ;
All SQL queries seems to expect it as quoted name , except Database
Metadata getColumns(..).
DatabaseMetaData dmd = conn.getMetaData();
ResultSet rs = dmd.getColumns(null, null , "\"ORDER\"" , null) ;
does not return any column information , where as
ResultSet rs = dmd.getColumns(null, null , "ORDER"" , null) ;
retunns the "ORDER" table columns Information.
I am wondering what is the correct usage here , i.e does metadata
calls suppose to
expect quoted tables names like SQL or the one without quotes ?
Thanks
-suresht
