The linked table capability in H2 is great! It opens the
possibilities for so many things.
However, I'm having a couple of issues that I'm having to work-around
using views:
1. The syntax for a linked table allows using a subquery or'(query-
string)' in lieu of the table name. If the query string itself must
contain single quotes, then I understandably get a syntax error as the
parser doesn't know I'm trying to embed quotes in my string.
Example: Executing
create linked table lt1 ('mydriver','myurl','user','pass',NULL,
'(select 'fish' as dinner,order_header.*,order_line.*order_line
from demarc.order_header , demarc.order_line where
order_header.orderid = order_line.orderid)')
gets: [Error Code: 42001, SQL State: 42001] Syntax error in SQL
statement "CREATE LINKED TABLE...
If I escape the quotes, it doesn't appear to make any difference.
Example: Executing
create linked table lt1 ('mydriver','myurl','user','pass',NULL,
'(select \'fish\' as dinner,order_header.*,order_line.*order_line
from demarc.order_header , demarc.order_line where
order_header.orderid = order_line.orderid)')
Seems like a bug or is there something I don't understand about
escaping quotes?
work-around: create the linked table without using single quotes,
then create a view with the linked table and your single quoted
items.
create linked table lt1 ('mydriver','myurl','user','pass',NULL,
'(select order_header.*,order_line.*order_line from
demarc.order_header , demarc.order_line where order_header.orderid =
order_line.orderid)');
create view v1 as select 'fish' as dinner,* from lt1;
It would be nice if I could create a linked table that did this
without using a view.
2. Given the linked table "lt1" from the previous example, if I
execute the statement:
select count(*) from lt1 then I get the following error:
cause: "java.sql.SQLException: ERROR: subquery in FROM must have an
alias
HINT: For example, FROM (SELECT ...) [AS] foo.;
ERROR: subquery in FROM must have an alias
HINT: For example, FROM (SELECT ...) [AS] foo."; SQL statement:
select count(*) from lt1 [90111-162] 90111/90111 (Help)
This is a little bewildering, as the syntax for the linked table does
not provide for an alias, and providing an alias there or with this
statement doesn't work either.
To add to this confusion, the statement "select * from lt1" *does*
work as expected.
Also, the error is not produced until AFTER the query runs - so it can
be quite a while before you even get this error message.
Work-around: Again, a view can be used to get around the issue:
create view v2 as select * from lt1;
select count(*) from v2;
However, now H2 is producing the count(*) instead of the remote
database doing so - this can mean a huge difference in performance and
can really be a problem for large remote tables - since now it is
sending something from every row for H2 to count - IE, 1 row vs all
rows.
3. non-functioning linked tables. I keep seeing an issue with linked
tables becoming non-functional. Suddenly, H2 doesn't show any columns
for the linked table and you can't select anything from it.
work-around - when you get an error selecting data from a linked
table, drop the table and re-create it, now you can use the table
again.
Any idea why the linked tables stop working? Could it be due to a
stale connection?
BTW - thanks for adding "DECODE" support to the oracle compatibility
mode!
Regards,
zing
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
