Invalid Request BLR

akli_agha · Post by **akli_agha** » 24.04.2010, 10:42

Hi,

I tried to create a stored proc at runtime with an TZSQLProcessor component which has its DelimiterType set to dtSetTerm but when I execute the script I had this message :

Invalid Request BLR at at offset 208 invalid parameter number

XP, D7, FB2.0, Zeos 664

Here is the code :

Code: Select all

SET TERM ^ ;

CREATE PROCEDURE GET_TOTAL (
    ID_FNM INTEGER,
    CR_DATE DATE)
RETURNS (
    TOT_BP DECIMAL(15,2),
    TOT_BS DECIMAL(15,2),
    TOT_VIR_D DECIMAL(15,2),
    TOT_VIR_E DECIMAL(15,2))
AS
begin
  /* BP */
  select sum(MT_BP) - SUM(MT_REGIE) from FNM
  where Id_FNM=:Id_FNm and date_bp <=:cr_date
  into tot_bp;

  /* BS */
  select sum(MT_BS) from FNM
  where Id_FNM=:Id_FNm and date_bs <=:cr_date
  into tot_bs;

  /* VIR D */
  select sum(MONTANT) from virement
  where Sens='D' and Id_FNM=:Id_FNm and date_vir <=:cr_date
  into tot_vir_D;

  /* VIR E */
  select sum(MONTANT) from virement
  where Sens='E' and Id_FNM=:Id_FNm and date_vir <=:cr_date
  into tot_vir_E;

  suspend;
end^
SET TERM ; ^

Thanks

Wild_Pointer · Post by **Wild_Pointer** » 26.04.2010, 07:23

akli_agha,
Try using TZConnection.ExecuteDirect instead.

akli_agha · Post by **akli_agha** » 27.04.2010, 07:34

Thanks,

ExecuteDirect ? Never used it !

Can you give me more details about the use of this statement.

Where the script must be ? In the Properties proporty ??

Thanks for your reponse

Wild_Pointer · Post by **Wild_Pointer** » 27.04.2010, 08:40

akli_agha,

please see http://zeos.firmos.at/viewtopic.php?t=2761&highlight=

good luck!!

akli_agha · Post by **akli_agha** » 29.04.2010, 10:19

I tried ZConnection.ExecuteDirect but had a compilation error : Not declared Id - ExecuteDirect !

XP, D7, FB2.0, Zeos 664

Wild_Pointer · Post by **Wild_Pointer** » 29.04.2010, 12:14

You tried ZConnection.ExecuteDirect(CREATE TABLE FOO.... ); and got error ? That's strange.

People are using it to create databases and other objects...
http://zeos.firmos.at/viewtopic.php?t=2627&highlight=

I never tried it on firebird, and never with version 6.6.4. I know it works on postgresql (zeos versions 6.6.6 and 7 )

akli_agha · Post by **akli_agha** » 29.04.2010, 17:10

In the link, ExecuteDirect is used with mySql. With firebird, 'properties' is used for the work. Perhaps this statement does'nt work with firebird.
Is there any one to confirm or not .

My problem still not solved !

Tahnks.

guidoaerts · Post by **guidoaerts** » 30.04.2010, 08:02

akli_agha,
No, you have to put your sql in the ExecuteDirect('string'), not in the 'properties'. It is implemented as a executeUpdate statement, returning an integer representing the number of affected rows.
guido

akli_agha · Post by **akli_agha** » 30.04.2010, 17:44

@ guidoaerts,

Here is the code :

Code: Select all

  DBPath := 'C:\MYFB_DB.FDB';

  MyUserId := 'SYSDBA';
  myPw     := 'masterkey';

  Connect_DB.User:= MyUserId;
  Connect_DB.Password:= myPw;

  Connect_DB.Database:= DBPath;
  Connect_DB.Protocol := 'firebird-2.0';
  Connect_DB.Properties.Clear;

  ZConnection.ExecuteDirect('CreateNewDatabase=CREATE DATABASE'  +  ' QuotedStr(DBPath) + '  USER  ' + QuotedStr(MyUserId) +
       '  PASSWORD  ' + QuotedStr(myPW) + '   PAGE_SIZE 4096');

I had a compilaion error : Id not declared : ExecuteDirect !

XP SP2, D7, FB 2.0

Thanks.

guidoaerts · Post by **guidoaerts** » 30.04.2010, 18:14

try Connect_DB.ExecuteDirect() instead of ZConnection.ExecuteDirect()
guido

akli_agha · Post by **akli_agha** » 30.04.2010, 22:44

Sorry for the confusion between ZConnection and Connect_DB !

I tried with Connect_DB.ExecuteDirect, the system delivers this message :
Connection is not opened yet !

guidoaerts · Post by **guidoaerts** » 01.05.2010, 17:03

ok, before you executedirect() you should make the connection with
Connect_DB.connect;

Post by **mdaems** » 01.05.2010, 21:02

ExecuteDirect is a method of a TZConnection component. It's a shortcut to avoid working with TZQuery components when you just want to send a query to the server that doesn't need a resultset.

When you have a valid database connection you can do
myconnection.ExecuteDirect('Insert into logs(time) values ("2001-12-01 23:35:59")');

Is this example clear enough?

Mark

akli_agha · Post by **akli_agha** » 02.05.2010, 09:40

Thank you, it's very clear !

Then ExecuteDirect can not be used to create the database itself !

Thanks again.

akli_agha · Post by **akli_agha** » 02.05.2010, 09:43

More ...

My problem is not executDirect but the non execution of the storedProc with Inavalid Request BLR Message and still insolved !

Thanks for help.