SQLSelect
DatabaseLink`
DatabaseLink`
SQLSelect
Details and Options
- To use SQLSelect, you first need to load DatabaseLink using Needs["DatabaseLink`"].
- The following options can be given:
-
"Distinct" False whether to return only distinct results "FetchSize" Automatic JDBC driver hint for filling result sets "GetAsStrings" False whether to return the results as strings "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 "SortingColumns" None how to sort the data "ColumnSymbols" None symbols to be associated with results "Timeout" $SQLTimeout the timeout for the query
Examples
open all close allBasic Examples (1)
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.
conn = OpenSQLConnection["publisher"];SQLSelect[conn, "ROYSCHED"]SQLSelect[conn, "ROYSCHED", {"TITLE_ID", "ROYALTY"}]Join data from multiple tables:
SQLSelect[conn, {"TITLES", "ROYSCHED"}, {{"TITLES", "TITLE"}, {"TITLES", "TITLE_ID"}, {"ROYSCHED", "ROYALTY"}}, SQLColumn[{"TITLES", "TITLE_ID"}] == SQLColumn[{"ROYSCHED", "TITLE_ID"}]]//Short[#, 15]&Select data matching a condition:
SQLSelect[conn, "ROYSCHED", {"TITLE_ID", "ROYALTY"}, .10 < SQLColumn["ROYALTY"] < .15]Select data matching a pattern:
SQLSelect[conn, "ROYSCHED", {"TITLE_ID", "ROYALTY"}, SQLStringMatchQ[SQLColumn["TITLE_ID"], "C%"]]SQLSelect[conn, "ROYSCHED", {"TITLE_ID", "ROYALTY"}, SQLStringMatchQ[SQLColumn["TITLE_ID"], "_S%"]]SQLSelect[conn, "ROYSCHED", {"TITLE_ID", "ROYALTY"}, SQLMemberQ[{.14, .16}, SQLColumn["ROYALTY"]]]CloseSQLConnection[conn];Scope (1)
Needs["DatabaseLink`"]conn = OpenSQLConnection["publisher"];Select data matching a condition:
SQLSelect[conn, "ROYSCHED", {"TITLE_ID", "ROYALTY"}, .13 > SQLColumn["ROYALTY"] > .10]SQLSelect[conn, "ROYSCHED", {"TITLE_ID", "ROYALTY"}, SQLColumn["ROYALTY"] == .12]SQLSelect[conn, "ROYSCHED", {"TITLE_ID", "ROYALTY"}, SQLColumn["ROYALTY"] != .12]SQLSelect[conn, "ROYSCHED", {"TITLE_ID", "ROYALTY"}, SQLColumn["ROYALTY"] > .12]SQLSelect[conn, "ROYSCHED", {"TITLE_ID", "ROYALTY"}, SQLColumn["ROYALTY"] < .12]SQLSelect[conn, "ROYSCHED", {"TITLE_ID", "ROYALTY"}, SQLColumn["ROYALTY"] ≥ .12]SQLSelect[conn, "ROYSCHED", {"TITLE_ID", "ROYALTY"}, SQLColumn["ROYALTY"] ≤ .12]Combine conditions using And and Or:
SQLSelect[conn, "ROYSCHED", {"TITLE_ID", "LORANGE", "ROYALTY"}, SQLColumn["ROYALTY"] == .12 && SQLColumn["LORANGE"] > 1000]SQLSelect[conn, "ROYSCHED", {"TITLE_ID", "ROYALTY"}, SQLColumn["ROYALTY"] == .12 || SQLColumn["ROYALTY"] == .14]CloseSQLConnection[conn];Options (8)
"Distinct" (1)
"FetchSize" (1)
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:
AddToClassPath["/Users/me/Documents/DatabaseLink/drivers"];
connSpec = {JDBC["oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@server.domain.wolfram.com:1521:xe"], Username -> "GUEST", Password -> "GUEST"};conn = OpenSQLConnection@@connSpecCompute memory in use, including both kernel and Java layers:
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"}}]memReport[]Fetch a large result with a small fetch size:
AbsoluteTiming[
ByteCount@SQLSelect[conn, "test_tab", "MaxRows" -> 20000, "FetchSize" -> 5]
]memReport[]CloseSQLConnection[conn];
ReinstallJava[];
conn = OpenSQLConnection@@connSpec;
memReport[]Fetch a large result with a large fetch size:
AbsoluteTiming[
ByteCount@SQLExecute[conn, "SELECT * FROM test_tab", "MaxRows" -> 20000, "FetchSize" -> 10000]
]memReport[]CloseSQLConnection[conn];"GetAsStrings" (1)
Needs["DatabaseLink`"]Retrieve data without converting it to Wolfram Language types:
conn = OpenSQLConnection["publisher"]SQLSelect[conn, "ROYSCHED", {"ROYALTY"}, "GetAsStrings" -> True]//InputFormCloseSQLConnection[conn];"MaxFieldSize" (1)
Needs["DatabaseLink`"]For some variable-length column types the "MaxFieldSize" option can be used to limit the number of bytes returned for a field:
conn = OpenSQLConnection[JDBC["Derby(Embedded)", FileNameJoin[{$TemporaryDirectory, "scratch"}]], "Properties" -> {"create" -> "true"}]Create a table with a VARCHAR field:
SQLCreateTable[conn, SQLTable["STRINGS"], {SQLColumn["string", "DataTypeName" -> "VARCHAR(512)"]}]SQLInsert[conn, SQLTable["STRINGS"], {SQLColumn["string"]}, {StringTake[ExampleData[{"Text", "AeneidEnglish"}], 512]}
]Select string contents, limiting byte length of results:
res = SQLSelect[conn, "STRINGS", "string", "MaxFieldSize" -> 64]StringLength@res[[1, 1]]SQLDropTable[conn, "STRINGS"]CloseSQLConnection[conn];"MaxRows" (1)
"ShowColumnHeadings" (1)
Needs["DatabaseLink`"]Include column names as the first row of the results:
conn = OpenSQLConnection["publisher"];SQLSelect[conn, "ROYSCHED", {"TITLE_ID", "ROYALTY"}, "ShowColumnHeadings" -> True]//Take[#, 10]&//TableFormCloseSQLConnection[conn];"SortingColumns" (1)
Needs["DatabaseLink`"]conn = OpenSQLConnection["publisher"];SQLSelect[conn, "ROYSCHED", {"TITLE_ID", "ROYALTY"},
"SortingColumns" -> {
SQLColumn["ROYALTY"] -> "Ascending",
SQLColumn["TITLE_ID"] -> "Ascending"
}
]CloseSQLConnection[conn];"ColumnSymbols" (1)
Needs["DatabaseLink`"]Supply a list of symbols for query results to be assigned to:
conn = OpenSQLConnection["demo"];Clear[entries, values, names];SQLSelect[conn, "SAMPLETABLE1", {"ENTRY", "VALUE", "NAME"}, "ColumnSymbols" -> {entries, values, names}];entriesvaluesnamesClear[entries, values, names];SQLSelect[conn, "SAMPLETABLE1", {"ENTRY", "VALUE", "NAME"}, "ColumnSymbols" -> Automatic];Names["Global`col*"]Global`col1Global`col2Global`col3Supply 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:
SQLSelect[conn, "SAMPLETABLE1", {"ENTRY", "VALUE"}, "ShowColumnHeadings" -> True,
"ColumnSymbols" -> Function[{cols, res},
With[{syms = Symbol["SAMPLETABLE1" <> #]& /@ cols},
Evaluate[syms] = Transpose[res]
]
]
]SAMPLETABLE1ENTRYSAMPLETABLE1VALUEClear@values;
SQLSelect[conn, "SAMPLETABLE1", "VALUE", "ColumnSymbols" -> ((values = Flatten[#2])&)];valuesCloseSQLConnection[conn];Applications (1)
Needs["DatabaseLink`"]conn = OpenSQLConnection["publisher"];data = SQLSelect[conn, "ROYSCHED", {"TITLE_ID", "ROYALTY"}]Histogram[data[[All, -1]]]CloseSQLConnection[conn];