0

I want to execute some runtime-generated SQL commands in a Transaction, there is no problem, but I should start this transaction if a condition was true, for example :

  SQLText := 'IF (SELECT COUNT(ID) FROM desk_table WHERE Status = 1 AND Number = ' 
           + IndDeskGrid.DataSource.DataSet.FieldByName('Number').AsString + ' > 0) BEGIN '
           + 'SET autocommit = 0;'
           + 'START TRANSACTION;'
           + 'INSERT INTO waiting_table (UName, DNumber, MDate, HDate, HaveReq)'
           + ' VALUES (' + QuotedStr(User.UName) + ', ' 
           + IndDeskGrid.DataSource.DataSet.FieldByName('Number').AsString
           + ', ' + QuotedStr(MTodayString) + ', ' + QuotedStr(HTodayString) + ', 2);'
           + 'UPDATE desk_table SET Status = 2 WHERE Number = ' 
           + IndDeskGrid.DataSource.DataSet.FieldByName('Number').AsString + ';'
           + 'COMMIT;'
           + 'SET autocommit = 1;'
           + 'END;';

Generated SQL :

IF (SELECT COUNT(ID) FROM desk_table WHERE Status = 1 AND Number = 202 > 0)
BEGIN
SET autocommit = 0;
START TRANSACTION;
INSERT INTO waiting_table (UName, DNumber, MDate, HDate, HaveReq)
VALUES ('UserName', 202, '2015/09/25', '2015/09/25', 2);
UPDATE desk_table SET Status = 2 WHERE Number = 202;
COMMIT;
SET autocommit = 1;
END;

but when I use IF like above code , I got syntax error

I have tried IF..THEN..ENDIF and I got same error

How can I do that without using Stored Procedures and Parameters ?!

I`m Using UniDAC and Delphi XE6 and MySQL(InnoDB)

thanks ...

J...
  • 30,968
  • 6
  • 66
  • 143
Mahmoud_Mehri
  • 1,643
  • 2
  • 20
  • 38
  • You may execute first part of query `SELECT COUNT(ID) FROM desk_table WHERE Status = 1 AND ....` and then you may execute second part according the result. Not in one SQL. – Val Marinov Sep 25 '15 at 14:08
  • But I should do it in one part – Mahmoud_Mehri Sep 25 '15 at 16:03
  • What is the reason for the conditional transaction start? I can not imagine any helpful use case for this. – Sir Rufo Sep 26 '15 at 06:30
  • I should do jobs if condition was true and if I start the transaction before condition , I should roll it back !; anyway , I created a SP and using now and there is no problem , thanks ... – Mahmoud_Mehri Sep 26 '15 at 08:47
  • Well, in a **transactional** DBMS there is **always** a transaction! But you can override the default autocommit of each single statement. So this makes no sense at all. Just execute your bunch of statements with `con.StartTransaction; try qry.ExecSQL; con.Commit; except con.Rollback; raise; end;` and no transaction handling inside the statements. That will be fine – Sir Rufo Sep 26 '15 at 09:39

2 Answers2

1

acutaly it's end if; that you must put at the end

Sebastien H.
  • 6,818
  • 2
  • 28
  • 36
0

Use local variable to store the result of the count query and then use the variable in the if condition.

declare @total int
SELECT @total = COUNT(ID) FROM desk_table WHERE Status = 1 AND Number = 202;
IF (@total > 0)
BEGIN
SET autocommit = 0;
START TRANSACTION;
INSERT INTO waiting_table (UName, DNumber, MDate, HDate, HaveReq)
VALUES ('UserName', 202, '2015/09/25', '2015/09/25', 2);
UPDATE desk_table SET Status = 2 WHERE Number = 202;
COMMIT;
SET autocommit = 1;
END;
coderboy
  • 148
  • 6