| Bug #33770 | Failure to execute the Stored Procedures with the parameters passed by name but | ||
|---|---|---|---|
| Submitted: | 9 Jan 2008 14:31 | Modified: | 9 Feb 2008 14:36 |
| Reporter: | Arun Merum | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | Ver 14.12 Distrib 5.0.45 | OS: | Windows (non positional parameters, executing, JAVA) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Non positional parameters | ||
[9 Jan 2008 14:33]
Arun Merum
This effects all the stored procedure users.
[9 Jan 2008 14:35]
Mark Matthews
That's not the syntax for passing values by name, at least not for JDBC. It *should* look like this:
sp = con.prepareCall("{ call get_movies( ?, ? )}");
sp.setString("title",'t');
sp.setString("genere",'a');
ResultSet res = sp.executeQuery();
(Even not using Java, I don't see what the benefit of the way you posted would be, since you're putting the names in their ordinal positions anyway!?)
[10 Feb 2008 0: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".
[5 Oct 2009 20:53]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/85797 2887 Guilhem Bichot 2009-10-05 Backport of the fix for BUG#33770 "Full table scan instead selected partitions for query more than 10 partitions" from 6.0, made in sergefp@mysql.com-20090205190644-q8632sniogedhtsu
Description: I have tried preparing a callable statement in JAVA and tried passing the variables non-positionally. The procedure is not considering the passed IN parameter values as NULL. con = getConnection(); sp = con.prepareCall("{ call get_movies( @title = ?, @genere = ? )}"); sp.setString(1,'t'); sp.setString(2,'a'); ResultSet res = sp.executeQuery(); The data base had the following records. +------+----------------------------------+------------+ | id | title | genere | +------+----------------------------------+------------+ | 1 | Couching Tiger and Hidden Dragon | Action | | 2 | Titanic | Romantic | | 3 | The Huin | Historical | +------+----------------------------------+------------+ And the stored procedure is returning the proper results if the statement is con.prepareCall("{ call get_movies( ?, ? )}"); So I think this is a bug in this version of MYSQL that its not allowing the users to pass the parameter values by name. How to repeat: delimiter // DROP PROCEDURE IF EXISTS dynamic_sql_builder// CREATE PROCEDURE dynamic_sql_builder(IN query_string VARCHAR(1000), INOUT variable text(100000000)) BEGIN SET variable = CONCAT(variable,query_string); END; DELIMITER $$ DROP PROCEDURE IF EXISTS `mein`.`get_movies` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `get_movies`(IN title VARCHAR(100),IN genere VARCHAR(100)) READS SQL DATA BEGIN SET @qry = ''; call dynamic_sql_builder ( ' SELECT ' ,@qry ); call dynamic_sql_builder ( ' title' ,@qry ); call dynamic_sql_builder ( ' ,genere' ,@qry ); call dynamic_sql_builder ( ' FROM' ,@qry ); call dynamic_sql_builder ( ' movie m' ,@qry ); call dynamic_sql_builder ( ' WHERE' ,@qry ); call dynamic_sql_builder ( ' 1 = 1 ' ,@qry ); IF( title IS NOT Null AND RTRIM(LTRIM(title)) <> '') THEN call dynamic_sql_builder ( ' AND lower(title) LIKE \'' ,@qry); call dynamic_sql_builder ( LOWER(LTRIM(RTRIM(title))),@qry); call dynamic_sql_builder ( '%\'',@qry); END IF; IF( genere IS NOT Null AND RTRIM(LTRIM(genere)) <> '') THEN call dynamic_sql_builder ( ' AND lower(genere) = \'',@qry); call dynamic_sql_builder ( LOWER(LTRIM(RTRIM(genere))),@qry); call dynamic_sql_builder ( '\'' ,@qry ); END IF; PREPARE stmt FROM @qry; EXECUTE stmt; END $$ DELIMITER ; The above are my stored procedures When I have executed the stored procedure like the below. call get_movies(@title = 't', @genere = 'a'); Its returing me all the rows in the database; However, when I execute call get_movies( 't', 'a'); Its giving me proper results. Suggested fix: Allow the parameters to be passed by name but not by the position;