db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From V Narayanan <V.Naraya...@Sun.COM>
Subject Re: Bug with lowercase table name ?
Date Thu, 12 Jul 2007 06:14:15 GMT
Hi John,


The following program returns the correct irrespective of if the  table 
is in uppercase or lower case.

I wrote the code very quickly. So I apologize for its primitiveness.


The program creates a table mytable in lower case. using a stmt.execute 
and does a

select on the table with the name both in uppercase and lower case. I 
tried this using

ij also. I have pasted that output also below.


code

--------


import java.sql.*;

public class CaseTest {
   
    public static void main (String[] args) throws Exception {
        Connection con = DriverManager.getConnection 
("jdbc:derby:mydb;create=true");
        Statement stmt = con.createStatement ();
        try {
            stmt.execute ("drop table mytable");
        }
        catch(SQLException sqle) {
           
        }
        stmt.execute("create table mytable(i int)");
        stmt.execute ("insert into mytable values(1)");
        ResultSet rs = stmt.executeQuery ("select * from mytable");
        rs.next ();
        System.out.println("value = " + rs.getInt (1));
        rs = stmt.executeQuery ("select * from MYTABLE");
        rs.next ();
        System.out.println("value = " + rs.getInt (1));
    }
}


Output

------------


value = 1
value = 1




Experiment with ij

-------------------------------


ij version 10.4
ij> connect 'jdbc:derby:testDB;create=true';
ij> create table abc(i int, name varchar(30));
0 rows inserted/updated/deleted
ij> insert into abc values(1, 'Narayanan');
1 row inserted/updated/deleted
ij> select * from abc;
I          |NAME                          
------------------------------------------
1          |Narayanan                     

1 row selected
ij> select * from ABC;
I          |NAME                          
------------------------------------------
1          |Narayanan                     

1 row selected
ij>


John Baker wrote:

> Hi,
>
> Is there any known issue with executing queries of databases that have
> table names in lowercase?
>
> If I create a table with a lowercase name and execute
>
>    select * from abc
>
> or
>    select * from ABC.abc
>
> then the results are
>
> Error code -1, SQL state 42X05: Table/View 'ABC' does not exist.
>
> Line 1, column 1
>
>
>
> Comparing to MSSQL Server, case doesn't matter.
> I have a table named product
>
> select * from DBO.product
>
> or
>
> select * from DBO.PRODUCT
>
> both return all rows from product
>
>
>
> Also, if I create a table ABCD in Derby then
> select * from ABCD
>
> returns the rows from ABCD.
>
> If I enclose the lowercase table name in quotes then this works.
>
> select * from "abc"
>
>
> However, I don't want to enclose the tablename in quotes.
>
> So, this appears to be a bug in Derby in SQL statements fail for a 
> table created with
> a lowercase name.
>
> - John
>>   


Mime
View raw message