Zeoslib Portal

Posted: **02.07.2019, 15:17**

I use a TZStoredProc component to call a stored procedure which returns a result set. The MySQL user has no privileges on the mysql-system-tables. TZStoredProc by default reads the mysql.proc-table on opening, but this of course results in "SELECT command denied to user '...'"

This can be avoided by setting TZConnection.UseMetadata to false. It can be switched on or of on an open TZConnection. So I have set up a small function "OpenStoredProcedure" which sets TZConnection.UseMetadata to false, opens the TStoredProc, and then switches UseMetadata to true again.

This appears to work for stored procedures with only one parameter, but fails in case of more parameters. The reason is that the function RegistereParamTypeAndName is not called from the method TZStoredProc.CreateStatement. This results in the array FParamNames in TZMysqlCallableStatement containing no names. The MySQL-server gets statements like :

Code: Select all

set @=1, @='test'
call storedprocedure(@,@)

instead of

Code: Select all

set @param1=1, @param2='test'
call storedprocedure(@param1, @param2)

MySQL accepts this, as @ seems to be a valid variable, but only the last assignment (@='test' in this example) has an effect.

I have solved this by modifiying TZStoredProc.CreateStatement like this :

Code: Select all


    if Supports(CallableStatement, IZParamNamedCallableStatement) and
      Assigned(FMetaResultSet) then
      if FMetaResultSet.Next then
        (CallableStatement as IZParamNamedCallableStatement).RegisterParamTypeAndName(
          I, FMetaResultSet.GetString(ProcColTypeNameIndex),
          Params[i].Name, FMetaResultSet.GetInt(ProcColPrecisionIndex),
          FMetaResultSet.GetInt(ProcColLengthIndex))
/////////////////////////////
/////// START   (FMetaResultSet is empty when the TZConnection.UseMetadata is false          
      else
        begin
        // If opened WITHOUT UseMetadata (connection property UseMetadata), then there will be no row in FMETARESULTSET.
        // I open stored procedures without UseMetadata in connection, because otherwise it needs mysql.proc.
        (CallableStatement as IZParamNamedCallableStatement).RegisterParamTypeAndName(
          I, '',
          Params[i].Name, 0,
          0);
        end
///////// END 
////////////////////////
  end;

I hope this is useful information for anyone.
Geert.

Zeoslib Portal

7.2.4-stable, MySQL, TZConnection.UseMetadata=false : TZStoredProc parameters not set

7.2.4-stable, MySQL, TZConnection.UseMetadata=false : TZStoredProc parameters not set