|
1
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  ;
32 import java.sql.*;
33
34
41 public class CallableStatementTestSetup
42 extends BaseJDBCTestSetup {
43
44 private static final String SOURCECLASS = "org.apache.derbyTesting." +
45 "functionTests.tests.jdbc4.CallableStatementTestSetup.";
46
47
48 private static final String [] TABLE_DROPS = new String [] {
49 "CSDATA"};
50
51 private static final String [] FUNCTION_DROPS = new String [] {
52 "INT_TO_STRING", "GET_BINARY_DB", "GET_VARCHAR_DB"};
53
54 private static final String [] PROCEDURE_DROPS = new String [] {
55 "GET_BINARY_DIRECT"};
56
57
58 public static final int STRING_BYTES_ID = 1;
59
63 public static final String STRING_BYTES =
64 "This is a string, converted to bytes and inserted into the database";
65
66
67 public static final int SQL_NULL_ID = 2;
68
69
74 public CallableStatementTestSetup(Test test) {
75 super(test);
76 }
77
78 protected void setUp()
79 throws SQLException {
80 Connection con = getConnection();
81 Statement stmt = con.createStatement();
83 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 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 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 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 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 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 {
131 Connection con = getConnection();
132 Statement stmt = con.createStatement();
133 for (String function : FUNCTION_DROPS) {
135 stmt.execute("DROP FUNCTION " + function);
136 }
137 for (String procedure : PROCEDURE_DROPS) {
139 stmt.execute("DROP PROCEDURE " + procedure);
140 }
141 for (String table : TABLE_DROPS) {
143 stmt.execute("DROP TABLE " + table);
144 }
145 stmt.close();
146 super.tearDown();
147 }
148
149
151
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
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
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
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
215
221 public static void getBinaryDirect(String inputString, byte[][] outputByte) {
222 try {
223 outputByte[0] = inputString.getBytes("UTF-16BE");
224 } catch (java.io.UnsupportedEncodingException uee) {
225 outputByte[0] = new byte[0];
226 }
227 }
228
229
235 public static byte[] getBinaryFromDb(int id)
236 throws Exception {
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
255 public static String getVarcharFromDb(int id)
256 throws Exception {
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 chardata = rs.getString(1);
263 rs.close();
264 stmt.close();
265 con.close();
266 return chardata;
267 }
268
269 }
270 |
Popular Tags |
Java API By Example, From Geeks To Geeks.