Bug #63835 Cann't get ResultSet after using CURSOR in procedure
Submitted: 22 Dec 2011 8:31 Modified: 30 Dec 2011 1:38
Reporter: Tank W Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / C++ Severity:S3 (Non-critical)
Version:1.0.5 OS:Microsoft Windows (windows server 2008)
Assigned to: CPU Architecture:Any
Tags: cursor, ResultSet, sql::PreparedStatement

[22 Dec 2011 8:31] Tank W
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())
    {
	//...
    }
    //...
[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