Nigel Maddocks wrote:
> callStmt.registerOutParameter(1,
> java.sql.Types.INTEGER);
>
> falls over with "Parameter number 1 is not an OUT
> parameter"
>
> The procedure definition is:
> PROCEDURE `employee_surname_count`(surname
> VARCHAR(50), OUT surname_count INT)
> BEGIN
> DECLARE rtn INT;
> SELECT COUNT(*) INTO rtn FROM employee e WHERE
> e.surname=surname;
> SET surname_count = rtn;
> END
>
> String lclRunSQL= "{CALL
> employee_surname_count('Jones', ?)}"
> callStmt = conn.prepareCall(lclRunSQL);
>
> callStmt
> .getParameterMetaData().getParameterCount(); ---
> reports 1 (one).
>
> I appreciate that I'm not supplying all parameters
> as question marks "?" to the prepared statement,
> but the jdbc driver is reporting the
> parameterCount as 1 - which is fine by me.
>
> The works OK in MS SQL Server and Oracle.
>
> ----
> If I redefine the PROCEDURE as (OUT surname_count
> INT, surname VARCHAR(50)) (i.e. reverse the
> parameters)
> and prepare it using lclRunSQL= "{CALL
> employee_surname_count(?, 'Jones')}"
> then all is OK.
> ----
> I would regard the problem I'm encountering as an
> error in MySQL. In any case, MySQL is not being
> self-consistent.
>
> Using MySQL version 5.0.18, Java 1.5.0_04, Tomcat
> 5.5.9, Windows XP Pro SP2.
Nigel,
I'll have to check the JDBC spec on this one, as I'm pretty sure that parameters refer to placeholders, not literals, but I could be mistaken.
In any case, there isn't a way for a client of MySQL (including the JDBC driver) to do what you ask for currently, as you need to pass in a variable to get an output parameter.
A workaround would be to declare the 'Jones' with a ? and call .setString() on it, and register it as an output parameter.
-Mark
Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html