/*
Copyright (c) 2002, 2012, 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 $
*/
public class CallableStatementTest extends BaseTestCase {
/**
* DOCUMENT ME!
*
* @param name
*/
public CallableStatementTest(String name) {
super(name);
}
/**
* Tests functioning of inout parameters
*
* @throws Exception
* if the test fails
*/
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("logger=StandardLogger,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.
*/
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.
*/
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.
*/
public void testSPParse() throws Exception {
if (versionMeetsMinimum(5, 0)) {
@SuppressWarnings("unused")
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.
*/
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.
*/
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
*/
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
*/
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();
}
}
}
}