"SQL" (External Evaluation System)

Listing of Supported Databases »

Details

ExternalEvaluate Usage

  • ExternalEvaluate["SQL",code] executes a string of SQL in a database connection and returns the result as a Wolfram Language expression.
  • ExternalEvaluate["SQL"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".
  • ExternalEvaluate[DatabaseReference[ref],code] is equivalent to ExternalEvaluate[{"SQL","Target"DatabaseReference[ref]},code].
  • The possible settings for target in ExternalEvaluate[{"SQL","Target"target},] 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.
  • A full list of supported data types is available for each supported database: TemplateBox[{"SQLite", {FrontEnd`EvaluationNotebook[], paclet:ref/databaseconnection/SQLite}, }, HyperlinkDefault], TemplateBox[{"PostgreSQL", {FrontEnd`EvaluationNotebook[], paclet:ref/databaseconnection/PostgreSQL}, }, HyperlinkDefault], TemplateBox[{"MySQL", {FrontEnd`EvaluationNotebook[], paclet:ref/databaseconnection/MySQL}, }, HyperlinkDefault], TemplateBox[{"MicrosoftSQL", {FrontEnd`EvaluationNotebook[], paclet:ref/databaseconnection/MicrosoftSQL}, }, HyperlinkDefault] and TemplateBox[{"Oracle", {FrontEnd`EvaluationNotebook[], paclet:ref/databaseconnection/Oracle}, }, HyperlinkDefault].

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 from the drop-down menu to get an SQL code cell:

SELECT * FROM employees LIMIT 10

Scope  (4)

Start a session:

Evaluate a query that is returning all tables:

Query a certain table using limit:

Rename columns using an AS statement:

Close the session:

By default ExternalEvaluate returns data using Dataset:

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

While using "Columns", numerical data is returned as a packed array when possible:

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 backend that is currently in use. Expressions like Integer, ByteArray, String, DateObject and TimeObject can be used if the backend supports them:

Applications  (2)

DatabaseReference can also represent an in-memory SQLite database:

All operations done during an in-memory session are lost at the end of an ExternalEvaluate call:

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:

Possible Issues  (1)

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: