in reply to Perl DBI not storing @prev_value in query

Have you read the documentation for DBD::mysql? It does not mention MySQL user-defined variables so I doubt that it supports them.

Replies are listed 'Best First'.
Re^2: Perl DBI not storing @prev_value in query
by pryrt (Abbot) on Jan 24, 2018 at 18:07 UTC

    A few months back, I was able to get some proof of concept code working. See my thread, especially the summary code in this post. I don't know whether the things I learned in that set of experiments will help, but I was able to make use of the @dmvar user-defined variable; I don't think it had to be inside of the DebugMe mySQL function to work... that's just specifically what I was trying to get at for that one. But I thus know that user-defined variables aren't out of the question in DBD::mysql.

    (caveat: not an SQL or mySQL expert)

      I haven't used MySQL for many years now (not since I left Lacuna Expanse any way) so I'm by no means an expert either.

      I just don't know enough about MySQL's user-defined variables. I would totally expect them to work inside of stored functions. If they work as a part of normal queries as well then it would useful if the DBD::mysql documentation at least mentioned them.

Re^2: Perl DBI not storing @prev_value in query
by Beaker (Beadle) on Jan 24, 2018 at 18:04 UTC
    hurray I found a solution, by adding : , (SELECT @prev_discussion:=0) pd, (SELECT @recent_post_rank:=0) rpr as follows:
    SELECT x.group_discussion_id, x.comment, x.date_posted, x.username, x.realname, TO_DAYS(NOW()) - TO_DAYS(x.date_posted) AS date_posted_days FROM ( SELECT gdp.group_discussion_post_id, gdp.group_discussion_id, gdp.comment, gdp.date_posted, m.username, m.realname, @recent_post_rank := IF ( @prev_discussion = gdp.group_discussion_id, @recent_post_rank + 1, 1 ) AS recent_post_rank, @prev_discussion := gdp.group_discussion_id FROM group_discussion_posts gdp JOIN members m ON m.memberid = gdp.memberid, (SELECT @prev_discussion:=0) pd, (SELECT @recent_post_rank:=0) rpr WHERE = 1 AND gdp.reply_to_post_id != 0 AND gdp.group_discussion_id IN (1, 2, 3) ORDER BY gdp.group_discussion_id DESC, gdp.group_discussion_post_id DESC ) x WHERE recent_post_rank <= 3
    Hopefully this might help someone else in the future. I'm not sure exactly why it works, but it creates a subquery to select each user defined variable, and an alias for each (which is never used).
      I'm not sure exactly why it works

      It works because the user defined variables need to be initialized. Because variables are session specific they are 'retained' when using MySQL workbench. You should notice that your original query does not work in MySQL workbench on the first run but then works on subsequent runs. Using DBD you are creating a new session each time so variables never get initialized. You could use your original query by adding a set command before the execute

      $dbh->do('SET @recent_post_rank := 0, @prev_discussion := 0');
Re^2: Perl DBI not storing @prev_value in query
by Beaker (Beadle) on Jan 24, 2018 at 17:18 UTC
    I have a statement including this: @row_num := @row_num + 1 AS rownum elsewhere in my code (in a $dbh->selectrow_array) and it works as expected, so it seems to be that it allows variables but doesn't not "store" them. Strange, as I thought DBI just sends the query to MySQL, I didn't think it needed to understand every aspect of the query?
      This is tricky because I have refactored with MySQL query as my last version was very inefficient, taking 5 seconds + to execute. This one is super fast but it doesn't look like I can use it if DBI won't support it, so I'm stuck now.