Start a new topic

Linked Tables in H2

[This topic is migrated from our old forums. The original author name has been removed]

I want to link to tables in a SQL Server 2005 db from H2. I can can connect from DbVis to both databases, and to H2 in either server mode or embedded mode but cannot create the linked table inside DbVis when H2 is in embedded mode. I think it is a classpath issue that I can't figure out how to set in DbVis. The H2 driver can't find the sqlserver driver. H2 has the create linked table feature that enables you to link a table in some other external database so you can see it in your H2 database, (see http://www.h2database.com/html/grammar.html?highlight=linked&search=linked#create_linked_table). Key to making this work of course is H2 must be able to find the jdbc driver for SQL server. H2 has an environment variable H2DRIVERS that you use to add the path to additional drivers. I am able to create links to my SQL Server 2005 database from H2 in the H2 console without any issues in either embedded or server mode. I can create the linked table from DbVis if H2 is run in server mode because H2 is running in a separate process and managing the classpath so it finds the driver via H2DRIVERS. The server mode is slower than embedded so I'd like to run H2 in embedded mode from DbVis but this means I need to be able to set additional classpath information for the linked drivers for H2. Does anyone have suggestions or can point me to docs to address this? Many thanks, Peter

[This reply is migrated from our old forums.]

Re: Linked Tables in H2
Tim, Try enclose the table name in double-quotes: select name from "myProduct" Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]
[Attachment has been removed.]

Re: Linked Tables in H2
Roger, I've followed your instructions and I've got the linked tables within my H2 db setup. If I double click the tables I can get a row count and see the data when I use the tabs however, if I attempt to write a select statement to query the tables I get a table not found error. Any help would be greatly appreciated. Thanks, - Tim
[This reply is migrated from our old forums. The original author name has been removed]

Re: Linked Tables in H2
Works perfect, many thanks. Apologies for not searching the existing FAQs thoroughly. Peter
[This reply is migrated from our old forums.]

Re: Linked Tables in H2
Peter, In order for a linked table setup in H2 to work properly with different database brands you must load the required driver file(s) with the H2 Embedded driver in DbVisualizer. Do this in Tools->Driver Manager, select the H2 Embedded driver in the list. In the right pane make sure the User Specified tab is selected and load the actual driver jar file(s). This should be all that is needed. Then to map a remote table with H2, navigate to the Linked Tables object in the Databases tab for the H2 connection and right-click and pick "Create Linked Table". Specify the required information. You find more information in: http://www.dbvis.com/doc/faq/#4.14 Regards Roger