SQLExecute
SQLExecute[conn,command]
executes a command in an SQL connection.
SQLExecute[conn,command,args]
passes arguments to the command.
SQLExecute[SQLSelect[conn,...]]
manages the opening and closing of conn.
Details and Options
- To use SQLExecute, you first need to load DatabaseLink using Needs["DatabaseLink`"].
- Use this function with caution, as you can lose data permanently!
- Options support and behavior varies with driver, driver version, and RDBMS type.
- Returns an integer specifying the number of rows affected by the query. Returns $Failed if an error is encountered when dropping a table.
- The following options can be given:
-
"ColumnSymbols" None symbols to be associated with results "EscapeProcessing" True translate escaped JDBC function syntax "FetchSize" Automatic JDBC driver hint for filling result sets "GetAsStrings" False return all results as strings "GetGeneratedKeys" False return keys associated with updated records "MaxFieldSize" Automatic byte limit for variable-length column types "MaxRows" Automatic the maximum number of rows to return "ShowColumnHeadings" False whether to return headings with the results "Timeout" $SQLTimeout the timeout for the query "BatchSize" 1000 queries with parameters will be processed incrementally in batches of this size "JavaBatching" True perform parameter batching in Java layer instead of Wolfram Language layer
Examples
open allclose allBasic Examples (1)Summary of the most common use cases

https://wolfram.com/xid/0duo3g7o0cctv-nwie1d
If you find that the examples in this section do not work as shown, you may need to install or restore the example database with the DatabaseLink`DatabaseExamples` package, as described in Using the Example Databases.

https://wolfram.com/xid/0duo3g7o0cctv-m20

https://wolfram.com/xid/0duo3g7o0cctv-dqz


https://wolfram.com/xid/0duo3g7o0cctv-gra


https://wolfram.com/xid/0duo3g7o0cctv-u11


https://wolfram.com/xid/0duo3g7o0cctv-eux


https://wolfram.com/xid/0duo3g7o0cctv-qux


https://wolfram.com/xid/0duo3g7o0cctv-qsm


https://wolfram.com/xid/0duo3g7o0cctv-i4k


https://wolfram.com/xid/0duo3g7o0cctv-x10


https://wolfram.com/xid/0duo3g7o0cctv-s0i


https://wolfram.com/xid/0duo3g7o0cctv-jrs
Scope (5)Survey of the scope of standard use cases
Selecting Data (1)

https://wolfram.com/xid/0duo3g7o0cctv-d07uap

https://wolfram.com/xid/0duo3g7o0cctv-6cvenh
Select data matching a condition:

https://wolfram.com/xid/0duo3g7o0cctv-rf3

Select the same data using a prepared statement:

https://wolfram.com/xid/0duo3g7o0cctv-mww

Specify a column in a prepared statement:

https://wolfram.com/xid/0duo3g7o0cctv-b1c

Give a sequence of arguments in a prepared statement:

https://wolfram.com/xid/0duo3g7o0cctv-rxa

Apply a database function to the selected data:

https://wolfram.com/xid/0duo3g7o0cctv-rdw


https://wolfram.com/xid/0duo3g7o0cctv-flc


https://wolfram.com/xid/0duo3g7o0cctv-bqz


https://wolfram.com/xid/0duo3g7o0cctv-fbw


https://wolfram.com/xid/0duo3g7o0cctv-x1d


https://wolfram.com/xid/0duo3g7o0cctv-fv4


https://wolfram.com/xid/0duo3g7o0cctv-s8f


https://wolfram.com/xid/0duo3g7o0cctv-y0n


https://wolfram.com/xid/0duo3g7o0cctv-jqs

Join data from multiple tables:

https://wolfram.com/xid/0duo3g7o0cctv-q0v


https://wolfram.com/xid/0duo3g7o0cctv-rzt
Creating Tables (1)

https://wolfram.com/xid/0duo3g7o0cctv-bt9l15

https://wolfram.com/xid/0duo3g7o0cctv-2xi5pj

https://wolfram.com/xid/0duo3g7o0cctv-d90


https://wolfram.com/xid/0duo3g7o0cctv-ezn


https://wolfram.com/xid/0duo3g7o0cctv-cc5

https://wolfram.com/xid/0duo3g7o0cctv-q4b

https://wolfram.com/xid/0duo3g7o0cctv-seu
Updating Data (1)

https://wolfram.com/xid/0duo3g7o0cctv-rkxlz

https://wolfram.com/xid/0duo3g7o0cctv-txa0tw

https://wolfram.com/xid/0duo3g7o0cctv-rb

https://wolfram.com/xid/0duo3g7o0cctv-tsy

https://wolfram.com/xid/0duo3g7o0cctv-q1c
Update data matching a condition:

https://wolfram.com/xid/0duo3g7o0cctv-emm


https://wolfram.com/xid/0duo3g7o0cctv-whw


https://wolfram.com/xid/0duo3g7o0cctv-glk


https://wolfram.com/xid/0duo3g7o0cctv-e0a


https://wolfram.com/xid/0duo3g7o0cctv-e42

https://wolfram.com/xid/0duo3g7o0cctv-ltw
Deleting Data (1)

https://wolfram.com/xid/0duo3g7o0cctv-geir5x

https://wolfram.com/xid/0duo3g7o0cctv-0n8xom

https://wolfram.com/xid/0duo3g7o0cctv-kgs

https://wolfram.com/xid/0duo3g7o0cctv-gz3

https://wolfram.com/xid/0duo3g7o0cctv-lmz

https://wolfram.com/xid/0duo3g7o0cctv-fo1
Delete data matching a condition:

https://wolfram.com/xid/0duo3g7o0cctv-ff7


https://wolfram.com/xid/0duo3g7o0cctv-wnp


https://wolfram.com/xid/0duo3g7o0cctv-ldr

https://wolfram.com/xid/0duo3g7o0cctv-u2s
Working with Dates and Times (1)

https://wolfram.com/xid/0duo3g7o0cctv-ibiu7g
Date and time data may be given in a query using DateObject, TimeObject or SQLDateTime. Create a table with DATE, TIME, and DATETIME column types:

https://wolfram.com/xid/0duo3g7o0cctv-nadd78

https://wolfram.com/xid/0duo3g7o0cctv-uo695t


https://wolfram.com/xid/0duo3g7o0cctv-75fm57

Selected date and time data will return with head SQLDateTime:

https://wolfram.com/xid/0duo3g7o0cctv-ztj4zu

Express the selected data in its original form:

https://wolfram.com/xid/0duo3g7o0cctv-4c3vto


https://wolfram.com/xid/0duo3g7o0cctv-x7lnv4
Generalizations & Extensions (1)Generalized and extended use cases
Options (11)Common values & functionality for each option
"ColumnSymbols" (1)

https://wolfram.com/xid/0duo3g7o0cctv-el9e2l
Supply a list of symbols for query results to be assigned to:

https://wolfram.com/xid/0duo3g7o0cctv-t25fkh

https://wolfram.com/xid/0duo3g7o0cctv-zybkcs

https://wolfram.com/xid/0duo3g7o0cctv-r520cd


https://wolfram.com/xid/0duo3g7o0cctv-v1sigx


https://wolfram.com/xid/0duo3g7o0cctv-1f7xby


https://wolfram.com/xid/0duo3g7o0cctv-fnhcq2

https://wolfram.com/xid/0duo3g7o0cctv-l2g8mx


https://wolfram.com/xid/0duo3g7o0cctv-eebc8u


https://wolfram.com/xid/0duo3g7o0cctv-ipj4p8


https://wolfram.com/xid/0duo3g7o0cctv-6auuuc

Supply a function to operate on column names and results. If column headings have not been requested, the first argument to the function will be Null:

https://wolfram.com/xid/0duo3g7o0cctv-s73t1i


https://wolfram.com/xid/0duo3g7o0cctv-ny4ec8


https://wolfram.com/xid/0duo3g7o0cctv-rhigon


https://wolfram.com/xid/0duo3g7o0cctv-8pyj6w

https://wolfram.com/xid/0duo3g7o0cctv-h07ubr


https://wolfram.com/xid/0duo3g7o0cctv-i1boqa
"EscapeProcessing" (1)

https://wolfram.com/xid/0duo3g7o0cctv-n0iyx8
The JDBC specification defines an escape call sequence for authoring queries that would otherwise require vendor-specific SQL. Set the "EscapeProcessing" option to employ defined escape syntax:

https://wolfram.com/xid/0duo3g7o0cctv-2ubcpp
Issue a query using escaped SQL:

https://wolfram.com/xid/0duo3g7o0cctv-8beouk

Issuing the query without escape processing results in an error:

https://wolfram.com/xid/0duo3g7o0cctv-4uhvuu


SQL native to this RDBMS may be used without escape processing:

https://wolfram.com/xid/0duo3g7o0cctv-3ptt5p

Issue a query using escaped SQL:

https://wolfram.com/xid/0duo3g7o0cctv-ix8a5e


https://wolfram.com/xid/0duo3g7o0cctv-4epl82


https://wolfram.com/xid/0duo3g7o0cctv-5pefim
Section 13.4 of the JDBC 4.0 Specification contains further information on escape processing and supported functions.
"FetchSize" (1)

https://wolfram.com/xid/0duo3g7o0cctv-yiasat
The "FetchSize" option suggests to the JDBC driver a number of rows to fetch on each visit to the database. Increasing this parameter reduces the amount of network traffic required to execute a query, at the cost of memory used:

https://wolfram.com/xid/0duo3g7o0cctv-8kx3j4

Compute memory in use, including both kernel and Java layers:

https://wolfram.com/xid/0duo3g7o0cctv-ytl7m8

https://wolfram.com/xid/0duo3g7o0cctv-8wg1yq

Fetch a large result with small fetch size:

https://wolfram.com/xid/0duo3g7o0cctv-6a3xz5


https://wolfram.com/xid/0duo3g7o0cctv-5n5t15


https://wolfram.com/xid/0duo3g7o0cctv-oemuqn

Fetch a large result with large fetch size:

https://wolfram.com/xid/0duo3g7o0cctv-5mg6ih


https://wolfram.com/xid/0duo3g7o0cctv-j1u7i1


https://wolfram.com/xid/0duo3g7o0cctv-lh0xx3
"GetAsStrings" (1)
"GetGeneratedKeys" (1)

https://wolfram.com/xid/0duo3g7o0cctv-m2swsk
Open a connection to a data source that supports generated key retrieval:

https://wolfram.com/xid/0duo3g7o0cctv-wqn2ay


https://wolfram.com/xid/0duo3g7o0cctv-1htpao

Create a table with an auto-incrementing primary key:

https://wolfram.com/xid/0duo3g7o0cctv-4hhm7r


https://wolfram.com/xid/0duo3g7o0cctv-f2emee


https://wolfram.com/xid/0duo3g7o0cctv-hdgtk8

Contents of the primary key column:

https://wolfram.com/xid/0duo3g7o0cctv-pkg6cn


https://wolfram.com/xid/0duo3g7o0cctv-0yss9


https://wolfram.com/xid/0duo3g7o0cctv-8c1tjq
"MaxFieldSize" (1)

https://wolfram.com/xid/0duo3g7o0cctv-zv1dkg
For some variable-length column types the "MaxFieldSize" option can be used to limit the number of bytes returned for a field:

https://wolfram.com/xid/0duo3g7o0cctv-se0adk

Create a table with a VARCHAR field:

https://wolfram.com/xid/0duo3g7o0cctv-u1pbj4


https://wolfram.com/xid/0duo3g7o0cctv-388mg0

Select string contents, limiting byte length of results:

https://wolfram.com/xid/0duo3g7o0cctv-fk3eis


https://wolfram.com/xid/0duo3g7o0cctv-f9vakd


https://wolfram.com/xid/0duo3g7o0cctv-6fdivw


https://wolfram.com/xid/0duo3g7o0cctv-wc311n
"MaxRows" (1)

https://wolfram.com/xid/0duo3g7o0cctv-tyirmw
Limit the number of rows returned in a result:

https://wolfram.com/xid/0duo3g7o0cctv-jlxonz

https://wolfram.com/xid/0duo3g7o0cctv-m2jfve


https://wolfram.com/xid/0duo3g7o0cctv-wa8x4b


https://wolfram.com/xid/0duo3g7o0cctv-kz7m79
"ShowColumnHeadings" (1)

https://wolfram.com/xid/0duo3g7o0cctv-mqhgqo
Include column names as the first row of the results:

https://wolfram.com/xid/0duo3g7o0cctv-2g4qnj

https://wolfram.com/xid/0duo3g7o0cctv-fca3dq

The column headings are equivalent to the unqualified column names:

https://wolfram.com/xid/0duo3g7o0cctv-25nd40


https://wolfram.com/xid/0duo3g7o0cctv-hy9fj7
"Timeout" (1)
"BatchSize" (1)

https://wolfram.com/xid/0duo3g7o0cctv-ztmiji
Adjust batch size to trade off memory use and speed when issuing queries with long parameter lists:

https://wolfram.com/xid/0duo3g7o0cctv-2jkuib


https://wolfram.com/xid/0duo3g7o0cctv-c4z5j3
Generate random data for insertion:

https://wolfram.com/xid/0duo3g7o0cctv-nsqauc

https://wolfram.com/xid/0duo3g7o0cctv-l2doge

https://wolfram.com/xid/0duo3g7o0cctv-g3h5q6

Compute memory in use, including both kernel and Java layers:

https://wolfram.com/xid/0duo3g7o0cctv-5t7p9i

https://wolfram.com/xid/0duo3g7o0cctv-dopiuk

Run a query with a small batch size, resulting in relatively light Java-side memory use:

https://wolfram.com/xid/0duo3g7o0cctv-xjbrd4


https://wolfram.com/xid/0duo3g7o0cctv-mv3mqx


https://wolfram.com/xid/0duo3g7o0cctv-9pujhq

Rerun with larger batch size, necessitating fewer server trips:

https://wolfram.com/xid/0duo3g7o0cctv-klqsye

Java memory use is higher in this case:

https://wolfram.com/xid/0duo3g7o0cctv-j50szs


https://wolfram.com/xid/0duo3g7o0cctv-zhot41


https://wolfram.com/xid/0duo3g7o0cctv-dhgp7r
"JavaBatching" (1)

https://wolfram.com/xid/0duo3g7o0cctv-7qua1x
Switch parameter batching from Java layer to Wolfram Language layer to trade speed for memory:

https://wolfram.com/xid/0duo3g7o0cctv-fb2uvv


https://wolfram.com/xid/0duo3g7o0cctv-g4upgz
Generate random data for insertion:

https://wolfram.com/xid/0duo3g7o0cctv-t8285i

https://wolfram.com/xid/0duo3g7o0cctv-58hgnq

https://wolfram.com/xid/0duo3g7o0cctv-zh0cal

Compute memory in use, including both kernel and Java layers:

https://wolfram.com/xid/0duo3g7o0cctv-yptq6r

https://wolfram.com/xid/0duo3g7o0cctv-38fhu4

Run query with Java-side batching:

https://wolfram.com/xid/0duo3g7o0cctv-vu9a8q


https://wolfram.com/xid/0duo3g7o0cctv-tlbbsc


https://wolfram.com/xid/0duo3g7o0cctv-21kq65

Run query with Wolfram Language-side batching:

https://wolfram.com/xid/0duo3g7o0cctv-2pjlcq

Total memory use is lower in this case:

https://wolfram.com/xid/0duo3g7o0cctv-87mg5l


https://wolfram.com/xid/0duo3g7o0cctv-pd9w51

https://wolfram.com/xid/0duo3g7o0cctv-grzbe8
Possible Issues (1)Common pitfalls and unexpected behavior

https://wolfram.com/xid/0duo3g7o0cctv-4f5klc
Retrieving generated keys from batch operations has driver- and RDBMS-dependent behavior. In many cases, only the last generated key is returned:

https://wolfram.com/xid/0duo3g7o0cctv-ixi73d
Create a table with an auto-incrementing primary key:

https://wolfram.com/xid/0duo3g7o0cctv-sfhdgu
When using a parameterized SQLExecute with this RDBMS and driver, only the last generated key is returned:

https://wolfram.com/xid/0duo3g7o0cctv-lz8dzi


https://wolfram.com/xid/0duo3g7o0cctv-mj76ai

https://wolfram.com/xid/0duo3g7o0cctv-pcnlm9