sql:connect

Returns an external object representing a connection to a SQL database. This object is used as the first argument of other functions such as sql:query, sql:insert, etc.

connect($options as map(*)) ➔ javatype:java.sql.Connection

Arguments

 

$options

map(*)

Database connection parameters

Result

javatype:java.sql.Connection

Namespace

http://saxon.sf.net/sql

Notes on the Saxon implementation

Introduced in Saxon 9.9. Designed to supersede the extension instruction sql:connect. The saxon-sql-10.#.jar file, distributed alongside the main JAR file, must be added to the classpath when these SQL extension functions are used. Changed in 10.5 to allow the JDBC driver name to be omitted, and to allow a DataSource name to be provided.

Details

Returns an external object representing a connection to a SQL database. This object is used as the first argument of other functions such as sql:query, sql:insert, etc.

The argument is a map, which follows the option parameter conventions. The defined options are:

Keyword Type Value
driver xs:string The Java class name of the JDBC driver to be used, for example sun.jdbc.odbc.JdbcOdbcDriver. No longer needed (and ignored if specified) from Saxon 10.5.
dataSource xs:string The name of a registered DataSource known to the Java naming service. If supplied, the database URL is ignored. NOTE: since the use of a DataSource typically depends on an execution framework such as an application server, Saxonica has performed only very limited testing of this feature, and user feedback about its functioning in different application environments is welcomed.
database xs:string The name (URL) of the database: naming conventions depend on the driver in use. An example for the Derby database might be jdbc:derby:jar:webdb. For MySQL, it might be jdbc:mysql://localhost:3306/test?user=root&password=secret
user xs:string Username to be used for authentication. Not needed if included in the database URL.
password xs:string Password to be used for authentication. Not needed if included in the database URL.
autoCommit xs:boolean Sets or unsets the auto-commit option on the connection that is established

For example:

<xsl:variable name="connection" select="sql:connect(map{ 'database':'jdbc:mysql://localhost/saxontest', 'driver':'com.mysql.jdbc.Driver', 'user':'dbadmin', 'password':$password, 'autoCommit':true()})"/>

It will often be appropriate to bind the result of the call to a global variable.

A dynamic error is thrown in the event of any connection failure. Improved diagnostics on the reason for failure are output if the configuration option Feature.TIMING is set (-t on the command line).

The connection object acts as a map and it is possible to call methods as dynamic function calls using the entries in this map. For example $connection?isClosed() will return a boolean indicating whether the connection has been closed.

For actions that have side-effects, it is recommended to invoke them using the saxon:do extension instruction: for example to close the connection use the instruction:

<saxon:do action="$connection?close()"/>

JDBC connections are not thread-safe. It is therefore advisable when using JDBC connections to suppress Saxon multi-threading by setting the configuration option Feature.ALLOW_MULTI_THREADING to false. A warning is issued if this is not done.

Note that the Saxon extension functions make no attempt to validate or verify the SQL statements being passed through the interface. In particular, there is no attempt to prevent SQL injection attacks: this is entirely the application's responsibility.