[pod] [xml]

名前

DBD::Sybase - DBIモジュールのためのSybaseデータベース・ドライバ

概要

    use DBI;
    $dbh = DBI->connect("dbi:Sybase:", $user, $passwd);
    # 詳細に付いてはDBIのドキュメントをご覧下さい

説明

DBD::Sybase はDBIモジュールと一緒に機能し、Sybaseデータベースへの アクセスを提供するPerlモジュールです。

Sybaseへの接続

インターフェース・ファイル

DBD::SybaseモジュールはSybase Open Client Client Library APIの上に 構築されています。このライブラリは論理サーバ名（例えばSYBASE）とサーバが 稼動している物理マシン／ポート番号とを結びつけるために、 Sybaseインターフェース・ファイル（Win32マシンではsql.ini）を使っています。 OpenClientライブラリはインタフェース・ファイルやその他の必要なファイル (localeファイルのような)の場所を見つけるために環境変数SYBASEを使っています。 SYBASE環境変数はSybaseインストールへのパスです（例えば'/usr/local/sybase')。 スクリプトの中で設定する必要があれば、BEGIN{}ブロックで設定しなければ いけません:

   BEGIN {
       $ENV{SYBASE} = '/opt/sybase/11.0.2';
   }
   $dbh = DBI->connect('dbi:Sybase:', $user, $passwd);

サーバ名の指定

DBD::Sybaseが接続するサーバのデフォルトはSYBASEです。 しかし２つの方法で指定することが出来ます。

DSQUERY環境変数で設定することが出来ます:

    $ENV{DSQUERY} = "ENGINEERING";
    $dbh = DBI->connect('dbi:Sybase:', $user, $passwd);

もしくはconnect()の第1引数にサーバ名を渡すことが出来ます：

    $dbh = DBI->connect("dbi:Sybase:server=ENGINEERING", $user, $passwd);

他の接続特有のパラメータの指定

ときどき他の接続プロパティを指定することは、必要（あるいは有益）です。 現在は以下のものがサポートされています:

これらの異なるパラメータは（serverも含めて）、各エントリをセミコロン(;)で 区切ってまとめて文字列にすることができます:

    $dbh = DBI->connect("dbi:Sybase:server=ENGINEERING;packetSize=8192;language=us_english;charset=iso_1",
                        $user, $pwd);

複数の結果セットの取り扱い

SybaseのTransact SQLは１つのSQLステートメントから複数の結果セットを 返すことができます。例えば以下の問い合わせ:

    select b.title, b.author, s.amount
      from books b, sales s
     where s.authorID = b.authorID
     order by b.author, b.title
    compute sum(s.amount) by b.author

これは、著者(author)とタイトル(title)による売上（sales)を一覧表示し、 著者毎の総売上を計算する2種類のタイプの行を返します。DBIの仕様では この状況を実際には扱っていませんし、危険でもありません

    exec my_proc @p1='this', @p2='that', @p3 out

my_procはいかなる数の結果も返すことができとします（つまり数が わからないSELECTステートメントを実行することができます）。

私はこれを各結果セットの最後で空の行を返し、さらに取り出すことが できるデータがあるかどうかをチェックするために見ることができる $sthに特別なSybaseの属性を設定することにより扱うように決めました。 その属性がsyb_more_resultsで、fetch()ループを再開始する 必要があるならば、チェックするべきです。

すべての結果が取り出されたことを保証するには、以下のような 基本的なfetchループを書くことができます:

     do {
         while($d = $sth->fetch) {
            ... データで何かします
         }
     } while($sth->{syb_more_results});

現在の結果セットの型は$sth->{syb_result_type}で取得することが できます。これは$SYBASE/include/cspublic.hで定義されている 数値を返します:

        #define CS_ROW_RESULT           (CS_INT)4040
        #define CS_CURSOR_RESULT        (CS_INT)4041
        #define CS_PARAM_RESULT         (CS_INT)4042
        #define CS_STATUS_RESULT        (CS_INT)4043
        #define CS_MSG_RESULT           (CS_INT)4044
        #define CS_COMPUTE_RESULT       (CS_INT)4045

特にストアド・プロシージャの戻りステータスはCS_STATUS_RESULT (4043)で 返され、通常はストアド・プロシージャの実行で返される最後の 結果セットです。

スクリプトに以下のステートメントを追加すれば、

    use DBD::Sybase;

プログラムの中では、数値の代わりに読み易いシンボルの値（CS_xxx_RESULT）を 使うことができます。

出力パラメータだけを返すストアド・プロシージャの扱いについては syb_output_param func() 呼び出しもご覧ください。

$sth->execute() 失敗モードの動き

これはバージョン0.21で変更されました!

DBD::Sybaseは1個のまとまりで複数ステートメントのSQLコマンドを 扱うことができます。例えば以下のようにいくつかの行を1つにまとめて 挿入することができます:

   $sth = $dbh->prepare("
   insert foo(one, two, three) values(1, 2, 3)
   insert foo(one, two, three) values(4, 5, 6)
   insert foo(one, two, three) values(10, 11, 12)
   insert foo(one, two, three) values(11, 12, 13)
   ");
   $sth->execute;

上記のINSERTのうち1つでも失敗すると、$sth->executeはundefを 返します。しかしながら失敗しなかった挿入は、AutoCommitがオフで なければ、まだデータベースにあります。

以下のようにステートメントを書くこともできます：

   $sth = $dbh->prepare("
   insert foo(one, two, three) values(1, 2, 3)
   select * from bar
   insert foo(one, two, three) values(10, 11, 12)
   ");
   $sth->execute;

2番目のinsertが失敗するものであれば、$sth->executeはundefを 返しません。barから行が取り出された後に、エラーが設定されます。

これが直感的にわかりやすいものではないことはわかっているのですが、 Sybase APIによって制限を受けているのです。

一方、上記の例が合理的でないがわかっています、しかしこの独特の順序を 示す必要があったのです... まさにこの動きを示すt/fail.tテスト・スクリプトを 見ることもできます。

Sybase特有の属性

このドライバ特有のハンドル属性がいくつかあります。これらの属性は 通常のDBI属性とぶつからないよう、すべてsyb_で始まります。

データベース・ハンドル属性

以下のSybase特有の属性をデータベース・ハンドルのレベルで設定することが できます:

ステートメント・ハンドル属性

以下の読込のみの属性がステートメント・レベルで利用することができます:

DATETIME出力フォーマットの制御

デフォルトではDBD::SybaseはDATETIMEとSMALLDATETIME列を Nov 15 1998 11:13AMの形式で返します。これは$dbh->func()メソッドを通して アクセスすることができる、特別な_date_fmt()関数を通じて変更することが できます。

書き方は以下の通り:

    $dbh->func($fmt, '_date_fmt');

$fmtは適用したいフォーマットを表わす文字列です。

フォーマットはSybaseの標準変換ルーチンをベースとしています。利用可能なフォーマット のうち以下のサブセットが実装されています:

ストアド・プロシージャからの出力パラメ−タ取り出し

Sybaseはストアド・プロシージャに出力パラメータの定義を渡すことができます。 これはC（またはPerl）でパラメータをリファレンスで渡すのにちょっと似ています。

Transact-SQLでは以下のようにします

   declare @id_value int, @id_name char(10)
   exec my_proc @name = 'a string', @number = 1234, @id = @id_value OUTPUT, @out_name = @id_name OUTPUT
   -- これで@id_value と @id_nameは'my_proc'が@idと@out_nameに設定した値に設定されます

それではどのようにしてDBD::Sybaseを使って@paramを取り出すのでしょうか？

もしストアド・プロシージャが出力パラメータだけを返すのであれば、 以下のように略して書くことができます。

    $sth = $dbh->prepare('...');
    $sth->execute;
    @results = $sth->func('syb_output_params');

これはそのプロシージャ呼び出しでのすべての出力パラメータのための配列を返し、 他の結果を無視します。何も出力パラメータがないか、何らかの理由でストアド・ プロシージャが失敗すると配列はundefになります。

さらに汎用的な方法は以下のようになります：

   $sth = $dbh->prepare("declare \@id_value int, \@id_name
      exec my_proc @name = 'a string', @number = 1234, @id = @id_value OUTPUT, @out_name = @id_name OUTPUT");
   $sth->execute;
   do {
      while($d = $sth->fetch) {
         if($sth->{syb_result_type} == 4042) { # PARAM結果の場合
            $id_value = $d->[0];
            $id_name  = $d->[1];
         }
      }
   } while($sth->{syb_more_results});

こうすると出力パラメータは特殊な結果セットの１つの行として返されます。

１つの$dbhでの複数のアクティブなステートメント

1つのデータベース・ハンドルで複数のアクティブなステートメントを開くことは 可能です。これは$dbhに既にアクティブなステートメント・ハンドルがあれば $dbh->prepare()の中で新しく物理的な接続を開くことによって実現されます。

この機能は他のドライバとの互換性を高めるために実装されていますが、 Sybaseドライバに直接コーディングしているのであれば、使うべきではありません。

もしAutoCommitがオフであると、1つの$dbhでの複数のステートメント・ハンドルは サポートされません。この状況で持ちあがる様々なデッドロックの問題を避ける ためです。そして異なる物理的な接続を参照しているとき、同時に複数の ステートメント・ハンドルを使っていてはトランザクションとしての本当の 整合性が得られないためです。

IMAGEとTEXT列の作業

DBD::SybaseはIMAGEやTEXTデータ(いわゆる"blob"データ)を標準のSQL ステートメントを通して格納し取り出すことができます。LongReadLenハンドル 属性は各データようそのために返されるIMAGEやTEXTデータの最大サイズを 制御します。

標準のSQLを使うとき、デフォルトではIMAGEデータは16進文字列に変換されます。 しかしsyb_binary_imagesハンドル属性を使って、この動きを変更することが できます。あるいはその変換をおこなうため、以下のようなことをすることも できます

    $binary = pack("H*", $hex_string);

IMAGEとTEXT データ型は?-形式のプレースホルダを使ってパラメータとして 渡すことはできません。そしてプレースホルダはIMAGEやTEXT列を 参照することができません(これはSybaseによって使われているTDSプロトコル の制限であり、DBD::Sybaseの制限ではありません。）

IMAGE/TEXTデータにアクセスし更新するもう一つの方法としては、 natice OpenClient APIを使うことがあります。これは$h->func()を 通して行われ、残念ながら少し複雑です。

ct_get_data()/ct_send_data()によるIMAGE/TEXTデータの取り扱い

AutoCommit、トランザクションそしてTransact-SQL

$h->{AutoCommit}がオフのとき、あなたが発行するデータを変更する すべてのSQLステートメント（INSERT/UPDATE/DELETE)は、$dbh->commitを 呼び出したときにだけ有効になります。

DBD::Sybaseは$h->{syb_chaind_txn}属性とアクセスされているサーバ のバージョンによって、これを２つの違った方法を通して実装しています。

$h->{syb_chained_txn}がオフであれば、DBD::Sybaseドライバは 最初の$dbh->prepareの前、そして$dbh->commit()または$dbh->rollback()の 後に、BEGIN TRANを送信します。これはうまく機能しますが、 CREATE TABLE（または他のDDL）ステートメントが入っていると うまくいきません。これらのCREATE TABLEステートメントは ストアド・プロシージャのどこか（例えばsp_helprotectは走るときに、 ２つの一時テーブルを作成します）に、埋め込まれていることがあります。 この制限はddl in tranオプション（データベース・レベルで。 sp_dboptionを通して）を設定することで回避することが出来ます。 これはトランザクションの間ずっとあるシステム・テーブルに対してロックを かけるために重大な影響を与えるかもしれないということに注意しなければ いけません。

もし$h->{syb_chained_txn} がオンであれば、DBD::SybaseはCHAINED オプションを設定します。これはSybaseが何も自動的にはコミットしない ということを示します。再びデータの変更を永続化するためには$dbh->commit()を 呼び出す必要があります。この場合、Sybaseは実行されるSQLコード中で BEGIN TRANステートメントを発行させません。そこでBEGIN TRAN ステートメントが入ったストアド・プロシージャを実行する必要があれば、 $h->{syb_chained_txn} = 0または $h->{AutoCommit} = 1としなければなりません。

?プレースホルダと$sth->executeへのバインド・パラメータの使い方

元になっているライブラリとデータベース･エンジンがそれをサポートしている 限り、DBD::SybaseはSQLステートメントの中での?プレースホルダの利用を サポートしています。これはSybaseがDynamic SQLと読んでいるものを使う ことによって行っています。?プレースホルダは以下のように書くことを可能に しています:

        $sth = $dbh->prepare("select * from employee where empno = ?");
        # employeeからempno == 1024である行を取り出します:
        $sth->execute(1024);
        while($data = $sth->fetch) {
            print "@$data\n";
        }
       # 今度はempno = 2000である行を取り出します:
        
        $sth->execute(2000);
        while($data = $sth->fetch) {
            print "@$data\n";
        }

?プレースホルダを使うとSybaseは処理を進め、SQLステートメントに対応する 一時的なストアド・プロシージャを作成します。そこで問い合わせに組み込まれる $sth->executeや$dbh->doに変数を渡し、いくつかの行が返されます。

DBD::Sybaseは?-形式のプレースホルダを扱うために、元になっている Sybase APIを使用します。select/insert/update/deleteステートメントの ため、DBD::SybaseはClient Library関数のct_dynamic()ファミリを呼び出します。 これはDBD::Sybaseに問い合わせへの各パラメータのデータ型の情報を与えます。

1つの結果セットを返すステートメントのためにだけ?-形式のプレースホルダを 使うことができます。そして?プレースホルダはWHERE節、UPDATEステートメントの SET節、あるいはINSERTステートメントのVALUES節にのみ書くことができます。

DBIドキュメントではNULL値とプレースホルダに関しては以下のように言っています:

これはSybaseデータベース・サーバーではうまくいきません。上記のことを 試すと以下のエラーを取得するでしょう:

この特殊な問題は?プレースホルダを使うとき、パラメータの解決のため prepare()操作がデータベース・サーバーに送信されることです。これは 各プレースホルダのためのデータ型を取り出します。残念ながら? is nullという書き方は ?プレースホルダを既存のテーブル列に結び付けません。そのためデータベース・サーバは データ型を見つけることが出来ません。この一連の処理はSybaseライブラリの 内側で起きるため、DBD::Sybaseには回避することができません。

Sybaseは他のシステムがfoo = NULLという書き方を扱うのと同じ方法で、通常通り foo = NULLという書き方を扱うということに注意してください。そこでSybase データベースに問い合わせるとき、正しい結果を取得するため上記のような複雑な 書き方は必要ありません。

元になっているAPIはストアド・プロシージャのためには?-形式のプレースホルダを サポートしていません。しかしこのドキュメントのどこかにある ストアド・プロシージャとプレースホルダというタイトルのセクションをごらんください。

?-styleプレースホルダはTEXTやIMAGEデータ項目をサーバに渡すためには 使えません。これはTDSプロトコルの制約であり、DBD::Sybaseのものでは ありません。

パフォーマンスの問題もあります：OpenClientは?プレースホルダがある prepare()呼び出しのたびにストアド・プロシージャをtempdbに作成します。 これらのオブジェクトを生成することはtempdbデータベースのなかの システム・テーブルを更新する必要があります。このため同時に実行される 複数のクライアントからの数多くのprepare()ステートメントは パフォーマンス・ホットスポットを作成するかもしれません。 Sybase11.9.x以降では、サーバ・メモリ・キャッシュに保持され、システム・テーブルに 全く影響を与えない"軽量の"一時的なストアド・プロシージャを作成するので、 この問題は修正されています。

しかしながら、あなたのアプリケーションがSybaseに対して走るのであれば、 通常は埋め込まれたSQLでの？プレースホルダを使うよりも、 アプリケーションがSybaseに特定のプロシージャを書きこむほうが 良好であることがわかりました。

好奇心から、私は、?プレースホルダをprepareするときのオーバーヘッドが 何かを見るためそのままのSQLのprepareやストアド・プロシージャのprepareと 比較し、いくつかの簡単な時間調整を行いました。11.0.3.3サーバー(linux)に 対して、プレールホルダのprepareは著しく遅く、オーバーヘッドを超えるためには prepareされたステートメントを30回する必要があります。12.0 サーバー(solaris) に対しては、プレースホルダのprepre()は少しだけそのままのSQLよりも 早くなります。これは私が本当にわからないことです。しかし数字は 非常にはっきりしています。

すべてのケースでストアド・プロシージャのprepare()呼び出しが 明らかにそして一貫して早く走りました。

しかしながらこのテストでは並行の問題を計測しようとしていません。

?形式のプレースホルダが入ったinsertを行った後、最後のIDENTITYの値を 取り出すことはできません。これはSybaseの制限/バグであり、DBD::Sybaseの 問題ではありません。例えばテーブルfooがidentity列をもっていると:

  $dbh->do("insert foo(col1, col2) values(?, ?)", undef, "string1", "string2");
  $sth = $dbh->prepare('select @@identity') 
    || die "Can't prepare the SQL statement: $DBI::errstr";
  $sth->execute || die "Can't execute the SQL statement: $DBI::errstr";
  #データを取り戻し
  while (my $row = $sth->fetchrow_arrayref()) {
    print "IDENTITY value = $row->[0]\n";
  }

これは常にidentityの値として0を返します。これは明らかに間違いです。 この動きは?形式のプレースホルダの取り扱いが、そのストアド・プロシージャが 実行されたとき一時的に格納されたプロシージャを使って実装されているためです。

明示的にinsertしたり、実行された後@@identityを取り出そうとする ストアド・プロシージャを使っても同じ動きになってしまいます。

Dynamic SQLについての詳細についてはOpenClient C プログラマーズガイド (OpenClient C Programmer's Guide)をご覧ください。ガイドはオン・ラインでも 見ることができます。 http://sybooks.sybase.com/

ストアド・プロシージャとプレースホルダ

注意: この機能は実験的です

DBD::Sybaseのこのバージョンでは?形式のプレースホルダをストアド・プロシージャ 呼び出しへのパラメータとして使う機能を導入しています。必要なことは そのストアド・プロシージャコールが"exec"で始まること、そして prepere()されているバッチにあるステートメントであることです:

例えば、これはストアド・プロシージャ呼び出しを名前付きパラメータでprepareしています:

    my $sth = $dbh->prepare("exec my_proc \@p1 = ?, \@p2 = ?");
    $sth->execute('one', 'two');

位置によるパラメータも使うことができます:

    my $sth = $dbh->prepare("exec my_proc ?, ?");
    $sth->execute('one', 'two');

同じprepareの中で位置によるパラメータと名前付きのパラメータを混在させてはいけません。

通常の方法でOUTPUTパラメータを指定することができます。しかし出力結果を 取得するためにbind_param_inout()を使うことは出来ません - 代わりに fetch() そして/あるいは $sth->func('syb_output_params')を呼び出す必要があります:

    my $sth = $dbh->prepare("exec my_proc \@p1 = ?, \@p2 = ?, \@p3 = ? OUTPUT ");
    $sth->execute('one', 'two', 'three');
    my (@data) = $sth->func('syb_output_params');

DBD::Sybaseは各パラメータについて正しいパラメータの型を理解しようとは しません(ほとんどのケースでこれは可能でしょう。しかし 今のところ私がその問題を避けようとするほどに十分な例外もあります)。 DBD::SybaseはデフォルトではパラメータをSQL_CHARとしてます。そして 明確な型の値でbind_param()を使って、これを何か違うものに設定しなければ なりません。型はそのとき覚えられます。そのため各パラメータについて 明示的な呼び出しを一度だけ使う必要があります:

    my $sth = $dbh->prepare("exec my_proc \@p1 = ?, \@p2 = ?");
    $sth->bind_param(1, 'one', SQL_CHAR);
    $sth->bind_param(2, 2.34, SQL_FLOAT);
    $sth->execute;
    ....
    $sth->execute('two', 3.456);
    etc...

SQL_NUMERICやSQL_DECIMALデータをバインドするとき、桁数や精度が 目標のパラメータ定義を超えると致命的な変換エラーになるかもしれません。

例えば、以下のストアド・プロシージャの定義について考えてみましょう:

    declare proc my_proc @p1 numeric(5,2) as...

そして以下のprepare/executeの一部について考えてください:

    my $sth = $dbh->prepare("exec my_proc \@p1 = ?");
    $sth->bind_param(1, 3.456, SQL_NUMERIC);

これは以下のエラーを起こします:

DBD::Sybase::st execute failed: Server message number=241 severity=16 state=2 line=0 procedure=dbitest text=Scale error during implicit conversion of NUMERIC value '3.456' to a NUMERIC field. (参考: DBD::Sybase::st execute failed: Server message number=241 severity=16 state=2 line=0 procedure=dbitest text=数値'3.456'のNUMERICフィールドへの暗黙の変換で桁数エラー。)

あなたはSybase(そしてDBD::Sybase)に、arithabortオプションを設定することにより、 この種のエラーを無視するように伝えることができます:

    $dbh->do("set arithabort off");

setコマンドとarithabortオプションについてのより多くの情報については Sybase Adaptive Server Enterprise Reference Manualでのsetコマンドを ご覧ください。

バグ

接続を閉じることなしに、バインド変数を持った呼び出し（つまり?形式の プレースホルダ）をたくさん行うとtempdbデータベースの領域が足りなくなって しまうかもしれません。私のシステムではtempdbデータベースは8MBあり、 ?パラメータを持つ760回のprepare()ステートメントで領域が一杯になりました。 これはSybaseがprepare()呼び出しのたびに、ストアド・プロシージャを 作成するためです。そこで本当に必要なときにだけ(つまりprepareされた同じ ステートメントを何回も実行するとき）、?形式のプレースホルダを使うことを 提案します。

新しいprimary_key_info()メソッドは、テーブルを作成したときに宣言での "プライマリ・キー"制約が入っているテーブルのためだけにデータを返します。

私は簡単なバグ・トラッキングデータベースを http://gw.peppler.org/cgi-bin/bug.cgi に 持っています。既にわかっているバグを見たり、新しいものを報告するために使うことが できます。peppler.orgはK56ダイアルアップ回線を介してネットにつながっていることに 気をつけてください。このために遅いかもしれません。

MS-SQLでのDBD::Sybaseの利用

MS-SQLはSybase 4.2として始まりました。そしてSybaseとMS-SQLの間には 多くの類似点があります。そのためSybase OpenClientライブラリあるいは FreeTDSライブラリ(http://www.freetds.orgを参照)のいずれかを使って MS-SQLデータサーバへの問い合わせを可能になっています。

しかしSybaseライブラリをMS-SQLサーバへの問い合わせのために使うことには いくらか制限があります。特に?形式のプレースホルダはサポートされていません (FreeTDSライブラリを使っている場合、そのライブラリの将来のリリースでは サポートされますが）。そしていくつかのsyb_属性はサポートされていない かもしれません。

SybaseはTEXTSIZE属性(いわゆるLongReadLen)のデフォルトを32Kに しています。しかしMS-SQL7は正しくそうしてはいないようです。 その結果、TEXT/IMAGEデータ列を持っているテーブルに問い合わせるとき 大量のメモリの要求を引き起こします。回避するためには$dbh->{LongReadLen}を 通して(もしそれがうまくいったら - 私は確認をしていません) あるいは $dbh->do("set textsize <somesize>");を通してTEXTSIZEを何らかの きちんとした値に設定してください。

nsql

nsql()呼び出しはSybase::DBlibにある同じ名前の関数をそのまま 移植したものです。

利用方法:

   @data = $dbh->func($sql, $type, $callback, 'nsql');

これは$sqlに入っている問い合わせを実行し、@dataに全てのデータを返します。 $typeパラメータは配列形式で(つまり'ARRAY'で$typeが渡します。これが デフォルトです)あるいは列名をキーとしたハッシュ形式で($typeが'HASH'で 渡します)各行が返されることを指定することができます。

もし$callbackが指定されると、それはperl subへのリファレンスとして取られ、 問い合わせにより返される各行が、そのルーチンによって返される代わりに このサブルーチンに渡されます(例えば大きな結果セットを処理することが できます)。

nsqlはデッドロック・リトライ・ロジックを可能にするため3つの特別な属性も チェックします (注意 これらの属性はいずれも、現時点では他のどこにも 何も影響を与えません):

デッドロックの検知はDBD::Sybaseの将来のバージョンでは$dbh->do()メソッドに 追加されるでしょう。

参考資料

DBI

Sybase OpenClient C マニュアル

Sybase Transact SQL マニュアル

作者(=AUTHOR)

DBD::Sybase by Michael Peppler

著作権(=COPYRIGHT)

The DBD::Sybase module is Copyright (c) 1997-2003 Michael Peppler. The DBD::Sybase module is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

謝辞

DBIについてTim Bunceへ、心から!

DBI/ACKNOWLEDGEMENTSもご覧ください。