0001: /*
0002: Copyright (C) 2002-2007 MySQL AB
0003:
0004: This program is free software; you can redistribute it and/or modify
0005: it under the terms of version 2 of the GNU General Public License as
0006: published by the Free Software Foundation.
0007:
0008: There are special exceptions to the terms and conditions of the GPL
0009: as it is applied to this software. View the full text of the
0010: exception in file EXCEPTIONS-CONNECTOR-J in the directory of this
0011: software distribution.
0012:
0013: This program is distributed in the hope that it will be useful,
0014: but WITHOUT ANY WARRANTY; without even the implied warranty of
0015: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
0016: GNU General Public License for more details.
0017:
0018: You should have received a copy of the GNU General Public License
0019: along with this program; if not, write to the Free Software
0020: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
0021:
0022:
0023:
0024: */
0025: package testsuite.regression;
0026:
0027: import java.io.ByteArrayInputStream;
0028: import java.io.ByteArrayOutputStream;
0029: import java.io.InputStream;
0030: import java.sql.CallableStatement;
0031: import java.sql.Connection;
0032: import java.sql.PreparedStatement;
0033: import java.sql.ResultSetMetaData;
0034: import java.sql.SQLException;
0035: import java.sql.Types;
0036: import java.util.List;
0037: import java.util.Properties;
0038:
0039: import com.mysql.jdbc.DatabaseMetaData;
0040: import com.mysql.jdbc.NonRegisteringDriver;
0041: import com.mysql.jdbc.SQLError;
0042: import com.mysql.jdbc.StringUtils;
0043:
0044: import testsuite.BaseTestCase;
0045:
0046: /**
0047: * Tests fixes for bugs in CallableStatement code.
0048: *
0049: * @version $Id: CallableStatementRegressionTest.java,v 1.1.2.6 2004/12/09
0050: * 15:57:26 mmatthew Exp $
0051: */
0052: public class CallableStatementRegressionTest extends BaseTestCase {
0053: /**
0054: * DOCUMENT ME!
0055: *
0056: * @param name
0057: */
0058: public CallableStatementRegressionTest(String name) {
0059: super (name);
0060:
0061: // TODO Auto-generated constructor stub
0062: }
0063:
0064: /**
0065: * Runs all test cases in this test suite
0066: *
0067: * @param args
0068: * ignored
0069: */
0070: public static void main(String[] args) {
0071: junit.textui.TestRunner
0072: .run(CallableStatementRegressionTest.class);
0073: }
0074:
0075: /**
0076: * Tests fix for BUG#3539 getProcedures() does not return any procedures in
0077: * result set
0078: *
0079: * @throws Exception
0080: * if an error occurs.
0081: */
0082: public void testBug3539() throws Exception {
0083: if (!serverSupportsStoredProcedures()) {
0084: return;
0085: }
0086:
0087: try {
0088: this .stmt
0089: .executeUpdate("DROP PROCEDURE IF EXISTS testBug3539");
0090: this .stmt.executeUpdate("CREATE PROCEDURE testBug3539()\n"
0091: + "BEGIN\n" + "SELECT 1;" + "end\n");
0092:
0093: this .rs = this .conn.getMetaData().getProcedures(null, null,
0094: "testBug3539");
0095:
0096: assertTrue(this .rs.next());
0097: assertTrue("testBug3539".equals(this .rs.getString(3)));
0098: } finally {
0099: this .stmt
0100: .executeUpdate("DROP PROCEDURE IF EXISTS testBug3539");
0101: }
0102: }
0103:
0104: /**
0105: * Tests fix for BUG#3540 getProcedureColumns doesn't work with wildcards
0106: * for procedure name
0107: *
0108: * @throws Exception
0109: * if an error occurs.
0110: */
0111: public void testBug3540() throws Exception {
0112: if (!serverSupportsStoredProcedures()) {
0113: return;
0114: }
0115: try {
0116: this .stmt
0117: .executeUpdate("DROP PROCEDURE IF EXISTS testBug3540");
0118: this .stmt
0119: .executeUpdate("CREATE PROCEDURE testBug3540(x int, out y int)\n"
0120: + "BEGIN\n" + "SELECT 1;" + "end\n");
0121:
0122: this .rs = this .conn.getMetaData().getProcedureColumns(null,
0123: null, "testBug3540%", "%");
0124:
0125: assertTrue(this .rs.next());
0126: assertTrue("testBug3540".equals(this .rs.getString(3)));
0127: assertTrue("x".equals(this .rs.getString(4)));
0128:
0129: assertTrue(this .rs.next());
0130: assertTrue("testBug3540".equals(this .rs.getString(3)));
0131: assertTrue("y".equals(this .rs.getString(4)));
0132:
0133: assertTrue(!this .rs.next());
0134: } finally {
0135: this .stmt
0136: .executeUpdate("DROP PROCEDURE IF EXISTS testBug3540");
0137: }
0138: }
0139:
0140: /**
0141: * Tests fix for BUG#7026 - DBMD.getProcedures() doesn't respect catalog
0142: * parameter
0143: *
0144: * @throws Exception
0145: * if the test fails.
0146: */
0147: public void testBug7026() throws Exception {
0148: if (!serverSupportsStoredProcedures()) {
0149: return;
0150: }
0151:
0152: try {
0153: this .stmt
0154: .executeUpdate("DROP PROCEDURE IF EXISTS testBug7026");
0155: this .stmt
0156: .executeUpdate("CREATE PROCEDURE testBug7026(x int, out y int)\n"
0157: + "BEGIN\n" + "SELECT 1;" + "end\n");
0158:
0159: //
0160: // Should be found this time.
0161: //
0162: this .rs = this .conn.getMetaData().getProcedures(
0163: this .conn.getCatalog(), null, "testBug7026");
0164:
0165: assertTrue(this .rs.next());
0166: assertTrue("testBug7026".equals(this .rs.getString(3)));
0167:
0168: assertTrue(!this .rs.next());
0169:
0170: //
0171: // This time, shouldn't be found, because not associated with
0172: // this (bogus) catalog
0173: //
0174: this .rs = this .conn.getMetaData().getProcedures("abfgerfg",
0175: null, "testBug7026");
0176: assertTrue(!this .rs.next());
0177:
0178: //
0179: // Should be found this time as well, as we haven't
0180: // specified a catalog.
0181: //
0182: this .rs = this .conn.getMetaData().getProcedures(null, null,
0183: "testBug7026");
0184:
0185: assertTrue(this .rs.next());
0186: assertTrue("testBug7026".equals(this .rs.getString(3)));
0187:
0188: assertTrue(!this .rs.next());
0189: } finally {
0190: this .stmt
0191: .executeUpdate("DROP PROCEDURE IF EXISTS testBug7026");
0192: }
0193: }
0194:
0195: /**
0196: * Tests fix for BUG#9319 -- Stored procedures with same name in different
0197: * databases confuse the driver when it tries to determine parameter
0198: * counts/types.
0199: *
0200: * @throws Exception
0201: * if the test fails
0202: */
0203: public void testBug9319() throws Exception {
0204: if (!serverSupportsStoredProcedures()) {
0205: return;
0206: }
0207:
0208: boolean doASelect = true; // SELECT currently causes the server to
0209: // hang on the
0210: // last execution of this testcase, filed as BUG#9405
0211:
0212: if (isAdminConnectionConfigured()) {
0213: Connection db2Connection = null;
0214: Connection db1Connection = null;
0215:
0216: try {
0217: db2Connection = getAdminConnection();
0218: db1Connection = getAdminConnection();
0219:
0220: db2Connection.createStatement().executeUpdate(
0221: "CREATE DATABASE IF NOT EXISTS db_9319_2");
0222: db2Connection.setCatalog("db_9319_2");
0223:
0224: db2Connection.createStatement().executeUpdate(
0225: "DROP PROCEDURE IF EXISTS COMPROVAR_USUARI");
0226:
0227: db2Connection
0228: .createStatement()
0229: .executeUpdate(
0230: "CREATE PROCEDURE COMPROVAR_USUARI(IN p_CodiUsuari VARCHAR(10),"
0231: + "\nIN p_contrasenya VARCHAR(10),"
0232: + "\nOUT p_userId INTEGER,"
0233: + "\nOUT p_userName VARCHAR(30),"
0234: + "\nOUT p_administrador VARCHAR(1),"
0235: + "\nOUT p_idioma VARCHAR(2))"
0236: + "\nBEGIN"
0237:
0238: + (doASelect ? "\nselect 2;"
0239: : "\nSELECT 2 INTO p_administrador;")
0240: + "\nEND");
0241:
0242: db1Connection.createStatement().executeUpdate(
0243: "CREATE DATABASE IF NOT EXISTS db_9319_1");
0244: db1Connection.setCatalog("db_9319_1");
0245:
0246: db1Connection.createStatement().executeUpdate(
0247: "DROP PROCEDURE IF EXISTS COMPROVAR_USUARI");
0248: db1Connection
0249: .createStatement()
0250: .executeUpdate(
0251: "CREATE PROCEDURE COMPROVAR_USUARI(IN p_CodiUsuari VARCHAR(10),"
0252: + "\nIN p_contrasenya VARCHAR(10),"
0253: + "\nOUT p_userId INTEGER,"
0254: + "\nOUT p_userName VARCHAR(30),"
0255: + "\nOUT p_administrador VARCHAR(1))"
0256: + "\nBEGIN"
0257: + (doASelect ? "\nselect 1;"
0258: : "\nSELECT 1 INTO p_administrador;")
0259: + "\nEND");
0260:
0261: CallableStatement cstmt = db2Connection
0262: .prepareCall("{ call COMPROVAR_USUARI(?, ?, ?, ?, ?, ?) }");
0263: cstmt.setString(1, "abc");
0264: cstmt.setString(2, "def");
0265: cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
0266: cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
0267: cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
0268:
0269: cstmt.registerOutParameter(6, java.sql.Types.VARCHAR);
0270:
0271: cstmt.execute();
0272:
0273: if (doASelect) {
0274: this .rs = cstmt.getResultSet();
0275: assertTrue(this .rs.next());
0276: assertEquals(2, this .rs.getInt(1));
0277: } else {
0278: assertEquals(2, cstmt.getInt(5));
0279: }
0280:
0281: cstmt = db1Connection
0282: .prepareCall("{ call COMPROVAR_USUARI(?, ?, ?, ?, ?, ?) }");
0283: cstmt.setString(1, "abc");
0284: cstmt.setString(2, "def");
0285: cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
0286: cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
0287: cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
0288:
0289: try {
0290: cstmt.registerOutParameter(6,
0291: java.sql.Types.VARCHAR);
0292: fail("Should've thrown an exception");
0293: } catch (SQLException sqlEx) {
0294: assertEquals(SQLError.SQL_STATE_ILLEGAL_ARGUMENT,
0295: sqlEx.getSQLState());
0296: }
0297:
0298: cstmt = db1Connection
0299: .prepareCall("{ call COMPROVAR_USUARI(?, ?, ?, ?, ?) }");
0300: cstmt.setString(1, "abc");
0301: cstmt.setString(2, "def");
0302: cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
0303: cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
0304: cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
0305:
0306: cstmt.execute();
0307:
0308: if (doASelect) {
0309: this .rs = cstmt.getResultSet();
0310: assertTrue(this .rs.next());
0311: assertEquals(1, this .rs.getInt(1));
0312: } else {
0313: assertEquals(1, cstmt.getInt(5));
0314: }
0315:
0316: String quoteChar = db2Connection.getMetaData()
0317: .getIdentifierQuoteString();
0318:
0319: cstmt = db2Connection.prepareCall("{ call " + quoteChar
0320: + db1Connection.getCatalog() + quoteChar + "."
0321: + quoteChar + "COMPROVAR_USUARI" + quoteChar
0322: + "(?, ?, ?, ?, ?) }");
0323: cstmt.setString(1, "abc");
0324: cstmt.setString(2, "def");
0325: cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
0326: cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
0327: cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
0328:
0329: cstmt.execute();
0330:
0331: if (doASelect) {
0332: this .rs = cstmt.getResultSet();
0333: assertTrue(this .rs.next());
0334: assertEquals(1, this .rs.getInt(1));
0335: } else {
0336: assertEquals(1, cstmt.getInt(5));
0337: }
0338: } finally {
0339: if (db2Connection != null) {
0340: db2Connection
0341: .createStatement()
0342: .executeUpdate(
0343: "DROP PROCEDURE IF EXISTS COMPROVAR_USUARI");
0344: db2Connection.createStatement().executeUpdate(
0345: "DROP DATABASE IF EXISTS db_9319_2");
0346: }
0347:
0348: if (db1Connection != null) {
0349: db1Connection
0350: .createStatement()
0351: .executeUpdate(
0352: "DROP PROCEDURE IF EXISTS COMPROVAR_USUARI");
0353: db1Connection.createStatement().executeUpdate(
0354: "DROP DATABASE IF EXISTS db_9319_1");
0355: }
0356: }
0357: }
0358: }
0359:
0360: /*
0361: * public void testBug9319() throws Exception { boolean doASelect = false; //
0362: * SELECT currently causes the server to hang on the // last execution of
0363: * this testcase, filed as BUG#9405
0364: *
0365: * if (versionMeetsMinimum(5, 0, 2)) { if (isAdminConnectionConfigured()) {
0366: * Connection db2Connection = null; Connection db1Connection = null;
0367: *
0368: * try { db2Connection = getAdminConnection();
0369: *
0370: * db2Connection.createStatement().executeUpdate( "CREATE DATABASE IF NOT
0371: * EXISTS db_9319"); db2Connection.setCatalog("db_9319");
0372: *
0373: * db2Connection.createStatement().executeUpdate( "DROP PROCEDURE IF EXISTS
0374: * COMPROVAR_USUARI");
0375: *
0376: * db2Connection.createStatement().executeUpdate( "CREATE PROCEDURE
0377: * COMPROVAR_USUARI(IN p_CodiUsuari VARCHAR(10)," + "\nIN p_contrasenya
0378: * VARCHAR(10)," + "\nOUT p_userId INTEGER," + "\nOUT p_userName
0379: * VARCHAR(30)," + "\nOUT p_administrador VARCHAR(1)," + "\nOUT p_idioma
0380: * VARCHAR(2))" + "\nBEGIN" + (doASelect ? "\nselect 2;" : "\nSELECT 2 INTO
0381: * p_administrador;" ) + "\nEND");
0382: *
0383: * this.stmt .executeUpdate("DROP PROCEDURE IF EXISTS COMPROVAR_USUARI");
0384: * this.stmt .executeUpdate("CREATE PROCEDURE COMPROVAR_USUARI(IN
0385: * p_CodiUsuari VARCHAR(10)," + "\nIN p_contrasenya VARCHAR(10)," + "\nOUT
0386: * p_userId INTEGER," + "\nOUT p_userName VARCHAR(30)," + "\nOUT
0387: * p_administrador VARCHAR(1))" + "\nBEGIN" + (doASelect ? "\nselect 1;" :
0388: * "\nSELECT 1 INTO p_administrador;" ) + "\nEND");
0389: *
0390: * CallableStatement cstmt = db2Connection .prepareCall("{ call
0391: * COMPROVAR_USUARI(?, ?, ?, ?, ?, ?) }"); cstmt.setString(1, "abc");
0392: * cstmt.setString(2, "def"); cstmt.registerOutParameter(3,
0393: * java.sql.Types.INTEGER); cstmt.registerOutParameter(4,
0394: * java.sql.Types.VARCHAR); cstmt.registerOutParameter(5,
0395: * java.sql.Types.VARCHAR);
0396: *
0397: * cstmt.registerOutParameter(6, java.sql.Types.VARCHAR);
0398: *
0399: * cstmt.execute();
0400: *
0401: * if (doASelect) { this.rs = cstmt.getResultSet();
0402: * assertTrue(this.rs.next()); assertEquals(2, this.rs.getInt(1)); } else {
0403: * assertEquals(2, cstmt.getInt(5)); }
0404: *
0405: * cstmt = this.conn .prepareCall("{ call COMPROVAR_USUARI(?, ?, ?, ?, ?, ?)
0406: * }"); cstmt.setString(1, "abc"); cstmt.setString(2, "def");
0407: * cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
0408: * cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
0409: * cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
0410: *
0411: * try { cstmt.registerOutParameter(6, java.sql.Types.VARCHAR);
0412: * fail("Should've thrown an exception"); } catch (SQLException sqlEx) {
0413: * assertEquals(SQLError.SQL_STATE_ILLEGAL_ARGUMENT, sqlEx .getSQLState()); }
0414: *
0415: * cstmt = this.conn .prepareCall("{ call COMPROVAR_USUARI(?, ?, ?, ?, ?)
0416: * }"); cstmt.setString(1, "abc"); cstmt.setString(2, "def");
0417: * cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
0418: * cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
0419: * cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
0420: *
0421: * cstmt.execute();
0422: *
0423: * if (doASelect) { this.rs = cstmt.getResultSet();
0424: * assertTrue(this.rs.next()); assertEquals(1, this.rs.getInt(1)); } else {
0425: * assertEquals(1, cstmt.getInt(5)); }
0426: *
0427: * String quoteChar =
0428: * db2Connection.getMetaData().getIdentifierQuoteString();
0429: *
0430: * cstmt = db2Connection .prepareCall("{ call " + quoteChar +
0431: * this.conn.getCatalog() + quoteChar + "." + quoteChar + "COMPROVAR_USUARI" +
0432: * quoteChar + "(?, ?, ?, ?, ?) }"); cstmt.setString(1, "abc");
0433: * cstmt.setString(2, "def"); cstmt.registerOutParameter(3,
0434: * java.sql.Types.INTEGER); cstmt.registerOutParameter(4,
0435: * java.sql.Types.VARCHAR); cstmt.registerOutParameter(5,
0436: * java.sql.Types.VARCHAR);
0437: *
0438: * cstmt.execute();
0439: *
0440: * if (doASelect) { this.rs = cstmt.getResultSet();
0441: * assertTrue(this.rs.next()); assertEquals(1, this.rs.getInt(1)); } else {
0442: * assertEquals(1, cstmt.getInt(5)); } } finally { if (db2Connection !=
0443: * null) { db2Connection.createStatement().executeUpdate( "DROP PROCEDURE IF
0444: * EXISTS COMPROVAR_USUARI"); //
0445: * db2Connection.createStatement().executeUpdate( // "DROP DATABASE IF
0446: * EXISTS db_9319"); }
0447: *
0448: * this.stmt .executeUpdate("DROP PROCEDURE IF EXISTS COMPROVAR_USUARI"); } } } }
0449: */
0450:
0451: /**
0452: * Tests fix for BUG#9682 - Stored procedures with DECIMAL parameters with
0453: * storage specifications that contained "," in them would fail.
0454: *
0455: * @throws Exception
0456: * if the test fails.
0457: */
0458: public void testBug9682() throws Exception {
0459: if (!serverSupportsStoredProcedures()) {
0460: return;
0461: }
0462:
0463: CallableStatement cStmt = null;
0464:
0465: try {
0466: this .stmt
0467: .executeUpdate("DROP PROCEDURE IF EXISTS testBug9682");
0468: this .stmt
0469: .executeUpdate("CREATE PROCEDURE testBug9682(decimalParam DECIMAL(18,0))"
0470: + "\nBEGIN" + "\n SELECT 1;" + "\nEND");
0471: cStmt = this .conn.prepareCall("Call testBug9682(?)");
0472: cStmt.setDouble(1, 18.0);
0473: cStmt.execute();
0474: } finally {
0475: if (cStmt != null) {
0476: cStmt.close();
0477: }
0478:
0479: this .stmt
0480: .executeUpdate("DROP PROCEDURE IF EXISTS testBug9682");
0481: }
0482: }
0483:
0484: /**
0485: * Tests fix forBUG#10310 - Driver doesn't support {?=CALL(...)} for calling
0486: * stored functions. This involved adding support for function retrieval to
0487: * DatabaseMetaData.getProcedures() and getProcedureColumns() as well.
0488: *
0489: * @throws Exception
0490: * if the test fails.
0491: */
0492: public void testBug10310() throws Exception {
0493: if (!serverSupportsStoredProcedures()) {
0494: return;
0495: }
0496:
0497: CallableStatement cStmt = null;
0498:
0499: try {
0500: this .stmt
0501: .executeUpdate("DROP FUNCTION IF EXISTS testBug10310");
0502: this .stmt
0503: .executeUpdate("CREATE FUNCTION testBug10310(a float, b bigint, c int) RETURNS INT"
0504: + "\nBEGIN" + "\nRETURN a;" + "\nEND");
0505: cStmt = this .conn
0506: .prepareCall("{? = CALL testBug10310(?,?,?)}");
0507: cStmt.registerOutParameter(1, Types.INTEGER);
0508: cStmt.setFloat(2, 2);
0509: cStmt.setInt(3, 1);
0510: cStmt.setInt(4, 1);
0511:
0512: if (!isRunningOnJdk131()) {
0513: assertEquals(4, cStmt.getParameterMetaData()
0514: .getParameterCount());
0515: assertEquals(Types.INTEGER, cStmt
0516: .getParameterMetaData().getParameterType(1));
0517: }
0518:
0519: assertFalse(cStmt.execute());
0520: assertEquals(2f, cStmt.getInt(1), .001);
0521: assertEquals("java.lang.Integer", cStmt.getObject(1)
0522: .getClass().getName());
0523:
0524: assertEquals(-1, cStmt.executeUpdate());
0525: assertEquals(2f, cStmt.getInt(1), .001);
0526: assertEquals("java.lang.Integer", cStmt.getObject(1)
0527: .getClass().getName());
0528:
0529: if (!isRunningOnJdk131()) {
0530: cStmt.setFloat("a", 4);
0531: cStmt.setInt("b", 1);
0532: cStmt.setInt("c", 1);
0533:
0534: assertFalse(cStmt.execute());
0535: assertEquals(4f, cStmt.getInt(1), .001);
0536: assertEquals("java.lang.Integer", cStmt.getObject(1)
0537: .getClass().getName());
0538:
0539: assertEquals(-1, cStmt.executeUpdate());
0540: assertEquals(4f, cStmt.getInt(1), .001);
0541: assertEquals("java.lang.Integer", cStmt.getObject(1)
0542: .getClass().getName());
0543: }
0544:
0545: // Check metadata while we're at it
0546:
0547: java.sql.DatabaseMetaData dbmd = this .conn.getMetaData();
0548:
0549: this .rs = dbmd.getProcedures(this .conn.getCatalog(), null,
0550: "testBug10310");
0551: this .rs.next();
0552: assertEquals("testBug10310", this .rs
0553: .getString("PROCEDURE_NAME"));
0554: assertEquals(DatabaseMetaData.procedureReturnsResult,
0555: this .rs.getShort("PROCEDURE_TYPE"));
0556: cStmt.setNull(2, Types.FLOAT);
0557: cStmt.setInt(3, 1);
0558: cStmt.setInt(4, 1);
0559:
0560: assertFalse(cStmt.execute());
0561: assertEquals(0f, cStmt.getInt(1), .001);
0562: assertEquals(true, cStmt.wasNull());
0563: assertEquals(null, cStmt.getObject(1));
0564: assertEquals(true, cStmt.wasNull());
0565:
0566: assertEquals(-1, cStmt.executeUpdate());
0567: assertEquals(0f, cStmt.getInt(1), .001);
0568: assertEquals(true, cStmt.wasNull());
0569: assertEquals(null, cStmt.getObject(1));
0570: assertEquals(true, cStmt.wasNull());
0571:
0572: // Check with literals, not all parameters filled!
0573: cStmt = this .conn
0574: .prepareCall("{? = CALL testBug10310(4,5,?)}");
0575: cStmt.registerOutParameter(1, Types.INTEGER);
0576: cStmt.setInt(2, 1);
0577:
0578: assertFalse(cStmt.execute());
0579: assertEquals(4f, cStmt.getInt(1), .001);
0580: assertEquals("java.lang.Integer", cStmt.getObject(1)
0581: .getClass().getName());
0582:
0583: assertEquals(-1, cStmt.executeUpdate());
0584: assertEquals(4f, cStmt.getInt(1), .001);
0585: assertEquals("java.lang.Integer", cStmt.getObject(1)
0586: .getClass().getName());
0587:
0588: if (!isRunningOnJdk131()) {
0589: assertEquals(2, cStmt.getParameterMetaData()
0590: .getParameterCount());
0591: assertEquals(Types.INTEGER, cStmt
0592: .getParameterMetaData().getParameterType(1));
0593: assertEquals(Types.INTEGER, cStmt
0594: .getParameterMetaData().getParameterType(2));
0595: }
0596: } finally {
0597: if (this .rs != null) {
0598: this .rs.close();
0599: this .rs = null;
0600: }
0601:
0602: if (cStmt != null) {
0603: cStmt.close();
0604: }
0605:
0606: this .stmt
0607: .executeUpdate("DROP FUNCTION IF EXISTS testBug10310");
0608: }
0609: }
0610:
0611: /**
0612: * Tests fix for Bug#12417 - stored procedure catalog name is case-sensitive
0613: * on Windows (this is actually a server bug, but we have a workaround in
0614: * place for it now).
0615: *
0616: * @throws Exception
0617: * if the test fails.
0618: */
0619: public void testBug12417() throws Exception {
0620: if (serverSupportsStoredProcedures()
0621: && isServerRunningOnWindows()) {
0622: Connection ucCatalogConn = null;
0623:
0624: try {
0625: this .stmt
0626: .executeUpdate("DROP PROCEDURE IF EXISTS testBug12417");
0627: this .stmt
0628: .executeUpdate("CREATE PROCEDURE testBug12417()\n"
0629: + "BEGIN\n" + "SELECT 1;" + "end\n");
0630: ucCatalogConn = getConnectionWithProps((Properties) null);
0631: ucCatalogConn.setCatalog(this .conn.getCatalog()
0632: .toUpperCase());
0633: ucCatalogConn.prepareCall("{call testBug12417()}");
0634: } finally {
0635: this .stmt
0636: .executeUpdate("DROP PROCEDURE IF EXISTS testBug3539");
0637:
0638: if (ucCatalogConn != null) {
0639: ucCatalogConn.close();
0640: }
0641: }
0642: }
0643: }
0644:
0645: public void testBug15121() throws Exception {
0646: if (false /* needs to be fixed on server */) {
0647: if (versionMeetsMinimum(5, 0)) {
0648: this .stmt
0649: .executeUpdate("DROP PROCEDURE IF EXISTS p_testBug15121");
0650:
0651: this .stmt
0652: .executeUpdate("CREATE PROCEDURE p_testBug15121()\n"
0653: + "BEGIN\n"
0654: + "SELECT * from idonotexist;\n"
0655: + "END");
0656:
0657: Properties props = new Properties();
0658: props.setProperty(
0659: NonRegisteringDriver.DBNAME_PROPERTY_KEY, "");
0660:
0661: Connection noDbConn = null;
0662:
0663: try {
0664: noDbConn = getConnectionWithProps(props);
0665:
0666: StringBuffer queryBuf = new StringBuffer("{call ");
0667: String quotedId = this .conn.getMetaData()
0668: .getIdentifierQuoteString();
0669: queryBuf.append(quotedId);
0670: queryBuf.append(this .conn.getCatalog());
0671: queryBuf.append(quotedId);
0672: queryBuf.append(".p_testBug15121()}");
0673:
0674: noDbConn.prepareCall(queryBuf.toString()).execute();
0675: } finally {
0676: if (noDbConn != null) {
0677: noDbConn.close();
0678: }
0679: }
0680: }
0681: }
0682: }
0683:
0684: /**
0685: * Tests fix for BUG#15464 - INOUT parameter does not store IN value.
0686: *
0687: * @throws Exception
0688: * if the test fails
0689: */
0690:
0691: public void testBug15464() throws Exception {
0692: if (!serverSupportsStoredProcedures()) {
0693: return;
0694: }
0695: CallableStatement storedProc = null;
0696:
0697: try {
0698: this .stmt
0699: .executeUpdate("DROP PROCEDURE IF EXISTS testInOutParam");
0700: this .stmt
0701: .executeUpdate("create procedure testInOutParam(IN p1 VARCHAR(255), INOUT p2 INT)\n"
0702: + "begin\n"
0703: + " DECLARE z INT;\n"
0704: + "SET z = p2 + 1;\n"
0705: + "SET p2 = z;\n"
0706: + "SELECT p1;\n"
0707: + "SELECT CONCAT('zyxw', p1);\n" + "end\n");
0708:
0709: storedProc = this .conn
0710: .prepareCall("{call testInOutParam(?, ?)}");
0711:
0712: storedProc.setString(1, "abcd");
0713: storedProc.setInt(2, 4);
0714: storedProc.registerOutParameter(2, Types.INTEGER);
0715:
0716: storedProc.execute();
0717:
0718: assertEquals(5, storedProc.getInt(2));
0719: } finally {
0720: this .stmt
0721: .executeUpdate("DROP PROCEDURE IF EXISTS testInOutParam");
0722: }
0723: }
0724:
0725: /**
0726: * Tests fix for BUG#17898 - registerOutParameter not working when some
0727: * parameters pre-populated. Still waiting for feedback from JDBC experts
0728: * group to determine what correct parameter count from getMetaData() should
0729: * be, however.
0730: *
0731: * @throws Exception
0732: * if the test fails
0733: */
0734: public void testBug17898() throws Exception {
0735: if (!serverSupportsStoredProcedures()) {
0736: return;
0737: }
0738:
0739: this .stmt
0740: .executeUpdate("DROP PROCEDURE IF EXISTS testBug17898");
0741: this .stmt
0742: .executeUpdate("CREATE PROCEDURE testBug17898(param1 VARCHAR(50), OUT param2 INT)\nBEGIN\nDECLARE rtn INT;\nSELECT 1 INTO rtn;\nSET param2=rtn;\nEND");
0743:
0744: CallableStatement cstmt = this .conn
0745: .prepareCall("{CALL testBug17898('foo', ?)}");
0746: cstmt.registerOutParameter(1, Types.INTEGER);
0747: cstmt.execute();
0748: assertEquals(1, cstmt.getInt(1));
0749:
0750: if (!isRunningOnJdk131()) {
0751: cstmt.clearParameters();
0752: cstmt.registerOutParameter("param2", Types.INTEGER);
0753: cstmt.execute();
0754: assertEquals(1, cstmt.getInt(1));
0755: }
0756:
0757: }
0758:
0759: /**
0760: * Tests fix for BUG#21462 - JDBC (and ODBC) specifications allow no-parenthesis
0761: * CALL statements for procedures with no arguments, MySQL server does not.
0762: *
0763: * @throws Exception if the test fails.
0764: */
0765: public void testBug21462() throws Exception {
0766: if (!serverSupportsStoredProcedures()) {
0767: return;
0768: }
0769:
0770: CallableStatement cstmt = null;
0771:
0772: try {
0773: this .stmt
0774: .executeUpdate("DROP PROCEDURE IF EXISTS testBug21462");
0775: this .stmt
0776: .executeUpdate("CREATE PROCEDURE testBug21462() BEGIN SELECT 1; END");
0777: cstmt = this .conn.prepareCall("{CALL testBug21462}");
0778: cstmt.execute();
0779: } finally {
0780: if (cstmt != null) {
0781: cstmt.close();
0782: }
0783:
0784: this .stmt
0785: .executeUpdate("DROP PROCEDURE IF EXISTS testBug21462");
0786: }
0787:
0788: }
0789:
0790: /**
0791: * Tests fix for BUG#22024 - Newlines causing whitespace to span confuse
0792: * procedure parser when getting parameter metadata for stored procedures.
0793: *
0794: * @throws Exception if the test fails
0795: */
0796: public void testBug22024() throws Exception {
0797: if (!serverSupportsStoredProcedures()) {
0798: return;
0799: }
0800:
0801: CallableStatement cstmt = null;
0802:
0803: try {
0804: this .stmt
0805: .executeUpdate("DROP PROCEDURE IF EXISTS testBug22024");
0806: this .stmt
0807: .executeUpdate("CREATE PROCEDURE testBug22024(\r\n)\r\n BEGIN SELECT 1; END");
0808: cstmt = this .conn.prepareCall("{CALL testBug22024()}");
0809: cstmt.execute();
0810:
0811: this .stmt
0812: .executeUpdate("DROP PROCEDURE IF EXISTS testBug22024");
0813: this .stmt
0814: .executeUpdate("CREATE PROCEDURE testBug22024(\r\na INT)\r\n BEGIN SELECT 1; END");
0815: cstmt = this .conn.prepareCall("{CALL testBug22024(?)}");
0816: cstmt.setInt(1, 1);
0817: cstmt.execute();
0818: } finally {
0819: if (cstmt != null) {
0820: cstmt.close();
0821: }
0822:
0823: this .stmt
0824: .executeUpdate("DROP PROCEDURE IF EXISTS testBug22024");
0825: }
0826:
0827: }
0828:
0829: /**
0830: * Tests workaround for server crash when calling stored procedures
0831: * via a server-side prepared statement (driver now detects
0832: * prepare(stored procedure) and substitutes client-side prepared statement).
0833: *
0834: * @throws Exception if the test fails
0835: */
0836: public void testBug22297() throws Exception {
0837: if (!serverSupportsStoredProcedures()) {
0838: return;
0839: }
0840:
0841: this .stmt
0842: .executeUpdate("DROP PROCEDURE IF EXISTS testBug22297");
0843:
0844: createTable("tblTestBug2297_1", "("
0845: + "id varchar(20) NOT NULL default '',"
0846: + "Income double(19,2) default NULL)");
0847:
0848: createTable("tblTestBug2297_2", "("
0849: + "id varchar(20) NOT NULL default '',"
0850: + "CreatedOn datetime default NULL)");
0851:
0852: this .stmt
0853: .executeUpdate("CREATE PROCEDURE testBug22297(pcaseid INT)"
0854: + "BEGIN"
0855: + "\nSET @sql = \"DROP TEMPORARY TABLE IF EXISTS tmpOrders\";"
0856: + " PREPARE stmt FROM @sql;"
0857: + " EXECUTE stmt;"
0858: + " DEALLOCATE PREPARE stmt;"
0859: + "\nSET @sql = \"CREATE TEMPORARY TABLE tmpOrders SELECT id, 100 AS Income FROM tblTestBug2297_1 GROUP BY id\";"
0860: + " PREPARE stmt FROM @sql;"
0861: + " EXECUTE stmt;"
0862: + " DEALLOCATE PREPARE stmt;"
0863: + "\n SELECT id, Income FROM (SELECT e.id AS id ,COALESCE(prof.Income,0) AS Income"
0864: + "\n FROM tblTestBug2297_2 e LEFT JOIN tmpOrders prof ON e.id = prof.id"
0865: + "\n WHERE e.CreatedOn > '2006-08-01') AS Final ORDER BY id;"
0866: + "\nEND");
0867:
0868: this .stmt
0869: .executeUpdate("INSERT INTO tblTestBug2297_1 (`id`,`Income`) VALUES "
0870: + "('a',4094.00),"
0871: + "('b',500.00),"
0872: + "('c',3462.17),"
0873: + " ('d',500.00),"
0874: + " ('e',600.00)");
0875:
0876: this .stmt
0877: .executeUpdate("INSERT INTO tblTestBug2297_2 (`id`,`CreatedOn`) VALUES "
0878: + "('d','2006-08-31 00:00:00'),"
0879: + "('e','2006-08-31 00:00:00'),"
0880: + "('b','2006-08-31 00:00:00'),"
0881: + "('c','2006-08-31 00:00:00'),"
0882: + "('a','2006-08-31 00:00:00')");
0883:
0884: try {
0885: this .pstmt = this .conn
0886: .prepareStatement("{CALL testBug22297(?)}");
0887: this .pstmt.setInt(1, 1);
0888: this .rs = this .pstmt.executeQuery();
0889:
0890: String[] ids = new String[] { "a", "b", "c", "d", "e" };
0891: int pos = 0;
0892:
0893: while (this .rs.next()) {
0894: assertEquals(ids[pos++], rs.getString(1));
0895: assertEquals(100, rs.getInt(2));
0896: }
0897:
0898: assertTrue(this .pstmt.getClass().getName()
0899: .indexOf("Server") == -1);
0900: } finally {
0901: closeMemberJDBCResources();
0902: }
0903:
0904: }
0905:
0906: public void testHugeNumberOfParameters() throws Exception {
0907: if (!serverSupportsStoredProcedures()) {
0908: return;
0909: }
0910:
0911: this .stmt
0912: .executeUpdate("DROP PROCEDURE IF EXISTS testHugeNumberOfParameters");
0913:
0914: StringBuffer procDef = new StringBuffer(
0915: "CREATE PROCEDURE testHugeNumberOfParameters(");
0916:
0917: for (int i = 0; i < 274; i++) {
0918: if (i != 0) {
0919: procDef.append(",");
0920: }
0921:
0922: procDef.append(" OUT param_" + i + " VARCHAR(32)");
0923: }
0924:
0925: procDef.append(")\nBEGIN\nSELECT 1;\nEND");
0926: this .stmt.executeUpdate(procDef.toString());
0927:
0928: CallableStatement cStmt = null;
0929:
0930: try {
0931: cStmt = this .conn
0932: .prepareCall("{call testHugeNumberOfParameters(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
0933: +
0934:
0935: "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
0936: + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
0937: + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
0938: + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
0939: + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
0940: + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
0941: cStmt.registerOutParameter(274, Types.VARCHAR);
0942:
0943: cStmt.execute();
0944: } finally {
0945: if (cStmt != null) {
0946: cStmt.close();
0947: }
0948: }
0949: }
0950:
0951: public void testPrepareOfMultiRs() throws Exception {
0952: if (!serverSupportsStoredProcedures()) {
0953: return;
0954: }
0955:
0956: this .stmt.executeUpdate("Drop procedure if exists p");
0957: this .stmt
0958: .executeUpdate("create procedure p () begin select 1; select 2; end;");
0959: PreparedStatement ps = null;
0960:
0961: try {
0962: ps = this .conn.prepareStatement("call p()");
0963:
0964: ps.execute();
0965: this .rs = ps.getResultSet();
0966: assertTrue(this .rs.next());
0967: assertEquals(1, this .rs.getInt(1));
0968: assertTrue(ps.getMoreResults());
0969: this .rs = ps.getResultSet();
0970: assertTrue(this .rs.next());
0971: assertEquals(2, this .rs.getInt(1));
0972: assertTrue(!ps.getMoreResults());
0973: } finally {
0974: if (this .rs != null) {
0975: this .rs.close();
0976: this .rs = null;
0977: }
0978:
0979: if (ps != null) {
0980: ps.close();
0981: }
0982: }
0983:
0984: }
0985:
0986: /**
0987: * Tests fix for BUG#25379 - INOUT parameters in CallableStatements get doubly-escaped.
0988: *
0989: * @throws Exception if the test fails.
0990: */
0991: public void testBug25379() throws Exception {
0992: if (!serverSupportsStoredProcedures()) {
0993: return;
0994: }
0995:
0996: createTable("testBug25379", "(col char(40))");
0997:
0998: try {
0999: this .stmt
1000: .executeUpdate("DROP PROCEDURE IF EXISTS sp_testBug25379");
1001: this .stmt
1002: .executeUpdate("CREATE PROCEDURE sp_testBug25379 (INOUT invalue char(255))"
1003: + "\nBEGIN"
1004: + "\ninsert into testBug25379(col) values(invalue);"
1005: + "\nEND");
1006:
1007: CallableStatement cstmt = this .conn
1008: .prepareCall("{call sp_testBug25379(?)}");
1009: cstmt.setString(1, "'john'");
1010: cstmt.executeUpdate();
1011: assertEquals("'john'", cstmt.getString(1));
1012: assertEquals("'john'", getSingleValue("testBug25379",
1013: "col", "").toString());
1014: } finally {
1015: this .stmt
1016: .executeUpdate("DROP PROCEDURE IF EXISTS sp_testBug25379");
1017: }
1018: }
1019:
1020: /**
1021: * Tests fix for BUG#25715 - CallableStatements with OUT/INOUT parameters that
1022: * are "binary" have extra 7 bytes (which happens to be the _binary introducer!)
1023: *
1024: * @throws Exception if the test fails.
1025: */
1026: public void testBug25715() throws Exception {
1027: if (!serverSupportsStoredProcedures()) {
1028: return; // no stored procs
1029: }
1030:
1031: if (isRunningOnJdk131()) {
1032: return; // no such method to test
1033: }
1034:
1035: createProcedure("spbug25715", "(INOUT mblob MEDIUMBLOB)"
1036: + "BEGIN" + " SELECT 1 FROM DUAL WHERE 1=0;" + "\nEND");
1037: CallableStatement cstmt = null;
1038:
1039: try {
1040: cstmt = this .conn.prepareCall("{call spbug25715(?)}");
1041:
1042: byte[] buf = new byte[65];
1043: for (int i = 0; i < 65; i++)
1044: buf[i] = 1;
1045: int il = buf.length;
1046:
1047: int[] typesToTest = new int[] { Types.BIT, Types.BINARY,
1048: Types.BLOB, Types.JAVA_OBJECT, Types.LONGVARBINARY,
1049: Types.VARBINARY };
1050:
1051: for (int i = 0; i < typesToTest.length; i++) {
1052:
1053: cstmt.setBinaryStream("mblob",
1054: new ByteArrayInputStream(buf), buf.length);
1055: cstmt.registerOutParameter("mblob", typesToTest[i]);
1056:
1057: cstmt.executeUpdate();
1058:
1059: InputStream is = cstmt.getBlob("mblob")
1060: .getBinaryStream();
1061: ByteArrayOutputStream bOut = new ByteArrayOutputStream();
1062:
1063: int bytesRead = 0;
1064: byte[] readBuf = new byte[256];
1065:
1066: while ((bytesRead = is.read(readBuf)) != -1) {
1067: bOut.write(readBuf, 0, bytesRead);
1068: }
1069:
1070: byte[] fromSelectBuf = bOut.toByteArray();
1071:
1072: int ol = fromSelectBuf.length;
1073:
1074: assertEquals(il, ol);
1075: }
1076:
1077: cstmt.close();
1078: } finally {
1079: closeMemberJDBCResources();
1080:
1081: if (cstmt != null) {
1082: cstmt.close();
1083: }
1084: }
1085:
1086: }
1087:
1088: protected boolean serverSupportsStoredProcedures()
1089: throws SQLException {
1090: return versionMeetsMinimum(5, 0);
1091: }
1092:
1093: public void testBug26143() throws Exception {
1094: if (!serverSupportsStoredProcedures()) {
1095: return; // no stored procedure support
1096: }
1097:
1098: this .stmt
1099: .executeUpdate("DROP PROCEDURE IF EXISTS testBug26143");
1100:
1101: this .stmt
1102: .executeUpdate("CREATE DEFINER=CURRENT_USER PROCEDURE testBug26143(I INT) COMMENT 'abcdefg'"
1103: + "\nBEGIN\n" + "SELECT I * 10;" + "\nEND");
1104:
1105: this .conn.prepareCall("{call testBug26143(?)").close();
1106: }
1107:
1108: /**
1109: * Tests fix for BUG#26959 - comments confuse procedure parser.
1110: *
1111: * @throws Exception if the test fails
1112: */
1113: public void testBug26959() throws Exception {
1114: if (!serverSupportsStoredProcedures()) {
1115: return;
1116: }
1117:
1118: createProcedure(
1119: "testBug26959",
1120: "(_ACTION varchar(20),"
1121: + "\n`/*dumb-identifier-1*/` int,"
1122: + "\n`#dumb-identifier-2` int,"
1123: + "\n`--dumb-identifier-3` int,"
1124: + "\n_CLIENT_ID int, -- ABC"
1125: + "\n_LOGIN_ID int, # DEF"
1126: + "\n_WHERE varchar(2000),"
1127: + "\n_SORT varchar(2000),"
1128: + "\n out _SQL varchar(/* inline right here - oh my gosh! */ 8000),"
1129: + "\n _SONG_ID int,"
1130: + "\n _NOTES varchar(2000),"
1131: + "\n out _RESULT varchar(10)"
1132: + "\n /*"
1133: + "\n , -- Generic result parameter"
1134: + "\n out _PERIOD_ID int, -- Returns the period_id. Useful when using @PREDEFLINK to return which is the last period"
1135: + "\n _SONGS_LIST varchar(8000),"
1136: + "\n _COMPOSERID int,"
1137: + "\n _PUBLISHERID int,"
1138: + "\n _PREDEFLINK int -- If the user is accessing through a predefined link: 0=none 1=last period"
1139: + "\n */) BEGIN SELECT 1; END");
1140:
1141: createProcedure(
1142: "testBug26959_1",
1143: "(`/*id*/` /* before type 1 */ varchar(20),"
1144: + "/* after type 1 */ OUT result2 DECIMAL(/*size1*/10,/*size2*/2) /* p2 */)"
1145: + "BEGIN SELECT action, result; END");
1146:
1147: try {
1148: this .conn
1149: .prepareCall(
1150: "{call testBug26959(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}")
1151: .close();
1152: this .rs = this .conn.getMetaData().getProcedureColumns(
1153: this .conn.getCatalog(), null, "testBug26959", "%");
1154:
1155: String[] parameterNames = new String[] { "_ACTION",
1156: "/*dumb-identifier-1*/", "#dumb-identifier-2",
1157: "--dumb-identifier-3", "_CLIENT_ID", "_LOGIN_ID",
1158: "_WHERE", "_SORT", "_SQL", "_SONG_ID", "_NOTES",
1159: "_RESULT" };
1160:
1161: int[] parameterTypes = new int[] { Types.VARCHAR,
1162: Types.INTEGER, Types.INTEGER, Types.INTEGER,
1163: Types.INTEGER, Types.INTEGER, Types.VARCHAR,
1164: Types.VARCHAR, Types.VARCHAR, Types.INTEGER,
1165: Types.VARCHAR, Types.VARCHAR };
1166:
1167: int[] direction = new int[] {
1168: DatabaseMetaData.procedureColumnIn,
1169: DatabaseMetaData.procedureColumnIn,
1170: DatabaseMetaData.procedureColumnIn,
1171: DatabaseMetaData.procedureColumnIn,
1172: DatabaseMetaData.procedureColumnIn,
1173: DatabaseMetaData.procedureColumnIn,
1174: DatabaseMetaData.procedureColumnIn,
1175: DatabaseMetaData.procedureColumnIn,
1176: DatabaseMetaData.procedureColumnOut,
1177: DatabaseMetaData.procedureColumnIn,
1178: DatabaseMetaData.procedureColumnIn,
1179: DatabaseMetaData.procedureColumnOut };
1180:
1181: int[] precision = new int[] { 20, 10, 10, 10, 10, 10, 2000,
1182: 2000, 8000, 10, 2000, 10 };
1183:
1184: int index = 0;
1185:
1186: while (this .rs.next()) {
1187: assertEquals(parameterNames[index], this .rs
1188: .getString("COLUMN_NAME"));
1189: assertEquals(parameterTypes[index], this .rs
1190: .getInt("DATA_TYPE"));
1191: assertEquals(precision[index], this .rs
1192: .getInt("PRECISION"));
1193: assertEquals(direction[index], this .rs
1194: .getInt("COLUMN_TYPE"));
1195: index++;
1196: }
1197:
1198: this .rs.close();
1199:
1200: index = 0;
1201: parameterNames = new String[] { "/*id*/", "result2" };
1202: parameterTypes = new int[] { Types.VARCHAR, Types.DECIMAL };
1203: precision = new int[] { 20, 10 };
1204: direction = new int[] { DatabaseMetaData.procedureColumnIn,
1205: DatabaseMetaData.procedureColumnOut };
1206: int[] scale = new int[] { 0, 2 };
1207:
1208: this .conn.prepareCall("{call testBug26959_1(?, ?)}")
1209: .close();
1210:
1211: this .rs = this .conn.getMetaData()
1212: .getProcedureColumns(this .conn.getCatalog(), null,
1213: "testBug26959_1", "%");
1214:
1215: while (this .rs.next()) {
1216: assertEquals(parameterNames[index], this .rs
1217: .getString("COLUMN_NAME"));
1218: assertEquals(parameterTypes[index], this .rs
1219: .getInt("DATA_TYPE"));
1220: assertEquals(precision[index], this .rs
1221: .getInt("PRECISION"));
1222: assertEquals(scale[index], this .rs.getInt("SCALE"));
1223: assertEquals(direction[index], this .rs
1224: .getInt("COLUMN_TYPE"));
1225:
1226: index++;
1227: }
1228: } finally {
1229: closeMemberJDBCResources();
1230: }
1231: }
1232:
1233: /**
1234: * Tests fix for BUG#27400 - CALL [comment] some_proc() doesn't work
1235: */
1236: public void testBug27400() throws Exception {
1237: if (!serverSupportsStoredProcedures()) {
1238: return; // SPs not supported
1239: }
1240:
1241: createProcedure("testBug27400",
1242: "(a INT, b VARCHAR(32)) BEGIN SELECT 1; END");
1243:
1244: CallableStatement cStmt = null;
1245:
1246: try {
1247: cStmt = this .conn
1248: .prepareCall("{CALL /* SOME COMMENT */ testBug27400( /* does this work too? */ ?, ?)} # and a commented ? here too");
1249: assertTrue(cStmt.toString().indexOf("/*") != -1); // we don't want to strip the comments
1250: cStmt.setInt(1, 1);
1251: cStmt.setString(2, "bleh");
1252: cStmt.execute();
1253: } finally {
1254: if (cStmt != null) {
1255: cStmt.close();
1256: }
1257: }
1258: }
1259:
1260: /**
1261: * Tests fix for BUG#28689 - CallableStatement.executeBatch()
1262: * doesn't work when connection property "noAccessToProcedureBodies"
1263: * has been set to "true".
1264: *
1265: * The fix involves changing the behavior of "noAccessToProcedureBodies",
1266: * in that the driver will now report all paramters as "IN" paramters
1267: * but allow callers to call registerOutParameter() on them.
1268: *
1269: * @throws Exception
1270: */
1271: public void testBug28689() throws Exception {
1272: if (!versionMeetsMinimum(5, 0)) {
1273: return; // no stored procedures
1274: }
1275:
1276: createTable("testBug28689", "(" +
1277:
1278: "`id` int(11) NOT NULL auto_increment,"
1279: + "`usuario` varchar(255) default NULL,"
1280: + "PRIMARY KEY (`id`)" + ")");
1281:
1282: this .stmt
1283: .executeUpdate("INSERT INTO testBug28689 (usuario) VALUES ('AAAAAA')");
1284:
1285: createProcedure(
1286: "sp_testBug28689",
1287: "(tid INT)"
1288: + "\nBEGIN"
1289: + "\nUPDATE testBug28689 SET usuario = 'BBBBBB' WHERE id = tid;"
1290: + "\nEND");
1291:
1292: Connection noProcedureBodiesConn = getConnectionWithProps("noAccessToProcedureBodies=true");
1293: CallableStatement cStmt = null;
1294:
1295: try {
1296: cStmt = noProcedureBodiesConn
1297: .prepareCall("{CALL sp_testBug28689(?)}");
1298: cStmt.setInt(1, 1);
1299: cStmt.addBatch();
1300: cStmt.executeBatch();
1301:
1302: assertEquals("BBBBBB", getSingleIndexedValueWithQuery(
1303: noProcedureBodiesConn, 1,
1304: "SELECT `usuario` FROM testBug28689 WHERE id=1"));
1305: } finally {
1306: if (cStmt != null) {
1307: cStmt.close();
1308: }
1309:
1310: if (noProcedureBodiesConn != null) {
1311: noProcedureBodiesConn.close();
1312: }
1313: }
1314: }
1315: }
|