Recent

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

egsuh

  • Full Member
  • ***
  • Posts: 241
[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

  • Full Member
  • ***
  • Posts: 230
Re: Help for SQL procedure
« Reply #1 on: July 22, 2019, 09:47:49 pm »
select f2 from tableB where where tableA.pkfield =
Lazarus 1.8.4 FPC 3.0.4 Linux Mint Mate 17.2 x86_64 GTK-2
Zeos 7.1.3 - Sqlite 3.8.2

Foro Lazarus en español http://forum.lazarus.freepascal.org/index.php/board,73.0.html

valdir.marcos

  • Hero Member
  • *****
  • Posts: 831
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

  • Full Member
  • ***
  • Posts: 241
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.