Bug #9139 Store procedure fail when it's execute with connecter/j or mysql

• Submitted: 12 Mar 2005 3:57
• Modified: 12 Mar 2005 15:45
• Reporter: John Bolanos
• Status: Closed
• Category: MySQL Server: User-defined functions ( UDF )
• Severity: S1 (Critical)
• Version: MySql 5.0
• OS: Windows (Windows xp Professional)
• CPU Architecture: Any

[12 Mar 2005 3:57] John Bolanos

Description:
When i excute the procedure attach i have the follow problem 
mysqld-max-nt.exe ha detectado un problema y debe cerrarse.

szAppName : mysqld-max-nt.exe     szAppVer : 0.0.0.0     
szModName : mysqld-max-nt.exe     szModVer : 0.0.0.0     offset : 00094cc4     

C:\DOCUME~1\JJB\CONFIG~1\Temp\WER47.tmp.dir00\mysqld-max-nt.exe.mdmp
C:\DOCUME~1\JJB\CONFIG~1\Temp\WER47.tmp.dir00\appcompat.txt

How to repeat:
I created the follow tables 

CREATE TABLE compania(
    compania char(20) NOT NULL,
    descripcion char(100),
    PRIMARY KEY (compania)
);

CREATE TABLE usuario (
    id char(10) not null,
    nombre char(100) not null,
    compania char(20) not null REFERENCES compania(compania),
    PRIMARY KEY (compania,id),
    INDEX nombreE (nombre)
);

Follow i created the follow procedure 

CREATE PROCEDURE creausuario ( IN chrUsuario VARCHAR(10),IN chrcontrasena VARCHAR(20), IN chrcompania VARCHAR(20) )
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE chrTmp VARCHAR(20);
  DECLARE cur1 CURSOR FOR SELECT compania FROM compania;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 

  
  OPEN cur1 ;
  REPEAT 
     FETCH cur1 INTO chrTmp ;  

     IF chrTmp = chrCompania THEN		
        INSERT INTO usuario VALUES (chrusuario, 'John Bolanos', PASSWORD(chrcontrasena), chrcompania) ;
     ELSE 
        INSERT INTO compania VALUES ( chrcompania, 'Prueba') ;
        INSERT INTO usuario VALUES (chrusuario, 'John Bolanos', PASSWORD(chrcontrasena), chrcompania) ;
     END IF ;
  UNTIL done END REPEAT ;

  CLOSE cur1;
END

The last i execute the procedure 
mysql>delimiter //
mysql>Call creausuario('c7950275','blacky','Ecopetrol');
Bug 

With connector/j

package scripst;

import java.sql.Connection; 
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.CallableStatement;
public class Datos{ 
    public static void main(String[] args) { 
	
        try {
	    Class.forName("com.mysql.jdbc.Driver"); 
	  } catch (Exception ex) {
	    System.out.println("Sin Error"); 	
	  }
	
        try { 
            
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/activos?user=root&password=blacky79502759");
            CallableStatement cStmt = conn.prepareCall("{call creausuario(?,?,?)}"); 
            cStmt.setString(1,"c7950275");
            cStmt.setString(2,"blacky");
            cStmt.setString(3,"Ecopetrol");
            System.out.println("Antes" );
            boolean hadResults = cStmt.execute();
            System.out.println("Despues" );
            
        } catch (SQLException ex) { 
            System.out.println("SQLException: " + ex.getMessage() ); 
            System.out.println("SQLState: " + ex.getSQLState() ); 
            System.out.println("VendorError: " + ex.getErrorCode() ); 
        }
        
        }
}

[12 Mar 2005 15:45] MySQL Verification Team

This issue was already fixed (server crash) in our BK source tree. You have an error in the second insert:

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.3-alpha-pro-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE compania(
    ->     compania char(20) NOT NULL,
    ->     descripcion char(100),
    ->     PRIMARY KEY (compania)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql>
mysql> CREATE TABLE usuario (
    ->     id char(10) not null,
    ->     nombre char(100) not null,
    ->     compania char(20) not null REFERENCES compania(compania),
    ->     PRIMARY KEY (compania,id),
    ->     INDEX nombreE (nombre)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE creausuario ( IN chrUsuario VARCHAR(10),IN chrcontrasena
    -> VARCHAR(20), IN chrcompania VARCHAR(20) )
    -> BEGIN
    ->   DECLARE done INT DEFAULT 0;
    ->   DECLARE chrTmp VARCHAR(20);
    ->   DECLARE cur1 CURSOR FOR SELECT compania FROM compania;
    ->   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    ->
    ->
    ->   OPEN cur1 ;
    ->   REPEAT
    ->      FETCH cur1 INTO chrTmp ;
    ->
    ->      IF chrTmp = chrCompania THEN
    ->         INSERT INTO usuario VALUES (chrusuario, 'John Bolanos',
    -> PASSWORD(chrcontrasena), chrcompania) ;
    ->      ELSE
    ->         INSERT INTO compania VALUES ( chrcompania, 'Prueba') ;
    ->         INSERT INTO usuario VALUES (chrusuario, 'John Bolanos',
    -> PASSWORD(chrcontrasena), chrcompania) ;
    ->      END IF ;
    ->   UNTIL done END REPEAT ;
    ->
    ->   CLOSE cur1;
    -> END//
Query OK, 0 rows affected (0.09 sec)

mysql> delimiter ;
mysql> Call creausuario('c7950275','blacky','Ecopetrol');
ERROR 1136 (21S01): Column count doesn't match value count at row 1