Bug #9319 same name procedure in diferent databases
Submitted: 21 Mar 2005 19:06 Modified: 31 May 2005 15:50
Reporter: Sergi Vergés Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: User-defined functions ( UDF ) Severity:S2 (Serious)
Version:5.0.3-beta-nt OS:Windows (Windows XP sp2)
Assigned to: CPU Architecture:Any

[21 Mar 2005 19:06] Sergi Vergés
Description:
I've three databases in the same server. Each database have on procedure with the same name but with diferent code and IN/OUT parameters the server doesn't use the correct procecdure from the database I'm using

How to repeat:
-create three databases
-create the following table in each database
DROP TABLE USUARIS
//
CREATE TABLE USUARIS(
	USUARI_ID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	CODI_USUARI VARCHAR(10) NOT NULL,
	CONTRASENYA VARCHAR(200) NOT NULL,
	NOM VARCHAR(100) NOT NULL,
	COGNOM1 VARCHAR(100) NOT NULL,
	COGNOM2 VARCHAR(100),
	DESCRIPCIO CHARACTER VARYING (255),
	ADMINISTRADOR VARCHAR(2),
	IDIOMA INTEGER,
	CREAT_PER INTEGER NOT NULL,
	DATA_CREACIO DATETIME NOT NULL,
	MODIFICAT_PER INTEGER NOT NULL,
	DATA_MODIFICACIO DATETIME NOT NULL
)
//
-create the following procedure in two of the databases
DROP PROCEDURE COMPROVAR_USUARI
//
CREATE PROCEDURE COMPROVAR_USUARI(IN p_CodiUsuari VARCHAR(10),
			 IN p_contrasenya VARCHAR(10),
			 OUT p_userId INTEGER,
			 OUT p_userName VARCHAR(30),
			 OUT p_administrador VARCHAR(1))
BEGIN
	select USUARI_ID, CONCAT(COGNOM1,' ',COGNOM2,', ',NOM), ADMINISTRADOR
	INTO p_userId, p_userName, p_administrador
	FROM USUARIS
	WHERE UPPER(CODI_USUARI)=p_CodiUsuari
	  AND CONTRASENYA=PASSWORD(p_contrasenya);
END
//
- create the following procedure in the other database
DROP PROCEDURE COMPROVAR_USUARI
//
CREATE PROCEDURE COMPROVAR_USUARI(IN p_CodiUsuari VARCHAR(10),
			 IN p_contrasenya VARCHAR(10),
			 OUT p_userId INTEGER,
			 OUT p_userName VARCHAR(30),
			 OUT p_administrador VARCHAR(1),
			 OUT p_idioma VARCHAR(2))
BEGIN
	select USUARI_ID, CONCAT(COGNOM1,' ',COGNOM2,', ',NOM), ADMINISTRADOR, IDIOMA
	INTO p_userId, p_userName, p_administrador, p_idioma
	FROM USUARIS, VALORS
	WHERE UPPER(CODI_USUARI)=p_CodiUsuari
	  AND CONTRASENYA=PASSWORD(p_contrasenya);
END
//
- create the 1.html page with two fileds, user and password and a button that submit the form to the 2.jsp page
- create the 2.jsp page that connects to the last database, with this code the get de parameters from 1.html
<%
  String user=request.getParameter("user");
  String passord=request.getParameter("password");
  if (cstmt!=null) cstmt.close(); 
  cstmt = ( CallableStatement )conn.prepareCall(
       "{ call COMPROVAR_USUARI(?, ?, ?, ?, ?, ?) }" );
  cstmt.setString( 1, codiUsuari);
  cstmt.setString( 2, contrasenya);
  cstmt.registerOutParameter( 3, java.sql.Types.INTEGER ); //uerId
  cstmt.registerOutParameter( 4, java.sql.Types.VARCHAR ); //userName
  cstmt.registerOutParameter( 5, java.sql.Types.VARCHAR ); //administrador
  cstmt.registerOutParameter( 6, java.sql.Types.VARCHAR ); //idioma
  
  cstmt.execute();  

%>

- when executing an error saying that parameter 6 is out of range (1, 5) is shown, when my database has a procedure with 6 parameters.
[24 Mar 2005 18:31] Mark Matthews
Does this error happen if you fully qualify the stored procedure name?

(i.e. code like this):

cstmt = ( CallableStatement )conn.prepareCall(
       "{ call db1.COMPROVAR_USUARI(?, ?, ?, ?, ?, ?) }" );

or alternatively:

cstmt = ( CallableStatement )conn.prepareCall(
       "{ call " + conn.getCatalog() + ".COMPROVAR_USUARI(?, ?, ?, ?, ?, ?) }" );
[29 Mar 2005 11:05] Sergi Vergés
Yes and no, using this two options you specified I get an error but not the same error. 
java.sql.SQLException: Parameter index of 2 is out of range (1, 0)
	at com.mysql.jdbc.CallableStatement.checkParameterIndexBounds(CallableStatement.java:1002)
	at com.mysql.jdbc.CallableStatement.checkIsOutputParam(CallableStatement.java:971)
	at com.mysql.jdbc.CallableStatement.registerOutParameter(CallableStatement.java:864).....
[11 Apr 2005 10:30] Sergi Vergés
I've tried the 5.0.3 beta server version and I've reproduced the error.
[11 May 2005 23: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".
[31 May 2005 15:37] Jorge del Conde
Thanks for your bug report.  I was able to reproduce this bug using 5.0.3
[31 May 2005 15:50] Mark Matthews
This is fixed in the nightly snapshots of 3.1.x, see http://downloads.mysql.com/snapshots.php