db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Suresh Thalamati (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-390) Import/export fails with table names that are quoted in SQL like import to table "Order"
Date Thu, 23 Jun 2005 18:45:09 GMT
    [ http://issues.apache.org/jira/browse/DERBY-390?page=comments#action_12314344 ] 

Suresh Thalamati commented on DERBY-390:
----------------------------------------

There are  two ways to  fix  this problem:
1) Import/Export   should  treat   schema Names ,  table  Names , Column Names  as  case-insensitive
, unless delimited by double quotes.   For  example ,   valid call for a quoted table name
is :
call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, '"ORDER"' , 'order.dat' ,  null, null, null, 0)
; 
call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, '"ORDER"' , 'order.dat' , null, null, null) ; 

Note that  table name is passed is delimited by double quotes. 

2) Import/Export   should  treat   schema Names ,  table  Names , Column Names  as  case sensitive.
  Because 
the   database  automatically translated the SQL identifier into uppercase by the system.
   All unquoted 
table names ..etc  should  be passed in   upper case.   For example:
To import/export into  a   table emp (create table emp(a int ) ) .
call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'EMP' , 'emp.dat' ,  null, null, null, 0) ; 
call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EMP' , 'emp.dat' , null, null, null) ; 
 
Note that  table name is passed in upper case. 

I like the first approach  because it  is more  consistent with SQL92 names and users need
not  pass  upper case
names   for  all  unquoted names(cases insensitive  names).    and also will not break any
existing application that
might be working becasue  current import/export procedures   accepts  names in case-insensitive
form. 
 I would like to  fix this problem with the first approach,   unless some one has objections.

 




> Import/export  fails   with table names that are quoted in SQL  like import  to table
 "Order"
> ----------------------------------------------------------------------------------------------
>
>          Key: DERBY-390
>          URL: http://issues.apache.org/jira/browse/DERBY-390
>      Project: Derby
>         Type: Bug
>     Versions: 10.0.2.1
>     Reporter: Suresh Thalamati

>
> import  fails   irrespective off whether the name is passed as  a quoted sttring or not:
> ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, '"ORDER"' , 'order.dat' ,
>                                       null, null, null, 0) ;
> ERROR XIE0M: Table '"ORDER"' does not exist.
> ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ORDER' , 'order.dat' ,
>                                       null, null, null, 0) ;
> ERROR 38000: The exception 'SQL Exception: Syntax error: Encountered "ORDER" at
> line 1, column 13.' was thrown while evaluating an expression.
> ERROR 42X01: Syntax error: Encountered "ORDER" at line 1, column 13.
> Export works with  a quoted table name but fails if the name is not quoted :
> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, '"ORDER"' , 'order.dat' ,
>                                  null, null, null) ;   --- PASSES
> ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ORDER' , 'order.dat' ,
>                                  null, null, null) ;
> ERROR 38000: The exception 'SQL Exception: Syntax error: Encountered "ORDER" at
> line 1, column 15.' was thrown while evaluating an expression.
> ERROR 42X01: Syntax error: Encountered "ORDER" at line 1, column 15.
> Repro:
> connect 'jdbc:derby:wombat;create=true';
> create table "ORDER"(a int ) ;
> insert into "ORDER" values(1) ;
> insert into "ORDER" values(2) ;
> insert into "ORDER" values(3) ;
> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, '"ORDER"' , 'order.dat' ,
>                                  null, null, null) ;
> -- Perform Import
> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, '"ORDER"' , 'order.dat' ,
>                                       null, null, null, 0) ;
> ERROR XIE0M: Table '"ORDER"' does not exist.
> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ORDER' , 'order.dat' ,
>                                       null, null, null, 0) ;
> ERROR 38000: The exception 'SQL Exception: Syntax error: Encountered "ORDER" at
> line 1, column 13.' was thrown while evaluating an expression.
> ERROR 42X01: Syntax error: Encountered "ORDER" at line 1, column 13.
> Note:  I think  the same problem will occur with  when  quoted schema Names and column
names are passed to import/export procedures. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Mime
View raw message