TADOCommand.Parameters.GetParamList returns empty

[BrianGooch]

Hi

Delphi 2009, Firebird 1.5, ODBC Driver Firebird 2.0.0 on win32.

I have the following TADOCommand set as
cmdType = cmdStoredProc
cmdText ='StoredProcName'
ConnectionString or Connection == Firebird/Interbase(r) Driver..... etc
Provider = MSDASQL

Connection works fine and the results can be displayed via an ADODataset.RecordSet in a DBGrid.

The StoredProc has parameters 1 x input and 40 x output.
I can create the 1 x input parameter using a variable say P1 = TParameter.Create(Parameters) and this works fine.

In order to avoid having to manually create the Parameters Collection - a lengthy coding procedure using the above P1 example, I have tried using the Parameters.GetParamList but this always returns empty.

Am I not using the components and methods correctly, or is it that the combination of MSDASQL provider and the Firebird ODBC 2.0 driver are not permitting the reading of the StoredProcs' parameter list as located on the Server metadata?

 

[DjangMan]

Call:

Parameters.Refresh;

 

[BrianGooch]

DjangMan

Thankyou for your response. Unfortunately it does not resolve the issue. A code fragment follows. A number of combinations have been tried as can be seen from the commenting out - the errors have also been noted.
This is test code only.

try
with AConnServer do begin
Connected:= True;
BeginTrans;
end;
AConnLocal.Connected:= True;
with ServerCmd do begin
CommandType:= cmdStoredProc;
CommandText:= 'LoadSale';
Parameters.Clear;
// Ok so far!
// next statement fails @ runtime : EaccessViolation ; try block aborts
{Parameters.CreateParameter(
'docid', ftInteger, pdInput, 2147483647, 113);}
// next statement fails @ runtime: EaccessViolation ; try block aborts
{with Parameters.AddParameter do begin
Name:= 'docid';
DataType:= ftInteger;
Direction:= pdInput;
Value:= 113;
end;}
// next statement works
P[0]:= TParameter.Create(Parameters);
with P[0] do begin
Name:= 'docid';
DataType:= ftInteger;
Direction:= pdInput;
Value:= 113;
end;
Prepared:= True;
// next statement produces empty result
// Parameters.GetParamList(List, Names);
// next statement produces @ runtime : EAccessViolation
// Parameters.Refresh;
// next statement records only parameter P[0]: docid 113
with Parameters do
for i:= 0 to Count - 1 do
LB1.Items:= Items.Name+' '+IntToStr(Items.Value);

L4.Caption:= P[0].Name +' '+ IntToStr(P[0].Value); // correct
// final statement correct complete row with docid = 113
Retrieved.RecordSet:= Execute;
// Error @ runtime : List out of bounds (1)
****** end of code fragment

finally somewhere down here

Kind Regards,
Brian Gooch

 

[DjangMan]

I don't think you should call Parameters.Clear. This is the code that I used to connect to a stored procedure in MSSQL using a TADOStoredProcedure that is already connected to the TADOConnection:

  with ADOStoredProc do
  begin
    ProcedureName := 'CreateNewDS_JOURNAL';
    Parameters.Refresh;
    Parameters.ParamByName('@CHART_CODE').Value := aChartCode;
    Parameters.ParamByName('@THE_DATE').Value := aJournalDate;
    Parameters.ParamByName('@HAS_ENTRIES').Value := 0;
    Parameters.ParamByName('@THE_DATEI').Value := DateToDateI(aJournalDate);
    Parameters.ParamByName('@EXPORT_COUNT').Value := 0;
    Parameters.ParamByName('@BATCH_PENDING').Value := 0;
    Parameters.ParamByName('@ADJ_PENDING').Value := 0;
    Parameters.ParamByName('@IS_BALANCED').Value := 0;
    ExecProc;
  end;

 

[BrianGooch]

DjangMan

Thanks, and for the example.
Test Results:
Just using the P[0] parameter docid, 113:
remove Parameters.Clear : data retrieved Ok.
also apply CreateParameters : empty result
then apply Parameters.Refresh: data retrieval incorrect

The incentive behind the GetParamList arises from not wishing to specify the params in the app code. We are going from one version of a DB to another (and to Firebird) and as there are several hundred SPs to do, our concern is the potential for error in the manual operation. Making it automatic would enhance our productivity, and improve the clarity of the code since the params would all be in one place - the database.

Any ideas are welcome.

Kind Regards,
Brian Gooch.