You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by rh...@apache.org on 2006/04/19 17:14:30 UTC
svn commit: r395263 - in /db/derby/code/trunk/java:
client/org/apache/derby/client/am/ engine/org/apache/derby/impl/jdbc/
testing/org/apache/derbyTesting/functionTests/tests/jdbc4/
Author: rhillegas
Date: Wed Apr 19 08:14:29 2006
New Revision: 395263
URL: http://svn.apache.org/viewcvs?rev=395263&view=rev
Log:
DERBY-1147: Commit Kristian's patch adding embedded and network client support for miscellaneous CallableStatement methods.
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/CallableStatementTestSetup.java (with props)
Modified:
db/derby/code/trunk/java/client/org/apache/derby/client/am/CallableStatement.java
db/derby/code/trunk/java/client/org/apache/derby/client/am/CallableStatement40.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedCallableStatement20.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedCallableStatement40.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/CallableStatementTest.java
Modified: db/derby/code/trunk/java/client/org/apache/derby/client/am/CallableStatement.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/client/org/apache/derby/client/am/CallableStatement.java?rev=395263&r1=395262&r2=395263&view=diff
==============================================================================
--- db/derby/code/trunk/java/client/org/apache/derby/client/am/CallableStatement.java (original)
+++ db/derby/code/trunk/java/client/org/apache/derby/client/am/CallableStatement.java Wed Apr 19 08:14:29 2006
@@ -20,9 +20,11 @@
package org.apache.derby.client.am;
-import java.sql.SQLException;
import org.apache.derby.shared.common.reference.SQLState;
+import java.io.Reader;
+import java.sql.SQLException;
+
public class CallableStatement extends PreparedStatement
implements java.sql.PreparedStatement,
java.sql.CallableStatement,
@@ -1377,6 +1379,34 @@
throw jdbcMethodNotImplemented();
}
+ //-------------------------- JDBC 4.0 methods --------------------------------
+
+ public Reader getCharacterStream(int parameterIndex)
+ throws SQLException {
+ try {
+ synchronized (connection_) {
+ if (agent_.loggingEnabled()) {
+ agent_.logWriter_.traceEntry(this, "getCharacterStream", parameterIndex);
+ }
+ super.checkForClosedStatement();
+ parameterIndex = checkForEscapedCallWithResult(parameterIndex);
+ checkGetterPreconditions(parameterIndex);
+ setWasNull(parameterIndex);
+ Reader reader = null;
+ if (this.wasNull_ == WAS_NOT_NULL) {
+ reader = singletonRowData_.getCharacterStream(parameterIndex);
+ }
+ if (agent_.loggingEnabled()) {
+ agent_.logWriter_.traceExit(this, "getCharacterStream", reader);
+ }
+ return reader;
+ }
+
+ } catch (SqlException se) {
+ throw se.getSQLException();
+ }
+ }
+
//----------------------------helper methods----------------------------------
private int checkForEscapedCallWithResult(int parameterIndex) throws SqlException {
Modified: db/derby/code/trunk/java/client/org/apache/derby/client/am/CallableStatement40.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/client/org/apache/derby/client/am/CallableStatement40.java?rev=395263&r1=395262&r2=395263&view=diff
==============================================================================
--- db/derby/code/trunk/java/client/org/apache/derby/client/am/CallableStatement40.java (original)
+++ db/derby/code/trunk/java/client/org/apache/derby/client/am/CallableStatement40.java Wed Apr 19 08:14:29 2006
@@ -42,11 +42,6 @@
super(agent, connection, sql, type, concurrency, holdability);
}
- public Reader getCharacterStream(int parameterIndex)
- throws SQLException {
- throw SQLExceptionFactory.notImplemented("getCharacterStream(int)");
- }
-
public Reader getCharacterStream(String parameterName)
throws SQLException {
throw SQLExceptionFactory.notImplemented("getCharacterStream(String)");
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedCallableStatement20.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedCallableStatement20.java?rev=395263&r1=395262&r2=395263&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedCallableStatement20.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedCallableStatement20.java Wed Apr 19 08:14:29 2006
@@ -26,6 +26,7 @@
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
+import java.sql.Types;
/* ---- New jdbc 2.0 types ----- */
import java.sql.Array;
@@ -36,14 +37,20 @@
import java.net.URL;
import java.util.Map;
+import java.io.ByteArrayInputStream;
import java.io.InputStream;
+import java.io.InputStreamReader;
import java.io.Reader;
+import java.io.StringReader;
+import java.io.UnsupportedEncodingException;
import java.util.Calendar;
import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.services.io.StreamStorable;
import org.apache.derby.iapi.sql.conn.StatementContext;
+import org.apache.derby.iapi.reference.JDBC30Translation;
import org.apache.derby.iapi.reference.SQLState;
import org.apache.derby.iapi.types.DataValueDescriptor;
@@ -1073,15 +1080,149 @@
}
return false;
}
-}
-
-
-
-
-
-
-
-
-
+ /////////////////////////////////////////////////////////////////////////
+ //
+ // JDBC 4.0 - New public methods
+ //
+ /////////////////////////////////////////////////////////////////////////
+
+ /**
+ * Retrieves the value of the designated parameter as a
+ * <code>java.io.Reader</code> object in the Java programming language.
+ * Introduced in JDBC 4.0.
+ *
+ * @param parameterIndex the first parameter is 1, the second is 2, ...
+ * @return a <code>java.io.Reader</code> object that contains the parameter
+ * value; if the value is SQL <code>NULL</code>, the value returned
+ * is <code>null</code> in the Java programming language.
+ * @throws SQLException if a database access error occurs or this method is
+ * called on a closed <code>CallableStatement</code>
+ */
+ public Reader getCharacterStream(int parameterIndex)
+ throws SQLException {
+ checkStatus();
+ // Make sure the specified parameter has mode OUT or IN/OUT.
+ switch (getParms().getParameterMode(parameterIndex)) {
+ case JDBC30Translation.PARAMETER_MODE_IN:
+ case JDBC30Translation.PARAMETER_MODE_UNKNOWN:
+ throw newSQLException(SQLState.LANG_NOT_OUTPUT_PARAMETER,
+ Integer.toString(parameterIndex));
+ }
+ Reader reader = null;
+ int paramType = getParameterJDBCType(parameterIndex);
+ switch (paramType) {
+ // Handle character/string types.
+ case Types.CHAR:
+ case Types.VARCHAR:
+ case Types.LONGVARCHAR:
+ case Types.CLOB:
+ boolean pushStack = false;
+ Object syncObject = getConnectionSynchronization();
+ synchronized (syncObject) {
+ try {
+ DataValueDescriptor param =
+ getParms().getParameterForGet(parameterIndex -1);
+ if (param.isNull()) {
+ break;
+ }
+ pushStack = true;
+ setupContextStack();
+
+ StreamStorable ss = (StreamStorable)param;
+ InputStream stream = ss.returnStream();
+ if (stream == null) {
+ reader = new StringReader(param.getString());
+ } else {
+ reader = new UTF8Reader(stream, 0, this, syncObject);
+ }
+ } catch (Throwable t) {
+ throw EmbedResultSet.noStateChangeException(t);
+ } finally {
+ if (pushStack) {
+ restoreContextStack();
+ }
+ }
+ } // End synchronized block
+ break;
+
+ // Handle binary types.
+ // JDBC says to support these, but no defintion exists for the output.
+ // Match JCC which treats the bytes as a UTF-16BE stream.
+ case Types.BINARY:
+ case Types.VARBINARY:
+ case Types.LONGVARBINARY:
+ case Types.BLOB:
+ try {
+ InputStream is = getBinaryStream(parameterIndex);
+ if (is != null) {
+ reader = new InputStreamReader(is, "UTF-16BE");
+ }
+ break;
+ } catch (UnsupportedEncodingException uee) {
+ throw newSQLException(uee.getMessage());
+ }
+
+ default:
+ throw newSQLException(SQLState.LANG_DATA_TYPE_GET_MISMATCH,
+ "java.io.Reader", Util.typeName(paramType));
+ }
+ // Update wasNull.
+ wasNull = (reader == null);
+ return reader;
+ }
+
+ // Private utility classes
+ /**
+ * Get binary stream for a parameter.
+ *
+ * @param parameterIndex first parameter is 1, second is 2 etc.
+ * @return a stream for the binary parameter, or <code>null</code>.
+ *
+ * @throws SQLException if a database access error occurs.
+ */
+ private InputStream getBinaryStream(int parameterIndex)
+ throws SQLException {
+ int paramType = getParameterJDBCType(parameterIndex);
+ switch (paramType) {
+ case Types.BINARY:
+ case Types.VARBINARY:
+ case Types.LONGVARBINARY:
+ case Types.BLOB:
+ break;
+ default:
+ throw newSQLException(SQLState.LANG_DATA_TYPE_GET_MISMATCH,
+ "java.io.InputStream", Util.typeName(paramType));
+ }
+
+ boolean pushStack = false;
+ synchronized (getConnectionSynchronization()) {
+ try {
+ DataValueDescriptor param =
+ getParms().getParameterForGet(parameterIndex -1);
+ wasNull = param.isNull();
+ if (wasNull) {
+ return null;
+ }
+ pushStack = true;
+ setupContextStack();
+
+ StreamStorable ss = (StreamStorable)param;
+ InputStream stream = ss.returnStream();
+ if (stream == null) {
+ stream = new ByteArrayInputStream(param.getBytes());
+ } else {
+ stream = new BinaryToRawStream(stream, param);
+ }
+ return stream;
+ } catch (Throwable t) {
+ throw EmbedResultSet.noStateChangeException(t);
+ } finally {
+ if (pushStack) {
+ restoreContextStack();
+ }
+ }
+ } // End synchronized block
+ }
+}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedCallableStatement40.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedCallableStatement40.java?rev=395263&r1=395262&r2=395263&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedCallableStatement40.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedCallableStatement40.java Wed Apr 19 08:14:29 2006
@@ -43,11 +43,6 @@
super(conn, sql, resultSetType, resultSetConcurrency, resultSetHoldability);
}
- public Reader getCharacterStream(int parameterIndex)
- throws SQLException {
- throw Util.notImplemented();
- }
-
public Reader getCharacterStream(String parameterName)
throws SQLException {
throw Util.notImplemented();
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/CallableStatementTest.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/CallableStatementTest.java?rev=395263&r1=395262&r2=395263&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/CallableStatementTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/CallableStatementTest.java Wed Apr 19 08:14:29 2006
@@ -25,6 +25,8 @@
import org.apache.derbyTesting.functionTests.util.BaseJDBCTestCase;
import org.apache.derbyTesting.functionTests.util.SQLStateConstants;
+import java.io.IOException;
+import java.io.Reader;
import java.sql.*;
import java.lang.reflect.Method;
import java.util.Vector;
@@ -40,7 +42,6 @@
/** Default callable statement used by the tests. */
private CallableStatement cStmt = null;
-
/**
* Create a test with the given name.
*
@@ -59,7 +60,8 @@
public void setUp()
throws SQLException {
con = getConnection();
- cStmt = con.prepareCall("values 1");
+ cStmt = con.prepareCall("? = CALL FLOOR(?)");
+ cStmt.registerOutParameter(1, Types.DOUBLE);
}
/**
@@ -81,6 +83,29 @@
con = null;
}
+ public void testNamedParametersAreNotSupported()
+ throws SQLException {
+ DatabaseMetaData met = con.getMetaData();
+ assertFalse("Named parameters are not supported, but the metadata " +
+ "says they are", met.supportsNamedParameters());
+ met = null;
+ }
+
+ public void testGetDoubleIntOnInParameter()
+ throws SQLException {
+ cStmt.setDouble(2, 3.3);
+ cStmt.execute();
+ try {
+ cStmt.getDouble(2);
+ fail("Calling getDouble on an IN parameter should throw " +
+ "an exception");
+ } catch (SQLException sqle) {
+ // SQLState differ between DerbyNetClient and embedded.
+ String sqlState = usingDerbyNetClient() ? "XJ091" : "XCL26";
+ assertSQLState("Unexpected SQLState", sqlState, sqle);
+ }
+ }
+
public void testGetNClobIntNotImplemented()
throws SQLException {
try {
@@ -124,17 +149,198 @@
}
}
- public void testGetCharacterStreamIntNotImplemented()
+
+ public void testGetCharacterStreamIntOnInvalidTypeDOUBLE()
throws SQLException {
+ cStmt.setDouble(2, 3.3);
+ cStmt.execute();
try {
cStmt.getCharacterStream(1);
- fail("CallableStatement.getCharacterStream(int) " +
- "should not be implemented");
- } catch (SQLFeatureNotSupportedException sfnse) {
- // We are fine, do nothing.
+ fail("An exception signalling invalid data type conversion " +
+ "should have been thrown");
+ } catch (SQLDataException sqlde) {
+ assertSQLState("Exception with invalid SQL state thrown on " +
+ "invalid data type conversion", "22005", sqlde);
+ }
+ }
+
+ /**
+ * Test which SQLState is thrown when getCharacterStream is called
+ * on an IN parameter of an unsupported type.
+ */
+ public void testGetCharacterStreamIntOnInParameterOfInvalidType()
+ throws SQLException {
+ cStmt.setDouble(2, 3.3);
+ cStmt.execute();
+ try {
+ cStmt.getCharacterStream(2);
+ fail("Calling getCharacterStream on an IN parameter should " +
+ "throw an exception");
+ } catch (SQLException sqle) {
+ // SQLState differ between DerbyNetClient and embedded.
+ String sqlState = usingDerbyNetClient() ? "XJ091" : "XCL26";
+ assertSQLState("Exception with invalid SQL state thrown for " +
+ "getCharacterStream on IN parameter",
+ sqlState, sqle);
+ }
+ }
+
+ /**
+ * Test which SQLState is thrown when getCharacterStream is called
+ * on an IN parameter of a supported type.
+ */
+ public void testGetCharacterStreamIntOnInParameterOfValidType()
+ throws SQLException {
+ cStmt = CallableStatementTestSetup.getBinaryDirectProcedure(con);
+ cStmt.setString(1, "A string");
+ cStmt.execute();
+ try {
+ cStmt.getCharacterStream(1);
+ fail("Calling getCharacterStream on an IN parameter should " +
+ "throw an exception");
+ } catch (SQLException sqle) {
+ // SQLState differ between DerbyNetClient and embedded.
+ String sqlState = usingDerbyNetClient() ? "XJ091" : "XCL26";
+ assertSQLState("Exception with invalid SQL state thrown for " +
+ "getCharacterStream on IN parameter",
+ sqlState, sqle);
}
}
+ /**
+ * Test basic use of getCharacterStream on character data.
+ * Create a CallableStatement that takes an integer as input and returns
+ * the number as a string. The string is read as a stream, and the integer
+ * is recreated from it and compared to the integer passed in.
+ */
+ public void testGetCharacterStreamIntVARCHAR()
+ throws IOException, SQLException {
+ cStmt = CallableStatementTestSetup.getIntToStringFunction(con);
+ cStmt.setInt(2, 4509);
+ assertFalse("No resultsets should be returned", cStmt.execute());
+ assertEquals("Incorrect updatecount", -1, cStmt.getUpdateCount());
+ // Get a character stream
+ Reader cStream = cStmt.getCharacterStream(1);
+ assertFalse("Stream should not be null", cStmt.wasNull());
+ assertNotNull("Stream is null even though wasNull() returned false",
+ cStream);
+ char[] chars = new char[4];
+ assertEquals("Wrong number of characters read",
+ 4, cStream.read(chars));
+ // Make sure we have reached end of stream.
+ assertEquals("Expected end of stream, but there were more data",
+ -1, cStream.read());
+ cStream.close();
+ String result = new String(chars);
+ assertEquals("Incorrect result obtained through java.io.Reader",
+ "4509", result);
+ }
+
+ /**
+ * Test basic use of getCharacterStream on binary data.
+ * Create a CallableStatement that takes a string as input and returns
+ * a byte representation, which is then read through a stream. The string
+ * is recreated and compared to the one passed in. Note that strings must
+ * be represented in UTF-16BE for this to work.
+ */
+ public void testGetCharacterStreamIntVARBINARYDirect()
+ throws IOException, SQLException {
+ String data = "This is the test string.";
+ cStmt = CallableStatementTestSetup.getBinaryDirectProcedure(con);
+ cStmt.setString(1, data);
+ assertFalse("No resultsets should be returned", cStmt.execute());
+ // Note that getUpdateCount behaves differently on client and embedded.
+ assertEquals("Incorrect updatecount",
+ usingEmbedded() ? 0 : -1,
+ cStmt.getUpdateCount());
+ Reader cStream = cStmt.getCharacterStream(2);
+ assertFalse("Stream should not be null", cStmt.wasNull());
+ assertNotNull("Stream is null even though wasNull() returned false",
+ cStream);
+ // Assume we don't know how many bytes the string will be represented
+ // by, just create enough space and read until stream is exhausted.
+ // To be able to read the string back, getBytes must be called with
+ // UTF-16BE charset, because Derby uses UTF-16BE encoding as default.
+ // JDBC does not specify which charset to use for binary data, and
+ // UTF-16BE was apparently selected to match JCC.
+ char[] tmpChars = new char[data.length() * 4];
+ int curChar = cStream.read();
+ int index = 0;
+ while (curChar != -1) {
+ tmpChars[index] = (char)curChar;
+ index++;
+ curChar = cStream.read();
+ }
+ cStream.close();
+ char[] chars = new char[index];
+ System.arraycopy(tmpChars, 0, chars, 0, index);
+ String result = new String(chars);
+ assertEquals("Incorrect result obtained through java.io.Reader",
+ data, result);
+ }
+
+ /**
+ * Fetch a string stored as bytes from the database through a reader,
+ * then recreate the string.
+ */
+ public void testGetCharacterStreamIntVARBINARYFromDb()
+ throws IOException, SQLException {
+ cStmt = CallableStatementTestSetup.getBinaryFromDbFunction(con);
+ cStmt.setInt(2, CallableStatementTestSetup.STRING_BYTES_ID);
+ assertFalse("No resultsets should be returned", cStmt.execute());
+ assertEquals("Incorrect updatecount", -1, cStmt.getUpdateCount());
+ Reader cStream = cStmt.getCharacterStream(1);
+ assertFalse("Stream should not be null", cStmt.wasNull());
+ assertNotNull("Stream is null even though wasNull() returned false",
+ cStream);
+ char[] tmpChars = new char[32672];
+ int curChar = cStream.read();
+ int index = 0;
+ while (curChar != -1) {
+ tmpChars[index] = (char)curChar;
+ index++;
+ curChar = cStream.read();
+ }
+ char[] chars = new char[index];
+ System.arraycopy(tmpChars, 0, chars, 0, index);
+ tmpChars = null;
+ cStream.close();
+ String result = new String(chars);
+ assertEquals("Strings not equal",
+ CallableStatementTestSetup.STRING_BYTES,
+ result);
+ }
+
+ /**
+ * Read a SQL NULL value from a VARBINARY column through a reader.
+ */
+ public void testGetCharacterStreamIntOnVARBINARYWithNull()
+ throws SQLException {
+ cStmt = CallableStatementTestSetup.getBinaryFromDbFunction(con);
+ cStmt.setInt(2, CallableStatementTestSetup.SQL_NULL_ID);
+ assertFalse("No resultsets should be returned", cStmt.execute());
+ assertEquals("Incorrect updatecount", -1, cStmt.getUpdateCount());
+ Reader cStream = cStmt.getCharacterStream(1);
+ assertTrue("Stream should be null", cStmt.wasNull());
+ assertNull("Stream is not null even though wasNull() returned true",
+ cStream);
+ }
+
+ /**
+ * Read a SQL NULL value from a VARCHAR column through a reader.
+ */
+ public void testGetCharacterStreamIntOnVARCHARWithNull()
+ throws SQLException {
+ cStmt = CallableStatementTestSetup.getVarcharFromDbFunction(con);
+ cStmt.setInt(2, CallableStatementTestSetup.SQL_NULL_ID);
+ assertFalse("No resultsets should be returned", cStmt.execute());
+ assertEquals("Incorrect updatecount", -1, cStmt.getUpdateCount());
+ Reader cStream = cStmt.getCharacterStream(1);
+ assertTrue("Stream should be null", cStmt.wasNull());
+ assertNull("Stream is not null even though wasNull() returned true",
+ cStream);
+ }
+
public void testGetCharacterStreamStringNotImplemented()
throws SQLException {
try {
@@ -329,8 +535,9 @@
* Return suite with all tests of the class.
*/
public static Test suite() {
- return (new TestSuite(CallableStatementTest.class,
- "CallableStatementTest suite"));
+ TestSuite suite = new TestSuite(CallableStatementTest.class,
+ "CallableStatementTest suite");
+ return new CallableStatementTestSetup(suite);
}
} // End class CallableStatementTest
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/CallableStatementTestSetup.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/CallableStatementTestSetup.java?rev=395263&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/CallableStatementTestSetup.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/CallableStatementTestSetup.java Wed Apr 19 08:14:29 2006
@@ -0,0 +1,266 @@
+/*
+
+ Derby - Class CallableStatementTestSetup
+
+ Copyright 2006 The Apache Software Foundation or its licensors, as applicable.
+
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+ */
+
+package org.apache.derbyTesting.functionTests.tests.jdbc4;
+
+import junit.framework.Assert;
+import junit.framework.Test;
+import junit.extensions.TestSetup;
+
+import org.apache.derbyTesting.functionTests.util.BaseJDBCTestCase;
+
+import java.io.UnsupportedEncodingException;
+import java.sql.*;
+
+/**
+ * Create the necessary tables, function and procedures for running the
+ * CallableStatement tests under JDK 1.6.
+ * Java methods used as functions and procedures are also implemented here,
+ * along with helper methods that returns CallableStatements for the various
+ * functions and procedures.
+ */
+public class CallableStatementTestSetup
+ extends TestSetup {
+
+ private static final String SOURCECLASS = "org.apache.derbyTesting." +
+ "functionTests.tests.jdbc4.CallableStatementTestSetup.";
+
+ /** List of tables to drop on tear-down */
+ private static final String[] TABLE_DROPS = new String[] {
+ "CSDATA"};
+ /** List of functions to drop on tear-down. */
+ private static final String[] FUNCTION_DROPS = new String[] {
+ "INT_TO_STRING", "GET_BINARY_DB", "GET_VARCHAR_DB"};
+ /** List of procedures to drop on tear-down. */
+ private static final String[] PROCEDURE_DROPS = new String[] {
+ "GET_BINARY_DIRECT"};
+
+ /** Id for row with byte representation of a string. */
+ public static final int STRING_BYTES_ID = 1;
+ /**
+ * String converted to bytes in UTF-16BE representation.
+ * Note that the charset used matters, and for Derby it must be UTF-16BE.
+ */
+ public static final String STRING_BYTES =
+ "This is a string, converted to bytes and inserted into the database";
+
+ /** Id for row with SQL NULL values. */
+ public static final int SQL_NULL_ID = 2;
+
+ /**
+ * Create a new test setup for the CallableStatementTest.
+ *
+ * @param test the test/suite to provide setup for.
+ */
+ public CallableStatementTestSetup(Test test) {
+ super(test);
+ }
+
+ public void setUp()
+ throws SQLException {
+ Connection con = BaseJDBCTestCase.getConnection();
+ // Create the tables, functions and procedures we need.
+ Statement stmt = con.createStatement();
+ // Create table CSDATA and populate
+ stmt.execute("CREATE TABLE CSDATA (ID INT PRIMARY KEY," +
+ "BINARYDATA VARCHAR(256) FOR BIT DATA, " +
+ "CHARDATA VARCHAR(256))");
+ PreparedStatement pStmt =
+ con.prepareStatement("INSERT INTO CSDATA VALUES (?,?,?)");
+ pStmt.setInt(1, STRING_BYTES_ID);
+ try {
+ pStmt.setBytes(2, STRING_BYTES.getBytes("UTF-16BE"));
+ } catch (UnsupportedEncodingException uee) {
+ SQLException sqle = new SQLException(uee.getMessage());
+ sqle.initCause(uee);
+ throw sqle;
+ }
+ pStmt.setString(3, STRING_BYTES);
+ pStmt.execute();
+ pStmt.setInt(1, SQL_NULL_ID);
+ pStmt.setNull(2, Types.VARBINARY);
+ pStmt.setNull(3, Types.VARCHAR);
+ pStmt.execute();
+ pStmt.close();
+
+ // Create function INT_TO_STRING
+ stmt.execute("CREATE FUNCTION INT_TO_STRING(INTNUM INT) " +
+ "RETURNS VARCHAR(10) " +
+ "PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA " +
+ "EXTERNAL NAME 'java.lang.Integer.toString'");
+ // Create procedure GET_BINARY_DIRECT
+ stmt.execute("CREATE PROCEDURE GET_BINARY_DIRECT(IN INSTRING " +
+ "VARCHAR(40), OUT OUTBYTES VARCHAR(160) FOR BIT DATA) " +
+ "DYNAMIC RESULT SETS 0 " +
+ "PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA " +
+ "EXTERNAL NAME '" + SOURCECLASS + "getBinaryDirect'");
+ // Create function GET_BINARY_DB
+ stmt.execute("CREATE FUNCTION GET_BINARY_DB(ID INT) " +
+ "RETURNS VARCHAR(256) FOR BIT DATA " +
+ "PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA " +
+ "EXTERNAL NAME '" + SOURCECLASS + "getBinaryFromDb'");
+ // Create function GET_VARCHAR_DB
+ stmt.execute("CREATE FUNCTION GET_VARCHAR_DB(ID INT) " +
+ "RETURNS VARCHAR(256) " +
+ "PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA " +
+ "EXTERNAL NAME '" + SOURCECLASS + "getVarcharFromDb'");
+ }
+
+ public void tearDown()
+ throws SQLException {
+ Connection con = BaseJDBCTestCase.getConnection();
+ Statement stmt = con.createStatement();
+ // Drop functions
+ for (String function : FUNCTION_DROPS) {
+ stmt.execute("DROP FUNCTION " + function);
+ }
+ // Drop procedures
+ for (String procedure : PROCEDURE_DROPS) {
+ stmt.execute("DROP PROCEDURE " + procedure);
+ }
+ // Drop tables
+ for (String table : TABLE_DROPS) {
+ stmt.execute("DROP TABLE " + table);
+ }
+ stmt.close();
+ con.close();
+ }
+
+ // Methods for getting CallableStatements
+
+ /**
+ * Return function converting an integer to a string.
+ * Parameter 1: output - String/VARCHAR
+ * Parameter 2: input - int/INT
+ */
+ public static CallableStatement getIntToStringFunction(Connection con)
+ throws SQLException {
+ Assert.assertNotNull("Connection cannot be null", con);
+ CallableStatement cStmt = con.prepareCall("?= CALL INT_TO_STRING(?)");
+ cStmt.registerOutParameter(1, Types.VARCHAR);
+ return cStmt;
+ }
+
+ /**
+ * Return statement for calling procedure that converts a string to a
+ * byte array (UTF-16BE charset).
+ * Parameter 1: input - String/VARCHAR(40)
+ * Parameter 2: output - byte[]/VARCHAR(160) FOR BIT DATA
+ */
+ public static CallableStatement getBinaryDirectProcedure(Connection con)
+ throws SQLException {
+ Assert.assertNotNull("Connection cannot be null", con);
+ CallableStatement cStmt =
+ con.prepareCall("CALL GET_BINARY_DIRECT(?,?)");
+ cStmt.registerOutParameter(2, Types.VARBINARY);
+ return cStmt;
+ }
+
+ /**
+ * Return statement for calling getBinaryFromDb function.
+ * Parameter 1: return/output - byte[]/VARCHAR FOR BINARY - data from db
+ * Parameter 2: input - int/INT - id for row to fetch
+ *
+ * @param con database connection.
+ * @return statement for executing getBinaryFromDb function.
+ */
+ public static CallableStatement getBinaryFromDbFunction(Connection con)
+ throws SQLException {
+ Assert.assertNotNull("Connection cannot be null", con);
+ CallableStatement cStmt =
+ con.prepareCall("?= CALL GET_BINARY_DB(?)");
+ cStmt.registerOutParameter(1, Types.VARBINARY);
+ return cStmt;
+ }
+
+ /**
+ * Return statement for calling getVarcharFromDb function.
+ * Parameter 1: return/output - String/VARCHAR - data from db
+ * Parameter 2: input - int/INT - id for row to fetch
+ *
+ * @param con database connection.
+ * @return statement for executing getVarcharFromDb function.
+ */
+ public static CallableStatement getVarcharFromDbFunction(Connection con)
+ throws SQLException {
+ Assert.assertNotNull("Connection cannot be null", con);
+ CallableStatement cStmt =
+ con.prepareCall("?= CALL GET_VARCHAR_DB(?)");
+ cStmt.registerOutParameter(1, Types.VARCHAR);
+ return cStmt;
+ }
+
+ // Methods used as functions and procedures in the db
+
+ /**
+ * Procedure creating a byte representation of a string.
+ *
+ * @param inputString a string.
+ * @param outputByte string returned as UTF-16BE byte representation.
+ */
+ public static void getBinaryDirect(String inputString, byte[][] outputByte) {
+ try {
+ outputByte[0] = inputString.getBytes("UTF-16BE");
+ } catch (java.io.UnsupportedEncodingException uee) {
+ outputByte[0] = new byte[0];
+ }
+ }
+
+ /**
+ * Function fetching binary data from the database.
+ *
+ * @param id id of row to fetch.
+ * @return a byte array.
+ */
+ public static byte[] getBinaryFromDb(int id)
+ throws Exception {
+ Connection con = DriverManager.getConnection("jdbc:default:connection");
+ Statement stmt = con.createStatement();
+ ResultSet rs = stmt.executeQuery("SELECT BINARYDATA FROM CSDATA " +
+ "WHERE ID = " + id);
+ rs.next();
+ byte[] bytes = rs.getBytes(1);
+ rs.close();
+ stmt.close();
+ con.close();
+ return bytes;
+ }
+
+ /**
+ * Function fetching character data from the database.
+ *
+ * @param id id of row to fetch.
+ * @return a string.
+ */
+ public static String getVarcharFromDb(int id)
+ throws Exception {
+ Connection con = DriverManager.getConnection("jdbc:default:connection");
+ Statement stmt = con.createStatement();
+ ResultSet rs = stmt.executeQuery("SELECT CHARDATA FROM CSDATA " +
+ "WHERE ID = " + id);
+ rs.next();
+ String chardata = rs.getString(1);
+ rs.close();
+ stmt.close();
+ con.close();
+ return chardata;
+ }
+
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/CallableStatementTestSetup.java
------------------------------------------------------------------------------
svn:eol-style = native