| 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: | |
| 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 | |
[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
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.