I am using this query to add rows in two different column
INSERT INTO tbl_chart (acode,adesc) VALUES (2,'3');
INSERT INTO tbl_pics (ID ,adesc) VALUES (1,'1');
select ROW_COUNT() into @c ;
IF ( c > 0) THEN
and trying to check row_count and save its value in variable c .
But this query gives error and it also saves the data after showing the error .
where is the problem ?
And the error is this :
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF ( c > 0) THEN
COMMIT' at line 1 */
Taken from another post MYSQL If statement in transaction causing error
MySQL doesn't recognize a statement beginning with the keyword IF as as a valid SQL statement.
The IF statement works only in the context of a compound statement (that is, a block of statements enclosed between BEGIN and END. Currently, the compound statement is only supported in the context of a stored program (stored procedure, function or trigger.)
As Rahul already stated,
IF statements work only in a stored procedure, a function or a trigger.
I'd just like to add, that you don't need to count at all if or how many rows were inserted. The following is an example of how to solve this in a stored procedure. It's your choice though, if you rather implement the logic behind it in your application code.
drop procedure if exists sp_my_transaction; delimiter $$ create procedure sp_my_transaction() begin declare continue handler for sqlexception begin rollback; exit procedure; end; start transaction; INSERT INTO tbl_chart (acode,adesc) VALUES (2,'3'); INSERT INTO tbl_pics (ID ,adesc) VALUES (1,'1'); commit; end $$ delimiter ;
You would then execute the procedure with
The logic behind it is, that you define with
declare continue handler for sqlexception
what to do, when an error occurs. If this happens and you don't have a continue handler, the procedure would simply abort and the transaction would still be open. The code after the point where the error occurs never gets reached in this case, so your transaction won't be committed either.