このところ、PostgreSQLやOracleのデータベースをマイクロソフト社のアクセスから操作するシステムを開発している。マイクロソフト社のアクセスには大量データを処理すると不安定になるなどの問題があるのだが、短期間に効率的にシステムを開発したい、さらに専用データベースをクライアントが非定型業務で活用したい という要求に対応するには、現時点でもマイクロソフト アクセスは有効なツールだ。
ところが、案の定、わけのわからないエラーが発生してしまった。サーバ上のPostgreSQLとOracleのテーブルをリンクしていたMDBを元に、ローカルのアクセスデータベースに切り替えたとたんに、「プロシージャの呼び出し、または引数が不正です」というエラーが出る。システムの起動直後にログイン画面を表示しようとすると、「プロシージャの呼び出し、または引数が不正です」というエラーだ。MDBの作成直後から出ることもあれば、一回目は正常に動作し、二回目以降にエラーとなることもある。もちろんプログラムの中身は一切変更していない。テーブルをアクセスのJetに変えただけだ。当初アクセスのJetで作ったMDBをPostgreSQL、Oracleに変更したMDBを元に戻しただけだ。
⇒
奇怪だが、良くあること の続きを読む
ExcelからDBのデータを取得する
データベースのデータをExcelで加工できれば、帳票や分析作業をExcelに任せることができて便利だ。
実現方法はいろいろある。かつてはHTML(今ならxml)形式に出力したテーブルをExcelに取り込んでいた。べたな方法ならCSVに吐き出してExcelに取り込む方法もある。
Excelデータベースクエリの設定
ここではExcelの外部データの取り込みの中からデータベースクエリで取り込む方法を採用した。
ExcelにMicrosoftクエリがインストールされていること。標準インストールではクエリは設定されていない。データ＞外部データの取り込みを選択する。インストール画面が出たら、インストールCDをセットしてしばらく待つ。
Excelデータベースクエリを使う
設定が終われば、データベースクエリが使える。データ＞外部データ＞データベースクエリを選択する。ODBC＞テーブル＞フィールドと選択する。範囲抽出条件や並び順も指定できる。
Excelで抽出条件を可変的に指定する
範囲抽出条件がミソ。使うたびに条件がExcel側で変更できなければ実用上意味がないからだ。
データ＞クエリの編集を選択して、クエリ画面を開き、おなじみのACCESS風クエリ画面で条件をパラメータにする。between [開始番号] and [終了番号] など。
この状態で、ファイル＞実行結果をExcelに返す を実行してもExcelに反映されているが、毎度こんな操作をするわけにはいかない。クエリにパラメータを設定したら、Excelでデータ＞パラメータを選択し、設定したパラメータの値をどこから取得するかを指定する。セルの値を選択し、Excelのどこかのセルを指定して、セルの値が変更されたらデータを更新にチェック。
これでExcelを開いたときは前回保存されたパラメータで抽出し、指定のセルの値を変更すると、即座に指定値で再クエリが実行され、データを取得しなおす。サーバに負荷をかけないためには、Excelを開いたときや、セルの値を変えたときではなく、明示的に取得したほうが良いだろう。
Excelとデータベースを連動して
これでデータの管理は本格DBで、データの加工、印刷、分析はExcelで という使い分けができる。Excelに印刷レイアウトを設定しておけば、システムで帳票を作ることもない。非定型業務もExcelに任せれば、使う側も、作る側も楽になる。
以上、Excel2000,2003で確認済み。2007は当分使わないので未確認。
表計算ソフトExcelにもDB同様データ型の定義があった
表計算ソフトとしてソロバン代わりに使われているマイクロソフトのエクセル（Excel）だが、現在では単なる表計算だけでなく、業務システムの集計・印刷加工のためのツールとしての使い方も広まっている。
入力したデータをAccessやPostgreSQL,MySQL,Oracleなどのデータベースに格納し、クエリーなどプログラム処理で抽出した後、印刷や集計をExcelに任せるという使い方だ。プログラムで集計処理や印刷レイアウトを実行するのに比べて開発工数が圧倒的に削減できる。加えてユーザ側に処理を委ねることで非定型処理などへの対応が柔軟に行えるメリットもある。
ところがDBからExcelにデータを引き渡す際に困ったことが起こってしまう。
Excelとデータベースの連動時に発生する困ったこと
当然のことながらデータベースは各フィールドごとに文字型・数値型・日付型・論理型などのデータ型を厳密に定義している。DBから抽出したデータもDBの定義に従ったデータ型に適合している。ところがこれらのデータをExcelで受け取ると予想外のデータ型として処理されてしまう。
DBの数量や単価のデータは数値型で定義されている。このデータをExcelに渡す。Excelでの表示は 12,345.78 などのカンマ編集付きの書式を設定する。これで大丈夫のハズだが・・・
数値型のデータが文字列に化けた！
ところが一定行（実験では４０５６行）以降のデータが文字列として表示、認識されている。本来なら数値なので右詰めで12,345.78 と表示されるハズのところが、左詰めの表示となり、このデータを使ってDBを更新すると、データ型違反のSQLエラーが発生する。
DBから抽出したデータなので数値型以外のデータは存在しないにもかかわらず、何回読み込み直しても、書式を再設定しても、結果は同じである。
どうもExcelは読み込み時に一定行までしか実データを検査していないようだ。しかも一定行以降は文字として処理を強行している。困ったことだ。
問題の回避策が見つかった
困ってばかりもいられないのでネットで検索すると下記のサイトが見つかった。
Excel ワークシートをリンクする際のデータ型問題を回避する方法
このサイトではExcelのシートをAccessのリンクテーブルとして使用する際の不都合だが、原因は同じだ。
Excelにもデータ型の指定があった
詳細は上記サイトを見ていただくとして、佐藤が理解したところを簡単にまとめると
- 実はExcelにもデータ型という概念がある
- データ型と書式はまったく別のモノである
- データ型は入力時あるいは確定時に決定される
- セルの値が確定した時点でExcelが入力値からデータ型を決定する
- 従って、いくら書式を設定してもデータ型には影響ない
- つまりExcelが一旦文字型だと決定したら書式設定では数値型には戻らない
ここからは佐藤の実験による補足
- 他のDBからデータを取り込む際にExcelは一定行までしかデータを検査しない
- Excelは一定行以降は文字型として取り込みを強行する
一定行以降のデータが勝手に型変更されてしまったらデータ加工用のツールとしてはExcelは使えない！！！
Excelによるデータ型の一方的な変更に対する回避策
上記サイトによると、『何とExcelでもデータ型が指定できる！』 らしい。
Excelのデータメニューから文字区切り（？？）を選択し、ウィザードで標準（＝数値）をチェックして確定すると、指定のセルの値が数値型として処理されるようになった！！
表示も右詰めの 12,345.67 になり、データベースへの更新でもエラーは起こらない。
しかし、何で 区切り文字 の設定でデータ型を指定しなければならないのか？ 発想が理解できない。
Excelをデータ加工ツールとして活用する場合の注意事項
データベースからデータを取り込んだ後、Excelによる勝手なデータ型の設定を打ち消すために、セル範囲を指定して、区切り文字ウィザードで数値型なら標準を、文字型なら文字を指定しておくこと。
上記の指定は数値型のセルすべてに対して行う必要がある。結構面倒で時間がかかる。こんなことをするよりExcelが取り込んだデータすべてを検査するか、一定行で検査を打ち切るなら以降は同じデータ型にしておいてくれれば問題はないのだが・・・。Office2007に期待するしかないか。
一昨日の講演会の後、指定のRSSを取得して一覧表示するプログラムの話で盛り上がった
AJAXで実現したよ などと言ってしまったが、見つからない！！！ 知的情報管理が不十分だった。
とりあえず Java で実現した例を示す。このブログのRSSを取得して表示します。（表示形式が汚いのはご容赦）
複眼ブログのRSSを取得して表示するページ
Linuxはプリインストール済み
毎日使ってはいますが、新しいシステムの開発で必要になったため、新規にLinuxサーバを購入しました。客先の意向でRedHatES4のプリインストールモデルになりました。
以前は自分でインストールしていました。プリインストールではどんな設定でインストールされているのか？ おっかなビックリで起動してみると、Linuxとしてはちゃんと動きます。しかもＧＵＩです。
ディスクは３６ＧＢを基準に適当にパーティションが切られていました。
データベースサーバとして使う
あとはPostgreSQLをインストールして、ＤＢサーバとして使えるようにすればＯＫです。
ディスク上にRPMがあるので、インストール画面でチェックを入れて更新するだけです。簡単です。Linuxもずいぶんとっつきやすくなったものです。
ところが
postgresユーザを作り、ＤＢをinitしようとすると・・・
⇒
久しぶりにＬｉｎｕｘ上にPostgreSQLをインストールしました の続きを読む
