MySQL Bugs: #30976: system variable returning a string from a stored procedure returns a null

Bug #30976	system variable returning a string from a stored procedure returns a null
Submitted:	12 Sep 2007 14:33	Modified:	31 Mar 2014 14:58
Reporter:	David Randolph	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.0.7	OS:	Windows
Assigned to:	Alexander Soklakov	CPU Architecture:	Any
Tags:	Connector/J

Description:
Using a system variable to return a string from a stored procedure returns a null when running the stored procedure through Connector/J

This process worked on:
Slackware 10.1
MySQL 5.0.25
Connector/j 5.0.4
Javac 1.5.0_09

For business reasons, this code was being moved to Windows
Windows XP
MySQL 5.0.40
Connector/J 5.0.7
Javac 1.6
and it doesn't work. Running the stored procedure from the mysql client returns the correct results but running the stored procedure from java returns null. 

How to repeat:
  --    This routine returns a result set of:
  --             ResponseCode char (1)
  --             Message      char (120)
  --             Transaction Number  BIGINT
  --             new chain id integer

CREATE Procedure gp_AddChain(
            IN Language    CHAR ( 1 ),
            IN newName  varchar ( 50 ),
            IN Addr1    varchar ( 50 ),
            IN Addr2    varchar ( 50 ),
            IN City     varchar ( 50 ),
            IN State       char ( 2 ),
            IN Zip         char ( 10 ),
            IN Phone    varchar ( 25 )
            )
BEGIN
  DECLARE new_id integer;
  DECLARE ErrorCode char (5);
  DECLARE ResponseCode char (1);

  SET ResponseCode= 'E';
  SET ErrorCode = 'HSTER';
  CALL gp_GetMessage ('HSTER', Language, @Found, @Message);

-- Do the insert

  INSERT INTO Chains( ChainName, Address1, Address2, City, State, PostalCode,
     Phone) VALUES( newname, Addr1, Addr2, City, State, Zip, Phone);

  SET new_id= Last_Insert_Id();
  SET ResponseCode= 'A';
  CALL gp_GetMessage ('APP  ', Language, @Found, @Message);
  CALL gp_IncTranCnt (0, @TranNumber); -- increment transaction count

-- Build the result set for the calling program

  SELECT ResponseCode, @Message, @TranNumber, new_id;

END;

running from the mysql client
mysql> call gp_AddChain ('E', 'NewChain', 'Addr1street', 'ad2', 'Plno', 'TX', '12345', '9726184199');
+--------------+----------------------+-------------+--------+
| ResponseCode | @Message             | @TranNumber | new_id |
+--------------+----------------------+-------------+--------+
| A            | Transaction Approved | 3           |      5 |
+--------------+----------------------+-------------+--------+
1 row in set (0.06 sec)

Query ok, 0 rows affected (0.08 sec)

Java code (being tested under eclipse so that we could single step it)

    public String[] addChain (
                 String Language,
                 String ChainName,
                 String Addr1,
                 String Addr2,
                 String City,
                 String sState,
                 String Zip,
                 String Phone
                 )
                       throws SQLException
    {
        String ResponseCode;
        String Message;
        String TranNumber;
        String ChainID;
        String [] results;
        PreparedStatement pstmt = null;
       // Validation Validate;

        results = new String [2];
        results [0] = "E";
        results [1] = "System Error";
        try
        {
            pstmt = con.prepareStatement(
                        "CALL gp_AddChain (?, " + // language
                                          "?, " + // name
                                          "?, " + // address1
                                          "?, " + // address2
                                          "?, " + // city
                                          "?, " + // state
                                          "?, " + // zip
                                          "?)"   // phone
                                               );

            pstmt.setString(1, Language);
            pstmt.setString(2, ChainName);
            pstmt.setString(3, Addr1);
            pstmt.setString(4, Addr2);
            pstmt.setString(5, City);
            pstmt.setString(6, sState);
            pstmt.setString(7, Zip);
            pstmt.setString(8, Phone);

            ResultSet rst = pstmt.executeQuery();
            rst.first();
            ResponseCode = rst.getString (1);
            Message      = rst.getString (2);
            if (ResponseCode.equalsIgnoreCase("A"))
            {
                TranNumber = rst.getString (3);
                ChainID = rst.getString ("new_id");
                results = new String[4];
                results [0] = ResponseCode;
                results [1] = Message;
                results [2] = TranNumber;
                results [3] = ChainID;
            }
            else
            {
                results = new String [2];
                results [0] = ResponseCode;
                results [1] = Message;
            }
        }
        finally
        {
            if (pstmt != null) pstmt.close();
        }
        return results;
    }

Thank you for a problem report. Please, try to repeat with a newer version of MySQL server, 5.0.45 or 5.0.48, and inform about the results. I want to be sure this problem is NOT related to server version.

The problem exists on version 5.0.45 community version nt

David, could you please attach full test case including connection string used and do test on latest snapshot? Do you have access to procedure body (with user that connects from Java)?

David, we are missing 2/3 of SP's and other things here... Are you interested in solving this at all?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

We redesigned our system months ago to work around this bug. We no longer have the SP's that show this bug.
Back then, I was surprised that the mySQL test bed didn't have the few extra lines to test for this bug.

David, thanks for replying. Can you please post things that are missing (as I requested earlier) so we can make full test case for your problem? It would be good for others with similar problems also.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I close this report as "Can't repeat" because there is no feedback for a long time and codebase is too old. Please, feel free to reopen it if the problem still exists in current driver.