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

[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  [Select][+][-]

1. @dynamic_columns:=NULL;

[Zvoni]

Try:

Code: SQL  [Select][+][-]

1. DELIMITER $$
2. SET SESSION group_concat_max_len = 1000000;
3.  
4. SET @dynamic_columns = NULL;
5.  
6. SELECT GROUP_CONCAT(
7.     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
8. ) INTO @dynamic_columns
9. FROM rider_laps ORDER BY LENGTH( laps_cnt), laps_cnt;
10.  
11. SET @dynamic_sql = CONCAT(
12.     'SELECT rider_id, ', @dynamic_columns, ' FROM rider_laps GROUP BY rider_id order by MAX( Lap_Time ) DESC'
13. );
14.  
15. PREPARE stmt FROM @dynamic_sql;
16. EXECUTE stmt;
17. DEALLOCATE PREPARE stmt$$
18. 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

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.

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 ?

[Zvoni]

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 ?

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][+][-]

1. DELIMITER $$
2.  
3. CREATE PROCEDURE MyStoredProcedure(OUT dynamic_sql VARCHAR(65535))  /* Or LONGTEXT?? */
4. BEGIN
5. SET SESSION group_concat_max_len = 1000000;
6.  
7. SET @dynamic_columns = NULL;
8.  
9. SELECT GROUP_CONCAT(
10.     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
11. ) INTO @dynamic_columns
12. FROM rider_laps ORDER BY LENGTH( laps_cnt), laps_cnt;
13.  
14. SET dynamic_sql = CONCAT(
15.     'SELECT rider_id, ', @dynamic_columns, ' FROM rider_laps GROUP BY rider_id order by MAX( Lap_Time ) DESC'
16. );
17.  
18. END$$
19.  
20. DELIMITER ;

Code: Pascal  [Select][+][-]

1. SQLQuery1.SQL.Text := 'call `MyStoredProcedure`(@Dynamic_sql)';
2. SQLQuery1.ExecSQL;
3. SQLQuery1.Close;
4. SQLQuery1.SQL.Text := 'SELECT @Dynamic_sql';
5. SQLQuery1.Open;

[phctech]

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]

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][+][-]

1. Var s:String;
2. ....
3.     SQLQuery1.SQL.Text := 'call `MyStoredProcedure`(@Dynamic_sql)';
4.     SQLQuery1.ExecSQL;
5.     SQLQuery1.Close;
6.     SQLQuery1.SQL.Text := 'SELECT @Dynamic_sql';
7.     SQLQuery1.Open;
8.     s:=SQLQuery1.Fields[0].AsString;
9.     SQLQuery1.Close;
10.     SQLQuery1.SQL.Text:=s;
11.     SQLQuery1.Open;