DelphiFAQ Home Search:

'Nonsense' error message 'parameter mismatch for procedure'

 

comments1 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!
    Content-type: text/html

    Comments:

    2005-11-28, 05:31:03
    Zaffo80 from Italy  
    rating
    I found what i was looking for

     

     

    NEW: Optional: Register   Login
    Email address (not necessary):

    Rate as
    Hide my email when showing my comment.
    Please notify me once a day about new comments on this topic.
    Please provide a valid email address if you select this option, or post under a registered account.
     

    Show city and country
    Show country only
    Hide my location
    You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
    Please type in the code:

    Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity.
    The owner of this web site reserves the right to delete such material.

    photo Add a picture: