"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:
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 allBasic 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:
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: