--- Helen Borrie <helebor@...> wrote:

> 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
>
>
>

____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs