I was wondering if it was possible to link to a MySQL (MariaDB) DB and use
a table in that DB to join with a table that is in the HSQLDB, so that
these two tables DBs are joined together. Does anyone know the syntax for
the linking? Though, I would like to have the external table appear as a
view in the HSQLDB and then use that in various queries and joins, since I
will be doing several different types of queries on it.
Thanks,
Jon
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am not an expert, but I will try to help. I don't think HSQLDB supports remote tables. But you can do it the other way round. MySQL 5 has a feature called 'Federated tables' where the data is stored remotely in a different database. So, in theory, you could create a query in MySQL that accesses data from a remote table in HSQLDB.
To find out more, look up MySQL Federated table or ask in a forum about MySQL. You will probably need to use the standard JDBC connect string to connect to the HSQLDB database.
This approach seems very complicated and difficult to get working. It would be a lot easier just to replicate the table you need inside HSQLDB.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The above is correct. HSQLDB does not support remote tables directly.
There is a way to access such remote tables by writing a Java function in HSQLDB. The function connects to MySQL and performs a query to get a subset of the data in a table. It then creates an HSQLDB ResultSet and populates it with the data from the MySQL table. This result set is then returned by the HSQLDB function. The function can be called and used in an HSQLDB join with the TABLE(myfunction()) construct.
BTW, the above applies to the latest version 2.3.x, not version 1.8.x
Last edit: Fred Toussi 2014-05-13
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi all,
I was wondering if it was possible to link to a MySQL (MariaDB) DB and use
a table in that DB to join with a table that is in the HSQLDB, so that
these two tables DBs are joined together. Does anyone know the syntax for
the linking? Though, I would like to have the external table appear as a
view in the HSQLDB and then use that in various queries and joins, since I
will be doing several different types of queries on it.
Thanks,
Jon
I am not an expert, but I will try to help. I don't think HSQLDB supports remote tables. But you can do it the other way round. MySQL 5 has a feature called 'Federated tables' where the data is stored remotely in a different database. So, in theory, you could create a query in MySQL that accesses data from a remote table in HSQLDB.
To find out more, look up MySQL Federated table or ask in a forum about MySQL. You will probably need to use the standard JDBC connect string to connect to the HSQLDB database.
This approach seems very complicated and difficult to get working. It would be a lot easier just to replicate the table you need inside HSQLDB.
The above is correct. HSQLDB does not support remote tables directly.
There is a way to access such remote tables by writing a Java function in HSQLDB. The function connects to MySQL and performs a query to get a subset of the data in a table. It then creates an HSQLDB ResultSet and populates it with the data from the MySQL table. This result set is then returned by the HSQLDB function. The function can be called and used in an HSQLDB join with the TABLE(myfunction()) construct.
BTW, the above applies to the latest version 2.3.x, not version 1.8.x
Last edit: Fred Toussi 2014-05-13