Forum > Databases

MySQL workbench executes script but errors when run for an SQL Query

(1/2) > >>

phctech:
This code works in mySQL work bench



SET SESSION group_concat_max_len = 1000000;

SET @dynamic_columns = NULL;

SELECT GROUP_CONCAT(
    DISTINCT CONCAT('MAX(CASE WHEN laps_cnt = "', laps_cnt, '" THEN TIME_FORMAT(lap_time, "%H %i %s") END) AS "', laps_cnt , '"') order by laps_cnt
) INTO @dynamic_columns
FROM rider_laps order by length( laps_cnt), laps_cnt;

SET @dynamic_sql = CONCAT(
    'SELECT rider_id, ', @dynamic_columns, ' FROM rider_laps GROUP BY rider_id order by MAX( Lap_Time ) DESC'
);

PREPARE stmt FROM @dynamic_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;



But if i cut and paste into the SQLQuery SQL param it produces an error

'Error executing query: you have an error in your SQL syntax: check the manual that corresponds to your mySQL server version for the right syntax to use near 'SET @dynamic_columns = NULL;

How do I make it work ?

Thaddy:
I can't immediately see it but the error is reported from MySQL, not from the Pascal code.
It may be that some pascal string type interfears, so check your strings for '" and "' sequences. (quote, double quote and double quote, quote pairs)
It may be unrelated in this case, but that happens to me all the time.

jcmontherock:
It's like in Pascal. You should write:

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---@dynamic_columns:=NULL;

Zvoni:
Try:

--- Code: SQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---DELIMITER $$SET SESSION group_concat_max_len = 1000000; SET @dynamic_columns = NULL; SELECT GROUP_CONCAT(    DISTINCT CONCAT('MAX(CASE WHEN laps_cnt = "', laps_cnt, '" THEN TIME_FORMAT(lap_time, "%H %i %s") END) AS "', laps_cnt , '"') ORDER BY laps_cnt) INTO @dynamic_columnsFROM rider_laps ORDER BY LENGTH( laps_cnt), laps_cnt; SET @dynamic_sql = CONCAT(    'SELECT rider_id, ', @dynamic_columns, ' FROM rider_laps GROUP BY rider_id order by MAX( Lap_Time ) DESC'); PREPARE stmt FROM @dynamic_sql;EXECUTE stmt;DEALLOCATE PREPARE stmt$$DELIMITER ;
EDIT: I think i would put that into a SP returning the SQL-String instead of executing it there.
Then it would be a simple TSQLQuery.Open

EDIT2: An alternative might be TSQLScript
https://www.freepascal.org/docs-html/fcl/sqldb/tsqlscript.html

EDIT3: Here's actually the explanation why his initial try failed: https://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.html

--- Quote ---TSQLQuery exposes the properties and some methods introduced in TCustomSQLQuery. It encapsulates a single SQL statement: it implements all the necessary #fcl.db.TDataset functionality to be able to handle a result set. It can also be used to execute a single SQL statement that does not return data, using the TCustomSQLQuery.ExecSQL method.
--- End quote ---

Everything said: Probably best bet is TSQLScript, but i have no idea if that can be used to RETURN data

phctech:
Have tried the above suggestions with no luck

Thanks anyway

I am thinking that maybe the ; indicates the end of the SQL statement so the TSQLQuery may not be suitable. I will check maybe the TSQLScript or other options

ZVoni what is it you ment by putting it into an SP ?

Navigation

[0] Message Index

[#] Next page