Recent

Author Topic: [SOLVED] Help for SQL procedure  (Read 1078 times)

egsuh

  • Hero Member
  • *****
  • Posts: 1273
[SOLVED] Help for SQL procedure
« on: July 22, 2019, 09:40:50 am »
Hi, I know this is not the right place to ask this, but I do not know better place.
What I'm trying to do is conditional operation within FireBird stored procedure. Specifically,

Code: Pascal  [Select][+][-]
  1. insert into tableA   (f1) values (:value1);
  2. // if successful:
  3.    select f2 from tableB where where tableA.pkfield = tableB.pkfield into :value2;
  4.    update tableA set f2=:value2;
  5.    update tableB set f3=:value1 where tableA.pkfield = tableB.pkfield;
  6.  
The reason to check success is that f1 is field of unique values. Well, I might not have to check the success or not because there there are some SQL error then the followings will not executed.
« Last Edit: July 23, 2019, 04:37:53 am by egsuh »

GAN

  • Sr. Member
  • ****
  • Posts: 370
Re: Help for SQL procedure
« Reply #1 on: July 22, 2019, 09:47:49 pm »
select f2 from tableB where where tableA.pkfield =
Lazarus 2.0.8 FPC 3.0.4 Linux Mint Mate 19.3
Zeos 7̶.̶2̶.̶6̶ 7.1.3a-stable - Sqlite 3.32.3 - LazReport

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Help for SQL procedure
« Reply #2 on: July 23, 2019, 04:28:13 am »
Hi, I know this is not the right place to ask this, but I do not know better place.
What I'm trying to do is conditional operation within FireBird stored procedure. Specifically,

Code: SQL  [Select][+][-]
  1. INSERT INTO tableA   (f1) VALUES (:value1);
  2. // IF successful:
  3.    SELECT f2 FROM tableB WHERE WHERE tableA.pkfield = tableB.pkfield INTO :value2;
  4.    UPDATE tableA SET f2=:value2;
  5.    UPDATE tableB SET f3=:value1 WHERE tableA.pkfield = tableB.pkfield;
  6.  
The reason to check success is that f1 is field of unique values. Well, I might not have to check the success or not because there there are some SQL error then the followings will not executed.
You should control the transaction (commit/rollback) on Lazarus, not on Firebird, which would mean to do everything or nothing.

Then, try something as simple as this on Firebird:
Code: SQL  [Select][+][-]
  1. EXECUTE Block
  2. AS
  3. BEGIN
  4.   IF (NOT EXISTS((SELECT F1 FROM TableA WHERE F1 = :value1))) THEN
  5.   BEGIN
  6.     INSERT INTO tableA(f1) VALUES (:value1);
  7.     UPDATE tableA SET f2 = (SELECT f2 FROM tableB WHERE WHERE tableA.pkfield = tableB.pkfield);
  8.     UPDATE tableB SET f3 = :value1 WHERE tableB.pkfield = tableA.pkfield;
  9.   END
  10. END

https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-execblock.html

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: Help for SQL procedure
« Reply #3 on: July 23, 2019, 04:37:35 am »
@marcos

Yes, thank you very much. This is exactly what I wanted.

 

TinyPortal © 2005-2018