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:Microsoft Windows
Assigned to: Alexander Soklakov CPU Architecture:Any
Tags: Connector/J

[12 Sep 2007 14:33] David Randolph
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;
    }
[12 Sep 2007 17:11] Valeriy Kravchuk
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.
[12 Sep 2007 17:28] David Randolph
The problem exists on version 5.0.45 community version nt
[11 Feb 2008 19:17] Tonci Grgin
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)?
[29 Feb 2008 9:52] Tonci Grgin
David, we are missing 2/3 of SP's and other things here... Are you interested in solving this at all?
[12 Mar 2008 0:00] Bugs System
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".
[12 Mar 2008 1:25] David Randolph
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.
[12 Mar 2008 8:11] Tonci Grgin
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.
[12 Apr 2008 23:00] Bugs System
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".
[31 Mar 2014 14:58] Alexander Soklakov
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.