Execute block with SQLDB query
lazarus / 16-Databases

----------------------------------------------------------------------
jwest · 2016-03-10 21:45

I am new in sqldb and I have allways used IBO in delphi.


I need execute the next sql statement with a TSQLQuery and a firebird
database:
I dropped a TIBConenction, a TSQLTransaction and a TSQLQuery in my
form.

When I try to run my sql statement I am getting the next exception:
'IBCon : PrepareStatement : -Dynamic SQL Error -SQL error code = -104
-Token unknown - line 18, column 30 -?'

What am I doing wrong?

My code is:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var
tn_seg:TSQLTransaction;      sql_seg:TSQLQuery;   
 gg:string;begin try   tn_seg.StartTransaction;   with sql_seg do
begin      //if not prepared  then prepare;     
parambyname('p1').AsDate:=date; //DATA DA SENHA     
parambyname('p2').AsBoolean:=st.values['ORTOFIS']='1';      
parambyname('p3').AsBoolean:=st.values['NORMPREF=']='1';     
parambyname('p4').AsInteger:=StrToIntDef(st.values['TIPOPREF'],0);   
 parambyname('p5').AsBoolean:=st.values['PRIMVEZ']='1';     
parambyname('p6').AsBoolean:=st.values['ESCOLHEMED']='1';      paramby
name('p7').AsInteger:=StrToIntDef(st.values['MEDESCOLHIDO'],0);     
parambyname('p8').AsString:=st.values['NOME'];     
parambyname('p9').AsString:=st.values['CPF'];     
parambyname('p10').AsInteger:=StrToIntDef(st.values['AVALSES'],-1);  
  Open;      gg:=format('%.4d',[fieldbyname('gerado').asInteger]); 
 end;  finally    tn_seg.Commit;  end;          end; Here is my sql
statement:
EXECUTE BLOCK (DT DATE = :p1,
   ORTO CHAR(1) = :p2,
   PREFER CHAR(1) = :p3,
   ID_GEST_CRI smallint = :p4,
   PRIMEIRAVEZ CHAR(1) = :p5,
   ESCOLHEMEDICO CHAR(1) = :p6,
   NRMEDICOPREF integer = :p7,
   NOME NH_NAME_60 =:p8,
   CPF NH_NAME_15 =:P9,
   AVALSES integer =:P10
)
   RETURNS (GERADO INTEGER)
AS
DECLARE L_VALOR INT;
BEGIN
    DELETE FROM TBL_TICKET WHERE DATA_GERADA<(CURRENT_DATE-2);
    SELECT FIRST 1 VALOR  FROM  TBL_TICKET
    WHERE DATA_GERADA=:DT INTO :L_VALOR;
    IF (:L_VALOR IS NULL) THEN   
        EXECUTE STATEMENT 'ALTER SEQUENCE ID_TBL_TICKET RESTART WITH
0';
    GERADO=GEN_ID(ID_TBL_TICKET,1);
    INSERT INTO TBL_TICKET (DATA_GERADA,VALOR,FOI_CHAMADO,DATA_CHAMADO
     ,ORTOPEDIA,
PREFERENCIAL,IDOSO_GESTANTE_CRIANCA,PRIMEIRAVEZ,ESCOLHEMEDICO,
        NRMEDICOPREF,NOME,CPF,AVALSES,BAIXADO)
      VALUES (:DT,:GERADO,'F',null,:ORTO, :PREFER, :ID_GEST_CRI,
:PRIMEIRAVEZ,
             :ESCOLHEMEDICO, :NRMEDICOPREF,:NOME,:CPF,:AVALSES,'F');
  SUSPEND;
END;

----------------------------------------------------------------------
Jurassic Pork · 2016-03-10 22:35

hello,
what is your version of firebird ?

Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i DECLARE
L_VALOR INT;
Are you sure that there is an INT datatype in firebird. Try INTEGER  :
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i DECLARE
L_VALOR INTEGER;

Friendly, J.P

----------------------------------------------------------------------
jwest · 2016-03-10 23:15

Hi, thank you for your response.

My firebird is 2.5.
The sql syntax is correct because I have it working with delphi/IBO.
In firebird 2.5 you can use INT or Integer, threre is no difference.
The execute block posted will be executed in the server.

Luiz

----------------------------------------------------------------------
LacaK · 2016-03-12 13:56

I think, that you must set:
ParseSQL := False;
And substitute parameters directly in SQL.Text (not using
ParamByName())
For example like:
SQL.Text := Format('EXECUTE BLOCK (DT DATE = %s ...', []);

----------------------------------------------------------------------
jwest · 2016-03-13 01:31

Thank you. 
It´s a good solution. But I solved creating a SP(store procedure on
database server) doing the same action.

Luiz