Hi again,
I found a way that works for the query I wanted in my sproc. It uses
your solution to insert into a temporary table, and then uses a SELECT
in the "IN"-part.
I don't know yet if this solution may have side-effects. I need to read
more about how temporary tables is handled by MySQL, as when it comes to
speed of execution in this solution.
Thanks for your help Peter.
My solution (with an argument that may look weird, but I found out
adding the extra paranthesis while generating the string in Python was
so much easier that doing it in the sproc) (working test):
-----------------------------------------------------------
DROP TABLE IF EXISTS user_test;
CREATE TABLE user_test (
user_id INT,
user_name VARCHAR(100),
PRIMARY KEY (user_id)
) ENGINE=MyIsam;
INSERT INTO user_test VALUES(1,'Bob'), (2,'Ann'), (3,'Bill'), (4,'Jim');
DROP PROCEDURE IF EXISTS get_users;
DELIMITER ::
CREATE PROCEDURE get_users(IN param VARCHAR(1000))
BEGIN
DROP TEMPORARY TABLE IF EXISTS temp_uids;
CREATE TEMPORARY TABLE temp_uids (
uid INT NOT NULL
);
SET @qry = CONCAT('INSERT INTO temp_uids VALUES ', param);
PREPARE stmt FROM @qry;
EXECUTE stmt;
SELECT user_id, user_name FROM user_test WHERE user_id IN (SELECT uid
FROM temp_uids);
DROP PREPARE stmt;
END; ::
DELIMITER ;
CALL get_users('(2), (3)');
-----------------------------------------------------------
MW
Peter Brawley wrote:
Hi Magne
>...the query I actually want to use this in, is a 100 line query with
lots of arguments.
>I don't feel to good about creating it into a bunch of strings (16)
that I have to
>concatenate with the variables inbetween.
The only alternative I know for current versions of MySQL is to assemble
the query in the app layer.
>Also, I was moving the query into a stored procedure because I wanted
to make
>the request fast, and the concatenating and string handling takes some
of that away.
Unfortunately MySQL sprocs do not yet deliver this advantage.
PB
-----
Magne Westlie wrote:
Hi,
Thanks a lot Peter, that was useful and it worked fine. The only
problem is that the query I actually want to use this in, is a 100
line query with lots of arguments. I don't feel to good about creating
it into a bunch of strings (16) that I have to concatenate with the
variables inbetween.
Also, I was moving the query into a stored procedure because I wanted
to make the request fast, and the concatenating and string handling
takes some of that away.
Is there another way?
Magne
Peter Brawley wrote:
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param
VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN ( |
but so long as it has those elements, it can be as complex as you
like. When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
('''abc'',''def'',''ghi''' ); |
||
||||PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
