Re: Case-sensitive problem in AS clause?

Laurent Mazuel wrote:
> Dear all,
>
> I maybe found a problem with the "isCaseSensitive" method in the
> "ResultSetMetadata" class when using it on a "AS" clause.
>
>> test=> Select ('Student' || "ID" ) AS StudentId, "ID", "Name" from
> The StudentId regular identifier becomes studentid, but this kind of
> transformation is not a problem, since a regular identifier is not case
> sensitive.

Select ('Student' || "ID" ) AS "StudentId", "ID", "Name" from "Student"

Quoting the "StudentID" will make it case sensitive for you resultset.

>
> The problem is where I execute my query in JDBC:
>> ~
>> ~
>>~
>> 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

Again as the as identifier is not quoting it default to the default
behavior for assignment. As far as the isCaseSensitive() it is found
that the method in the Pgjdbc,
org/postgresql/jdbc2/AbstractJdbc2ResultSetMetaData.java is:

/*
* Does a column's case matter? ASSUMPTION: Any field that is
* not obviously case insensitive is assumed to be case sensitive
*
* @param column the first column is 1, the second is 2...
* @return true if so
* @exception SQLException if a database access error occurs
*/
public boolean isCaseSensitive(int column) throws SQLException
{
Field field = getField(column);
return connection.getTypeInfo().isCaseSensitive(field.getOID());
}

As indicated by the comment, case is going to be assumed case sensitive,
true. Since the column is a result of AS clause it seems to be assigned
as a generic PGObject type. That type would then to be case insensitive.

>
> 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.

Recommed you quote AS "StudentID" to get the behavior you desire.

danap.