* * *

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

ronhud

  • New member
  • *
  • Posts: 43
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: 43
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: 195
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: 1253
    • 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: 43
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: 515
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: 515
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: 43
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: 1253
    • 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: 1253
    • 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: 118
    • 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: 195
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: 515
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: 515
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: 515
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.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus