Recent

Author Topic: Firebird 'if' problem  (Read 6993 times)

tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Re: Firebird 'if' problem
« Reply #15 on: April 19, 2018, 11:02:02 am »
However, a simpler solution could be to use "EXECUTE BLOCK" with your parameterised values "declared" as variables, as this would allow you to use PSQL as an inline query.

@tonyw

I also thought about suggesting an execute block statement, but wasn't sure whether the db access controls can parse the parameters. I never tried this by myself...


Best regards
You can do this in (e.g.) IBX using the TIBSQL component, but the way Execute Block works doesn't make it that easy. That's because you have to set the "ParamCheck" property to false and use positional parameters rather than named parameters - the parser isn't clever enough to parse the parameters separate from the body of the block. You also need to use SUSPEND statements in the block if you want to get any values returned. For example:

Execute Block (EMP_NO Integer = ?) Returns (Full_Name VarChar(1024))
As
Begin
           Select FULL_NAME  From Employee Where Emp_no = :EMP_NO INTO :Full_Name;
          SUSPEND;
End

will work with a TIBSQL as long as the ParamCheck property is false. In the above you have to set the parameter using the positional syntax e.g.

with IBSQL1 do
begin
        Transaction.Active := true;
        ParamCheck := false;
        Params[0].AsInteger := 2;
        ExecQuery;
       writeln(FieldByName('FULL_NAME').Name + ': ',FieldByName('FULL_NAME').AsString);
end;

with the above query would print out "FULL_NAME: Nelson, Robert"

ttomas

  • Full Member
  • ***
  • Posts: 245
Re: Firebird 'if' problem
« Reply #16 on: April 19, 2018, 11:19:00 am »
Correct execute block with parameters, work with Fb3
Code: Pascal  [Select][+][-]
  1. execute block(eqsymbol INTEGER=:eqsymbol,
  2.   eqname varchar(80)=:eqname
  3. )
  4. as
  5. begin
  6.   IF (NOT EXISTS (SELECT 1
  7.                 FROM EQUITLST WHERE EQSYMBOL = :eqsymbol)) then
  8.      INSERT INTO EQUITLST (eqsymbol,eqname)
  9.      VALUES  (:eqsymbol,:eqname);
  10.   exit;
  11. end
  12.  
Use SQLQuery.ExecSQL or Execute
Change integer, varchar(80) to type of EQUITLST
« Last Edit: April 19, 2018, 12:05:04 pm by ttomas »

 

TinyPortal © 2005-2018