DatabaseLink`
DatabaseLink`

OpenSQLConnection

OpenSQLConnection[src]

makes a connection to a named data source.

OpenSQLConnection[JDBC[…]]

makes a connection to a data source described by a JDBC object.

OpenSQLConnection[]

opens a GUI for creating and managing named data sources.

Details and Options

  • To use OpenSQLConnection, you first need to load DatabaseLink using Needs["DatabaseLink`"].
  • The following options can be given:
  • "Name"""name of the connection
    "Description"""textual description of the connection
    "Username" ""username to use for connecting
    "Password" ""password to use for connecting
    "Location"""location of the file that defines the connection
    "Catalog" Automaticlocation of the database catalog
    "Properties" {}key-value pairs, as rules, passed to JDBC driver
    "ReadOnly" Automaticset the connection to be read only
    "RelativePath" Falseindicates whether or not database location is specified relative to configuration (for file-based databases)
    "Timeout" $SQLTimeouttimeout setting for operations, in seconds
    "TransactionIsolationLevel" Automaticset transaction isolation for the connection
    "UseConnectionPool" Automaticopen the connection from a managed pool
    "Version"Noneversion of the configuration file
    "AutoCommit" Trueset the autocommit mode for the connection

Examples

open allclose all

Basic Examples  (1)

If you find that the examples in this section do not work as shown, you may need to install or restore the example database with the "DatabaseLink`DatabaseExamples`" package, as described in Using the Example Databases.

Open a connection to a named data source:

Open a connection to a data source specified using JDBC:

This opens a GUI:

If you find that the examples in this section do not work as shown, you may need to install or restore the example database with the "DatabaseLink`DatabaseExamples`" package, as described in Using the Example Databases.

Options  (10)

"Username"  (1)

Specify username:

"Password"  (1)

Specify username and password:

Use $Prompt to be prompted for the password:

"Catalog"  (1)

Implementation of catalogs varies significantly among RDBMS types and their drivers. In MySQL, for example, "Catalog" may be used to specify the database. Connect to the database sandbox in the MySQL installation on localhost:

Equivalently, specify the database in the connection URL:

"Properties"  (1)

Additional settings may be passed to the JDBC driver by means of the "Properties" option. For example, Derby requires the property "create" be set to "true" for databases that do not already exist:

With the property set, the connection opens normally:

Equivalently, set attributes in a connection URL:

"ReadOnly"  (1)

A read-only connection will not permit modification or creation of tables or records:

"RelativePath"  (1)

For file-based databases, supplied paths to resources are interpreted as relative to the current working directory if they are not absolute. Force a path interpretation relative to a given location using the "RelativePath" option:

"Timeout"  (1)

A connection attempt will time out in $SQLTimeout seconds, which has a driver-dependent default. Override it with the "Timeout" option, supplying an integer number of seconds:

"TransactionIsolationLevel"  (1)

Transactions on the opened connection are isolated using a driver-dependent default. Override it with the "TransactionIsolationLevel" option, supplying one of "ReadUncommitted", "ReadCommitted", "RepeatableRead", or "Serializable":

More information about transactions and isolation levels is available in the Transactions tutorial.

"UseConnectionPool"  (1)

Set the "UseConnectionPool" option to open a connection in a managed pool. The pool will be created if it does not already exist:

This is the pool object associated with the connection:

Close the pool and any associated connections:

More information about connection pools is available in the Connection Pools tutorial.

"AutoCommit"  (1)

Set the "AutoCommit" option to open a connection. Connecting database with default AutoCommit set to True.

Creating table and inserting data by enabling AutoCommit mode. Insertion operation takes more time when AutoCommit mode is enabled.

Connecting database by disabling AutoCommit mode.

Insertion operation takes less time while performing bulk insert when AutoCommit is disabled.

Insertion is completed but not committed.

Other database connections are not able to see the changes before commit.

Committing the databases changes.

Other database connections are able to see the changes after commit.

Possible Issues  (2)

Not all JDBC drivers implement timeout-related methods. The "Timeout" option has no effect with certain drivers:

Not all JDBC drivers implement all available settings for the "TransactionIsolationLevel" option. Here an opened connection reverts to the default isolation level: