Other [solved] MySQL - using SET @var with ExecQuery2

[udg]

Edit: read post#2 for the solution. I left this post unaltered to let you follow my findings

Hi all,

in the context of a complex query using dynamic data, I can't find a proper way to use ExecQuery2 with a statement that MySQL do accept when entered directly.

'Original statement:
SET @testid := 1;
SET @table = (SELECT Agt.`mytablename` FROM psm.ag_agetyp as Agt
               LEFT JOIN psm.ag_agende As Agd on Agd.fk_agetyp_id = Agt.id
               WHERE Agd.id = @testid );
SELECT @table;

'Using ExecQuery2:
Cursor = sql1.ExecQuery2($"SET @testid := ? ;
                                      SET @table = (SELECT Agt.`mytablename` FROM psm.ag_agetyp as Agt
                                     LEFT JOIN psm.ag_agende As Agd on Agd.fk_agetyp_id = Agt.id
                                     WHERE Agd.id = @testid );
                                     SELECT @table;  "$, _
                                     Array As Object(AGData.Get("agnd_id")))

This is trapped by a try..catch showing the "You have an error in your SQL.." error.
I guess it depends on how I try to pass the parameter when setting @testid, but how am I supposed to do it?

Note: I originally tried to pass the parameter directly to the WHERE clause; same error.
Note2: as said, this is just a fragment of the whole statement. What I really do is to read a table name from ag_agetyp based on an ID sent as a parameter, store that name in @table and then use it in a different query in order to read data fom that table, dinamically chosen.

TIA

 

[udg]

I'm reading about CreateCallStatement. Maybe I found the answer to my question.

Edit: it works!!!

Dim query As String = "CALL psm.GetAppData(?,?)"
Dim obj As Object = sql1.CreateCallStatement(query, Array As Object(AGData.Get("agnd_id"), AGData.Get("app_date")))
Cursor = sql1.ExecCall(obj)

So, the solution was to write a stored procedure (GetAppData) where all the needed statements are executed one after the other.
In my case, one statement dynamically retrieves the name of a table based on the passed in parameters, then a second statement uses that table to retrieve some data along with data from other tables, all based on the passed in parameters.

udg