Calling a prepared statement

[NoComprende]

Suppose a prepared statement called Stmnt exists on the server and has two parameters. I know it can be called using the C MySQL API

set @x=SomeValue,@y=SomeOtherValue;
execute Stmnt using @x,@y;

but is there a way I can call it using a TMyQuery component such that it receives the result set?

[ViktorV]

To get the result of execution of an existing prepared statement, you can use the following code:

Code: Select all

  MyQuery.SQL.Text := 'SET @X = :X; ' +
    'SET @Y = :Y; ' +
    'EXECUTE stmt USING @X, @Y;';
  MyQuery.ParamByName('X').Value := SomeValue;
  MyQuery.ParamByName('Y').Value := SomeOtherValue;
  MyQuery.Open;

[NoComprende]

Thanks for the reply Victor. I tried the following

Using the MySQL query browser I executed the following code

prepare stmnt from 'select rID from Runner where hID=? order by rID';

I then entered the following in a TMyQuery SQL tab

set @x:=:x; execute stmnt using @x;

After setting the parameter to a numeric value in the Parameters tab and clicking the 'Execute' button I keep getting the message "#42000You have an error in your SQL syntax; check the ..... for the right syntax to use near 'execute stmnt using @x' at line 1."

[NoComprende]

Assuming the silence means it cannot be done then, if I call the prepared statement via the c api is there any way I can assign the returned result set to a TMyQuery?

[ViktorV]

To solve the issue, try to replace the code in your sample

Code: Select all

set @x:=:x; execute stmnt using @x;

with the following

Code: Select all

set @x = :x; execute stmnt using @x;

If this doesn't solve the issue, please specify the version of the MySQL server you are connecting to and send a small sample to demonstrate the issue to viktorv*devart*com
Note: a prepared statement created in one session is not available to other sessions, so you can't access the prepared statement generated in MySQL query browser from TMyQuery. See more details about prepared statements in MySQL documentation: https://dev.mysql.com/doc/refman/5.1/en/prepare.html

[NoComprende]

Hi Victor, I'm using MyDAC 5.90.0.60 and MySQL 5.1.

I got the same error message when I tried the replacement code.
I tried it with a statement with no parameters i.e.

prepare stmnt from 'select * from SomeTable';

but when I tried the code

execute stmnt;

in a TMyQuery I got the message

"#HY000This command is not supported in the prepared statement protocol yet."

Presumably I need to update either my MyDAC components or my version of MySQL?

[ViktorV]

Unfortunately, we couldn't reproduce the problem following the steps you described. Please update MyDAC to the latest version (8.5.14) - and if this doesn't solve the issue, try to execute these queries on another MySQL server version. In addition, make sure you are connecting to the MySQL server in Direct Mode.

[NoComprende]

Thanks Victor.

[ViktorV]

Please let us know whether the issue is resolved?

[NoComprende]

You were right all along Victor. The reason it wasn't working for me must have been down to setting the prepare in MySQL's query browser.

I used a TMyCommand for the prepare and then a TMyQuery to execute it and your original example all worked as expected. Thanks for your help.

[NoComprende]

I've scrubbed this post as after posting it I discovered that the '#HY000This command is not supported in the prepared statement protocol yet' error messages were being caused by having the TMyQuery AutoPrepare option set to true. The TMyQuery was attempting to prepare a call to a prepared statement.

[ViktorV]

The point is that MySQL cannot prepare such SQL statement. You cannot use API calls for preparing the PREPARE, EXECUTE, or DEALLOCATE PREPARE statements. For more information, please refer to http://dev.mysql.com/doc/refman/5.1/en/ ... ments.html
To avoid the problem, you should set the TMyQuery.AutoPrepare property to False.
If there is anything else I can help you with, please contact me.

[NoComprende]

Thanks Victor. I didn't actually try preparing the 'execute stmnt', it was just that I had set the AutoPrepares in the queries (when I was tackling the problem by constantly changing the SQL->Text) and forgot to set AutoPrepare to false after you gave me the solution.

This is probably a MySQL question rather than a MyDAC one but I'd be grateful if you could help.

I've got a MySQL function that executes a complex select and then returns the result throught the following construct

select Answer from ..complex query .. into S;
return S;

Suppose I
prepare ComplexQuery from 'complex query text';

How could I get at the result inside the MySQL function?

execute ComplexQuery using @x,@y into S;

doesn't work.

[ViktorV]

This question is not related to MyDAC functionality. Please contact MySQL server developers for a solution or ask this question on specialized forums.

[NoComprende]

Got the answer on the MySQL forum.

For example

prepare Stmnt from 'select Col from Tbl where PrimaryColumn=? into @Answer';

function would have a single parameter that corresponded to the PrimaryColumn value and the code would be along the lines of

set @x=FunctionParamValue;
execute Stmnt using @x;
return @Answer;