Recent

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

ronhud

  • New member
  • *
  • Posts: 49
Firebird 'if' problem
« on: April 16, 2018, 05:59:13 pm »
'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

  • New member
  • *
  • Posts: 49
Re: Firebird 'if' problem
« Reply #1 on: April 16, 2018, 06:08:15 pm »
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

  • Full Member
  • ***
  • Posts: 199
Re: Firebird 'if' problem
« Reply #2 on: April 16, 2018, 07:09:31 pm »
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
Linux Mint 18.3 - KDE / Windows8 / Lazarus 1.8.4 / trunk -qt

Zoran

  • Hero Member
  • *****
  • Posts: 1283
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: Firebird 'if' problem
« Reply #3 on: April 16, 2018, 07:21:53 pm »

Code: SQL  [Select]
  1. INSERT INTO equitlst (eqsymbol, eqname)
  2. VALUES (:eqsymbol,:eqname)
  3. WHERE NOT EXISTS (SELECT 1 FROM equitlst WHERE eqsymbol = :eqsymbol)
  4.  

ronhud

  • New member
  • *
  • Posts: 49
Re: Firebird 'if' problem
« Reply #4 on: April 16, 2018, 09:06:52 pm »
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

  • Hero Member
  • *****
  • Posts: 544
Re: Firebird 'if' problem
« Reply #5 on: April 16, 2018, 09:10:37 pm »
Code: SQL  [Select]
  1. INSERT INTO equitlst (eqsymbol, eqname)
  2. VALUES (:eqsymbol,:eqname)
  3. WHERE NOT EXISTS (SELECT 1 FROM equitlst WHERE eqsymbol = :eqsymbol)

Sorry, Zoran, but this structure does not work on Firebird.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 544
Re: Firebird 'if' problem
« Reply #6 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]
  1. UPDATE OR INSERT INTO EquitLst(eqsymbol, eqname) VALUES(:eqsymbol, :eqname) Matching(eqsymbol);

ronhud

  • New member
  • *
  • Posts: 49
Re: Firebird 'if' problem
« Reply #7 on: April 16, 2018, 10:13:58 pm »
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

  • Hero Member
  • *****
  • Posts: 1283
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: Firebird 'if' problem
« Reply #8 on: April 16, 2018, 11:29:17 pm »
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

Code: SQL  [Select]
  1. INSERT INTO equitlst (eqsymbol, eqname)
  2. VALUES (:eqsymbol,:eqname)
  3. 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

  • Hero Member
  • *****
  • Posts: 1283
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: Firebird 'if' problem
« Reply #9 on: April 17, 2018, 12:49:42 am »
@ronhud, Groffy's suggestion is the simplest and easiest of the possible solutions for Firebird:
 
Code: SQL  [Select]
  1. 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]
  1. INSERT INTO equitlst (eqsymbol, eqname)
  2. SELECT :eqsymbol,:eqname
  3. FROM rdb$database
  4. WHERE NOT EXISTS (SELECT 1 FROM equitlst WHERE eqsymbol = :eqsymbol)

tonyw

  • Full Member
  • ***
  • Posts: 124
    • MWA Software
Re: Firebird 'if' problem
« Reply #10 on: April 18, 2018, 10:29:09 am »
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

  • Full Member
  • ***
  • Posts: 199
Re: Firebird 'if' problem
« Reply #11 on: April 18, 2018, 05:36:07 pm »
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
Linux Mint 18.3 - KDE / Windows8 / Lazarus 1.8.4 / trunk -qt

valdir.marcos

  • Hero Member
  • *****
  • Posts: 544
Re: Firebird 'if' problem
« Reply #12 on: April 18, 2018, 07:16:51 pm »
But this is not the same, when the record is already present, it updates the table.

You are right.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 544
Re: Firebird 'if' problem
« Reply #13 on: April 18, 2018, 07:54:13 pm »
Although it is possible, it would be exaggerated to use Merge or Execute Block commands to solve a simple conditional insert problem.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 544
Re: Firebird 'if' problem
« Reply #14 on: April 18, 2018, 08:24:32 pm »
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...

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]
  1. ...
  2. SQLScript1.Script.ADD('Update TableA');
  3. SQLScript1.Script.ADD('Set FieldB = ' + '456');
  4. SQLScript1.Script.ADD('where FieldA = ' + '123' + ';');
  5. ...

The classical solution for "Execute Block" on SQLQuery is using Stored Procedures.