| Bug #63835 | Cann't get ResultSet after using CURSOR in procedure | ||
|---|---|---|---|
| Submitted: | 22 Dec 2011 8:31 | Modified: | 17 Nov 2022 19:29 |
| Reporter: | Tank W | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / C++ | Severity: | S3 (Non-critical) |
| Version: | 1.0.5 | OS: | Windows (windows server 2008) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | cursor, ResultSet, sql::PreparedStatement | ||
[22 Dec 2011 8:40]
Tank W
step 4 changes "SET rstRoleID = '';" to "SET rstRoleID = '1';"
[23 Dec 2011 4:12]
Tank W
It's ok when I changed mysql server 5.5 to 5.1. It means the problem is about mysql server 5.5.
[26 Dec 2011 17:58]
Sveta Smirnova
Thank you for the report. Please send us full C++ code you use.
[27 Dec 2011 1:25]
Tank W
EnmDBResult UserDAL::GetUserByKey(UserDef *o_ent, const char * userName)
{
if(!o_ent)
{
return EnmDBResult_FAIL;
}
sql::PreparedStatement *pstmt = NULL;
sql::ResultSet *res = NULL;
EnmDBResult rst = EnmDBResult_FAIL;
try
{
pstmt = ((sql::Connection*)_conn)->prepareStatement("CALL SP_User_SELECT_BY_ID(?)");
pstmt->setString(1, userName);
pstmt->executeQuery();
res = pstmt->getResultSet();
if (res->next())
{
rst = SetStringValue(&o_ent->UserName, F_User_UserName, _pool, res);
if (rst == EnmDBResult_OK)
{
rst = SetStringValue(&o_ent->Password, F_User_Password, _pool, res);
}
if (rst == EnmDBResult_OK)
{
rst = SetStringValue(&o_ent->Name, F_User_Name, _pool, res);
}
if (rst == EnmDBResult_OK)
{
rst = SetStringValue(&o_ent->Email, F_User_Email, _pool, res);
}
if (rst == EnmDBResult_OK)
{
/// Get role list
int len = 0;
std::string strGet = res->getString(F_User_RoleID);
len = strGet.length();
if (len > 0)
{
o_ent->RoleIDArray = apr_array_make(_pool, 1, sizeof(apr_int32_t));
char *strList = (char *)apr_palloc(_pool, len + 1);
memcpy(strList, strGet.c_str(), len);
strList[len] = 0;
char *last;
char *cur = apr_strtok(strList, kDBSplit, &last);
while(cur != NULL)
{
int *temp = (int *)apr_array_push(o_ent->RoleIDArray);
*temp = apr_atoi64(cur);
cur = apr_strtok(NULL, kDBSplit, &last);
}
}
else
{
o_ent->RoleIDArray = NULL;
}
}
}
else
{
rst = EnmDBResult_NotExist;
}
}
catch (sql::SQLException &e)
{
WriteSQLException(&e, "UserDAL::GetUserByKey");
rst = EnmDBResult_SQLException;
}
EnmDBResult cret = CloseExecute(pstmt, res, ((sql::Connection*)_conn));
if(cret != EnmDBResult_OK)
{
rst = cret;
}
return rst;
}
[29 Dec 2011 19:48]
Sveta Smirnova
Thank you for the feedback. Verified as described. Version 1.1.0 does not allow me to execute executeQuery on procedure which returns result set at all. 5.5 vs 5.1 issue, I think, happens, because 5.5 handles warnings in handlers differently.
[29 Dec 2011 19:51]
Sveta Smirnova
short test case and how-to
Attachment: bug63835.howto.txt (text/plain), 2.30 KiB.
[30 Dec 2011 1:38]
Tank W
Thank you for your answer
[17 Nov 2022 19:29]
Luis Silva
Posted by developer: Problem no longer reproducible on current connector with 5.7 or 8.0 server.
Description: It returns 1 recordIwhen i run "CALL SP_User_SELECT_BY_ID('andy')" in MySQL Command Line Client,but I cann't get ResultSet by PreparedStatement.getResultSet() in c++. It's ok in c++ if comment the sql statements about cursor. IDE: vs2008 MysQL: 5.5 How to repeat: step 1: -------------------------------------------------------------------------------- CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_User_SELECT_BY_ID`( IN pPK_UserName varchar(192)) BEGIN DECLARE tempRoleID varchar(16); DECLARE rstRoleID varchar(128); #get user role list，split by "\n". DECLARE noMore int; DECLARE curRole CURSOR FOR SELECT `PFK_RoleID` FROM `TC_UserRole` WHERE `PFK_UserName` = pPK_UserName; DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMore = 1; ### SET rstRoleID = ''; SET noMore = 0; OPEN curRole; FETCH curRole INTO tempRoleID; WHILE noMore <> 1 DO SET rstRoleID = CONCAT(rstRoleID, tempRoleID); FETCH curRole INTO tempRoleID; IF noMore <> 1 THEN SET rstRoleID = CONCAT(rstRoleID, '\n'); END IF; END WHILE; CLOSE curRole; IF rstRoleID <> '' THEN SELECT TC_User.*, rstRoleID AS FK_RoleID FROM `TC_User` WHERE `PK_UserName` = pPK_UserName; END IF; END step 2: run "CALL SP_User_SELECT_BY_ID('andy')" in MySQL Command Line Client. -------------------------------------------------------------------------------- mysql> call sp_user_select_by_id('andy') \G *************************** 1. row *************************** PK_UserName: andy F_Password: 123456 F_Name: andy Lee F_Email: andy@gmail.xom F_Status: 0 F_ExpireDate: 123147879 F_RegisterTime: 1231568789 F_RegisterIP: 127.0.0.1 FK_RoleID: 0 1 2 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec) step 3: access in c++, res->next() return false. -------------------------------------------------------------------------------- sql::PreparedStatement *pstmt = NULL; sql::ResultSet *res = NULL; try { pstmt = _conn->prepareStatement("CALL SP_User_SELECT_BY_ID(?)"); pstmt->setString(1, userName); //(userName = "andy") pstmt->execute(); res = pstmt->getResultSet(); if (res->next()) { //... } //... step 4: comment the sql statements about cursor. -------------------------------------------------------------------------------- ... ### SET rstRoleID = ''; SET noMore = 0; -- OPEN curRole; -- FETCH curRole INTO tempRoleID; -- WHILE noMore <> 1 DO -- SET rstRoleID = CONCAT(rstRoleID, tempRoleID); -- FETCH curRole INTO tempRoleID; -- IF noMore <> 1 THEN SET rstRoleID = CONCAT(rstRoleID, '\n'); -- END IF; -- END WHILE; -- CLOSE curRole; IF rstRoleID <> '' THEN ... step 5: access in c++, res->next() return true, and got the data I want. -------------------------------------------------------------------------------- sql::PreparedStatement *pstmt = NULL; sql::ResultSet *res = NULL; try { pstmt = _conn->prepareStatement("CALL SP_User_SELECT_BY_ID(?)"); pstmt->setString(1, userName); //(userName = "andy") pstmt->execute(); res = pstmt->getResultSet(); if (res->next()) { //... } //...