Create JDBC Data Source and Set Options Programmatically

Open Live Script

This example shows how to create a JDBC data source at the command line, configure the data source by setting JDBC connection options, set additional JDBC driver-specific options, and save the data source. The example configures a data source for a Microsoft® SQL Server® database.

Create JDBC Data Source

Create an SQL Server data source.

opts = configureJDBCDataSource("Vendor","Microsoft SQL Server")

opts = 
  JDBCConnectionOptions with properties:

                      Vendor: 'Microsoft SQL Server'
              DataSourceName: ''

                DatabaseName: ''
                      Server: 'localhost'
                  PortNumber: 1433
                    AuthType: 'Server'

          JDBCDriverLocation: ''

opts is a JDBCConnectionOptions object with these properties:

Vendor — Database vendor name
DataSourceName — Name of the data source
DatabaseName — Name of the database
Server — Name of the database server
PortNumber — Port number
AuthType — Authentication type
JDBCDriverLocation — Full path of the JDBC driver file

Set JDBC Connection Options

Configure the data source by setting the JDBC connection options for the data source SQLServerDataSource, database server dbtb04, port number 54317, full path to the JDBC driver file, and Windows® authentication.

opts = setConnectionOptions(opts, ...
    "DataSourceName","SQLServerDataSource", ...
    "Server","dbtb04","PortNumber",54317, ...
    "JDBCDriverLocation","C:\Drivers\sqljdbc4.jar", ...
    "AuthType","Windows")

opts = 
  JDBCConnectionOptions with properties:

                      Vendor: 'Microsoft SQL Server'
              DataSourceName: 'SQLServerDataSource'

                DatabaseName: ''
                      Server: 'dbtb04'
                  PortNumber: 54317
                    AuthType: 'Windows'

          JDBCDriverLocation: 'C:\Drivers\sqljdbc4.jar'

The setConnectionOptions function sets the DataSourceName, Server, PortNumber, AuthType, and JDBCDriverLocation properties in the JDBCConnectionOptions object.

Add JDBC Driver-Specific Connection Options

Add two JDBC driver-specific connection options by using name-value pair arguments. The first option specifies a timeout value for establishing the database connection. The second option disables SSL encryption. opts contains a new section of properties for the additional JDBC connection options.

opts = addConnectionOptions(opts,"loginTimeout",20, ...
    "encrypt",false)

opts = 
  JDBCConnectionOptions with properties:

                      Vendor: 'Microsoft SQL Server'
              DataSourceName: 'SQLServerDataSource'

                DatabaseName: ''
                      Server: 'dbtb04'
                  PortNumber: 54317
                    AuthType: 'Windows'

          JDBCDriverLocation: 'C:\Drivers\sqljdbc4.jar'

  Additional JDBC Connection Options:

                     encrypt: 'false'
                loginTimeout: '20'

To remove any additional connection options, use the rmConnectionOptions function.

Test and Save JDBC Data Source

Test the database connection with a blank user name and password. The testConnection function returns the logical 1, which indicates the database connection is successful.

username = "";
password = "";
status = testConnection(opts,username,password)

status = logical
   1

Save the configured data source.

saveAsJDBCDataSource(opts)

Documentation

Create JDBC Data Source and Set Options Programmatically