Zeoslib Portal

Posted: **04.02.2021, 19:51**

I started to experiment with stored procedures. Is there any reason why this code works:

 ZStoredProc1.Connection := OracleConnection;
 ZStoredProc1.Params.CreateParam(ftLongWord, 'pGlobalID', ptResult);
 ZStoredProc1.StoredProcName := 'GetNextGlobalID';
 ZStoredProc1.Connection.Connect;
 ZStoredProc1.ExecProc;

and this one throws the exception "Cannot retrieve resultset data"?

Code: Select all

 ZStoredProc1.Connection := OracleConnection;
 ZStoredProc1.StoredProcName := 'GetNextGlobalID';
 ZStoredProc1.Params.CreateParam(ftLongWord, 'pGlobalID', ptResult);
 ZStoredProc1.Connection.Connect;
 ZStoredProc1.ExecProc;

Posted: **05.02.2021, 23:22**

I have a feeling that this is related to this bugfix.
The below code worked fine in my test application but throws the same "cannot retrieve resultset" exception in my real application.
TZAbstractRODataset.ExecSQL -> TZStoredProc.SetStatementParams says that Params.Count is 9, however I have only one parameter.

Exception is raised in TZAbstractRODataset.ExecSQL -> TZAbstractRODataset.RetrieveParamValues -> TZAbstractCallableStatement.IsNull -> TZAbstractPreparedStatement.IsNull -> TZAbstractPreparedStatement.ParamterIndex2ResultSetIndex. The cycle still sees the 9 parameters, retrieves the first one without issues but throws the exception in the second round... which is expected, as we have only one parameter.

I'll see if I can dig deeper.

Posted: **05.02.2021, 23:57**

WRONG!!!

To answer my original question:
The setter method of StoredProcName triggers a metadata lookup. This lookup will automatically collect and update the .Params property of the TZStoredProc object. If you add parameters first and then update the procedure name, your previously created parameters will be omitted.

I also issued a pull request to fix the schema / catalog consideration, see here.

Zeoslib Portal

TZStoredParam anomaly

TZStoredParam anomaly

Re: TZStoredParam anomaly

Re: TZStoredParam anomaly