I have written a MYSQL procedure and there is mistake in syntax, can't figure out the correct syntax

Question

The MYSQL procedure will
1. Create TABLE1_ARCHIVE similar to TABLE1 if it is not there.
2.Insert the values from the TABLE1 into TABLE1_ARCHIVE based on date condition
3. Delete those records from TABLE1.

Link to the code: https://paiza.io/projects/Eq7I5YGo-lt7_gu8wpQNdg?language=mysql

CREATE PROCEDURE ARCHIVE_EVENTS ( IN f_table VARCHAR(255),
                                IN t_table VARCHAR(255),
                                IN t_ts TIMESTAMP)
BEGIN
    DECLARE c_sql VARCHAR(255);
     DECLARE i_sql VARCHAR(255);
     DECLARE d_sql VARCHAR(255);    

SET @c_sql = CONCAT(' CREATE TABLE IF NOT EXISTS ', @t_table , ' LIKE ', @f_table );
PREPARE stmt FROM  @c_sql;
EXECUTE stmt ;

SET @i_sql = CONCAT(' INSERT INTO ', @t_table, ' SELECT * FROM ', @f_table, ' WHERE `event_date` <=  ', @t_ts);
PREPARE stmt FROM  @i_sql;
EXECUTE stmt ;

COMMIT;

SET @d_sql = CONCAT(' DELETE FROM ', @f_table, ' WHERE `event_date` <= ', @t_ts);
PREPARE stmt FROM  @d_sql;
EXECUTE stmt ;
COMMIT;
END;

CALL ARCHIVE_EVENTS ('TABLE1', 'TABLE1_ARCHIVE', now());

After doing the changes, the procedure looks like this and is still not working :

The error that i am getting is

ERROR 1064 (42000) at line 2: 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 '' at line 3

CREATE PROCEDURE ARCHIVE_EVENTS (IN f_table VARCHAR(255),IN t_table VARCHAR(255),IN t_ts TIMESTAMP)
BEGIN
    DECLARE c_sql VARCHAR(255);
    DECLARE i_sql VARCHAR(255);
    DECLARE d_sql VARCHAR(255);    

    SET c_sql = CONCAT(' CREATE TABLE IF NOT EXISTS ', t_table , ' LIKE ', f_table);
    PREPARE stmt FROM  c_sql;
    EXECUTE stmt ;

    SET i_sql = CONCAT(' INSERT INTO ', t_table, ' SELECT * FROM ', f_table, ' WHERE `event_date` <= ', t_ts);
    PREPARE stmt FROM  i_sql;
    EXECUTE stmt ;

    COMMIT;

    SET d_sql = CONCAT(' DELETE FROM ', f_table, ' WHERE `event_date` <= ', t_ts);
    PREPARE stmt FROM  d_sql;
    EXECUTE stmt ;
    COMMIT;
END;

CALL ARCHIVE_EVENTS ('TEST', 'TEST_ARCHIVE', now());

"I have written a MYSQL procedure and getting the following error ! Not sure if the syntax is correct" i don't see a error in the question. — Raymond Nijland, May 09 '18 at 14:12
You are confusing '@'variables declared variables and parameters. @f_table is a different variable to f_table. All your '@'variables will contain null. — P.Salmon, May 09 '18 at 14:16
Also adding to @P.Salmon 's comment also this approach opens you up to SQL injections without validating variables t_table and f_table are real table names (use information schema and `EXECUTE USING .. (prevents SQL injection)` and IF statements to check if the table exists before executing the CREATE/INSERT/DELETE queries)., To use `EXECUTE ... USING ...` to prevent SQL injections see a example here https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html .. If you can't figure it out ask a new question. — Raymond Nijland, May 09 '18 at 14:20
@P.Salmon , let me correct the syntax and check it once. I will post the error as well. I have missed to include the exception that i got while executing this procedure. — heman123, May 09 '18 at 16:33
@P.Salmon still getting the same exception. Added the exception that i got as well. — heman123, May 09 '18 at 17:18
@RaymondNijland , thanks for the suggestion and the link. I can look at it later once this procedure starts working. I have added the exception as well. Can you look at it and suggest what changes has to be done. — heman123, May 09 '18 at 17:26
https://paiza.io/projects/Eq7I5YGo-lt7_gu8wpQNdg?language=mysql — heman123, May 09 '18 at 17:26

score 1 · Answer 1 · answered May 09 '18 at 18:40

It isn't difficult to debug a procedure like this just put in a few select statements. Note @variables have to be used in the prepared statements and you have not quoted the date t_ts. And possibly you have not set delimiters.

    drop procedure if exists p;
    delimiter $$

    CREATE PROCEDURE p (IN f_table VARCHAR(255),IN t_table VARCHAR(255),IN t_ts TIMESTAMP)
    BEGIN

    SET @c_sql = (select CONCAT(' CREATE TABLE IF NOT EXISTS ', t_table , ' LIKE ', f_table));
    select @c_sql;

    /*PREPARE stmt FROM  @c_sql;
        EXECUTE stmt ;
         deallocate prepare stmt;
    */   
    SET @i_sql = (select CONCAT(' INSERT INTO ', t_table, ' SELECT * FROM ', f_table, ' WHERE `event_date` <= ',char(39), t_ts,char(39)));
    select @i_sql;
    /*
    PREPARE stmt FROM  @i_sql;
    EXECUTE stmt ;
    deallocate prepare stmt;

    COMMIT;
    */
    SET @d_sql = (select CONCAT(' DELETE FROM ', f_table, ' WHERE `event_date` <= ', char(39),t_ts, char(39)));
    select @d_sql;
    /*
    PREPARE stmt FROM  @d_sql;
    EXECUTE stmt ;
    deallocate prepare stmt;
    COMMIT;
    */
END $$
delimiter ;
drop table users_copy;
CALL p('users', 'users_copy', now());

Yield these statements
+---------------------------------------------------+
| @c_sql                                            |
+---------------------------------------------------+
|  CREATE TABLE IF NOT EXISTS users_copy LIKE users |
+---------------------------------------------------+
1 row in set (0.00 sec)

+-----------------------------------------------------------------------------------------+
| @i_sql                                                                                  |
+-----------------------------------------------------------------------------------------+
|  INSERT INTO users_copy SELECT * FROM users WHERE `event_date` <= '2018-05-09 19:36:54' |
+-----------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

+----------------------------------------------------------------+
| @d_sql                                                         |
+----------------------------------------------------------------+
|  DELETE FROM users WHERE `event_date` <= '2018-05-09 19:36:54' |
+----------------------------------------------------------------+
1 row in set (0.04 sec)

You could then test each of them if you wished or uncomment the prepared statements and let rip.

Individual queries are working absolutely fine even for me. But i have to run this procedure in loop. So i have to make sure when the procedure runs it executes these queries along. When i tried to execute the queries after the changes suggested by @Kesav . I got the following exeption: — heman123, May 10 '18 at 09:53
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 '00:00:00' at line 1 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 '00:00:00' at line 1 — heman123, May 10 '18 at 09:53
CALL ARCHIVE_EVENTS ('TEST', 'TEST_ARCHIVE', TIMESTAMP('2018-05-10 00:00:00')); — heman123, May 10 '18 at 09:54
@kesav does not wrap t_ts in single quotes so I'm not really surprised. — P.Salmon, May 10 '18 at 10:08
SET @i_sql = CONCAT(' INSERT INTO ', t_table, ' SELECT * FROM ', f_table, ' WHERE `event_date` <= ', t_ts); -> What change do i need to do here ? I did not get your point, sorry — heman123, May 10 '18 at 10:22
SET @i_sql = CONCAT(' INSERT INTO ', t_table, ' SELECT * FROM ', f_table, ' WHERE `event_date` <= ',char(39), t_ts,char(39)); -> this worked, got it — heman123, May 10 '18 at 10:33

score 1 · Answer 2 · answered May 09 '18 at 18:52

1

DROP PROCEDURE IF EXISTS ARCHIVE_EVENTS;
CREATE PROCEDURE ARCHIVE_EVENTS (IN f_table CHAR(100),IN t_table 
CHAR(100),IN t_ts TIMESTAMP)
READS SQL DATA
COMMENT 'Test'
BEGIN

SET @c_sql = CONCAT(' CREATE TABLE IF NOT EXISTS ', t_table , ' LIKE ', f_table);
PREPARE stmt FROM  @c_sql;
EXECUTE stmt ;

SET @i_sql = CONCAT(' INSERT INTO ', t_table, ' SELECT * FROM ', f_table, ' WHERE `dateTime` <= ', DATE(t_ts));
PREPARE stmt1 FROM  @i_sql;
EXECUTE stmt1 ;

COMMIT;

SET @d_sql = CONCAT(' DELETE FROM ', f_table, ' WHERE `dateTime` <= ', DATE(t_ts));
PREPARE stmt2 FROM  @d_sql;
EXECUTE stmt2 ;
COMMIT;
END;

CALL ARCHIVE_EVENTS ('TEST', 'WS_REL_TEST', now());

answered May 09 '18 at 18:52

Keshav

66
3

Welcome to Stackoverflow. Please don't just dump your code in your answer, try to describe a little bit what is your solution. Pleas take some time to read this: https://stackoverflow.com/help/how-to-answer – sɐunıɔןɐqɐp May 09 '18 at 19:06
use this link for the minified working version of the above code. [link]http://rextester.com/ORIL14490 – Keshav May 09 '18 at 19:12
SET @i_sql = CONCAT(' INSERT INTO ', t_table, ' SELECT * FROM ', f_table, ' WHERE event_date <= ',char(39), t_ts,char(39)); there is one correction and it works – heman123 May 10 '18 at 10:42

score 0 · Answer 3 · answered May 10 '18 at 10:45

CREATE PROCEDURE ARCHIVE_EVENTS (IN f_table CHAR(100),IN t_table 
CHAR(100),IN t_ts TIMESTAMP)

BEGIN

SET @c_sql = CONCAT(' CREATE TABLE IF NOT EXISTS ', t_table , ' LIKE ', f_table);

SET @i_sql = CONCAT(' INSERT INTO ', t_table, ' SELECT * FROM ', f_table, ' WHERE `event_date` <= ',char(39), t_ts,char(39));

SET @d_sql = CONCAT(' DELETE FROM ', f_table, ' WHERE `event_date` <= ',char(39), t_ts,char(39));

PREPARE stmt FROM  @c_sql;
PREPARE stmt1 FROM  @i_sql;
PREPARE stmt2 FROM  @d_sql;


EXECUTE stmt ;
EXECUTE stmt1 ;
EXECUTE stmt2 ;
COMMIT;

END;

This is the working code, thanks to the above answers. This is the final solution for the query that worked for me.

I have written a MYSQL procedure and there is mistake in syntax, can't figure out the correct syntax

3 Answers3