"SQL-JDBC" (External Evaluation System)

Listing of Supported Databases »

Details

ExternalEvaluate Usage

  • ExternalEvaluate["SQL-JDBC",code] executes a string of SQL in a database connection and returns the result as a Wolfram Language expression.
  • ExternalEvaluate["SQL-JDBC"form,code] executes the SQL string and returns the result in the specified form. Possible specifications for form are "Dataset", "Rows", "NamedRows", "Columns" and "NamedColumns".
  • The possible settings for Evaluator in ExternalEvaluate[{"SQL-JDBC","Evaluator"Evaluator},…] include:
  • "path"or File["path"]path to an SQLite database
    URL["url"] a connection specified in the form "backend://user:password@host:port/name"
    DatabaseReference[…]an SQL database connection
    SQLConnection[…]an SQL-JDBC database connection

Data Types

  • SQL data types are mapped to appropriate Wolfram Language expressions.
  • Dates and times are typically converted to DateObject and TimeObject.
  • Binary data is converted to ByteArray.

Usage Notes

  • String templates (<*…*>) can be used to evaluate and insert Wolfram Language expressions into the SQL code string.

Examples

open allclose all

Basic Examples  (2)

Specify a reference to the demo database:

Evaluate a query in SQL and return the result:

To use SQL in an external language cell, you need to register a default database:

Type > and select SQL-JDBC from the drop-down menu to get an SQL-JDBC code cell:

SELECT * FROM employees LIMIT 10

Scope  (5)

Start a session:

Evaluate a query that is returning all tables:

Query a certain table using limit:

Rename columns using AS statement:

Close the session:

ExternalEvaluate also accepts DatabaseLink`SQLConnection:

Any JDBC driver supported by DatabaseLink` can be used:

By default, ExternalEvaluate returns data using Dataset:

"ReturnType" can be used to return data in a different form:

Start a session to a local PostgreSQL database (to evaluate this input, you would need to have an appropriate PostgreSQL database instance running):

When supported, ExternalEvaluate will return Wolfram Language expressions instead of strings:

String templates can be used to insert Wolfram Language expressions into SQL code:

Set two variables:

The expression x^2+y^2 is evaluated in the Wolfram Language, and the result is converted and inserted into the SQL code string:

Manually provide arguments to the template by using an Association:

Use named arguments:

Parameters are automatically normalized according to the database back end that is currently in use. Expressions like Integer, ByteArray, String, DateObject and TimeObject can be used if the back end supports them:

Applications  (2)

Operations that are doing side effects on the database typically return Null:

For some back ends, it is possible to insert data and specify a return value:

It is possible to keep a connection open in order to perform a rollback later. Start a new database connection:

Insert some data in it:

Start a transaction block and delete some data:

Check that the row has been deleted during the transaction:

Perform the rollback and run the query again:

Close the connection:

Possible Issues  (2)

Usually a database returns dates using the server time zone:

Change the query to return a column at a particular time zone:

Or "SessionEpilog" can be used to set a default time zone:

ExternalEvaluate will always maintain a new connection internally, even if an already opened connection is provided:

StartExternalSession will create a JDBC connection:

After deleting the ExternalSessionObject, the connection will be closed automatically:

Close all pending connections: