001: // jTDS JDBC Driver for Microsoft SQL Server and Sybase
002: // Copyright (C) 2004 The jTDS Project
003: //
004: // This library is free software; you can redistribute it and/or
005: // modify it under the terms of the GNU Lesser General Public
006: // License as published by the Free Software Foundation; either
007: // version 2.1 of the License, or (at your option) any later version.
008: //
009: // This library is distributed in the hope that it will be useful,
010: // but WITHOUT ANY WARRANTY; without even the implied warranty of
011: // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
012: // Lesser General Public License for more details.
013: //
014: // You should have received a copy of the GNU Lesser General Public
015: // License along with this library; if not, write to the Free Software
016: // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
017: //
018: package net.sourceforge.jtds.test;
019:
020: import java.sql.*;
021: import java.math.BigDecimal;
022:
023: //
024: // MJH - Changes for new jTDS version
025: // Added registerOutParameter to testCallableStatementParsing2
026: //
027: /**
028: * @version 1.0
029: */
030: public class CallableStatementTest extends TestBase {
031: public CallableStatementTest(String name) {
032: super (name);
033: }
034:
035: public void testCallableStatement() throws Exception {
036: CallableStatement cstmt = con.prepareCall("{call sp_who}");
037:
038: cstmt.close();
039: }
040:
041: public void testCallableStatement1() throws Exception {
042: CallableStatement cstmt = con.prepareCall("sp_who");
043:
044: ResultSet rs = cstmt.executeQuery();
045: dump(rs);
046:
047: rs.close();
048: cstmt.close();
049: }
050:
051: public void testCallableStatementCall1() throws Exception {
052: CallableStatement cstmt = con.prepareCall("{call sp_who}");
053:
054: ResultSet rs = cstmt.executeQuery();
055: dump(rs);
056:
057: rs.close();
058: cstmt.close();
059: }
060:
061: public void testCallableStatementCall2() throws Exception {
062: CallableStatement cstmt = con.prepareCall("{CALL sp_who}");
063:
064: ResultSet rs = cstmt.executeQuery();
065: dump(rs);
066:
067: rs.close();
068: cstmt.close();
069: }
070:
071: public void testCallableStatementCall3() throws Exception {
072: CallableStatement cstmt = con.prepareCall("{cAlL sp_who}");
073:
074: ResultSet rs = cstmt.executeQuery();
075: dump(rs);
076:
077: rs.close();
078: cstmt.close();
079: }
080:
081: /**
082: * Test for bug [974801] stored procedure error in Northwind
083: */
084: public void testCallableStatementCall4() throws Exception {
085: Statement stmt;
086:
087: try {
088: stmt = con.createStatement();
089: stmt
090: .execute("create procedure \"#test space\" as SELECT COUNT(*) FROM sysobjects");
091: stmt.close();
092:
093: CallableStatement cstmt = con
094: .prepareCall("{call \"#test space\"}");
095:
096: ResultSet rs = cstmt.executeQuery();
097: dump(rs);
098:
099: rs.close();
100: cstmt.close();
101: } finally {
102: stmt = con.createStatement();
103: stmt.execute("drop procedure \"#test space\"");
104: stmt.close();
105: }
106: }
107:
108: public void testCallableStatementExec1() throws Exception {
109: CallableStatement cstmt = con.prepareCall("exec sp_who");
110:
111: ResultSet rs = cstmt.executeQuery();
112: dump(rs);
113:
114: rs.close();
115: cstmt.close();
116: }
117:
118: public void testCallableStatementExec2() throws Exception {
119: CallableStatement cstmt = con.prepareCall("EXEC sp_who");
120:
121: ResultSet rs = cstmt.executeQuery();
122: dump(rs);
123:
124: rs.close();
125: cstmt.close();
126: }
127:
128: public void testCallableStatementExec3() throws Exception {
129: CallableStatement cstmt = con.prepareCall("execute sp_who");
130:
131: ResultSet rs = cstmt.executeQuery();
132: dump(rs);
133:
134: rs.close();
135: cstmt.close();
136: }
137:
138: public void testCallableStatementExec4() throws Exception {
139: CallableStatement cstmt = con.prepareCall("EXECUTE sp_who");
140:
141: ResultSet rs = cstmt.executeQuery();
142: dump(rs);
143:
144: rs.close();
145: cstmt.close();
146: }
147:
148: public void testCallableStatementExec5() throws Exception {
149: CallableStatement cstmt = con.prepareCall("eXeC sp_who");
150:
151: ResultSet rs = cstmt.executeQuery();
152: dump(rs);
153:
154: rs.close();
155: cstmt.close();
156: }
157:
158: public void testCallableStatementExec6() throws Exception {
159: CallableStatement cstmt = con.prepareCall("ExEcUtE sp_who");
160:
161: ResultSet rs = cstmt.executeQuery();
162: dump(rs);
163:
164: rs.close();
165: cstmt.close();
166: }
167:
168: public void testCallableStatementExec7() throws Exception {
169: CallableStatement cstmt = con
170: .prepareCall("execute \"master\"..sp_who");
171:
172: ResultSet rs = cstmt.executeQuery();
173: dump(rs);
174:
175: rs.close();
176: cstmt.close();
177: }
178:
179: public void testCallableStatementExec8() throws Exception {
180: Statement stmt;
181:
182: try {
183: stmt = con.createStatement();
184: stmt
185: .execute("create procedure #test as SELECT COUNT(*) FROM sysobjects");
186: stmt.close();
187:
188: CallableStatement cstmt = con.prepareCall("execute #test");
189:
190: ResultSet rs = cstmt.executeQuery();
191: dump(rs);
192:
193: rs.close();
194: cstmt.close();
195: } finally {
196: stmt = con.createStatement();
197: stmt.execute("drop procedure #test");
198: stmt.close();
199: }
200: }
201:
202: /**
203: * Test for bug [978175] 0.8: Stored Procedure call doesn't work anymore
204: */
205: public void testCallableStatementExec9() throws Exception {
206: CallableStatement cstmt = con.prepareCall("{call sp_who}");
207:
208: assertTrue(cstmt.execute());
209:
210: ResultSet rs = cstmt.getResultSet();
211:
212: if (rs == null) {
213: fail("Null ResultSet returned");
214: } else {
215: dump(rs);
216: rs.close();
217: }
218:
219: cstmt.close();
220: }
221:
222: public void testCallableStatementParsing1() throws Exception {
223: String data = "New {order} plus {1} more";
224: Statement stmt = con.createStatement();
225:
226: stmt.execute("CREATE TABLE #csp1 (data VARCHAR(32))");
227: stmt.close();
228:
229: stmt = con.createStatement();
230: stmt
231: .execute("create procedure #sp_csp1 @data VARCHAR(32) as INSERT INTO #csp1 (data) VALUES(@data)");
232: stmt.close();
233:
234: CallableStatement cstmt = con.prepareCall("{call #sp_csp1(?)}");
235:
236: cstmt.setString(1, data);
237: cstmt.execute();
238: cstmt.close();
239:
240: stmt = con.createStatement();
241: ResultSet rs = stmt.executeQuery("SELECT data FROM #csp1");
242:
243: assertTrue(rs.next());
244:
245: assertTrue(data.equals(rs.getString(1)));
246:
247: assertTrue(!rs.next());
248: rs.close();
249: stmt.close();
250: }
251:
252: /**
253: * Test for bug [938632] String index out of bounds error in 0.8rc1.
254: */
255: public void testCallableStatementParsing2() throws Exception {
256: try {
257: Statement stmt = con.createStatement();
258:
259: stmt
260: .execute("create procedure #load_smtp_in_1gr_ls804192 as SELECT name FROM sysobjects");
261: stmt.close();
262:
263: CallableStatement cstmt = con
264: .prepareCall("{?=call #load_smtp_in_1gr_ls804192}");
265: cstmt.registerOutParameter(1, java.sql.Types.INTEGER); // MJH 01/05/04
266: cstmt.execute();
267: cstmt.close();
268: } finally {
269: Statement stmt = con.createStatement();
270:
271: stmt.execute("drop procedure #load_smtp_in_1gr_ls804192");
272: stmt.close();
273: }
274: }
275:
276: /**
277: * Test for bug [1006845] Stored procedure with 18 parameters.
278: */
279: public void testCallableStatementParsing3() throws Exception {
280: CallableStatement cstmt = con
281: .prepareCall("{Call Test(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
282: cstmt.close();
283: }
284:
285: /**
286: * Test for incorrect exception thrown/no exception thrown when invalid
287: * call escape is used.
288: * <p/>
289: * See https://sourceforge.net/forum/forum.php?thread_id=1144619&forum_id=104389
290: * for more detail.
291: */
292: public void testCallableStatementParsing4() throws SQLException {
293: try {
294: con
295: .prepareCall("{call ? = sp_create_employee (?, ?, ?, ?, ?, ?)}");
296: fail("Was expecting an invalid escape sequence error");
297: } catch (SQLException ex) {
298: assertEquals("22025", ex.getSQLState());
299: }
300: }
301:
302: /**
303: * Test for bug [1052942] Error processing JDBC call escape. (A blank
304: * before the final <code>}</code> causes the parser to fail).
305: */
306: public void testCallableStatementParsing5() throws Exception {
307: CallableStatement cstmt = con
308: .prepareCall(" { Call Test(?,?) } ");
309: cstmt.close();
310: }
311:
312: /**
313: * Test for incorrect exception thrown/no exception thrown when invalid
314: * call escape is used.
315: * <p/>
316: * A message containing the correct missing terminator should be generated.
317: */
318: public void testCallableStatementParsing6() throws SQLException {
319: try {
320: con.prepareCall("{call sp_test(?, ?)");
321: fail("Was expecting an invalid escape error");
322: } catch (SQLException ex) {
323: assertEquals("22025", ex.getSQLState());
324: assertTrue(ex.getMessage().indexOf('}') != -1);
325: }
326: }
327:
328: /**
329: * Test for incorrect exception thrown/no exception thrown when invalid
330: * call escape is used.
331: * <p/>
332: * A message containing the correct missing terminator should be generated.
333: */
334: public void testCallableStatementParsing7() throws SQLException {
335: try {
336: con.prepareCall("{call sp_test(?, ?}");
337: fail("Was expecting an invalid escape error");
338: } catch (SQLException ex) {
339: assertEquals("22025", ex.getSQLState());
340: assertTrue(ex.getMessage().indexOf(')') != -1);
341: }
342: }
343:
344: /**
345: * Test for reature request [956800] setNull(): Not implemented.
346: */
347: public void testCallableSetNull1() throws Exception {
348: Statement stmt = con.createStatement();
349: stmt
350: .execute("CREATE TABLE #callablesetnull1 (data CHAR(1) NULL)");
351: stmt.close();
352:
353: try {
354: stmt = con.createStatement();
355: stmt
356: .execute("create procedure #procCallableSetNull1 @data char(1) "
357: + "as INSERT INTO #callablesetnull1 (data) VALUES (@data)");
358: stmt.close();
359:
360: CallableStatement cstmt = con
361: .prepareCall("{call #procCallableSetNull1(?)}");
362: // Test CallableStatement.setNull(int,Types.NULL)
363: cstmt.setNull(1, Types.NULL);
364: cstmt.execute();
365: cstmt.close();
366:
367: stmt = con.createStatement();
368: ResultSet rs = stmt
369: .executeQuery("SELECT data FROM #callablesetnull1");
370:
371: assertTrue(rs.next());
372:
373: // Test ResultSet.getString()
374: assertNull(rs.getString(1));
375: assertTrue(rs.wasNull());
376:
377: assertTrue(!rs.next());
378: stmt.close();
379: rs.close();
380: } finally {
381: stmt = con.createStatement();
382: stmt.execute("drop procedure #procCallableSetNull1");
383: stmt.close();
384: }
385: }
386:
387: /**
388: * Test for bug [974284] retval on callable statement isn't handled correctly
389: */
390: public void testCallableRegisterOutParameter1() throws Exception {
391: Statement stmt = con.createStatement();
392: stmt
393: .execute("create procedure #rop1 @a varchar(1), @b varchar(1) as\r\n "
394: + "begin\r\n" + "return 1\r\n" + "end");
395: stmt.close();
396:
397: CallableStatement cstmt = con
398: .prepareCall("{? = call #rop1(?, ?)}");
399:
400: cstmt.registerOutParameter(1, Types.INTEGER);
401: cstmt.setString(2, "a");
402: cstmt.setString(3, "b");
403: cstmt.execute();
404:
405: assertEquals(1, cstmt.getInt(1));
406: assertEquals("1", cstmt.getString(1));
407:
408: cstmt.close();
409: }
410:
411: /**
412: * Test for bug [994888] Callable statement and Float output parameter
413: */
414: public void testCallableRegisterOutParameter2() throws Exception {
415: Statement stmt = con.createStatement();
416: stmt
417: .execute("create procedure #rop2 @data float OUTPUT as\r\n "
418: + "begin\r\n" + "set @data = 1.1\r\n" + "end");
419: stmt.close();
420:
421: CallableStatement cstmt = con.prepareCall("{call #rop2(?)}");
422:
423: cstmt.registerOutParameter(1, Types.FLOAT);
424: cstmt.execute();
425:
426: assertTrue(cstmt.getFloat(1) == 1.1f);
427: cstmt.close();
428: }
429:
430: /**
431: * Test for bug [994988] Network error when null is returned via int output parm
432: */
433: public void testCallableRegisterOutParameter3() throws Exception {
434: Statement stmt = con.createStatement();
435: stmt.execute("create procedure #rop3 @data int OUTPUT as\r\n "
436: + "begin\r\n" + "set @data = null\r\n" + "end");
437: stmt.close();
438:
439: CallableStatement cstmt = con.prepareCall("{call #rop3(?)}");
440:
441: cstmt.registerOutParameter(1, Types.INTEGER);
442: cstmt.execute();
443:
444: cstmt.getInt(1);
445: assertTrue(cstmt.wasNull());
446: cstmt.close();
447: }
448:
449: /**
450: * Test for bug [983432] Prepared call doesn't work with jTDS 0.8
451: */
452: public void testCallableRegisterOutParameter4() throws Exception {
453: CallableStatement cstmt = con
454: .prepareCall("{call sp_addtype T_INTEGER, int, 'NULL'}");
455: Statement stmt = con.createStatement();
456:
457: try {
458: cstmt.execute();
459: cstmt.close();
460:
461: stmt
462: .execute("create procedure rop4 @data T_INTEGER OUTPUT as\r\n "
463: + "begin\r\n" + "set @data = 1\r\n" + "end");
464: stmt.close();
465:
466: cstmt = con.prepareCall("{call rop4(?)}");
467:
468: cstmt.registerOutParameter(1, Types.VARCHAR);
469: cstmt.execute();
470:
471: assertEquals(cstmt.getInt(1), 1);
472: assertTrue(!cstmt.wasNull());
473: cstmt.close();
474:
475: cstmt = con.prepareCall("rop4 ?");
476:
477: cstmt.registerOutParameter(1, Types.VARCHAR);
478: cstmt.execute();
479:
480: assertEquals(cstmt.getInt(1), 1);
481: assertTrue(!cstmt.wasNull());
482: cstmt.close();
483: } finally {
484: stmt = con.createStatement();
485: stmt.execute("drop procedure rop4");
486: stmt.close();
487:
488: cstmt = con.prepareCall("{call sp_droptype 'T_INTEGER'}");
489: cstmt.execute();
490: cstmt.close();
491: }
492: }
493:
494: /**
495: * Test for bug [991640] java.sql.Date error and RAISERROR problem
496: */
497: public void testCallableError1() throws Exception {
498: String text = "test message";
499:
500: Statement stmt = con.createStatement();
501: stmt.execute("create procedure #ce1 as\r\n " + "begin\r\n"
502: + "RAISERROR('" + text + "', 16, 1 )\r\n" + "end");
503: stmt.close();
504:
505: CallableStatement cstmt = con.prepareCall("{call #ce1}");
506:
507: try {
508: cstmt.execute();
509: assertTrue(false);
510: } catch (SQLException e) {
511: assertTrue(e.getMessage().equals(text));
512: }
513:
514: cstmt.close();
515: }
516:
517: /**
518: * Test that procedure outputs are available immediately for procedures
519: * that do not return ResultSets (i.e that update counts are cached).
520: */
521: public void testProcessUpdateCounts1() throws SQLException {
522: Statement stmt = con.createStatement();
523: assertFalse(stmt
524: .execute("CREATE TABLE #testProcessUpdateCounts1 (val INT)"));
525: assertFalse(stmt
526: .execute("CREATE PROCEDURE #procTestProcessUpdateCounts1"
527: + " @res INT OUT AS"
528: + " INSERT INTO #testProcessUpdateCounts1 VALUES (1)"
529: + " UPDATE #testProcessUpdateCounts1 SET val = 2"
530: + " INSERT INTO #testProcessUpdateCounts1 VALUES (1)"
531: + " UPDATE #testProcessUpdateCounts1 SET val = 3"
532: + " SET @res = 13" + " RETURN 14"));
533: stmt.close();
534:
535: CallableStatement cstmt = con
536: .prepareCall("{?=call #procTestProcessUpdateCounts1(?)}");
537: cstmt.registerOutParameter(1, Types.INTEGER);
538: cstmt.registerOutParameter(2, Types.INTEGER);
539:
540: assertFalse(cstmt.execute());
541: assertEquals(14, cstmt.getInt(1));
542: assertEquals(13, cstmt.getInt(2));
543:
544: assertEquals(1, cstmt.getUpdateCount()); // INSERT
545:
546: assertFalse(cstmt.getMoreResults());
547: assertEquals(1, cstmt.getUpdateCount()); // UPDATE
548:
549: assertFalse(cstmt.getMoreResults());
550: assertEquals(1, cstmt.getUpdateCount()); // INSERT
551:
552: assertFalse(cstmt.getMoreResults());
553: assertEquals(2, cstmt.getUpdateCount()); // UPDATE
554:
555: assertFalse(cstmt.getMoreResults());
556: assertEquals(-1, cstmt.getUpdateCount());
557:
558: cstmt.close();
559: }
560:
561: /**
562: * Test that procedure outputs are available immediately after processing
563: * the last ResultSet returned by the procedure (i.e that update counts
564: * are cached).
565: */
566: public void testProcessUpdateCounts2() throws SQLException {
567: Statement stmt = con.createStatement();
568: assertFalse(stmt
569: .execute("CREATE TABLE #testProcessUpdateCounts2 (val INT)"));
570: assertFalse(stmt
571: .execute("CREATE PROCEDURE #procTestProcessUpdateCounts2"
572: + " @res INT OUT AS"
573: + " INSERT INTO #testProcessUpdateCounts2 VALUES (1)"
574: + " UPDATE #testProcessUpdateCounts2 SET val = 2"
575: + " SELECT * FROM #testProcessUpdateCounts2"
576: + " INSERT INTO #testProcessUpdateCounts2 VALUES (1)"
577: + " UPDATE #testProcessUpdateCounts2 SET val = 3"
578: + " SET @res = 13" + " RETURN 14"));
579: stmt.close();
580:
581: CallableStatement cstmt = con
582: .prepareCall("{?=call #procTestProcessUpdateCounts2(?)}");
583: cstmt.registerOutParameter(1, Types.INTEGER);
584: cstmt.registerOutParameter(2, Types.INTEGER);
585:
586: assertFalse(cstmt.execute());
587: try {
588: assertEquals(14, cstmt.getInt(1));
589: assertEquals(13, cstmt.getInt(2));
590: // Don't fail the test if we got here. Another driver or a future
591: // version could cache all the results and obtain the output
592: // parameter values from the beginning.
593: } catch (SQLException ex) {
594: assertEquals("HY010", ex.getSQLState());
595: assertTrue(ex.getMessage().indexOf("getMoreResults()") >= 0);
596: }
597:
598: assertEquals(1, cstmt.getUpdateCount()); // INSERT
599:
600: assertFalse(cstmt.getMoreResults());
601: assertEquals(1, cstmt.getUpdateCount()); // UPDATE
602:
603: assertTrue(cstmt.getMoreResults()); // SELECT
604:
605: assertFalse(cstmt.getMoreResults());
606: assertEquals(14, cstmt.getInt(1));
607: assertEquals(13, cstmt.getInt(2));
608: assertEquals(1, cstmt.getUpdateCount()); // INSERT
609:
610: assertFalse(cstmt.getMoreResults());
611: assertEquals(2, cstmt.getUpdateCount()); // UPDATE
612:
613: assertFalse(cstmt.getMoreResults());
614: assertEquals(-1, cstmt.getUpdateCount());
615:
616: cstmt.close();
617: }
618:
619: /**
620: * Test that procedure outputs are available immediately after processing
621: * the last ResultSet returned by the procedure (i.e that update counts
622: * are cached) even if getMoreResults() is not called.
623: */
624: public void testProcessUpdateCounts3() throws SQLException {
625: Statement stmt = con.createStatement();
626: assertFalse(stmt
627: .execute("CREATE TABLE #testProcessUpdateCounts3 (val INT)"));
628: assertFalse(stmt
629: .execute("CREATE PROCEDURE #procTestProcessUpdateCounts3"
630: + " @res INT OUT AS"
631: + " INSERT INTO #testProcessUpdateCounts3 VALUES (1)"
632: + " UPDATE #testProcessUpdateCounts3 SET val = 2"
633: + " SELECT * FROM #testProcessUpdateCounts3"
634: + " INSERT INTO #testProcessUpdateCounts3 VALUES (1)"
635: + " UPDATE #testProcessUpdateCounts3 SET val = 3"
636: + " SET @res = 13" + " RETURN 14"));
637: stmt.close();
638:
639: CallableStatement cstmt = con
640: .prepareCall("{?=call #procTestProcessUpdateCounts3(?)}");
641: cstmt.registerOutParameter(1, Types.INTEGER);
642: cstmt.registerOutParameter(2, Types.INTEGER);
643:
644: assertFalse(cstmt.execute());
645: try {
646: assertEquals(14, cstmt.getInt(1));
647: assertEquals(13, cstmt.getInt(2));
648: // Don't fail the test if we got here. Another driver or a future
649: // version could cache all the results and obtain the output
650: // parameter values from the beginning.
651: } catch (SQLException ex) {
652: assertEquals("HY010", ex.getSQLState());
653: assertTrue(ex.getMessage().indexOf("getMoreResults()") >= 0);
654: }
655:
656: assertEquals(1, cstmt.getUpdateCount()); // INSERT
657:
658: assertFalse(cstmt.getMoreResults());
659: assertEquals(1, cstmt.getUpdateCount()); // UPDATE
660:
661: assertTrue(cstmt.getMoreResults()); // SELECT
662: ResultSet rs = cstmt.getResultSet();
663: assertNotNull(rs);
664: // Close the ResultSet; this should cache the following update counts
665: rs.close();
666:
667: assertEquals(14, cstmt.getInt(1));
668: assertEquals(13, cstmt.getInt(2));
669:
670: assertFalse(cstmt.getMoreResults());
671: assertEquals(1, cstmt.getUpdateCount()); // INSERT
672:
673: assertFalse(cstmt.getMoreResults());
674: assertEquals(2, cstmt.getUpdateCount()); // UPDATE
675:
676: assertFalse(cstmt.getMoreResults());
677: assertEquals(-1, cstmt.getUpdateCount());
678:
679: cstmt.close();
680: }
681:
682: /**
683: * Test that procedure outputs are available immediately after processing
684: * the last ResultSet returned by the procedure (i.e that update counts
685: * are cached) even if getMoreResults() and ResultSet.close() are not
686: * called.
687: */
688: public void testProcessUpdateCounts4() throws SQLException {
689: Statement stmt = con.createStatement();
690: assertFalse(stmt
691: .execute("CREATE TABLE #testProcessUpdateCounts4 (val INT)"));
692: assertFalse(stmt
693: .execute("CREATE PROCEDURE #procTestProcessUpdateCounts4"
694: + " @res INT OUT AS"
695: + " INSERT INTO #testProcessUpdateCounts4 VALUES (1)"
696: + " UPDATE #testProcessUpdateCounts4 SET val = 2"
697: + " SELECT * FROM #testProcessUpdateCounts4"
698: + " INSERT INTO #testProcessUpdateCounts4 VALUES (1)"
699: + " UPDATE #testProcessUpdateCounts4 SET val = 3"
700: + " SET @res = 13" + " RETURN 14"));
701: stmt.close();
702:
703: CallableStatement cstmt = con
704: .prepareCall("{?=call #procTestProcessUpdateCounts4(?)}");
705: cstmt.registerOutParameter(1, Types.INTEGER);
706: cstmt.registerOutParameter(2, Types.INTEGER);
707:
708: assertFalse(cstmt.execute());
709: try {
710: assertEquals(14, cstmt.getInt(1));
711: assertEquals(13, cstmt.getInt(2));
712: // Don't fail the test if we got here. Another driver or a future
713: // version could cache all the results and obtain the output
714: // parameter values from the beginning.
715: } catch (SQLException ex) {
716: assertEquals("HY010", ex.getSQLState());
717: assertTrue(ex.getMessage().indexOf("getMoreResults()") >= 0);
718: }
719:
720: assertEquals(1, cstmt.getUpdateCount()); // INSERT
721:
722: assertFalse(cstmt.getMoreResults());
723: assertEquals(1, cstmt.getUpdateCount()); // UPDATE
724:
725: assertTrue(cstmt.getMoreResults()); // SELECT
726: ResultSet rs = cstmt.getResultSet();
727: assertNotNull(rs);
728: // Process all rows; this should cache the following update counts
729: assertTrue(rs.next());
730: assertFalse(rs.next());
731:
732: assertEquals(14, cstmt.getInt(1));
733: assertEquals(13, cstmt.getInt(2));
734:
735: // Only close the ResultSet now
736: rs.close();
737:
738: assertFalse(cstmt.getMoreResults());
739: assertEquals(1, cstmt.getUpdateCount()); // INSERT
740:
741: assertFalse(cstmt.getMoreResults());
742: assertEquals(2, cstmt.getUpdateCount()); // UPDATE
743:
744: assertFalse(cstmt.getMoreResults());
745: assertEquals(-1, cstmt.getUpdateCount());
746:
747: cstmt.close();
748: }
749:
750: /**
751: * Test for bug [ 1062671 ] SQLParser unable to parse CONVERT(char,{ts ?},102)
752: */
753: public void testTsEscape() throws Exception {
754: Timestamp ts = Timestamp.valueOf("2004-01-01 23:56:56");
755: Statement stmt = con.createStatement();
756: assertFalse(stmt
757: .execute("CREATE TABLE #testTsEscape (val DATETIME)"));
758: PreparedStatement pstmt = con
759: .prepareStatement("INSERT INTO #testTsEscape VALUES({ts ?})");
760: pstmt.setTimestamp(1, ts);
761: assertEquals(1, pstmt.executeUpdate());
762: ResultSet rs = stmt.executeQuery("SELECT * FROM #testTsEscape");
763: assertTrue(rs.next());
764: assertEquals(ts, rs.getTimestamp(1));
765: }
766:
767: /**
768: * Test for separation of IN and INOUT/OUT parameter values
769: */
770: public void testInOutParameters() throws Exception {
771: Statement stmt = con.createStatement();
772: stmt
773: .execute("CREATE PROC #testInOut @in int, @out int output as SELECT @out = @out + @in");
774: CallableStatement cstmt = con
775: .prepareCall("{ call #testInOut ( ?,? ) }");
776: cstmt.setInt(1, 1);
777: cstmt.registerOutParameter(2, Types.INTEGER);
778: cstmt.setInt(2, 2);
779: cstmt.execute();
780: assertEquals(3, cstmt.getInt(2));
781: cstmt.execute();
782: assertEquals(3, cstmt.getInt(2));
783: }
784:
785: /**
786: * Test that procedure names containing semicolons are parsed correctly.
787: */
788: public void testSemicolonProcedures() throws Exception {
789: Statement stmt = con.createStatement();
790: stmt
791: .execute("CREATE PROC #testInOut @in int, @out int output as SELECT @out = @out + @in");
792: CallableStatement cstmt = con
793: .prepareCall("{call #testInOut;1(?,?)}");
794: cstmt.setInt(1, 1);
795: cstmt.registerOutParameter(2, Types.INTEGER);
796: cstmt.setInt(2, 2);
797: cstmt.execute();
798: assertEquals(3, cstmt.getInt(2));
799: cstmt.execute();
800: assertEquals(3, cstmt.getInt(2));
801: }
802:
803: /**
804: * Test that procedure calls with both literal parameters and parameterr
805: * markers are executed correctly (bug [1078927] Callable statement fails).
806: */
807: public void testNonRpcProc1() throws Exception {
808: Statement stmt = con.createStatement();
809: stmt
810: .execute("create proc #testsp1 @p1 int, @p2 int out as set @p2 = @p1");
811: stmt.close();
812:
813: CallableStatement cstmt = con
814: .prepareCall("{call #testsp1(100, ?)}");
815: cstmt.setInt(1, 1);
816: cstmt.execute();
817: cstmt.close();
818: }
819:
820: /**
821: * Test that procedure calls with both literal parameters and parameterr
822: * markers are executed correctly (bug [1078927] Callable statement fails).
823: */
824: public void testNonRpcProc2() throws Exception {
825: Statement stmt = con.createStatement();
826: stmt
827: .execute("create proc #testsp2 @p1 int, @p2 int as return 99");
828: stmt.close();
829:
830: CallableStatement cstmt = con
831: .prepareCall("{?=call #testsp2(100, ?)}");
832: cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
833: cstmt.setInt(2, 2);
834: cstmt.execute();
835: assertEquals(99, cstmt.getInt(1));
836: cstmt.close();
837: }
838:
839: /**
840: * Test for bug [1152329] Spurious output params assigned (TIMESTMP).
841: * <p/>
842: * If a stored procedure execute WRITETEXT or UPDATETEXT commands, spurious
843: * output parameter data is returned to the client. This additional data
844: * can be confused with the real output parameter data leading to an output
845: * string parameter returning the text ?TIMESTMP? on SQL Server 7+ or
846: * binary garbage on other servers.
847: */
848: public void testWritetext() throws Exception {
849: Statement stmt = con.createStatement();
850: stmt
851: .execute("create proc #testWritetext @p1 varchar(20) output as "
852: + "begin "
853: + "create table #test (id int, txt text) "
854: + "insert into #test (id, txt) values(1, '') "
855: + "declare @ptr binary(16) "
856: + "select @ptr = (select textptr(txt) from #test where id = 1) "
857: + "writetext #test.txt @ptr 'This is a test' "
858: + "select @p1 = 'done' " + "end");
859: stmt.close();
860:
861: CallableStatement cstmt = con
862: .prepareCall("{call #testWritetext(?)}");
863: cstmt.registerOutParameter(1, Types.VARCHAR);
864: cstmt.execute();
865: assertEquals("done", cstmt.getString(1));
866: cstmt.close();
867: }
868:
869: /**
870: * Test for bug [1047208] SQLException chaining not implemented correctly:
871: * checks that all errors are returned and that output variables are also
872: * returned.
873: */
874: public void testErrorOutputParams() throws Exception {
875: Statement stmt = con.createStatement();
876: stmt.execute("CREATE PROC #error_proc @p1 int out AS \r\n"
877: + "RAISERROR ('TEST EXCEPTION', 15, 1)\r\n"
878: + "SELECT @P1=100\r\n"
879: + "CREATE TABLE #DUMMY (id int)\r\n"
880: + "INSERT INTO #DUMMY VALUES(1)\r\n"
881: + "INSERT INTO #DUMMY VALUES(1)");
882: stmt.close();
883:
884: CallableStatement cstmt = con
885: .prepareCall("{call #error_proc(?)}");
886: cstmt.registerOutParameter(1, Types.INTEGER);
887: try {
888: cstmt.execute();
889: fail("Expecting exception");
890: } catch (SQLException e) {
891: assertEquals("TEST EXCEPTION", e.getMessage());
892: }
893: assertEquals(100, cstmt.getInt(1));
894: cstmt.close();
895: }
896:
897: /**
898: * Test for bug [1236078] Procedure doesn't get called for some BigDecimal
899: * values - invalid bug.
900: */
901: public void testBigDecimal() throws Exception {
902: Statement stmt = con.createStatement();
903: assertEquals(
904: 0,
905: stmt
906: .executeUpdate("CREATE TABLE #dec_test "
907: + "(ColumnVC varchar(50) NULL, ColumnDec decimal(18,4) NULL)"));
908: assertEquals(
909: 0,
910: stmt
911: .executeUpdate("CREATE PROCEDURE #dec_test2"
912: + "(@inVc varchar(32), @inBd decimal(18,4)) AS "
913: + "begin "
914: + "update #dec_test set columnvc = @inVc, columndec = @inBd "
915: + "end"));
916: assertEquals(
917: 1,
918: stmt
919: .executeUpdate("insert #dec_test (columnvc, columndec) values (null, null)"));
920: stmt.close();
921:
922: CallableStatement cstmt = con
923: .prepareCall("{call #dec_test2 (?,?)}");
924: cstmt.setString(1, "D: " + new java.util.Date());
925: cstmt.setBigDecimal(2, new BigDecimal("2.9E+7"));
926: assertEquals(1, cstmt.executeUpdate());
927: cstmt.close();
928: }
929:
930: /**
931: * Test that output result sets, return values and output parameters are
932: * correctly handled for a remote procedure call.
933: * To set up this test you will a local and remote server where the remote
934: * server allows logins from the local test server.
935: * Install the following stored procedure on the remote server:
936: *
937: * create proc jtds_remote @in varchar(16), @out varchar(32) output as
938: * begin
939: * select 'result set'
940: * set @out = 'Test ' + @in;
941: * return 1
942: * end
943: *
944: * Uncomment this test and amend the remoteserver name in the prepareCall
945: * statement below to be the actual name of your remote server.
946: *
947: * The TDS stream for this test will comprise a result set, a dummy return
948: * (0x79) value and then the actual return and output parameter (0xAC) records.
949: *
950: * This call will fail with jtds 1.1 as the dummy return value of 0 in the
951: * TDS stream will preempt the capture of the actual value 1. In addition the
952: * return value will be assigned to the output parameter and the actual output
953: * parameter value will be lost.
954: *
955: *
956: public void testRemoteCallWithResultSet() throws Exception {
957: CallableStatement cstmt = con.prepareCall(
958: "{?=call remoteserver.database.user.jtds_remote(?,?)}");
959: cstmt.registerOutParameter(1, Types.INTEGER);
960: cstmt.setString(2, "data");
961: cstmt.registerOutParameter(3, Types.VARCHAR);
962: cstmt.execute();
963: ResultSet rs = cstmt.getResultSet();
964: assertNotNull(rs);
965: assertTrue(rs.next());
966: assertEquals("result set", rs.getString(1));
967: assertFalse(rs.next());
968: rs.close();
969: assertEquals(1, cstmt.getInt(1));
970: assertEquals("Test data", cstmt.getString(3));
971: cstmt.close();
972: }
973: */
974:
975: public static void main(String[] args) {
976: junit.textui.TestRunner.run(CallableStatementTest.class);
977: }
978: }
|