Invalid Request BLR

[akli_agha]

Hi,

I had Invalid Request BLR message when I execute this code in a TZSQLProcessor component.
ZSQLProcessor.Delimiter = ;
ZSQLProcessor.DelimiterType = dtSetTerm

Any idea ???

Code: Select all

SET TERM ^ ;

ALTER PROCEDURE GET_CA_CLIENT (
    ID_CLIENT INTEGER,
    DATE_CA DATE)
RETURNS (
    TOT_VENTE DECIMAL(15,2),
    TOT_CAISSE DECIMAL(15,2))
AS
begin
  SELECT SUM(MTHT+MTTVA+MTTMB-MTREMISE)
  FROM FENT
  WHERE ID_CLIENT=:ID_CLIENT AND
                DFACT <=:DATE_CA
  into TOT_VENTE;

  SELECT SUM(MT_CHQ + MT_ESP)
  FROM FENT_CS
  WHERE ID_CLIENT=:ID_CLIENT AND
        DFACT <=:DATE_CA
  into TOT_CAISSE;

  suspend;
end^
SET TERM ; ^

[akli_agha]

It seems there is no solution for the question ??
Is it a firebird or zeos error ??

any response ??

[mdaems]

akli_agha,

I remember (and a search on the forum confirms) that this isn't a new issue. However, I don't know the solution either.

I must say, I didn't look deep into the TZSQLProcessor yet.
One of the problems may be that it splits the script incorrectly into statements when using SET TERM.

This can be verified by using a TZSQLMonitor to see what's sent to the server as one query.

The error message is generated by firebird, but may very well occur because zeoslib doesn't handle the script correctly.
Can you provide a sqlmonitor trace file?

Mark

[akli_agha]

Thank's Mark,

No, it is'nt a new issue because of I had no solution yet ! I tried again may be someone can help me.

NB. No problem to create this SP on IBExpert.

Here is an SqlMonitor trace :

2010-09-22 18:42:19 cat: Execute, proto: firebird-2.0, msg: ALTER PROCEDURE GET_CA_CLIENT ( ID_CLIENT INTEGER, DATE_CA DATE) RETURNS ( TOT_VENTE DECIMAL(15,2), TOT_CAISSE DECIMAL(15,2)) AS begin SELECT SUM(MTHT+MTTVA+MTTMB-MTREMISE) FROM FENT WHERE ID_CLIENT=? AND DFACT <=? into TOT_VENTE; SELECT SUM(MT_CHQ + MT_ESP) FROM FENT_CS WHERE ID_CLIENT=? AND DFACT <=? into TOT_CAISSE; suspend; end
2010-09-22 18:42:19 cat: Transaction, proto: firebird-2.0, msg: invalid request BLR at offset 199 bad parameter number, errcode: -104, error: Invalid token
2010-09-22 18:42:19 cat: Transaction, proto: firebird-2.0, msg: TRANSACTION ROLLBACK

[akli_agha]

Thanks for mdaems !

The solution ?? Just set the paramCheck proporty to False !
That's it !

[mdaems]

And now the reason why it didn't work with paramcheck=true...

With paramcheck=true zeoslib searches for parameters in your query or sqlprocessor. These parameters must be in the format ':paramname'. If these are found zeoslib substitutes this parameter by a question mark (?) and send the new query to the FB server for preparation. After preparation the parameters are sent and the statement is executed. (for details you should check FB documentation or read about prepared statements, eg here).

Now, in case of creating a stored procedure the colon gets a complete new meaning in the script. It shouldn't be interpreted as a parameter. That's what puzzled akli_agha.

So setting paramcheck to true solved the problem.
But what if you really want to use parameters somewhere else in the script, then? Well, unfortunately, there's no solution in zeoslib 6.6. BUT, I just added a new property to TZSQLProcessor and TZQuery in zeoslib 7. This property is called ParamChar and defaults to ':'. Don't think I need to say more. Now you can define the character to indicate a parameter yourself. Within reasonable limits, of course. Just using a letter, or minus sign will return an (appropriate) error message.
Who feels like testig this new feature?

Mark