The Database Connector is a JDBC based connector for working with databases in MATLAB.
It is both an object-oriented MATLAB layer to JDBC drivers and a smart mechanism to transport data from the Java to the MATLAB side in a performing and controlled manner.
Objectives & capabilities
Lower the price-tag
One of the two main objectives of the MonkeyProof Database Connector is to bring the cost of working with databases and MATLAB down. In many of our projects we need to interact with databases. The individual user (licensing) cost to access databases from MATLAB had to come down.
Data type mapping
MATLAB's standard Java interface does not provide sufficient control over how database types (both standard JDBC and database specific types) are converted into MATLAB data types. The Database Connector provides full control by means of configurable data type mapping.
Performance
MATLAB's standard Java interface puts a serious constraint on the performance when transporting database data from the Java side to MATLAB. This performance needed to be improved. The MonkeyProof Database Connector provides high-performance data transfer via native memory, as well as custom Java-MATLAB, and the standard Java-MATLAB interface.
Maximum fall-back
Maximum flexibility to handle unexpected behavior and non-standard configurations of JDBC drivers is achieved by making the underlying Java objects available to the user.
Configurability
A high level of configurability is required to allow for new flavors of databases to be supported. The MonkeyProof Database Connector has been tested with a variety of JDBC drivers without issues.
Callable Statements
Many of our database applications rely on server side functionality programmed in stored procedures. The MonkeyProof Database Connector contains functionality to retrieve the calling interface of the stored procedure and generate the necessary MATLAB code for you.
Robustness
The object-oriented architecture architecture of the Database Connector allows for an effective caching mechanism of defined prepared- and callable-statements. On a broken connection statement Java objects are typically lost. The Database Connector however, caches the necessary information in MATLAB to allow for quick re-instantiation of the Java Statement objects.
Single Sign-On (SSO)
The Database Connector supports single sign-on for Microsoft SQL Server on Windows.
Key features
- Cross-platform (Windows, Linux, Mac)
- Built on JDBC
- Optimized for performance
- Works with multiple database (flavors) in parallel
- MySQL, Microsoft SQL Server, and Oracle supported
- Other database flavors with JDBC drivers successfully tested
- MATLAB side caching of JDBC objects
- Retrieve data into MATLAB cells, structures, and tables
- Supports plain SQL, prepared and callable statements
- Supports transactions
- Supports MS SQL Server Single Sign-On on Windows
- Data-type mapping configurable (services available)
- Underlying Java objects available for maximum control
- Hooks for execution of MATLAB code on e.g. establishment of a connection
- Create callable statement objects by procedure name
- Generate template code for working statements
Code examples
First, a plain SQL based quick-start example is provided. Second, a slightly more advanced example is given in which the information from the database is being used to create the MATLAB Callable Statement object.
Example 1: Getting started
This example includes the code for database connector object creation and clean-up.
% Install the mysql driver specifics once
monkeyproof.database.install('mysql.xml');
% Create a mysql database connector object
% First argument defines the database configuration to use
% Next arguments are self explaining name-value pairs
dbc = monkeyproof.database.jdbc('mysql.xml', ...
'address', 'myserver.mydomain', ...
'databaseName', 'mydatabase');
% Just query away
% The connector will ask for your credentials
q = dbc.query('SELECT * FROM mytable');
% Log off
dbc.exit
% Cleanup
dbc.delete
Example 2: Working with callable statements
This code excludes database object creation and clean-up.
% Create statement based on procName, procSchema, procCatalog
myCallStmt = dbc.createCallStmt('createNewUser', 'dbo', dbc.databaseName)
% Note: A unique identifier 'mydatabase.dbo.createNewUser' has been
% generated from the location of the procedure in the database
% Generate template code for using the callable statement (output not shown)
myCallStmt.getCode()
% Code below (input struct and calling syntax) based on generated template
myNewUser = struct( ...
'firstname', 'John', ...
'lastname', 'Doe', ...
'e_mail', 'john.doe@example.com', ...
'username', 'john.doe', ...
'status', 'Active');
% Do the actual call
result = dbc.updateCallStmt('mydatabase.dbo.createNewUser', myNewUser)