FAQ - 技術的質問: SQL

FAQのトップページに戻る

IDQuestion
016CachéのIsolation レベルについて教えてください。
050グローバルデータをオブジェクトやSQLから利用したい
051SQLでユニークな番号を自動付番する機能はありますか？
052SELECT ... FOR UPDATEは使用できますか？
111SQLログイン認証の際にログ出力をおこないたい
112SQLからCachéのプログラムを実行し、値を返すことはできますか？
113Cachéのクラスに32個より多くのインデックスがあると、MS-ACCESSからそのクラスにリンクしようとすると、エラーとなってリンクできない
114埋め込みSQL &SQL( select xxx into :var from ... ) としたとき、ホスト変数varに意図しない値が格納される場合があります。
169ODBCクライアントで <NOROUTINE> / <NOLINE> エラーを受け取ります。 どうすればいいですか？
188SQLインジェクション対策はありますか？
190テーブルのカラム単位にセキュリティをかけることはできますか？
210Extentクエリーの結果が、ID順になりません。
214複数の結果セットを一度に返す方法はありますか？
217CachéのODBC対応バージョンは、何ですか？
218CachéのJDBC対応バージョンは何ですか？

それぞれの項目についてのお問い合わせは、各項目内のをクリックしてください。

 

Question : 016

CachéのIsolation レベルについて教えてください。

SQL92では以下の4つのIsolation レベルを定義しています。

  1. Read Uncommitted
  2. Read Committed
  3. Repeatable Read
  4. Serializable

Caché SQLではこのうち 1. Read Uncommitted と 2. Read Committed をサポートしており、既定のIsolation レベルは Read Uncommitted です。

ダイレクトアクセスによるトランザクションではLockコマンドを用いて、各Isolation レベルで規定している振る舞いを実装する事が可能です。

| Back to Top

Question : 050

グローバルデータをオブジェクトやSQLから利用したい

SQLストレージを用いて、ユーザー作成のグローバルデータの構造をクラスにマッピングすることができます。

統一データアーキテクチャにより、テーブル定義も生成されますので、SQLでアクセスすることもできるようになります。

詳細は添付資料をご覧ください

| Back to Top

Question : 051

SQLでユニークな番号を自動付番する機能はありますか？

Cachéには連番を生成する関数($INCREMENT)があります。

その関数をSQLのSELECT文で使用する場合には OracleのSequence相当の機能を実装したクラスを利用する方法があります。

実例はサンプルをご覧ください。

| Back to Top

Question : 052

SELECT ... FOR UPDATEは使用できますか？

SELECT ... FOR UPDATEは明示的な行ロック取得の方法として多くのRDBMSで実装されているため、この機能を使われているケースも多いと思います。

ここではCachéで同等の機能を実現する方法をご紹介しています。

DECLARE CURSOR C1 IS SELECT Name FROM Person WHERE Name LIKE 'A%' FOR UPDATE
OPEN C1
LOOP
FETCH C1 INTO name
... name を表示
... 終了ならLOOPをEXIT
END LOOP
CLOSE C1

上記のようなSQL文は、Cachéでは下記のSQL文で代替可能です。

&SQL(START TRANSACTION ISOLATION LEVEL READ COMMITTED)
&SQL(UPDATE Person SET ID=ID Where Name like 'A%')
&SQL(DECLARE C1 CURSOR FOR SELECT ID,Name into :id,:name FROM Person Where Name like 'A%')
&SQL(OPEN C1)
&SQL(FETCH C1)
While (SQLCODE = 0) {
    Write id, ":  ", name,!        &SQL(FETCH C1)
}
&SQL(CLOSE C1)
&SQL(COMMIT)

| Back to Top

Question : 111

SQLログイン認証の際にログ出力をおこないたい

以下のようにユーザ名、パスワードを引数に持ち、認証が成功した場合はユーザ名、失敗したときは""(NULL)を返すルーチン(SecTest^SecTest)を作成し、標準の認証システムを書き換えることができます。

SecTest(user,pass)
// user1のパスワードがuser1の場合、認証OKのログを作成
if user="user1",pass="user1" {
set ^sqllog($i(^sqllog))="認証OK;"_$horolog_";"_user
quit user
}
// 認証できなかった場合、認証NGのログを作成
set ^sqllog($i(^sqllog))="認証NG;"_$horolog_";"_user
quit ""

このルーチンを$SYSTEM.SQL.SetSQLLoginOverride()関数を使用して置き換えます。

> do $SYSTEM.SQL.SetSQLLoginOverride("SecTest^SecTest")

ただし標準の認証ができなくなりますので、パスワードを別に保管、参照する仕組みを記述する必要があります。

| Back to Top

Question : 112

SQLからCachéのプログラムを実行し、値を返すことはできますか？

はい、できます。

添付のtest.xmlをインポートして、クラス定義をごらんください。

以下のコマンドで、ダミーデータも作成できます。

USER> do ##class(User.test).Populate(10)

メソッド getLatestID のように、CacheObjectScriptで記述し、戻り値を設定します。

さらに、このメソッドをClassMethodかつ、SQLProcedureに指定します。

これによって

SELECT SQLUser.getLatestID() as LatestID
FROM SQLUser.test
WHERE ID=1

と実行すると、Caché ObjectScriptの実行結果が、LatestIDとして表示されます。

また、こうすると、最新IDのnameだけ表示します。

SELECT name
FROM SQLUser.test
WHERE ID=SQLUser.getLatestID()

| Back to Top

Question : 113

Cachéのクラスに32個より多くのインデックスがあると、MS-ACCESSからそのクラスにリンクしようとすると、エラーとなってリンクできない

これは、MS-ACCESSの制限事項です。MS-ACCESSは、インデックスが32個より多いテーブルにリンクできません。

この問題を回避する方法として、直接テーブル（クラス）をリンクするのではなく、VIEWを使用する方法があります。

| Back to Top

Question : 114

埋め込みSQL &SQL( select xxx into :var from ... ) としたとき、ホスト変数varに意図しない値が格納される場合があります。

埋め込みSQLのホスト変数(出力)は、SQLCODE=0 (埋め込みSQL正常終了)の場合のみ保証されます。SQLCODEが0以外の場合(該当データがない100やエラー等)で値が設定される場合もあります。埋め込みSQLを使用する場合は、必ずSQLCODEを確認してエラーチェックを行うようにして下さい。

例)
悪い例:

&sql( select name into :name from sample.person where id = 1001 )
If $Get(name)'="" { // <-- SQLCODE=0以外でnameの値は保証されない
Write !,"Name = ", name
} Else {
Write !,"No such person"
}

上記の変更例：

&sql( select name into :name from sample.person where id = 1001 )
If SQLCODE=0 {
Write !,"Name = ", name
} ElseIf SQLCODE=100 {<
Write !,"No such person"
} Else {
Write !,"SQL ERROR: ",SQLCODE
}

参考:

Cache開発ガイド -> Cache SQL の使用法 -> Chapter 9: 埋め込み SQL -> 9.2.4 ホスト変数

http://docs.intersystems.com/cache20082j/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_esql#GSQL_C15273

ホスト変数の値には、以下のような制限事項があります。

入力ホスト変数は、埋め込み SQL の後では有効になりません。
出力ホスト変数は、SQLCODE = 0 のときに埋め込み SQL の後でのみ有効性が保証されます。

| Back to Top

Question : 169

ODBCクライアントで <NOROUTINE> / <NOLINE> エラーを受け取ります。 どうすればいいですか？

こちらのエラーは、多くの場合、クエリキャッシュの管理情報(グローバル ^mcq)とクエリキャッシュ実行ルーチン(CacheSql*)の不整合によって発生します。

Cachéでは、クエリキャッシュ情報を^mcqに格納しますが、何らかの原因で^mcqが示すルーチンがCacheSql*に存在していない、もしくは合致しない場合、これらのエラーが起こります。

これらの問題は、クエリキャッシュを削除することで解決されます。

クエリキャッシュの削除は、システム管理ポータルのSQL > SQLスキーマを参照 > クエリを削除 にて実行できます。

上記を実行しても改善しない場合は、該当ネームスペース上でグローバル ^mcq およびルーチン CacheSql*.* (OBJルーチンを含む)を削除して再度ご確認下さい。

| Back to Top

Caché5.1.x～Question : 188

SQLインジェクション対策はありますか？

SQLインジェクションに関しては、様々なWebサイトで対策等が公開されていますが、Caché SQLを使ったアプリケーションでも、他のRDBMSと同様にそれらの対策を適切に実施することで、SQLインジェクションは、防げると思われます。

さらに、Cachéの場合、一般的なRDBMSに比較して、SQLインジェクションを行ないにくいいくつかの施策が組み込まれています。

1.　Caché SQLでは、一度のリクエストで複数個のSQL文は、実行できませんので、セミコロン（;）の後ろに悪意のあるコマンドを入力時に追加するというテクニックが使えません。

2.　Cachéは、SQLアクセスだけではなく、オブジェクトアクセスも提供しています。

更新処理を、オブジェクトアクセスで実装することで、一般ユーザーに対するSQL文による更新処理を禁止することにより、SQL文のインジェクションによるアプリケーションが予期していないデータベースの改竄を防ぐことができます。

3.　CachéのWeb技術であるCSPでは、urlのクエリー文字列は、全て暗号化できるので、クエリー文字列から、インジェクションの手がかりを得るなどのテクニックが使用しずらいです。

4.　Cachéのセキュリティモデルでは、ユーザーだけではなくアプリケーションにデータベースへのアクセス権限を付与することができます。　そして、ユーザーには、アプリケーションの実行の権限だけを与えて、データベースへの読み書きの権限を制限することができます。

こうすることにより、悪意のあるユーザーがデータベースそのものを直接アクセスする機会をさらに狭めることができます。

| Back to Top

Caché2008.2.x～Question : 190

テーブルのカラム単位にセキュリティをかけることはできますか？

2008.2以降、カラムベースのセキュリティ機能が追加されました。

5.1以降のバージョンでは、テーブル自体のアクセス権限を制限し、かわりにVIEWによるカラムアクセスの制限を行なうことにより、その機能をエミュレーションすることが可能です。

| Back to Top

Question : 210

Extentクエリーの結果が、ID順になりません。

%persistentクラスに既定で生成されるExtentクエリーは、ID順に結果セットを出力することを意図していませんので、これは、仕様通りの動きとなります。

並び順は、設定されているインデックスにより、変わることがあります。

| Back to Top

Caché5.1.x～Question : 214

複数の結果セットを一度に返す方法はありますか？

Cachéは、複数のSQL文を一度に受け付けませんので、SQL文での複数結果セットのサポートはありません。

しかし、ストアドプロシジャを使い、複数結果セットを返すことはできます。

そのサンプルを添付しています。

| Back to Top

Caché2008.2.x～Question : 217

CachéのODBC対応バージョンは、何ですか？

Caché ODBCドライバーは、現時点ではODBC V3.5 APIをサポートしています。

| Back to Top

Caché2008.2.x～Question : 218

CachéのJDBC対応バージョンは何ですか？

現時点(2009.1)で、JDBC V4.0APIに対応しています。

| Back to Top