Skip to content

Latest commit

 

History

History
59 lines (41 loc) · 1.92 KB

sql_server.md

File metadata and controls

59 lines (41 loc) · 1.92 KB

SQL Server SQL Dialect

Microsoft SQL Server is a Relational Database Management System (RDBMS) developed by Microsoft.

Uploading the JDBC Driver to EXAOperation

First download the jTDS JDBC driver.

  1. Create a bucket in BucketFS
  2. Upload the driver to BucketFS

Installing the Adapter Script

Upload the latest available release of Virtual Schema JDBC Adapter to Bucket FS.

Then create a schema to hold the adapter script.

CREATE SCHEMA ADAPTER;

The SQL statement below creates the adapter script, defines the Java class that serves as entry point and tells the UDF framework where to find the libraries (JAR files) for Virtual Schema and database driver.

CREATE OR REPLACE JAVA ADAPTER SCRIPT ADAPTER.JDBC_ADAPTER AS
  %scriptclass com.exasol.adapter.RequestDispatcher;
  %jar /buckets/<BFS service>/<bucket>/virtualschema-jdbc-adapter-dist-1.19.2.jar;
  %jar /buckets/<BFS service>/<bucket>/jtds.jar;
/

Defining a Named Connection

Define the connection to SQL Server as shown below. We recommend using TLS to secure the connection.

CREATE OR REPLACE CONNECTION SQLSERVER_CONNECTION
TO 'jdbc:jtds:sqlserver://<server name>:<port>/<database name>'
USER '<user>'
IDENTIFIED BY '<passsword>';

Creating a Virtual Schema

Below you see how an SQL Server Virtual Schema is created.

CREATE VIRTUAL SCHEMA <virtual schema name>
    USING ADAPTER.JDBC_ADAPTER
    WITH
    SQL_DIALECT = 'SQLSERVER'
    CONNECTION_NAME = 'SQLSERVER_CONNECTION'
    CATALOG_NAME   =  '<catalog name>'
    SCHEMA_NAME = '<database name>';

Testing inforamtion

The SQL Server Dialect was tested with the jTDS 1.3.1 JDBC driver and SQL Server 2014.