'Nonsense' error message 'parameter mismatch for procedure'

Delphi
.NET (2)
Database (72)
Delphi IDE (90)
Network (39)
Printing (3)
Strings (12)
VCL (83)
Windows with Delphi (280)

Exchange Links

About this site

Links to us

See also:

Adding arguments to a stored procedure in InterBase

'Nonsense' error message 'parameter mismatch for procedure'

1 comments. Current rating: 4 stars

(1 votes). Leave comments and/ or rate it.

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!

Comments:

2005-11-28, 05:31:03

Zaffo80 from Italy

I found what i was looking for


	Search: