WOLFRAM

Wolfram Language & System Documentation Center
Wolfram Language Home Page »
DatabaseLink`
DatabaseLink`

OpenSQLConnection

Updated in 14

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)Summary of the most common use cases

In[1]:=1
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-djvc6r

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:

In[2]:=2
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-p6c
Out[2]=2
In[3]:=3
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-ots

Open a connection to a data source specified using JDBC:

In[4]:=4
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-wz7
Out[4]=4
In[5]:=5
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-gxm

This opens a GUI:

In[6]:=6
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-p3f
Out[6]=6
In[7]:=7
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-q8d

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)Common values & functionality for each option

"Username"  (1)

In[1]:=1
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-bwnnzn

Specify username:

In[2]:=2
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-bra
Out[2]=2
In[3]:=3
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-s54

"Password"  (1)

In[1]:=1
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-c0za6w

Specify username and password:

In[2]:=2
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-c9d
Out[2]=2
In[3]:=3
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-hnv

Use $Prompt to be prompted for the password:

In[4]:=4
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-vk4
Out[4]=4
In[5]:=5
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-fqt

"Catalog"  (1)

In[1]:=1
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-4e2g0c

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:

In[2]:=2
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-lwovya
In[3]:=3
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-t55ubo
In[4]:=4
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-6n7mcs

Equivalently, specify the database in the connection URL:

In[5]:=5
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-onrlqq
In[6]:=6
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-sosjp6
In[7]:=7
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-44m08n

"Properties"  (1)

In[1]:=1
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-ifuk8b

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:

In[2]:=2
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-xqlska
Out[2]=2

With the property set, the connection opens normally:

In[3]:=3
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-s1l9x8
Out[3]=3
In[4]:=4
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-4qqvtq

Equivalently, set attributes in a connection URL:

In[5]:=5
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-q38ywx
Out[5]=5
In[6]:=6
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-lzgtcn

"ReadOnly"  (1)

In[1]:=1
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-v06ee4

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

In[2]:=2
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-8ysuya
Out[2]=2
In[3]:=3
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-pjqagx
Out[3]=3
In[4]:=4
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-u7elwe

"RelativePath"  (1)

In[1]:=1
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-eaoaqq

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:

In[2]:=2
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-z8ln5x
Out[2]=2
In[3]:=3
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-uybm13
Out[3]=3
In[4]:=4
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-zfjrvd

"Timeout"  (1)

In[1]:=1
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-i492p2

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:

In[2]:=2
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-dpfd0t
Out[2]=2

"TransactionIsolationLevel"  (1)

In[1]:=1
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-eanbod

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

In[2]:=2
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-zft4cr
Out[2]=2
In[3]:=3
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-zeuou4
Out[3]=3
In[4]:=4
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-dkv13m
Out[4]=4
In[5]:=5
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-zotyz6

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

"UseConnectionPool"  (1)

In[1]:=1
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-mddsr0

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

In[2]:=2
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-x4za3
Out[2]=2

This is the pool object associated with the connection:

In[3]:=3
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-2kjp9r
Out[3]=3

Close the pool and any associated connections:

In[4]:=4
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-zefqhs

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

"AutoCommit"  (1)

In[1]:=1
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-f7p09z

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

In[2]:=2
✖
https://wolfram.com/xid/0ciga77kvwby4stjm
Out[2]=2

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

In[3]:=3
✖
https://wolfram.com/xid/0ciga77kvwby4stjm
Out[3]=3

Connecting database by disabling AutoCommit mode.

In[4]:=4
✖
https://wolfram.com/xid/0ciga77kvwby4stjm
Out[4]=4

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

In[5]:=5
✖
https://wolfram.com/xid/0ciga77kvwby4stjm
Out[5]=5

Insertion is completed but not committed.

In[6]:=6
✖
https://wolfram.com/xid/0ciga77kvwby4stjm
Out[6]=6

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

In[7]:=7
✖
https://wolfram.com/xid/0ciga77kvwby4stjm
Out[7]=7

Committing the databases changes.

In[8]:=8
✖
https://wolfram.com/xid/0ciga77kvwby4stjm
Out[8]=8

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

In[9]:=9
✖
https://wolfram.com/xid/0ciga77kvwby4stjm
Out[9]=9
In[10]:=10
✖
https://wolfram.com/xid/0ciga77kvwby4stjm

Possible Issues  (2)Common pitfalls and unexpected behavior

In[1]:=1
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-bcdguu

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

In[2]:=2
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-j33ms7
Out[2]=2
In[1]:=1
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-pk5tjm

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

In[2]:=2
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-2laogh
Out[2]=2
In[3]:=3
✖
https://wolfram.com/xid/0ciga77kvwby4stjm-9qe1xt