CONNECT JDBC Table Type: Accessing Tables from other DBMS

You are viewing an old version of this article. View the current version here.

The JDBC table type is a newly implemented table type and this first version should be regarded as experimental. In particular, if the automatic compilation of it should be ok after the java JDK was installed, the complete distribution of it is not yet implemented. It is possible that this first version be only available for MariaDB source distribution.

This will require that:

1. The Java SDK is installed on your system. 2. The file JdbcInterface.class is available on your system. 3. And of course, some JDBC drivers exist to be used.

As soon as possible, this will be made automatic, hopefully before you use this type.

Setting the required information

Before any operation with a JDBC driver can be made, CONNECT must initialize the environment that will make working with Java possible. This will consist of:

1. Loading dynamically the JVM library module. 2. Creating the Java Virtual Machine. 3. Establishing contact with the JdbcInterface wrapper class. 4. Connecting to the used JDBC driver.

Indeed, the JVM library module is not statically linked to the CONNECT plugin. This is to make possible using a CONNECT plugin that have been compiled with the JDBC table type on a machine where the Java SDK is not installed. Otherwise, users not interested by the JDBC table type would be still obliged to install the Java SDK on their machine to be able to load the CONNECT storage engine.

JVM Library Location

If the JVM library (jvm.dll on Windows, libjvm.so on Linux) was not placed in the standard library load path, CONNECT cannot find it and must be told where to search for. This happens in particular on Linux when the Oracle package was installed in private location.

Adding its search path to the LD_LIBRARY_PATH environment variable is possible but complicated because making an environment variable permanent on Linux is pain full (many different methods must be used depending on the Linux version and the used shell)

This is why CONNECT introduced a new global variable connect_jvm_path to store this information. It can be set when starting the server as a command line option or even afterward before the first use of the JDBC table type. For example:

set global connect_jvm_path="/usr/lib/jvm/java-8-oracle/jre/lib/i386/server"

Once this library is loaded, CONNECT can create the required Java Virtual Machine.

Java Class Path

This is the list of paths Java search when loading classes. With CONNECT, the classes to load will be the JdbcInterface.class, the wrapper class used to communicate with the drivers, and the used JDBC driver classes that are grouped inside jar files.

Caution: This class path is passed as a parameter to the Java Virtual Machine (JVM) when creating it and cannot be modified as it is a read only property. In addition, because MariaDB is a multi-threading application, this JVM cannot be destroyed and will be used throughout the entire life of the MariaDB server. Therefore, be sure it is correctly set before you use the JDBC table type for the first time. Otherwise there will be practically no alternative than to shut down the server and restart it.

The path to the JdbcInterface wrapper class depends on where the file JdbcInterface.class is located. It is unclear yet to know where because this will depend on the installation process. If you start from a source distribution, it will be in the storage/connect directory where are the source files of CONNECT.

Because the class path always includes the current directory, it is perhaps a good idea to place this file there (in sql/data).

Remains the paths of all the installed JDBC drivers that you intend to use. Some applications use an environment variable CLASSPATH to contain them, separated by ‘:’ on Linux and by ‘;’ on Windows. If it actually exists and if it is available inside MariaDB, so far so good. You can check this using an UDF function provided by CONNECT that returns environment variable values:

create function envar returns string soname 'ha_connect.so'; select envar('CLASSPATH');

Most of the time, this will return null. This is why CONNECT introduced a global variable to store this information. As for the jvm path, this variable connect_class_path should be specified when starting the server but can also be set before using the JDBC table type for the first time.

As an example, here is how I start MariaDB when doing tests on Linux:

olivier@olivier-Aspire-8920:$ sudo /usr/local/mysql/bin/mysqld -u root --console --default-storage-engine=myisam --skip-innodb --connect_jvm_path="/usr/lib/jvm/java-8-oracle/jre/lib/i386/server" --connect_class_path="/home/olivier/mariadb/10.1/storage/connect:/media/olivier/SOURCE/mysql-connector-java-6.0.2/mysql-connector-java-6.0.2-bin.jar"

CONNECT JDBC Tables

These tables are given the type JDBC. For instance, supposing you want to access the boys table located on and external local or remote database management system providing a JDBC connector:

create table boys ( name char(12), city char(12), birth date, hired date);

To access this table via JDBC you can create a table such as:

create table jboys engine=connect table_type=JDBC tabname=boys connection='jdbc:mysql:localhost/dbname?user=root';

The CONNECTION option is the URL used to establish the connection with the remote server. Its syntax depends on the external DBMS and in this example is the one used to connect as root to a MySQL or MariaDB local database using the MySQL JDBC connector.

As for ODBC, the columns definition can be omitted and will be retrieved by the discovery process. The restrictions concerning column definitions are the same than for ODBC.

JDBC specific restrictions

Connecting via data sources is not supported yet.

Other restrictions are the same as for the ODBC table type.

Connecting to a JDBC driver

The connection to the driver is established by the JdbcInterface wrapper class. To do so it uses the options that is provided when creating the CONNECT JDBC tables. Inside the Java wrapper, the driver’s main class is loaded by the DriverManager.getConnection function that takes three arguments:

URLThat is the URL that you specified in the CONNECTION option. User As specified in the OPTION_LIST or NULL if not specified. Password As specified in the OPTION_LIST or NULL if not specified.

The URL varies depending on the connected DBMS. Refer to the documentation of the specific JDBC driver for a description of the syntax to use. User and password can also be specified in the option list.

Note: In previous versions of JDBC, to obtain a connection, java first had to initialize the JDBC driver by calling the method Class.forName. In this case, see the documentation of your DBMS driver to obtain the name of the class that implements the interface java.sql.Driver. This name can be specified as an option DRIVER to be put in the option list. However, most modern JDBC drivers since version 4 are self-loading and do not require this option to be specified.

When establishing the connection, the wrapper class also creates some required items and in particular a statement class. Some characteristics of this statement will depend on the options specified when creating the table:

Scrollable Determines the cursor type: no= forward_only or yes=scroll_insensitive. Block_size Will be used to set the statement fetch size.

Fetch Size The fetch size determines the number of rows that are internally retrieved by the driver on each interaction with the DBMS. Its default value depends on the JDBC driver. It is equal to 10 for some drivers but not for the MySQL or MariaDB connectors.

The MySQL/MariaDB connectors retrieve all the rows returned by one query and keep them in a memory cache. This is generally all right performance wise but is not when retrieving a big result set that can make the query fail with memory exhausted exception.

To avoid this, when accessing a big table and expecting large result sets, you should specify the BLOCK_SIZE option to 1 (the only acceptable value) However a problem remains:

Suppose you execute a query such as:

select id, name, phone from jbig limit 10;

Not knowing the limit clause, CONNECT sends to the remote DBMS the query:

SELECT id, name, phone FROM big;

In this query big can be a huge table having million rows. Having correctly specified the block size as 1 when creating the table, the wrapper just reads the 10 first rows and stops. However, when closing the statement, these MySQL/MariaDB drivers must still retrieve all the rows returned by the query. This is why, the wrapper class when closing the statement also cancels the query to stop that extra reading.

The bad news is that if it works all right for the MySQL driver, it does not for the MariaDB driver that apparently ignores the cancel command. The good news is that you can use the MySQL driver to access MariaDB databases.

Random Access to JDBC Tables The same methods described for ODBC tables can be used with JDBC tables.

Note that in the case of the MySQL or MariaDB connectors, because they internally read the whole result set in memory, using the MEMORY option would be a waste of memory. It is much better to specify the use of a scrollable cursor when needed.

Other Operations with JDBC Tables

Except for the way the connection string is specified and the table type set to JDBC, all operations with ODBC tables are done for JDBC tables the same way. Refer to the ODBC chapter to know about:

Accessing specified views (SRCDEF) Data modifying operations. Sending commands to a data source. JDBC catalog information.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.