Forum > Databases

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

<< < (2/2)

Zvoni:

--- Quote from: phctech on August 12, 2024, 11:25:00 am ---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 ?

--- End quote ---
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  [+][-]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 $$ 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  [+][-]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";}};} ---SQLQuery1.SQL.Text := 'call `MyStoredProcedure`(@Dynamic_sql)';SQLQuery1.ExecSQL;SQLQuery1.Close;SQLQuery1.SQL.Text := 'SELECT @Dynamic_sql';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:

--- 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


--- End quote ---
*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  [+][-]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";}};} ---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;

Navigation

[0] Message Index

[*] Previous page