WOLFRAM

Wolfram Language & System Documentation Center

"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 all close all

Basic Examples  (2)

Specify a reference to the demo database:

Wolfram Language code: db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]]

Evaluate a query in SQL and return the result:

Wolfram Language code: ExternalEvaluate[{"SQL-JDBC", "Evaluator" -> db}, "SELECT * FROM offices"]

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

Wolfram Language code: db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]]
Wolfram Language code: RegisterExternalEvaluator["SQL-JDBC", db]

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:

Wolfram Language code: db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]]
Wolfram Language code: session = StartExternalSession[{"SQL-JDBC", "Evaluator" -> db}]

Evaluate a query that is returning all tables:

Wolfram Language code: ExternalEvaluate[session, "SELECT name FROM sqlite_master WHERE name NOT LIKE 'sqlite_%'"]

Query a certain table using limit:

Wolfram Language code: ExternalEvaluate[session, "SELECT * FROM offices limit 4"]

Rename columns using AS statement:

Wolfram Language code: ExternalEvaluate[session, "SELECT officeCode AS id, addressLine1 || ' - ' || addressLine2 AS address FROM offices"]

Close the session:

Wolfram Language code: DeleteObject[session]

ExternalEvaluate also accepts DatabaseLink`SQLConnection:

Wolfram Language code: Needs["DatabaseLink`"];
Wolfram Language code: db = SQLConnection[JDBC["SQLite", FindFile["ExampleData/ecommerce-database.sqlite"]]];
Wolfram Language code: ExternalEvaluate[db, "SELECT * FROM offices"]

Any JDBC driver supported by DatabaseLink` can be used:

Wolfram Language code: ExternalEvaluate[SQLConnection[JDBC["PostgreSQL", "localhost/template1"]], "SELECT * FROM generate_series(3, 6)"]

By default, ExternalEvaluate returns data using Dataset:

Wolfram Language code: db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]]
Wolfram Language code: ExternalEvaluate[{"SQL-JDBC", "Evaluator" -> db}, "SELECT officeCode AS pk, phone FROM offices"]

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

Wolfram Language code: ExternalEvaluate[{"SQL-JDBC", "Evaluator" -> db, "ReturnType" -> "Rows"}, "SELECT officeCode AS pk, phone FROM offices"]
Wolfram Language code: ExternalEvaluate[{"SQL-JDBC", "Evaluator" -> db, "ReturnType" -> "NamedRows"}, "SELECT officeCode AS pk, phone FROM offices"]
Wolfram Language code: ExternalEvaluate[{"SQL-JDBC", "Evaluator" -> db, "ReturnType" -> "Columns"}, "SELECT officeCode AS pk, phone FROM offices"]
Wolfram Language code: ExternalEvaluate[{"SQL-JDBC", "Evaluator" -> db, "ReturnType" -> "NamedColumns"}, "SELECT officeCode AS pk, phone FROM offices"]

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

Wolfram Language code: db = DatabaseReference[URL["postgres://localhost/template1"]]

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

Wolfram Language code: ExternalEvaluate[{"SQL-JDBC", "Evaluator" -> db}, "SELECT NOW() AS now, NOW()::date AS date, interval '1 day' AS interval, '1.23'::decimal AS decimal"]

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

Wolfram Language code: db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]];

Set two variables:

Wolfram Language code: x = 3; y = 2;

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

Wolfram Language code: ExternalEvaluate[{"SQL-JDBC", "Evaluator" -> db}, "SELECT <* x^2 + y^2 *> AS value"]

Manually provide arguments to the template by using an Association:

Wolfram Language code: ExternalEvaluate[{"SQL-JDBC", "Evaluator" -> db}, <| "Command" -> "SELECT city, addressLine1, country FROM offices WHERE country = `1`", "TemplateArguments" -> {"USA"} |>]

Use named arguments:

Wolfram Language code: ExternalEvaluate[{"SQL-JDBC", "Evaluator" -> db}, <| "Command" -> "SELECT officeCode, city, addressLine1 FROM offices WHERE officeCode < `n`", "TemplateArguments" -> <|"n" -> 4|> |>]

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:

Wolfram Language code: ExternalEvaluate[{"SQL-JDBC", "Evaluator" -> DatabaseReference["postgresql://localhost/template1"]}, <| "Command" -> "SELECT `` AS number, NOW() AS now", "TemplateArguments" -> {1} |>]

Applications  (2)

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

Wolfram Language code: ExternalEvaluate[ {"SQL-JDBC", DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]]}, { "CREATE TEMPORARY TABLE t(x INTEGER PRIMARY KEY ASC, y, z);", "INSERT INTO t(x, y, z) VALUES (1, 2, 3)" }]

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

Wolfram Language code: ExternalEvaluate[ {"SQL-JDBC", DatabaseReference["postgresql://localhost/template1"]}, { "CREATE TEMPORARY TABLE test_data( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL )", "INSERT INTO test_data(name) VALUES ('john') RETURNING(\"id\") as pk", "SELECT id, name FROM test_data" }]

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

Wolfram Language code: session = StartExternalSession[{"SQL-JDBC", DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]]}]

Insert some data in it:

Wolfram Language code: ExternalEvaluate[session, { "CREATE TEMPORARY TABLE t(id SERIAL PRIMARY KEY, y INTEGER, z INTEGER)", "INSERT INTO t(id, y, z) VALUES (1, 2, 3)", "INSERT INTO t(id, y, z) VALUES (3, 4, 6)", "INSERT INTO t(id, y, z) VALUES (4, 5, 6)" }];
Wolfram Language code: ExternalEvaluate[session, "SELECT * from t"]

Start a transaction block and delete some data:

Wolfram Language code: ExternalEvaluate[session, "BEGIN"];
Wolfram Language code: ExternalEvaluate[session, "DELETE FROM t WHERE id=4"];

Check that the row has been deleted during the transaction:

Wolfram Language code: ExternalEvaluate[session, "SELECT * from t"]

Perform the rollback and run the query again:

Wolfram Language code: ExternalEvaluate[session, "ROLLBACK"];
Wolfram Language code: ExternalEvaluate[session, "SELECT * from t"]

Close the connection:

Wolfram Language code: DeleteObject[session]

Possible Issues  (2)

Usually a database returns dates using the server time zone:

Wolfram Language code: db = DatabaseReference[URL["postgres://localhost/template1"]];
Wolfram Language code: ExternalEvaluate[{"SQL-JDBC", "Evaluator" -> db, "ReturnType" -> "Rows"}, "SELECT NOW()"]

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

Wolfram Language code: ExternalEvaluate[{"SQL-JDBC", "Evaluator" -> db, "ReturnType" -> "Rows"}, "SELECT NOW() at time zone 'UTC'"]

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

Wolfram Language code: ExternalEvaluate[<|"Evaluator" -> db, "System" -> "SQL-JDBC", "SessionProlog" -> "SET TIME ZONE 'UTC'", "ReturnType" -> "Rows"|>, "SELECT NOW()"]

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

Wolfram Language code: Needs["DatabaseLink`"];
Wolfram Language code: conn = OpenSQLConnection[JDBC["SQLite", FindFile["ExampleData/ecommerce-database.sqlite"]]]
Wolfram Language code: SQLConnections[]

StartExternalSession will create a JDBC connection:

Wolfram Language code: session = StartExternalSession[conn]
Wolfram Language code: SQLConnections[]

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

Wolfram Language code: DeleteObject[session];
Wolfram Language code: SQLConnections[]

Close all pending connections:

Wolfram Language code: DatabaseLink`CloseSQLConnection[DatabaseLink`SQLConnections[]]