"SQL-JDBC" (External Evaluation System)
Listing of Supported Databases »Details
- To configure a database for use with JDBC in ExternalEvaluate, see details for each specific database in the listing of supported databases.
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.
Examples
open allclose allBasic Examples (2)
Scope (5)
Evaluate a query that is returning all tables:
Query a certain table using limit:
Rename columns using AS statement:
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:
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:
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:
Start a transaction block and delete some data:
Check that the row has been deleted during the transaction:
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: