membender has asked for the wisdom of the Perl Monks concerning the following question:

Hello, I'm wondering if there's a simple way to have mysql execute two (or more) statements with one execute call. The first would just set a temporary variable based on SELECTing something from one table, and the following would use that variable in its query or queries. One can do this in phpmyadmin (and the mysql client, I imagine) relatively straightforwardly, using SET. eg,

SET @t_id:=(SELECT Team_ID FROM Teams WHERE Name='Boston');
SELECT * FROM Games WHERE Team_ID=@t_id AND Date>'2012-05-01';
[plus indefinitely more, separated by semicolons;]

[download]

I could have my script retrieve t_id and then place its value into the second SELECT, but it seems like it would be much more efficient to just have mysql do both in the same call, especially since I don't want to do anything else with t_id within the script.

I've tried executing the entire string (concatenating the SET and SELECT commands above), but nothing seems to be returned. I hope I'm missing something simple (and apologies if I am!), but I haven't been able to find anything on this.

Thanks for any help you can offer. It will be very helpful.

UPDATE: I am using prepared queries, many of which are UPDATEs, so I don't need record sets in return. I started a separate thread with more details. Look for mutliple, mysql and execute/executing in the title, or http://perlmonks.org/?node_id=973409. Thank you for your help! (I think Anonymous Monk in Re^3 is on to what the real issue is :-)

Replies are listed 'Best First'.
Re: Executing multiple mysql commands in a single execute() call.
by roboticus (Chancellor) on May 30, 2012 at 10:43 UTC

    membender:

    Not all databases allow you to submit multiple statements in a batch. But if you're using one that does, there shouldn't be a problem. However, you haven't showed any code, so it's possible that your problem is related to string handling and/or error handling rather than multiple statements in a batch. Something like this should be fine:

    my $SQL1 = q{
SET @t_id:=(SELECT Team_ID FROM Teams WHERE Name='Boston');
SELECT * FROM Games WHERE Team_ID=@t_id AND Date>'2012-05-01';
};

    [download]

    while something like these will fail because perl will try to put the value of @t_id into the string for you, which will usually cause the SQL to fail:

    my $SQL2 = "
SET @t_id:=(SELECT Team_ID FROM Teams WHERE Name='Boston');
SELECT * FROM Games WHERE Team_ID=@t_id AND Date>'2012-05-01';
";
my $SQL3 = <<EOSQL;
SET @t_id:=(SELECT Team_ID FROM Teams WHERE Name='Boston');
SELECT * FROM Games WHERE Team_ID=@t_id AND Date>'2012-05-01';
EOSQL

    [download]

    But we can't tell what the error is, because you've provided no code nor error messages. So it's purely a shot in the dark.

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]
[select]
Re: Executing multiple mysql commands in a single execute() call.
by Corion (Patriarch) on May 30, 2012 at 09:16 UTC

    Are you sure that multiple SELECT statements make sense? Do all queries return the same data types in their columns? Maybe you just want a UNION statement?

    SELECT * FROM Games WHERE Team_ID=@t_id AND Date>'2012-05-01'
UNION
SELECT * FROM Games WHERE Team_ID=@t_id AND Date between '2012-04-01' 
+and '2012-05-01'
-- ...

    [download]

    Also, why do you want to use a variable there, instead of using a join clause? MySQL seems to have "eval" Google tells me, and the documentation also seems to support the idea of having a query return multiple resultsets. Maybe that solves your problem in a different way.

[reply]
[d/l]

      I'm not sure I understand your questions, and you may have misunderstood mine. I want to set t_id from one table and then use it in many following queries to other tables. I could join or use nested selects in all the following queries, but there's no reason to do all that work every time, when the value of t_id stays the same!

      I checked out eval and it seems to be more complicated than what I need. I just want to set a variable and use it, after all.

[reply]

        Why cannot you take the results from your SELECT Team_ID query, and put it into a Perl variable? You can then, without much trouble, use it in all your further queries by passing it to $sth->execute($team_id, $other, $params)

        What Corion was wondering about is, if you want to put multiple queries into one string and execute it, how would you read the results? How would you distinguish between the results of one query and the next one? (I don't think most database interfaces support multiple result sets.) How would jamming all the queries into a single string help readability?

        We'd like to see some code from you, especially on how you call the database. You are using prepared queries, aren't you?

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom