SQL Execute with multible querys



  • I would like to execute a transaction with several querys via SQL Execute query. The structure and function is legitimate and works everywhere but not in BAS.

    As an example, I would like to output an IP that has not been used for at least 10 seconds, in the query I would like to lock the column until it is updated. In between, I still have the IP output via Select.

    START TRANSACTION;
    set @ip = (SELECT ip FROM proxy WHERE (UNIX_TIMESTAMP() - acc_last_use) > 10 ORDER BY acc_last_use LIMIT 1 FOR UPDATE);
    SELECT @ip;
    UPDATE proxy SET acc_last_use = UNIX_TIMESTAMP() WHERE ip = @ip;
    SET @ip = NULL;
    COMMIT;
    

    does anyone have an idea how I can solve this? It's about not outputting double colums with multithread.



  • @shadowrob, if you have a MySQL or MariaDB database, download the latest version of the module: SQL.zip, and in the additional settings of the "Setup connection" action for the dialect options, specify this { multipleStatements: true } value, with the field type expression

    b9d79dbd-c6e1-47c6-aafd-13ef83acfcce-image.png



  • The server is MariaDB, thank you, I will test this immediately.



  • @GhostZ I have now tested this and it works.
    Only now I have the problem that the output it writes only corresponds to this:

    Thread #1 : [object Object], [object Object], [object Object], [object Object].
    

    the number of objects always corresponds to the number of queries
    but when i start the sql debug i see that it returns the correct answer

    Executing (default): START TRANSACTION; set @ip = (SELECT ip FROM proxy WHERE (UNIX_TIMESTAMP() - acc_last_use) > 10 ORDER BY acc_last_use LIMIT 1 FOR UPDATE); SELECT @ip; UPDATE proxy SET acc_last_use = UNIX_TIMESTAMP() WHERE ip = @ip; SET @ip = NULL; COMMIT;
    
    
    Result: [[{"fieldCount":0,"affectedRows":0,"insertId":0,"info":"","serverStatus":11,"warningStatus":0},{"fieldCount":0,"affectedRows":0,"insertId":0,"info":"","serverStatus":43,"warningStatus":0},[{"@ip":"XXX.XXX.XXX.XXX"}],{"fieldCount":0,"affectedRows":1,"insertId":0,"info":"Rows matched: 1 Changed: 1 Warnings: 0","serverStatus":43,"warningStatus":0,"changedRows":1},{"fieldCount":0,"affectedRows":0,"insertId":0,"info":"","serverStatus":11,"warningStatus":0},{"fieldCount":0,"affectedRows":0,"insertId":0,"info":"","serverStatus":2,"warningStatus":0}],[{"fieldCount":0,"affectedRows":0,"insertId":0,"info":"","serverStatus":11,"warningStatus":0},{"fieldCount":0,"affectedRows":0,"insertId":0,"info":"","serverStatus":43,"warningStatus":0},[{"@ip":"XXX.XXX.XXX.XXX"}],{"fieldCount":0,"affectedRows":1,"insertId":0,"info":"Rows matched: 1 Changed: 1 Warnings: 0","serverStatus":43,"warningStatus":0,"changedRows":1},{"fieldCount":0,"affectedRows":0,"insertId":0,"info":"","serverStatus":11,"warningStatus":0},{"fieldCount":0,"affectedRows":0,"insertId":0,"info":"","serverStatus":2,"warningStatus":0}]]
    


  • @shadowrob, use the "To string" action, before outputting the result to the log



  • @GhostZ you are the best, thank you very much.


Log in to reply