Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Making Procedure to Use Multiple Insert in PHPMyAdmin Fail

I’m facing a problem where my syntax in PhpMyAdmin got an error

this is my code

CREATE PROCEDURE `tambah_semua`(IN `Nama` VARCHAR(255), IN 
`Plat_Nomor` VARCHAR(8), IN `Deskripsi` TEXT, IN `Total` INT) NOT 
DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER INSERT INTO `User`
 (`Nama`, `Plat_Nomor`,`Role`) VALUES (Nama, Plat_Nomor,4);
    
    INSERT INTO `Booking`(`ID_User`, `Tanggal`, `Deskripsi`) 
    VALUES (
        (SELECT `ID` FROM `User` WHERE `Nama` = Nama),
        CURRENT_DATE,
        Deskripsi
    );
    
    INSERT INTO `Transaksi`(`ID_Booking`, `Total`, `Lunas`) 
    VALUES (
        (SELECT Booking.`ID` FROM User
        LEFT JOIN Booking ON User.ID = Booking.ID_User 
        WHERE User.Nama = Nama AND Booking.Tanggal = CURRENT_DATE),
        Total,
        'Lunas'
    );

and got error like this

The following query has failed: "CREATE PROCEDURE tambah_semua > …

MySQL said: #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 ‘INSERT INTO Booking(ID_User, Tanggal, Deskripsi)
VALUES (
‘ at line 3

I dont see the main problem on the line 3 or at least I don’t know the problem I just talk to GPT and Gemini and nothing works so far

What I do before is maybe the ID_User cannot be defined that fast, so I’m using declare to store the variable first, and then use it on the rest of the table like booking and transaksi but it still not working

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I Know The error right now

    it is because I just want to assign the value right away, so in order to fix it I'm using the variable to store it first and the

    DROP PROCEDURE `tambah_semua`; CREATE DEFINER=`asdsad`@`localhost` 
PROCEDURE `tambah_semua`(IN `Nama` VARCHAR(255), IN `Plat_Nomor` VARCHAR(8), 
IN `Deskripsis` TEXT, IN `Total` INT) NOT DETERMINISTIC CONTAINS SQL SQL 
SECURITY DEFINER BEGIN DECLARE user_id INT; DECLARE book_id INT; 
-- Insert new user INSERT INTO `User` (`Nama`, `Plat_Nomor`, `Role`) VALUES 
(Nama, Plat_Nomor, 4); -- Get the newly inserted user ID SELECT 
LAST_INSERT_ID() INTO user_id; -- Insert into Booking table INSERT INTO 
`Booking` (`ID_User`, `Tanggal`, `Deskripsi`) VALUES (user_id, CURRENT_DATE, 
Deskripsis); -- Get the newly inserted booking ID SELECT LAST_INSERT_ID() INTO
 book_id; -- Insert into Transaksi INSERT INTO `Transaksi`(`ID_Booking`, 
`Total`, `Lunas`) VALUES (book_id, Total, 'Lunas'); END

  2. 0

    When you have multiple statements in a routine they need to be written within a BEGIN ... END block. You also need to change the DELIMITER so the first ; is not seen as the end of the routine declaration.

    You can use LAST_INSERT_ID() directly in the next INSERT query. As each INSERT is dependent on the previous one, and they do not make sense unless they all complete successfully, you should wrap them in a transaction and declare an exit handler to rollback if any of the queries fail.

    I have prefixed the variable names with v (for variable but you can use whatever convention you like) so that the variable names are not the same as any of the column names. In this case, with only INSERTs, it does not matter, but if you had SELECTs or UPDATEs it is common to see bugs due to naming collisions.

    DELIMITER $$

DROP PROCEDURE IF EXISTS `tambah_semua`$$
CREATE PROCEDURE `tambah_semua`(
    IN `vNama` VARCHAR(255),
    IN `vPlat_Nomor` VARCHAR(8),
    IN `vDeskripsi` TEXT,
    IN `vTotal` INT
)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    INSERT INTO `User` (`Nama`, `Plat_Nomor`, `Role`)
    VALUES (vNama, vPlat_Nomor, 4);
    
    INSERT INTO `Booking` (`ID_User`, `Tanggal`, `Deskripsi`) 
    VALUES (LAST_INSERT_ID(), CURRENT_DATE, vDeskripsi);
    
    INSERT INTO `Transaksi` (`ID_Booking`, `Total`, `Lunas`) 
    VALUES (LAST_INSERT_ID(), vTotal, 'Lunas');

    COMMIT;

END$$

DELIMITER ;

    Here’s a db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search