ストリーム・データ (BLOB と CLOB) の格納と使用

BLOB と CLOB

InterSystems SQL には、データベースに BLOB (Binary Large Object) や CLOB (Character Large Object) をストリーム・オブジェクトとして保存する機能があります。BLOB は、イメージなどのバイナリ情報の保存に、CLOB は文字情報の保存に使用します。BLOB と CLOB は、最大 4 ギガバイトのデータを保存できます (この制限は JDBC と ODBC の仕様により決められています)。

BLOB と CLOB の処理方法はほぼ同じですが、ODBC または JDBC クライアントからアクセスされたときに、文字エンコード変換を処理する方法 (Unicode からマルチバイト文字への変換など) のみ異なります。BLOB のデータはバイナリ・データとして処理され、他の文字コードに変換されませんが、CLOB のデータは文字データとして処理され、必要に応じて変換されます。

バイナリ・ストリーム・ファイル (BLOB) に 1 つの出力不能文字 $CHAR(0) が含まれる場合、これは空のバイナリ・ストリームと見なされます。これは、空のバイナリ・ストリーム値 "" と同じです。存在はしていますが (NULL ではありませんが)、その長さは 0 です。

オブジェクトの観点から見ると、BLOB と CLOB は ストリーム・オブジェクトと見なされます。詳細は、"クラスの定義と使用" の “ストリームを使用した作業” の章を参照してください。

ストリーム・データ・フィールドの定義

InterSystems SQL では、ストリーム・フィールドにさまざまなデータ型名をサポートしています。これらの InterSystems のデータ型名は、以下に対応する同義語です。

• 文字ストリーム ： データ型 LONGVARCHAR。%Stream.GlobalCharacterOpens in a new tab クラスと ODBC/JDBC データ型 -1 にマップされます。

• 文字ストリーム ： データ型 LONGVARBINARY。%Stream.GlobalBinaryOpens in a new tab クラスと ODBC/JDBC データ型 -4 にマップされます。

一部の InterSystems ストリーム・データ型では、データ精度値を指定できます。この値は空命令で、許容されたサイズのストリーム・データには影響を与えません。予期されるサイズの将来のデータをドキュメント化するために提供されています。

ストリーム・データ型のデータ型マッピングについては、"InterSystems SQL リファレンス" の "データ型" リファレンス・ページを参照してください。

テーブル (永続クラス) のフィールド (プロパティ) を定義する方法については、"永続クラスの作成によるテーブルの定義" および "DDL を使用したテーブルの定義" を参照してください。永続クラスのストリーム・プロパティを定義する際、オプションで LOCATION パラメータを指定できます。"クラスの定義と使用" の “ストリームを使用した作業” の章の "ストリーム・プロパティの宣言" を参照してください。

以下の例では、2 つのストリーム・フィールドを含むテーブルを定義します。

CREATE TABLE Sample.MyTable (
    Name VARCHAR(50) NOT NULL,
    Notes LONGVARCHAR,
    Photo LONGVARBINARY)

ストリーム・データ・フィールドへのデータの挿入

ストリーム・フィールドにデータを INSERT するには、以下の 3 つの方法があります。

• %Stream.GlobalCharacterOpens in a new tab フィールド ： 文字ストリーム・データを直接挿入できます。以下に例を示します。

  INSERT INTO Sample.MyTable (Name,Notes)
      VALUES ('Fred','These are extensive notes about Fred')

  

• %Stream.GlobalCharacterOpens in a new tab および %Stream.GlobalBinaryOpens in a new tab フィールド ： OREF を使用して、ストリーム・データを挿入できます。Write()Opens in a new tab メソッドを使用して文字列を文字ストリームに追加するか、WriteLine()Opens in a new tab メソッドを使用して文字列と行ターミネータを文字ストリームに追加します。既定では、行ターミネータは $CHAR(13,10) (キャリッジ・リターン/改行) です。LineTerminator プロパティを設定して、この行ターミネータを変更できます。次の例の最初の部分では、2 つの文字列とそのターミネータで構成される文字ストリームを作成し、埋め込み SQL を使用してストリーム・フィールドに挿入します。この例の 2 つ目の部分では、文字ストリーム長を返して、ターミネータを示す文字ストリーム・データを表示します。

  CreateAndInsertCharacterStream
    SET gcoref=##class(%Stream.GlobalCharacter).%New()
    DO gcoref.WriteLine("First Line")
    DO gcoref.WriteLine("Second Line")
    &sql(INSERT INTO Sample.MyTable (Name,Notes)
      VALUES ('Fred',:gcoref))
         IF SQLCODE<0 {WRITE "SQLCODE ERROR:"_SQLCODE_" "_%msg  QUIT}
         ELSE {WRITE "Insert successful",!}
  DisplayTheCharacterStream
    KILL ^CacheStream
    WRITE gcoref.%Save(),!
    ZWRITE ^CacheStream

  

• %Stream.GlobalCharacterOpens in a new tab および %Stream.GlobalBinaryOpens in a new tab フィールド ： ファイルから読み取ることで、ストリーム・データを挿入できます。以下に例を示します。

    SET myf="C:\InterSystems\IRIS\mgr\temp\IMG_0190.JPG"
    OPEN myf:("RF"):10
    USE myf:0
    READ x(1):10
    &sql(INSERT INTO Sample.MyTable (Name,Photo) VALUES ('George',:x(1)))
         IF SQLCODE<0 {WRITE "INSERT Failed:"_SQLCODE_" "_%msg  QUIT}
         ELSE {WRITE "INSERT successful",!}
    CLOSE myf

  

  詳細は、"入出力デバイス・ガイド" の "シーケンシャル・ファイルの入出力" を参照してください。

DEFAULT 値または算出値として挿入される文字列データは、ストリーム・フィールドに適した形式で格納されます。

ストリーム・フィールド・データのクエリ

クエリ select-item は、以下の例に示すように、ストリーム・フィールドを選択し、ストリーム・オブジェクトの整形式 OID (オブジェクト ID) 値を返します。

SELECT Name,Photo,Notes 
FROM Sample.MyTable WHERE Photo IS NOT NULL

OID は、$lb("1","%Stream.GlobalCharacter","^EW3K.Cn9X.S") のような %List 形式のデータ・アドレスです。

• OID の最初の要素は、テーブルに挿入された各ストリーム・データ値に割り当てられる、連続する正の整数 (1 で始まる) です。例えば、行 1 にストリーム・フィールド Photo および Notes の値が挿入される場合、これらには 1 と 2 が割り当てられます。行 2 に Notes の値が挿入される場合、3 が割り当てられます。行 3 に Photo および Notes の値が挿入される場合、これらには 4 と 5 が割り当てられます。割り当て順序は、INSERT コマンドで指定されている順序ではなく、テーブル定義でフィールドがリストされている順序です。既定では、ストリーム位置のグローバル・カウンタに対応する、単一の整数シーケンスが使用されます。ただし、以下に説明するように、テーブルに複数のストリーム・カウンタが存在することがあります。

  UPDATE 操作では、初期整数値は変更されません。DELETE 操作では、整数シーケンスにギャップが生じる可能性がありますが、これらの整数値は変更されません。DELETE を使用してすべてのレコードを削除しても、この整数カウンタはリセットされません。すべてのテーブル・ストリーム・フィールドで既定の StreamLocation 値が使用されている場合、TRUNCATE TABLE を使用してすべてのレコードを削除すると、この整数カウンタはリセットされます。TRUNCATE TABLE を使用して、埋め込みオブジェクト (%SerialObject) クラスのストリーム整数カウンタをリセットすることはできません。

• OID の 2 番目の要素はストリーム・データ型 (%Stream.GlobalCharacter または %Stream.GlobalBinary) です。

• OID の 3 番目の要素はグローバル変数です。既定では、その名前はパッケージ名およびテーブルに対応する永続クラス名から生成されます。“S” (ストリームの場合) が追加されます。

  • テーブルが SQL CREATE TABLE コマンドを使用して作成された場合、これらのパッケージ名および永続クラス名はそれぞれ 4 つの文字にハッシュ化されます (例 ： ^EW3K.Cn9X.S)。このグローバルには、最後に割り当てられたストリーム・データ挿入カウンタの値が含まれます。ストリーム・フィールド・データが挿入されていない場合、または TRUNCATE TABLE を使用してすべてのテーブル・データが削除されている場合、このグローバルは定義されません。

  • テーブルが永続クラスとして作成された場合、これらのパッケージ名および永続クラス名はハッシュ化されません (例 ： ^Sample.MyTableS)。既定では、これは StreamLocation ストレージ・キーワード <StreamLocation>^Sample.MyTableS</StreamLocation> の値です。

    既定のストリーム位置は、^Sample.MyTableS などのグローバルです。このグローバルを使用して、カスタム LOCATION のない、すべてのストリーム・プロパティ (フィールド) への挿入をカウントします。例えば、Sample.MyTable 内のすべてのストリーム・プロパティが既定のストリーム位置を使用するとします。Sample.MyTable のストリーム・プロパティに 10 個のストリーム・データ値が挿入されている場合、^Sample.MyTableS グローバルには値 10 が含まれます。このグローバルには、最後に割り当てられたストリーム・データ挿入カウンタの値が含まれます。ストリーム・フィールド・データが挿入されていない場合、または TRUNCATE TABLE を使用してすべてのテーブル・データが削除されている場合、このグローバルは定義されません。

    ストリーム・フィールド・プロパティを定義する際、次のようなカスタム LOCATION を定義できます ： Property Note2 As %Stream.GlobalCharacter (LOCATION="^MyCustomGlobalS");。この状況では、^MyCustomGlobalS グローバルは、この LOCATION を指定するストリーム・プロパティのストリーム・データ挿入カウンタとして機能します。LOCATION を指定しないストリーム・プロパティは、既定のストリーム位置グローバル (^Sample.MyTableS) をストリーム・データ挿入カウンタとして使用します。各グローバルは、その位置に関連付けられているストリーム・プロパティの挿入をカウントします。ストリーム・フィールド・データが挿入されていない場合、位置グローバルは定義されません。1 つまたは複数のストリーム・プロパティが LOCATION を定義している場合、TRUNCATE TABLE はストリーム・カウンタをリセットしません。

  これらのストリーム位置グローバル変数の添え字には、各ストリーム・フィールドのデータが含まれます。例えば、^EW3K.Cn9X.S(3) は 3 番目に挿入されたストリーム・データ項目を表します。^EW3K.Cn9X.S(3,0) はデータの長さです。^EW3K.Cn9X.S(3,1) は実際のストリーム・データ値です。

クエリの WHERE 節または HAVING 節でのストリーム・フィールドの使用は、厳しく制限されます。ストリーム・フィールドで等値条件またはその他の関係演算子 (=、!=、<、>)、包含関係演算子 ( ] ) または後続関係演算子 ( [ ) を使用することはできません。ストリーム・フィールドでこれらの演算子を使用しようとすると、SQLCODE -313 エラーが返されます。ストリーム・フィールドを使用する有効な述語については、"述語条件とストリーム" を参照してください。

DISTINCT、GROUP BY、および ORDER BY

データそのものに重複が含まれる場合でも、各ストリーム・データ・フィールドの OID 値は一意です。これらの SELECT 節は、データ値ではなくストリーム OID 値に対して機能します。このため、クエリでストリーム・フィールドに適用された場合、以下のようになります。

• DISTINCT 節は、重複するストリーム・データ値には影響を与えません。DISTINCT 節は、ストリーム・フィールドが NULL のレコード数を、1 つの NULL レコードに削減します。

• GROUP BY 節は、重複するストリーム・データ値には影響を与えません。GROUP BY 節は、ストリーム・フィールドが NULL のレコード数を、1 つの NULL レコードに削減します。

• ORDER BY 節は、ストリーム・データ値をデータ値ではなく OID 値で並べます。ORDER BY 節は、ストリーム・フィールド・データ値を持つレコードをリストする前に、ストリーム・フィールドが NULL のレコードをリストします。

述語条件とストリーム

IS [NOT] NULL 述語は、以下の例に示すように、ストリーム・フィールドのデータ値に適用可能です。

SELECT Name,Notes 
FROM Sample.MyTable WHERE Notes IS NOT NULL

BETWEEN、EXISTS、IN、%INLIST、LIKE、%MATCHES、および %PATTERN 述語は、以下の例に示すようにストリーム・オブジェクトの OID 値に適用可能です。

SELECT Name,Notes 
FROM Sample.MyTable WHERE Notes %MATCHES '*1[0-9]*GlobalChar*'

ストリーム・フィールドでその他の述語条件を使用しようとすると、SQLCODE -313 エラーが返されます。

集約関数とストリーム

COUNT 集約関数は、以下の例に示すように、ストリーム・フィールドを取得し、フィールドに NULL でない値を含む行をカウントします。

SELECT COUNT(Photo) AS PicRows,COUNT(Notes) AS NoteRows
FROM Sample.MyTable

ただし、COUNT(DISTINCT) はストリーム・フィールドではサポートされません。

その他の集約関数は、ストリーム・フィールドではサポートされません。ストリーム・フィールドをその他の集約関数で使用しようとすると、SQLCODE -37 エラーが返されます。

スカラ関数とストリーム

InterSystems SQL では、関数をストリーム・フィールドに適用できません。ただし、%OBJECT、CHARACTER_LENGTH (または CHAR_LENGTH または DATALENGTH)、SUBSTRING、CONVERT、XMLCONCAT、XMLELEMENT、XMLFOREST、および %INTERNAL 関数は例外です。ストリーム・フィールドをその他の SQL 関数で引数として使用しようとすると、SQLCODE -37 エラーが返されます。

• %OBJECT 関数は、以下の例に示すように、ストリーム・オブジェクトを開き (OID を取得し)、oref (オブジェクト参照) を返します。

  SELECT Name,Notes,%OBJECT(Notes) AS NotesOref
  FROM Sample.MyTable WHERE Notes IS NOT NULL

  

• CHARACTER_LENGTH 関数、CHAR_LENGTH 関数、および DATALENGTH 関数は、以下の例に示すように、ストリーム・フィールドを取得し、実際のデータ長を返します。

  SELECT Name,DATALENGTH(Notes) AS NotesNumChars
  FROM Sample.MyTable WHERE Notes IS NOT NULL

  

• SUBSTRING 関数は、以下の例に示すように、ストリーム・フィールドを取得し、ストリーム・フィールドの実際のデータ値の指定された部分文字列を返します。

  SELECT Name,SUBSTRING(Notes,1,10) AS Notes1st10Chars
  FROM Sample.MyTable WHERE Notes IS NOT NULL

  

  管理ポータルの SQL 実行インタフェースから発行された場合、SUBSTRING 関数は最大 100 文字のストリーム・フィールド・データの部分文字列を返します。ストリーム・データの指定された部分文字列が 100 文字より長い場合、これは 100 番目の文字の後の省略記号 (...) で示されます。

• CONVERT 関数を使用して、以下の例に示すように、ストリーム・データ型を VARCHAR に変換できます。

  SELECT Name,CONVERT(VARCHAR(100),Notes) AS NotesTextAsStr
  FROM Sample.MyTable WHERE Notes IS NOT NULL

  

  CONVERT(datatype,expression) 構文は、ストリーム・データの変換をサポートします。VARCHAR の精度が実際のストリーム・データの長さより小さい場合、返り値を VARCHAR の精度まで切り捨てます。VARCHAR の精度が実際のストリーム・データの長さより大きい場合、返り値は実際のストリーム・データの長さになります。埋め込みは行われません。

  {fn CONVERT(expression,datatype)} 構文はストリーム・データの変換をサポートしていません。SQLCODE -37 エラーが返されます。

• %INTERNAL 関数は、ストリーム・フィールドに使用できますが、演算を実行しません。