Import data from Microsoft SQL server to Matlab using JDBC driver

Since Matlab Visual Query Builder is a simple tool mainly for testing purpose, it is not recommended for setting up database connection within Matlab. This article talks about how to use Matlab Database toolbox functions to set up a connection to Microsoft SQL server database.

Environment

• Windows 7, 64-bit
• Matlab R2012a
• Microsoft SQL Server 2008

Installation

1. Download JDBC driver from Microsoft and install it so that Matlab is able to interact with SQL Server database via the Java interface.
• You can install the driver to a local folder, such as

  C:\Program Files\Microsoft SQL Server JDBC Driver 3.0

2. Insert the path of the database driver JAR file in the classpath.txt file
• The path file is located in the Matlab installation folder, such as

  C:\Program Files\MATLAB\R2012a\toolbox\local\classpath.txt

• A quick way to edit the path file is to run Matlab command:

  open('classpath.txt')

• Add the following line to the end of the file:
  

  C:\Program Files\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\sqljdbc4.jar

3. Insert the path to the folder containing sqljdbc_auth.dll in the librarypath.txt file
• The path file is located in the Matlab installation folder, such as

  C:\Program Files\MATLAB\R2012a\toolbox\local\librarypath.txt

• A quick way to edit the path file is to run Matlab command:

  open('librarypath.txt')

• Add the following line to the end of the file:
  

  C:\Program Files\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\auth\x64

  Note: The path entry should not include the file name sqljdbc_auth.dll.
• The sqljdbc_auth.dll file is installed in the following location:
  

  [installation]\sqljdbc_[version]\[language]\auth\[arch]

  where [installation] is the installation directory of the SQL server driver.
• If you are running a 32-bit Java Virtual Machine (JVM), use the sqljdbc_auth.dll file in the x86 folder, even if the operating system is the x64 version.
• If you are running a 64-bit JVM on a x64 processor, use the sqljdbc_auth.dll file in the x64 folder.
• If you are running a 64-bit JVM on a IA-64 processor, use the sqljdbc_auth.dll file in the IA64 folder.
4. Edit startup.m and add the following line to the end of the file:
   setdbprefs('DataReturnFormat','structure')
   • If you don't have a startup.m file, create one in your default Matlab directory:

     C:\users\user_name\Documents\MATLAB\

5. Notes:
   • If Matlab refuses to save these files, you may have to restart Matlab using Run as administrator and try again
   • Use path command to check whether following directories has been added to Matlab

     C:\Program Files\MATLAB\R2011a\toolbox\database\database

     C:\Program Files\MATLAB\R2011a\toolbox\database\dbdemos

     C:\Program Files\MATLAB\R2011a\toolbox\database\vqb

     If they aren't, type following commands to add them:

     addpath('C:\Program Files\MATLAB\R2011a\toolbox\database\database','-end');

     addpath('C:\Program Files\MATLAB\R2011a\toolbox\database\dbdemos','-end');

     addpath('C:\Program Files\MATLAB\R2011a\toolbox\database\vqb','-end');

Usage

• Use database function to connect to a MS SQL Server database, using following syntax
  conn = database(instance, username, password, driver, databaseurl)
  where the arguments, for connecting to MS SQL server with JDBC, are:
• Instance --- the name of the database, specified as a string
• username --- User name required to access database, specified as a string. If no user name is required, specify empty strings, ''.
• password --- Password required to access database, specified as a string. If no password is required, specify empty strings, ''.
• driver --- JDBC driver name, specified as a string. This is the name of the Java driver that implements the java.sql.Driver interface.
  For MS SQL server: JDBC Driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
• databaseurl --- Database connection URL, specified as a string. This is a vendor-specific URL that is typically constructed using connection properties like server name, port number, database name, and so on.
  For MS SQL server, database URL = jdbc:sqlserver://localhost:port;database=databasename
  Note: 1433 is a commonly used default port number.
• One example of Matlab code

  % Make connection to database using JDBC driver.
  conn = database('MARKET_DATA_NYMEX','username','password', …
  'com.microsoft.sqlserver.jdbc.SQLServerDriver', …
  'jdbc:sqlserver://111.22.333.44:1433;database=MARKET_DATA_NYMEX');
   
  % Read data from database.
  e = exec(conn,'SELECT * FROM TableName');
  setdbprefs('DataReturnFormat','cellarray')
  e = fetch(e);
  close(e)
   
  % Assign data to output variable.
  Exciting_Data = e.Data;
   
  % Close database connection.
  close(conn)