This of course is possible but is quite a bit of work and I am not sure there is enough demand to undertake this.

Meanwhile, you can use a ODBC/JDBC bridge. There are a couple of commercial ones (for instance EasySoft at $450) Unfortunately, the SourgeForge project is dead, probably because is was not so easy to develop and/or not enough interest on it..

I shall look at the feasibility of it anyway.

After taking a look at it, does it look like the JNI would be easy to integrate?

My need for this is two fold... I am having trouble getting stable ODBC drivers that work with pools, etc. Nothing to do with CONNECT engine, but ODBC seems quite frail (at least on linux)... my experience with JDBC is that it just works (maybe I've been lucky). So being able to get completely out of the ODBC world sounds liberating.

No, it definitely won't be easy but feasible. And there are a lot of problems that will have to be solved.
For instance calling JDBC directly from C++ seems not possible because some functions have java objects as arguments that would be difficult to create and mainly because they can throw exceptions that I don't know how to handle with JNI.
Therefore an intermediate java class must be written that will call JDBC, MariaDB calling this class through JNI.
Many problem will be raised by how to compile and install it automatically.
Anyway I am working on it but don't look forward for it soon...

Fantastic. Let me know if I can be of any assistance!

Yes of course. I am working on Windows and you can help me testing on Linux.

I have attached three files, two of them allowing to test connecting to a data base server from a C++ program via the data source JDBC server (JdbcInterface.java is the source of the JdbcInterface class that I provided for information)

Several things must be done to compile and test this program.

• Java JDK must be installed.
• The Java VM program must be in the execution path.
• To compile the C++ interface, additional include directory must add the JDK include directory and the machine dependant sub-directory.
• To link edit the program, the JDK/lib/jvm.lib must be added.

When establishing the Java Virtual Machine, the classpath of all used classes must be passed as an option. To do this, edit the JdbcClient.cpp file and change the lines 26-28 to add the path to the JdbcInterface.class file and to the path of all the JDBC drivers you intend to use.

Also edit the lines 87-89 to set them according to the JDBC driver you are using (I was using the MySQL connector 5.1.17)

All this to give you an idea of the problems to solve to make this automatic as much as possible with a distributed version of MariaDB...

Good luck! Let me know how it works.

The JDBC table type is now ready and I have tested it successfully on Windows. I shall try to test it on Linux (ubuntu) as soon as possible.

However, there are distribution issues that must be solved and for which I am not conversant. I have asked proper persons at MariaDB for help but I don't know whether and when this will be solved.

This is fantastic news! I wasn't expecting this so soon.

Don't be too much excited. Manu distribution problems remain and I don't know when they will be solved.

However, if you work on a source distribution, I can send you the updated source code and try to explain how it should be compiled and installed.

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) but this has not been tested yet.

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. The paths specified in this variable will be added to the ones, if any, of the CLASSPATH environment variable. 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.

Note: For jar files, the paths specified in these variables must include the fully qualified jar file name, not only the directories.

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:

1. URL That is the URL that you specified in the CONNECTION option.
2. User As specified in the OPTION_LIST or NULL if not specified.
3. 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.

The newly annouced version 10.1.16 contains the source of the JDBC table type. Unfortunately this an outdated version that can be used only for pre-testing a future application.

Does this mean that the complied 10.1.16 version in the repos right now should be skipped?
Should I wait for 10.1.17?

I just tried Oracle 8 jvm on cent OS 7.

When I add the following line to my.cnf

connect_jvm_path=/opt/oracle/jdk1.8.0_101/jre/lib/amd64/server

then start the server it fails:

● mariadb.service - MariaDB database server

   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)

  Drop-In: /etc/systemd/system/mariadb.service.d

           └─migrated-from-my.cnf-settings.conf

   Active: failed (Result: exit-code) since Tue 2016-07-26 19:28:04 UTC; 1min 20s ago

  Process: 1638 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, stat

us=7)

  Process: 1568 ExecStartPre=/bin/sh -c VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] &&   systemctl set-environment _

WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)

  Process: 1566 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCC

ESS)

 Main PID: 1638 (code=exited, status=7)

   Status: "MariaDB server is down"

Jul 26 19:28:02 di-mariadb-test1 mysqld[1638]: 2016-07-26 19:28:02 140590093703296 [Note] InnoDB: 128 rollback segmen

t(s) are active.

Jul 26 19:28:02 di-mariadb-test1 mysqld[1638]: 2016-07-26 19:28:02 140590093703296 [Note] InnoDB: Waiting for purge t

o start

Jul 26 19:28:02 di-mariadb-test1 mysqld[1638]: 2016-07-26 19:28:02 140590093703296 [Note] InnoDB:  Percona XtraDB (ht

tp://www.percona.com) 5.6.30-76.3 started; log sequence number 494830563

Jul 26 19:28:02 di-mariadb-test1 mysqld[1638]: 2016-07-26 19:28:02 140590093703296 [Note] Plugin 'FEEDBACK' is disabl

ed.

Jul 26 19:28:02 di-mariadb-test1 mysqld[1638]: 2016-07-26 19:28:02 140590093703296 [ERROR] /usr/sbin/mysqld: unknown 

variable 'connect_jvm_path=/opt/oracle/jdk1.8.0_101/jre/lib/amd64/server'

Jul 26 19:28:02 di-mariadb-test1 mysqld[1638]: 2016-07-26 19:28:02 140590093703296 [ERROR] Aborting

Jul 26 19:28:04 di-mariadb-test1 systemd[1]: mariadb.service: main process exited, code=exited, status=7/NOTRUNNING

Jul 26 19:28:04 di-mariadb-test1 systemd[1]: Failed to start MariaDB database server.

Jul 26 19:28:04 di-mariadb-test1 systemd[1]: Unit mariadb.service entered failed state.

Jul 26 19:28:04 di-mariadb-test1 systemd[1]: mariadb.service failed.

[robert_dyas@di-mariadb-test1 ~]$ 

I don't remember exatly the status of the version that is distributed with MariaDB 10.1.16.

I think you can begin to play with it and see what happen. It should work in most cases.

About your Oracle bug, it look like CONNECT is not installed yet. Therefore the CONNECT variables are unknown and this make the starting of the server to fail. So remove it from your cnf file, start the server and install CONNECT. Then you can put it back to the cnf file.

connect is installed and works with ODBC
but when i add those two variables to the my.cnf and restart the server, it won't restart.

If connect is installed, this is because that old version does not implement the connect_jvm_path variable. So you cannot use it, either in the my.cnf file nor by entering a SET command.

Then, the only way to make it work is to export the environment variable LD_LIBRARY_PATH with the path to the jvm lib in it.

Sorry about that.

Yes, connect is installed and works with ODBC driver.
This is what I am adding to my.cnf under the [msqld] section:

connect_jvm_path=/opt/oracle/jdk1.8.0_101/jre/lib/amd64/server

connect_class_path=/opt/oracle/jdk1.8.0_101/jre/lib/ext

I assume that if the driver jar files are placed in .../jre/lib/ext then I don't need the connect_class_path... is that a correct assumption? Can it find the JdbcInterface jar itself? If not, where would it be located in the compiled/distro centos distribution directory hierarchy?

I can wait until 10.1.17 to test if that is necessary.

As I said in my last comment, you cannot use connect_jvm_path with 10.1.16.
I think your second assumption is false because finding the jvm lib is a C++ job that looks into the LD_LIBRARY_PATH (or its defaut one) and finding jar files is a java job that look into the class path. Note that using the connect_class_path is a facility but you can also export the CLASS_PATH environment variable.
These exports can be done in a script that would start the server.

Normally, the 10.1.17 should install the JdbcInterface.jar file in the plugin directory.

MariaDB version 10.0.27 is available and contains the proper JDBC table support.