UniStoredProc (MySQL) return parameter error

[stevel]

Using latest UniDAC, and Delphi XE7 Update 1, with MySQL database server ver 5.6

I have a stored procedure that returns a parameter. On my datamodule, I have TUniStoredProc, and the generated SQL code in the component looks like this:

CALL spfundsrc_I(:Pdocumenttitle, :Pdocument_id, @NewID); SELECT CAST(@NewID AS SIGNED) AS '@NewID'

This is the Delphi code in the application:

Code: Select all

begin
  // :Pdoc_filenm, :Pdoc_data, @NewID
  datamod.uspspdocument_I.ParamByName('Pdoc_filenm').AsString := ExtractFileName(ADocumentFile);
  datamod.uspspdocument_I.ParamByName('Pdoc_data').LoadFromFile(ADocumentFile, ftBlob);
  datamod.uspspdocument_I.Prepared := True; // <--- Error occurs here!
  datamod.uspspdocument_I.ExecProc;

  Result := datamod.uspspdocument_I.ParamByName('NewID').AsInteger;
end;

This is the error message:

---------------------------
Debugger Exception Notification
---------------------------
Project XXXXXXXX.exe raised exception class EMySqlException with message '
#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT CAST(@NewID AS SIGNED) AS '@NewID'' at line 1'.
---------------------------
Break Continue Help
---------------------------

This is the MySQL stored procedure:

Code: Select all

CREATE PROCEDURE spdocument_I(Pdoc_filenm VarChar(255), Pdoc_data BLOB, OUT NewID Integer)
  NO SQL
BEGIN
  INSERT INTO tbdocument (
    doc_filenm,
    doc_data)
  VALUES (
    Pdoc_filenm,
    Pdoc_data);
  SET NewID = LAST_INSERT_ID();
END
/

Hope you can suggest how I can call this stored procedure and also get the NewID return parameter value after its completed?

[ViktorV]

MySQL doesn't support return of out parameters of stored procedures. To avoid this restriction, UniDAC generates code, that allows to return out parameters. At such a query, MySQL can't prepare such code on the server. Therefore you shouldn't call the Prepare method for stored procedures that have out parameters.