前回は、SQL文の使い方を見てみた。今回は、それの続きとして、単品データから分類別データ・小分類別データをテーブルに書き出すやり方を見てみよう。やり方として 二通りの方法を考えてみた。
１．単品データをMySQLでLOADDATAする前に 元データを編集して分類別データ・小分類別データも作ってしまう。
２．MySQLのテーブルから期間を指定してデータを取り込み、集計してテーブルに入れる。
まず、一つ目の方法をみてみよう。
この際の問題点は、データをLOADDATAを使って入れている点だ。LOADDATAを使う際に、パラメータで同一キーが存在する場合の動作を規定できる（当然仕組み上、上書きにしているが）が、万一同一データがあった場合に 入力データから作成した分類・小分類別データの値が 不完全なデータに置き換わってしまう可能性がある。これを回避する為には、データを作成する際にMySQLのテーブルを読まなくてはならない。更に、テキストファイルに対して、マスターデータを結びつける為には、マスターのテーブルをも読まなければならない。
実際に、この処理をVisualBasic2005Expressで作成してみた。してはみてが、動作が遅くて話にならない。入力件数をカウントしながら動かしてみると、ポン・ポンといった形で カウントアップが目で追える程度のスピードだ。これでは駄目だろう。
'SQL接続文字列Mttは入力単品データのテーブル名が設定してあります。
次に本命の二つ目のやり方に移る。画面は、左のようなイメージで作成してみた。基本的には 処理をバッチファイルから動かすことを想定しているので、開始日・終了日はコマンドラインから取り込むが、テスト時や障害時には手入力出切るようにそれなりのインターフェースは備えておくようにする。（なお、通常右半分は隠しておき、参照ボタン押し下げ時に日付選択出切るような形にする。）
更に、メッセージを画面に出しはするが、途中経過のみを出すにとどめ、エラーやその他情報はログファイルに書き出すようにする。そのようにしないと 「エラーメッセージを出して処理がとまっていた」では、バッチファイルとして自動実行させる意味が無い。エラーログは規定ホルダーに一週間分のログフアィルを作っておき、それをサイクリックに使うようにする。（つまり、一週間前まではバッチ処理のエラーを確認できるようにするわけだ）
今回の基本的なやり方は、DataReaderで指定期間の集計したデータを取り出し、これをDataAdaputerとして読み込んだテーブルに書き出すようにする。前回利用したSQL文をそのまま入力ソースとするわけだ。データ取り出し部分と書き出し部分のソースを一部抜粋すると以下のようになる。
Try
MsString = “server=xxx.yyy.zzz.aaa;user=username;password=password;database=master;Connect Timeout=5000;"
Cn.ConnectionString = MsString
'MAK Select command
sqlCmd = Cn.CreateCommand
SQLCMD.CommandText = "select t.kigyo, t.mise, t.busdt, min(t.bumon) as tbumon, m.bunrui," & _
" sum(t.suryo) as tsuryo, sum(t.uriage) as turiage, sum(dscqty) as tdscqty, sum(dsprat) as tdsprat" & _
" from TMAST m right outer join " & Mtt & " t" & _
" on m.kigyo=t.kigyo and m.scancd=t.scancd" & _
" where t.busdt>='" & Kaisi & "' and t.busdt<='" & Syuryo & "'" & _
" group by t.kigyo, t.mise, t.busdt, m.bunrui"
===中略
'SQL接続文字列Bttは出力分類テーブルのテーブル名が設定してあります。
Try
'DataAdapterオブジェクトの作成
sqlDA = New MySql.Data.MySqlClient.MySqlDataAdapter
'DataSetオブジェクトの作成
resultDS = New DataSet()
'MySQL接続文字列
MyconString = “server=xxx.yyy.zzz.aaa;user=username;password=password;database=master;Connect Timeout=5000;"
'接続を作成します
sqlDA.SelectCommand = New MySqlCommand()
sqlDA.SelectCommand.CommandType = CommandType.Text
sqlDA.SelectCommand.CommandText = "SELECT kigyo, mise, store, busdt, bumon, bunrui, uriage, suryo, kycut, dsprat,
dscqty, arari" & _
" FROM " & Btt & _
" where busdt>='" & Kaisi & "' and busdt<='" & Syuryo & "'"
sqlDA.SelectCommand.Connection = New MySqlConnection(MyconString)
'スキーマ情報をDatasetに追加する
sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey
===中略
Try
'接続を作成
sqlDA.Fill(resultDS, Btt)
Cn.Open()
dr = SQLCMD.ExecuteReader()
While (dr.Read())
Incnt = Incnt + 1
ReDim IIdata(7)
'データの読み込み
IIdata(0) = Val(dr("kigyo"))
IIdata(1) = Val(dr("mise"))
IIdata(2) = Val(dr("tbumon"))
If (IsDBNull(dr("bunrui"))) Then'===この部分が、マスターに無い商品対応の部分です。
IIdata(3) = IIdata(2) * 100
Else
If Val(dr("bunrui")) = 0 Then
IIdata(3) = IIdata(2) * 100
Else
IIdata(3) = Val(dr("bunrui"))
End If
End If
IIdata(4) = Val(dr("tsuryo"))
IIdata(5) = Val(dr("turiage"))
IIdata(6) = Val(dr("tdscqty"))
IIdata(7) = Val(dr("tdsprat"))
YYMMDD = dr("busdt")
Istore = LsSSCNV(IIdata(0), IIdata(1))'===これは、２桁の店番号を３桁店番号に変換するユーザー関数です。（通常は不要です）
findTheseVals(0) = IIdata(0) '企業コード
findTheseVals(1) = IIdata(1) '店(2桁）
findTheseVals(2) = YYMMDD '日付
findTheseVals(3) = IIdata(3) '中分類
oDR = resultDS.Tables(Btt).Rows.Find(findTheseVals)
If oDR Is Nothing Then '無い場合は追加
oDR = resultDS.Tables(Btt).NewRow()
===以下略
更に、サーバーサイドでユーザー登録をしておく必要がある。たとえば、以下のようにする。
GRANT ALL PRIVILEGES ON master.* TO 'username'@'xxx.yyy.zzz.aaa' IDENTIFIED BY
'password';
(使用するデータベースをmasterデータベース。接続するユーザー名をusername。接続するPCのIPアドレスがxxx.yyy.zzz.aaa。パスワードがpasswordの場合）
なお接続するIPアドレスのかわりに%を指定してしまうと 全てのアドレスから接続可能になってしまうので使わない事。
それでは、この処理を動かした結果はどうだったろうか？その時の結果を以下にしめす。
2011-01-11-18:50:11 mkBbh2 を起動します。おまち下さい。開始日=2011-01-06
終了日=2011-01-07
2011-01-11-18:50:50 mkBbh2 を書き出します。おまち下さい。入力= 16778 出力= 16778
2011-01-11-18:51:07 mkBbh2 の書き出しが終了しました
2011-01-11-18:51:07 mkBbh2 品種設定を起動します。おまち下さい。開始日=2011-01-06 終了日=2011-01-07
2011-01-11-18:51:41 mkBbh2 品種設定を書き出します。おまち下さい。入力= 45891 出力= 45891
2011-01-11-18:52:29 mkBbh2 品種設定の書き出しが終了しました
2011-01-11-18:54:35 mkBbh2 を起動します。おまち下さい。開始日=2011-01-01 終了日=2011-01-05
2011-01-11-18:55:26 mkBbh2 読み込み中にエラー = 5 明細:Fatal error encountered during
command execution.
2011-01-11-18:55:26 mkBbh2 の書き出しが終了しました
2011-01-11-18:55:26 mkBbh2 品種設定を起動します。おまち下さい。開始日=2011-01-01 終了日=2011-01-05
2011-01-11-18:56:06 mkBbh2 品種設定読み込み中にエラー = 5 明細:Fatal error encountered during
command execution.
2011-01-11-18:56:06 mkBbh2 品種設定の書き出しが終了しました
一度目、テーブルをクリエートして処理を動かしたら、問題なく動いた。次に、期間を１日から５日迄で指定して動かしたところ、「Fatal Error・・・」というのが出ていた。Fatal Errorといえば、続行不可能なエラーだ。一体、何があったのだろう？この後、何度処理しても同じようにエラーが出てしまい、処理を進める事が出来なかった。
ひょっとしたら、タイムアウトが起きているのではないかと思い至るまでに一晩かかった。タイムアウトを指定する為、接続文字列の最後に「Connect Timeout ３０」を書いてみた。（上のソース参照。なお ソースではタイムアウトを4000にしてある）
2011-01-12-11:47:07 mkBbh2 を書き出します。おまち下さい。入力= 17249 出力= 17201
更新= 48
2011-01-12-11:47:26 mkBbh2 の書き出しが終了しました
2011-01-12-11:47:26 mkBbh2 品種設定を起動します。おまち下さい。開始日=2011-01-08 終了日=2011-01-09
2011-01-12-11:48:08 mkBbh2 品種設定読み込み中にエラー = 5 明細:Timeout expired. The timeout
period elapsed prior to completion of the operation or the server is not
responding. 場所= 53853
2011-01-12-11:48:49 mkBbh2 品種設定の書き出しが終了しました
思った通り、タイムアウトが発生していたようだ。53853件目でエラーという事は、タイムアウトの時間が短すぎるのではないか？それなら、タイムアウトの時間を長くすればという事で タイムアウトを０に（マニュアルでは無制限に待つという事らしいが）してみた。しかし、この場合には待ちもせずにすぐエラーになってしまった。それでは 「待ち時間を４０００にしてみたら」、という事で行ってみた。しかし、結果は散々なのもだった。より少ない件数でタイムアウトしてしまう。
無料のMySQLと無料のMySQL ConnecterNetと無料のVisualBasic2005ExpressEdition。障害があった場合に、どこに確認すれば良いのか？そして、その障害を誰かが修正してくれるのか？オープンソースの難しさが こんな所にあったとは。・・・
どうしたら良いのか？途方にくれてしまった。
プログラムの内容を上のように書くと、インデントされていない為に読みづらくなってしまいます。この為、内容をPDF化したものをつけてあります。必要ならこちらを参照してみて下さい。PDFファイル。ちなみに、このファイルはVisualBasic
2005
Expressの内容をOpenOffice.Org2.4のWriterに貼り付けし PDFにエクスポートしたものです。テキストコピー可の形で作成していますので、そのまま使用することも可能です。
簡単に説明しておきますが、処理にホルダー体系は左のようになっています。VB2005にて作成した実行形式ファイルをOBJホルダーにコピーして実行しています。
bｋｌｇホルダーにはbkupknri.txtファイルがあｒ、これに書き出すべきファイル名が記載されます。詳しくはソースを確認してみてください。なお、パラメータに０を指定した場合の動きが間違っていますので、見直ししてみてください。
ソースを見られると、同じような処理が２度かかれていることに気づかれると思います。単品販売データ等がTP1101のように文字の後ろに年月が入るようになっています。これは、データを年・月毎に別テーブルに格納している為です。従って、月をまたいだ指定を行った場合には二つのテーブルに対してデータ処理を行う必要がある事になり、その対応の為です。VB６まではこういう場合にGoSubを利用できたのですが、.Netではこの便利なGoSubが使えなくなり全てFunctionやSubで呼び出して処理を行ってくれとMicrosoftでは言っています。しかし、ファイル処理やテーブル処理の場合に引数がかなり多く必要とされます。つまり処理がかえって煩雑になってしまうし、その度ごとに変数を指定したりする必要もあり、非常に使いづらいものになっているのではないでしょうか。同じ関数の中で さらりとGosubで処理する方が 簡潔だと思うのですが。
タダで使わせてもらって文句言うのもどうかと思いますし、文句を言い出すときりがなくなるのでこの位にしておきます。ただ、何故こんな点を変えなければならないのか？みたいな部分もあります。たとえばMsgBoxがMesｓageBox.showとなった。それはそれで構わないのですが、パラメータの指定順序まで変える必要がどこにあったのか？別な物だと考えれば良いのでしょうが、あまりにもあまりにも。・・・・。
