MySQL Forums
Forum List  »  General

PREPARED Statement use in Query Browser (BUG?)
Posted by: Donald West
Date: February 26, 2007 01:11PM

Please help. I have a need to limit the number of rows returned from a SELECT with a variable number of rows.

If there is a way to do this other than LIMIT and PREPARED, I will be glad to hear a suggestion.

The only straightforward way I could find to do this is by using PREPARED. Otherwise LIMIT only takes a constant. The PREPARED solution works in the command line client but not in the Query Browser. I must be missing something because MySQL is so powerful and this is so simple a request. The details follow.

Here is the sample table I have created to keep things simple:
mysql> SELECT * FROM table_123;
+------+
| nums |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.05 sec)

If I want to limit the number of rows returned using LIMIT with a constant, it is simple:
mysql> SELECT * FROM table_123 LIMIT 1;
+------+
| nums |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

But if I want to limit the number of rows with a variable, I have to use a PREPARED statement. This is what it says in the MySQL online pdf manual on page 834 (By the way, my version is 5.0.27):

For prepared statements, you can use placeholders (supported as of MySQL version 5.0.7). The following
statements will return one row from the tbl table:
SET @a=1;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
EXECUTE STMT USING @a;


Here is PREPARED statement use in the MySQL command line client:


mysql> SET @a = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE STMT FROM
-> 'SELECT * FROM table_123
'> LIMIT ?;';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE STMT USING @a;
+------+
| nums |
+------+
| 1 |
+------+
1 row in set (0.00 sec)


So it works in the command line client. When I do the same thing in the Query Browser, I have to use ‘START TRANSACTION’ and ‘COMMIT’ in order to set variables in the Query Browser. Here is the code:
START TRANSACTION;
SET @a = 1;
PREPARE STMT FROM
'SELECT * FROM table_123
LIMIT ?;';
EXECUTE STMT USING @a;
COMMIT;

No result set is returned.
If I use the EXECUTE statement outside of ‘START TRANSACTION’ and ‘COMMIT’, I receive the message: ‘Unknown prepared statement handler (STMT) given to execute, number 1243’

Options: ReplyQuote


Subject
Written By
Posted
PREPARED Statement use in Query Browser (BUG?)
February 26, 2007 01:11PM


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.