KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > jdbc4 > CallableStatementTestSetup


1 /*
2  
3    Derby - Class CallableStatementTestSetup
4  
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to you under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11  
12       http://www.apache.org/licenses/LICENSE-2.0
13  
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19  
20  */

21
22 package org.apache.derbyTesting.functionTests.tests.jdbc4;
23
24 import junit.framework.Assert;
25 import junit.framework.Test;
26 import junit.extensions.TestSetup;
27
28 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
29 import org.apache.derbyTesting.junit.BaseJDBCTestSetup;
30
31 import java.io.UnsupportedEncodingException JavaDoc;
32 import java.sql.*;
33
34 /**
35  * Create the necessary tables, function and procedures for running the
36  * CallableStatement tests under JDK 1.6.
37  * Java methods used as functions and procedures are also implemented here,
38  * along with helper methods that returns CallableStatements for the various
39  * functions and procedures.
40  */

41 public class CallableStatementTestSetup
42     extends BaseJDBCTestSetup {
43
44     private static final String JavaDoc SOURCECLASS = "org.apache.derbyTesting." +
45         "functionTests.tests.jdbc4.CallableStatementTestSetup.";
46     
47     /** List of tables to drop on tear-down */
48     private static final String JavaDoc[] TABLE_DROPS = new String JavaDoc[] {
49         "CSDATA"};
50     /** List of functions to drop on tear-down. */
51     private static final String JavaDoc[] FUNCTION_DROPS = new String JavaDoc[] {
52         "INT_TO_STRING", "GET_BINARY_DB", "GET_VARCHAR_DB"};
53     /** List of procedures to drop on tear-down. */
54     private static final String JavaDoc[] PROCEDURE_DROPS = new String JavaDoc[] {
55         "GET_BINARY_DIRECT"};
56
57     /** Id for row with byte representation of a string. */
58     public static final int STRING_BYTES_ID = 1;
59     /**
60      * String converted to bytes in UTF-16BE representation.
61      * Note that the charset used matters, and for Derby it must be UTF-16BE.
62      */

63     public static final String JavaDoc STRING_BYTES =
64         "This is a string, converted to bytes and inserted into the database";
65
66     /** Id for row with SQL NULL values. */
67     public static final int SQL_NULL_ID = 2;
68     
69     /**
70      * Create a new test setup for the CallableStatementTest.
71      *
72      * @param test the test/suite to provide setup for.
73      */

74     public CallableStatementTestSetup(Test test) {
75         super(test);
76     }
77
78     protected void setUp()
79         throws SQLException {
80         Connection con = getConnection();
81         // Create the tables, functions and procedures we need.
82
Statement stmt = con.createStatement();
83         // Create table CSDATA and populate
84
stmt.execute("CREATE TABLE CSDATA (ID INT PRIMARY KEY," +
85                 "BINARYDATA VARCHAR(256) FOR BIT DATA, " +
86                 "CHARDATA VARCHAR(256))");
87         PreparedStatement pStmt =
88             con.prepareStatement("INSERT INTO CSDATA VALUES (?,?,?)");
89         pStmt.setInt(1, STRING_BYTES_ID);
90         try {
91             pStmt.setBytes(2, STRING_BYTES.getBytes("UTF-16BE"));
92         } catch (UnsupportedEncodingException JavaDoc uee) {
93            SQLException sqle = new SQLException(uee.getMessage());
94            sqle.initCause(uee);
95            throw sqle;
96         }
97         pStmt.setString(3, STRING_BYTES);
98         pStmt.execute();
99         pStmt.setInt(1, SQL_NULL_ID);
100         pStmt.setNull(2, Types.VARBINARY);
101         pStmt.setNull(3, Types.VARCHAR);
102         pStmt.execute();
103         pStmt.close();
104
105         // Create function INT_TO_STRING
106
stmt.execute("CREATE FUNCTION INT_TO_STRING(INTNUM INT) " +
107                 "RETURNS VARCHAR(10) " +
108                 "PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA " +
109                 "EXTERNAL NAME 'java.lang.Integer.toString'");
110         // Create procedure GET_BINARY_DIRECT
111
stmt.execute("CREATE PROCEDURE GET_BINARY_DIRECT(IN INSTRING " +
112                 "VARCHAR(40), OUT OUTBYTES VARCHAR(160) FOR BIT DATA) " +
113                 "DYNAMIC RESULT SETS 0 " +
114                 "PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA " +
115                 "EXTERNAL NAME '" + SOURCECLASS + "getBinaryDirect'");
116         // Create function GET_BINARY_DB
117
stmt.execute("CREATE FUNCTION GET_BINARY_DB(ID INT) " +
118                 "RETURNS VARCHAR(256) FOR BIT DATA " +
119                 "PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA " +
120                 "EXTERNAL NAME '" + SOURCECLASS + "getBinaryFromDb'");
121         // Create function GET_VARCHAR_DB
122
stmt.execute("CREATE FUNCTION GET_VARCHAR_DB(ID INT) " +
123                 "RETURNS VARCHAR(256) " +
124                 "PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA " +
125                 "EXTERNAL NAME '" + SOURCECLASS + "getVarcharFromDb'");
126         stmt.close();
127     }
128
129     protected void tearDown()
130         throws Exception JavaDoc {
131         Connection con = getConnection();
132         Statement stmt = con.createStatement();
133         // Drop functions
134
for (String JavaDoc function : FUNCTION_DROPS) {
135             stmt.execute("DROP FUNCTION " + function);
136         }
137         // Drop procedures
138
for (String JavaDoc procedure : PROCEDURE_DROPS) {
139             stmt.execute("DROP PROCEDURE " + procedure);
140         }
141         // Drop tables
142
for (String JavaDoc table : TABLE_DROPS) {
143             stmt.execute("DROP TABLE " + table);
144         }
145         stmt.close();
146         super.tearDown();
147     }
148
149     // Methods for getting CallableStatements
150

151     /**
152      * Return function converting an integer to a string.
153      * Parameter 1: output - String/VARCHAR
154      * Parameter 2: input - int/INT
155      */

156     public static CallableStatement getIntToStringFunction(Connection con)
157         throws SQLException {
158         Assert.assertNotNull("Connection cannot be null", con);
159         CallableStatement cStmt = con.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(Connection con)
171         throws SQLException {
172         Assert.assertNotNull("Connection cannot be null", con);
173         CallableStatement cStmt =
174             con.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(Connection con)
188         throws SQLException {
189         Assert.assertNotNull("Connection cannot be null", con);
190         CallableStatement cStmt =
191             con.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(Connection con)
205         throws SQLException {
206         Assert.assertNotNull("Connection cannot be null", con);
207         CallableStatement cStmt =
208             con.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 JavaDoc inputString, byte[][] outputByte) {
222         try {
223             outputByte[0] = inputString.getBytes("UTF-16BE");
224         } catch (java.io.UnsupportedEncodingException JavaDoc uee) {
225             outputByte[0] = new byte[0];
226         }
227     }
228     
229     /**
230      * Function fetching binary data from the database.
231      *
232      * @param id id of row to fetch.
233      * @return a byte array.
234      */

235     public static byte[] getBinaryFromDb(int id)
236         throws Exception JavaDoc {
237         Connection con = DriverManager.getConnection("jdbc:default:connection");
238         Statement stmt = con.createStatement();
239         ResultSet rs = stmt.executeQuery("SELECT BINARYDATA FROM CSDATA " +
240                 "WHERE ID = " + id);
241         rs.next();
242         byte[] bytes = rs.getBytes(1);
243         rs.close();
244         stmt.close();
245         con.close();
246         return bytes;
247     }
248     
249     /**
250      * Function fetching character data from the database.
251      *
252      * @param id id of row to fetch.
253      * @return a string.
254      */

255     public static String JavaDoc getVarcharFromDb(int id)
256         throws Exception JavaDoc {
257         Connection con = DriverManager.getConnection("jdbc:default:connection");
258         Statement stmt = con.createStatement();
259         ResultSet rs = stmt.executeQuery("SELECT CHARDATA FROM CSDATA " +
260                 "WHERE ID = " + id);
261         rs.next();
262         String JavaDoc chardata = rs.getString(1);
263         rs.close();
264         stmt.close();
265         con.close();
266         return chardata;
267     }
268
269 }
270
Popular Tags