Ok, I found the origin of the problem.
It was due to this line of code :
CallableStatement cStmt = connection.prepareCall("{call eucalyptus.simpleproc(?)}");
The problem was that I specified the database name and the procedure name.
So I removed the database name and It worked fine.
CallableStatement cStmt = connection.prepareCall("{call simpleproc(?)}");
The fact is that when I created the procedure, I was using the eucalyptus database, so the proc table contains this data :
[shell]
mysql> select * from mysql.proc where specific_name like 'simpleproc';
+------------+------------+-----------+---------------+----------+-----------------+------------------+---------------+---------------+---------+-------------------------------------------------------------------+----------------+---------------------+---------------------+----------+---------+
| db | name | type | specific_name | language | sql_data_access | is_deterministic | security_type | param_list | returns | body | definer | created | modified | sql_mode | comment |
+------------+------------+-----------+---------------+----------+-----------------+------------------+---------------+---------------+---------+-------------------------------------------------------------------+----------------+---------------------+---------------------+----------+---------+
| eucalyptus | simpleproc | PROCEDURE | simpleproc | SQL | CONTAINS_SQL | NO | DEFINER | OUT param INT | | BEGIN
SELECT COUNT(*) INTO param FROM eucalyptus.mouvem04;
END | root@localhost | 2004-11-12 11:05:33 | 0000-00-00 00:00:00 | | |
+------------+------------+-----------+---------------+----------+-----------------+------------------+---------------+---------------+---------+-------------------------------------------------------------------+----------------+---------------------+---------------------+----------+---------+
[/shell]
When I call the procedure from the shell, I have to specify the database name (if no database is selected) to get a correct result.
[shell]
mysql> call eucalyptus.simpleproc(@a);
Query OK, 0 rows affected (0.88 sec)
mysql> select @a;
+--------+
| @a |
+--------+
| 293583 |
+--------+
[/shell]
If no database is selected and if I do not specify the database name, then I get an error while calling the procedure.
[error code]
mysql> call simpleproc(@a);
ERROR 1304 (42000): PROCEDURE .simpleproc does not exist
[/error code]
When the correct database is selected, then I do not have to specify its name to get a correct result.
[shell]
mysql> use eucalyptus
Database changed
mysql> call simpleproc(@a);
Query OK, 0 rows affected (0.91 sec)
mysql> select @a;
+--------+
| @a |
+--------+
| 293583 |
+--------+
1 row in set (0.00 sec)
[/shell]
If the correct database is selected, I still got a correct result if I specify the database name while calling the procedure:
[shell]
mysql> use eucalyptus
Database changed
mysql> call eucalyptus.simpleproc(@a);
Query OK, 0 rows affected (0.88 sec)
mysql> select @a;
+--------+
| @a |
+--------+
| 293583 |
+--------+
1 row in set (0.00 sec)
[/shell]
In this situation, Connector/J 's behaviour is quite disturbing (to me ;-) ).
Is it a bug? :-(
Regards,
Nathanaël