Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Received-SPF: pass (hermes.apache.org: local policy)
Date: Thu, 28 Apr 2005 13:23:47 +0530
From: Shreyas Kaushik <Shreyas.Kaushik@Sun.COM>
Subject: Re: [jira] Commented: (DERBY-229) Column names on ResultSet.updateXXX
 and getXXX methods are handled incorrectly
In-reply-to: <4296CA3D.709@Sourcery.Org>
To: Derby Development <derby-dev@db.apache.org>
Message-id: <4270968B.2090205@Sun.com>
MIME-version: 1.0
Content-type: multipart/mixed; boundary="Boundary_(ID_VgiJKwhEG5jC1SNl8KuImQ)"
User-Agent: Mozilla Thunderbird 1.0 (X11/20041208)
References: <61394903.1113885946666.JavaMail.jira@ajax.apache.org>
 <42654CBF.3080906@Sourcery.Org> <426F2745.80506@sun.com>
 <4296CA3D.709@Sourcery.Org>

This is a multi-part message in MIME format.

--Boundary_(ID_VgiJKwhEG5jC1SNl8KuImQ)
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT

Find the patch attached.

~ Shreyas

Satheesh Bandaram wrote:

>Cool. Thanks! Let me know if I can help.
>
>Satheesh
>
>Shreyas Kaushik wrote:
>
>  
>
>>I will start looking at this. I volunteer ;-)
>>
>>~ Shreyas
>>
>>Satheesh Bandaram wrote:
>>
>>    
>>
>>>Looks like this bug has all the information needed to reproduce and
>>>to generate a patch. Is there any interest to submit a patch for this
>>>relatively easy fix? A complete patch would include a fix, along the
>>>general idea provided here and a test case added to jdbcapi test
>>>suite. Good opportunity for anyone wanting to get started modifying
>>>Derby code. Any volunteers? :-)
>>>
>>>I am willing to help, if needed.
>>>
>>>Satheesh
>>>
>>>Mamta A. Satoor (JIRA) wrote:
>>>
>>>      
>>>
>>>>    [
>>>>http://issues.apache.org/jira/browse/DERBY-229?page=comments#action_63128
>>>>]
>>>>    Mamta A. Satoor commented on DERBY-229:
>>>>---------------------------------------
>>>>
>>>>The getter [updater] methods with column names (in
>>>>EmbedResultSet.java) use the protected method findColumnName(String
>>>>columnName) to determine the column index for the passed column
>>>>name. And the loop for the lookup starts from the end towards the
>>>>beginning of the ResultDescription and that seems to be the problem
>>>>behind the current behavior.     ResultDescription rd =
>>>>resultDescription;
>>>>
>>>>       // 1 or 0 based? assume 1 (probably wrong)
>>>>       for (int i=rd.getColumnCount(); i>=1; i--) {
>>>>
>>>>           String name = rd.getColumnDescriptor(i).getName();
>>>>           if (StringUtil.SQLEqualsIgnoreCase(columnName, name)) {
>>>>               return i;
>>>>           }
>>>>       }
>>>>
>>>>
>>>> 
>>>>
>>>>        
>>>>
>>>>>Column names on ResultSet.updateXXX and getXXX methods are handled
>>>>>incorrectly
>>>>>------------------------------------------------------------------------------
>>>>>
>>>>>
>>>>>        Key: DERBY-229
>>>>>        URL: http://issues.apache.org/jira/browse/DERBY-229
>>>>>    Project: Derby
>>>>>       Type: Bug
>>>>> Components: JDBC
>>>>>   Versions: 10.1.0.0, 10.0.2.1
>>>>>   Reporter: Daniel John Debrunner
>>>>>Attachments: x.java
>>>>>
>>>>>Sections 14.2.3 and [14.2.3] of JDBC 3.0 spec say
>>>>>Column names supplied to getter [updater] methods are case
>>>>>insensitive. If a select list
>>>>>contains the same column more than once, the first instance of the
>>>>>column will be
>>>>>returned [updated].
>>>>>Derby returns or updates the last column in the select list, not
>>>>>the first. With the attached Java class I see
>>>>>PRE-UPDATE
>>>>>1,100
>>>>>POST-UPDATE
>>>>>1,500
>>>>>POST-UPDATE getXXX(name)
>>>>>AB:500,ab500
>>>>>  
>>>>>          
>>>>>
>>>> 
>>>>
>>>>        
>>>>
>>
>>    
>>
>
>  
>

--Boundary_(ID_VgiJKwhEG5jC1SNl8KuImQ)
Content-type: text/plain; name=Derby-229.patch
Content-transfer-encoding: 7BIT
Content-disposition: inline; filename=Derby-229.patch

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: /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,87 @@
+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(); 
+                        stmt.executeUpdate("create table caseiscol(COL1 int ,\"col1\" int)");
+
+   			con.commit();
+   			
+			stmt.executeUpdate("insert into caseiscol values (1,346)");
+
+			con.commit();
+
+			stmt1 = con.prepareStatement("select COL1, \"col1\" from caseiscol FOR UPDATE",ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+		        rs = stmt1.executeQuery();
+                        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()) {
+			   rs.updateInt("col1",100);
+			   rs.updateInt("COL1",900);
+			   rs.updateRow();
+			}
+			rs.close();
+
+			System.out.println("After update...");
+			rs = stmt1.executeQuery();
+			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()) {
+			   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();
+                }
+        }
+}     
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,10 @@
 		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 
+        // in queries and the first column should be returned.
+        for(int i = 1 ; i<=rd.getColumnCount();i++) {
     		String name = rd.getColumnDescriptor(i).getName();
     		if (StringUtil.SQLEqualsIgnoreCase(columnName, name)) {
     			return i;

--Boundary_(ID_VgiJKwhEG5jC1SNl8KuImQ)--