001: /*
002:
003: Derby - Class CallableStatementTestSetup
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to you under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.tests.jdbc4;
023:
024: import junit.framework.Assert;
025: import junit.framework.Test;
026: import junit.extensions.TestSetup;
027:
028: import org.apache.derbyTesting.junit.BaseJDBCTestCase;
029: import org.apache.derbyTesting.junit.BaseJDBCTestSetup;
030:
031: import java.io.UnsupportedEncodingException;
032: import java.sql.*;
033:
034: /**
035: * Create the necessary tables, function and procedures for running the
036: * CallableStatement tests under JDK 1.6.
037: * Java methods used as functions and procedures are also implemented here,
038: * along with helper methods that returns CallableStatements for the various
039: * functions and procedures.
040: */
041: public class CallableStatementTestSetup extends BaseJDBCTestSetup {
042:
043: private static final String SOURCECLASS = "org.apache.derbyTesting."
044: + "functionTests.tests.jdbc4.CallableStatementTestSetup.";
045:
046: /** List of tables to drop on tear-down */
047: private static final String[] TABLE_DROPS = new String[] { "CSDATA" };
048: /** List of functions to drop on tear-down. */
049: private static final String[] FUNCTION_DROPS = new String[] {
050: "INT_TO_STRING", "GET_BINARY_DB", "GET_VARCHAR_DB" };
051: /** List of procedures to drop on tear-down. */
052: private static final String[] PROCEDURE_DROPS = new String[] { "GET_BINARY_DIRECT" };
053:
054: /** Id for row with byte representation of a string. */
055: public static final int STRING_BYTES_ID = 1;
056: /**
057: * String converted to bytes in UTF-16BE representation.
058: * Note that the charset used matters, and for Derby it must be UTF-16BE.
059: */
060: public static final String STRING_BYTES = "This is a string, converted to bytes and inserted into the database";
061:
062: /** Id for row with SQL NULL values. */
063: public static final int SQL_NULL_ID = 2;
064:
065: /**
066: * Create a new test setup for the CallableStatementTest.
067: *
068: * @param test the test/suite to provide setup for.
069: */
070: public CallableStatementTestSetup(Test test) {
071: super (test);
072: }
073:
074: protected void setUp() throws SQLException {
075: Connection con = getConnection();
076: // Create the tables, functions and procedures we need.
077: Statement stmt = con.createStatement();
078: // Create table CSDATA and populate
079: stmt.execute("CREATE TABLE CSDATA (ID INT PRIMARY KEY,"
080: + "BINARYDATA VARCHAR(256) FOR BIT DATA, "
081: + "CHARDATA VARCHAR(256))");
082: PreparedStatement pStmt = con
083: .prepareStatement("INSERT INTO CSDATA VALUES (?,?,?)");
084: pStmt.setInt(1, STRING_BYTES_ID);
085: try {
086: pStmt.setBytes(2, STRING_BYTES.getBytes("UTF-16BE"));
087: } catch (UnsupportedEncodingException uee) {
088: SQLException sqle = new SQLException(uee.getMessage());
089: sqle.initCause(uee);
090: throw sqle;
091: }
092: pStmt.setString(3, STRING_BYTES);
093: pStmt.execute();
094: pStmt.setInt(1, SQL_NULL_ID);
095: pStmt.setNull(2, Types.VARBINARY);
096: pStmt.setNull(3, Types.VARCHAR);
097: pStmt.execute();
098: pStmt.close();
099:
100: // Create function INT_TO_STRING
101: stmt.execute("CREATE FUNCTION INT_TO_STRING(INTNUM INT) "
102: + "RETURNS VARCHAR(10) "
103: + "PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA "
104: + "EXTERNAL NAME 'java.lang.Integer.toString'");
105: // Create procedure GET_BINARY_DIRECT
106: stmt
107: .execute("CREATE PROCEDURE GET_BINARY_DIRECT(IN INSTRING "
108: + "VARCHAR(40), OUT OUTBYTES VARCHAR(160) FOR BIT DATA) "
109: + "DYNAMIC RESULT SETS 0 "
110: + "PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA "
111: + "EXTERNAL NAME '"
112: + SOURCECLASS
113: + "getBinaryDirect'");
114: // Create function GET_BINARY_DB
115: stmt.execute("CREATE FUNCTION GET_BINARY_DB(ID INT) "
116: + "RETURNS VARCHAR(256) FOR BIT DATA "
117: + "PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA "
118: + "EXTERNAL NAME '" + SOURCECLASS + "getBinaryFromDb'");
119: // Create function GET_VARCHAR_DB
120: stmt
121: .execute("CREATE FUNCTION GET_VARCHAR_DB(ID INT) "
122: + "RETURNS VARCHAR(256) "
123: + "PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA "
124: + "EXTERNAL NAME '" + SOURCECLASS
125: + "getVarcharFromDb'");
126: stmt.close();
127: }
128:
129: protected void tearDown() throws Exception {
130: Connection con = getConnection();
131: Statement stmt = con.createStatement();
132: // Drop functions
133: for (String function : FUNCTION_DROPS) {
134: stmt.execute("DROP FUNCTION " + function);
135: }
136: // Drop procedures
137: for (String procedure : PROCEDURE_DROPS) {
138: stmt.execute("DROP PROCEDURE " + procedure);
139: }
140: // Drop tables
141: for (String table : TABLE_DROPS) {
142: stmt.execute("DROP TABLE " + table);
143: }
144: stmt.close();
145: super .tearDown();
146: }
147:
148: // Methods for getting CallableStatements
149:
150: /**
151: * Return function converting an integer to a string.
152: * Parameter 1: output - String/VARCHAR
153: * Parameter 2: input - int/INT
154: */
155: public static CallableStatement getIntToStringFunction(
156: Connection con) throws SQLException {
157: Assert.assertNotNull("Connection cannot be null", con);
158: CallableStatement cStmt = con
159: .prepareCall("?= CALL INT_TO_STRING(?)");
160: cStmt.registerOutParameter(1, Types.VARCHAR);
161: return cStmt;
162: }
163:
164: /**
165: * Return statement for calling procedure that converts a string to a
166: * byte array (UTF-16BE charset).
167: * Parameter 1: input - String/VARCHAR(40)
168: * Parameter 2: output - byte[]/VARCHAR(160) FOR BIT DATA
169: */
170: public static CallableStatement getBinaryDirectProcedure(
171: Connection con) throws SQLException {
172: Assert.assertNotNull("Connection cannot be null", con);
173: CallableStatement cStmt = con
174: .prepareCall("CALL GET_BINARY_DIRECT(?,?)");
175: cStmt.registerOutParameter(2, Types.VARBINARY);
176: return cStmt;
177: }
178:
179: /**
180: * Return statement for calling getBinaryFromDb function.
181: * Parameter 1: return/output - byte[]/VARCHAR FOR BINARY - data from db
182: * Parameter 2: input - int/INT - id for row to fetch
183: *
184: * @param con database connection.
185: * @return statement for executing getBinaryFromDb function.
186: */
187: public static CallableStatement getBinaryFromDbFunction(
188: Connection con) throws SQLException {
189: Assert.assertNotNull("Connection cannot be null", con);
190: CallableStatement cStmt = con
191: .prepareCall("?= CALL GET_BINARY_DB(?)");
192: cStmt.registerOutParameter(1, Types.VARBINARY);
193: return cStmt;
194: }
195:
196: /**
197: * Return statement for calling getVarcharFromDb function.
198: * Parameter 1: return/output - String/VARCHAR - data from db
199: * Parameter 2: input - int/INT - id for row to fetch
200: *
201: * @param con database connection.
202: * @return statement for executing getVarcharFromDb function.
203: */
204: public static CallableStatement getVarcharFromDbFunction(
205: Connection con) throws SQLException {
206: Assert.assertNotNull("Connection cannot be null", con);
207: CallableStatement cStmt = con
208: .prepareCall("?= CALL GET_VARCHAR_DB(?)");
209: cStmt.registerOutParameter(1, Types.VARCHAR);
210: return cStmt;
211: }
212:
213: // Methods used as functions and procedures in the db
214:
215: /**
216: * Procedure creating a byte representation of a string.
217: *
218: * @param inputString a string.
219: * @param outputByte string returned as UTF-16BE byte representation.
220: */
221: public static void getBinaryDirect(String inputString,
222: byte[][] outputByte) {
223: try {
224: outputByte[0] = inputString.getBytes("UTF-16BE");
225: } catch (java.io.UnsupportedEncodingException uee) {
226: outputByte[0] = new byte[0];
227: }
228: }
229:
230: /**
231: * Function fetching binary data from the database.
232: *
233: * @param id id of row to fetch.
234: * @return a byte array.
235: */
236: public static byte[] getBinaryFromDb(int id) throws Exception {
237: Connection con = DriverManager
238: .getConnection("jdbc:default:connection");
239: Statement stmt = con.createStatement();
240: ResultSet rs = stmt
241: .executeQuery("SELECT BINARYDATA FROM CSDATA "
242: + "WHERE ID = " + id);
243: rs.next();
244: byte[] bytes = rs.getBytes(1);
245: rs.close();
246: stmt.close();
247: con.close();
248: return bytes;
249: }
250:
251: /**
252: * Function fetching character data from the database.
253: *
254: * @param id id of row to fetch.
255: * @return a string.
256: */
257: public static String getVarcharFromDb(int id) throws Exception {
258: Connection con = DriverManager
259: .getConnection("jdbc:default:connection");
260: Statement stmt = con.createStatement();
261: ResultSet rs = stmt.executeQuery("SELECT CHARDATA FROM CSDATA "
262: + "WHERE ID = " + id);
263: rs.next();
264: String chardata = rs.getString(1);
265: rs.close();
266: stmt.close();
267: con.close();
268: return chardata;
269: }
270:
271: }
|