Good morning all,

We had the following error reported with one of our DB's. Helen was kind
enough to give us a possible solution to the problem.

We would now like to no "Why" the error occured so that we do not repeat
the problem next time.

Could anyone explain the why bit to us.

Huge thanks in advance.


---------- Original error --------------

>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

------- Helens response ----------------

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)


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

--
Regards,
Grant Brown

Product Development Manager
Phone : 02 4229 1185
Mobile : 0412 926 995
Email : grant@...
Web : www.sitedoc.com.au

SiteDoc - Easy to Use - Powerful Results