この節では、JMPからデータベースにアクセスする方法について説明します。
「クエリービルダー」を用いると、SQLステートメントを書かずに、データベースのデータを選択し、データを読み込むことができます。データテーブルに読み込む前に、データをプレビューすることもできます。「クエリービルダー」で作成したクエリーは、共有することができ、他のユーザがカスタマイズしたり、実行してりできます。
「クエリービルダー」で作成したクエリーは、JSLスクリプトでも実行できます。スクリプトを開かずに「クエリービルダー」で作成したクエリーを実行するには、Include()を使用します。
query = Include( "$DOCUMENTS/Airline.jmpquery" );
query << Run Foreground( );
クエリーをフォアグラウンドで実行する代わりに、Run Background()を使ってバックグラウンドで実行するか、Run()を使ってクエリーの実行に関する「クエリービルダー」の環境設定を適用することもできます。デフォルトでは、クエリーはバックグラウンドで実行されます。
クエリーで使えるメッセージの詳細については、『スクリプト構文リファレンス』のSQLのメッセージを参照してください。
JMPではODBCがサポートされています。Open Database関数を使ってJSLでSQLデータベースへアクセスできます。
dt = Open Database(
"Connect Dialog" | "DSN=...", // データソース
"sqlStatement" | "dataTableName" | "SQLFILE=...", // SQLステートメント
Invisible, //テーブルの読み込み時にそのテーブルを非表示にするオプションのキーワード
"outputTableName" // 新しいテーブルの名前
);
メモ: データベーステーブル名に$# -+/%()&|;?の文字を使用する場合は、角括弧（[]）で囲む必要があります。
第1引数には、読み込みたいデータソースを指定します。次のいずれかを指定してください。
• "Connect Dialog"を指定すると、Select Data Sourceウィンドウ（Windows）またはChoose DSNウィンドウ（macOS）が表示されます。
• "DSN="の後にデータソースの名前、および、接続に必要な情報を指定すると、そのデータソースに接続します。Windowsでは、ODBCデータソース アドミニストレーターの［ユーザー DSN］タブまたは［システム DSN］タブの名前列にデータソース名が表示されます。macOSでは、ODBC ManagerまたはiODBC Driver ManagerにDSNが表示されます。その他の文字列は、ODBCデータソースによって異なります。
たとえば、次のような手順で要約できます。
"DSN=dBASE Files;DBQ=C:/Program Files/SAS/JMP/17/Samples/Import Data;"
第2引数には、次の3つのうちいずれかを2重引用符で囲んで指定します。
1. 実行するSQLステートメント。たとえば次のように、2重引用符で囲んで、SELECTステートメントを指定してください。
"SELECT AGE, SEX, WEIGHT FROM BIGCLASS"
SQLは、データソースがサポートしているSQLに適合していなければなりません。つまり、「Big Class」という名前のテーブルは、「Big」と「Class」の間にスペースがあるため、それを考慮して引用符で囲む必要があります（スペースが許可されている場合）。引用符はデータソースによって異なりますが、通常は、二重引用符の「"」か、一重引用符の「’」および「‘」か、もしくは鉤括弧の「[]」を使います。
2. データテーブルの名前。データテーブル名だけを指定した場合、"SELECT * FROM"というSQLステートメントを実行するのと同じ処理が行われます。たとえば、第2引数を次のように指定すれば、Open Databaseは"SELECT * FROM BIGCLASS"を実行することになります。
"BIGCLASS"
3. "SQLFILE="に続いて、実行するSQLステートメントの書かれたテキストファイルへのパス。たとえば次の引数の場合は、ディレクトリC:¥にあるファイル「mySQLFile.txt」を開き、中に書かれたSQLステートメントを実行します。
"SQLFILE=C:¥mySQLFile.txt"
オプションの引数Invisibleは、非表示のデータテーブルを作成します。なお、非表示のデータテーブルは、明示的に閉じるまでメモリ内にとどまるため、不要になったものは閉じるよう注意してください。非表示のテーブルを閉じるには、Close(dt)を実行します。ここで、dtは、データテーブル参照の変数です。
オプションの引数outputTableNameは、作成される出力テーブルがあれば、その名前を指定します。Open Databaseが必ずデータテーブルを戻すわけではないことに注意してください。戻り値はヌルになることもあります。データテーブルが戻されるかどうかは、実行されるSQLステートメントの種類によります。たとえば、SELECTステートメントはデータテーブルを戻しますが、DROP TABLEステートメントはデータテーブルを戻しません。
JMPのデータテーブルを、JSLを使ってデータベースに保存するには、データテーブルの参照にSave Database( )メッセージを送ります。
dt << Save Database( "connectInfo", "TableName" );
第1引数は、Open Databaseの場合と同様に機能します。一部のデータベースでは、既存のテーブルに置き換えてテーブルを保存することができないので注意してください。そのような場合にデータベース上のテーブルを置換するには、Open Databaseコマンドで、「DROP TABLE」というSQLステートメントを実行して、そのテーブルをあらかじめ削除してください。
Open Database ( "connectinfo", "DROP TABLE TableName" );
メモ: JMP 13以降では、名前にスペースが入っているテーブルや、大文字と小文字が混在しているテーブルでも、データソースがサポートしている限り、保存することができます。スペースは、Apache HiveとApache Hadoop以外のほとんどのデータソースでサポートされています。大文字と小文字が混在している名前は、そのまま使用されますが、SQLではほとんどの場合大文字と小文字の区別がありません。
次のスクリプトは、SQLクエリーでデータベースを開き、それを新しい名前でデータベースに保存し、その後、新しいテーブルを削除します。
dt = Open Database(
"Connect Dialog",
"SELECT age, sex, weight FROM \!"Bigclass$\!"",
"My Big Class"
);
dt << Save Database( "Connect Dialog", "MY_BIG_CLASS" );
Open Database( "Connect Dialog", "DROP TABLE BIGCLASS.MY_BIG_CLASS" );
メモ: ODBCデータベースからデータを読み込む際、ユーザIDとパスワード情報を含んだテーブル変数が追加される可能性があります。これを防ぐためには、pref(ODBC Hide Connection String(1))によって環境設定を設定します。または、［ファイル］メニュー（Windows）または［JMP］メニュー（macOS）を選択し、［環境設定］>［テーブル］で［ODBC接続文字列を非表示にする］を選択します。『JMPの使用法』のデータテーブルの環境設定を参照してください。
以下の関数を使用すると、データベースに対して、より複雑な処理を行えます。
db = Create Database Connection( "パスワードを使用した接続文字列" );
Execute SQL( db, "SQL statement", <invisible>, <"New Table Title"> );
Close Database Connection( db );
これら3つの関数を使用すれば、接続を開き、Execute SQLを数回呼び出した後、接続を閉じることができます。Create Database Connectionは、Execute SQLとClose Database Connectionで使用できるハンドルを戻します。
送信されたSQL文によって、データベースのテーブルから、JMPのデータテーブルが作成される場合と、作成されない場合があります。「SELECT」は通常、JMPのデータテーブルを作成します。しかし、「INSERT INTO」は、データベース内のテーブルを変更するものなので、JMPのデータテーブルは作成されません。
次のプログラムは、データベースへの接続を開きます。
dbc = Create Database Connection(
"DSN=dBASE Files;DBQ=$SAMPLE_IMPORT_DATA/;"
);
次のプログラムは、上記の接続を使って、SQLステートメントを実行します。
dt = Execute SQL( dbc,
"SELECT HEIGHT, WEIGHT FROM Bigclass", "NewTable"
);
次のプログラムは、処理が完了したら、ODBC接続を閉じます。
Close Database Connection( dbc );
テーブルを選択しないで、データベース接続だけを確立したほうがよい場合もあるかもしれません。スクリプトにより、すでに確立されている接続を使用してDatabase Openウィンドウを開くことが可能です。このようなスクリプトは暗号化しておくことをお勧めします。以下はその例です。
dbc = Create Database Connection(
"DSN=dBASE Files;DBQ=$SAMPLE_IMPORT_DATA/;UID=MyDatabase;pwd=MyPassword");
Main Menu( " Open Table" );
メモ: 接続文字列を引数に指定してExecute SQL()を実行した場合、作成されたデータテーブルの中に可読なテキストのパスワードが含まれてしまいます。それを避けるために、「クエリービルダー」でSQLクエリーを作成し、そのクエリーをJSLで実行する方法を推奨します。そうすると、パスワードがデータテーブルに含まれません。