Hi Shreyas,

Which codeline was blurb below that you cut and pasted from as I did not see the blurb below in the javadocs for supportsMixedCaseQuotedIdentifiers.

supportsMixedCaseQuotedIdentifiers

public boolean supportsMixedCaseQuotedIdentifiers()
                                           throws SQLException
Retrieves whether this database treats mixed case quoted SQL identifiers as case sensitive and as a result stores them in mixed case.

Returns:
true if so; false otherwise
Throws:
SQLException - if a database access error occurs


However the tutorial (3rd edition) does indicate a compliant driver is true.

The JDBC spec does not mention this in the compliance section.  Looks like another issue I need to clarify for JDBC 4.0.

Regards
Lance



Shreyas Kaushik wrote:
Does the JDBC spec say anything about this ? I did not find anything that mandates this or says if this return false the driver is not JDBC compliant.

On the contrary the comment says,

  /**
    * Does the database treat mixed case unquoted SQL identifiers as
    * case sensitive and as a result store them in mixed case?
    *
    * A JDBC-Compliant driver will always return false.
    *
    * @return true if so
    */

Lance, can you help us out here ?

~ Shreyas

Bernt M. Johnsen wrote:

Hi all,

I have the following comment to this patch. This solves the problem of
getting the *first* matching column instead of the *last*.

But: Since DatabaseMetaData.supportsMixedCaseQuotedIdentifiers()
return true (which is a requirement to JDBC compliant drivers), it
should be possible to do e.g. getXXX("\"quotedId\"") to get the column
which exactly matches "quotedId". (Alternatively,
supportsMixedCaseQuotedIdentifiers() could return false, but then the
driver would not be JDBC compliant).

If this is considered a new Jira issue I'd willingly file and fix it
(need developer status though).

Bernt

 

Shreyas Kaushik wrote (2005-05-02 11:43:27):
                        
Hi,

This is the latest patch.
I have taken care of comments from Mamta and Dan. Please let me know if I have missed anything.

~ Shreyas
  

 

Index: /drivers/derby/trunk/java/testing/org/apache/derbyTesting/functionTests/master/caseInsensitiveColumn.out
===================================================================
--- /drivers/derby/trunk/java/testing/org/apache/derbyTesting/functionTests/master/caseInsensitiveColumn.out    (revision 0)
+++ /drivers/derby/trunk/java/testing/org/apache/derbyTesting/functionTests/master/caseInsensitiveColumn.out    (revision 0)
@@ -0,0 +1,9 @@
+Test caseInsensitiveColumn starting
+Before updation...
+ResultSet is: 1
+ResultSet is: 346
+After update...
+Column Number 1: 900
+Column Number 2: 346
+Col COL1: 900
+Col col1: 900
Index: java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java
===================================================================
--- java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java    (revision 165091)
+++ java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java    (working copy)
@@ -3591,8 +3591,14 @@
        ResultDescription rd = resultDescription;

        // 1 or 0 based? assume 1 (probably wrong)
-        for (int i=rd.getColumnCount(); i>=1; i--) {
+        // Changing the order in which columns are found from 1 till column count.
+        // This is necessary in cases where the column names are the same but are in different cases.
+        // This is because in updateXXX and getXXX methods column names are case insensitive
+        // and in that case the first column should be returned.
+        +        int columnCount = rd.getColumnCount();

+        for(int i = 1 ; i<= columnCount;i++) {
            String name = rd.getColumnDescriptor(i).getName();
            if (StringUtil.SQLEqualsIgnoreCase(columnName, name)) {
                return i;
Index: /drivers/derby/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/caseInsensitiveColumn.java
===================================================================
--- /drivers/derby/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/caseInsensitiveColumn.java    (revision 0)
+++ /drivers/derby/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/caseInsensitiveColumn.java    (revision 0)
@@ -0,0 +1,97 @@
+package org.apache.derbyTesting.functionTests.tests.jdbcapi;
+
+
+import java.sql.*;
+
+import org.apache.derby.tools.ij;
+import org.apache.derby.tools.JDBCDisplayUtil;
+
+public class caseInsensitiveColumn {
+
+    public static void main(String[] args) {
+        test1(args);
+    }
+    +        public static void test1(String []args) {   +                Connection con;
+                ResultSet rs;
+                Statement stmt = null;
+                PreparedStatement stmt1 = null;
+
+                System.out.println("Test caseInsensitiveColumn starting");
+
+                try
+                {
+                        // use the ij utility to read the property file and
+                        // make the initial connection.
+                        ij.getPropertyArg(args);
+                        con = ij.startJBMS();
+                   
+            con.setAutoCommit(false);                                                  +
+                        stmt = con.createStatement(); +
+            // create a table with two columns, their names differ in they being in different cases.
+                        stmt.executeUpdate("create table caseiscol(COL1 int ,\"col1\" int)");
+
+               con.commit();
+              
+            stmt.executeUpdate("insert into caseiscol values (1,346)");
+
+            con.commit();
+
+                        // select data from this table for updating
+            stmt1 = con.prepareStatement("select COL1, \"col1\" from caseiscol FOR UPDATE",ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+                rs = stmt1.executeQuery();
+
+            // Get the data and disply it before updating.
+                        System.out.println("Before updation...");
+            while(rs.next()) {
+               System.out.println("ResultSet is: "+rs.getObject(1));
+               System.out.println("ResultSet is: "+rs.getObject(2));
+            }
+                        rs.close();
+            rs = stmt1.executeQuery();
+            while(rs.next()) {
+               // Update the two columns with different data.
+               // Since update is case insensitive only the first column should get updated in both cases.
+               rs.updateInt("col1",100);
+               rs.updateInt("COL1",900);
+               rs.updateRow();
+            }
+            rs.close();
+
+            System.out.println("After update...");
+            rs = stmt1.executeQuery();
+
+            // Display the data after updating. Only the first column should have the updated value.
+            while(rs.next()) {
+               System.out.println("Column Number 1: "+rs.getInt(1));
+               System.out.println("Column Number 2: "+rs.getInt(2));
+            }
+            rs.close();
+            rs = stmt1.executeQuery();
+            while(rs.next()) {
+               // Again checking for case insensitive behaviour here, should display the data in the first column.
+               System.out.println("Col COL1: "+rs.getInt("COL1"));
+               System.out.println("Col col1: "+rs.getInt("col1"));
+            }
+            rs.close();
+         } catch(SQLException sqle) {
+            dumpSQLExceptions(sqle);
+            sqle.printStackTrace();
+         } catch(Throwable e) {
+            System.out.println("FAIL -- unexpected exception: "+e);
+                   e.printStackTrace();
+
+         }
+     }
+     +     static private void dumpSQLExceptions (SQLException se) {
+                System.out.println("FAIL -- unexpected exception");
+                while (se != null) {
+                        System.out.println("SQLSTATE("+se.getSQLState()+"): "+se);
+                        se = se.getNextException();
+                }
+        }
+}