DataReader is not returning results from a MySQL Stored Proc
- by Glenn Slaven
I have a stored proc in MySQL (5.5) that I'm calling from a C# application (using MySQL.Data v6.4.4.0).
I have a bunch of other procs that work fine, but this is not returning any results, the data reader says the result set is empty. The proc does a couple of inserts & an update inside a transaction then selects 2 local variables to return. The inserts & update are happening, but the select is not returning.
When I run the proc manually it works, gives a single row with the two fields, but the data reader is empty.
This is the proc:
CREATE DEFINER=`root`@`localhost` PROCEDURE `File_UpdateFile`(IN siteId INT, IN fileId INT, IN description VARCHAR(100), IN folderId INT, IN fileSize INT, IN filePath VARCHAR(100), IN userId INT)
BEGIN
START TRANSACTION;
SELECT MAX(v.versionNumber) + 1 INTO @versionNumber
FROM `file_version` v
JOIN `file` f ON (v.fileId = f.fileId)
WHERE v.fileId = fileId AND f.siteId = siteId;
INSERT INTO `file_version` (fileId, versionNumber, description, fileSize, filePath, uploadedOn, uploadedBy, fileVersionState)
VALUES (fileId, @versionNumber, description, fileSize, filePath, NOW(), userId, 0);
INSERT INTO filehistory (fileId, `action`, userId, createdOn) VALUES (fileId, 'UPDATE', userId, NOW());
UPDATE `file` f SET f.checkedOutBy = NULL WHERE f.fileId = fileId;
COMMIT;
SELECT fileId, @versionNumber `versionNumber`;
END$$
I'm calling the proc using Dapper, but I've debugged into the SqlMapper class and I can see that the reader is not returning anything.
