Skip site navigation (1) Skip section navigation (2)

Case-sensitive problem in AS clause?

From: Laurent Mazuel <lmazuel(at)antidot(dot)net>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Case-sensitive problem in AS clause?
Date: 2012-07-16 14:15:54
Message-ID: 5004221A.4030508@antidot.net (view raw or flat)
Thread:
Lists: pgsql-jdbc
Dear all,

I maybe found a problem with the "isCaseSensitive" method in the 
"ResultSetMetadata" class when using it on a "AS" clause.

With the following test DB:
> test=> CREATE TABLE "Student" ("ID" INTEGER,"Name" VARCHAR(15));
> CREATE TABLE
> test=> INSERT INTO "Student" ("ID", "Name") VALUES(10,'Venus');
> INSERT 0 1
I execute the query with the "AS" clause:
> test=> Select ('Student' || "ID" ) AS StudentId, "ID", "Name" from 
> "Student" ;
>  studentid | ID | Name
> -----------+----+-------
>  Student10 | 10 | Venus
> (1 ligne)

The StudentId regular identifier becomes studentid, but this kind of 
transformation is not a problem, since a regular identifier is not case 
sensitive.

The problem is where I execute my query in JDBC:
>     @Test
>     public void testAsCaseSensitive() throws Exception {
>     Connection conn = 
> DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/test","root","root");
>     java.sql.Statement s = conn.createStatement(
>         ResultSet.HOLD_CURSORS_OVER_COMMIT, ResultSet.CONCUR_READ_ONLY);
>     s.executeQuery("Select ('Student' || \"ID\" ) AS StudentId from 
> \"Student\";");
>     ResultSet rs = s.getResultSet();
>     ResultSetMetaData metaData = rs.getMetaData();
>     int n = metaData.getColumnCount();
>     for (int i = 1; i <= n; i++) {
>         System.out.println("Column: "+metaData.getColumnLabel(i)+" 
> "+metaData.isCaseSensitive(i));
>     }
>     }
I obtain the output:
> Column: studentid true

Then, the column name is changed from StudentId to studentid, but the 
"isCaseSensitive" flag is "true". I think it is not correct, since it is 
impossible from now, when a user ask for the StudentId column to 
retrieve it from a resultset. The "isCaseSensitive" don't authorize to 
accept the string StudentId as a valid column name for the studentid 
column in the ResultSet.

This on: psql (PostgreSQL) 9.1.4 (debian-squeeze backport packages), 
with Maven dependency on JDBC plugin:
> <dependency>
> <groupId>postgresql</groupId>
> <artifactId>postgresql</artifactId>
> <version>9.1-901.jdbc4</version>
> </dependency>


I hope that I don't have miss something and I don't bother you....

Best regards,

Laurent Mazuel

-- 
Laurent Mazuel (lmazuel(at)antidot(dot)net)
Ingénieur R&D, Web Sémantique
Antidot - Solutions de recherche d'information
29 avenue Jean Monnet, 13410 LAMBESC (FRANCE)
Tel: (33) 4 42 63 67 90 / Fax: (33) 4 42 28 61 03



Responses

pgsql-jdbc by date

Next:From: dmpDate: 2012-07-16 16:05:29
Subject: Re: Case-sensitive problem in AS clause?
Previous:From: Radim KolarDate: 2012-07-14 14:48:23
Subject: 9.0-802 in maven central

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group