Why does this MySQL 5.6 stored procedure not compile with MySQL Workbench?

0 votes
1 answer
182 views
                          Why doesn't this MySQL 5.6 stored procedure compile with MySQL Workbench?

    CREATE PROCEDURE spDataLocationSetActive
    (             
              IN driveLetter    varchar(1),
              IN active         tinyint(1)
    )          
    BEGIN
    
    
    
    if(      Select  DriveLetter
                            From    DataLocations
                            Where   DriveLetter = driveLetter      )
    then
    
           
    	
                    UPDATE
                            DataLocations
                    SET
                            Active = 1
                    WHERE
                            DriveLetter = driveLetter;
    end if;
    
    END

    'DriveLetter', 'varchar(128)', 'NO', 'PRI', '', ''
    'Directory', 'varchar(200)', 'NO', '', 'SCR\\DATA\\', ''
    'MaxUsage', 'smallint(6)', 'NO', '', '95', ''
    'Sequence', 'smallint(6)', 'YES', '', NULL, ''
    'ErrorMessage', 'varchar(255)', 'YES', '', NULL, ''
    'DriveCapacity', 'bigint(20)', 'YES', '', NULL, ''
    'DriveFreeSpace', 'bigint(20)', 'YES', '', NULL, ''
    'Active', 'tinyint(1)', 'NO', '', '1', ''

Here is the result  of Show Create Table DataLocations.

    Table,"Create Table"
    DataLocations,"CREATE TABLE DataLocations (
      DriveLetter varchar(128) NOT NULL DEFAULT '',
      Directory varchar(200) NOT NULL DEFAULT 'SCR\\DATA\\',
      MaxUsage smallint(6) NOT NULL DEFAULT '95',
      Sequence smallint(6) DEFAULT NULL,
      ErrorMessage varchar(255) DEFAULT NULL,
      DriveCapacity bigint(20) DEFAULT NULL,
      DriveFreeSpace bigint(20) DEFAULT NULL,
      Active tinyint(1) NOT NULL DEFAULT '1',
      InactiveCount smallint(6) NOT NULL DEFAULT '0',
      LastInactiveDateTime datetime NOT NULL DEFAULT '2001-01-01 00:00:00',
      Enabled tinyint(1) NOT NULL DEFAULT '1',
      LastEnabledDateTime datetime NOT NULL DEFAULT '2001-01-01 00:00:00',
      PRIMARY KEY (DriveLetter),
      KEY IX_DataLocations_DriveLetter_Active_Enabled      (DriveLetter,Active,Enabled)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1"

and here is the  insert statement


    INSERT INTO scdvr.DataLocations
    (DriveLetter,
    Directory,
    MaxUsage,
    Sequence,
    ErrorMessage,
    DriveCapacity,
    DriveFreeSpace,
    Active,
    InactiveCount,
    LastInactiveDateTime,
    Enabled,
    LastEnabledDateTime)
    VALUES
    ('/',
    'SCDVR/',
    85,
    1,
    15,
    80000000000,
    1,
    0,
    '2001-01-01 00:00:00',
    1,
    '2001-01-01 00:00:00');



I was wondering how to fix its syntax. Thank you.
                        
Asked by Frank (167 rep)
Mar 24, 2016, 06:56 AM
Last activity: Jun 27, 2025, 10:00 AM
Why does this MySQL 5.6 stored procedure not compile with MySQL Workbench?

Related Questions
