SQL Syntax Error With Procedure with using COL_LENGTH()

Question

I'm getting the below error when running the below. Looking at the code it looks correct to me. I'm not fully sure though.

Stored procedure creation failed: (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 'BEGIN ALTER TABLE sitesettings ADD backgroundColor varchar(255) DEFAULT '

CREATE PROCEDURE p() 
BEGIN 
    IF COL_LENGTH('sitesettings', 'backgroundColor') IS NULL 
    BEGIN 
        ALTER TABLE sitesettings ADD backgroundColor varchar(255) DEFAULT '#202225'; 
    END 

    IF COL_LENGTH('sitesettings', 'logintype') IS NULL BEGIN 
        ALTER TABLE sitesettings ADD logintype INT DEFAULT 1 
    END 

    IF NOT EXISTS (SELECT 'X' 
        FROM   INFORMATION_SCHEMA.TABLES 
        WHERE  TABLE_NAME = 'classicusers') 
    BEGIN 
        CREATE TABLE classicusers( 
        id TEXT NOT NULL, 
        username TEXT NOT NULL, 
        password TEXT NOT NULL); 
    END 
END;

' it looks correct to me' - for sqlserver perhaps it is but there is no col_length function in mysql - Is this question tagged correctly? — P.Salmon, Feb 24 '20 at 07:44

score 0 · Accepted Answer · answered Feb 24 '20 at 08:45

I removed all syntax errors, si taht at least Workbench don't mind

The Syntax for IF is

IF condition THEN
   -- do something
END IF

Everything else results in errors

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `p`()
BEGIN 
    IF COL_LENGTH('sitesettings', 'backgroundColor') IS NULL THEN
        BEGIN 
            ALTER TABLE sitesettings ADD backgroundColor varchar(255) DEFAULT '#202225'; 
        END;
   END IF;
    IF COL_LENGTH('sitesettings', 'logintype') IS NULL THEN
        BEGIN 
            ALTER TABLE sitesettings ADD logintype INT DEFAULT 1 ;
        END; 
    END IF;
    IF NOT EXISTS (SELECT 'X' 
        FROM   INFORMATION_SCHEMA.TABLES 
        WHERE  TABLE_NAME = 'classicusers')  THEN
        BEGIN 
            CREATE TABLE classicusers( 
            id TEXT NOT NULL, 
            username TEXT NOT NULL, 
            password TEXT NOT NULL); 
        END;
    END IF;
END$$
DELIMITER ;

Worked, now just have this `FUNCTION fgfc004.COL_LENGTH does not exist` but it should though right? — FAXES, Feb 24 '20 at 08:53
There is no COL_LENGTH in mysql only in ms sql , so you have to write a function that has somethin like this https://stackoverflow.com/a/10492187/5193536 — nbk, Feb 24 '20 at 08:59

SQL Syntax Error With Procedure with using COL_LENGTH()

1 Answers1