Firebird 'if' problem
lazarus / 16-Databases

----------------------------------------------------------------------
ronhud · 2018-04-16 15:59

'IF NOT EXISTS (SELECT EQSYMBOL FROM EQUITLST WHERE EQSYMBOL =
:eqsymbol) INSERT INTO EQUITLST (eqsymbol,eqname) VALUES 
(:eqsymbol,:eqname)'

This statement  is failing on IF     - error 104

Using Lazarus with Firebird and sqldb
Should I be able to use INSERT and IF in the same statement?

----------------------------------------------------------------------
ronhud · 2018-04-16 16:08

The statement actually reads as follows:-

'IF NOT EXISTS (SELECT EQSYMBOL FROM EQUITLST WHERE EQSYMBOL =
:eqsymbol) THEN INSERT INTO EQUITLST (eqsymbol,eqname) VALUES 
(:eqsymbol,:eqname)'

mistyped earlier.

----------------------------------------------------------------------
Groffy · 2018-04-16 17:09

if statement is only possible in PSQL language. You can use the update
or insert syntax :

update or insert into EQUITLST(eqsymbol,eqname) VALUES 
(:eqsymbol,:eqname) matching(EQSYMBOL)

and see if it works for your needs


Best regards

----------------------------------------------------------------------
Zoran · 2018-04-16 17:21

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
equitlst (eqsymbol, eqname)VALUES (:eqsymbol,:eqname)WHERE NOT EXISTS
(SELECT 1 FROM equitlst WHERE eqsymbol = :eqsymbol)

----------------------------------------------------------------------
ronhud · 2018-04-16 19:06

Zoran tried that and failed with error 104 but said line 1 col 54
WHERE.   However col 54 in the statement is the m in eqsymbol in
VALUES

----------------------------------------------------------------------
valdir.marcos · 2018-04-16 19:10

Quote from: Zoran on April 16, 2018, 07:21:53 pmCode: SQL
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
equitlst (eqsymbol, eqname)VALUES (:eqsymbol,:eqname)WHERE NOT EXISTS
(SELECT 1 FROM equitlst WHERE eqsymbol = :eqsymbol)
Sorry, Zoran, but this structure does not work on Firebird.

----------------------------------------------------------------------
valdir.marcos · 2018-04-16 19:19

@ronhud, Groffy's suggestion is the simplest and easiest of the
possible solutions for Firebird:

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i UPDATE OR
INSERT INTO EquitLst(eqsymbol, eqname) VALUES(:eqsymbol, :eqname)
Matching(eqsymbol);

----------------------------------------------------------------------
ronhud · 2018-04-16 20:13

I have resolved the problem by using 2 queries.    First one is a
select count(*) and if the result is 0 then I do the insert.

----------------------------------------------------------------------
Zoran · 2018-04-16 21:29

Quote from: ronhud on April 16, 2018, 09:06:52 pmZoran tried that and
failed with error 104 but said line 1 col 54 WHERE.   However col 54
in the statement is the m in eqsymbol in VALUES

Quote from: valdir.marcos on April 16, 2018, 09:10:37 pmQuote from:
Zoran on April 16, 2018, 07:21:53 pmCode: SQL
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
equitlst (eqsymbol, eqname)VALUES (:eqsymbol,:eqname)WHERE NOT EXISTS
(SELECT 1 FROM equitlst WHERE eqsymbol = :eqsymbol)
Sorry, Zoran, but this structure does not work on Firebird.

Sorry. 
Writing without thinking...

----------------------------------------------------------------------
Zoran · 2018-04-16 22:49

Quote from: valdir.marcos on April 16, 2018, 09:19:51 pm@ronhud,
Groffy's suggestion is the simplest and easiest of the possible
solutions for Firebird:

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i UPDATE OR
INSERT INTO EquitLst(eqsymbol, eqname) VALUES(:eqsymbol, :eqname)
Matching(eqsymbol);
But this is not the same, when the record is already present, it
updates the table.

This will work:

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
equitlst (eqsymbol, eqname)SELECT :eqsymbol,:eqnameFROM
rdb$databaseWHERE NOT EXISTS (SELECT 1 FROM equitlst WHERE eqsymbol =
:eqsymbol)

----------------------------------------------------------------------
tonyw · 2018-04-18 08:29

A "merge" statement might also work for you i.e.

Merge INTO EquitLst E
Using (SELECT :eqsymbo as eqsymbol, :eqname as eqname FROM
rdb$database) D
On D.eqsymbol = E.eqsymbol
When not matched then insert (eqsymbol, eqname) Values  (D.eqsymbol,
D.eqname);

although in the select statement you may have to cast the columns to
the same type as the columns to be inserted. 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.

----------------------------------------------------------------------
Groffy · 2018-04-18 15:36

Quote from: tonyw on April 18, 2018, 10:29:09 amHowever, 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

----------------------------------------------------------------------
valdir.marcos · 2018-04-18 17:16

Quote from: Zoran on April 17, 2018, 12:49:42 amBut this is not the
same, when the record is already present, it updates the table.
You are right.

----------------------------------------------------------------------
valdir.marcos · 2018-04-18 17:54

Although it is possible, it would be exaggerated to use Merge or
Execute Block commands to solve a simple conditional insert problem.

----------------------------------------------------------------------
valdir.marcos · 2018-04-18 18:24

Quote from: Groffy on April 18, 2018, 05:36:07 pmI 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...
So far, I could not manage to use Execute Block in SQLQuery, but it
works with limits on SQLScript:
- you can't "open" script to extract information, just "execute" it;
- you can't assign "execute block input parameters" because you can't
neither set "SQLScript parameters" nor prepare the statement;
- but you can be creative and insert parameters information via script
text:

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
...SQLScript1.Script.ADD('Update TableA');SQLScript1.Script.ADD('Set
FieldB = ' + '456');SQLScript1.Script.ADD('where FieldA = ' + '123' +
';');...
The classical solution for "Execute Block" on SQLQuery is using Stored
Procedures.

----------------------------------------------------------------------
tonyw · 2018-04-19 09:02

Quote from: Groffy on April 18, 2018, 05:36:07 pmQuote from: tonyw on
April 18, 2018, 10:29:09 amHowever, 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 · 2018-04-19 09:19

Correct execute block with parameters, work with Fb3
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 execute
block(eqsymbol INTEGER=:eqsymbol,  eqname varchar(80)=:eqname)asbegin 
IF (NOT EXISTS (SELECT 1                FROM EQUITLST WHERE EQSYMBOL =
:eqsymbol)) then     INSERT INTO EQUITLST (eqsymbol,eqname)    
 VALUES  (:eqsymbol,:eqname);  exit;end Use SQLQuery.ExecSQL or
Execute
Change integer, varchar(80) to type of EQUITLST