in out parameter patch

Attached is a patch that partially implements in/out parameters.

Known issues include batch update fails due to selects being used.

I'd like to know if there are any other issues before proceeding along
this path.


--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

? callable.diff
Index: org/postgresql/jdbc2/AbstractJdbc2Statement.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java,v
retrieving revision 1.72
diff -c -r1.72 AbstractJdbc2Statement.java
*** org/postgresql/jdbc2/AbstractJdbc2Statement.java    16 Feb 2005 18:28:45 -0000    1.72
--- org/postgresql/jdbc2/AbstractJdbc2Statement.java    22 Apr 2005 03:01:35 -0000
***************
*** 90,100 ****
      // functionReturnType contains the user supplied value to check
      // testReturn contains a modified version to make it easier to
      // check the getXXX methods..
!     private int functionReturnType;
!     private int testReturn;
      // returnTypeSet is true when a proper call to registerOutParameter has been made
      private boolean returnTypeSet;
!     protected Object callResult;
      protected int maxfieldSize = 0;

      public ResultSet createDriverResultSet(Field[] fields, Vector tuples)
--- 90,100 ----
      // functionReturnType contains the user supplied value to check
      // testReturn contains a modified version to make it easier to
      // check the getXXX methods..
!     private int []functionReturnType;
!     private int []testReturn;
      // returnTypeSet is true when a proper call to registerOutParameter has been made
      private boolean returnTypeSet;
!     protected Object []callResult;
      protected int maxfieldSize = 0;

      public ResultSet createDriverResultSet(Field[] fields, Vector tuples)
***************
*** 125,130 ****
--- 125,134 ----
          this.preparedQuery = connection.getQueryExecutor().createParameterizedQuery(parsed_sql);
          this.preparedParameters = preparedQuery.createParameterList();

+         this.testReturn = new int[preparedParameters.getParameterCount()+1];
+         this.functionReturnType = new int[preparedParameters.getParameterCount()+1];
+
+
          resultsettype = rsType;
          concurrency = rsConcurrency;
      }
***************
*** 339,352 ****
              if (!rs.next())
                  throw new PSQLException(GT.tr("A CallableStatement was executed with nothing returned."),
PSQLState.NO_DATA);

!             callResult = rs.getObject(1);
!             int columnType = rs.getMetaData().getColumnType(1);
!             if (columnType != functionReturnType)
!                 throw new PSQLException (GT.tr("A CallableStatement function was executed and the return was of type
{0}however type {1} was registered.", 
                                                 new Object[]{
                                                     "java.sql.Types=" + columnType, "java.sql.Types=" +
functionReturnType}), 
                                           PSQLState.DATA_TYPE_MISMATCH);
!
              rs.close();
              result = null;
              return false;
--- 343,363 ----
              if (!rs.next())
                  throw new PSQLException(GT.tr("A CallableStatement was executed with nothing returned."),
PSQLState.NO_DATA);

!             // figure out how many columns
!             int cols = rs.getMetaData().getColumnCount();
!             callResult = new Object[cols];
!
!             // move them into the result set
!             for ( int i=0; i < cols; i++)
!             {
!                 callResult[i] = rs.getObject(i+1);
!                 int columnType = rs.getMetaData().getColumnType(1);
!                 if (columnType != functionReturnType[i])
!                     throw new PSQLException (GT.tr("A CallableStatement function was executed and the return was of
type{0} however type {1} was registered.", 
                                                 new Object[]{
                                                     "java.sql.Types=" + columnType, "java.sql.Types=" +
functionReturnType}), 
                                           PSQLState.DATA_TYPE_MISMATCH);
!             }
              rs.close();
              result = null;
              return false;
***************
*** 418,424 ****
      }

      private boolean isClosed = false;
!
      /*
       * getUpdateCount returns the current result as an update count,
       * if the result is a ResultSet or there are no more results, -1
--- 429,435 ----
      }

      private boolean isClosed = false;
!     private int lastIndex = 0;
      /*
       * getUpdateCount returns the current result as an update count,
       * if the result is a ResultSet or there are no more results, -1
***************
*** 1700,1712 ****
          // functionReturnType contains the user supplied value to check
          // testReturn contains a modified version to make it easier to
          // check the getXXX methods..
!         functionReturnType = sqlType;
!         testReturn = sqlType;
!         if (functionReturnType == Types.CHAR ||
!                 functionReturnType == Types.LONGVARCHAR)
!             testReturn = Types.VARCHAR;
!         else if (functionReturnType == Types.FLOAT)
!             testReturn = Types.REAL; // changes to streamline later error checking
          returnTypeSet = true;
      }

--- 1711,1723 ----
          // functionReturnType contains the user supplied value to check
          // testReturn contains a modified version to make it easier to
          // check the getXXX methods..
!         functionReturnType[parameterIndex-1] = sqlType;
!         testReturn[parameterIndex-1] = sqlType;
!         if (functionReturnType[parameterIndex-1] == Types.CHAR ||
!                 functionReturnType[parameterIndex-1] == Types.LONGVARCHAR)
!             testReturn[parameterIndex-1] = Types.VARCHAR;
!         else if (functionReturnType[parameterIndex-1] == Types.FLOAT)
!             testReturn[parameterIndex-1] = Types.REAL; // changes to streamline later error checking
          returnTypeSet = true;
      }

***************
*** 1741,1747 ****
      public boolean wasNull() throws SQLException
      {
          // check to see if the last access threw an exception
!         return (callResult == null);
      }

      /*
--- 1752,1758 ----
      public boolean wasNull() throws SQLException
      {
          // check to see if the last access threw an exception
!         return (callResult[lastIndex-1] == null);
      }

      /*
***************
*** 1756,1762 ****
      {
          checkClosed();
          checkIndex (parameterIndex, Types.VARCHAR, "String");
!         return (String)callResult;
      }


--- 1767,1773 ----
      {
          checkClosed();
          checkIndex (parameterIndex, Types.VARCHAR, "String");
!         return (String)callResult[parameterIndex-1];
      }


***************
*** 1773,1779 ****
          checkIndex (parameterIndex, Types.BIT, "Boolean");
          if (callResult == null)
              return false;
!         return ((Boolean)callResult).booleanValue ();
      }

      /*
--- 1784,1790 ----
          checkIndex (parameterIndex, Types.BIT, "Boolean");
          if (callResult == null)
              return false;
!         return ((Boolean)callResult[parameterIndex-1]).booleanValue ();
      }

      /*
***************
*** 1807,1813 ****
          checkIndex (parameterIndex, Types.SMALLINT, "Short");
          if (callResult == null)
              return 0;
!         return (short)((Short)callResult).intValue ();
      }


--- 1818,1824 ----
          checkIndex (parameterIndex, Types.SMALLINT, "Short");
          if (callResult == null)
              return 0;
!         return (short)((Short)callResult[parameterIndex-1]).intValue ();
      }


***************
*** 1824,1830 ****
          checkIndex (parameterIndex, Types.INTEGER, "Int");
          if (callResult == null)
              return 0;
!         return ((Integer)callResult).intValue ();
      }

      /*
--- 1835,1841 ----
          checkIndex (parameterIndex, Types.INTEGER, "Int");
          if (callResult == null)
              return 0;
!         return ((Integer)callResult[parameterIndex-1]).intValue ();
      }

      /*
***************
*** 1840,1846 ****
          checkIndex (parameterIndex, Types.BIGINT, "Long");
          if (callResult == null)
              return 0;
!         return ((Long)callResult).longValue ();
      }

      /*
--- 1851,1857 ----
          checkIndex (parameterIndex, Types.BIGINT, "Long");
          if (callResult == null)
              return 0;
!         return ((Long)callResult[parameterIndex-1]).longValue ();
      }

      /*
***************
*** 1856,1862 ****
          checkIndex (parameterIndex, Types.REAL, "Float");
          if (callResult == null)
              return 0;
!         return ((Float)callResult).floatValue ();
      }

      /*
--- 1867,1873 ----
          checkIndex (parameterIndex, Types.REAL, "Float");
          if (callResult == null)
              return 0;
!         return ((Float)callResult[parameterIndex-1]).floatValue ();
      }

      /*
***************
*** 1872,1878 ****
          checkIndex (parameterIndex, Types.DOUBLE, "Double");
          if (callResult == null)
              return 0;
!         return ((Double)callResult).doubleValue ();
      }

      /*
--- 1883,1889 ----
          checkIndex (parameterIndex, Types.DOUBLE, "Double");
          if (callResult == null)
              return 0;
!         return ((Double)callResult[parameterIndex-1]).doubleValue ();
      }

      /*
***************
*** 1891,1897 ****
      {
          checkClosed();
          checkIndex (parameterIndex, Types.NUMERIC, "BigDecimal");
!         return ((BigDecimal)callResult);
      }

      /*
--- 1902,1908 ----
      {
          checkClosed();
          checkIndex (parameterIndex, Types.NUMERIC, "BigDecimal");
!         return ((BigDecimal)callResult[parameterIndex-1]);
      }

      /*
***************
*** 1906,1912 ****
      {
          checkClosed();
          checkIndex (parameterIndex, Types.VARBINARY, Types.BINARY, "Bytes");
!         return ((byte [])callResult);
      }


--- 1917,1923 ----
      {
          checkClosed();
          checkIndex (parameterIndex, Types.VARBINARY, Types.BINARY, "Bytes");
!         return ((byte [])callResult[parameterIndex-1]);
      }


***************
*** 1921,1927 ****
      {
          checkClosed();
          checkIndex (parameterIndex, Types.DATE, "Date");
!         return (java.sql.Date)callResult;
      }

      /*
--- 1932,1938 ----
      {
          checkClosed();
          checkIndex (parameterIndex, Types.DATE, "Date");
!         return (java.sql.Date)callResult[parameterIndex-1];
      }

      /*
***************
*** 1935,1941 ****
      {
          checkClosed();
          checkIndex (parameterIndex, Types.TIME, "Time");
!         return (java.sql.Time)callResult;
      }

      /*
--- 1946,1952 ----
      {
          checkClosed();
          checkIndex (parameterIndex, Types.TIME, "Time");
!         return (java.sql.Time)callResult[parameterIndex-1];
      }

      /*
***************
*** 1950,1956 ****
      {
          checkClosed();
          checkIndex (parameterIndex, Types.TIMESTAMP, "Timestamp");
!         return (java.sql.Timestamp)callResult;
      }

      // getObject returns a Java object for the parameter.
--- 1961,1967 ----
      {
          checkClosed();
          checkIndex (parameterIndex, Types.TIMESTAMP, "Timestamp");
!         return (java.sql.Timestamp)callResult[parameterIndex-1];
      }

      // getObject returns a Java object for the parameter.
***************
*** 1978,1984 ****
      {
          checkClosed();
          checkIndex (parameterIndex);
!         return callResult;
      }

      /*
--- 1989,1995 ----
      {
          checkClosed();
          checkIndex (parameterIndex);
!         return callResult[parameterIndex-1];
      }

      /*
***************
*** 2129,2134 ****
--- 2140,2146 ----
              case 5:  // Should be at 'call ' either at start of string or after ?=
                  if ((ch == 'c' || ch == 'C') && i + 4 <= len && p_sql.substring(i, i + 4).equalsIgnoreCase("call"))
                  {
+                     isFunction=true;
                      i += 4;
                      ++state;
                  }
***************
*** 2243,2249 ****
      throws SQLException
      {
          checkIndex (parameterIndex);
!         if (type1 != this.testReturn && type2 != this.testReturn)
              throw new PSQLException(GT.tr("Parameter of type {0} was registered, but call to get{1} (sqltype={2}) was
made.",
                                            new Object[]{"java.sql.Types=" + testReturn,
                                                         getName,
--- 2255,2261 ----
      throws SQLException
      {
          checkIndex (parameterIndex);
!         if (type1 != this.testReturn[parameterIndex-1] && type2 != this.testReturn[parameterIndex-1])
              throw new PSQLException(GT.tr("Parameter of type {0} was registered, but call to get{1} (sqltype={2}) was
made.",
                                            new Object[]{"java.sql.Types=" + testReturn,
                                                         getName,
***************
*** 2257,2263 ****
      throws SQLException
      {
          checkIndex (parameterIndex);
!         if (type != this.testReturn)
              throw new PSQLException(GT.tr("Parameter of type {0} was registered, but call to get{1} (sqltype={2}) was
made.",
                                            new Object[]{"java.sql.Types=" + testReturn,
                                                         getName,
--- 2269,2275 ----
      throws SQLException
      {
          checkIndex (parameterIndex);
!         if (type != this.testReturn[parameterIndex-1])
              throw new PSQLException(GT.tr("Parameter of type {0} was registered, but call to get{1} (sqltype={2}) was
made.",
                                            new Object[]{"java.sql.Types=" + testReturn,
                                                         getName,
***************
*** 2273,2280 ****
--- 2285,2295 ----
      {
          if (!isFunction)
              throw new PSQLException(GT.tr("A CallableStatement was declared, but no call to registerOutParameter(1,
<sometype>) was made."), PSQLState.STATEMENT_NOT_ALLOWED_IN_FUNCTION_CALL); 
+         lastIndex =  parameterIndex;
+         /*
          if (parameterIndex != 1)
              throw new PSQLException (GT.tr("PostgreSQL only supports a single OUT function return value at index
1."),PSQLState.STATEMENT_NOT_ALLOWED_IN_FUNCTION_CALL); 
+         */
      }

      public void setPrepareThreshold(int newThreshold) throws SQLException {
***************
*** 2772,2785 ****
      {
          checkClosed();
          checkIndex(i, Types.ARRAY, "Array");
!         return (Array)callResult;
      }

      public java.math.BigDecimal getBigDecimal(int parameterIndex) throws SQLException
      {
          checkClosed();
          checkIndex (parameterIndex, Types.NUMERIC, "BigDecimal");
!         return ((BigDecimal)callResult);
      }

      public Blob getBlob(int i) throws SQLException
--- 2787,2800 ----
      {
          checkClosed();
          checkIndex(i, Types.ARRAY, "Array");
!         return (Array)callResult[i-1];
      }

      public java.math.BigDecimal getBigDecimal(int parameterIndex) throws SQLException
      {
          checkClosed();
          checkIndex (parameterIndex, Types.NUMERIC, "BigDecimal");
!         return ((BigDecimal)callResult[parameterIndex-1]);
      }

      public Blob getBlob(int i) throws SQLException
Index: org/postgresql/test/jdbc2/CallableStmtTest.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/test/jdbc2/CallableStmtTest.java,v
retrieving revision 1.13
diff -c -r1.13 CallableStmtTest.java
*** org/postgresql/test/jdbc2/CallableStmtTest.java    10 Feb 2005 19:53:17 -0000    1.13
--- org/postgresql/test/jdbc2/CallableStmtTest.java    22 Apr 2005 03:01:35 -0000
***************
*** 25,30 ****
--- 25,38 ----
      public CallableStmtTest (String name)
      {
          super(name);
+         try
+         {
+             Class.forName("org.postgresql.Driver");
+         }
+         catch (Exception ex )
+         {
+
+         }
      }

      protected void setUp() throws Exception
***************
*** 43,48 ****
--- 51,64 ----
          stmt.execute ("CREATE OR REPLACE FUNCTION testspg__getNumeric (numeric) " +
                        "RETURNS numeric AS ' DECLARE inString alias for $1; " +
                        "begin return 42; end; ' LANGUAGE 'plpgsql';");
+         stmt.execute("create temp table numeric_tab (MAX_VAL NUMERIC(30,15), MIN_VAL NUMERIC(30,15), NULL_VAL
NUMERIC(30,15)NULL)"); 
+         stmt.execute("insert into numeric_tab values ( 999999999999999,0.000000000000001, null)");
+         stmt.execute("create type Numeric_Proc_RetType as(it1 numeric(30,15),it2 numeric(30,15),it3
numeric(30,15));");
+         boolean ret = stmt.execute("create function "
+                                  + "Numeric_Proc(numeric(30,15),numeric(30,15),numeric(30,15)) returns
Numeric_Proc_RetTypeas " 
+                                  + "'declare work_ret record; begin select * into work_ret from Numeric_Tab; return
work_ret;end;' " 
+                                  + "language 'plpgsql';");
+
          stmt.execute ("CREATE OR REPLACE FUNCTION testspg__getNumericWithoutArg() " +
                  "RETURNS numeric AS '  " +
                  "begin return 42; end; ' LANGUAGE 'plpgsql';");
***************
*** 58,63 ****
--- 74,81 ----
          stmt.execute ("drop FUNCTION testspg__getDouble (float);");
          stmt.execute ("drop FUNCTION testspg__getInt (int);");
          stmt.execute ("drop FUNCTION testspg__getNumeric (numeric);");
+         stmt.execute("drop function Numeric_Proc(numeric, numeric, numeric)");
+         stmt.execute("drop type Numeric_Proc_RetType");
          stmt.execute ("drop FUNCTION testspg__getNumericWithoutArg ();");
          stmt.execute ("DROP FUNCTION getarray();");
          stmt.execute ("DROP FUNCTION raisenotice();");
***************
*** 71,76 ****
--- 89,126 ----
      //testGetString ();
      //}

+
+     public void testNumeric() throws Throwable
+     {
+         CallableStatement call = con.prepareCall( "{ call Numeric_Proc(?,?,?) }" ) ;
+
+         call.registerOutParameter(1,Types.NUMERIC);
+         call.registerOutParameter(2,Types.NUMERIC);
+         call.registerOutParameter(3,Types.NUMERIC);
+
+         call.setBigDecimal(2,new java.math.BigDecimal(1));
+         call.setBigDecimal(3,new java.math.BigDecimal(2));
+         call.setBigDecimal(4,new java.math.BigDecimal(3));
+
+         call.execute();
+         java.math.BigDecimal ret = call.getBigDecimal(1);
+         assertTrue ("correct return from getNumeric () should be 999999999999999.000000000000000 but returned " +
ret.toString(),
+                                       ret.equals (new java.math.BigDecimal("999999999999999.000000000000000")));
+
+         ret=call.getBigDecimal(2);
+         assertTrue ("correct return from getNumeric ()",
+                                       ret.equals (new java.math.BigDecimal("0.000000000000001")));
+         try
+         {
+             ret = call.getBigDecimal(3);
+         }catch(NullPointerException ex)
+         {
+             assertTrue("This should be null",call.wasNull());
+         }
+
+
+     }
+
      public void testGetDouble () throws Throwable
      {
          CallableStatement call = con.prepareCall (func + pkgName + "getDouble (?) }");
Index: org/postgresql/test/jdbc3/TypesTest.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/test/jdbc3/TypesTest.java,v
retrieving revision 1.6
diff -c -r1.6 TypesTest.java
*** org/postgresql/test/jdbc3/TypesTest.java    11 Jan 2005 08:25:49 -0000    1.6
--- org/postgresql/test/jdbc3/TypesTest.java    22 Apr 2005 03:01:35 -0000
***************
*** 19,24 ****
--- 19,32 ----

      public TypesTest(String name) {
          super(name);
+         try
+         {
+             Class.forName("org.postgresql.Driver");
+         }
+         catch (Exception ex )
+         {
+
+         }
      }

      protected void setUp() throws SQLException {
***************
*** 64,68 ****
--- 72,84 ----
          assertEquals(true, cs.getBoolean(1));
          cs.close();
      }
+     public void testUnknownType() throws SQLException {
+         Statement stmt = _conn.createStatement();
+
+         ResultSet rs = stmt.executeQuery("select 'foo' as icon1, 'foo2' as icon2 ");
+         assertTrue(rs.next());
+         assertFalse(rs.getString("icon1").equalsIgnoreCase("foo1"));
+         assertFalse(rs.getString("icon2").equalsIgnoreCase("foo2"));
+     }

  }