システム奮闘記：その56

ODBC経由でLinuxとAS400の連動に成功!



Tweet

(2006年12月17日に掲載) 
機会は突然やってきた!!

　「システム奮闘記:その51」(ODBC経由のAS400(iSeries,i5)とLinuxの連動)で
AS400とLinuxの連動を試みたものの、全くできなかったため断念してしまった。

  このまま永久に不可能かと思ったが、2006年8月に急展開する。

  なんとAS400の買い替えが決まったのだ！

  なにせ、今まで使っていた機種は、1996年に購入した物で
処理速度は遅いし、老朽化も進んでいる事情があった。
  そのため以前から、上司が上層部にAS400の買い替えの必要性を訴えていた。
  上司の訴えが通って、AS400の買い替えになった。


  もし、新しい機種にSQL開発キットが付いていれば、ODBCを経由した
AS400とLinuxの連動が可能かもしれない。

  しかし、SQL開発キットは別途購入の必要があった。
  普段なら、そこで断念となるのだが、SQL開発キットのお試し版が
70日間使えるという。

  よっしゃ、ええ機会や！

  もし、AS400とLinuxとの連動が可能になった事で、より便利なシステムが
構築できる事を示せば、SQL開発キットの稟議を挙げやすいし、
稟議そのものも通りやすくなる。
お試し版はありがたい
お試し版の存在はありがたいと感じる。
導入前に机上の想定で「これだけの利点がありますよ」とか
「これだけの費用対効果がありますよ」と言っても
上層部が納得してくれないと、どんなに良い物でも稟議が通れない。

それに、導入した後で「こんな便利な事ができるのか」と気づく事もある。
その意味では、お試し版で使ってみて判断できるのは助かる。


  ワクワクする。
  AS400の新しい機種が到着する。
  だが、届いたままのAS400はタダの箱で、自力では設定できない。
  ベンダーの技術者が設定するまで触れない。

  技術者がAS400の設定するまで、我慢する事になる。

  その間に、Linux側で連動実験の準備を行う。

  実験方法は既にわかっている。なにせ、悪戦苦闘した上、その記録は
「システム奮闘記：その51」にまとめている。
  なので、自分で書いたシステム奮闘記を見ながら作業を進める。
  
  まずは、パソコンにRedHat9.0 にインストールする。
  そして、Apache + PHP + ODBC を実現するために、Apacheをインストール。
  ソースコンパイルが好きな私は、ソースコンパイルを行う。
configureのオプション
[suga@server]% ./configure --enable-module=so 
赤い部分は、Apacheが動的にモジュールを取り込めるようにするための
オプションです。


  そして、ODBCのインストール。
  unixODBCというソフトを使うのだが、ここでソースコンパイルを行うと
問題が発生する。
  AS400への接続のドライバがRPMなので、unixODBCもRPMで入れておかないと
RPMの機能の、ライブラリ依存の検知でエラーが出る。
エラーの内容
[root@server]# rpm -ihv iSeriesAccess-5.4.0-1.0.i386.rpm  
エラー: Failed dependencies:
        libodbcinst.so.1 is needed by iSeriesAccess-5.4.0-1.0
        libodbc.so.1 is needed by iSeriesAccess-5.4.0-1.0


  もし、知識があれば、上手にunixODBCをソースコンパイルして、
その後で、ODBCのドライバをRPMでインストールできるかもしれないが、
事務員の私には、そんな知識はないので、unixODBCはRPMを使う。

  (注意)
  実際には、unixODBCをソースコンパイルしても、問題なく
  AS400のODBCドライバはインストールは可能です。
  その方法は後述しています。


  RedHat9.0の場合、3枚目のCD-ROMに、unixODBCのRPMが入っているので
以下のソフトをインストールする。
CD-ROMからインストールしたunixODBCに関するソフト
unixODBC-2.2.3-6.i386.rpm
unixODBC-devel-2.2.3-6.i386.rpm
unixODBC-kde-2.2.3-6.i386.rpm
qt-ODBC-3.1.1-6.i386.rpm

赤い部分は、unixODBCの本体。
青い部分は、unixODBCのヘッダーファイル等が入っている。
この後、PHPのインストールの際、ソースコンパイルで行う場合は
これも絶対に入れておく必要がある。

残りは、GUIでODBCドライバの設定を行うためのファイルのようだ。


  次に、PHPを入れる。
  PHP4を触っている私なので、PHP4を入れる。
  ここも好みを優先させてソースコンパイルを行う。
  configureのオプションを書きました。
configureのオプション
[suga@server]% ./configure --with-apxs=/usr/local/apache/bin/apxs --with-unixODBC --enable-mbstring 
青い部分は、PHP4をApacheのモジュールとして取り込むためのオプション。
ディレクトリの指定は、Apache側にあるモジュールとして取り込むための
ツール(apxs)の場所を指している。
Apacheをソースコンパイルした場合、/usr/local/apacheの中に
Apache関係のファイルが格納される。

赤い部分は、unixODBCと連動させるためのオプション。

残りは、バルチバイトの処理を可能にするオプション。
つまり、日本語処理を可能にするためのオプションだ。


  そして、IBMのホームページから、LinuxからAS400へODBC接続を行うための
ライブラリをダウンロードする。
ODBCのライブラリが入ったファイル名
iSeriesAccess-5.2.0-1.14.i386.rpm

IBMのダウンロードのページのURL
http://www-03.ibm.com/servers/eserver/iseries/access/linux/


  ダウンロードしたRPMファイルをインストールする。

  そして、ODBCドライバの接続のための設定を行う。
  odbc.iniファイルを手で記述するのも可能だが、私はGUIを使う。
  ODBCConfigコマンドを使えば可能だ。
ODBCの設定
AS400用のドライバのODBCの設定画面

　ODBCの設定の詳しい事については「システム奮闘記：その51」
(ODBCを利用したAS400とLinuxの連動「iSeries Access for Linux」に挑戦)
をご覧ください。

  準備完了。あとはお試し版が稼働するのを待つばかりだ。

  遠足を前日に控えた小学生と同様に、ワクワクした気分で
夜も眠れないくらいだ (実際は、ぐっすり寝ています)


  2006年10月4日、ベンダーの技術者の方が来た。
  既存のAS400からデータの移行などを行う際に、SQL開発キットのお試し版の
起動もしてもらう事にした。

  そして、ついにSQL開発キットが起動した！

  ワクワクする私。
  早速、isql コマンドを使って、使えるかどうか試してみる。
  だが・・・

  なんで、エラーが出んねん （ＴＴ）
エラーの内容
[suga@server]% isql AS400
[ISQL]ERROR: Could not SQLConnect
[suga@server]% 


  設定を見ても原因がわからない。
  ベンダーの技術者が「こちらで必要な事があれば、調べてみますので
その時は、おっしゃってください」と言った。

  なんでか首を傾げたが、ふと思った。

  通信状態を見てみよう！

  そこで、登場するのが、 tcpdump コマンドだ。
  isqlコマンドを使っている間に、パケット情報を取得する事にした。
  そして、その情報を見ると、予想外の結果が出た。

  全くAS400と通信してへん  (・・)

  思わず目が点になった。
  「なんでやねん・・・」と固まるしかなかった。 

  だが、ベンダー技術者は ODBCには詳しくない上、この日の作業が
終わったので、帰る事になった。

  私は、ベンダーの技術者に「Linuxの問題なので調べてみます」と言った。


  さて、Linux側に原因がありそうなのだが、見当がつかない。
  もしかして、SQL開発キットを使ってもダメなのか。

  だが、この程度で諦める私ではない。
  rmtodbc コマンドを使ってみる事にした。

  このコマンドは、ODBC経由でAS400に命令を送るためのコマンドだ。

  だが、rmtodbcコマンドは問題なく使える。
  何が原因なんだろうか・・・。

  1つの仮説を立てた。

  isqlコマンドの場合、AS400は動かない！

  そういえば、前編でも isqlコマンドを使って実験を行っているが、
tcpdumpでパケットの情報を見ていない気がする。

  isqlコマンドだけが使えないという事を願って、PHP4のODBCの関数を使って、
AS400に接続する事を考えた。
  (実際には、isqlコマンドも使えます。その話は後述しています)

  そこで以下の簡単なPHP4のプログラムを書いてみた。
  内容は、ODBC接続を行い、接続IDを画面に出力するプログラムだ。
簡単なプログラム( odbc1.php )
<HTML><HEAD><TITLE>AS400 ODBC test</TITLE></HEAD>

<BODY BGCOOR="WHITE">

<?php

$conn = odbc_connect("AS400","XXXX","YYYY");

print "conn = $conn <BR>";

odbc_close($conn);

?>

</BODY></HTML>

XXXXは、AS400のユーザーID
YYYYは、AS400のパスワード

$conn は、ODBCで接続した時の、接続IDを入れる変数。


  結果は・・・

  接続IDをキチンと取得している！
プログラムの実行結果

conn = Resource id #2


  ならばと思い、SQL文を送り、クエリーの結果IDを表示させて見る事にした。
簡単なプログラム( odbc2.php )
<HTML><HEAD><TITLE>AS400 ODBC test</TITLE></HEAD>

<BODY BGCOOR="WHITE">

<?php

$conn = odbc_connect("AS400","XXXX","YYYY");

print "conn = $conn <BR>";


$SQL = "SELECT * FROM AAAA.BBBB" ;
$QID = odbc_exec($conn,$SQL);

print "QID = $QID <BR>";

odbc_close($conn);

?>

</BODY></HTML>

XXXXは、AS400のユーザーID
YYYYは、AS400のパスワード

AAAAは、AS400上のライブラリ
BBBBは、AS400のデータテーブル(物理ファイル名)

$conn は、ODBCで接続した時の、接続IDを入れる変数。
$QIDは、クエリーの結果IDを入れる変数


  実行結果は・・・

  クエリーの結果IDをキチンと取得している！
プログラムの実行結果

conn = Resource id #2
QID = Resource id #3 


  これはうまくいきそうな感じがする。

  だんだん興奮してくる。
  気持ちを抑えながら、AS400のデータをSQL文で抽出して画面に表示させて
見る事にした。

  次のプログラムを動かしてみる。
簡単なプログラム( odbc3.php )
<HTML><HEAD><TITLE>AS400 ODBC test</TITLE></HEAD>

<BODY BGCOOR="WHITE">

<?php

$conn = odbc_connect("AS400","XXXX","YYYY");

$SQL = "SELECT * FROM AAAA.BBBB" ;
$QID = odbc_exec($conn,$SQL);

$record = array();
odbc_fetch_into($QID,$record);

odbc_close($conn);

print_r($record);

?>

</BODY></HTML>

XXXXは、AS400のユーザーID
YYYYは、AS400のパスワード

AAAAは、AS400上のライブラリ
BBBBは、AS400のデータテーブル(物理ファイル名)

odbc_fetch_into()関数は、配列変数の$recordに、SQL文の出力結果を入れる。
$record の配列で、キーは数字になる。


  見事にAS400のデータを抽出できた！！  V(^o^)V

  LinuxとAS400との連動ができたのだ。
  うちの会社にとっては画期的な事なのだ！！


  どうやら初期設定では、AS400から取り込んだデータの文字コードは
EUCになっているようだ。



  ところで、isqlコマンドが動かなかった事を書きました。
  なぜ、動かなかったのか原因を書きます。

  isqlコマンドが動かなかった時、isqlコマンドは使えないと思った。
  だが、以下のサイトを見て思った。
  http://www.e-bellnet.com/special/vision/vision_0405.html


  せや、IDとパスワードを指定してへんかった！

  そこで、次のようにした。
再び isqlコマンドを使ってみる
[suga@server]% isql AS400 XXX YYYY
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

赤い部分は、AS400へのログインID
青い部分は、ログインのパスワード


  動いた！！

  どうやら、AS400への接続の場合は、ODBCにつけた名前以外にも
ログインのIDとパスワードをつける必要があるようだ。

  ちなみに、なぜ、ログインのIDとパスワードをつける事に
気がつかなかった理由だが、同じように、isqlコマンドで、
PostgreSQLにアクセスする場合、ログインのIDとパスワードを
省略しても問題がないのだ。
  そのため、AS400でも同じ感覚でisqlコマンドを使おうとしたのだった。


  さて、isqlコマンドが使える事がわかったので、早速、SQL文を入れてみる。
  社員マスターのデータを検索してみる。
SQL文を使ってみる
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT * FROM AAAA.BBBB

| CCC  | DDD     |               | 

SQL> 

空白の部分は、社員の氏名が入るフィールドなのだが、
ここでは出てこなかった

「AAAA」、「BBBB」、「CCC」、「DDD」の値は 機密事項なので内緒。
もちろん、実際のフィールドは3つではない。フィールド数も内緒 (^^)


  うーん、日本語の部分が表示されていない。

  ここで、isqlコマンドだと日本語の表示ができないと思い込んでしまった。


  だが、後でわかった話、初期状態では日本語を表示しないようになっているが
設定ファイルを変更する事で、日本語の表示は可能な事がわかった。

  AS400を購入した先に、iSeriesAccessのLinux版について問い合わせた。
  購入先は「うちでは扱っていませんが、IBMの資料を送ります」と言ったので
早速、その資料を見た。
  資料の中で、odbc.iniの設定部分で、文字コードの処理の部分があった。

  CCSIDの値を設定すれば良いというのだ。
  EUCで出力する場合は、CCSIDの値を「1350」にして、Shift_JISの場合は
CCSIDの値を「943」を指定する。
odbc.ini の設定
[AS400]
Description             = iSeries Access ODBC Driver
Driver          = iSeries Access ODBC Driver
System          = AS400のIPアドレス
UserID          = AS400のユーザーID
Password                = AS400のパスワード
Naming          = 0
DefaultLibraries                = QGPL
Database                = 
ConnectionType          = 0
CommitMode              = 2
ExtendedDynamic         = 1
DefaultPkgLibrary               = QGPL
DefaultPackage          = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression            = 1
MaxFieldLength          = 32
BlockFetch              = 1
BlockSizeKB             = 128
ExtendedColInfo         = 0
LibraryView             = 0
AllowUnsupportedChar            = 0
ForceTranslation                = 0
Trace           = 0
CCSID = 1350

青い部分が、CCSIDの設定部分。
初期状態では、この項目がないので、odbc.iniファイルに
直接、追加を行う必要がある。


  さて、設定変更を行ったので、もう一度、isqlコマンドを使ってみて
日本語が表示できるかどうか確認してみる。
  すると・・・
SQL文を使ってみる
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT * FROM AAAA.BBBB

| CCC  | DDD     | 山田 太郎  | 

SQL> 

見事に日本語が表示できた。
ちなみに、半角カナの場合も表示できます。


  しかし、CCSIDの値の設定は、あくまでもAS400からLinuxで
データを送った場合に変換される文字コードの指定であって、
LinuxからAS400へ転送する場合、Linux側で使っている文字コードを
知らせる役割は持っていない。

  それが証拠に、日本語を入れたSQL文だとエラーが出る。
エラーの様子
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT * FROM AAAA.BBBB WHERE CCCC LIKE '%山田%'
[ISQL]ERROR: Could not SQLPrepare

赤い部分はエラー内容。
曖昧検索のために日本語を入れてみたが、受け付けてくれないようだ。


  もちろん、PHP + ODBC を使って、Web上で検索させる場合も
odbc.iniファイルで、CCSIDの値の指定を記述しただけでは
曖昧検索などでSQL文に日本語を入れたらエラーが出る。

  SQLに入れる日本語の文字コードを、SJIS、UTF-8でも試しましたが
結果はダメだった。

  ちなみに、CCSIDですが、IBMの英語のサイトを見て、以下の言葉の
省略になっている事を知った。

  Coded Character Set Idntifiers

  つまり、文字コードのIDをセットするものだという。


  SQL文で、INSERT、UPDATE、そしてSELECTでの条件指定での場合で、
日本語を使うとエラーが出る。この時は、原因がわからないため先送りした。
  しかし、その後、解決ができた。その事は、後述しています。


いよいよ稟議をあげる

  さて、SQL文の作成の際、INSERTやUPDATE、そして、SELECT文で
WHEREの後に日本語を入れる場合には難アリのようだ。
  だが、SQL文に日本語を入れない場合は、問題なく使える。

  AS400とLinuxとの連動。
  IT化を進めて行く上で、大きな可能性を秘めていると感じる。

  オープンソースで中小企業のＩＴ化！

  をスローガンに、オープンソースを活用してシステムを構築してきたが
情報系ばかりで基幹系でのオープンソースの活用は行っていなかった。

  だが今回は、Apache + PHP + ODBC で基幹システムに接続している。

  オープンソースに取り組んで6年。
  ようやく基幹システムに手をつけた！！


  大きな可能性を秘めていると感じるが、その可能性を少しづつでも
具体化していく必要がある。
  いくら「大きな可能性」と声を大にして叫んでも、具体例を示さないと

  SQL開発キットの稟議が通らないのらー！！

  そうなのだ。まずは稟議を通さないと話にならないのだ。

 
  そこで何か試作品を作成する必要がある。

  ふと思いついたのが、次の2つだった。
思いついた事
(1) 
Web上で売上実績などを表示させる。
役員が表示させた時点での情報を得られるだけでなく、
閲覧のために、わざわざ役員のパソコンに
AS400のエミュレーターを購入必要がなくなる。

その上、Web上での閲覧により、帳票出力が減れば
紙の削減にもつながる。
(2) 
AS400のデータを直接使う事によって
社内向け検索システムを構築。

今までは、一度、データをパソコンに落とし込み
それを加工してPostgreSQLに入れていたが、
それだとデータの更新を忘れると整合性が
取れなく問題があるが、直接、AS400のデータを使うと
整合性の問題も解消される。


  早速、売上実績をWeb上で表示するプログラムと、検索システムの
プログラムを作成した。


  だが、もう一押しが欲しい。
  ふと思いついたのが、FPDFを使ったPDF帳票の出力だ。
  FPDFを導入してみたが、社内で使い道がなかった。

  その上、月初になると、上司がP-COMM(AS400のエミュレーター)を使って、
AS400のデータをパソコン上に、CVS型式のファイルに落とし込み、
それをエクセルを使って手作業で加工して役員に送っていた。
  凄く手間がかかる上、作成するのに時間がかかっていた。
  しかし、以前、ベンダー製品の購入の稟議を上げたが、価格の面から
費用対効果が合わないという事で却下になった。


  だが、SQL開発キットを使えば、定価16万円。
  しかも、他の費用はかからない上、Linux側で自動的にデータを加工して、
PDFに出力すれば、データ加工を行っていた上司の手間が省ける。
 しかも、FPDFを使えば、無料でPDFファイルの生成できる。

  実績表をPDFファイルで出力しちゃえ！

  早速、定規を持ってA4の紙を測りながら、表の作成に取り組んだ。

  PDF帳票作成につきましては、詳しくは「システム奮闘記：その55」の
PHP言語とFPDFを活用したPDF帳票作成システムをご覧ください (^^)

  そして完成した試作品を上司に見せた。
  ボタン1つで即座にPDFファイルが出力されるので、  
上司が「これが自動化されると助かる」と言ってくれた。

  大企業のように費用をかけなくてもIT化は可能！

  これが私が追求していた中小企業のIT化の姿だ。
  

  PDF帳票は、大きな関心を寄せる事ができる。
  部長に試作品を渡すと「役員会にかけてみるわ」と言ってくれた。

  役員会の結果・・・

  導入OKだった  V(^^)V

  今度、PDF帳票ができる。
  上司だけでなく、他の人でもAS400のプリンタで印刷された帳票を
わざわざ手作業でエクセルに転記している人がいるが、
その人の手間も省けるのだ。




日本語の処理問題との格闘


  残りの問題があった。
  AS400に送るSQL文だ。今のままだと日本語を含めるとエラーが出るため、
日本語のデータを入れたり更新したり、日本語を使った曖昧検索ができない。


  検索サイトで調べてみる事にしたが、何せAS400とLinuxの連動の話自体
情報が少ないだけに、曖昧検索やレコード挿入のINSERTや、
レコードの更新のUPDATEでの日本語の扱いまで探しきれなかった。

  そんなある日の事、あるサイトに、以下の内容が書いてあった。
  「INSERTやUPDATEなどで日本語を使う場合は、パラメーターマーカーを使った
プリペア文を用意して、日本語データをバインドする」という内容だった。

  この時、思った。

  AS400側で何か設定しないといけないのか！

  だが、検索サイトで調べるが、それらしいサイトが見つからない。
  例え、該当の内容の記述が見つかったとしても、とてもAS400の設定なんぞ
できるわけもない。
  そこで、AS400の購入先に電話をするが

  私達もわかりません・・・

  だった (^^;;


  IBMに聞くしかない。
  幸い、IBMのサポートがあるため、IBMに聞く事にした。

  IBMのサポートセンターから「ODBCのプリペア関数を使って」と言われる。
  もちろん・・・

  プリペアって何?

  だった。
  なので、根堀り葉堀り聞く事にした。
  すると、次のようにする必要があるという。

  まず、SQL文だが、肝心の日本語が入る部分は「?」を入れるという。
  LIKEを使ったSELECT文の曖昧検索を例をあげると以下のようになる。

  SELECT * FROM TABLE WHERE KENMEI LIKE ?

  そして、日本語の記述したい場所に「?」を入れるというのだ。
  そして、記述したい日本語を配列のキーにする。
  odbc_execute()関数の第1引数にODBC接続ID、第2引数に配列にすれば
日本語が使えるというのだ。

  UPDATE文を例に、もっとわかりやすく書くと、次の通りになる。
  TABLEというデータベーステーブルがある。
TABLE という名のテーブル
フィールド名NUMBERNAKAMI
フィールド属性数字日本語

  このフィールドのNUMBERが「1」の時、「井上和香」が入っていたとする。
それを私の大好きな「小野真弓」に変えたい場合、以下のようにする。
こんな感じになる
PHP言語でODBC関数の活用と、SQLのプリペア文について
(1)で、SQL文の内容を$sqlの文字変数に格納するのだが、
この段階では置き換えたい日本語は記述せず、代わりに「?」を入れる

(2)で、odbc_prepare()関数を実行させる。
これが「プリペアする」という意味だ。
この関数の返り値がクエリーIDになる。
もちろん、odbc_execute()関数を実行した後でないと
クエリー結果は見れないのだが。

(3)で、置き換えたい日本語を配列のキーにする。

(4)で、odbc_execute()関数を実行させる。
この時、SQL文の中に入れた「?」の所に、置き換えたい日本語が入り
初めてSQL文が実行されるのだ。
この返り値が「1」なら、クエリーの実行が成功した事を意味する。


  こんな面倒なやり方なのだが、この方法でないと、日本語を使った
SQL文が使えないというのだ。

  ところで、odbc_prepare()関数がなぜあるのか疑問に思った。
  SQL文の処理を高速にするなどの役目があるようなのだが、
今回は、この部分は割愛します。調べるのが大変だもーん (^^;;
プリペア文について (2006/12/20)
「大変だもーん」で逃げたのだが、実は、難しい話ではなかった。
odbc_prepare()関数は、以下の2つの理由で使われる事がある。
プリペア文の役目は、ODBC以外でも、他のデータベースでも共通している。

(その1)

SQL文は、そのままではデータベースは理解できないため、
SQL文を送ると同時に、構文解析などが行われる。
つまり、SQL文にもコンパイルがかけられるのだ。

さて以下のSQL文を使うとする。

SELECT * FROM  BSBALL WHERE BSCOMM LIKE $value

もし、$valueの値だけを変えて、何度も同じSQL文を使う場合、
個々にSQL文をコンパイルをしていたのでは、高速な処理が望めない。
そこで、$valueの部分を「?」にして、
一度、prepare(プリペア)でコンパイルをかけておく。
そして、「?」の部分に、odbc_execute()を使って、$valueの値を代入する。
$valueの値さえ変更すれば、SQL文はコンパイル不要で
何度でも再利用可能なため、処理が高速になるというわけだ。


(その2)

SQLインジェクトを防止するために使われる。
外部からの文字列を代入する部分を「?」を置く。
「?」を置く事により、odbc_execute()で代入する場合は
「'」といった特殊文字が、普通の文字として
代入されるため、SQLインジェクトを防止になる。


  さて、早速、プログラムを作ってみた。

  AS400に、以下の物理ファイル(データベース)を作成して
INSERT文で試してみる事にした。
BSBALL という名のテーブル
フィールド名BSNUMBBSCOMM
フィールド属性数字日本語

  そしてINSERT文のプログラム。
insert.php
<?php
  $OID = odbc_connect("AS400","ID","PASSWORD");

  $key = array("1","阪神２位だった");

  $sql ="INSERT INTO BSBALL VALUES( ? , ?)" ;

  $pid = odbc_prepare($OID,$sql);

  $QID = odbc_execute($pid,$key);

  odbc_close($OID);

?>


  さて、実験結果は・・・
実験結果
[suga@server]# isql AS400 ID PASSWORD
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT * FROM BSBALL
+--------+-------------------+
| BSNUMB | BSCOMM            |
+--------+-------------------+
| 1      | 阪神２位だった    |
+--------+-------------------+
1 rows returned
SQL> 


  見事、成功！

  次に、UPDATE文で試してみる事にした。
update.php
<?php
  $OID = odbc_connect("AS400","ID","PASSWORD");

  $key = array("来年は阪神優勝！");

  $sql ="UPDATE BSBALL SET BSCOMM = ? WHERE BSNUMB = 1";

  $pid = odbc_prepare($OID,$sql);

  $QID = odbc_execute($pid,$key);

  odbc_close($OID);

?>


  さて、実験結果は・・・
実験結果
[suga@server]# isql AS400 ID PASSWORD
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT * FROM BSBALL
+--------+-------------------+
| BSNUMB | BSCOMM            |
+--------+-------------------+
| 1      | 来年は阪神優勝！　|
+--------+-------------------+
1 rows returned
SQL> 


  見事、成功！

  どうやら、これでうまくいきそうな感じだ。
  そして、LIKEを使ったSELECT文の曖昧検索を行ってみる事にした。
like.php
<?php
  $OID = odbc_connect("AS400","ID","PASSWORD");

  //  「%」は曖昧検索の際に使う
  //  「%」任意の文字数という意味のワイルドカード
  //   前後を「%」ではさむ事で、「阪神」という言葉を含んだ文字列になる

  $key = array("%阪神%");

  $sql ="SELECT * FROM  BSBALL WHERE BSCOMM LIKE ?";

  $pid = odbc_prepare($OID,$sql);

  $QID = odbc_execute($pid,$key);

  //  検索結果の第2フィールドの中身を出力
  $comment = odbc_result($pid,2);
  print $comment;

  odbc_close($OID);

?>


  Webで実験結果(出力結果)を見たのだが・・・

  何も表示されへん （ＴＴ）

  だった。

  どこで問題が発生しているのか確かめるべく、個々の関数の返り値を
表示させてみる事にした。
like.php
<?php
  $OID = odbc_connect("AS400","ID","PASSWORD");

  print "OID = $OID <BR>" ;

  //  「%」は曖昧検索の際に使う
  //  「%」任意の文字数という意味のワイルドカード
  //   前後を「%」ではさむ事で、「阪神」という言葉を含んだ文字列になる

  $key = array("%阪神%");

  $sql ="SELECT * FROM  BSBALL WHERE BSCOMM LIKE ?";

  $pid = odbc_prepare($OID,$sql);

  print "pid = $pid <BR>" ;

  $QID = odbc_execute($pid,$key);

  print "QID = $QID <BR>" ;

  //  検索結果の第2フィールドの中身を出力
  $comment = odbc_result($pid,2);
  print $comment;

  odbc_close($OID);

?>
実験結果
OID = Resource id #2
pid = Resource id #3
QID = 


  odbc_execute()関数の返り値がない・・・。

  これは、クエリーの実行が失敗した事を意味する。

  原因がわからない。

  なので、IBMのサポートセンターを電話をかける。
  サポートセンターの人も「うーん、どこが原因だろう」という感じ。
  そこで、サンプルプログラムをもらう事にした。

  だが、サンプルプログラムと私の書いたプログラムを見比べたが・・・

  方法は全く変わらへん （ＴＴ）

  だった。

  なので、IBMのサポートセンターへ電話をかける。
  サポートセンターの人は「もしかして、ODBCドライバーの問題ではなく
Linux側の問題ではないでしょうか。ソフトのバージョンなどの
問題が考えられます」だった。

  この日のサポートセンターの人は、あまりLinuxに詳しくない人だった。
  これを書くと、サポートセンターの人には酷かもしれないが、
こんなやりとりになった。
サポートセンターとのやりとり
(ピンクの文字は心の中で思った事です)
私 では、すみませんが、バージョンを教えてください
IBM 私、Linuxは詳しくないのでわかりません
私 ・・・
Linuxは、どこのディストリビューターですか。
インストールの際は、ソースコンパイルですか、RPMですか。
IBM RPMです
私 では、次のようにしてください。
rpmの後ろにオプション「-qi」を付けてください。
そして、オプションの後ろに、ソフトの名前を入れてください。
そしてENTERキーを押してください。
(どっちがサポートセンターや・・・)
IBM unixODBCは2.2.11ですね。
菅さんはソースコンパイルをされているのですか。
私 ApacheやPHPはソースコンパイルしています
IBM ソースコンパイルだと、インストールした方の癖が出ますね
私 ・・・
癖なんて、でるわけねぇーだろ・・・。
逆に癖が出た方が、よっぽど恐いぞ・・・
使うライブラリによってオプションの有無を行うだけだよ
IBM Apacheでマルチバイトの処理ができていないとか
私 (Apacheにマルチバイトが関係あるんかいな・・・)
私、過去に何度もApacheのコンパイルをした事があるけど
マルチバイトのオプションなんて聞いた事がないですよ。
PHPの場合はありますけど、付けていますし、付けていなければ
日本語処理ができないですから 

  上のような、笑えるような、顔が引きつるような、やりとりをしながら、
サポートセンターの人が使っているソフトのバージョンを聞き出す。

  Linuxは、openSuSE-10.1を使っているという。
  unixODBC-2.2.11。PHP-5.1.4、Apache2.0系だという。


  さて、困った。openSuSEを入れないといけないが手元にない。
  ネットからISOファイルをダウンロードしてCD-Rに焼くか、
それとも本の付属のCD-ROMを使うかだ。

  でも、ちょっと待って考えた。
  別に、OSまで変更する必要はあらへんやないか。
  ディストリビューションや、カーネル依存の問題ではなさそうだからだ。
  なので「unixODBCなどのバージョンを変更すれば、ええやん」という
結論に至った。

  という事で、文字変換に関係なさそうなApacheは1.3.37のままコンパイル。
configureのオプション
[suga@server]% ./configure --enable-module=so
赤い部分は、Apacheが動的にモジュールを取り込めるようにするための
オプションです。 

  unixODBCだが、RPMだとバージョンが古い。
  RedHat9.0のCD-ROMについているのは、unixODBC-2.2.3だった。

  なので、ソースコンパイルを行う。
  IBMのサポートセンターでは、unixOBDC-2.2.11のバージョンだったが、
unixODBC-2.2.12が最新だったので、最新バージョンを入れてみる事にした。
configureのオプション
[suga@server]% ./configure --sysconfdir=/etc
赤い部分は、unixODBCの設定ファイル(odbc.ini、odbcinst.ini)の
ディレクトリが /etc になるようにするオプションです。 

  そして、PHP-4.4.4を入れてみた。
configureのオプション
[suga@server]% ./configure --with-apxs=/usr/local/apache/bin/apxs --with-unixODBC --enable-mbstring
青い部分は、PHP4をApacheのモジュールとして取り込むためのオプション。
ディレクトリの指定は、Apache側にあるモジュールとして取り込むための
ツール(apxs)の場所を指している。
Apacheをソースコンパイルした場合、/usr/local/apacheの中に
Apache関係のファイルが格納される。

赤い部分は、unixODBCと連動させるためのオプション。

残りは、バルチバイトの処理を可能にするオプション。
つまり、日本語処理を可能にするためのオプションだ。


  そして、AS400への接続のためのODBCドライバのインストールを
行うのだが、素直にRPMでインストールしようとしても、RPMのため、
依存性のチェックに引っかかる。

  ここで立往生かと思ったのだが、この時は、頭が冴えていた。

  RPMのオプションを見てみよう！

青い部分は、PHP4をApacheのモジュールとして取り込むためのオプション。
ディレクトリの指定は、Apache側にあるモジュールとして取り込むための
ツール(apxs)の場所を指している。
Apacheをソースコンパイルした場合、/usr/local/apacheの中に
Apache関係のファイルが格納される。

赤い部分は、unixODBCと連動させるためのオプション。

残りは、バルチバイトの処理を可能にするオプション。
つまり、日本語処理を可能にするためのオプションだ。


  そして、AS400への接続のためのODBCドライバのインストールを
行うのだが、素直にRPMでインストールしようとしても、RPMのため、
依存性のチェックに引っかかる。

  ここで立往生かと思ったのだが、この時は、頭が冴えていた。

  RPMのオプションを見てみよう！

青い部分は、PHP4をApacheのモジュールとして取り込むためのオプション。
ディレクトリの指定は、Apache側にあるモジュールとして取り込むための
ツール(apxs)の場所を指している。
Apacheをソースコンパイルした場合、/usr/local/apacheの中に
Apache関係のファイルが格納される。

赤い部分は、unixODBCと連動させるためのオプション。

残りは、バルチバイトの処理を可能にするオプション。
つまり、日本語処理を可能にするためのオプションだ。


  そして、AS400への接続のためのODBCドライバのインストールを
行うのだが、素直にRPMでインストールしようとしても、RPMのため、
依存性のチェックに引っかかる。

  ここで立往生かと思ったのだが、この時は、頭が冴えていた。

  RPMのオプションを見てみよう！
RPMのオプション
[suga@server]% rpm -v

  --nodeps                         パッケージの依存関係の検証をしません


  これだと思った。
  このオプションを使えば、依存性のチェックを行わなくなる。

  そして、AS400への接続ドライバのインストールしてみる。
AS400への接続ドライバのインストール
[root@server]% rpm --nodeps -ihv  iSeriesAccess-5.4.0-1.0.i386.rpm
Preparing...                ########################################### [100%]
   1:iSeriesAccess          ########################################### [100%]
post install processing for iSeriesAccess 1.0...1
iSeries Access ODBC Driver has been deleted (if it existed at all) because its usage count became zero
odbcinst: Driver installed. Usage count increased to 1. 
    Target directory is /etc


  インストール成功 (^^)V

  unixODBCへの、AS400のODBCドライバ登録を行ってくれる。
  そのため、ODBCドライバの登録情報(ドライバのあるディレクトリなど)の
記述ファイル(odbcinst.ini)には、自動的に書き込まれる。
  上の青文字の部分が、自動的に登録をしている記述になる。


  さて、これでLIKEを使ったSELECT文で、日本語を使ってみた。
  だが、結果は・・・

  アカンかった （ＴＴ）

  だが、unixODBCのバージョンを、この時の最新バージョンに上げたため
unixODBC-2.2.3では見られなかった物が見られるようになった。
  それは、PHPの実行時に吐き出すエラーだった。
unixODBCを2.2.12に上げたお陰で見れるようになったエラー
Warning: odbc_execute() [function.odbc-execute]:
 SQL error: [unixODBC][IBM][iSeries Access ODBC ドライバー]
[DB2 UDB]SQL0302 - ホスト変数またはパラメーター*Nで変換エラー。
, SQL state S1000 in SQLExecute in /usr/local/apache/htdocs/like.php
 on line 32

unixODBC-2.2.3の時は、こんな表示はなかった。
PHPのバージョンは変更していない事から、PHPがODBC関数を使う際に
使っているライブラリの仕様が、unixODBCのバージョンを上げた事によって、
変更されたため、エラー表示が出るようになったと思われる。


  だが、エラーはエラーで、日本語処理がうまくいかない。

  もしかして、PHPのバージョンが4.4.4だとダメなのかと思って、
今度は、PHP-5.1の最新版のPHP-5.1.6を入れてみた。
configureのオプション
[suga@server]% ./configure --with-apxs=/usr/local/apache/bin/apxs --disable-libxml --enable-mbstring --with-unixODBC --disable-simplexml --disable-xml --disable-xmlreader --disable-xmlwriter --disable-dom --without-pear 
PHP-5.1.6のソースコンパイルは、オプションが多くなった。
RedHat9.0で、私がインストールした時、XML関係が中途半端に
インストールされたため、XML関係の依存性がややこしく、
configureのたびにエラーを吐くため、依存性を全部外すのに
結構、手間がかかりました (^^;;

この辺りは、RedHat9.0をインストール方法などに依存するため、
他の人がコンパイルするのには、あまり参考にならないと思う。


  だが、結果は・・・

  アカンかった （ＴＴ）

  その後、IBMのサポートセンターのバージョンと同じ、unixOBDC-2.2.11、
PHP-5.1.4でもやってみたのだが、

  やっぱり、アカンかった （ＴＴ）

  どうやらunixODBCやPHPのバージョンの問題ではなさそうだ。
  だが、バージョンの違いが原因でない事がわかっても
何ら前に進む事ができなかった。
  なので・・・

  絶望的な気分になった (--;;


  だが、ふと思った。

  AS400の物理ファイルの定義に問題があるのではないか。

  日本語が入るフィールドだが、文字タイプが「J」と「O」の2種類ある。
  「J」は全角のみ入るが、「O」は全角・半角の共存が可能だ。

  曖昧検索の実験につかった物理ファイルだが、フィールド定義が
案の定、「J」になっていた。
AS400上の物理ファイルの設定を見てみる
AS400(iSeries,i5)ので物理ファイルのデータタイプの設定

  そこで別の物理ファイルを作成し、日本語のフィールドを「O」にした。
AS400上の物理ファイルの設定を変更してみる
AS400(iSeries,i5)ので物理ファイルのデータタイプを「O」にする設定

  そして、曖昧検索にしたら

  見事、成功した！！

  この時、unixODBCのバージョンの違いが原因ではない事を確信した。
  データタイプの問題だという所まで突き止めた。

  うちの会社のAS400の物理ファイル(データベース)の設定だが、
データタイプ「J」を使っているのだが大半だ。

  私が入社する以前に、AS400を導入した時に関わった人の話では
「全角文字しか使わへんと思ったから、何も考えずに『J』にしたなぁ」
だった。

  何も知らない人から見れば「データタイプを変えればええやん」と思うが
そんな単純な問題ではない。

  データタイプを変更となれば、おそろしく手間のかかる作業が必要だ。
AS400の物理ファイルの変更は凄い手間がかかる
AS400の物理ファイルなのだが、データベースを設計しなおすと
一度、データをバックアップした上で、データベースのコンパイルが必要だ。
その上で、データを戻す。それだけではない。

そのデータベースを使っているPRGプログラムの再コンパイルや
画面表示や画面入力などの画面プログラムの変更と再コンパイルを
行う必要があるのだ。凄く手間がかかるので、手をつけたくないのだ・・・




  翌日、IBMのサポートセンターに電話をした。
  unixODBCのバージョンの違いが原因ではなく、AS400の物理ファイルの
データタイプの違いが原因だと伝えた。
  そして、データタイプが「J」でも日本語の曖昧検索ができる方法を尋ねた。

  IBMのサポートセンターの人は「これは仕様の問題ですね」と言った。
  続けて「色々、検証してみます」と言った。


  何時間か後、IBMのサポートセンターから電話が入る。
  「やはりデータタイプ『J』では無理のようです」だった。

  Windows版のODBCドライバの場合、曖昧検索で使うワイルドカードの「%」を
全角の「％」に置き換えると、問題なく稼働するが、Linuxだと
検索結果が出るが、とても曖昧検索とは言えない物だというのだ。
こんな感じにするがダメだった
<?php
  $OID = odbc_connect("AS400","ID","PASSWORD");

  //  曖昧検索に使うワイルドカードを半角「%」ではなく
  //  全角の「％」を使ってみた

  $key = array("％阪神％");

  $sql ="SELECT * FROM  BSBALL WHERE BSCOMM LIKE ?";

  $pid = odbc_prepare($OID,$sql);

  $QID = odbc_execute($pid,$key);

  //  検索結果の第2フィールドの中身を出力
  $comment = odbc_result($pid,2);
  print $comment;

  odbc_close($OID);

?>


  実際に、何十レコードもあるデータベースで実験を行ったが、
実験結果は目も当てられない物だった。


  IBMのサポートセンターの人は次のようにいった。
  「この事例は、報告いたしますが、なにぶん、反映されるまで
時間がかかります。なかなか日本語の問題を報告しても
対応してくれないのですが、挙げないよりかはマシなので」だった。


  この時、思った。
  マルチバイト処理の問題で欧米の対応が凄く悪いのは、
オープンソースだけでなく、商用ソフトでも起こっているのだと思った。

  瞬間湯沸機になった私は怒りのあまり

  ふざけるな！  欧米の連中らめ！ (--#

  となった。
  別に、私は、外国嫌いで尊皇攘夷論を唱える幕末志士ではないし、
「欲しがりません。勝つまでは」と唱える戦中派でもない。

  しかし、文字コードの問題で、欧米の身勝手さには怒りを感じる。
マルチバイト言語を使っている人間として「ふざけるな」と思う。

  マルチバイト文字の処理の分野(国際化)では、日本が頑張っている。
  時には、身勝手な欧米の開発者と抜き差しならぬ激しい議論になるという。

  そういう意味では、日本の開発者には敬意を感じ、頭が上がらない。


  だが、20分後、IBMのサポートセンターから電話が入る。
  話を聞くと・・・

  「J」タイプでも検索できました

  だった。

  あの「欧米の連中らめ！」という怒りは何だったのだろうか。
  思わず拍子抜けになった (^^;;

  全く人騒がせなIBMだと思った。

  ただ、曖昧検索で使うワイルドカード「％」(全角)を使う場所が違うという。
  array()の中に直接、埋め込むのではなく、array()の中に入れる前に
"％".文字列変数."％" という感じで使うのだ。

  具体的にソースコードにすると、次のようになる。
こうすれば良いのだ (^^)
<?php
  $OID = odbc_connect("AS400","ID","PASSWORD");

  //  曖昧検索に使うワイルドカードを半角「%」ではなく
  //  全角の「％」を使ってみた

  //  曖昧検索に使うキーワード
  $comm = "阪神" ;

  //  ここで曖昧検索に使う文字列の前後を「％」で挟み込む
  $key = "％".$comm."％" ;
 
  $sql ="SELECT * FROM  BSBALL WHERE BSCOMM LIKE ?";

  $pid = odbc_prepare($OID,$sql);

  $QID = odbc_execute($pid,array($key));

  //  検索結果の第2フィールドの中身を出力
  $comment = odbc_result($pid,2);
  print $comment;

  odbc_close($OID);

?>

曖昧検索の文字列を、"とピリオドを使って前後を「％」で挟み込む。
青い部分だ。この時点で、直接、"％阪神％"とするとNGだという。

そして、odbc_execute()関数の部分で、配列のキーに、
該当の文字列を入れる。ピンクの部分だ。


  早速、実験をやってみた。
  すると・・・

  ようやく実験成功だ！

  これで日本語の問題が解決できた。

  AS400とLinuxとの連動が可能になった。

  中小企業でAS400を使っている所は多いので、Linuxとの連動は
OSSを使った中小企業のIT化の展開に使えるのではないかと考える。

  Linuxサーバーでグループウェアを稼働させ、データをAS400に置いたり、
基幹システムのデータを、グループウェアで表示させる方法や
ネット販売システムを構築して、AS400の受注データと連動など
色々な使い道が考えられる。

  Openfficeとの連動も可能なため、Linuxデスクトップを導入し、
OpenOfficeでAS400のデータ利用をしたり、書類の作成もできます。

　そして応用としてPDF帳票作成システムの構築が可能になりました。
　PHP言語とFPDFを活用したPDF帳票作成システムについては
詳しくは「システム奮闘記：その55」をご覧下さい。

  色々、展開できそうな感じなだけに、今後が楽しみだ (^^)V


まとめ

  AS400とLinuxとの連動がようやく可能になりました。

  今回の連動のポイントは、AS400の物理ファイルのデータタイプで
日本語の入る部分が「O」の場合は、何も小細工する必要はありませんが
「J」の場合は、小細工が必要だという事です。

  AS400とLinuxとの連動で、情報が少ない事から、この奮闘記が
少しでもお役に立てれば幸いだと思います。
次章:「SMARTでハードディスクの健康診断(smartmontools)」を読む
前章:「無料のFPDFでPDF帳票システム構築」を読む
目次:システム奮闘記に戻る

Tweet