DelphiFAQ Home Search:
General :: Programming :: Delphi :: Database
Database development with Delphi. Includes issues with BDE, ADO and InterBase.

Articles:

This list is sorted by recent document popularity (not total page views).
New documents will first appear at the bottom.

Only the 40 most recently viewed articles are shown.
You can see the full list here.

Featured Article

'Nonsense' error message 'parameter mismatch for procedure'

Question:
When I called a stored procedure from a trigger, I got a seemingly wrong error message
'invalid request BLR at offset yyy, parameter mismatch for procedure XXX'
but the passed parameters were fine.

Answer:
The solution is to handle the return value.
A stored procedure that returns one or more values per row in the result set cannot be called with execute procedure ... Instead you have to select those returned values into some variables (of the correct type, of course.. but a wrong type would cause a compilation error).
Also remember if there is a chance to have more than one row returned, then you need to put a FOR statement in front of it, as the 3rd example shows. If you suspect that there could be several rows returned but you only care for the first one, then

  • make sure that the results come in a defined order
    or
  • modify your query and use the MIN() operator to make sure that only one row comes back.

    See the sample code below..

    // this one does not work: 
     //  execute procedure update_petrochemical_feedstocks (1800024, 2001);
     
     
     // this one does work:
     declare variable v_sd integer;
     declare variable v_fp integer;
     declare variable v_ar integer;
     begin
       select * from update_petrochemical_feedstocks (1800024, 2001) 
       into :v_sd, :v_fp, :v_ar;
     end
     
     // multiple rows? then you neeed to use FOR
     begin
       for
         select * from update_petrochemical_feedstocks (1800024, 2001) 
         into :v_sd, :v_fp, :v_ar
       do; 
     end
     
     // force it to be only one row with the MIN() operator
     begin
       select MIN(sd_id)
       from update_petrochemical_feedstocks (1800024, 2001) 
       into :v_sd;
     end
     
    You don't like the formatting? Check out SourceCoder then!
  • Generated 16:01:37 on Mar 24, 2019