Bug #33744 Field values cannot be retrieved with rs.getString("viewname.columnname")
Submitted: 8 Jan 2008 16:33 Modified: 18 Feb 2009 18:31
Reporter: Donato Tagliabue Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.5 OS:Linux (Red Hat)
Assigned to: CPU Architecture:Any
Tags: column names, ResultSet, Views

[8 Jan 2008 16:33] Donato Tagliabue
Description:
Up to MySQL 5.0.27, it was possible to read data out from a resultset, with the following code:
ResultSet rs = aStatement.executeQuery("select afield from aview");
rs.next();
String theValue = rs.getString("aview.afield");

or also with:
String theValue = rs.getString("atable.afield");
where atable is the name of the table underlying the view and containing the field.

With MySQL 5.0.37 on Linux (tested also with 5.0.45 on Windows), and the latest connector/J, both the previous give a "aview.afield not found" "atable.afield not found" error.

The only possibility is to use:
String theValue = rs.getString("afield");
but in that way the table/view cannot be specified (if in the resultset there are two fields from different tables with the same names this might be a limitation).

How to repeat:
Just run the previous statements. I ran them inside a JSP page, using myISAM tables, Tomcat 6.0.14, connector/J 5.1.5 and got the errors.
[18 Feb 2009 18:31] Tonci Grgin
Donato, I apologize for missing this report... There has been much discussion regarding metadata returned from views, please search bugs db for this.

Please test yours select from view with mysql command line client started with -T (for MySQL server 5.0) or with --column-type-info (for MySQL server 5.1). *If* you can see something like:
mysql> select * from myview;
Field   1:  `ID`
Catalog:    `def`
Database:   `test`
Table:      `myview`  <<<<
Org_table:  `myview`  <<<<
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 2
Decimals:   0
Flags:      NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY

Field   2:  `NASCIMENTO`
Catalog:    `def`
Database:   `test`
Table:      `myview`  <<<<
Org_table:  `myview`  <<<<
Type:       DATE
Collation:  binary (63)
Length:     10
Max_length: 10
Decimals:   0
Flags:      BINARY

+----+------------+
| ID | NASCIMENTO |
+----+------------+
|  3 | 2007-01-15 |
|  5 | 2005-05-05 |
|  7 | 2003-03-03 |
|  8 | 2004-04-04 |
| 10 | 2012-12-12 |
+----+------------+
6 rows in set (0.00 sec)

then your code will work. Only drawback is if you need "original table name" (can't have it, but *clients* should never know they're selecting against a view).

Here's my code (mind you, Props is empty! Nothing special added!):
	Connection conn1 = null;
	try {
	     conn1 = getConnectionWithProps(props);
	     this.rs = this.stmt.executeQuery("SELECT Nascimento FROM myview");
	     this.rs.next();
	     System.out.println(rs.getString("myview.Nascimento"));
	     this.rs.close();
	     this.stmt.close();
	     DatabaseMetaData metaData1 = conn1.getMetaData();
	     this.rs = metaData1.getColumns(null, null, "myview", null);
	     this.rs.next();
	     assertEquals("myview", this.rs.getString("TABLE_NAME"));
	     assertEquals("ID", this.rs.getString("COLUMN_NAME"));
	     this.rs.next();
	     assertEquals("myview", this.rs.getString("TABLE_NAME"));
	     assertEquals("NASCIMENTO", this.rs.getString("COLUMN_NAME"));
--<cut>--
and the result:
java.vm.version         : 1.5.0_17-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_17-b04
Connected to 5.1.30-community-log
os.name                 : Windows Server 2008
os.version              : null
sun.management.compiler : HotSpot Client Compiler
2007-01-15

Time: 0,344

OK (1 test)