Introduction

There is a lot of useful information in MarkLogic Server's documentation surrounding many of the new features of MarkLogic 9 - including the new SQL implementation, improvements made to the ODBC driver and the new system for generating SQL "view" templates for your data. This article attempts to pull it all together by showing all the measures needed to create a successful connection and to verify that everything is set up correctly and works as expected?

This guide presents a step-by-step walk through covering the installation of all the necessary components, the configuration of the ODBC driver and the loading of data into MarkLogic in order to create a Template View that will allow a SQL query to be rendered.

Prerequisites

We're starting with a clean install of Redhat Enterprise Linux 7:

$ uname -a
Linux engrlab-128-084.engrlab.marklogic.com 3.10.0-327.4.5.el7.x86_64 #1 SMP Thu Jan 21 04:10:29 EST 2016 x86_64 x86_64 x86_64 GNU/Linux

In this example, I'm using yum to manage the additional dependencies (openssl-libs and unixODBC) required for the MarkLogic ODBC driver:

$ sudo yum install openssl-libs
Package 1:openssl-libs-1.0.2k-8.el7.x86_64 already installed and latest version
Nothing to do

$ sudo yum install unixODBC
Package unixODBC-2.3.1-11.el7.x86_64 already installed and latest version
Nothing to do

If you want to use the latest version of unixODBC (2.3.4 at the time of writing), you can get it using cURL by running curl -O ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.4.tar.gz

$ curl -O ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.4.tar.gz
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 1787k  100 1787k    0     0   235k      0  0:00:07  0:00:07 --:--:--  371k

Please note - as per the documentation, this method will require unixODBC to be compiled so additional dependencies may need to be met for this.

This article assumes that you have downloaded the ODBC driver for MarkLogic Server and the MarkLogic 9 install binary and have those available on your machine:

$ ll
total 310112
-r--r--r-- 1 support support 316795526 Nov 16 04:19 MarkLogic-9.0-3.x86_64.rpm
-r--r--r-- 1 support support    754596 Nov 16 04:18 mlsqlodbc-1.3-3.x86_64.rpm

Getting started: installing and configuring MarkLogic 9 with an ODBC Server

We will start by installing and starting MarkLogic 9:

$ sudo rpm -i MarkLogic-9.0-3.x86_64.rpm
$ sudo service MarkLogic start
Starting MarkLogic:                                        [  OK  ]

From there, we can point our browser at http://host:8001 and walk through the initial MarkLogic install process:

As soon as the install process has been completed and you have created an Administrator user for MarkLogic Server, we're ready to create an ODBC Application Server.

To do this, go to Configure > Groups > Default > App Servers and select the Create ODBC tab:

Next we're going to make the minimal configuration necessary by entering the required fields - the odbc server name, the Application Server module directory root and the port.

In this example we will configure the Application Server using the following values:

odbc server name

ml-odbc

root

/

port

5432

After this is done, confirm that the Application Server has been created by going to Configure > Groups > Default > App Servers and ensure that you can see the ODBC Server listed and configured on port 5432 as per the image below:

Getting started: Setting up the MarkLogic ODBC Driver

Use RPM to install the ODBC driver:

$ sudo rpm -i mlsqlodbc-1.3-3.x86_64.rpm
odbcinst: Driver installed. Usage count increased to 1.
    Target directory is /etc

Configure the base template as instructed in the installation guide:

$ odbcinst -i -s -f /opt/MarkLogic/templates/mlsql.template

Getting started: ensure unixODBC is configured

To ensure the unixODBC commandline client is configured, you can run isql -h to bring up the help options:

$ isql -h

**********************************************
* unixODBC - isql                            *
**********************************************
* Syntax                                     *
*                                            *
*      isql DSN [UID [PWD]] [options]        *
*                                            *
* Options                                    *
*                                            *
* -b         batch.(no prompting etc)        *
* -dx        delimit columns with x          *
* -x0xXX     delimit columns with XX, where  *
*            x is in hex, ie 0x09 is tab     *
* -w         wrap results in an HTML table   *
* -c         column names on first row.      *
*            (only used when -d)             *
* -mn        limit column display width to n *
* -v         verbose.                        *
* -lx        set locale to x                 *
* -q         wrap char fields in dquotes     *
* -3         Use ODBC 3 calls                *
* -n         Use new line processing         *
* -e         Use SQLExecDirect not Prepare   *
* -k         Use SQLDriverConnect            *
* --version  version                         *
*                                            *
* Commands                                   *
*                                            *
* help - list tables                         *
* help table - list columns in table         *
* help help - list all help options          *
*                                            *
* Examples                                   *
*                                            *
*      isql WebDB MyID MyPWD -w < My.sql     *
*                                            *
*      Each line in My.sql must contain      *
*      exactly 1 SQL command except for the  *
*      last line which must be blank (unless *
*      -n option specified).                 *
*                                            *
* Please visit;                              *
*                                            *
*      http://www.unixodbc.org               *
*      nick@lurcher.org                      *
*      pharvey@codebydesign.com              *
**********************************************

If you're not seeing the above message, it could be possible that there's another application on your system overriding this, for this configuration, the isql command is found at /usr/bin/isql:

$ which isql /usr/bin/isql

Getting started: initial connection test

If you're happy that isql is correctly, installed, we're ready to test the connection using isql -v:

$ isql -v MarkLogicSQL admin admin
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

Let's confirm that it's really working by loading some data into MarkLogic and creating an SQL view around that data.

Loading sample data into MarkLogic

To load data, we're going to use Query Console to insert the same sample data that is created in the Quick Start Documentation:

To access Query Console, point your browser at http://host:8000 and make note of the following:

Ensure the database is set to Documents (or at least, matches the database specified by your ODBC Application Server) and ensure that the Query Type is set to JavaScript

When these are both set correctly, run the code to generate sample data (note that this data is taken from the quick start guide and reproduced here for convenience):