Limit + Function issue

[Ator]

Hi

I have a function "MaxCars()" that returns an integer.
Then I try to execute this query:
"SELECT * FROM Cars LIMIT MaxCars()"

I get an error:
"You have an error....near MaxCars()"

How do I use a function in combination with LIMIT?

 

[hvass]

limit has to take a constant positive integer as its argument not a function that returns an integer

To get around this you have to use the prepared statement handler approach available from version 5 so for example

select @n:=ceiling(rand()*10);
prepare mystatementhandler from 'select * from mytable limit ?';
execute mystatementhandler using @n;

 

[Ator]

Hi hvass!

Now I have this:

SELECT @n := 3;
PREPARE STMT FROM 'SELECT * FROM Cars LIMIT ?';
EXECUTE STMT USING @n;

When I execute it, I get this error:
"Unknown prepared statement handler (STMT) given to EXECUTE"

Im working in "MySQL Query Browser" (win).
I tried with a DELIMITER too, but it did not work.

What is the difference between "SELECT @n := 3" and "SET @n = 3"?

What's wrong? =)

 

[johnwm]

The correct syntax for prepared statement parameters is

SET @n = 3;

See

http://dev.mysql.com/doc/refman/5.0/en/sqlps.html

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[Ator]

I still get the same error... :/

 

[johnwm]

1. Did you check the documentation I pointed to?
2. Are you using MySQL 5.0.7 or above as specified in the documentation linked?

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[Ator]

Hi John

Yes, I checked the documentation.
It works if I execute the code in "MySQL Command Line Client", but I can't get it to work in MySQL Query Browser.

 

[johnwm]

I failed to notice the query browser bit! It's a one line support only - see

http://bugs.mysql.com/bug.php?id=10304

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints