testConnection

Test JDBC or ODBC database connection

Since R2020b

Syntax

status = testConnection(opts,username,password)

[status,message] = testConnection(opts,username,password)

Description

status = testConnection(opts,username,password) tests the JDBC or ODBC database connection, a user name, and a password, where opts is one of the following:

• SQLConnectionOptions object for JDBC connections.

• SQLConnectionOptions object for ODBC connections.

[status,message] = testConnection(opts,username,password) also returns the error message associated with testing the database connection.

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.

Retrieve Message for JDBC Database Connection Test

Create and configure a JDBC data source to a Microsoft® SQL Server® database. Test the database connection to the JDBC data source and retrieve the error message.

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 SQL Server authentication.

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

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: "Server"

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

Test the database connection using an incorrect user name and password. The testConnection function returns the logical 0, which indicates the database connection fails. Retrieve and display the error message for the failed connection.

username = "wronguser";
password = "wrongpassword";
[status,message] = testConnection(opts,username,password)

status = logical
   0

message = 
'JDBC Driver Error: Login failed for user 'wronguser'. ClientConnectionId:dfe57aed-973f-4c5f-838b-d733a24c40f0'

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.

username — User name

character vector | string scalar

User name required to access the database, specified as a character vector or string scalar. If no user name is required, specify an empty value "".

Data Types: char | string

password — Password

character vector | string scalar

Password required to access the database, specified as a character vector or string scalar. If no password is required, specify an empty value "".

Data Types: char | string

Output Arguments

status — Connection status

logical

Connection status, returned as a logical true if the connection test passes or false if the connection test fails.

message — Error message

character vector

Error message, returned as a character vector. If the connection test passes, then the error message is an empty character vector. Otherwise, the error message contains text from the failed database connection.

Version History

Introduced in R2020b

R2024a: Test ODBC database connection

Test connections for ODBC database connections.