Transaction problem 1

[QatQat]

Hi everyone,

I have created these two tables.


create table USERS (
userID int unsigned not null primary key auto_increment,
user_name varchar (50),
user_surname varchar (50)
) type=InnoDB;

create table LOGIN (
loginID int unsigned not null primary key auto_increment,
userID int unsigned,
login_name varchar (50),
login_password varchar (50)
) type=InnoDB;


Now I would like to insert values into the users table, store the userID into a variable and pass the value
in the related field of the login table.
Here is my transaction

BEGIN WORK;
INSERT INTO USERS VALUES ('null', 'user_name', 'user_surname');

select @myvar:=last_insert_id();

INSERT INTO LOGIN VALUES ('null', '@myvar', 'login_name', 'login_password');

COMMIT;


If I run it, it does not give any error but, when I open the 'Login' table, only 0 have been stored in the userid field
and not the correct value from the 'users' table.
If I run this transaction in the prompt it shows the correct value stored in the variable so I assume
that there is something wrong in the way I recall the variable.

Can anyone tell me what I am doing wrong?

Thank you


Qatqat

 

[swampBoogie]

Omit the auto_increment column from the insert

INSERT INTO USERS(user_name,user_surname)
VALUES ('user_name', 'user_surname')

Actually your statement is invalid as you try to insert a charaacter string into a numeric column but sloppy Mysql does not seem to care about such things.

 

[QatQat]

There is nothing wrong with my insert statement.
I fact I am not inserting any value in the int column.
If you want to try it yourself you can either use 'null' or '', it still inserts the self generated id.

My problem is not that. I have a problem, as I explained before in retrieving the value previously stored in the variable.

select @myvar:=last_insert_id();

I then want to pass that value in the userid field of the login table.

Any other idea?


Qatqat

 

[swampBoogie]

I see the error

You have enclosed the variable in '' so it is interpreted as a string and hence Mysql inserts something else instead of giving an error as would have been the proper action.

INSERT INTO LOGIN(user_id,login_name,login_password)
VALUES ( @myvar, 'login_name', 'login_password')

 

[QatQat]

Thanks a lot,

I was reading and reading through it without seeing the thing.
You got a star


Bye

Qatqat