Multiple sql statements in query manager

  • Resolved gaetgodi

    (@gaetgodi)


    3 years, 3 months ago

    I can produce multiple sql statements but they do not seem to be in the same session.
    I can do
    set @table = ‘tablename’;
    /
    select * from @table
    /

    I get an error on the select, it is not recognizing my session variable.
    Is there another way to do this?
    I have created a faily complex pickleball ladder system with this kind of query using your data access system.
    My queries however were created externally, it would be nice to be able to create them here.

Viewing 2 replies - 1 through 2 (of 2 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi @gaetgodi,

    Great question! ??

    You can use user defined variables, but they cannot be used directly in an SQL statement as an identifier or as part of an identifier. Please visit this page for more information:
    https://dev.mysql.com/doc/refman/8.0/en/user-variables.html
    Your situation is explained in the third code example.

    Here is a solution that works with WP Data Access:

    SET @column = “columnname”
    /
    SET @table = “tablename”
    /
    SET @select = CONCAT(“SELECT “, @column, ” FROM “, @table)
    /
    PREPARE stmt FROM @select
    /
    EXECUTE stmt
    /

    If you replace the / with ; it works in other MySQL command line tools as well. I tested with version 5 and version 8.

    Let me know if you need further assistance.

    Good luck,
    Peter

    Thread Starter gaetgodi

    (@gaetgodi)

    Works very well, thank you, just have to couch it with prepare and execute. Should also add
    deallocate prepare stmt
    /

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Multiple sql statements in query manager’ is closed to new replies.