At 12:14 PM 9/08/2005 +1000, you wrote:

>Hi to all,
>
>I am reposting this due to not receiving a response from the list.
>
>Has anyone got any suggestions on how to resolve this.
>
>The actual SP in question compiles OK but any attempt at restore and or
>compiling any SP that uses the SP in question fails.
>
>And yet the application that calls the SP along with any other SP
>contained in the DB works without any problem.
>
>---------- Original Post ---------------------------
>
>Can anyone tell me what this error message means and how do I fix the
>problem.
>
>Thanks heaps in advance.
>
>the error message is reported during a restore of the database
>
>The restore is done using Database WorkBench 2.17.
>
>gbak: restoring index FK_CLIENT_REP_LETTERS
>gbak: restoring index FK_COMPANY_LETERS
>ERROR: invalid request BLR at offset 917
>parameter mismatch for procedure AUTOTEXT_TOWN_DATA
>
>----- the stored procedure in question is following
>
>SET TERM ^^ ;
>CREATE PROCEDURE AUTOTEXT_TOWN_DATA (
> DETECT_ID BigInt,
> STYLE_REQ SmallInt,
> TY_REQ SmallInt)
>returns (
> RESULT_STR VarChar(150) CHARACTER SET ASCII)
>AS
>
>declare variable F_Town VarChar(80);
>declare variable F_State VarChar(80);
>declare variable F_Country VarChar(80);
>declare variable F_PCode Integer = 0;
>declare variable F_Spacer VarChar(5) = ', ';
>declare variable Temo_ID BigInt = 0;
>begin
>
>/* get the town ID Number */
> if (TY_REQ = 1) then
> begin
> select TOWNID from COMNAMES where DATATYPE=14364 into :Temo_ID;
> end
> else if (TY_REQ = 2) then
> begin
> select TOWN_ID from BUSDETAILS where DETECTID = :DETECT_ID into
>:Temo_ID;
> end
> else if (TY_REQ = 3) then
> begin
> select TOWNID from CONTACTS where DETECTID = :DETECT_ID into :Temo_ID;
> end
> else if (TY_REQ = 4) then
> begin
> select MAILTOWNID from CONTACTS where DETECTID = :DETECT_ID into
>:Temo_ID;
> end
>
>/* get the town components */
> SELECT ITEM001, ITEM002, ITEM003,INT001
> FROM D_LIST_HEAD where DETECTID=:Temo_ID
> into :F_Town, :F_State, :F_Country,:F_PCode;
>/* output the result string */
> if (STYLE_REQ = 1) then RESULT_STR = :F_Town;
> if (STYLE_REQ = 2) then RESULT_STR = :F_Town
>||F_Spacer||:F_State||F_Spacer||cast(:F_PCode as VarChar(10));
> if (STYLE_REQ = 3) then RESULT_STR = :F_State;
> if (STYLE_REQ = 4) then RESULT_STR = cast(:F_PCode as VarChar(10));
> if (STYLE_REQ = 5) then RESULT_STR = :F_Country;
>
> suspend;
>end
>^^
>SET TERM ; ^^

Character set mismatches? If the default database character set is
anything but ASCII then you have a potential anomaly in the parameter
descriptions.

Try casting ALL of your possible results as

CAST (TheEntireExpression AS VARCHAR(150) CHARACTER SET ASCII)

./hb