| Bug #43556 | Proble retrieving columns from a result set generated using views | ||
|---|---|---|---|
| Submitted: | 11 Mar 2009 9:49 | Modified: | 12 Mar 2009 10:43 |
| Reporter: | Rob Stone | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 5.1.7 | OS: | Any (fails on linux and windows) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | column name, Result Set, VIEW | ||
[12 Mar 2009 10:28]
Tonci Grgin
Test case for our test framework.
Attachment: TestBug43556.java (text/x-java), 2.36 KiB.
[12 Mar 2009 10:34]
Tonci Grgin
Hi Rob and thanks for your report. I am unable to repeat the problem described with test case attached. Client on W2K8x64 with latest c/J 5.1 sources. MySQL server 5.1.31GA on OpenSolaris 2008.11 x64 remote host. Test case output: Done. Connected to 5.1.31-log java.vm.version : 1.5.0_17-b04 java.vm.vendor : Sun Microsystems Inc. java.runtime.version : 1.5.0_17-b04 os : Windows Server 2008, 6.0, x86 sun.management.compiler : HotSpot Client Compiler ------------------------------------------------- Time: 0,349 OK (1 test) Please attach fully functioning test case next time to avoid misunderstanding and possible syntax errors. Please try to repeat the problem with latest c/J build from snapshots or build it yourself. Problem described here is known and in server metadata (no "Org_table" returned) but it has been fixed. Start mysql cl client with "--column-type-info" (for 5.1 +) or with "-T" (5.0) and observe metadata returned. c:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -h** -p --column-t ype-info test Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 173 Server version: 5.1.31-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SELECT test.vbug43556.VChr FROM vbug43556; Field 1: `VChr` Catalog: `def` Database: `test` Table: `vbug43556` Org_table: `vbug43556` <<< This used to be empty Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 20 Max_length: 5 Decimals: 0 Flags: +-------+ | VChr | +-------+ | Row 1 | +-------+ 1 row in set (0.00 sec)
[12 Mar 2009 10:35]
Tonci Grgin
Ahh, one more comment. I believe that code working around server metadata bug was removed as server problem was fixed. Check changelogs.
[12 Mar 2009 10:43]
Rob Stone
Thanks Toni, we are currently using MySQL server version 5.0. As we have a workaround (removing the table names) we aren't too worried about this problem, it is good to know that it won't happen when we eventually upgrade to 5.1. Cheers Rob
Description: My application generates SQL statements, the results of which are then processed by the same application. Recently we have converted one of our tables into a view and some of the generated queries no longer work. The problem is caused by the queries including fully qualified column names (ie. tablename.columnname) in the select statement, the application then retrieves the data from the result set using queryResults.getString('tablename.columnname'). This works fine for tables, but fails when the table is really a view. How to repeat: For the following SQL: select A.COL1 from A If A is a table attempts to access A.COL1 using results.getString("A.COL1") succeed. If A is a view then results.getString("A.COL1") will fail with an unknown column error. Suggested fix: Removing the table name from the query fixes this, however this is not an option when several tables are joined to produce the results. The spec suggests that specifying labels for the columns should work, however this is not an option for my application as there is currently no way in which labels can be specified in the configuration that is used to generate the queries.