Zeos stored procedure "parameter mismatch" errors

[JD]

I have written some pascal code to restrict results retrieved from a Firebird 2.1 table based on a start date and an end date. I am having problems passing string parameters to a Firebird stored procedure that fetches these results using ZeosLib components. The stored procedure with string parameters works perfectly when executed from inside the Firebird database but it raises a "parameter mismatch" exception from inside my code.

Code: Select all

 with zsprRapports do
  begin
    Close;
    //ParamByName('DEBUT_PERIODE').AsString := '''01.01.2009''';
    //ParamByName('FIN_PERIODE').AsString := '''31.12.2009''';
    StoredProcName := 'UTILISATION_FONDS_SOLIDARITE';
    ParamByName('DEBUT_PERIODE').AsString := '''01.01.2009''';
    ParamByName('FIN_PERIODE').AsString := '''31.12.2009''';
    Open;
    First;

    // Compute the number of rows to be added to the StringGrid BEFORE filling the grid
    SGrid1.RowCount := RecordCount + SGrid1.FixedRows;

    // Fill the string grid with the results of the stored procedure
    while not EOF do
    begin
      for intGridCol := 0 to FieldCount - 1 do
      begin
        SGrid1.Cells[intGridCol + SGrid1.FixedCols, intGridRow + SGrid1.FixedRows] :=
          Fields[intGridCol].AsString;
      end;
      Next;
      Inc(intGridRow);
    end;

    Close;
  end;

I've tried using date parameters in the stored procedure & in my code but the result is the same. I believe the problem is my code or the zeos stored procedure component.

Any tips to help me get round this problem will be greatly appreciated.

[seawolf]

Every time I need to work with DateTime I use EncodeDate/EncodeTime/EncodeDateTime functions to pass parameters as string values.

Have you tried using that functions?

[JD]

Unfortunately, I'm still getting the same error. I took your advice & introduced the EncodeDate function in two seperate tests:

a) passing the parameters as dates to a stored procedure with date parameters

Code: Select all

    ParamByName('DEBUT_PERIODE').AsDate := EncodeDate(2009, 04, 01);
    ParamByName('FIN_PERIODE').AsDate := EncodeDate(2009, 04, 30);

b) passing the parameters as strings (by using EncodeDate and DateToStr functions) to a stored procedure with string parameters

Code: Select all

    ParamByName('DEBUT_PERIODE').AsString := DateToStr(EncodeDate(2009, 04, 01));
    ParamByName('FIN_PERIODE').AsString := DateToStr(EncodeDate(2009, 04, 30));

In both cases, I still got the parameter mismatch error in my application even though the stored procedure worked perfectly in the firebird database.

[klchin]

Hi,

Why not try to use DateToStr( EncodeDate(2001, 01, 01) ) to
find out the format needed by firebird.

[JD]

Hi,

Why not try to use DateToStr( EncodeDate(2001, 01, 01) ) to
find out the format needed by firebird.

I don't understand. I thought I already tried that with the second code snippet I posted above. Can you give me more details please?

[klchin]

Hi,

It might due to the date format, like
yyyy/mm/dd or dd/mm/yyyy.

The main reason I suggest to use 2001/01/01, is to
find out the problem really due to date format or not.

[JD]

Hi,

It might due to the date format, like
yyyy/mm/dd or dd/mm/yyyy.

The main reason I suggest to use 2001/01/01, is to
find out the problem really due to date format or not.

The problem is not the date format because I'm using the same date format in zeos queries without problems elsewhere.

For example when I change the code so that a query is run on the stored procedure instead of calling the stored procedure directly, it works! I could leave it like this but I prefer to transfer the processing to the server to speed things up hence my wanting to call the stored procedure directly.

[seawolf]

I've tried several tests, and at the moment I have no problems. Anyway I noted a couple of things:

- I tried using ParamByName or Params[x].asDate
- If you call several times that procedure, before call it try setting property procedureName := ''
and then procedureName := .. storedprocedure name.

Eventually try creating runtime that component.