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:
022: /**
023: * Test the JDBC 3.0 features of CallableStatement.
024: *
025: * @author Alin Sinpalean
026: * @created 04/07/2004
027: */
028: public class CallableStatementJDBC3Test extends TestBase {
029: public CallableStatementJDBC3Test(String name) {
030: super (name);
031: }
032:
033: /**
034: * Test named parameters.
035: */
036: public void testNamedParameters0001() throws Exception {
037: final String data = "New {order} plus {1} more";
038: final String outData = "test";
039: Statement stmt = con.createStatement();
040:
041: stmt.execute("CREATE TABLE #csn1 (data VARCHAR(32))");
042: stmt.close();
043:
044: stmt = con.createStatement();
045: stmt
046: .execute("create procedure #sp_csn1 @data VARCHAR(32) OUT as "
047: + "INSERT INTO #csn1 (data) VALUES(@data) "
048: + "SET @data = '" + outData + "'" + "RETURN 13");
049: stmt.close();
050:
051: CallableStatement cstmt = con
052: .prepareCall("{?=call #sp_csn1(?)}");
053:
054: cstmt.registerOutParameter("@return_status", Types.INTEGER);
055: cstmt.setString("@data", data);
056: cstmt.registerOutParameter("@data", Types.VARCHAR);
057: assertEquals(1, cstmt.executeUpdate());
058: assertFalse(cstmt.getMoreResults());
059: assertEquals(-1, cstmt.getUpdateCount());
060: assertEquals(outData, cstmt.getString("@data"));
061: cstmt.close();
062:
063: stmt = con.createStatement();
064: ResultSet rs = stmt.executeQuery("SELECT data FROM #csn1");
065:
066: assertTrue(rs.next());
067: assertEquals(data, rs.getString(1));
068: assertTrue(!rs.next());
069:
070: rs.close();
071: stmt.close();
072: }
073:
074: /**
075: * Test for bug [946171] null boolean in CallableStatement bug
076: */
077: public void testCallableRegisterOutParameter1() throws Exception {
078: Statement stmt = con.createStatement();
079: stmt
080: .execute("create procedure #rop1 @bool bit, @whatever int OUTPUT as\r\n "
081: + "begin\r\n" + "set @whatever = 1\r\n" + "end");
082: stmt.close();
083:
084: try {
085: CallableStatement cstmt = con
086: .prepareCall("{call #rop1(?,?)}");
087:
088: cstmt.setNull(1, Types.BOOLEAN);
089: cstmt.registerOutParameter(2, Types.INTEGER);
090: cstmt.execute();
091:
092: assertTrue(cstmt.getInt(2) == 1);
093: cstmt.close();
094: } finally {
095: stmt = con.createStatement();
096: stmt.execute("drop procedure #rop1");
097: stmt.close();
098: }
099: }
100:
101: /**
102: * Test for bug [992715] wasnull() always returns false
103: */
104: public void testCallableRegisterOutParameter2() throws Exception {
105: Statement stmt = con.createStatement();
106: stmt
107: .execute("create procedure #rop2 @bool bit, @whatever varchar(1) OUTPUT as\r\n "
108: + "begin\r\n"
109: + "set @whatever = null\r\n"
110: + "end");
111: stmt.close();
112:
113: CallableStatement cstmt = con.prepareCall("{call #rop2(?,?)}");
114:
115: cstmt.setNull(1, Types.BOOLEAN);
116: cstmt.registerOutParameter(2, Types.VARCHAR);
117: cstmt.execute();
118:
119: assertTrue(cstmt.getString(2) == null);
120: assertTrue(cstmt.wasNull());
121: cstmt.close();
122: }
123:
124: public static void main(String[] args) {
125: junit.textui.TestRunner.run(CallableStatementJDBC3Test.class);
126: }
127: }
|