Logo Search packages:      
Sourcecode: mysql-connector-java version File versions  Download package

CallableStatementTest.java

/*
 Copyright (c) 2002, 2010, Oracle and/or its affiliates. All rights reserved.
 

  The MySQL Connector/J is licensed under the terms of the GPLv2
  <http://www.gnu.org/licenses/old-licenses/gpl-2.0.html>, like most MySQL Connectors.
  There are special exceptions to the terms and conditions of the GPLv2 as it is applied to
  this software, see the FLOSS License Exception
  <http://www.mysql.com/about/legal/licensing/foss-exception.html>.

  This program is free software; you can redistribute it and/or modify it under the terms
  of the GNU General Public License as published by the Free Software Foundation; version 2
  of the License.

  This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
  without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
  See the GNU General Public License for more details.

  You should have received a copy of the GNU General Public License along with this
  program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth
  Floor, Boston, MA 02110-1301  USA



 */
package testsuite.simple;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Properties;

import testsuite.BaseTestCase;

import com.mysql.jdbc.SQLError;
import com.mysql.jdbc.log.StandardLogger;

/**
 * Tests callable statement functionality.
 * 
 * @author Mark Matthews
 * @version $Id: CallableStatementTest.java,v 1.1.2.1 2005/05/13 18:58:37
 *          mmatthews Exp $
 */
00049 public class CallableStatementTest extends BaseTestCase {
      /**
       * DOCUMENT ME!
       * 
       * @param name
       */
00055       public CallableStatementTest(String name) {
            super(name);
      }

      /**
       * Tests functioning of inout parameters
       * 
       * @throws Exception
       *             if the test fails
       */

00066       public void testInOutParams() throws Exception {
            if (versionMeetsMinimum(5, 0)) {
                  CallableStatement storedProc = null;

                  createProcedure("testInOutParam",
                              "(IN p1 VARCHAR(255), INOUT p2 INT)\n" + "begin\n"
                                          + " DECLARE z INT;\n" + "SET z = p2 + 1;\n"
                                          + "SET p2 = z;\n" + "SELECT p1;\n"
                                          + "SELECT CONCAT('zyxw', p1);\n" + "end\n");

                  storedProc = this.conn.prepareCall("{call testInOutParam(?, ?)}");

                  storedProc.setString(1, "abcd");
                  storedProc.setInt(2, 4);
                  storedProc.registerOutParameter(2, Types.INTEGER);

                  storedProc.execute();

                  assertEquals(5, storedProc.getInt(2));

            }
      }

      public void testBatch() throws Exception {
            if (versionMeetsMinimum(5, 0)) {
                  Connection batchedConn = null;

                  try {
                        createTable("testBatchTable", "(field1 INT)");
                        createProcedure("testBatch", "(IN foo VARCHAR(15))\n"
                                    + "begin\n"
                                    + "INSERT INTO testBatchTable VALUES (foo);\n"
                                    + "end\n");

                        executeBatchedStoredProc(this.conn);

                        batchedConn = getConnectionWithProps("rewriteBatchedStatements=true,profileSQL=true");

                        StringBuffer outBuf = new StringBuffer();
                        StandardLogger.bufferedLog = outBuf;
                        executeBatchedStoredProc(batchedConn);
                        String[] log = outBuf.toString().split(";");
                        assertTrue(log.length > 20);
                  } finally {
                        StandardLogger.bufferedLog = null;

                        if (batchedConn != null) {
                              batchedConn.close();
                        }
                  }
            }
      }

      private void executeBatchedStoredProc(Connection c) throws Exception {
            this.stmt.executeUpdate("TRUNCATE TABLE testBatchTable");

            CallableStatement storedProc = c.prepareCall("{call testBatch(?)}");

            try {
                  int numBatches = 300;

                  for (int i = 0; i < numBatches; i++) {
                        storedProc.setInt(1, i + 1);
                        storedProc.addBatch();
                  }

                  int[] counts = storedProc.executeBatch();

                  assertEquals(numBatches, counts.length);

                  for (int i = 0; i < numBatches; i++) {
                        assertEquals(1, counts[i]);
                  }

                  this.rs = this.stmt
                              .executeQuery("SELECT field1 FROM testBatchTable ORDER BY field1 ASC");

                  for (int i = 0; i < numBatches; i++) {
                        assertTrue(this.rs.next());
                        assertEquals(i + 1, this.rs.getInt(1));
                  }
            } finally {

                  if (storedProc != null) {
                        storedProc.close();
                  }
            }
      }

      /**
       * Tests functioning of output parameters.
       * 
       * @throws Exception
       *             if the test fails.
       */
00161       public void testOutParams() throws Exception {
            if (versionMeetsMinimum(5, 0)) {
                  CallableStatement storedProc = null;

                  createProcedure("testOutParam", "(x int, out y int)\n" + "begin\n"
                              + "declare z int;\n" + "set z = x+1, y = z;\n" + "end\n");

                  storedProc = this.conn.prepareCall("{call testOutParam(?, ?)}");

                  storedProc.setInt(1, 5);
                  storedProc.registerOutParameter(2, Types.INTEGER);

                  storedProc.execute();

                  System.out.println(storedProc);

                  int indexedOutParamToTest = storedProc.getInt(2);

                  if (!isRunningOnJdk131()) {
                        int namedOutParamToTest = storedProc.getInt("y");

                        assertTrue("Named and indexed parameter are not the same",
                                    indexedOutParamToTest == namedOutParamToTest);
                        assertTrue("Output value not returned correctly",
                                    indexedOutParamToTest == 6);

                        // Start over, using named parameters, this time
                        storedProc.clearParameters();
                        storedProc.setInt("x", 32);
                        storedProc.registerOutParameter("y", Types.INTEGER);

                        storedProc.execute();

                        indexedOutParamToTest = storedProc.getInt(2);
                        namedOutParamToTest = storedProc.getInt("y");

                        assertTrue("Named and indexed parameter are not the same",
                                    indexedOutParamToTest == namedOutParamToTest);
                        assertTrue("Output value not returned correctly",
                                    indexedOutParamToTest == 33);

                        try {
                              storedProc.registerOutParameter("x", Types.INTEGER);
                              assertTrue(
                                          "Should not be able to register an out parameter on a non-out parameter",
                                          true);
                        } catch (SQLException sqlEx) {
                              if (!SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
                                          .getSQLState())) {
                                    throw sqlEx;
                              }
                        }

                        try {
                              storedProc.getInt("x");
                              assertTrue(
                                          "Should not be able to retreive an out parameter on a non-out parameter",
                                          true);
                        } catch (SQLException sqlEx) {
                              if (!SQLError.SQL_STATE_COLUMN_NOT_FOUND.equals(sqlEx
                                          .getSQLState())) {
                                    throw sqlEx;
                              }
                        }
                  }

                  try {
                        storedProc.registerOutParameter(1, Types.INTEGER);
                        assertTrue(
                                    "Should not be able to register an out parameter on a non-out parameter",
                                    true);
                  } catch (SQLException sqlEx) {
                        if (!SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
                                    .getSQLState())) {
                              throw sqlEx;
                        }
                  }
            }
      }

      /**
       * Tests functioning of output parameters.
       * 
       * @throws Exception
       *             if the test fails.
       */
00247       public void testResultSet() throws Exception {
            if (versionMeetsMinimum(5, 0)) {
                  CallableStatement storedProc = null;

                  createTable("testSpResultTbl1", "(field1 INT)");
                  this.stmt
                              .executeUpdate("INSERT INTO testSpResultTbl1 VALUES (1), (2)");
                  createTable("testSpResultTbl2", "(field2 varchar(255))");
                  this.stmt
                              .executeUpdate("INSERT INTO testSpResultTbl2 VALUES ('abc'), ('def')");

                  createProcedure(
                              "testSpResult",
                              "()\n"
                                          + "BEGIN\n"
                                          + "SELECT field2 FROM testSpResultTbl2 WHERE field2='abc';\n"
                                          + "UPDATE testSpResultTbl1 SET field1=2;\n"
                                          + "SELECT field2 FROM testSpResultTbl2 WHERE field2='def';\n"
                                          + "end\n");

                  storedProc = this.conn.prepareCall("{call testSpResult()}");

                  storedProc.execute();

                  this.rs = storedProc.getResultSet();

                  ResultSetMetaData rsmd = this.rs.getMetaData();

                  assertTrue(rsmd.getColumnCount() == 1);
                  assertTrue("field2".equals(rsmd.getColumnName(1)));
                  assertTrue(rsmd.getColumnType(1) == Types.VARCHAR);

                  assertTrue(this.rs.next());

                  assertTrue("abc".equals(this.rs.getString(1)));

                  // TODO: This does not yet work in MySQL 5.0
                  // assertTrue(!storedProc.getMoreResults());
                  // assertTrue(storedProc.getUpdateCount() == 2);
                  assertTrue(storedProc.getMoreResults());

                  ResultSet nextResultSet = storedProc.getResultSet();

                  rsmd = nextResultSet.getMetaData();

                  assertTrue(rsmd.getColumnCount() == 1);
                  assertTrue("field2".equals(rsmd.getColumnName(1)));
                  assertTrue(rsmd.getColumnType(1) == Types.VARCHAR);

                  assertTrue(nextResultSet.next());

                  assertTrue("def".equals(nextResultSet.getString(1)));

                  nextResultSet.close();

                  this.rs.close();

                  storedProc.execute();
            }
      }

      /**
       * Tests parsing of stored procedures
       * 
       * @throws Exception
       *             if an error occurs.
       */
00314       public void testSPParse() throws Exception {

            if (versionMeetsMinimum(5, 0)) {

                  CallableStatement storedProc = null;

                  createProcedure("testSpParse", "(IN FOO VARCHAR(15))\n" + "BEGIN\n"
                              + "SELECT 1;\n" + "end\n");

                  storedProc = this.conn.prepareCall("{call testSpParse()}");

            }
      }

      /**
       * Tests parsing/execution of stored procedures with no parameters...
       * 
       * @throws Exception
       *             if an error occurs.
       */
00334       public void testSPNoParams() throws Exception {

            if (versionMeetsMinimum(5, 0)) {

                  CallableStatement storedProc = null;

                  createProcedure("testSPNoParams", "()\n" + "BEGIN\n"
                              + "SELECT 1;\n" + "end\n");

                  storedProc = this.conn.prepareCall("{call testSPNoParams()}");
                  storedProc.execute();

            }
      }

      /**
       * Tests parsing of stored procedures
       * 
       * @throws Exception
       *             if an error occurs.
       */
00355       public void testSPCache() throws Exception {
            if (isRunningOnJdk131()) {
                  return; // no support for LRUCache
            }

            if (versionMeetsMinimum(5, 0)) {

                  CallableStatement storedProc = null;

                  createProcedure("testSpParse", "(IN FOO VARCHAR(15))\n" + "BEGIN\n"
                              + "SELECT 1;\n" + "end\n");

                  int numIterations = 10;

                  long startTime = System.currentTimeMillis();

                  for (int i = 0; i < numIterations; i++) {
                        storedProc = this.conn.prepareCall("{call testSpParse(?)}");
                        storedProc.close();
                  }

                  long elapsedTime = System.currentTimeMillis() - startTime;

                  System.out.println("Standard parsing/execution: " + elapsedTime
                              + " ms");

                  storedProc = this.conn.prepareCall("{call testSpParse(?)}");
                  storedProc.setString(1, "abc");
                  this.rs = storedProc.executeQuery();

                  assertTrue(this.rs.next());
                  assertTrue(this.rs.getInt(1) == 1);

                  Properties props = new Properties();
                  props.setProperty("cacheCallableStmts", "true");

                  Connection cachedSpConn = getConnectionWithProps(props);

                  startTime = System.currentTimeMillis();

                  for (int i = 0; i < numIterations; i++) {
                        storedProc = cachedSpConn.prepareCall("{call testSpParse(?)}");
                        storedProc.close();
                  }

                  elapsedTime = System.currentTimeMillis() - startTime;

                  System.out.println("Cached parse stage: " + elapsedTime + " ms");

                  storedProc = cachedSpConn.prepareCall("{call testSpParse(?)}");
                  storedProc.setString(1, "abc");
                  this.rs = storedProc.executeQuery();

                  assertTrue(this.rs.next());
                  assertTrue(this.rs.getInt(1) == 1);

            }
      }

      public void testOutParamsNoBodies() throws Exception {
            if (versionMeetsMinimum(5, 0)) {
                  CallableStatement storedProc = null;

                  Properties props = new Properties();
                  props.setProperty("noAccessToProcedureBodies", "true");

                  Connection spConn = getConnectionWithProps(props);

                  createProcedure("testOutParam", "(x int, out y int)\n" + "begin\n"
                              + "declare z int;\n" + "set z = x+1, y = z;\n" + "end\n");

                  storedProc = spConn.prepareCall("{call testOutParam(?, ?)}");

                  storedProc.setInt(1, 5);
                  storedProc.registerOutParameter(2, Types.INTEGER);

                  storedProc.execute();

                  int indexedOutParamToTest = storedProc.getInt(2);

                  assertTrue("Output value not returned correctly",
                              indexedOutParamToTest == 6);

                  storedProc.clearParameters();
                  storedProc.setInt(1, 32);
                  storedProc.registerOutParameter(2, Types.INTEGER);

                  storedProc.execute();

                  indexedOutParamToTest = storedProc.getInt(2);

                  assertTrue("Output value not returned correctly",
                              indexedOutParamToTest == 33);
            }
      }

      /**
       * Runs all test cases in this test suite
       * 
       * @param args
       */
00456       public static void main(String[] args) {
            junit.textui.TestRunner.run(CallableStatementTest.class);
      }

      /**
       * Tests the new parameter parser that doesn't require "BEGIN" or "\n" at
       * end of parameter declaration
       * 
       * @throws Exception
       */
00466       public void testParameterParser() throws Exception {

            if (!versionMeetsMinimum(5, 0)) {
                  return;
            }

            CallableStatement cstmt = null;

            try {

                  createTable("t1",
                              "(id   char(16) not null default '', data int not null)");
                  createTable("t2", "(s   char(16),  i   int,  d   double)");

                  createProcedure("foo42",
                              "() insert into test.t1 values ('foo', 42);");
                  this.conn.prepareCall("{CALL foo42()}");
                  this.conn.prepareCall("{CALL foo42}");

                  createProcedure("bar",
                              "(x char(16), y int, z DECIMAL(10)) insert into test.t1 values (x, y);");
                  cstmt = this.conn.prepareCall("{CALL bar(?, ?, ?)}");

                  if (!isRunningOnJdk131()) {
                        ParameterMetaData md = cstmt.getParameterMetaData();
                        assertEquals(3, md.getParameterCount());
                        assertEquals(Types.CHAR, md.getParameterType(1));
                        assertEquals(Types.INTEGER, md.getParameterType(2));
                        assertEquals(Types.DECIMAL, md.getParameterType(3));
                  }

                  createProcedure("p", "() label1: WHILE @a=0 DO SET @a=1; END WHILE");
                  this.conn.prepareCall("{CALL p()}");

                  createFunction("f", "() RETURNS INT NO SQL return 1; ");
                  cstmt = this.conn.prepareCall("{? = CALL f()}");

                  if (!isRunningOnJdk131()) {
                        ParameterMetaData md = cstmt.getParameterMetaData();
                        assertEquals(Types.INTEGER, md.getParameterType(1));
                  }
            } finally {
                  if (cstmt != null) {
                        cstmt.close();
                  }
            }
      }
}

Generated by  Doxygen 1.6.0   Back to index