databaseConnectionOptions

Create database connection options

Since R2020b

Syntax

opts = databaseConnectionOptions(drivertype,vendor)

opts = databaseConnectionOptions(datasource)

Description

opts = databaseConnectionOptions(drivertype,vendor) creates an SQLConnectionOptions object opts using the specified driver type and database vendor. The SQLConnectionOptions object contains the database connection options.

opts = databaseConnectionOptions(datasource) enables you to edit an existing data source using its name.

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")

Create ODBC Data Source for Other Vendors

Create a data source that connects to other servers on the Windows or macOS platform by using the community version of Databricks^®.

On the Windows platform, use the databaseConnectionOptions function with the vendor input argument set to "other" to create a data source.

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

opts =
 
  SQLConnectionOptions with properties:
 
              DataSourceName: ""
                      Vendor: "Other"
 
                  ODBCDriver: ""  

Configure the data source by setting the ODBC connection options.

opts = setoptions(opts,"DataSourceName","databricks-server","ODBCDriver","Simba Spark ODBC Driver", ...
"Host","community.cloud.databricks.com","Port","443","AuthMech","3","ThriftTransport","2", ...
"ssl","1","httpPath","sql/protocolv1/o/5263663312480005/0119-142946-quslz33e", ...
"SparkServerType","3","ServiceDiscoveryMode","No Service Discovery")

opts = 

  SQLConnectionOptions with properties:
 
              DataSourceName: "databricks-server"
                      Vendor: "Other"
 
                  ODBCDriver: "Simba Spark ODBC Driver"
 
    Additional Connection Options:
 
                    AuthMech: "3"
                        Host: "community.cloud.databricks.com"
                        Port: "443"
        ServiceDiscoveryMode: "No Service Discovery"
             SparkServerType: "3"
             ThriftTransport: "2"
                    httpPath: "sql/protocolv1/o/5263663312480005/0119-142946-quslz33e"
                         ssl: "1"

Alternatively, on the macOS platform, use the databaseConnectionOptions function with the vendor input argument set to "other" to create a data source.

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

opts =
 
  SQLConnectionOptions with properties:
 
              DataSourceName: ""
                      Vendor: "Other"
 
                  ODBCDriver: ""
               DriverManager: "unixODBC"

Configure the data source by setting the ODBC connection options.

opts = setoptions(opts,"DataSourceName","databricks-server","ODBCDriver","/Library/simba/spark/lib/libsparkodbc_sb64-universal.dylib", ...
"Host","community.cloud.databricks.com","Port","443","AuthMech","3","ThriftTransport","2", "ssl","1", ... 
"httpPath","sql/protocolv1/o/5263663312480005/0119-142946-quslz33e","SparkServerType","3","ServiceDiscoveryMode","No Service Discovery")

opts =
 
  SQLConnectionOptions with properties:
 
              DataSourceName: "databricks-server"
                      Vendor: "Other"
 
                  ODBCDriver: "/Library/simba/spark/lib/libsparkodbc_sb64-universal.dylib"
               DriverManager: "unixODBC"
 
    Additional Connection Options:
 
                    AuthMech: "3"
                        Host: "community.cloud.databricks.com"
                        Port: "443"
        ServiceDiscoveryMode: "No Service Discovery"
             SparkServerType: "3"
             ThriftTransport: "2"
                    httpPath: "sql/protocolv1/o/5263663312480005/0119-142946-quslz33e"
                         ssl: "1"

Input Arguments

drivertype — Driver type

"jdbc" | "odbc" | "native"

Driver type, specified as one of these values:

• "jdbc" — JDBC driver

• "odbc" — ODBC driver (since R2024a)

• "native" — Native interface

You can specify the value as either a character vector or string scalar.

vendor — Database vendor

"Microsoft SQL Server" | "MySQL" | "Oracle" | "PostgreSQL" | "Other" | ...

Database vendor, specified as one of these values:

• For JDBC drivers or (since R2024a) ODBC drivers

  • "Microsoft SQL Server" — Microsoft^® SQL Server^® database

  • "MySQL" — MySQL database

  • "Oracle" — Oracle^® database

  • "PostgreSQL" — PostgreSQL database

  • "Other" — Other database

• For native interfaces

  • "MySQL" — MySQL native interface

  • "PostgreSQL" — PostgreSQL native interface

  • "Cassandra" — Apache Cassandra^® database C++ interface

If the drivertype input argument is "jdbc", then the vendor argument must be one of the values for JDBC drivers. If the drivertype input argument is "native", then the vendor argument must be one of the values for native interfaces.

You can specify the value as either a character vector or a string scalar.

datasource — Data source name

character vector | string scalar

Data source name, specified as a character vector or string scalar. Specify the name of an existing data source.

Example: "myDataSource"

Data Types: char | string

Output Arguments

opts — Database connection options

SQLConnectionOptions object

Database connection options, returned as one of the following objects:

• SQLConnectionOptions — JDBC database connection options

• SQLConnectionOptions — ODBC database connection options

• SQLConnectionOptions — MySQL native interface connection options

• SQLConnectionOptions — PostgreSQL native interface connection options

• CassandraConnectionOptions — Apache Cassandra database connection options

Alternative Functionality

App

You can open the JDBC Data Source Configuration, MySQL Data Source Configuration, or PostgreSQL Data Source Configuration dialog boxes using the Database Explorer app. In the Data Source section of the Database Explorer tab, select one of these accordingly:

• Configure Data Source > Configure JDBC data source

• Configure Data Source > Configure native data source > MySQL

• Configure Data Source > Configure native data source > PostgreSQL

Version History

Introduced in R2020b

R2024a: Addition of ODBC as a database connection option

databaseConnectionOptions supports ODBC database connections.