OpenSQLConnection
OpenSQLConnection[src]
makes a connection to a named data source.
makes a connection to a data source described by a JDBC object.
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" Automatic location of the database catalog "Properties" {} key-value pairs, as rules, passed to JDBC driver "ReadOnly" Automatic set the connection to be read only "RelativePath" False indicates whether or not database location is specified relative to configuration (for file-based databases) "Timeout" $SQLTimeout timeout setting for operations, in seconds "TransactionIsolationLevel" Automatic set transaction isolation for the connection "UseConnectionPool" Automatic open the connection from a managed pool "Version" None version of the configuration file "AutoCommit" True set the autocommit mode for the connection
Examples
open all close allBasic Examples (1)
Needs["DatabaseLink`"]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:
conn = OpenSQLConnection["demo"]CloseSQLConnection[conn];Open a connection to a data source specified using JDBC:
conn = OpenSQLConnection[ JDBC["HSQL(Standalone)", ToFileName[{$UserBaseDirectory, "DatabaseResources", "Examples"}, "demo"] ],
"Name" -> "manualA", "Username" -> "sa"]CloseSQLConnection[conn];conn = OpenSQLConnection[]CloseSQLConnection[conn];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)
"Password" (1)
Needs["DatabaseLink`"]Specify username and password:
conn = OpenSQLConnection["demo", "Username" -> "sa", "Password" -> ""]CloseSQLConnection[conn];Use $Prompt to be prompted for the password:
conn = OpenSQLConnection["demo", "Username" -> "sa", "Password" -> "$Prompt"]CloseSQLConnection[conn];"Catalog" (1)
Needs["DatabaseLink`"]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:
conn = OpenSQLConnection[JDBC["MySQL(Connector/J)", "localhost"], "Username" -> "guest", "Password" -> "guest", "Catalog" -> "sandbox"]DatabaseLink`SQLConnection[DatabaseLink`JDBC["MySQL(Connector/J)", "localhost"],
JLink`Objects`vm1`JavaObject3410209317847041, 2, "Catalog" -> "sandbox", "Description" -> None,
"Location" -> None, "Name" -> None, "Password" -> "Marflow17", "Properties" -> {},
"ReadOnly" -> Automatic, "RelativePath" -> False, "TransactionIsolationLevel" -> Automatic,
"UseConnectionPool" -> Automatic, "Username" -> "root", "Version" -> None]CloseSQLConnection[conn];Equivalently, specify the database in the connection URL:
conn = OpenSQLConnection[JDBC["MySQL(Connector/J)", "localhost/sandbox"], "Username" -> "guest", "Password" -> "guest"]DatabaseLink`SQLConnection[DatabaseLink`JDBC["MySQL(Connector/J)", "localhost/sandbox"],
JLink`Objects`vm1`JavaObject7161744753426433, 3, "Catalog" -> Automatic, "Description" -> None,
"Location" -> None, "Name" -> None, "Password" -> "Marflow17", "Properties" -> {},
"ReadOnly" -> Automatic, "RelativePath" -> False, "TransactionIsolationLevel" -> Automatic,
"UseConnectionPool" -> Automatic, "Username" -> "root", "Version" -> None]CloseSQLConnection[conn];"Properties" (1)
Needs["DatabaseLink`"]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:
conn = OpenSQLConnection[JDBC["Derby(Embedded)", FileNameJoin[{$TemporaryDirectory, "posts1"}]]]
With the property set, the connection opens normally:
conn = OpenSQLConnection[JDBC["Derby(Embedded)", FileNameJoin[{$TemporaryDirectory, "posts1"}]], "Properties" -> {"create" -> "true"}]CloseSQLConnection[conn];
DeleteDirectory[FileNameJoin[{$TemporaryDirectory, "posts1"}], DeleteContents -> True]Equivalently, set attributes in a connection URL:
conn = OpenSQLConnection[JDBC["Derby(Embedded)", FileNameJoin[{$TemporaryDirectory, "posts2"}] <> ";create=true;"]]CloseSQLConnection[conn];
DeleteDirectory[FileNameJoin[{$TemporaryDirectory, "posts2"}], DeleteContents -> True]"ReadOnly" (1)
Needs["DatabaseLink`"]A read-only connection will not permit modification or creation of tables or records:
conn = OpenSQLConnection[JDBC["HSQL(Memory)", "scratchpad"], "ReadOnly" -> True]SQLCreateTable[conn,
SQLTable["A"], {SQLColumn["A1", "DataTypeName" -> "VARCHAR(128)"]}
]
CloseSQLConnection[conn];"RelativePath" (1)
Needs["DatabaseLink`"]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:
conn = OpenSQLConnection[JDBC["SQLite", "interactions.db"], "Location" -> $TemporaryDirectory, "RelativePath" -> True]FileNames["interactions.db", $TemporaryDirectory]CloseSQLConnection[conn];
DeleteFile[FileNameJoin[{$TemporaryDirectory, "interactions.db"}]]"Timeout" (1)
Needs["DatabaseLink`"]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:
AbsoluteTiming@OpenSQLConnection[JDBC["PostgreSQL", "1.2.3.4/nonexistent"], "Timeout" -> 5]
"TransactionIsolationLevel" (1)
Needs["DatabaseLink`"]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":
c1 = OpenSQLConnection["publisher", "TransactionIsolationLevel" -> Automatic]c2 = OpenSQLConnection["publisher", "TransactionIsolationLevel" -> "Serializable"]c3 = OpenSQLConnection["publisher", "TransactionIsolationLevel" -> "RepeatableRead"]CloseSQLConnection /@ {c1, c2, c3};More information about transactions and isolation levels is available in the Transactions tutorial.
"UseConnectionPool" (1)
Needs["DatabaseLink`"]Set the "UseConnectionPool" option to open a connection in a managed pool. The pool will be created if it does not already exist:
conn = OpenSQLConnection["demo", "UseConnectionPool" -> True]This is the pool object associated with the connection:
SQLConnectionPools[conn]Close the pool and any associated connections:
SQLConnectionPoolClose[SQLConnectionPools[conn]]More information about connection pools is available in the Connection Pools tutorial.
"AutoCommit" (1)
Needs["DatabaseLink`"]Set the "AutoCommit" option to open a connection. Connecting database with default AutoCommit set to True.
conn = OpenSQLConnection[JDBC["MySQL(Connector/J)", "testqa64lx11"], "Username" -> "testuser", "Password" -> "password", "Catalog" -> "testdb"]Creating table and inserting data by enabling AutoCommit mode. Insertion operation takes more time when AutoCommit mode is enabled.
SQLExecute[conn, "Drop TABLE if exists test_table"];
SQLExecute[conn, "CREATE TABLE test_table (id int, value int)"];
data = Table[RandomInteger[2, 2], 2000];
SQLInsert[conn, "test_table", {"id", "value"}, data]//AbsoluteTiming//FirstConnecting database by disabling AutoCommit mode.
conn2 = OpenSQLConnection[JDBC["MySQL(Connector/J)", "testqa64lx11"], "Username" -> "testuser", "Password" -> "password", "Catalog" -> "testdb", "AutoCommit" -> False]Insertion operation takes less time while performing bulk insert when AutoCommit is disabled.
SQLInsert[conn2, "test_table", {"id", "value"}, data]//AbsoluteTiming//FirstInsertion is completed but not committed.
SQLExecute[conn2, "Select count(*) from test_table"]Other database connections are not able to see the changes before commit.
SQLExecute[conn, "Select count(*) from test_table"]Committing the databases changes.
SQLExecute[conn2, "commit"]Other database connections are able to see the changes after commit.
SQLExecute[conn, "Select count(*) from test_table"]CloseSQLConnection /@ {conn, conn2};Possible Issues (2)
Needs["DatabaseLink`"]Not all JDBC drivers implement timeout-related methods. The "Timeout" option has no effect with certain drivers:
AbsoluteTiming@OpenSQLConnection[JDBC["MySQL(Connector/J)", "1.2.3.4/nonexistent"], "Timeout" -> 1]
Needs["DatabaseLink`"]Not all JDBC drivers implement all available settings for the "TransactionIsolationLevel" option. Here an opened connection reverts to the default isolation level:
conn = OpenSQLConnection["publisher", "TransactionIsolationLevel" -> "ReadUncommitted"]CloseSQLConnection[conn];