WOLFRAM

Wolfram Language & System Documentation Center

SQLColumn[…]

represents a column in an SQL table.

Details and Options
Details and Options Details and Options
Examples  
Basic Examples  
Options  
"DataTypeName"  
"DataLength"  
"Default"  
"Nullable"  
"PrimaryKey"  
See Also
Tech Notes
DatabaseLink`
DatabaseLink`

SQLColumn

SQLColumn[…]

represents a column in an SQL table.

Details and Options

  • To use SQLColumn, you first need to load DatabaseLink using Needs["DatabaseLink`"].
  • The following options can be given:
  • "DataTypeName" Nonetype of the entry
    "DataLength" Nonemaximum length for variable length data
    "Default" Nonedefault value for column
    "Nullable" Falsewhether the entry can be null
    "PrimaryKey" Falseindicates a primary key column

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"];

List the columns in a table:

Wolfram Language code: SQLColumns[conn, "SAMPLETABLE1"]

Select data matching a condition:

Wolfram Language code: SQLSelect[conn, "SAMPLETABLE1", SQLColumn["VALUE"] > 6]

Use a prepared statement:

Wolfram Language code: SQLExecute[conn, "SELECT `1` FROM `2`", {SQLArgument[SQLColumn["VALUE"], SQLColumn["NAME"]], SQLTable["SAMPLETABLE1"]}]
Wolfram Language code: CloseSQLConnection[conn];

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.

Options  (5)

"DataTypeName"  (1)

Wolfram Language code: Needs["DatabaseLink`"]

Specify data types for columns:

Wolfram Language code: conn = OpenSQLConnection["demo"];
Wolfram Language code: SQLCreateTable[conn, SQLTable["TEST"], { SQLColumn["TINYINTCOL", "DataTypeName" -> "TINYINT"], SQLColumn["SMALLINTCOL", "DataTypeName" -> "SMALLINT"], SQLColumn["INTEGERCOL", "DataTypeName" -> "INTEGER"], SQLColumn["BIGINTCOL", "DataTypeName" -> "BIGINT"], SQLColumn["NUMERICCOL", "DataTypeName" -> "NUMERIC"], SQLColumn["DECIMALCOL", "DataTypeName" -> "DECIMAL"], SQLColumn["FLOATCOL", "DataTypeName" -> "FLOAT"], SQLColumn["REALCOL", "DataTypeName" -> "REAL"], SQLColumn["DOUBLECOL", "DataTypeName" -> "DOUBLE"], SQLColumn["BITCOL", "DataTypeName" -> "BIT"], SQLColumn["LONGVARBINARYCOL", "DataTypeName" -> "LONGVARBINARY"], SQLColumn["VARBINARYCOL", "DataTypeName" -> "VARBINARY", "DataLength" -> 1000], SQLColumn["BINARYCOL", "DataTypeName" -> "BINARY"], SQLColumn["LONGVARCHARCOL", "DataTypeName" -> "LONGVARCHAR"], SQLColumn["VARCHARCOL", "DataTypeName" -> "VARCHAR", "DataLength" -> 5], SQLColumn["CHARCOL", "DataTypeName" -> "CHAR", "DataLength" -> 3], SQLColumn["DATECOL", "DataTypeName" -> "DATE"], SQLColumn["TIMECOL", "DataTypeName" -> "TIME"], SQLColumn["TIMESTAMPCOL", "DataTypeName" -> "TIMESTAMP"], SQLColumn["OBJECTCOL", "DataTypeName" -> "OBJECT"] }];
Wolfram Language code: SQLDropTable[conn, "TEST"];
Wolfram Language code: CloseSQLConnection[conn];

"DataLength"  (1)

Wolfram Language code: Needs["DatabaseLink`"]

Specify data lengths for appropriate types:

Wolfram Language code: conn = OpenSQLConnection["demo"];
Wolfram Language code: SQLCreateTable[conn, SQLTable["TEST"], {SQLColumn["X", "DataTypeName" -> "VARCHAR", "DataLength" -> 5], SQLColumn["Y", "DataTypeName" -> "CHAR", "DataLength" -> 3]}];
Wolfram Language code: SQLDropTable[conn, "TEST"];
Wolfram Language code: CloseSQLConnection[conn];

"Default"  (1)

Wolfram Language code: Needs["DatabaseLink`"]

Specify column defaults:

Wolfram Language code: conn = OpenSQLConnection["demo"];
Wolfram Language code: SQLCreateTable[conn, SQLTable["TEST"], {SQLColumn["X", "DataTypeName" -> "TINYINT", "Default" -> -1], SQLColumn["Y", "DataTypeName" -> "TIME", "Default" -> "00:00:00"]}];
Wolfram Language code: SQLDropTable[conn, "TEST"];
Wolfram Language code: CloseSQLConnection[conn];

"Nullable"  (1)

Wolfram Language code: Needs["DatabaseLink`"]

Allow nullable column values:

Wolfram Language code: conn = OpenSQLConnection["demo"];
Wolfram Language code: SQLCreateTable[conn, SQLTable["TEST"], {SQLColumn["A", "DataTypeName" -> "Integer", "Nullable" -> True], SQLColumn["B", "DataTypeName" -> "Integer", "Nullable" -> False]}];
Wolfram Language code: SQLDropTable[conn, "TEST"];
Wolfram Language code: CloseSQLConnection[conn];

"PrimaryKey"  (1)

Wolfram Language code: Needs["DatabaseLink`"]

Specify a primary key column:

Wolfram Language code: conn = OpenSQLConnection[JDBC["SQLite(Memory)", "scratch"]];
Wolfram Language code: SQLCreateTable[conn, SQLTable["TEST"], {SQLColumn["X", "DataTypeName" -> "INTEGER", "PrimaryKey" -> True], SQLColumn["Y", "DataTypeName" -> "CHAR", "DataLength" -> 3]}];
Wolfram Language code: SQLTablePrimaryKeys[conn, "TEST", "ShowColumnHeadings" -> True]
Wolfram Language code: SQLDropTable[conn, "TEST"];
Wolfram Language code: CloseSQLConnection[conn];