I cannot SELECT on a LINKED TABLE.

[Lee,YB]

Hi,

My 'LINKED TABLE' does not work as expected.

I can execute simple 'SELECT' statement on a linked table,
but 'SELECT' statement fails when SQL statement contains 'WHERE'
clause with any column name,
saying "Column XXX not found" when it exists.

Is this a bug or an designed behavior? Any Workaround ?

I use the following statements on H2 1.0.75 :

----- SQL Stamentes ---------------------

[Creating LINKED TABLE:]
CREATE LINKED TABLE link_mouse430_2('com.mysql.jdbc.Driver',
'jdbc:mysql://remoteserver.com:3306/annotation', 'userid', 'userpw',
'mouse430_2');


[This SQL works:]
select * from link_mouse430_2
create table mouse430_2 as select * from link_mouse430_2


[This SQL does not work:]
select * from link_mouse430_2 where probe_id = 'a';

Column PROBE_ID not found; SQL statement:
select * from link_mouse430_2 where probe_id = 'a' [42122-75]
42S22/42122 (Help)
org.h2.jdbc.JdbcSQLException: Column PROBE_ID not found; SQL
statement:
select * from link_mouse430_2 where probe_id = 'a' [42122-75]
at org.h2.message.Message.getSQLException(Message.java:103)
at org.h2.message.Message.getSQLException(Message.java:114)
at org.h2.message.Message.getSQLException(Message.java:77)
at
org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:
125)
at org.h2.expression.Comparison.optimize(Comparison.java:133)
at org.h2.command.dml.Select.prepare(Select.java:688)
at org.h2.command.Parser.prepareCommand(Parser.java:235)
at org.h2.engine.Session.prepareLocal(Session.java:285)
at org.h2.engine.Session.prepareCommand(Session.java:246)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:
1042)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
at org.h2.server.web.WebThread.getResult(WebThread.java:1707)
at org.h2.server.web.WebThread.query(WebThread.java:1270)
at org.h2.server.web.WebThread.process(WebThread.java:437)
at org.h2.server.web.WebThread.processRequest(WebThread.java:184)
at org.h2.server.web.WebThread.process(WebThread.java:234)
at org.h2.server.web.WebThread.run(WebThread.java:194)

[Lee,YB]

Sorry for pool english.

I have found some error cause:

I cannot exec 'SELECT' statement without quoting 'column name'.
on the table created by 'LINKED TABLE'.

next 2 statement fails, saying "Column PROBE_ID not found".

select * from link_mouse430_2 where probe_id = 'a';

select * from link_mouse430_2 where PROBE_ID = 'a';

but this statement work good.

select * from link_mouse430_2 where 'probe_id' = 'a';

select * from link_mouse430_2 where 'PROBE_ID' = 'a';

Can I configure H2 server to 'SELECT' linked tables without quotation?

[Thomas Mueller]

Hi,

This is strange, H2 usually converts the column names of linked tables
to be case insensitive if required. I have just tested that in my
installation. I use MySQL 5.0.27-community-nt and
default-storage-engine=INNODB (in my.ini). What MySQL version and
storage engine do you use?

Could you please run this query in the H2 Console against the MySQL database?

@LIST @COLUMNS

This is an undocumented of the H2 Console - it runs
DatabaseMetaData.getColumns and displays the result as a list instead
of a table. Could you then copy the result and post the result? Please
set the 'Max rows' to 'All' before you run this statement.

Regards,
Thomas

[Lee,YB]

Thanks for your reply,

> This is strange, H2 usually converts the column names of linked tables
> to be case insensitive if required. I have just tested that in my
> installation. I use MySQL 5.0.27-community-nt and

I Installed MySQL Distrib 5.0.22 under Ubunto Linux,
And Since my OS is a linux, In my configuration, MySQL is case
senstitive to table name and *case insensitive* to column name.
I made MySQL table file as INNODB from windows xp, then moved the
files to ubunto server, and it works as expected, with usual SQL
statements.

> Could you please run this query in the H2 Console against the MySQL database?

> @LIST @COLUMNS

I found nothing strange, and google groups does not support html?.
please find attached below.

* You can also re-generate the case, since linked MySQL is open
readonly to internet. It is very small table (total row count = 132)


CREATE LINKED TABLE LINK_TABLE_INFO('com.mysql.jdbc.Driver',
'jdbc:mysql://genplex.co.kr:3306/annotation3', 'istech',
'annotation',
'table_info')


The test SQL which will fail : select * from link_table_info where
table_name = 'Rice'


Regards,
Lee,YB
--------------------------------------------

Attachment : case result of @LIST @COLUMNS on LINKED TABLE
Url : http://genplex.co.kr/temp/h2test.html

[Thomas Mueller]

Hi,

Thanks a lot! I think I have now found the problem. This will be fixed
in the next release (in about two weeks or so).

Regards,
Thomas