Invalid Request BLR

[akli_agha]

Hi,

I executed this script with TZSQProcessor and had the message below :

Code: Select all

SET TERM ^ ;

CREATE PROCEDURE GET_CREDIT_BY_STRUCT (
    cr_idstruct smallint,
    cr_chap char(5),
    cr_art char(2),
    cr_par char(2),
    cr_aln char(2),
    cr_date date)
returns (
    tot_bp decimal(15,2),
    tot_bs decimal(15,2),
    tot_br decimal(15,2),
    tot_vir_d decimal(15,2),
    tot_vir_e decimal(15,2))
as
begin
      /* BP */
      select sum(dotation) from bud_p
      where chap=:cr_Chap and Art=:cr_Art and par=:cr_Par and aln=:cr_Aln and
            id_struct=:cr_Idstruct and date_bp <=:cr_date
      into tot_bp;
    
      /* BS */
      select sum(dotation) from bud_s
      where chap=:cr_Chap and Art=:cr_Art and par=:cr_Par and aln=:cr_Aln and
            id_struct=:cr_Idstruct and date_bs<=:cr_date
      into tot_bs;
    
      /* BR */
      select sum(dotation) from bud_R
      where chap=:cr_Chap and Art=:cr_Art and par=:cr_Par and aln=:cr_Aln and
            id_struct=:cr_Idstruct and date_br<=:cr_date
      into tot_bR;
    
      /* VIR D */
      select sum(MONTANT) from virement
      where Sens='D' and chap=:cr_Chap and Art=:cr_Art and par=:cr_Par and
            aln=:cr_Aln and id_struct=:cr_Idstruct and date_vir<=:cr_date
      into tot_vir_D;
    
      /* VIR E */
      select sum(MONTANT) from virement
      where Sens='E' and chap=:cr_Chap and Art=:cr_Art and par=:cr_Par and
            aln=:cr_Aln and id_struct=:cr_Idstruct and date_vir<=:cr_date
      into tot_vir_E;

  suspend;
end^
SET TERM ; ^

Message : Invalid Request BLR at offset 300 bad parameter number

PS : Zeos 664, Firebird 2.0.1, Delphi 7 E

An idea ?? Thanks.

[mariuszekpl]

Invalid Request BLR at offset 300 bad parameter number - it is error from firebird, may be you have error in your SQL?

[akli_agha]

SQL ???

The sql script is sent with the post. The single SQL remained is : sqProc.Script.execute !

Other details ??

Merci.

[mariuszekpl]

You should test your sql script with "EMS SQL MANAGER"

[mdaems]

Please check what text is sent to the Firebird server by zeoslib. You can do this easily by adding a TZSQLMonitor to your form. Configure it to send it's output to a log file. That's usually the easiest way of working.

When the sql is not complete there may be some parsing error in the processor.

Actually, now I reread your script, the problem may very well be the 'set Term' lines. I'd try to replace the two SET TERM lines by just setting the delimiter property of the TZSQLProcessor to '^'.

I believe SET TERM is not a server command, but a client command in the client usually provided with your database. So it's the command line program equivalent of setting the property of our tzsqlprocessor. I'm not sure in how far we wanted to provide compatibility with scripts written for the standard commandline clients.

Mark

[seawolf]

Unfortunately your code cannot be replicated because of missing definition of bud_p,bud_s,bud_R,virement

Have you set ?

FProcessor.DelimiterType := dtSetTerm;

Have you set paramCheck = true?

[akli_agha]

Thanks a lot for your responses !

Sorry for my lateness !

Have you set ?
FProcessor.DelimiterType := dtSetTerm;
Have you set paramCheck = true?
Yes !

Unfortunately your code cannot be replicated because of missing definition of bud_p,bud_s,bud_R,virement

I can send the entire DB.

**------------
PS : When I put just this portion of code below (just one select), all is fine .

Code: Select all

SET TERM ^ ;

CREATE PROCEDURE GET_CREDIT_BY_STRUCT (
    cr_idstruct smallint,
    cr_chap char(5),
    cr_art char(2),
    cr_par char(2),
    cr_aln char(2),
    cr_date date)
returns (
    tot_bp decimal(15,2),
    tot_bs decimal(15,2),
    tot_br decimal(15,2),
    tot_vir_d decimal(15,2),
    tot_vir_e decimal(15,2))
as
begin
      /* BP */
      select sum(dotation) from bud_p
      where chap=:cr_Chap and Art=:cr_Art and par=:cr_Par and Aln =: cr_Aln and id_struct=:cr_Idstruct and date_bp <=:cr_date into tot_bp;

  suspend;
end^
SET TERM ; ^

But I add one other select contained in the script, the famous message apears !


Thanks again

[seawolf]

It looks like your procedure does not returns any coloumn.

I mean, you should add

RETURNING
"tot_bp"
INTO
:"tot_bp"

RETURNING
"tot_bs"
INTO
:"tot_bs"

and so on

[akli_agha]

RETURNING is setted in the head of the SP !