setoptions

Set JDBC or ODBC connection options

Syntax

opts = setoptions(opts,Option1,OptionValue1,...,OptionN,OptionValueN)

Description

opts = setoptions(opts,Option1,OptionValue1,...,OptionN,OptionValueN) sets JDBC or ODBC connection options, where opts is one of the following:

• SQLConnectionOptions object for JDBC connections.

• SQLConnectionOptions object for ODBC connections.

Examples

Create JDBC Data Source

Create, configure, test, and save a JDBC data source for a Microsoft® SQL Server® database.

Create an SQL Server data source for a JDBC database connection.

vendor = "Microsoft SQL Server";
opts = databaseConnectionOptions("jdbc",vendor)

opts = 
  SQLConnectionOptions with properties:

              DataSourceName: ""
                      Vendor: "Microsoft SQL Server"

          JDBCDriverLocation: ""
                DatabaseName: ""
                      Server: "localhost"
                  PortNumber: 1433
          AuthenticationType: "Server"

opts is an SQLConnectionOptions object with these properties:

• DataSourceName — Name of the data source

• Vendor — Database vendor name

• JDBCDriverLocation — Full path of the JDBC driver file

• DatabaseName — Name of the database

• Server — Name of the database server

• PortNumber — Port number

• AuthenticationType — Authentication type

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

opts = setoptions(opts, ...
    'DataSourceName',"SQLServerDataSource", ...
    'JDBCDriverLocation',"C:\Drivers\mssql-jdbc-7.0.0.jre8.jar", ...
    'DatabaseName',"toystore_doc",'Server',"dbtb04", ...
    'PortNumber',54317,'AuthenticationType',"Windows")

opts = 
  SQLConnectionOptions with properties:

              DataSourceName: "SQLServerDataSource"
                      Vendor: "Microsoft SQL Server"

          JDBCDriverLocation: "C:\Drivers\mssql-jdbc-7.0.0.jre8.jar"
                DatabaseName: "toystore_doc"
                      Server: "dbtb04"
                  PortNumber: 54317
          AuthenticationType: "Windows"

The setoptions function sets the DataSourceName, JDBCDriverLocation, DatabaseName, Server, PortNumber, and AuthenticationType properties in the SQLConnectionOptions object.

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.

saveAsDataSource(opts)

You can connect to the new data source using the database function or the Database Explorer app.

Edit Existing JDBC Data Source

Edit an existing JDBC data source for a Microsoft® SQL Server® database. Set an additional JDBC driver-specific option, and save the data source.

Retrieve the existing SQL Server data source SQLServerDataSource.

datasource = "SQLServerDataSource";
opts = databaseConnectionOptions(datasource)

opts = 
  SQLConnectionOptions with properties:

              DataSourceName: "SQLServerDataSource"
                      Vendor: "Microsoft SQL Server"

          JDBCDriverLocation: "C:\Drivers\mssql-jdbc-7.0.0.jre8.jar"
                DatabaseName: "toystore_doc"
                      Server: "dbtb04"
                  PortNumber: 54317
          AuthenticationType: "Windows"

opts is an SQLConnectionOptions object with these properties:

• DataSourceName — Name of the data source

• Vendor — Database vendor name

• JDBCDriverLocation — Full path of the JDBC driver file

• DatabaseName — Name of the database

• Server — Name of the database server

• PortNumber — Port number

• AuthenticationType — Authentication type

Add a JDBC driver-specific connection option by using a name-value pair argument. The option specifies a timeout value for establishing the database connection. opts contains a new section of properties for the additional JDBC connection option.

opts = setoptions(opts,'loginTimeout',"20")

opts = 
  SQLConnectionOptions with properties:

              DataSourceName: "SQLServerDataSource"
                      Vendor: "Microsoft SQL Server"

          JDBCDriverLocation: "C:\Drivers\mssql-jdbc-7.0.0.jre8.jar"
                DatabaseName: "toystore_doc"
                      Server: "dbtb04"
                  PortNumber: 54317
          AuthenticationType: "Windows"

	Additional Connection Options:

                loginTimeout: "20"

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 updated data source.

saveAsDataSource(opts)

Create ODBC Data Source

Create a data source that connects to a MySQL^® server on the Windows^® or macOS platform.

On the Windows platform, use the databaseConnectionOptions function to create a data source that connects to a MySQL server.

opts = databaseConnectionOptions('odbc','mysql')

opts =

SQLConnectionOptions with properties:
 
              DataSourceName: ""
                      Vendor: "MySQL"
 
                DatabaseName: ""
                      Server: "localhost"
                  PortNumber: 3306
                  ODBCDriver: "MariaDB ODBC 3.1 Driver"

Configure the data source by setting the ODBC connection options.

opts = opts.setoptions('DataSourceName','mysql_odbc','DatabaseName',"toy_store","Server","dbtb09");
opts.saveAsDataSource();

Alternatively, use the databaseConnectionOptions function on the macOS platform to create a data source that connects to a MySQL server.

opts = databaseConnectionOptions("odbc","MySQL") 

opts =

SQLConnectionOptions with properties:
 
              DataSourceName: "mysql-server-test"
                      Vendor: "MySQL"
 
                DatabaseName: "toy_store"
                      Server: "dbtb09"
                  PortNumber: 3306
                  ODBCDriver: "/Applications/MATLAB_R2024a.app/bin/maci64/libmaodbc.dylib"
               DriverManager: "unixODBC"  

Configure the data source by setting the ODBC connection options.

opts = setoptions(opts,"DataSourceName","mysql-server-test", ...
    "DatabaseName","toy_store","Server","dbtb01")

Input Arguments

opts — Database connection options

SQLConnectionOptions object

Database connection options, specified as one of the following:

• SQLConnectionOptions object for JDBC connections.

• SQLConnectionOptions object for ODBC connections.

Option1,OptionValue1,...,OptionN,OptionValueN — JDBC or ODBC connection options to set

name-value arguments

JDBC or ODBC connection options to set, specified as one or more name-value arguments. Option is a character vector or string scalar that specifies the name of a connection option. OptionValue is the value of the connection option, specified as a character vector, string scalar, logical scalar, or numeric scalar. You can specify any connection option that is a property of the following objects:

• SQLConnectionOptions for JDBC connections.

• SQLConnectionOptions for ODBC connections.

Example: "DataSourceName","myDataSource","Server","localhost","PortNumber",3306 configures a JDBC data source named myDataSource that is located on the local server with the port number 3306. "DataSourceName","mysql-server-test","DatabaseName","toy_store","Server","dbtb01" configures an ODBC data source named mysql-server-test that is located on a server named dbtb01.

Output Arguments

opts — Database connection options

SQLConnectionOptions object

Database connection options, returned as one of the following:

• SQLConnectionOptions object for JDBC connections.

• SQLConnectionOptions object for ODBC connections.

Version History

Introduced in R2020b

R2024a: Set ODBC connection options

Set connection options for ODBC database connections.