0

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.

  • Can you give a "SHOW CREATE TABLE DataLocations\G" and some sample lines of data (is that what is at the bottom of your code?). Please provide the data in the form of "INSERT INTO DataLocations VALUES(F1, F2,....Fn); and we can take a look. – Vérace Mar 24 '16 at 7:09
  • @Vérace , I just added SHOW CREATE TABLE DataLocations result and INSERT INTO scdvr.DataLocations (...) to the end of my original Stack Overflow question above. Thank you, – Frank Mar 24 '16 at 8:36
0

You have to put DELIMITER $$ at the beginning of MySQL script file.

DELIMITER $$
CREATE PROCEDURE spDataLocationSetActive
(             
          IN driveLetter5  varchar(128),
          IN active         tinyint(1)
)          
BEGIN
DECLARE J INT;
Select count(*) INTO J
                        From    DataLocations dl
                        Where   dl.DriveLetter = '/'  LIMIT 1;

IF (J IS NULL) THEN                        


        SELECT dl.Enabled FROM DataLocations dl;
ELSE        
        SELECT dl.Enabled FROM DataLocations dl;
END IF;

END

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.