Problem in select stmt inside stored procedure

[yrrah]

Hello, i've a problem regarding the select stmt inside the stored procedure.

Here's my code:

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `p`.`getNames`$$
 
CREATE PROCEDURE `p`.`getNames`(q varchar)
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
    exec q;
 
    END$$
 
DELIMITER ;
 

i want to execute the variable q.

for ex: (from the java class)

q = "select * from user where username = '"+u+"' AND name = '"+n+"';";
 

then i'll just pass it to the stored procedure. however, that doesn't work, there's an error that says

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | R' at line 1

there are certain conditions in the java class so q varies depending on the user inputs.

please help me.

Please enclose any code within the proper code tags. See the Posting Guidelines on how to do that. - moderator

[amitpatel66]

Hello, i've a problem regarding the select stmt inside the stored procedure.

Here's my code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `p`.`getNames`$$

CREATE PROCEDURE `p`.`getNames`(q varchar)
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
exec q;

END$$

DELIMITER ;

i want to execute the variable q.

for ex: (from the java class)
q = "select * from user where username = '"+u+"' AND name = '"+n+"';";

then i'll just pass it to the stored procedure. however, that doesn't work, there's an error that says

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | R' at line 1


there are certain conditions in the java class so q varies depending on the user inputs.

please help me.

Try this query:

 
q = "select * from user where username = '" +u+"' AND name = '"+n+"'";
 
 

[yrrah]

Try this query:

 
q = "select * from user where username = '" +u+"' AND name = '"+n+"'";
 
 

Ah..what i want to do is create a stored procedure that will execute any query that i like to pass. the query would vary depending on the java class. the java class would then pass it to the mysql.

[amitpatel66]

Ah..what i want to do is create a stored procedure that will execute any query that i like to pass. the query would vary depending on the java class. the java class would then pass it to the mysql.

Yes but its just a syntax error and its probably because the semicolon that you had placed at the end of your query. I removed a semi colon and have you tested that query or it does not work either?

[yrrah]

Yes but its just a syntax error and its probably because the semicolon that you had placed at the end of your query. I removed a semi colon and have you tested that query or it does not work either?

I've tried it out already but it doesn't work.:(
It says:

Error Code : 1243
Unknown prepared statement handler (q) given to EXECUTE

what does this mean?

thanks for your help!

[amitpatel66]

I've tried it out already but it doesn't work.:(
It says:

Error Code : 1243
Unknown prepared statement handler (q) given to EXECUTE

what does this mean?

thanks for your help!

Could you please post your java source that defines variable q for reference? Now this error is due to some java code and the SQL statement is fine.

[yrrah]

Could you please post your java source that defines variable q for reference? Now this error is due to some java code and the SQL statement is fine.

i'm using sqlyog. so i call it in the query tab.

what i did is this:

call sp_name('select * from person');

[amitpatel66]

i'm using sqlyog. so i call it in the query tab.

what i did is this:

call sp_name('select * from person');

Is your procedure running fine when you pass this select statement??

[yrrah]

Is your procedure running fine when you pass this select statement??

Hi, I already got this one. thanks for your help!!!

:D:)