0

I have a script that I run to create my database. I run this with

source <file>

when it finishes it shows

mysql>

I then type

show databases; 

and it goes to next line and puts

->

as if I have missed the ;, but I haven't.

it does the same for

show tables;

I dont know what i have missed.

the script is

DROP DATABASE IF EXISTS app;
CREATE DATABASE app;
USE app;

CREATE TABLE `user`
(
    id INT PRIMARY KEY AUTO_INCREMENT, 
    forename VARCHAR(20) NOT NULL, 
    surname VARCHAR(20) NOT NULL, 
    `status` VARCHAR(15), 
    deleted BOOLEAN
);

CREATE TABLE login
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    email VARCHAR(50) NOT NULL UNIQUE,
    `password` VARCHAR(65),
    FOREIGN KEY(user_id) REFERENCES `user`(id)
);

DELIMITER //
CREATE PROCEDURE sp_Create_User
(
    in_forename VARCHAR(20), 
    in_surname VARCHAR(20), 
    in_status VARCHAR(15), 
    in_deleted BOOLEAN, 
    in_email VARCHAR(50), 
    in_password VARCHAR(65)
)
BEGIN
DECLARE found_user_id INT;
INSERT INTO `user`
(
    forename,
    surname,
    `status`,
    deleted 
)
VALUES
(
    in_forename,
    in_surname,
    in_status,
    in_deleted  
);
SET found_user_id = 
(
    SELECT id 
    FROM app.`user`
    WHERE 
    forename = in_forename 
    AND 
    surname = in_surname 
    AND 
    id 
    NOT IN 
    (
        SELECT user_id FROM app.login
    )
);
INSERT INTO app.login
(
    user_id,
    email,
    `password`
)
VALUES
(
    found_user_id,
    in_email,
    in_password
);
END //

CREATE PROCEDURE sp_Get_All_Users()
BEGIN
SELECT u.forename, u.surname, u.`status`, u.deleted, l.user_id, l.email, l.password 
FROM `user` u
INNER JOIN login l
ON
u.id = l.user_id;
END //

CREATE PROCEDURE sp_Update_User_Status(in_id INT, in_status VARCHAR(15))
BEGIN
UPDATE app.`user`
SET `status` = in_status
WHERE id = in_id;
END //

CREATE PROCEDURE sp_Delete_User(in_id INT, in_deleted BOOLEAN)
BEGIN
UPDATE app.`user`
SET deleted = in_deleted
WHERE id = in_id;
END //

CREATE PROCEDURE sp_Get_Login(in_email VARCHAR(50), in_password VARCHAR(65))
BEGIN
SELECT l.user_id, l.email, l.`password`, u.deleted, u.status
FROM login l
INNER JOIN `user` u
ON l.user_id = u.id
WHERE l.email = in_email AND l.`password` = in_password;
END //

CREATE PROCEDURE sp_Update_Password(in_user_id INT, in_old_password VARCHAR(65), in_new_password VARCHAR(65))
BEGIN
UPDATE app.login
SET `password` = in_new_password
WHERE user_id = in_user_id AND `password` = in_old_password;
END //
2

It's because you have

DELIMITER //

in your query.

Either add this in the script or run it manually after the script finishes running:

DELIMITER ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.