| Bug #24990 | Placing parenthesis within the COMMENT tag causes 2 Exceptions for procs | ||
|---|---|---|---|
| Submitted: | 12 Dec 2006 2:10 | Modified: | 25 Jul 2007 6:47 |
| Reporter: | Cory Twibell | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 5.0.3-commercial | OS: | Solaris (Solaris 8) |
| Assigned to: | CPU Architecture: | Any | |
[15 Dec 2006 9:54]
Tonci Grgin
Hi Cory and thanks for your problem report.
I was unable to verify it using latest c/J 5.0.x sources against our standard SP test:
DELIMITER $$
create procedure testInOutParam(IN p1 VARCHAR(255), INOUT p2 INT) COMMENT "This should provoke error (tableinfo)"
begin
DECLARE z INT;
SET z = p2 + 1;
SET p2 = z;
SELECT p1;
SELECT CONCAT('zyxw', p1);
end$$
DELIMITER ;
public void testInOutParams() throws Exception {
if (versionMeetsMinimum(5, 0)) {
CallableStatement storedProc = null;
try {
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));
} finally {
this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testInOutParam");
}
}
}
General log:
061215 10:46:23 4 Query create procedure testInOutParam(IN p1 VARCHAR(255), INOUT p2 INT) COMMENT "This should provoke error (tableinfo)"
begin
DECLARE z INT;
SET z = p2 + 1;
SET p2 = z;
SELECT p1;
SELECT CONCAT('zyxw', p1);
end
061215 10:46:28 20 Connect root@localhost on test
061215 10:46:29 20 Query SHOW VARIABLES
20 Query SHOW COLLATION
20 Query SET character_set_results = NULL
20 Query SET autocommit=1
20 Query SET sql_mode='STRICT_TRANS_TABLES'
20 Query SELECT VERSION()
20 Query USE `test`
20 Query SELECT DATABASE()
20 Query USE `test`
20 Query SHOW CREATE PROCEDURE `test`.`testInOutParam`
20 Query SET @com_mysql_jdbc_outparam_p2=_binary'4'
20 Query CALL testInOutParam('abcd', @com_mysql_jdbc_outparam_p2)
20 Query SELECT @com_mysql_jdbc_outparam_p2
20 Query DROP PROCEDURE IF EXISTS testInOutParam
20 Quit
Environment:
-Xmx256M -Dcom.mysql.jdbc.testsuite.url.default=jdbc:mysql://localhost:3306/test?user=root&password=
MySQL server 5.0.27BK on Win XP Pro SP2 localhost
JDK 1.5.0_10
[15 Dec 2006 15:47]
Cory Twibell
Please use MySQL5.0.22-pro for Solaris 8
[20 Dec 2006 11:10]
Sveta Smirnova
Please provide code you use to create connection.
[21 Jan 2007 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[25 Jul 2007 6:47]
Sveta Smirnova
I can not repeat described behaviour on Solaris.
[25 Jul 2007 7:00]
Tonci Grgin
Even though this is in no way critical, there have been several fixes regarding comments in c/J. Probably one of them fixed this problem too.
Description: When creating a store procedure with a COMMENT tag that contains a set of parenthesis, you get either: If you have parameters, you get StringIndexOutOfBoundsException If you have no parameters, you get NumberFormatException How to repeat: CREATE PROCEDURE RETRIEVE_ID ( IN _ID INT ) COMMENT "Selects my data, which includes (table info)" BEGIN DECLARE S TEXT; SET S = "SELECT * from DB.TBL"; IF ( _ID > 0 ) then SET S = CONCAT( S, " WHERE ID = ", _ID ); SET @SQL = S; PREPARE STMT FROM @SQL; EXECUTE STMT; DEALLOCATE PREPARE STMT; END; java program: public Test() { try { String s = "DB.RETRIEVE_ID"; Connection con; // Already have connection here CallableStatement cs = con.prepareCall( "{ call " + s + "(?)}" ); cs.setObject( 1, new Integer(0) ); cs.executeQuery(); } catch ( Exception e ) { e.printStackTrace(); } } Suggested fix: Is there a mismatch between the server and JDBC for the COMMENT tag within stored procedures?