MySQL Bugs: #13373: upper, ucase string functions doesn't work

Bug #13373	upper, ucase string functions doesn't work
Submitted:	21 Sep 2005 9:58	Modified:	21 Sep 2005 15:22
Reporter:	Sergi Vergés	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0 BK source	OS:	Windows (Windows 2000/Linux)
Assigned to:		CPU Architecture:	Any

Description:
String functions upper and ucase via java connector doesn't work fine.

The database version is 5.0.11, and if I use the functions on mySQL Client these works fine.

In previous versions of java connector these functions where working correctly.

How to repeat:
create a jsp
update a database varchar field, using string functions upper or ucase.

Sorry I was not able to reproduce the bug in my test case. Coud you please create and attach some test case ?

- mysql code to create the table and the procedure that will be called in TEST data base
DELIMITER //
DROP TABLE SERGI
//
CREATE TABLE SERGI(A VARCHAR(100), B VARCHAR(100), C VARCHAR(100))
//
DROP PROCEDURE pr_ucase
//
CREATE PROCEDURE pr_ucase(IN p_name VARCHAR(100))
BEGIN
	
	/*INSERT THE VALUES*/
	INSERT INTO SERGI(A,B, C)
	VALUES(p_name, UPPER(p_name),UCASE(p_name));
END
//
DELIMITER ;

- java code. It calls the procedure, and the show inserted records to the console

  public static void main(String[] args)  
  {
    Connection conn = null;
    CallableStatement cstmt = null;
    PreparedStatement stmt = null;
    ResultSet rset = null;  
  try 
  {
  
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "password");          
      conn.setAutoCommit(false);

      if (cstmt!=null) cstmt.close();  
      cstmt = ( CallableStatement )conn.prepareCall(
           "{ call test.pr_ucase( ? )  }" );
      cstmt.setString( 1, "sergi");

      cstmt.execute();      
      conn.commit();
      
      if (stmt!=null) stmt.close();
      if (rset!=null) rset.close();
      stmt=conn.prepareStatement( "select a,b,c from SERGI ");
      rset = stmt.executeQuery( );
      
      while (rset.next()){
        System.out.println("original="+rset.getString(1)+" upper="+rset.getString(2)+" ucase="+rset.getString(3));
      }
      
  } catch (Exception ex) 
  {
    ex.printStackTrace();
  }
  }

- just create a java class, paste the main method
- be sure the jconnector 3.1.6 are in the classpath
- run the java class

Two questions. First, what do you mean by "doesn't work", please be more specific. Second, 3.1.6 isn't the latest released version of Connector/J 3.1, you should make sure that 3.1.10 doesn't solve the issue, since there were many changes between 3.1.6 and 3.1.10 regarding stored procedures and string handling when the server returns "vague" metadata.

Please get back to us when you have answers to both questions.

What you meant works in mysql client? Testing with source server it not works
to me with client, so it is a SP issue:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.14-rc-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DELIMITER //
mysql> CREATE DATABASE hx//
Query OK, 1 row affected (0.03 sec)

mysql> USE hx//
Database changed
mysql> CREATE TABLE SERGI(A VARCHAR(100), B VARCHAR(100), C VARCHAR(100))
    -> //
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE PROCEDURE pr_ucase(IN p_name VARCHAR(100))
    -> BEGIN
    -> 
    -> /*INSERT THE VALUES*/
    -> INSERT INTO SERGI(A,B, C)
    -> VALUES(p_name, UPPER(p_name),UCASE(p_name));
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL pr_ucase("sergi");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * from SERGI;
+-------+-------+-------+
| A     | B     | C     |
+-------+-------+-------+
| sergi | sergi | sergi |
+-------+-------+-------+
1 row in set (0.00 sec)

mysql>

FIRST: "doesn't work" means that the functions doesn't do what's expected, I mean thant doesn't transform the string to UPPERCASE.
Example done witht MySQL Client:

mysql> select upper('sergi'), ucase('sergi');
+----------------+----------------+
| upper('sergi') | ucase('sergi') |
+----------------+----------------+
| SERGI          | SERGI          |
+----------------+----------------+
1 row in set (0.00 sec)

SECOND: I've tested with 3.1.10 and 3.2.0 alpha J/connector, and the behaviour is the same.

If you look your test you can notice that calling the procedure with MySQL Client, the upper and ucase functions are not working too.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.14-rc-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DELIMITER //
mysql> CREATE DATABASE hx//
Query OK, 1 row affected (0.00 sec)

mysql> USE hx//
Database changed
mysql> CREATE TABLE SERGI(A VARCHAR(100), B VARCHAR(100), C VARCHAR(100))
    -> //
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE PROCEDURE pr_ucase(IN p_name VARCHAR(100))
    -> BEGIN
    -> 
    -> /*INSERT THE VALUES*/
    -> INSERT INTO SERGI(A,B, C)
    -> VALUES(p_name, UPPER(p_name),UCASE(p_name));
    -> END
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> CALL pr_ucase("sergi");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO SERGI(A,B, C)
    -> VALUES("sergei", UPPER("sergei"),UCASE("sergei"));
Query OK, 1 row affected (0.00 sec)

mysql> SET @var_name="sergei";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SERGI(A,B, C)
    -> VALUES(@var_name, UPPER(@var_name),UCASE(@var_name));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * from SERGI;
+--------+--------+--------+
| A      | B      | C      |
+--------+--------+--------+
| sergi  | sergi  | sergi  |
| sergei | SERGEI | SERGEI |
| sergei | SERGEI | SERGEI |
+--------+--------+--------+
3 rows in set (0.00 sec)

mysql>

This is a duplicate of Bug #12903.