Bug #34034 MySQL Connector/J 5.1.5 SELECT field AS myname not myname in ResultSetMetaData
Submitted: 24 Jan 2008 13:47 Modified: 24 Jan 2008 13:58
Reporter: Steve Markowski Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version: OS:Any
Assigned to: CPU Architecture:Any

[24 Jan 2008 13:47] Steve Markowski
Description:
From java, create a java.sql.Connection into the MySQL database using the Conneector/J 5.1.5 Driver.

Any SELECT field AS myname ... is not returned in the ResultSetMetaData.getColumnName()

How to repeat:
import java.util.*;
import java.io.*;
import java.sql.*;
import javax.sql.*;

/**
 * This example returns the following after build:
 * <PRE>
 * command line build step:
 * C:\>javac -cp ".;download/mysql/mysql-connector-java-5.1.5-bin.jar" mysqltest.java
 * 
 * command line run step (MySQL must be running on 3306):
 * C:\>java -cp ".;download/mysql/mysql-connector-java-5.1.5-bin.jar" mysqltest
 *  colname = [Db]   <<<<=== given the SQL statement SELECT Db AS MYDB FROM DB this is not correct
 *  value   = [test]
 * </PRE>
 **/
public class mysqltest {
	static String driverName = "com.mysql.jdbc.Driver";
	static String dbDriver   = "jdbc:mysql://127.0.0.1:3306/mysql";
	static String dbUser     = "root";
	static String dbPassword = "";

	public mysqltest(){}

	public static void main(String[] args) {
		String sql   = "SELECT Db AS MYDB FROM DB";
		String value = "";
		java.sql.Connection conn = null;

		try { Class.forName(driverName); } catch(Exception e) { e.printStackTrace(); }

		try { conn = DriverManager.getConnection(dbDriver, dbUser, dbPassword); } catch(Exception e) {}

		if(sql != null) {
			java.sql.Statement s = null;
			java.sql.ResultSet rs = null;
			try {
				s = conn.createStatement();
				rs = s.executeQuery(sql);

				ResultSetMetaData rsmd = rs.getMetaData();
				String colname = "";
				colname = rsmd.getColumnName(1);

				if(rs.next())
					value = rs.getString(1);

				System.out.println(" colname = ["+colname+"]");
				System.out.println(" value   = ["+value+"]");
			} catch(SQLException sqe) {
			} finally {
				if(s != null)  { try { s.close(); } catch(Exception ex) {} }
				if(rs != null)  { try { rs.close(); } catch(Exception ex) {} }
			}
		}

		try { conn.close(); } catch(Exception ex) {}

	}
}
[24 Jan 2008 13:58] Tonci Grgin
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Explanation:
Hi Steve and thanks for your report. I believe it is a duplicate of Bug#31499 (Bug#32504 and many more). The behavior you ask for isn't actually JDBC-compliant (which is why there was a change in behavior in 5.1). If you want the old, non-compliant behavior, you should add "useOldAliasMetadataBehavior=true" as a configuration parameter in your JDBC URL.

Note: The JDBC-compliant way of getting the information you're asking for, i.e. the "alias" for the column is by calling ResultSetMetaData.getColumnLabel(), not
getColumnName(). The reason that getColumnName() is _not_ supposed to return the alias, is because it is supposed to be possible for a consumer of this API to generate a DML statement based on the metadata present in the ResultSet and ResultSetMetaData, and thus getColumnName() should return the _physical_ column name, not the alias.