[delphi-users:4834] fIREBIRD2.5 あいまい検索ができない 関数 upper で エラーについて

[弥栄]

いつも大変お世話になります。大須賀と申します。

現在開発環境　Firebird_1.5　IBX、 Delphi XE8,　Win10　64bitです。
今回　FB_1.5 → FB_2_5　 FBConverterで移行しました。

FB_1_5．FDB　
Default　Character　set: SJIS_0208
lc_cｔｙpe=SJIS_0208

FB_2_5．FDB
Default　Character　set: SJIS_0208
lc_cｔｙpe=SJIS_0208

データ検索で全角半角混在を検索したいです。
FB1.5では、問題なく検索ができておりました。

しかし、FB2.5では、
aritjmetic exception, numeric overflow, or string truncation
cannot transliterate characterset between character sets;
のエラーが発生してしまい検索できません。

Table　Jyuushodata
　RECNO: SMALLINT Not Null
MEMO: Varchar(60) Nullable // 全角半角 混在
　　・
　 ・
　 ・　

\FB25\isql
SET NAMES SJIS_0208;
connect "FB_2_5.FDB" user '****' password ****;

検索
SELECT recno, upper(memo) from Jyuushodata;
FB_1_5 では実行出来ていた upper(memo) が
FB_2_5 では エラー が発生します。
半角 大文字・小文字を区別しない「あいまい検索」ができません。

過去ログ等、自分なりに調査しましたが、解決出来ず今回
相談させていただきました。キャラクターに関する知識不足で
FB2.5からは、厳格になったとのことで、解決方法お教えいただきたく
御願いいたします。




MLホームページ: https://www.freeml.com/delphi-users

----------------------------------------------------------------------
Powered by freeml -- https://www.freeml.com/ --
------------------------------------------------------[freeml byGMO]--

あなたの街のチラシがいつでも無料で見放題！
チラシをクリックしてチラシが拡大されたらポイントゲット♪
まずはかんたん登録♪　-ポイントタウン-
https://www.pointtown.com/ptu/rd.cgi?cid=8912
----------------------------------------------------------------------

[トム猫（はやしつとむ）]

はやしです。

Firebirdの話なので出てきました。

Firebird.2.5.4でSJIS_0208のデータベースを作って、以下のSQLをFlameRobinで実行すると問題なく実行出来ます。

select upper('あいうえおｱｲｳｴｵｯ')
from rdb$database

あいうえおｱｲｳｴｵｯ

なので、upper()や半角に問題があるのでなく、データ側に問題があるのではないでしょうか？

特に問題が起きそうなのは、FB1.5でSJIS_0208サーバーとSJIS_0208クライアントの関係でINSERTされたデータは、コード変換もチェックも行われていなかったのでSJIS_0208外の文字が入っている場合に、移行後エラーとなります。

ですので、FB1.5でSJIS_0208だったデータをFB2.5へ移すのであれば移行先はUTF-8にした方がよいと思います。厳密なSJIS_0208だと㈱や㈲などもすべて弾かれますので。

参考：山本隆さんのブログ
https://www.gesource.jp/weblog/?p=4943

よろしくお願いいたします。

+++++++++++++++++++++++++++++++

From Hayashi "tomneko" tsutomu
Mail: tomn...@gmail.com
Web: http://www.tomnekosoft.com

+++++++++++++++++++++++++++++++


2018年2月6日 8:32 弥栄 <delphi...@freeml.com>:

[弥栄]

いつも大変お世話になります。大須賀と申します。
林様早速の返答ありがとうございます。徹底入門で勉強させていただいています。

現在環境追加　FIREBIRD-2.5.0.26074　win32bit　（FIREBIRDユーサ゛ー会よりタ゛ウンロート゛したもの）
使用OS　WINDOWS10　64bit　です。

下記プログラムでSQL内のUPPER関数を外した場合は、文字検索が出来ました。
UPPERを入れた場合、エラーが発生してしまいます。
（FIREBIRD2.5.4で試していない為、申し訳ありません。）
お手数おかけいたしますが、是非お教え願います。


procedure TMainForm.MemoKensaku;
var
Chr : array [0..255] of char;
begin
if MainForm.GetMemoKensaku.Text <> '' then
begin

MainForm.KensakuHouhou := 'MemoKensaku';

MainForm.mMemokensaku := Trim(AnsiUpperCase(MainForm.GetMemokensaku.Text));

//全角を半角に変換する例
Windows.LCMapString(
GetUserDefaultLCID(),
LCMAP_HALFWIDTH,
PChar(mMemokensaku), //変換する文字列
Length(mMemokensaku) + 1, //サイズ
chr, //変換結果
Sizeof(chr) //サイズ
);
MainForm.mHankakuMemokensaku := Chr;

//WINDOWS API 半角を全角に変換する
Windows.LCMapString(
GetUserDefaultLCID(),
LCMAP_FULLWIDTH,
PChar(mMemokensaku),
Length(mMemokensaku) + 1,
Chr,
Sizeof(chr)
);
MainForm.mZenkakuMemokensaku := chr;

//**********************************************************************
DM.IBJyuushoDataQry.Close;
DM.IBJyuushoDataQry.SQL.Clear;
DM.IBJyuushoDataQry.SQL.Add('SELECT * FROM JyuushoData ');
DM.IBjyuushoDataQry.SQL.Add('WHERE (Upper(Memo) Like :Memo1) or (Upper(Memo) Like :Memo2) ');
DM.IBJyuushoDataQry.SQL.Add('ORDER BY Code ');
DM.IBJyuushoDataQry.ParamByName('Memo1').AsString := '%'+mHankakuMemokensaku+'%';
DM.IBJyuushoDataQry.ParamByName('Memo2').AsString := '%'+mZenkakuMemokensaku+'%';
DM.IBJyuushoDataQry.Open;
//**********************************************************************

if not DM.IBJyuushoDataQry.Eof then
begin
MainForm.DBGrid1.SetFocus;
end
else
begin
ShowMessage('メモの登録がありません' +#13#13 +' ');
MainForm.ShuuryouButton.SetFocus;
end;
//endif
end
else
begin
MainForm.ShuuryouButton.SetFocus;
end;
//endif
end;


また、FBconverter-1.0.0.12　32bit　のバイナリをダウンロードしてFirebird2.5にバージョンアップ
をトライしております。
Firebird1.5　SJIS0208　→　Firebird2.5 UTF-8　
Firebird2.5　SJIS0208（今回変換したデータベース）　→　Firebird2.5　UTF-8
では、ｆｌａｍｅRobinで確認したことろ、文字化けしてしまい全く変換できませんでした。

[トム猫（はやしつとむ）]

はやしです。

＞IBJyuushoDataQry.

こちらは何のコンポーネントでしょうか？IBXですか？

もしIBXならFirebird2.5とは互換性がないので、FireDACとかdbExpressとかをお使いください。

また、Firebird2.5の最新版は2.5.8ですので、まずは最新版をお試しください。

https://firebirdsql.org/en/firebird-2-5-8/

FBConverterの方も最新版は、1.0.5です。こちらからご利用ください。

https://github.com/tomneko/fbconverter/releases

それから、根本的なことですが、そもそもなんで upper() が必要なのですか？これアルファベット系だけですよね？半角とか全角とかと関係ないんですが。

+++++++++++++++++++++++++++++++

From Hayashi "tomneko" tsutomu
Mail: tomn...@gmail.com
Web: http://www.tomnekosoft.com

+++++++++++++++++++++++++++++++

2018年2月6日 12:26 弥栄 <delphi...@freeml.com>:

[弥栄]

大須賀と申します。
林様皆様、助言大変ありがとうございます。

私たちは、小規模企業で、現在Firebird　、　delpphi、　IBXに大きく依存しております。
ミドルウエアの変更には知識不足及びアプリケーション数が多いため時間が掛かり
そうです。

FIREBIRD1.5は安定しており、恥ずかしながら、バージョンアップを怠って参りました。

今回、林様にお教えいただきました。

Firebird2.5の最新版は2.5.8
FBConverterの方も最新版、1.0.5　を使用して再度FIREBIRD1.5の
コンバートに挑戦します。
（Firebird2.1にハ゛ーシ゛ョンアッフ゜でも検討したいと思います。）

また、アルファベット系の検索ですが会社名等検索で（Firebird、Delphi）等
大文字と、小文字の混在を大文字にして検索を行っていました。

何か、この方法以外に全角、半角、大文字、小文字を検索する方法ご有りましたら
お教えください。

> > DM.IBJyuushoDataQry.SQL.Add(SELECT * FROM JyuushoData );
> > DM.IBjyuushoDataQry.SQL.Add(WHERE (Upper(Memo) Like :Memo1) or (Upper(Memo) Like :Memo2) );
> > DM.IBJyuushoDataQry.SQL.Add(ORDER BY Code );
> > DM.IBJyuushoDataQry.ParamByName(Memo1).AsString := %+mHankakuMemokensaku+%;
> > DM.IBJyuushoDataQry.ParamByName(Memo2).AsString := %+mZenkakuMemokensaku+%;

> > DM.IBJyuushoDataQry.Open;
> > //**********************************************************************
> >
> > if not DM.IBJyuushoDataQry.Eof then
> > begin
> > MainForm.DBGrid1.SetFocus;
> > end
> > else
> > begin

> > ShowMessage(メモの登録がありません +#13#13 + );

[トム猫（はやしつとむ）]

はやしです。

Firebird1.5から2.5へ切り替える理由はなんなのでしょう？

ミドルウェアを変更する程度の開発力もないのであれば、DBの変更などしない方がよいですよ。

無理にバージョンアップしなくても、小規模ならパフォーマンスなんて問題にならないでしょうし、変更する意味がありません。

一般に、DBを別のDBに変更するのは一からアプリ開発するくらいの体力がいりますが、バージョンアップにしてもミドルウェアや開発環境の変更を伴えば、やはりヘビーです。

やるのであれば、それなりに努力をして自力でなんとかするか、そうでなければ出来る会社に発注した方がよいですよ。

中小だから、人が少ないから、出来る人がいないから、すべて言い訳です。それを選択したのはみなさんなわけですし、こうしないとまずいですよとここでお話しても「そんなことまで出来ません」とおっしゃられたら、答える意味もありません。

出来ないならやめましょう。外注すべきです。

以上

+++++++++++++++++++++++++++++++

From Hayashi "tomneko" tsutomu
Mail: tomn...@gmail.com
Web: http://www.tomnekosoft.com

+++++++++++++++++++++++++++++++

2018年2月6日 14:59 弥栄 <delphi...@freeml.com>:

[DEKO]

こんにちは。

> Firebird 2.5 UTF-8

IBX はそのままですと UTF-8 を正しく処理できません。
"UNICODE_FSS" を使うか IBX の改変が必要です。

[IBX (InterBase Express) で Firebird を使う (Qiita)]
https://qiita.com/ht_deko/items/a51df59c34a6c859296b

# 本当なら Firebird に正式対応している
# DBX や FireDAC をつかうべきだと思います。


> 大文字 / 小文字

Firebird DB の文字セットが "NONE" の場合、
文字照合順序は期待した通りになりません。

# 最悪、UDF を作るというテがない訳じゃありませんが...

> aritjmetic exception, numeric overflow, or string truncation
> cannot transliterate characterset between character sets;

Firebird は...というか InterBase もですが、
VARCHAR(60) のフィールドに 70文字を格納できたりします。

このようなサイズを超えて格納された文字列にアクセスすると先述のエラーが発生します。
簡単に検証するには、DB のバックアップを取った上で
ALTER TABLE を使いフィールドのサイズを２倍に拡張してみる事です。
これでエラーが出ないようでしたら、元 DB には範囲を超えて格納されたデータが存在します。

また、DB の文字コードを UTF-8 にした場合にも、
"IBX を改変しない限り" 上記のエラーが発生します (Qiita の記事を参照してください)。


> FBConverter

FBConverter は異なる文字コード間で DB 変換を行った場合、
VARCHAR / CHAR の文字数を自動で拡張します。

しかしながら、SJIS_0208 で VARCHAR(255) のものを
UTF-8 にしようとしても VARCHAR(756) とかには拡張できないため、
VARCHAR(255) のままとなります。

文字コード変換の際、変換後の文字列で書き込むので、
実際には格納が足りなかったとしても、データは書き込まれてしまいます。
VARCHAR(255) に 300 文字とかがあり得ます。

こういう事例はコンバーターでは対処できません。
VARCHAR(255) フィールドを BLOB SUB_TYPE TEXT にした
DB を新たに作って移行すべきだと思います。
(まぁ、BLOB は BLOB で文字コードを保持しないので厄介ですが...)

--
富永英明 (DEKO)
-----------------------------
http://ht-deko.com/
de...@ht-deko.minim.ne.jp
-----------------------------

[DEKO]

> LIKE

例えば VARCHAR(10) のフィールドを "%検索文字列%" であいまい検索すると、
Firebird 2.x では失敗する事があったと思います (IBX)。

これは VARCHAR(10) のフィールドに 10 文字キッチリ
格納されていて、検索文字列も 10 文字の場合に発生します。

# 対処方法は ALTER TABLE で 前後 % 分の２文字を増やし、
# 文字列は VARCHAR - 2 で書き込むようにする事です。

[弥栄]

大須賀と申します。
林様貴重な助言いただき大変ありがとうございます。

windows10　Fall　Creaters　UpdateでFirebird1.5が削除されました。
（結果的に再インストールが可能で、現在再インストールさせていただきました）
この件により、Firebird2.5への移行を決断しました。

おっしゃる通り、ｄｂExpress　（DBX）へのミドルウェアの変更に挑戦したいと思います。
恥ずかしい発言大変申し訳有りませんでした。

DEKO様

ご助言いただき大変ありがとうございました。
早速、文字数をALTER　TABLEにて文字数を増やして、再度検索を行います。

http://okamerin.com/nc/title/Firebird_upgrade_to2.5_p2.htm
記事を読ませていただき、同様のエラーがあるのでは無いかと思っておりましたが
解決が書いていなかった為、困っておりました。

>例えば VARCHAR(10) のフィールドを "%検索文字列%" であいまい検索すると、
>Firebird 2.x では失敗する事があったと思います (IBX)。

>これは VARCHAR(10) のフィールドに 10 文字キッチリ
>格納されていて、検索文字列も 10 文字の場合に発生します。

># 対処方法は ALTER TABLE で 前後 % 分の２文字を増やし、
># 文字列は VARCHAR - 2 で書き込むようにする事です。