SQLExecute
DatabaseLink`
DatabaseLink`
SQLExecute
SQLExecute[conn,command]
SQL接続でコマンドを実行する．
SQLExecute[conn,command,args]
コマンドに引数を渡す．
SQLExecute[SQLSelect[conn,...]]
conn の開閉を管理する．
詳細とオプション
- SQLExecuteを使うためには，まずNeeds["DatabaseLink`"]を使って DatabaseLink をロードする必要がある．
- この関数はデータを削除して元に戻せなくなることがあるため，使用には十分な注意が必要．
- オプションのサポートと動作はドライバ，ドライバのバージョン，RDBMSタイプによって異なる．
- クエリの影響を受ける行数を指定する整数を返す．表の削除の際にエラーが生じたら$Failedを返す．
- 使用可能なオプション
-
"ColumnSymbols" None 結果に関連付けるシンボル "EscapeProcessing" True エスケープされたJDBC関数シンタックスを変換する "FetchSize" Automatic JDBCドライバへの結果セットの大きさの提示 "GetAsStrings" False すべての結果を文字列として返す "GetGeneratedKeys" False 更新されたレコードに関連付けられたキーを返す "MaxFieldSize" Automatic 可変長の列のタイプのバイト制限 "MaxRows" Automatic 返される最大行数 "ShowColumnHeadings" False 結果とともに列見出しを返すかどうか "Timeout" $SQLTimeout クエリのタイムアウト "BatchSize" 1000 パラメータを伴うクエリはこのサイズのバッチごとに処理される "JavaBatching" True Wolfram言語層の代りにJava層でパラメータのバッチ処理を行う
例題
すべて開く すべて閉じる例 (1)
Needs["DatabaseLink`"]このセクションの例を実際に試してみて，記載の通りに動作しなかった場合は，データベースの例の使用で述べているようにDatabaseLink`DatabaseExamples`パッケージを使ってデータベースをインストールするか，もとの状態に戻す必要がある可能性がある．
conn = OpenSQLConnection["demo"];SQLExecute[conn, "SELECT * FROM SAMPLETABLE1"]SQLExecute[conn, "CREATE TABLE TEST (X INTEGER, Y DOUBLE)"]SQLExecute[conn, "INSERT INTO TEST (X,Y) VALUES (2, 6.7)"]SQLExecute[conn, "INSERT INTO TEST (X,Y) VALUES (`1`, `2`)", {5, 2.1}]SQLExecute[conn, "SELECT * FROM TEST"]SQLExecute[conn, "UPDATE TEST SET Y=6.8"]SQLExecute[conn, "SELECT * FROM TEST"]SQLExecute[conn, "DELETE FROM TEST"]SQLExecute[conn, "DROP TABLE TEST"]CloseSQLConnection[conn];スコープ (5)
データの選択 (1)
Needs["DatabaseLink`"]conn = OpenSQLConnection["publisher"];SQLExecute[conn, "SELECT * FROM ROYSCHED WHERE ROYALTY >= .11 AND ROYALTY <= .12"]SQLExecute[conn, "SELECT * FROM ROYSCHED WHERE ROYALTY >= `1` AND ROYALTY <= `2`", {0.11, 0.12}]SQLExecute[conn, "SELECT `1` FROM ROYSCHED WHERE TITLE_ID = `2`", {SQLColumn["ROYALTY"], "BS1011"}]SQLExecute[conn, "SELECT `1` FROM ROYSCHED WHERE TITLE_ID = `2`", {SQLArgument[SQLColumn["LORANGE"], SQLColumn["HIRANGE"], SQLColumn["ROYALTY"]], "BS1011"}]SQLExecute[conn, "SELECT COUNT(ROYALTY) FROM ROYSCHED"]SQLExecute[conn, "SELECT MIN(ROYALTY) FROM ROYSCHED"]SQLExecute[conn, "SELECT ROYALTY * 2 FROM ROYSCHED"]SQLExecute[conn, "SELECT ROYALTY / 10 FROM ROYSCHED"]SQLExecute[conn, "SELECT -ROYALTY FROM ROYSCHED"]SQLExecute[conn, "SELECT DISTINCT ROYALTY FROM ROYSCHED"]SQLExecute[conn, "SELECT TITLE_ID, MIN(ROYALTY) FROM ROYSCHED GROUP BY TITLE_ID"]SQLExecute[conn, "SELECT TOP 5 * FROM ROYSCHED"]SQLExecute[conn, "SELECT LIMIT 5 10 * FROM ROYSCHED"]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"]CloseSQLConnection[conn];表の作成 (1)
Needs["DatabaseLink`"]conn = OpenSQLConnection["demo"];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))"]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))"]SQLExecute[conn, "DROP TABLE MAILER"];SQLExecute[conn, "DROP TABLE ADDRESSES"];CloseSQLConnection[conn];データの更新 (1)
Needs["DatabaseLink`"]conn = OpenSQLConnection["demo"];SQLExecute[conn, "CREATE TABLE TEST (X INTEGER, Y DOUBLE)"];SQLExecute[conn, "INSERT INTO TEST (X, Y) VALUES (4, 8.3)"];SQLExecute[conn, "INSERT INTO TEST (X, Y) VALUES (3, 9.1)"];SQLExecute[conn, "UPDATE TEST SET X = 7 WHERE Y < 9"]SQLExecute[conn, "SELECT * FROM TEST"]SQLExecute[conn, "UPDATE TEST SET X = `1` WHERE Y >= `2`", {6, 9}]SQLExecute[conn, "SELECT * FROM TEST"]SQLExecute[conn, "DROP TABLE TEST"];CloseSQLConnection[conn];データの削除 (1)
Needs["DatabaseLink`"]conn = OpenSQLConnection["demo"];SQLExecute[conn, "CREATE TABLE TEST (X INTEGER, Y DOUBLE)"];SQLExecute[conn, "INSERT INTO TEST (X, Y) VALUES (4, 8.3)"];SQLExecute[conn, "INSERT INTO TEST (X, Y) VALUES (3, 9.1)"];SQLExecute[conn, "INSERT INTO TEST (X, Y) VALUES (1, 1.6)"];SQLExecute[conn, "DELETE FROM TEST WHERE X = 1"]SQLExecute[conn, "DELETE FROM TEST WHERE Y = `1`", {8.3}]SQLExecute[conn, "DROP TABLE TEST"];CloseSQLConnection[conn];日付と時刻の使用 (1)
Needs["DatabaseLink`"]日付と時刻のデータは，DateObject，TimeObject，またはSQLDateTimeを使って与えることができる．コラムタイプがDATE，TIME，DATETIME である表を作成する：
conn = OpenSQLConnection[JDBC["HSQL(Memory)", "temp"]];SQLCreateTable[conn, SQLTable["DateTimeTable"], {
SQLColumn["Col1", "DataTypeName" -> "DATE"], SQLColumn["Col2", "DataTypeName" -> "TIME"],
SQLColumn["Col3", "DataTypeName" -> "DATETIME"]}]SQLExecute[conn, "INSERT INTO DateTimeTable (Col1, Col2, Col3) VALUES (`1`)", {SQLArgument[DateObject[], TimeObject[], SQLDateTime[DateList[]]]}]選択された日付と時刻は頭部SQLDateTimeで返される：
dat = SQLSelect[conn, SQLTable["DateTimeTable"]]Inner[Apply, {DateObject, TimeObject, Identity}, First@dat, List]CloseSQLConnection[conn];一般化と拡張 (1)
オプション (11)
"ColumnSymbols" (1)
Needs["DatabaseLink`"]conn = OpenSQLConnection["demo"];Clear[entries, values, names];SQLExecute[conn, "SELECT ENTRY, VALUE, NAME FROM SAMPLETABLE1", "ColumnSymbols" -> {entries, values, names}];entriesvaluesnamesSQLExecute[conn, "SELECT ENTRY, VALUE, NAME FROM SAMPLETABLE1", "ColumnSymbols" -> Automatic];Names["Global`*"]Global`col1Global`col2Global`col3列の名前と結果に作用する関数を与える．列見出しが要求されていない場合は，関数の最初の引数はNullとなる：
SQLExecute[conn, "SELECT ENTRY, VALUE FROM SAMPLETABLE1", "ShowColumnHeadings" -> True,
"ColumnSymbols" -> Function[{cols, res},
With[{syms = Symbol["SAMPLETABLE1" <> #]& /@ cols},
Evaluate[syms] = Transpose[res]
]
]
]SAMPLETABLE1ENTRYSAMPLETABLE1VALUEClear@values;
SQLExecute[conn, "SELECT VALUE FROM SAMPLETABLE1", "ColumnSymbols" -> ((values = Flatten[#2])&)];valuesCloseSQLConnection[conn];"EscapeProcessing" (1)
Needs["DatabaseLink`"]JDBCの仕様は，クエリを作成するためのエスケープ呼出しシーケンスを定義する．そうでなければ業者特有のSQLが必要となる．"EscapeProcessing"オプションを設定して，定義されたエスケープシンタックスを使用する：
conn = OpenSQLConnection[JDBC["H2(Memory)", "sandbox"]];SQLExecute[conn, "SELECT {fn week({d '2014-01-08'})}", "EscapeProcessing" -> True]SQLExecute[conn, "SELECT {fn week({d '2014-01-08'})}", "EscapeProcessing" -> False]
このRDBMSにネイティブのSQLはエスケープ処理をしないでも使用できる：
SQLExecute[conn, "SELECT extract(week from DATE '2014-01-08')", "EscapeProcessing" -> False]SQLExecute[conn, "SELECT {fn lcase('GIRAFFE')}", "EscapeProcessing" -> True]SQLExecute[conn, "SELECT lower('GIRAFFE')"]CloseSQLConnection[conn];JDBC 4.0の仕様のセクション13.4には，エスケープ処理とサポートされている関数の詳細情報が含まれている．
"FetchSize" (1)
Needs["JLink`"];
Needs["DatabaseLink`"];"FetchSize"オプションは，データベースへの各アクセスの際に取り出す行数をJDBCドライバに提案する．パラメータを増やすと，使用メモリ量が増え，代りにクエリの実行に必要なネットワークトラフィック量が減る：
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@@connSpecjavaMem[] := 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[]AbsoluteTiming[
ByteCount@SQLExecute[conn, "SELECT * FROM test_tab", "MaxRows" -> 20000, "FetchSize" -> 5]
]memReport[]CloseSQLConnection[conn];
ReinstallJava[];
conn = OpenSQLConnection@@connSpec;
memReport[]AbsoluteTiming[
ByteCount@SQLExecute[conn, "SELECT * FROM test_tab", "MaxRows" -> 20000, "FetchSize" -> 10000]
]memReport[]CloseSQLConnection[conn];"GetAsStrings" (1)
"GetGeneratedKeys" (1)
Needs["DatabaseLink`"]生成されたキーの読取りをサポートするデータソースへの接続を開く：
conn = OpenSQLConnection["demo"]SQLConnectionInformation[conn, "SupportsGetGeneratedKeys"]SQLExecute[conn,
"CREATE TABLE PEOPLE (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(128) DEFAULT 'Steve',
age INTEGER
)"]SQLExecute[conn, "INSERT INTO PEOPLE (name, age) VALUES ('Amy', 64), ('Jean', 47)",
"GetGeneratedKeys" -> True]SQLExecute[conn, "INSERT INTO PEOPLE (name, age) VALUES (?,?)",
{{"Amy", 64}, {"Jean", 47}},
"GetGeneratedKeys" -> True]SQLExecute[conn, "SELECT id FROM PEOPLE"]SQLDropTable[conn, "PEOPLE"]CloseSQLConnection[conn];"MaxFieldSize" (1)
Needs["DatabaseLink`"]可変長の列のタイプによっては，"MaxFieldSize"オプションを使ってフィールドに返されるバイト数を制限することができる：
conn = OpenSQLConnection[JDBC["Derby(Embedded)", FileNameJoin[{$TemporaryDirectory, "scratch"}]], "Properties" -> {"create" -> "true"}]SQLExecute[conn, "CREATE TABLE STRINGS (string VARCHAR(512))"]SQLExecute[conn, "INSERT INTO strings (string) VALUES (?)", {StringTake[ExampleData[{"Text", "AeneidEnglish"}], 512]}
]res = SQLExecute[conn, "SELECT string FROM strings", "MaxFieldSize" -> 64]StringLength@res[[1, 1]]SQLDropTable[conn, "STRINGS"]CloseSQLConnection[conn];"MaxRows" (1)
Needs["DatabaseLink`"]conn = OpenSQLConnection["publisher"];SQLExecute[conn, "SELECT COUNT(*) FROM AUTHORS"]SQLExecute[conn, "SELECT * FROM AUTHORS", "MaxRows" -> 5]//TableFormCloseSQLConnection[conn];"ShowColumnHeadings" (1)
Needs["DatabaseLink`"]conn = OpenSQLConnection["publisher"];res = SQLExecute[conn, "SELECT * FROM AUTHORS", "ShowColumnHeadings" -> True, "MaxRows" -> 5];TableForm@resFirst@res === SQLColumnNames[conn, "AUTHORS"][[All, 2]]CloseSQLConnection[conn];"Timeout" (1)
Needs["DatabaseLink`"]conn = OpenSQLConnection[JDBC["MySQL(Connector/J)", "localhost/testdb"]]AbsoluteTiming@SQLExecute[conn, "SELECT SLEEP(5)", "Timeout" -> 3]
CloseSQLConnection[conn];"BatchSize" (1)
Needs["DatabaseLink`"];
Needs["JLink`"];長いパラメータリストを含むクエリを生成する際に，使用メモリ量と速度のトレードオフをバッチサイズで調整する：
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@@connSpecSQLExecute[conn,
"CREATE TABLE SCRATCH(
A FLOAT,
B INTEGER,
C VARCHAR(128)
)"];randomRows[n_] := Transpose[{RandomInteger[10000, n], RandomReal[1, n], RandomChoice[WordData[], n]}];data = randomRows[2 * 10 ^ 5];ByteCount@datajavaMem[] := 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[]小さいバッチサイズでクエリを実行すると，Java側のメモリ使用量は比較的小さくなる：
AbsoluteTiming@SQLExecute[conn, "INSERT INTO SCRATCH (A, B, C) VALUES (?, ?, ?)",
data,
"BatchSize" -> 10
]//FirstmemReport[]SQLDelete[conn, "SCRATCH"];
CloseSQLConnection[conn];
ReinstallJava[];
conn = OpenSQLConnection@@connSpec;
memReport[]大きいバッチサイズでもう一度実行すると，必要なサーバートリップが少なくなる：
AbsoluteTiming@SQLExecute[conn, "INSERT INTO SCRATCH (A, B, C) VALUES (?, ?, ?)",
data,
"BatchSize" -> 100000
]//FirstmemReport[]SQLDropTable[conn, "SCRATCH"]CloseSQLConnection[conn];"JavaBatching" (1)
Needs["DatabaseLink`"];
Needs["JLink`"];パラメータのバッチ処理をJava層からWolfram言語層に切り換え，速度を落とす代りにメモリ使用量を減らす：
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@@connSpecSQLExecute[conn,
"CREATE TABLE SCRATCH(
A FLOAT,
B INTEGER,
C VARCHAR(128)
)"];randomRows[n_] := Transpose[{RandomInteger[10000, n], RandomReal[1, n], RandomChoice[WordData[], n]}];data = randomRows[5 * 10 ^ 4];ByteCount@datajavaMem[] := 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[]AbsoluteTiming@SQLExecute[conn, "INSERT INTO SCRATCH (A, B, C) VALUES (?, ?, ?)",
data,
"JavaBatching" -> True,
"BatchSize" -> 10
]//FirstmemReport[]SQLDelete[conn, "SCRATCH"];
CloseSQLConnection[conn];
ReinstallJava[];
conn = OpenSQLConnection@@connSpec;
memReport[]AbsoluteTiming@SQLExecute[conn, "INSERT INTO SCRATCH (A, B, C) VALUES (?, ?, ?)",
data,
"JavaBatching" -> False,
"BatchSize" -> 10
]//FirstmemReport[]SQLDropTable[conn, "SCRATCH"];CloseSQLConnection[conn];考えられる問題 (1)
Needs["DatabaseLink`"]バッチ操作で生成されたキーを取り出すのは，ドライバとRDBMSに依存する動作である．多くの場合，最後に生成されたキーが返される：
conn = OpenSQLConnection[JDBC["SQLite(Memory)", "genkeys"]];SQLCreateTable[conn, "PEOPLE", {
SQLColumn["ID", "DataTypeName" -> "INTEGER", "PrimaryKey" -> True],
SQLColumn["NAME", "DataTypeName" -> "VARCHAR", "DataLength" -> 128, "Default" -> "Steve"],
SQLColumn["AGE", "DataTypeName" -> "INTEGER"]
}];パラメータ化されたSQLExecuteをこのRDBMSとドライバで使う場合は，最後に生成されたキーだけが返される：
SQLExecute[conn, "INSERT INTO PEOPLE (NAME, AGE) VALUES (?,?)",
{{"Amy", 64}, {"Jean", 47}},
"GetGeneratedKeys" -> True]SQLDropTable[conn, "PEOPLE"];CloseSQLConnection[conn];
JDBC 4.0の仕様