DataReader 未从 MySQL 存储过程返回结果
我在 MySQL (5.5) 中有一个存储过程，我从 C# 应用程序调用它（使用 MySQL.Data v6.4.4.0）。
我有很多其他工作正常的过程，但这没有返回任何结果，数据读取器说结果集是空的。该过程执行了一些插入和操作。事务内的更新然后选择 2 个局部变量返回。插入物和正在更新，但选择没有返回。
当我手动运行该过程时，它可以工作，给出包含两个字段的单行，但数据读取器为空。
这是过程：
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$$
我正在使用 Dapper 调用过程，但我已经调试到 SqlMapper 类，并且我可以看到读取器没有返回任何内容。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
作为解决方法 - 如果您需要从存储的函数中检索标量值，则可以通过 OUT 参数来完成。
格伦，我试图理解原因，发现就是这样。
实际上我们使用了另一个组件，您可能会在我们的 GUI 工具（dbForge Studio 的免费 Express 版本）中看到多个结果集的结果。
As a workaround - if you need to retrieve just a scalar value from a stored function, you can do it through the OUT parameters.
Glenn, I tried to understand the reason and found just this way.
Actually we use another components, you may see the result of multiple result-set in our GUI tool (free express version of dbForge Studio).