user1972028 user1972028 - 4 months ago 15
SQL Question

Syntax error in a compound sql statement around Start Transaction, i guess

I am struggling with this compound mysql. I am using the Start Transaction for the first time. So anything will be really helpful.

START TRANSACTION
INSERT
INTO
p_ucourse(
course_name,
course_goal,
course_time,
course_creator_id,
course_status
)
VALUES(
'This Course',
'Goal of this course',
480,
1,
1
);
SET
ucourse_id = LAST_INSERT_ID();
INSERT
INTO
r_ucourse_module(course_id,
module_id,
rank)
VALUES(ucourse_id, 1, 1);

INSERT
INTO
r_ucourse_eu(
course_id,
lu_id,
rank,
afterclass
)
VALUES(ucourse_id, 1, 1, 0);
COMMIT


And it throws up the following error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT



INTO
p_ucourse(
course_name,
course_goal,
course_time,' at line 2

I am not being able to get the syntax of the thing.

Answer

Missing semi-colon at the end of the first line. Ditto with Commit Try:

START TRANSACTION;
..
..
COMMIT;

See START TRANSACTION, COMMIT, and ROLLBACK Syntax for a few examples.

... ...

as for your other issue, consider the below:

create table MyThings
(   id int auto_increment primary key,
    thing varchar(100) not null
);
insert MyThings(thing) values ('Fred, the pet Anchovy');
SET ucourse_id = LAST_INSERT_ID(); -- Error 1193: unknown sys var ... 
SET @ucourse_id = LAST_INSERT_ID(); -- YIPPIE, not a problem (user variable)

So, the first one above (SET ucourse_id) choked, because it was assumed to be a LOCAL Variable (as it did not have an @ sign). The whole thing was not running in a stored proc/function (I assumed). Local Variables need to have life breathed into them with a DECLARE.

but...

declare k int; -- error, can't do this outside of a store proc/func etc

So, one should read up on User Variables vs Local Variables, when and how one can use them.