Re: Connector/J stored proc [Solved]
Posted by: Nathanael Mazuir
Date: November 18, 2004 04:17AM

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

Options: ReplyQuote


Subject
Written By
Posted
November 12, 2004 10:11AM
Re: Connector/J stored proc [Solved]
November 18, 2004 04:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.