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: | |
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
[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)