SQLResultSetOpen
SQLResultSetOpen[query]
makes a result set from an SQL query.
Details and Options
- To use SQLResultSetOpen, you first need to load DatabaseLink using Needs["DatabaseLink`"].
- The following options can be given:
-
"Mode" "ScrollInsensitive" the timeout for the query "FetchDirection" Automatic processing direction hint to JDBC driver "FetchSize" Automatic JDBC driver hint for filling result sets
Examples
open all close allBasic Examples (2)
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"];rs = SQLResultSetOpen[SQLExecute[conn, "SELECT * FROM roysched"]]SQLResultSetClose[rs]CloseSQLConnection[conn];Needs["DatabaseLink`"]Use SQLSelect instead of SQLExecute:
conn = OpenSQLConnection["publisher"];rs = SQLResultSetOpen[SQLSelect[conn, "roysched"]]SQLResultSetClose[rs]CloseSQLConnection[conn];Options (3)
"Mode" (2)
Needs["DatabaseLink`"]Specify one of "ForwardOnly", "ScrollInsensitive", or "ScrollSensitive", depending on whether the desired result set is scrollable and sensitive to changes in the underlying data:
conn = OpenSQLConnection["publisher"];rs = SQLResultSetOpen[SQLExecute[conn, "SELECT * FROM roysched"], "Mode" -> "ForwardOnly"]SQLResultSetRead[rs, 10]SQLResultSetClose[rs]CloseSQLConnection[conn];Needs["DatabaseLink`"];
Needs["JLink`"];The mode "MySQLStreaming" is supported by some MySQL drivers. This unscrollable mode is suitable for streaming large result sets with low memory use:
conn = OpenSQLConnection[JDBC["MySQL(Connector/J)", "localhost/sandbox"], "Username" -> "guest", "Password" -> "guest"]Compute 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[]Read a conventional forward-only result set 100 rows at a time:
rs = SQLResultSetOpen[SQLExecute[conn, "SELECT * FROM test_tab LIMIT 100000"], "Mode" -> "ForwardOnly"]AbsoluteTiming[rows = 0;While[ListQ[data = SQLResultSetRead[rs, 100]], rows += Length@data];rows]Memory use is relatively high, as the driver is keeping a copy of the result set in memory:
memReport[]SQLResultSetClose[rs];
CloseSQLConnection[conn];
ReinstallJava[];
conn = OpenSQLConnection[JDBC["MySQL(Connector/J)", "localhost/sandbox"], "Username" -> "guest", "Password" -> "guest"];
memReport[]Read the result set in streaming mode:
rs = SQLResultSetOpen[SQLExecute[conn, "SELECT * FROM test_tab LIMIT 100000"], "Mode" -> "MySQLStreaming"]AbsoluteTiming[rows = 0;While[ListQ[data = SQLResultSetRead[rs, 100]], rows += Length@data];rows]Memory use in the streaming case is relatively low:
memReport[]SQLResultSetClose[rs];
CloseSQLConnection[conn];"FetchDirection" (1)
Needs["DatabaseLink`"]Specify "Forward", "Reverse", or "Unknown" to direct the JDBC driver to employ different fetching and caching strategies:
conn = OpenSQLConnection["publisher"];rs = SQLResultSetOpen[SQLExecute[conn, "SELECT * FROM roysched"], "FetchDirection" -> "Reverse"]SQLResultSetRead[rs, 10]SQLResultSetClose[rs]CloseSQLConnection[conn];