Wolfram Language & System Documentation Center

SQLExecute

DatabaseLink`

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

Basic Examples (1)

Wolfram Language code: Needs["DatabaseLink`"]

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.

Open a connection:

Wolfram Language code: conn = OpenSQLConnection["demo"];

Select data:

Wolfram Language code: SQLExecute[conn, "SELECT * FROM SAMPLETABLE1"]

Create a table:

Wolfram Language code: SQLExecute[conn, "CREATE TABLE TEST (X INTEGER, Y DOUBLE)"]

Insert data:

Wolfram Language code: SQLExecute[conn, "INSERT INTO TEST (X,Y) VALUES (2, 6.7)"]

Wolfram Language code: SQLExecute[conn, "INSERT INTO TEST (X,Y) VALUES (`1`, `2`)", {5, 2.1}]

Select data:

Wolfram Language code: SQLExecute[conn, "SELECT * FROM TEST"]

Update data:

Wolfram Language code: SQLExecute[conn, "UPDATE TEST SET Y=6.8"]

Wolfram Language code: SQLExecute[conn, "SELECT * FROM TEST"]

Delete data:

Wolfram Language code: SQLExecute[conn, "DELETE FROM TEST"]

Drop the table:

Wolfram Language code: SQLExecute[conn, "DROP TABLE TEST"]

Wolfram Language code: CloseSQLConnection[conn];

Scope (5)

Selecting Data (1)

Wolfram Language code: Needs["DatabaseLink`"]

Wolfram Language code: conn = OpenSQLConnection["publisher"];

Select data matching a condition:

Wolfram Language code: SQLExecute[conn, "SELECT * FROM ROYSCHED WHERE ROYALTY >= .11 AND ROYALTY <= .12"]

Select the same data using a prepared statement:

Wolfram Language code: SQLExecute[conn, "SELECT * FROM ROYSCHED WHERE ROYALTY >= `1` AND ROYALTY <= `2`", {0.11, 0.12}]

Specify a column in a prepared statement:

Wolfram Language code: SQLExecute[conn, "SELECT `1` FROM ROYSCHED WHERE TITLE_ID = `2`", {SQLColumn["ROYALTY"], "BS1011"}]

Give a sequence of arguments in a prepared statement:

Wolfram Language code:

SQLExecute[conn, "SELECT `1` FROM ROYSCHED WHERE TITLE_ID = `2`", {SQLArgument[SQLColumn["LORANGE"], SQLColumn["HIRANGE"], SQLColumn["ROYALTY"]], "BS1011"}]

Apply a database function to the selected data:

Wolfram Language code: SQLExecute[conn, "SELECT COUNT(ROYALTY) FROM ROYSCHED"]

Wolfram Language code: SQLExecute[conn, "SELECT MIN(ROYALTY) FROM ROYSCHED"]

Wolfram Language code: SQLExecute[conn, "SELECT ROYALTY * 2 FROM ROYSCHED"]

Wolfram Language code: SQLExecute[conn, "SELECT ROYALTY / 10 FROM ROYSCHED"]

Wolfram Language code: SQLExecute[conn, "SELECT -ROYALTY FROM ROYSCHED"]

Select only distinct values:

Wolfram Language code: SQLExecute[conn, "SELECT DISTINCT ROYALTY FROM ROYSCHED"]

Group values:

Wolfram Language code: SQLExecute[conn, "SELECT TITLE_ID, MIN(ROYALTY) FROM ROYSCHED GROUP BY TITLE_ID"]

Select a range of results:

Wolfram Language code: SQLExecute[conn, "SELECT TOP 5 * FROM ROYSCHED"]

Wolfram Language code: SQLExecute[conn, "SELECT LIMIT 5 10 * FROM ROYSCHED"]

Join data from multiple tables:

Wolfram Language code:

SQLExecute[conn, "SELECT DISTINCT TITLES.TITLE FROM TITLES INNER JOIN ROYSCHED ON TITLES.TITLE_ID=ROYSCHED.TITLE_ID WHERE TITLES.PUB_ID='0877' AND ROYSCHED.ROYALTY > .1"]

Wolfram Language code: CloseSQLConnection[conn];

Creating Tables (1)

Wolfram Language code: Needs["DatabaseLink`"]

Wolfram Language code: conn = OpenSQLConnection["demo"];

Wolfram Language code:

SQLExecute[conn, 
	"CREATE TABLE ADDRESSES (
    USERNAME VARCHAR(32) NOT NULL PRIMARY KEY, 
    ADDRESS VARCHAR(128),
    CITY VARCHAR(64), 
    ZIPCODE VARCHAR(12), 
    UNIQUE (ADDRESS, CITY, ZIPCODE))"]

Wolfram Language code:

SQLExecute[conn, "CREATE TABLE MAILER (
  MAILERID INT IDENTITY, 
  USERNAME VARCHAR(21) NOT NULL, 
  SENDMAILER BIT DEFAULT '1' NOT NULL,
  FOREIGN KEY (USERNAME) REFERENCES ADDRESSES (USERNAME))"]

Wolfram Language code: SQLExecute[conn, "DROP TABLE MAILER"];

Wolfram Language code: SQLExecute[conn, "DROP TABLE ADDRESSES"];

Wolfram Language code: CloseSQLConnection[conn];

Updating Data (1)

Wolfram Language code: Needs["DatabaseLink`"]

Wolfram Language code: conn = OpenSQLConnection["demo"];

Wolfram Language code: SQLExecute[conn, "CREATE TABLE TEST (X INTEGER, Y DOUBLE)"];

Wolfram Language code: SQLExecute[conn, "INSERT INTO TEST (X, Y) VALUES (4, 8.3)"];

Wolfram Language code: SQLExecute[conn, "INSERT INTO TEST (X, Y) VALUES (3, 9.1)"];

Update data matching a condition:

Wolfram Language code: SQLExecute[conn, "UPDATE TEST SET X = 7 WHERE Y < 9"]

Wolfram Language code: SQLExecute[conn, "SELECT * FROM TEST"]

Use a prepared statement:

Wolfram Language code: SQLExecute[conn, "UPDATE TEST SET X = `1` WHERE Y >= `2`", {6, 9}]

Wolfram Language code: SQLExecute[conn, "SELECT * FROM TEST"]

Wolfram Language code: SQLExecute[conn, "DROP TABLE TEST"];

Wolfram Language code: CloseSQLConnection[conn];

Deleting Data (1)

Wolfram Language code: Needs["DatabaseLink`"]

Wolfram Language code: conn = OpenSQLConnection["demo"];

Wolfram Language code: SQLExecute[conn, "CREATE TABLE TEST (X INTEGER, Y DOUBLE)"];

Wolfram Language code: SQLExecute[conn, "INSERT INTO TEST (X, Y) VALUES (4, 8.3)"];

Wolfram Language code: SQLExecute[conn, "INSERT INTO TEST (X, Y) VALUES (3, 9.1)"];

Wolfram Language code: SQLExecute[conn, "INSERT INTO TEST (X, Y) VALUES (1, 1.6)"];

Delete data matching a condition:

Wolfram Language code: SQLExecute[conn, "DELETE FROM TEST WHERE X = 1"]

Use a prepared statement:

Wolfram Language code: SQLExecute[conn, "DELETE FROM TEST WHERE Y = `1`", {8.3}]

Wolfram Language code: SQLExecute[conn, "DROP TABLE TEST"];

Wolfram Language code: CloseSQLConnection[conn];

Working with Dates and Times (1)

Wolfram Language code: Needs["DatabaseLink`"]

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:

Wolfram Language code: conn = OpenSQLConnection[JDBC["HSQL(Memory)", "temp"]];

Wolfram Language code:

SQLCreateTable[conn, SQLTable["DateTimeTable"], {
	SQLColumn["Col1", "DataTypeName" -> "DATE"], SQLColumn["Col2", "DataTypeName" -> "TIME"], 
	SQLColumn["Col3", "DataTypeName" -> "DATETIME"]}]

Insert one row:

Wolfram Language code:

SQLExecute[conn, "INSERT INTO DateTimeTable (Col1, Col2, Col3) VALUES (`1`)", {SQLArgument[DateObject[], TimeObject[], SQLDateTime[DateList[]]]}]

Selected date and time data will return with head SQLDateTime:

Wolfram Language code: dat = SQLSelect[conn, SQLTable["DateTimeTable"]]

Express the selected data in its original form:

Wolfram Language code: Inner[Apply, {DateObject, TimeObject, Identity}, First@dat, List]

Wolfram Language code: CloseSQLConnection[conn];

Generalizations & Extensions (1)

Wolfram Language code: Needs["DatabaseLink`"]

Read from a data source, allowing DatabaseLink to open and close the connection:

Wolfram Language code: SQLExecute[SQLSelect["publisher", "AUTHORS", {"AU_FNAME", "AU_LNAME"}, SQLStringMatchQ[SQLColumn["AU_FNAME"], "M%"]]]

Options (11)

"ColumnSymbols" (1)

Wolfram Language code: Needs["DatabaseLink`"]

Supply a list of symbols for query results to be assigned to:

Wolfram Language code: conn = OpenSQLConnection["demo"];

Wolfram Language code:

Clear[entries, values, names];SQLExecute[conn, "SELECT ENTRY, VALUE, NAME FROM SAMPLETABLE1", "ColumnSymbols" -> {entries, values, names}];

Wolfram Language code: entries

Wolfram Language code: values

Wolfram Language code: names

Create symbols automatically:

Wolfram Language code: SQLExecute[conn, "SELECT ENTRY, VALUE, NAME FROM SAMPLETABLE1", "ColumnSymbols" -> Automatic];

Wolfram Language code: Names["Global`*"]

Wolfram Language code: Global`col1

Wolfram Language code: Global`col2

Wolfram Language code: Global`col3

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:

Wolfram Language code:

SQLExecute[conn, "SELECT ENTRY, VALUE FROM SAMPLETABLE1", "ShowColumnHeadings" -> True, 
	"ColumnSymbols" -> Function[{cols, res}, 
	With[{syms = Symbol["SAMPLETABLE1" <> #]& /@ cols}, 
	Evaluate[syms] = Transpose[res]
	]
	]
	]

Wolfram Language code: SAMPLETABLE1ENTRY

Wolfram Language code: SAMPLETABLE1VALUE

Wolfram Language code:

Clear@values;
SQLExecute[conn, "SELECT VALUE FROM SAMPLETABLE1", "ColumnSymbols" -> ((values = Flatten[#2])&)];

Wolfram Language code: values

Wolfram Language code: CloseSQLConnection[conn];

"EscapeProcessing" (1)

Wolfram Language code: Needs["DatabaseLink`"]

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:

Wolfram Language code: conn = OpenSQLConnection[JDBC["H2(Memory)", "sandbox"]];

Issue a query using escaped SQL:

Wolfram Language code: SQLExecute[conn, "SELECT {fn week({d '2014-01-08'})}", "EscapeProcessing" -> True]

Issuing the query without escape processing results in an error:

Wolfram Language code: SQLExecute[conn, "SELECT {fn week({d '2014-01-08'})}", "EscapeProcessing" -> False]

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

Wolfram Language code: SQLExecute[conn, "SELECT extract(week from DATE '2014-01-08')", "EscapeProcessing" -> False]

Issue a query using escaped SQL:

Wolfram Language code: SQLExecute[conn, "SELECT {fn lcase('GIRAFFE')}", "EscapeProcessing" -> True]

Use SQL native to this RDBMS:

Wolfram Language code: SQLExecute[conn, "SELECT lower('GIRAFFE')"]

Wolfram Language code: CloseSQLConnection[conn];

Section 13.4 of the JDBC 4.0 Specification contains further information on escape processing and supported functions.

"FetchSize" (1)

Wolfram Language code:

Needs["JLink`"];
Needs["DatabaseLink`"];

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:

Wolfram Language code:

AddToClassPath["/Users/me/Documents/DatabaseLink/drivers"];
connSpec = {JDBC["oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@server.domain.com:1521:xe"], Username -> "GUEST", Password -> "GUEST"};conn = OpenSQLConnection@@connSpec

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

Wolfram Language code:

javaMem[] := Module[{rt}, 
	LoadJavaClass["java.lang.Runtime"];
	rt = Runtime`getRuntime[];
	rt@totalMemory[](* allocated; some is free for jvm use *)
	];
memReport[] := TableForm[List@{MemoryInUse[], javaMem[], MemoryInUse[] + javaMem[]}, TableHeadings -> {None, {"Kernel", "Java", "Total"}}]

Wolfram Language code: memReport[]

Fetch a large result with small fetch size:

Wolfram Language code:

AbsoluteTiming[
	ByteCount@SQLExecute[conn, "SELECT * FROM test_tab", "MaxRows" -> 20000, "FetchSize" -> 5]
	]

Wolfram Language code: memReport[]

Reset:

Wolfram Language code:

CloseSQLConnection[conn];
ReinstallJava[];
conn = OpenSQLConnection@@connSpec;
memReport[]

Fetch a large result with large fetch size:

Wolfram Language code:

AbsoluteTiming[
	ByteCount@SQLExecute[conn, "SELECT * FROM test_tab", "MaxRows" -> 20000, "FetchSize" -> 10000]
	]

Wolfram Language code: memReport[]

Wolfram Language code: CloseSQLConnection[conn];

"GetAsStrings" (1)

Wolfram Language code: Needs["DatabaseLink`"]

Retrieve data without converting it to Wolfram Language types:

Wolfram Language code: conn = OpenSQLConnection["demo"]

Wolfram Language code: SQLExecute[conn, "SELECT * FROM SAMPLETABLE1", "GetAsStrings" -> True]//InputForm

Wolfram Language code: CloseSQLConnection[conn];

"GetGeneratedKeys" (1)

Wolfram Language code: Needs["DatabaseLink`"]

Open a connection to a data source that supports generated key retrieval:

Wolfram Language code: conn = OpenSQLConnection["demo"]

Wolfram Language code: SQLConnectionInformation[conn, "SupportsGetGeneratedKeys"]

Create a table with an auto-incrementing primary key:

Wolfram Language code:

SQLExecute[conn, 
	"CREATE TABLE PEOPLE (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
    name VARCHAR(128) DEFAULT 'Steve',
    age INTEGER
)"]

Insert rows, returning keys:

Wolfram Language code:

SQLExecute[conn, "INSERT INTO PEOPLE (name, age) VALUES ('Amy', 64), ('Jean', 47)", 
	"GetGeneratedKeys" -> True]

Wolfram Language code:

SQLExecute[conn, "INSERT INTO PEOPLE (name, age) VALUES (?,?)", 
	{{"Amy", 64}, {"Jean", 47}}, 
	"GetGeneratedKeys" -> True]

Contents of the primary key column:

Wolfram Language code: SQLExecute[conn, "SELECT id FROM PEOPLE"]

Wolfram Language code: SQLDropTable[conn, "PEOPLE"]

Wolfram Language code: CloseSQLConnection[conn];

"MaxFieldSize" (1)

Wolfram Language code: Needs["DatabaseLink`"]

For some variable-length column types the "MaxFieldSize" option can be used to limit the number of bytes returned for a field:

Wolfram Language code:

conn = OpenSQLConnection[JDBC["Derby(Embedded)", FileNameJoin[{$TemporaryDirectory, "scratch"}]], "Properties" -> {"create" -> "true"}]

Create a table with a VARCHAR field:

Wolfram Language code: SQLExecute[conn, "CREATE TABLE STRINGS (string VARCHAR(512))"]

Insert string data:

Wolfram Language code:

SQLExecute[conn, "INSERT INTO strings (string) VALUES (?)", {StringTake[ExampleData[{"Text", "AeneidEnglish"}], 512]}
	]

Select string contents, limiting byte length of results:

Wolfram Language code: res = SQLExecute[conn, "SELECT string FROM strings", "MaxFieldSize" -> 64]

Wolfram Language code: StringLength@res[[1, 1]]

Wolfram Language code: SQLDropTable[conn, "STRINGS"]

Wolfram Language code: CloseSQLConnection[conn];

"MaxRows" (1)

Wolfram Language code: Needs["DatabaseLink`"]

Limit the number of rows returned in a result:

Wolfram Language code: conn = OpenSQLConnection["publisher"];

Wolfram Language code: SQLExecute[conn, "SELECT COUNT(*) FROM AUTHORS"]

Wolfram Language code: SQLExecute[conn, "SELECT * FROM AUTHORS", "MaxRows" -> 5]//TableForm

Wolfram Language code: CloseSQLConnection[conn];

"ShowColumnHeadings" (1)

Wolfram Language code: Needs["DatabaseLink`"]

Include column names as the first row of the results:

Wolfram Language code: conn = OpenSQLConnection["publisher"];

Wolfram Language code: res = SQLExecute[conn, "SELECT * FROM AUTHORS", "ShowColumnHeadings" -> True, "MaxRows" -> 5];TableForm@res

The column headings are equivalent to the unqualified column names:

Wolfram Language code: First@res === SQLColumnNames[conn, "AUTHORS"][[All, 2]]

Wolfram Language code: CloseSQLConnection[conn];

"Timeout" (1)

Wolfram Language code: Needs["DatabaseLink`"]

Set a timeout value for a particular query:

Wolfram Language code: conn = OpenSQLConnection[JDBC["MySQL(Connector/J))", "localhost/testdb"]]

Wolfram Language code: AbsoluteTiming@SQLExecute[conn, "SELECT SLEEP(5)", "Timeout" -> 3]

Wolfram Language code: CloseSQLConnection[conn];

"BatchSize" (1)

Wolfram Language code:

Needs["DatabaseLink`"];
Needs["JLink`"];

Adjust batch size to trade off memory use and speed when issuing queries with long parameter lists:

Wolfram Language code:

AddToClassPath["/Users/me/Documents/DatabaseLink/drivers"];
connSpec = {JDBC["oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@server.domain.com:1521:xe"], Username -> "GUEST", Password -> "GUEST"};conn = OpenSQLConnection@@connSpec

Wolfram Language code:

SQLExecute[conn, 
	"CREATE TABLE SCRATCH(
    A FLOAT,
	B INTEGER,
	C VARCHAR(128)
)"];

Generate random data for insertion:

Wolfram Language code: randomRows[n_] := Transpose[{RandomInteger[10000, n], RandomReal[1, n], RandomChoice[WordData[], n]}];

Wolfram Language code: data = randomRows[2 * 10 ^ 5];

Wolfram Language code: ByteCount@data

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

Wolfram Language code:

javaMem[] := Module[{rt}, 
	LoadJavaClass["java.lang.Runtime"];
	rt = Runtime`getRuntime[];
	rt@totalMemory[](* allocated; some is free for jvm use *)
	];
memReport[] := TableForm[List@{MemoryInUse[], javaMem[], MemoryInUse[] + javaMem[]}, TableHeadings -> {None, {"Kernel", "Java", "Total"}}]

Wolfram Language code: memReport[]

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

Wolfram Language code:

AbsoluteTiming@SQLExecute[conn, "INSERT INTO SCRATCH (A, B, C) VALUES (?, ?, ?)", 
	data, 
	"BatchSize" -> 10
	]//First

Wolfram Language code: memReport[]

Reset:

Wolfram Language code:

SQLDelete[conn, "SCRATCH"];
CloseSQLConnection[conn];
ReinstallJava[];
conn = OpenSQLConnection@@connSpec;
memReport[]

Rerun with larger batch size, necessitating fewer server trips:

Wolfram Language code:

AbsoluteTiming@SQLExecute[conn, "INSERT INTO SCRATCH (A, B, C) VALUES (?, ?, ?)", 
	data, 
	"BatchSize" -> 100000
	]//First

Java memory use is higher in this case:

Wolfram Language code: memReport[]

Wolfram Language code: SQLDropTable[conn, "SCRATCH"]

Wolfram Language code: CloseSQLConnection[conn];

"JavaBatching" (1)

Wolfram Language code:

Needs["DatabaseLink`"];
Needs["JLink`"];

Switch parameter batching from Java layer to Wolfram Language layer to trade speed for memory:

Wolfram Language code:

AddToClassPath["/Users/me/Documents/DatabaseLink/drivers"];
connSpec = {JDBC["oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@server.domain.com:1521:xe"], Username -> "GUEST", Password -> "GUEST"};conn = OpenSQLConnection@@connSpec

Wolfram Language code:

SQLExecute[conn, 
	"CREATE TABLE SCRATCH(
    A FLOAT,
	B INTEGER,
	C VARCHAR(128)
)"];

Generate random data for insertion:

Wolfram Language code: randomRows[n_] := Transpose[{RandomInteger[10000, n], RandomReal[1, n], RandomChoice[WordData[], n]}];

Wolfram Language code: data = randomRows[5 * 10 ^ 4];

Wolfram Language code: ByteCount@data

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

Wolfram Language code:

javaMem[] := Module[{rt}, 
	LoadJavaClass["java.lang.Runtime"];
	rt = Runtime`getRuntime[];
	rt@totalMemory[](* allocated; some is free for jvm use *)
	];
memReport[] := TableForm[List@{MemoryInUse[], javaMem[], MemoryInUse[] + javaMem[]}, TableHeadings -> {None, {"Kernel", "Java", "Total"}}]

Wolfram Language code: memReport[]

Run query with Java-side batching:

Wolfram Language code:

AbsoluteTiming@SQLExecute[conn, "INSERT INTO SCRATCH (A, B, C) VALUES (?, ?, ?)", 
	data, 
	"JavaBatching" -> True, 
	"BatchSize" -> 10
	]//First

Wolfram Language code: memReport[]

Reset:

Wolfram Language code:

SQLDelete[conn, "SCRATCH"];
CloseSQLConnection[conn];
ReinstallJava[];
conn = OpenSQLConnection@@connSpec;
memReport[]

Run query with Wolfram Language-side batching:

Wolfram Language code:

AbsoluteTiming@SQLExecute[conn, "INSERT INTO SCRATCH (A, B, C) VALUES (?, ?, ?)", 
	data, 
	"JavaBatching" -> False, 
	"BatchSize" -> 10
	]//First

Total memory use is lower in this case:

Wolfram Language code: memReport[]

Wolfram Language code: SQLDropTable[conn, "SCRATCH"];

Wolfram Language code: CloseSQLConnection[conn];

Possible Issues (1)

Wolfram Language code: Needs["DatabaseLink`"]

Retrieving generated keys from batch operations has driver- and RDBMS-dependent behavior. In many cases, only the last generated key is returned:

Wolfram Language code: conn = OpenSQLConnection[JDBC["SQLite(Memory)", "genkeys"]];

Create a table with an auto-incrementing primary key:

Wolfram Language code:

SQLCreateTable[conn, "PEOPLE", {
	SQLColumn["ID", "DataTypeName" -> "INTEGER", "PrimaryKey" -> True], 
	SQLColumn["NAME", "DataTypeName" -> "VARCHAR", "DataLength" -> 128, "Default" -> "Steve"], 
	SQLColumn["AGE", "DataTypeName" -> "INTEGER"]
}];

When using a parameterized SQLExecute with this RDBMS and driver, only the last generated key is returned:

Wolfram Language code:

SQLExecute[conn, "INSERT INTO PEOPLE (NAME, AGE) VALUES (?,?)", 
	{{"Amy", 64}, {"Jean", 47}}, 
	"GetGeneratedKeys" -> True]

Wolfram Language code: SQLDropTable[conn, "PEOPLE"];

Wolfram Language code: CloseSQLConnection[conn];

Top

More Learning

Tech Support

Wolfram Solutions

Wolfram Solutions For Education

Get Started

Grow Your Skills

Work with Us

Educational Programs for Adults

Educational Programs for Youth

Read

SQLExecute

Details and Options

Examples

Basic Examples (1)

Scope (5)

Selecting Data (1)

Creating Tables (1)

Updating Data (1)

Deleting Data (1)

Working with Dates and Times (1)

Generalizations & Extensions (1)

Options (11)

"ColumnSymbols" (1)

"EscapeProcessing" (1)

"FetchSize" (1)

"GetAsStrings" (1)

"GetGeneratedKeys" (1)

"MaxFieldSize" (1)

"MaxRows" (1)

"ShowColumnHeadings" (1)

"Timeout" (1)

"BatchSize" (1)

"JavaBatching" (1)

Possible Issues (1)

SQLExecute

Details and Options

Examples

Basic Examples (1)

Scope (5)

Selecting Data (1)

Creating Tables (1)

Updating Data (1)

Deleting Data (1)

Working with Dates and Times (1)

Generalizations & Extensions (1)

Options (11)

"ColumnSymbols" (1)

"EscapeProcessing" (1)

"FetchSize" (1)

"GetAsStrings" (1)

"GetGeneratedKeys" (1)

"MaxFieldSize" (1)

"MaxRows" (1)

"ShowColumnHeadings" (1)

"Timeout" (1)

"BatchSize" (1)

"JavaBatching" (1)

Possible Issues (1)

See Also

Tech Notes

Related Guides

Related Links