DatabaseLink`
DatabaseLink`

SQLInsert

SQLInsert[conn,table,cols,data]

inserts data into a table in an SQL connection.

Details and Options

  • To use SQLInsert, you first need to load DatabaseLink using Needs["DatabaseLink`"].
  • The following options can be given:
  • "ColumnSymbols"Nonesymbols to be associated with results
    "EscapeProcessing"Truetranslate escaped JDBC function syntax
    "FetchSize"AutomaticJDBC driver hint for filling result sets
    "GetAsStrings"Falsereturn all results as strings
    "GetGeneratedKeys"Falsereturn keys associated with updated records
    "MaxFieldSize"Automaticbyte limit for variable-length column types
    "MaxRows"Automaticthe maximum number of rows to return
    "ShowColumnHeadings"Falsewhether to return headings with the results
    "Timeout"$SQLTimeoutthe timeout for the query
    "BatchSize" 1000process inserts in batches of this many records
    "JavaBatching" Trueperform parameter batching in Java layer instead of Wolfram Language layer

Examples

open allclose all

Basic Examples  (1)

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:

Insert one row:

Insert multiple rows:

Scope  (1)

Working with Dates and Times  (1)

Date and time data may be supplied using DateObject, TimeObject or SQLDateTime. Create a table with DATE, TIME, and DATETIME column types:

Generate some data for insertion:

Insert the generated data:

Selected date and time data will return with head SQLDateTime:

Express the selected data in its original form:

Clear the table:

A DATE column will ignore minute, hour, and second elements in supplied date constructs. A TIME column will ignore year, month, and day elements in supplied date constructs.

For column types that record both date and time elements, the time will be taken to be 00:00:00 if not supplied. The date will be taken to be today if not supplied:

Options  (2)

"BatchSize"  (1)

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

Generate random data for insertion:

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

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

Reset:

Rerun with larger batch size, necessitating fewer server trips:

Java memory use is higher in this case:

"JavaBatching"  (1)

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

Generate random data for insertion:

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

Run query with Java-side batching:

Reset:

Run query with Wolfram Language-side batching:

Total memory use is lower in this case:

Possible Issues  (1)

A DateObject entered without a setting for the TimeZone option will use the default $TimeZone. Note that a numeric value for the time zone will not adjust for daylight saving that might apply to the date.

Here the value of $TimeZone is a numerical value.

This time zone setting is used by DateObject[{2017,1,1}].

The time returned by SQLDateTime comes back using the default timezone of "America/Chicago".

To avoid this problem, use a named zone ID or $TimeZoneEntity.