db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Suresh Thalamati <suresh.thalam...@gmail.com>
Subject Re: getColumns(...) and quoted table names ?
Date Thu, 23 Jun 2005 19:30:20 GMT
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 <suresh.thalamati@gmail.com> 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
>>>>
>>>>
>>>>
>>>>
>>>>  
>>>>        
>>>>
>>> 
>>>
>>>      
>>>
>>
>>    
>>
>
>  
>



Mime
View raw message