MySQL workbench executes script but errors when run for an SQL Query
lazarus / 16-Databases

----------------------------------------------------------------------
phctech · 2024-08-11 09:08

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 · 2024-08-11 12:27

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 · 2024-08-11 13:35

It's like in Pascal. You should write:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
@dynamic_columns:=NULL;

----------------------------------------------------------------------
Zvoni · 2024-08-12 06:15

Try:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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
QuoteTSQLQuery 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.
Everything said: Probably best bet is TSQLScript, but i have no idea
if that can be used to RETURN data

----------------------------------------------------------------------
phctech · 2024-08-12 09:25

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 ?

----------------------------------------------------------------------
Zvoni · 2024-08-12 09:46

Quote from: phctech on August 12, 2024, 11:25:00 amHave 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 ?
MySQL supports stored procedures.
Instead of trying to execute your original code as a Query, you can
put it into a stored Procedure with an out-parameter (@dynamic_sql
being the out-parameter)
You would only need the code until your PREPARE-Statement (Prepare and
everything later not needed).
Then you can call that stored procedure with your TSQLQuery-Instance.
But IIRC, it's a bit of a hassle to grab the Value of the OUT-
Parameter.
Maybe someone more experienced with MySQL than myself can shed some
light

EDIT: Found here:
https://forum.lazarus.freepascal.org/index.php?topic=46658.0
untested though
On the Server (No idea about Syntax. I'm guessing here):
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i DELIMITER
$$ CREATE PROCEDURE MyStoredProcedure(OUT dynamic_sql VARCHAR(65535))
 /* Or LONGTEXT?? */BEGINSET 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'); END$$ DELIMITER ;
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1.SQL.Text := 'call `MyStoredProcedure`(@Dynamic_sql)';SQLQuer
y1.ExecSQL;SQLQuery1.Close;SQLQuery1.SQL.Text := 'SELECT
@Dynamic_sql';SQLQuery1.Open;

----------------------------------------------------------------------
phctech · 2024-08-13 11:40

I ended up getting it to work. May not be the prettiest

My solution
Add Zeos Access tools

Add with appropriate settings connecting each other
ZConnection
ZStoredProc - to SP from Zvoni ( thanks )
TDataSource
DbGrid2

Code ( testing on buttonClick )
  zstoredproc1.ExecProc;
  zstoredproc1.open;
  dbgrid2.DataSource.DataSet.First;
  SQLQ_Result.SQL.Text :=
dbgrid2.DataSource.DataSet.Fields[0].asString;
  SQLQ_Result.Open;    

I could not get a result with Zvoni's code only returned the SQL not
the result
I could not access the zstoredproc1.datasourse.dataset.first without
an error
So displayed the result in a 2nd grid ( hidden ) then passed the SQL
to the Query.

At least it works but will try and find a better solution

Thank's for the help guys

----------------------------------------------------------------------
Zvoni · 2024-08-13 11:45

Quote from: phctech on August 13, 2024, 01:40:13 pm
I could not get a result with Zvoni's code only returned the SQL not
the result

*sigh*.... you get an SQLString back from that code

[SARCASM ON]
Whatever you could do with that SQL-String????
Maybe "Close" your SQLQuery1?
Maybe assign THAT SQL-String to SQLQuery1.SQL.Text??
Maybe then "Open" SQLQuery1 again, and then look at the RESULT????
[SARCASM OFF]
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Var
s:String;....    SQLQuery1.SQL.Text := 'call
`MyStoredProcedure`(@Dynamic_sql)';    SQLQuery1.ExecSQL;   
SQLQuery1.Close;    SQLQuery1.SQL.Text := 'SELECT @Dynamic_sql';   
SQLQuery1.Open;    s:=SQLQuery1.Fields[0].AsString;   
SQLQuery1.Close;    SQLQuery1.SQL.Text:=s;    SQLQuery1.Open;