001: /*
002: Copyright (C) 2002-2007 MySQL AB
003:
004: This program is free software; you can redistribute it and/or modify
005: it under the terms of version 2 of the GNU General Public License as
006: published by the Free Software Foundation.
007:
008: There are special exceptions to the terms and conditions of the GPL
009: as it is applied to this software. View the full text of the
010: exception in file EXCEPTIONS-CONNECTOR-J in the directory of this
011: software distribution.
012:
013: This program is distributed in the hope that it will be useful,
014: but WITHOUT ANY WARRANTY; without even the implied warranty of
015: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
016: GNU General Public License for more details.
017:
018: You should have received a copy of the GNU General Public License
019: along with this program; if not, write to the Free Software
020: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
021:
022:
023:
024: */
025: package testsuite.simple;
026:
027: import com.mysql.jdbc.SQLError;
028: import com.mysql.jdbc.log.StandardLogger;
029:
030: import testsuite.BaseTestCase;
031:
032: import java.sql.CallableStatement;
033: import java.sql.Connection;
034: import java.sql.ParameterMetaData;
035: import java.sql.ResultSet;
036: import java.sql.ResultSetMetaData;
037: import java.sql.SQLException;
038: import java.sql.Types;
039:
040: import java.util.Properties;
041:
042: /**
043: * Tests callable statement functionality.
044: *
045: * @author Mark Matthews
046: * @version $Id: CallableStatementTest.java,v 1.1.2.1 2005/05/13 18:58:37
047: * mmatthews Exp $
048: */
049: public class CallableStatementTest extends BaseTestCase {
050: /**
051: * DOCUMENT ME!
052: *
053: * @param name
054: */
055: public CallableStatementTest(String name) {
056: super (name);
057:
058: // TODO Auto-generated constructor stub
059: }
060:
061: /**
062: * Tests functioning of inout parameters
063: *
064: * @throws Exception
065: * if the test fails
066: */
067:
068: public void testInOutParams() throws Exception {
069: if (versionMeetsMinimum(5, 0)) {
070: CallableStatement storedProc = null;
071:
072: try {
073: this .stmt
074: .executeUpdate("DROP PROCEDURE IF EXISTS testInOutParam");
075: this .stmt
076: .executeUpdate("create procedure testInOutParam(IN p1 VARCHAR(255), INOUT p2 INT)\n"
077: + "begin\n"
078: + " DECLARE z INT;\n"
079: + "SET z = p2 + 1;\n"
080: + "SET p2 = z;\n"
081: + "SELECT p1;\n"
082: + "SELECT CONCAT('zyxw', p1);\n"
083: + "end\n");
084:
085: storedProc = this .conn
086: .prepareCall("{call testInOutParam(?, ?)}");
087:
088: storedProc.setString(1, "abcd");
089: storedProc.setInt(2, 4);
090: storedProc.registerOutParameter(2, Types.INTEGER);
091:
092: storedProc.execute();
093:
094: assertEquals(5, storedProc.getInt(2));
095: } finally {
096: this .stmt
097: .executeUpdate("DROP PROCEDURE IF EXISTS testInOutParam");
098: }
099: }
100: }
101:
102: public void testBatch() throws Exception {
103: if (versionMeetsMinimum(5, 0)) {
104: Connection batchedConn = null;
105:
106: try {
107: createTable("testBatchTable", "(field1 INT)");
108: createProcedure("testBatch", "(IN foo VARCHAR(15))\n"
109: + "begin\n"
110: + "INSERT INTO testBatchTable VALUES (foo);\n"
111: + "end\n");
112:
113: executeBatchedStoredProc(this .conn);
114:
115: batchedConn = getConnectionWithProps("rewriteBatchedStatements=true,profileSQL=true");
116:
117: StringBuffer outBuf = new StringBuffer();
118: StandardLogger.bufferedLog = outBuf;
119: executeBatchedStoredProc(batchedConn);
120: String[] log = outBuf.toString().split(";");
121: assertTrue(log.length > 20);
122: } finally {
123: StandardLogger.bufferedLog = null;
124:
125: closeMemberJDBCResources();
126:
127: if (batchedConn != null) {
128: batchedConn.close();
129: }
130: }
131: }
132: }
133:
134: private void executeBatchedStoredProc(Connection c)
135: throws Exception {
136: this .stmt.executeUpdate("TRUNCATE TABLE testBatchTable");
137:
138: CallableStatement storedProc = c
139: .prepareCall("{call testBatch(?)}");
140:
141: try {
142: int numBatches = 300;
143:
144: for (int i = 0; i < numBatches; i++) {
145: storedProc.setInt(1, i + 1);
146: storedProc.addBatch();
147: }
148:
149: int[] counts = storedProc.executeBatch();
150:
151: assertEquals(numBatches, counts.length);
152:
153: for (int i = 0; i < numBatches; i++) {
154: assertEquals(1, counts[i]);
155: }
156:
157: this .rs = this .stmt
158: .executeQuery("SELECT field1 FROM testBatchTable ORDER BY field1 ASC");
159:
160: for (int i = 0; i < numBatches; i++) {
161: assertTrue(this .rs.next());
162: assertEquals(i + 1, this .rs.getInt(1));
163: }
164: } finally {
165: closeMemberJDBCResources();
166:
167: if (storedProc != null) {
168: storedProc.close();
169: }
170: }
171: }
172:
173: /**
174: * Tests functioning of output parameters.
175: *
176: * @throws Exception
177: * if the test fails.
178: */
179: public void testOutParams() throws Exception {
180: if (versionMeetsMinimum(5, 0)) {
181: CallableStatement storedProc = null;
182:
183: try {
184: this .stmt
185: .executeUpdate("DROP PROCEDURE IF EXISTS testOutParam");
186: this .stmt
187: .executeUpdate("CREATE PROCEDURE testOutParam(x int, out y int)\n"
188: + "begin\n"
189: + "declare z int;\n"
190: + "set z = x+1, y = z;\n" + "end\n");
191:
192: storedProc = this .conn
193: .prepareCall("{call testOutParam(?, ?)}");
194:
195: storedProc.setInt(1, 5);
196: storedProc.registerOutParameter(2, Types.INTEGER);
197:
198: storedProc.execute();
199:
200: System.out.println(storedProc);
201:
202: int indexedOutParamToTest = storedProc.getInt(2);
203:
204: if (!isRunningOnJdk131()) {
205: int namedOutParamToTest = storedProc.getInt("y");
206:
207: assertTrue(
208: "Named and indexed parameter are not the same",
209: indexedOutParamToTest == namedOutParamToTest);
210: assertTrue("Output value not returned correctly",
211: indexedOutParamToTest == 6);
212:
213: // Start over, using named parameters, this time
214: storedProc.clearParameters();
215: storedProc.setInt("x", 32);
216: storedProc.registerOutParameter("y", Types.INTEGER);
217:
218: storedProc.execute();
219:
220: indexedOutParamToTest = storedProc.getInt(2);
221: namedOutParamToTest = storedProc.getInt("y");
222:
223: assertTrue(
224: "Named and indexed parameter are not the same",
225: indexedOutParamToTest == namedOutParamToTest);
226: assertTrue("Output value not returned correctly",
227: indexedOutParamToTest == 33);
228:
229: try {
230: storedProc.registerOutParameter("x",
231: Types.INTEGER);
232: assertTrue(
233: "Should not be able to register an out parameter on a non-out parameter",
234: true);
235: } catch (SQLException sqlEx) {
236: if (!SQLError.SQL_STATE_ILLEGAL_ARGUMENT
237: .equals(sqlEx.getSQLState())) {
238: throw sqlEx;
239: }
240: }
241:
242: try {
243: storedProc.getInt("x");
244: assertTrue(
245: "Should not be able to retreive an out parameter on a non-out parameter",
246: true);
247: } catch (SQLException sqlEx) {
248: if (!SQLError.SQL_STATE_COLUMN_NOT_FOUND
249: .equals(sqlEx.getSQLState())) {
250: throw sqlEx;
251: }
252: }
253: }
254:
255: try {
256: storedProc.registerOutParameter(1, Types.INTEGER);
257: assertTrue(
258: "Should not be able to register an out parameter on a non-out parameter",
259: true);
260: } catch (SQLException sqlEx) {
261: if (!SQLError.SQL_STATE_ILLEGAL_ARGUMENT
262: .equals(sqlEx.getSQLState())) {
263: throw sqlEx;
264: }
265: }
266: } finally {
267: this .stmt.executeUpdate("DROP PROCEDURE testOutParam");
268: }
269: }
270: }
271:
272: /**
273: * Tests functioning of output parameters.
274: *
275: * @throws Exception
276: * if the test fails.
277: */
278: public void testResultSet() throws Exception {
279: if (versionMeetsMinimum(5, 0)) {
280: CallableStatement storedProc = null;
281:
282: try {
283: this .stmt
284: .executeUpdate("DROP TABLE IF EXISTS testSpResultTbl1");
285: this .stmt
286: .executeUpdate("DROP TABLE IF EXISTS testSpResultTbl2");
287: this .stmt
288: .executeUpdate("CREATE TABLE testSpResultTbl1 (field1 INT)");
289: this .stmt
290: .executeUpdate("INSERT INTO testSpResultTbl1 VALUES (1), (2)");
291: this .stmt
292: .executeUpdate("CREATE TABLE testSpResultTbl2 (field2 varchar(255))");
293: this .stmt
294: .executeUpdate("INSERT INTO testSpResultTbl2 VALUES ('abc'), ('def')");
295:
296: this .stmt
297: .executeUpdate("DROP PROCEDURE IF EXISTS testSpResult");
298: this .stmt
299: .executeUpdate("CREATE PROCEDURE testSpResult()\n"
300: + "BEGIN\n"
301: + "SELECT field2 FROM testSpResultTbl2 WHERE field2='abc';\n"
302: + "UPDATE testSpResultTbl1 SET field1=2;\n"
303: + "SELECT field2 FROM testSpResultTbl2 WHERE field2='def';\n"
304: + "end\n");
305:
306: storedProc = this .conn
307: .prepareCall("{call testSpResult()}");
308:
309: storedProc.execute();
310:
311: this .rs = storedProc.getResultSet();
312:
313: ResultSetMetaData rsmd = this .rs.getMetaData();
314:
315: assertTrue(rsmd.getColumnCount() == 1);
316: assertTrue("field2".equals(rsmd.getColumnName(1)));
317: assertTrue(rsmd.getColumnType(1) == Types.VARCHAR);
318:
319: assertTrue(this .rs.next());
320:
321: assertTrue("abc".equals(this .rs.getString(1)));
322:
323: // TODO: This does not yet work in MySQL 5.0
324: // assertTrue(!storedProc.getMoreResults());
325: // assertTrue(storedProc.getUpdateCount() == 2);
326: assertTrue(storedProc.getMoreResults());
327:
328: ResultSet nextResultSet = storedProc.getResultSet();
329:
330: rsmd = nextResultSet.getMetaData();
331:
332: assertTrue(rsmd.getColumnCount() == 1);
333: assertTrue("field2".equals(rsmd.getColumnName(1)));
334: assertTrue(rsmd.getColumnType(1) == Types.VARCHAR);
335:
336: assertTrue(nextResultSet.next());
337:
338: assertTrue("def".equals(nextResultSet.getString(1)));
339:
340: nextResultSet.close();
341:
342: this .rs.close();
343:
344: storedProc.execute();
345:
346: } finally {
347: this .stmt
348: .executeUpdate("DROP PROCEDURE IF EXISTS testSpResult");
349: this .stmt
350: .executeUpdate("DROP TABLE IF EXISTS testSpResultTbl1");
351: this .stmt
352: .executeUpdate("DROP TABLE IF EXISTS testSpResultTbl2");
353: }
354: }
355: }
356:
357: /**
358: * Tests parsing of stored procedures
359: *
360: * @throws Exception
361: * if an error occurs.
362: */
363: public void testSPParse() throws Exception {
364:
365: if (versionMeetsMinimum(5, 0)) {
366:
367: CallableStatement storedProc = null;
368:
369: try {
370:
371: this .stmt
372: .executeUpdate("DROP PROCEDURE IF EXISTS testSpParse");
373: this .stmt
374: .executeUpdate("CREATE PROCEDURE testSpParse(IN FOO VARCHAR(15))\n"
375: + "BEGIN\n" + "SELECT 1;\n" + "end\n");
376:
377: storedProc = this .conn
378: .prepareCall("{call testSpParse()}");
379:
380: } finally {
381: this .stmt
382: .executeUpdate("DROP PROCEDURE IF EXISTS testSpParse");
383: }
384: }
385: }
386:
387: /**
388: * Tests parsing/execution of stored procedures with no parameters...
389: *
390: * @throws Exception
391: * if an error occurs.
392: */
393: public void testSPNoParams() throws Exception {
394:
395: if (versionMeetsMinimum(5, 0)) {
396:
397: CallableStatement storedProc = null;
398:
399: try {
400:
401: this .stmt
402: .executeUpdate("DROP PROCEDURE IF EXISTS testSPNoParams");
403: this .stmt
404: .executeUpdate("CREATE PROCEDURE testSPNoParams()\n"
405: + "BEGIN\n" + "SELECT 1;\n" + "end\n");
406:
407: storedProc = this .conn
408: .prepareCall("{call testSPNoParams()}");
409: storedProc.execute();
410:
411: } finally {
412: this .stmt
413: .executeUpdate("DROP PROCEDURE IF EXISTS testSPNoParams");
414: }
415: }
416: }
417:
418: /**
419: * Tests parsing of stored procedures
420: *
421: * @throws Exception
422: * if an error occurs.
423: */
424: public void testSPCache() throws Exception {
425: if (isRunningOnJdk131()) {
426: return; // no support for LRUCache
427: }
428:
429: if (versionMeetsMinimum(5, 0)) {
430:
431: CallableStatement storedProc = null;
432:
433: try {
434:
435: this .stmt
436: .executeUpdate("DROP PROCEDURE IF EXISTS testSpParse");
437: this .stmt
438: .executeUpdate("CREATE PROCEDURE testSpParse(IN FOO VARCHAR(15))\n"
439: + "BEGIN\n" + "SELECT 1;\n" + "end\n");
440:
441: int numIterations = 10;
442:
443: long startTime = System.currentTimeMillis();
444:
445: for (int i = 0; i < numIterations; i++) {
446: storedProc = this .conn
447: .prepareCall("{call testSpParse(?)}");
448: storedProc.close();
449: }
450:
451: long elapsedTime = System.currentTimeMillis()
452: - startTime;
453:
454: System.out.println("Standard parsing/execution: "
455: + elapsedTime + " ms");
456:
457: storedProc = this .conn
458: .prepareCall("{call testSpParse(?)}");
459: storedProc.setString(1, "abc");
460: this .rs = storedProc.executeQuery();
461:
462: assertTrue(this .rs.next());
463: assertTrue(this .rs.getInt(1) == 1);
464:
465: Properties props = new Properties();
466: props.setProperty("cacheCallableStmts", "true");
467:
468: Connection cachedSpConn = getConnectionWithProps(props);
469:
470: startTime = System.currentTimeMillis();
471:
472: for (int i = 0; i < numIterations; i++) {
473: storedProc = cachedSpConn
474: .prepareCall("{call testSpParse(?)}");
475: storedProc.close();
476: }
477:
478: elapsedTime = System.currentTimeMillis() - startTime;
479:
480: System.out.println("Cached parse stage: " + elapsedTime
481: + " ms");
482:
483: storedProc = cachedSpConn
484: .prepareCall("{call testSpParse(?)}");
485: storedProc.setString(1, "abc");
486: this .rs = storedProc.executeQuery();
487:
488: assertTrue(this .rs.next());
489: assertTrue(this .rs.getInt(1) == 1);
490:
491: } finally {
492: this .stmt
493: .executeUpdate("DROP PROCEDURE IF EXISTS testSpParse");
494: }
495: }
496: }
497:
498: public void testOutParamsNoBodies() throws Exception {
499: if (versionMeetsMinimum(5, 0)) {
500: CallableStatement storedProc = null;
501:
502: Properties props = new Properties();
503: props.setProperty("noAccessToProcedureBodies", "true");
504:
505: Connection spConn = getConnectionWithProps(props);
506:
507: try {
508: this .stmt
509: .executeUpdate("DROP PROCEDURE IF EXISTS testOutParam");
510: this .stmt
511: .executeUpdate("CREATE PROCEDURE testOutParam(x int, out y int)\n"
512: + "begin\n"
513: + "declare z int;\n"
514: + "set z = x+1, y = z;\n" + "end\n");
515:
516: storedProc = spConn
517: .prepareCall("{call testOutParam(?, ?)}");
518:
519: storedProc.setInt(1, 5);
520: storedProc.registerOutParameter(2, Types.INTEGER);
521:
522: storedProc.execute();
523:
524: int indexedOutParamToTest = storedProc.getInt(2);
525:
526: assertTrue("Output value not returned correctly",
527: indexedOutParamToTest == 6);
528:
529: storedProc.clearParameters();
530: storedProc.setInt(1, 32);
531: storedProc.registerOutParameter(2, Types.INTEGER);
532:
533: storedProc.execute();
534:
535: indexedOutParamToTest = storedProc.getInt(2);
536:
537: assertTrue("Output value not returned correctly",
538: indexedOutParamToTest == 33);
539: } finally {
540: this .stmt.executeUpdate("DROP PROCEDURE testOutParam");
541: }
542: }
543: }
544:
545: /**
546: * Runs all test cases in this test suite
547: *
548: * @param args
549: */
550: public static void main(String[] args) {
551: junit.textui.TestRunner.run(CallableStatementTest.class);
552: }
553:
554: /** Tests the new parameter parser that doesn't require "BEGIN" or "\n" at end
555: * of parameter declaration
556: * @throws Exception
557: */
558: public void testParameterParser() throws Exception {
559:
560: if (!versionMeetsMinimum(5, 0)) {
561: return;
562: }
563:
564: CallableStatement cstmt = null;
565:
566: try {
567:
568: createTable("t1",
569: "(id char(16) not null default '', data int not null)");
570: createTable("t2", "(s char(16), i int, d double)");
571:
572: createProcedure("foo42",
573: "() insert into test.t1 values ('foo', 42);");
574: this .conn.prepareCall("{CALL foo42()}");
575: this .conn.prepareCall("{CALL foo42}");
576:
577: createProcedure("bar",
578: "(x char(16), y int, z DECIMAL(10)) insert into test.t1 values (x, y);");
579: cstmt = this .conn.prepareCall("{CALL bar(?, ?, ?)}");
580:
581: if (!isRunningOnJdk131()) {
582: ParameterMetaData md = cstmt.getParameterMetaData();
583: assertEquals(3, md.getParameterCount());
584: assertEquals(Types.CHAR, md.getParameterType(1));
585: assertEquals(Types.INTEGER, md.getParameterType(2));
586: assertEquals(Types.DECIMAL, md.getParameterType(3));
587: }
588:
589: createProcedure("p",
590: "() label1: WHILE @a=0 DO SET @a=1; END WHILE");
591: this .conn.prepareCall("{CALL p()}");
592:
593: createFunction("f", "() RETURNS INT return 1; ");
594: cstmt = this .conn.prepareCall("{? = CALL f()}");
595:
596: if (!isRunningOnJdk131()) {
597: ParameterMetaData md = cstmt.getParameterMetaData();
598: assertEquals(Types.INTEGER, md.getParameterType(1));
599: }
600: } finally {
601: if (cstmt != null) {
602: cstmt.close();
603: }
604: }
605: }
606: }
|