db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r982936 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/types/ engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ testing...
Date Fri, 06 Aug 2010 12:23:11 GMT
Author: rhillegas
Date: Fri Aug  6 12:23:10 2010
New Revision: 982936

URL: http://svn.apache.org/viewvc?rev=982936&view=rev
Log:
DERBY-4066: Allow LOBs as function/procedure input arguments and return values. LOBs still
can't be used as output and inout arguments to procedures.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataType.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataValueDescriptor.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateAliasNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MethodCallNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/LOB.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiSignatures.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiSignaturesTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LOB.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataType.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataType.java?rev=982936&r1=982935&r2=982936&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataType.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataType.java Fri Aug  6 12:23:10
2010
@@ -34,6 +34,8 @@ import org.apache.derby.iapi.services.sa
 import org.apache.derby.iapi.services.i18n.LocaleFinder;
 
 import java.io.InputStream;
+import java.sql.Blob;
+import java.sql.Clob;
 import java.sql.Date;
 import java.sql.Time;
 import java.sql.Timestamp;
@@ -389,6 +391,28 @@ public abstract class DataType
 		throwLangSetMismatch("java.lang.String");
 	}
 
+	/**
+	 * Set the value of this DataValueDescriptor.
+	 * At DataType level just throws an error lower classes will override
+	 *
+	 * @param theValue	The Blob value to set this DataValueDescriptor to
+	 */
+	public void setValue(Blob theValue) throws StandardException
+	{
+		throwLangSetMismatch("java.sql.Blob");
+	}
+ 
+	/**
+	 * Set the value of this DataValueDescriptor.
+	 * At DataType level just throws an error lower classes will override
+	 *
+	 * @param theValue	The Clob value to set this DataValueDescriptor to
+	 */
+	public void setValue(Clob theValue) throws StandardException
+	{
+		throwLangSetMismatch("java.sql.Clob");
+	}
+
 
     /**
 	 * Set the value of this DataValueDescriptor to the given int value

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataValueDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataValueDescriptor.java?rev=982936&r1=982935&r2=982936&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataValueDescriptor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataValueDescriptor.java Fri
Aug  6 12:23:10 2010
@@ -29,6 +29,8 @@ import org.apache.derby.iapi.services.io
 
 import java.io.InputStream;
 import java.io.IOException;
+import java.sql.Blob;
+import java.sql.Clob;
 import java.sql.Date;
 import java.sql.ResultSet;
 import java.sql.PreparedStatement;
@@ -503,6 +505,22 @@ public interface DataValueDescriptor ext
 	 */
 	public void setValue(String theValue) throws StandardException;
 
+ 	/**
+	 * Set the value of this DataValueDescriptor from a Blob.
+	 *
+	 * @param theValue	The Blob value to set this DataValueDescriptor to
+	 *
+	 */
+	public void setValue(Blob theValue) throws StandardException;
+    
+	/**
+	 * Set the value of this DataValueDescriptor from a Clob.
+	 *
+	 * @param theValue	The Clob value to set this DataValueDescriptor to
+	 *
+	 */
+	public void setValue(Clob theValue) throws StandardException;
+
 	/**
 	 * Set the value of this DataValueDescriptor.
 	 *

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateAliasNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateAliasNode.java?rev=982936&r1=982935&r2=982936&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateAliasNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateAliasNode.java
Fri Aug  6 12:23:10 2010
@@ -32,6 +32,7 @@ import org.apache.derby.iapi.types.Strin
 
 import org.apache.derby.iapi.sql.dictionary.DataDictionary;
 import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor;
+import org.apache.derby.iapi.reference.JDBC30Translation;
 
 import org.apache.derby.iapi.error.StandardException;
 
@@ -172,12 +173,31 @@ public class CreateAliasNode extends DDL
 
 					modes = new int[paramCount];
 					for (int i = 0; i < paramCount; i++) {
-						modes[i] = ((Integer) (((Vector) parameters[2]).elementAt(i))).intValue();
-
-						if ( (!types[ i ].isUserDefinedType()) && TypeId.getBuiltInTypeId(types[i].getJDBCTypeId()).isLongConcatableTypeId())
-							throw StandardException.newException(SQLState.LANG_LONG_DATA_TYPE_NOT_ALLOWED, names[i]);
-
-					}
+                        int currentMode =  ((Integer) (((Vector) parameters[2]).elementAt(i))).intValue();
+                        modes[i] = currentMode;
+  
+                        boolean isOutputParameter =
+                            ( (currentMode == JDBC30Translation.PARAMETER_MODE_OUT) || (currentMode
== JDBC30Translation.PARAMETER_MODE_IN_OUT) );
+                        
+                        //
+                        // We used to forbid LOBs as method parameters. DERBY-4066 lifted
this
+                        // restriction except for the output parameters of
+                        // procedures. In that case, we are not able to send the
+                        // output LOB across the network to a client today. In addition,
we still don't support XML values as parameters.
+                        // Presumably, the XML datatype would map to a JDBC java.sql.SQLXML
type.
+                        // We have no support for that type today.
+                        //
+                        if ( !types[ i ].isUserDefinedType() )
+                        {
+                            if
+                                (
+                                 ( TypeId.getBuiltInTypeId(types[i].getJDBCTypeId()).isLongConcatableTypeId()
&& isOutputParameter )
+                                 ||
+                                 (TypeId.getBuiltInTypeId(types[i].getJDBCTypeId()).isXMLTypeId())
+                                 )
+                            { throw StandardException.newException(SQLState.LANG_LONG_DATA_TYPE_NOT_ALLOWED,
names[i]); }
+                        }
+                    }
 
 					if (paramCount > 1) {
 						String[] dupNameCheck = new String[paramCount];

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MethodCallNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MethodCallNode.java?rev=982936&r1=982935&r2=982936&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MethodCallNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MethodCallNode.java
Fri Aug  6 12:23:10 2010
@@ -339,13 +339,6 @@ abstract class MethodCallNode extends Ja
 					if (routineInfo == null)
 						signature[ parm ] = methodParms[ parm ].getJSQLType();
                     
-                    // prohibit LOB columns/types
-                    if (signature[parm] != null) {
-                        String type = signature[parm].getSQLType().getTypeId().getSQLTypeName();
-                        if (type.equals("BLOB") || type.equals("CLOB") || type.equals("NCLOB"))
{
-                            throw StandardException.newException(SQLState.LOB_AS_METHOD_ARGUMENT_OR_RECEIVER);
-                        }
-                    }
 				}
 			}
 		}

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/LOB.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/LOB.out?rev=982936&r1=982935&r2=982936&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/LOB.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/LOB.out
Fri Aug  6 12:23:10 2010
@@ -17,11 +17,8 @@ ij> --
 -- This test lang/LOB.sql still includes tests for 
 -- DB2 UDB incompatible datatype NCLOB.
 -- Still waiting for DB2 UDB compatible functionality for NCLOB to be implemented
-
 -- Also note that in DB2 UDB, to create BLOB and CLOB strings greater than 1 gigabyte,
 -- the NOT LOGGED option must be specified (SQLSTATE 42993).
-
-
 -- test that BLOB/CLOB are not reserved words
 create table blob(a int);
 0 rows inserted/updated/deleted
@@ -497,21 +494,6 @@ ij> values (cast('1' as clob(1M)))->toSt
 ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver.
 ij> values (cast('1' as nclob(1M)))->toString();
 ERROR 0A000: Feature not implemented: NCLOB.
-ij> -- LOB column as parameter not allowed
-select b->equals('3') from b;
-ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver.
-ij> select c->equals('3') from c;
-ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver.
-ij> -- explicit LOB as parameter not allowed
-values '3'->equals(cast('3' as blob(7)));
-ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
-ij> values '3'->equals(cast('3' as clob(7)));
-ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver.
-ij> -- LOB column as parameter not allowed
-select '3'->equals(b) from b;
-ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver.
-ij> select '3'->equals(c) from c;
-ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver.
 ij> drop table b;
 0 rows inserted/updated/deleted
 ij> drop table c;

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java?rev=982936&r1=982935&r2=982936&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java
Fri Aug  6 12:23:10 2010
@@ -47,6 +47,7 @@ import org.apache.derbyTesting.junit.Bas
 import org.apache.derbyTesting.junit.JDBC;
 import org.apache.derbyTesting.junit.TestConfiguration;
 import org.apache.derbyTesting.junit.Utilities;
+import org.apache.derbyTesting.functionTests.tests.lang.StringColumnVTI;
 
 /**
  * 
@@ -54,6 +55,9 @@ import org.apache.derbyTesting.junit.Uti
 public class ParameterMappingTest extends BaseJDBCTestCase {
     private static boolean HAVE_BIG_DECIMAL;
 
+    private static final String BAD_TYPE = "42962";
+    private static final String UTF8 = "UTF-8";
+
     static {
         if (JDBC.vmSupportsJSR169())
             HAVE_BIG_DECIMAL = false;
@@ -543,7 +547,7 @@ public class ParameterMappingTest extend
                 s.execute(procSQL);
             } catch (SQLException sqle) {
                 // may get error that column is not allowed
-                if ("42962".equals(sqle.getSQLState()))
+                if (BAD_TYPE.equals(sqle.getSQLState()))
                     continue;
                 else
                     fail(sqle.getSQLState() + ":" + sqle.getMessage());
@@ -587,11 +591,12 @@ public class ParameterMappingTest extend
                             // set the IN value with an accepted value according to its type
                             // set the INOUT value with an accepted value according to its
registered type
                             if (setValidValue(csp, 1, jdbcTypes[type]) && setValidValue(csp,
2, jopt)) {
-                                
+
                                 csp.execute();
                                 
-                                // now get the INOUT, OUT parameters according to their registered
type.
+                               // now get the INOUT, OUT parameters according to their registered
type.
                                 getOutValue(csp, 2, jopt,type); 
+                                
                                 getOutValue(csp, 3, jopt,type);
                         }
 
@@ -599,6 +604,10 @@ public class ParameterMappingTest extend
                             boolean expectedConversionError = ("22018".equals(sqle.getSQLState())||

                                                                "22007".equals(sqle.getSQLState())
||
                                                                "22005".equals(sqle.getSQLState()));
+                            if ( !expectedConversionError)
+                            {
+                                printStackTrace( sqle );
+                            }
                             assertTrue("FAIL: Unexpected exception" + sqle.getSQLState()
+ ":" + sqle.getMessage(),
                                     expectedConversionError);
                         }
@@ -613,7 +622,135 @@ public class ParameterMappingTest extend
                 s.close();
                 conn.commit();
          }      
-}
+    }
+
+    /**
+     * Verify correct mapping of clobs.
+     */
+    public void testClobMapping() throws Exception
+    {
+        Connection conn = getConnection();
+        PreparedStatement ps;
+        CallableStatement cs;
+
+        //
+        // Clob input parameter
+        //
+        ps = chattyPrepare
+            (
+             conn,
+             "create procedure clobIn\n" +
+             "( in c clob, out result varchar( 100 ) )\n" +
+             "language java\n" +
+             "parameter style java\n" +
+             "no sql\n" +
+             "external name '" + getClass().getName() + ".clobIn'\n"
+             );
+        ps.execute();
+        ps.close();
+
+        cs = chattyPrepareCall( conn, "call clobIn( cast( 'def' as clob ), ? )" );
+        cs.registerOutParameter( 1, Types.VARCHAR );
+        cs.execute();
+        assertEquals( "def", cs.getString( 1 ) );
+        cs.close();
+
+        cs = chattyPrepareCall( conn, "call clobIn( ?, ? )" );
+        cs.setClob( 1, new StringColumnVTI.SimpleClob( "ghi" ) );
+        cs.registerOutParameter( 2, Types.VARCHAR );
+        cs.execute();
+        assertEquals( "ghi", cs.getString( 2 ) );
+        cs.close();
+
+        //
+        // Clob output parameter
+        //
+        expectCompilationError
+            (
+             BAD_TYPE,
+             "create procedure clobOut\n" +
+             "( out c clob )\n" +
+             "language java\n" +
+             "parameter style java\n" +
+             "no sql\n" +
+             "external name '" + getClass().getName() + ".clobOut'\n"
+             );
+        
+        //
+        // Clob inout parameter
+        //
+        expectCompilationError
+            (
+             BAD_TYPE,
+             "create procedure clobInOut\n" +
+             "( inout c clob )\n" +
+             "language java\n" +
+             "parameter style java\n" +
+             "no sql\n" +
+             "external name '" + getClass().getName() + ".clobInOut'\n"
+             );
+    }
+
+    /**
+     * Verify correct mapping of blobs.
+     */
+    public void testBlobMapping() throws Exception
+    {
+        Connection conn = getConnection();
+        PreparedStatement ps;
+        CallableStatement cs;
+
+        //
+        // Blob input parameter
+        //
+        ps = chattyPrepare
+            (
+             conn,
+             "create procedure blobIn\n" +
+             "( in c blob, out result varchar( 100 ) )\n" +
+             "language java\n" +
+             "parameter style java\n" +
+             "no sql\n" +
+             "external name '" + getClass().getName() + ".blobIn'\n"
+             );
+        ps.execute();
+        ps.close();
+
+        cs = chattyPrepareCall( conn, "call blobIn( ?, ? )" );
+        cs.setBlob( 1, new StringColumnVTI.SimpleBlob( "ghi".getBytes( UTF8 ) ) );
+        cs.registerOutParameter( 2, Types.VARCHAR );
+        cs.execute();
+        assertEquals( "ghi", cs.getString( 2 ) );
+        cs.close();
+
+        //
+        // Blob output parameter
+        //
+        expectCompilationError
+            (
+             BAD_TYPE,
+             "create procedure blobOut\n" +
+             "( out c blob )\n" +
+             "language java\n" +
+             "parameter style java\n" +
+             "no sql\n" +
+             "external name '" + getClass().getName() + ".blobOut'\n"
+             );
+        
+        //
+        // Blob inout parameter
+        //
+        expectCompilationError
+            (
+             BAD_TYPE,
+             "create procedure blobInOut\n" +
+             "( inout c blob )\n" +
+             "language java\n" +
+             "parameter style java\n" +
+             "no sql\n" +
+             "external name '" + getClass().getName() + ".blobInOut'\n"
+             );
+    }
 
     /*
      * (non-Javadoc)
@@ -3776,6 +3913,127 @@ public class ParameterMappingTest extend
                 out[0] = new BigDecimal(84.1);
         }
 
+    /*
+    ** Procedures which use LOBs - for parameter mapping testing.
+    */
+
+    public static void pmap(Blob in, Blob[] inout, Blob[] out) throws SQLException {
+        int    leftLength = (int) in.length();
+        int    rightLength = (int) inout[0].length();
+        byte[] left = in.getBytes( 1L, leftLength );
+        byte[] right = inout[0].getBytes( 1L, rightLength );
+        byte[] retval = new byte[ leftLength + rightLength ];
+        System.arraycopy( left, 0, retval, 0, leftLength );
+        System.arraycopy( right, 0, retval, leftLength, rightLength );
+        inout[0] = new StringColumnVTI.SimpleBlob( retval );
+        
+        out[0] = new StringColumnVTI.SimpleBlob( new byte[] { (byte) 1, (byte) 2, (byte)
3 } );
+    }
+
+    public static void pmap(Clob in, Clob[] inout, Clob[] out) throws SQLException {
+        inout[0] = new StringColumnVTI.SimpleClob( in.getSubString( 1L, (int) in.length()
) + inout[0].getSubString( 1L, (int) inout[0].length() ) );
+        out[0] = new StringColumnVTI.SimpleClob( "abc" );
+    }
+
+    //
+    // Clob procs
+    //
+    
+    public static void clobIn( Clob c, String[] result ) throws SQLException
+    {
+        result[ 0 ] = getClobValue( c );
+    }
+    public static void clobOut( Clob[] c ) throws SQLException
+    {
+        c[ 0 ] = new StringColumnVTI.SimpleClob( "abc" );
+    }
+    public static void clobInOut( Clob[] c ) throws SQLException
+    {
+        String value = getClobValue( c[ 0 ] );
+        
+        char[] inValue = value.toCharArray();
+        char[] outValue = reverse( inValue );
+
+        c[ 0 ] = new StringColumnVTI.SimpleClob( new String( outValue ) );
+    }
+
+    private static String getClobValue( Clob c ) throws SQLException
+    {
+        return c.getSubString( 1L, (int) c.length() );
+    }
+    private static char[] reverse( char[] in )
+    {
+        int count = in.length;
+
+        char[] retval = new char[ count ];
+        for ( int i = 0; i < count; i++ ) { retval[ i ] = in[ (count - i) - 1 ]; }
+
+        return retval;
+    }
+    
+    //
+    // Blob procs
+    //
+    
+    public static void blobIn( Blob c, String[] result ) throws Exception
+    {
+        result[ 0 ] = getBlobValue( c );
+    }
+    public static void blobOut( Blob[] c ) throws Exception
+    {
+        c[ 0 ] = new StringColumnVTI.SimpleBlob( "abc".getBytes( UTF8 ) );
+    }
+    public static void blobInOut( Blob[] c ) throws Exception
+    {
+        String value = getBlobValue( c[ 0 ] );
+        
+        char[] inValue = value.toCharArray();
+        char[] outValue = reverse( inValue );
+
+        c[ 0 ] = new StringColumnVTI.SimpleBlob( (new String( outValue )).getBytes( UTF8
) );
+    }
+
+    private static String getBlobValue( Blob c ) throws Exception
+    {
+        byte[] bytes = c.getBytes( 1L, (int) c.length() );
+
+        return new String( bytes, UTF8 );
+    }
+    
+    //
+    // Debug helpers
+    //
+    
+    /**
+     * Prepare a statement and report its sql text.
+     */
+    protected PreparedStatement   chattyPrepare( Connection conn, String text )
+        throws SQLException
+    {
+        println( "Preparing statement:\n\t" + text );
+        
+        return conn.prepareStatement( text );
+    }
+    /**
+     * Prepare a call statement and report its sql text.
+     */
+    protected CallableStatement   chattyPrepareCall( Connection conn, String text )
+        throws SQLException
+    {
+        println( "Preparing statement:\n\t" + text );
+        
+        return conn.prepareCall( text );
+    }
+
+    /**
+     * Assert that the statement text, when compiled, raises an exception
+     */
+    protected void    expectCompilationError( String sqlState, String query )
+    {
+        println( "\nExpecting " + sqlState + " when preparing:\n\t" + query );
+
+        assertCompileError( sqlState, query );
+    }
 }
 
 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiSignatures.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiSignatures.java?rev=982936&r1=982935&r2=982936&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiSignatures.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiSignatures.java
Fri Aug  6 12:23:10 2010
@@ -1329,4 +1329,48 @@ public  class   AnsiSignatures
     public  static  Blob  blob_Blob_String( Blob a ) { return new StringColumnVTI.SimpleBlob(
new byte[] { (byte) -1 } ); }
     public  static  Blob  blob_Blob_String( Object a ) { return new StringColumnVTI.SimpleBlob(
new byte[] { (byte) -1 } ); }
 
+    //
+    // BLOB arguments
+    //
+    public  static  String  varchar_Blob_Blob( Blob a ) throws Exception { return new String(
a.getBytes( 1L, (int) a.length() ), "UTF-8"  ); }
+    public  static  String  varchar_Blob_Blob( byte a ) { return "-1"; }
+    public  static  String  varchar_Blob_Blob( int a ) { return "-1"; }
+    public  static  String  varchar_Blob_Blob( long a ) { return "-1"; }
+    public  static  String  varchar_Blob_Blob( float a ) { return "-1"; }
+    public  static  String  varchar_Blob_Blob( double a ) { return "-1"; }
+    public  static  String  varchar_Blob_Blob( Byte a ) { return "-1"; }
+    public  static  String  varchar_Blob_Blob( Short a ) { return "-1"; }
+    public  static  String  varchar_Blob_Blob( Long a ) { return "-1"; }
+    public  static  String  varchar_Blob_Blob( Float a ) { return "-1"; }
+    public  static  String  varchar_Blob_Blob( Double a ) { return "-1"; }
+    public  static  String  varchar_Blob_Blob( BigDecimal a ) { return "-1"; }
+    public  static  String  varchar_Blob_Blob( Date a ) { return "-1"; }
+    public  static  String  varchar_Blob_Blob( Time a ) { return "-1"; }
+    public  static  String  varchar_Blob_Blob( Timestamp a ) { return "-1"; }
+    public  static  String  varchar_Blob_Blob( Clob a ) { return "-1"; }
+    public  static  String  varchar_Blob_Blob( String a ) { return "-1"; }
+    public  static  String  varchar_Blob_Blob( Object a ) { return "-1"; }
+
+    //
+    // CLOB arguments
+    //
+    public  static  String  varchar_Clob_Clob( Clob a ) throws Exception { return a.getSubString(
1L, (int) a.length() ); }
+    public  static  String  varchar_Clob_Clob( byte a ) { return "-1"; }
+    public  static  String  varchar_Clob_Clob( int a ) { return "-1"; }
+    public  static  String  varchar_Clob_Clob( long a ) { return "-1"; }
+    public  static  String  varchar_Clob_Clob( float a ) { return "-1"; }
+    public  static  String  varchar_Clob_Clob( double a ) { return "-1"; }
+    public  static  String  varchar_Clob_Clob( Byte a ) { return "-1"; }
+    public  static  String  varchar_Clob_Clob( Short a ) { return "-1"; }
+    public  static  String  varchar_Clob_Clob( Long a ) { return "-1"; }
+    public  static  String  varchar_Clob_Clob( Float a ) { return "-1"; }
+    public  static  String  varchar_Clob_Clob( Double a ) { return "-1"; }
+    public  static  String  varchar_Clob_Clob( BigDecimal a ) { return "-1"; }
+    public  static  String  varchar_Clob_Clob( Date a ) { return "-1"; }
+    public  static  String  varchar_Clob_Clob( Time a ) { return "-1"; }
+    public  static  String  varchar_Clob_Clob( Timestamp a ) { return "-1"; }
+    public  static  String  varchar_Clob_Clob( Blob a ) { return "-1"; }
+    public  static  String  varchar_Clob_Clob( String a ) { return "-1"; }
+    public  static  String  varchar_Clob_Clob( Object a ) { return "-1"; }
+
 }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiSignaturesTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiSignaturesTest.java?rev=982936&r1=982935&r2=982936&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiSignaturesTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiSignaturesTest.java
Fri Aug  6 12:23:10 2010
@@ -23,6 +23,8 @@ package org.apache.derbyTesting.function
 
 import java.sql.SQLException;
 import java.sql.SQLWarning;
+import java.sql.Blob;
+import java.sql.Clob;
 import java.sql.Connection;
 import java.sql.Statement;
 import java.sql.PreparedStatement;
@@ -391,15 +393,45 @@ public class AnsiSignaturesTest extends 
     public  void    test_clob_Clob_String()
         throws Exception
     {
+        Connection  conn = getConnection();
+
         declareAndRunFunction
             ( "clob_Clob_String", "clob", new String[] { "varchar( 10 )" }, "'3'", "3" );
+
+        // now test clob arguments
+        declareFunction( conn, "varchar_Clob_Clob", "varchar( 10 )", new String[] { "clob"
} );
+
+        runFunction( conn, "varchar_Clob_Clob", " \"clob_Clob_String\"( 'abc' )", "abc",
null );
+
+        // make sure that you can set lob-typed ? parameters
+        PreparedStatement ps = chattyPrepare( conn, "values ( \"varchar_Clob_Clob\"( ? )
)" );
+        String expectedValue = "abcdef";
+        Clob clob = AnsiSignatures.clob_Clob_String( expectedValue );
+        ps.setClob( 1, clob );
+        String actualValue = (String) getScalarString( ps );
+        assertTrue( expectedValue.equals( actualValue ) );
     }
 
     public  void    test_blob_Blob_String()
         throws Exception
     {
+        Connection  conn = getConnection();
+
         declareAndRunFunction
             ( "blob_Blob_String", "blob", new String[] { "varchar( 10 )" }, "'3'", "33" );
+
+        // now test blob arguments
+        declareFunction( conn, "varchar_Blob_Blob", "varchar( 10 )", new String[] { "blob"
} );
+
+        runFunction( conn, "varchar_Blob_Blob", " \"blob_Blob_String\"( 'abc' )", "abc",
null );
+
+        // make sure that you can set lob-typed ? parameters
+        PreparedStatement ps = chattyPrepare( conn, "values ( \"varchar_Blob_Blob\"( ? )
)" );
+        String expectedValue = "34";
+        Blob blob = AnsiSignatures.blob_Blob_String( expectedValue );
+        ps.setBlob( 1, blob );
+        String actualValue = getScalarString( ps );
+        assertTrue( expectedValue.equals( actualValue ) );
     }
 
     ///////////////////////////////////////////////////////////////////////////////////
@@ -610,7 +642,7 @@ public class AnsiSignaturesTest extends 
             ( "double_amb_byte_double", "double", new String[] { "double" }, "3.0", "3.0",
AMBIGUOUS );
     }
     
-
+    
     ///////////////////////////////////////////////////////////////////////////////////
     //
     // UNRESOLVABLE METHODS
@@ -813,4 +845,28 @@ public class AnsiSignaturesTest extends 
         return '"' + raw + '"';
     }
     
+    /**
+     * Prepare a statement and report its sql text.
+     */
+    protected PreparedStatement   chattyPrepare( Connection conn, String text )
+        throws SQLException
+    {
+        println( "Preparing statement:\n\t" + text );
+        
+        return conn.prepareStatement( text );
+    }
+
+    /** Get a scalar result from a query */
+    private String getScalarString( PreparedStatement ps ) throws Exception
+    {
+        ResultSet rs = ps.executeQuery();
+        rs.next();
+        String retval = rs.getString( 1 );
+
+        rs.close();
+        ps.close();
+
+        return retval;
+    }
+  
 }
\ No newline at end of file

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LOB.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LOB.sql?rev=982936&r1=982935&r2=982936&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LOB.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LOB.sql
Fri Aug  6 12:23:10 2010
@@ -17,11 +17,8 @@
 -- This test lang/LOB.sql still includes tests for 
 -- DB2 UDB incompatible datatype NCLOB.
 -- Still waiting for DB2 UDB compatible functionality for NCLOB to be implemented
-
 -- Also note that in DB2 UDB, to create BLOB and CLOB strings greater than 1 gigabyte,
 -- the NOT LOGGED option must be specified (SQLSTATE 42993).
-
-
 -- test that BLOB/CLOB are not reserved words
 create table blob(a int);
 insert into blob values(3);
@@ -299,18 +296,6 @@ values (cast('1' as blob(1M)))->toString
 values (cast('1' as clob(1M)))->toString();
 values (cast('1' as nclob(1M)))->toString();
 
--- LOB column as parameter not allowed
-select b->equals('3') from b;
-select c->equals('3') from c;
-
--- explicit LOB as parameter not allowed
-values '3'->equals(cast('3' as blob(7)));
-values '3'->equals(cast('3' as clob(7)));
-
--- LOB column as parameter not allowed
-select '3'->equals(b) from b;
-select '3'->equals(c) from c;
-
 drop table b;
 drop table c;
 



Mime
View raw message