大西彰のウェブログ

UTF-8対応Webアプリケーションの構築：　第２回（MSDE2000）

UTF-8対応Webアプリケーションの構築の記事の第２回は、MSDE2000(以下、MSDE)を取り上げたいと思います。
SQL Server 2000(以下、SQL Server)を使っている人なら、MSDEを使いこなすのは簡単！？と思いきや、GUIツールのないMSDEは、知らないとセットアップの段階から躓くことになります。今回は、UnicodeをMSDEで扱う方法を取り上げますが、MSDEのインストール上の注意点なども取り上げてみたいと思います。
前回の話で、UTF-8の入出力をWebブラウザとASPの間で行った場合、ASP側でUTF-8がUCS-2に変換されることを説明しました。順序的には、データベースの手前のビジネスコンポーネントの話を取り上げると良いのですが、意図的にデータベースの話を２回目に持ってきています。
あまり知られていない（というか意識する人が少ない）と思いますが、MSDEやSQL Serverは、クエリー可能な列に対してUTF-8文字列を格納することができません。要するにバイナリデータとして持たせる分には問題ないのですが、検索目的ではUTF-8は使えません。このため、MSDEあるいはSQL ServerにUnicodeを格納する場合は、必然的にUCS-2のUnicodeで格納する必要があります。Windowsの世界でUnicodeといえば、UCS-2を指すのですが、インターネットなどシステムの相互運用の世界においてUnicodeといった場合、必ずしもUCS-2というコンテキストにはなりません。特に異（機種）システムの通信の際は、UTF-8やUTF-16に変換した結果を利用する必要があります。XMLがUTF-8やUTF-16をサポートしているのはこのためだと思います。エンドユーザにしてみれば「正しく」読み書きできていれば内部コードはどうでもいい話です。しかしながら、システムを設計・実装する人にとって文字コードの内部表現・外部表現というのは無視できるものではありません。ただし、特定言語で閉じた世界であれば、その特定言語に対応したＯＳ・ミドルウェア・データベース・開発ツールを使ってシステムが実装されてしまえば、この文字コードの問題はそれほどシビアに考えるものではありません。
さて、MSDE(SQL Server)でUnicodeを取り扱う場合は、それほど難しいことはありません。MSDEでサポートされるUnicodeデータ型は、次の３種類です。

・nchar(文字型)
・nvarchar(可変長文字型)
・ntext(テキスト型)

に変換すればいいのです。
しかし、ここには落とし穴があります。charとvarcharは8000文字まで取り扱えますが、ncharとnvarcharは4000文字しか取り扱えないのです。したがって、DDLを書く際にnvarchar(5000)といった宣言はできません。それともうひとつ落とし穴があります。MSDEの１行あたりの最大バイト数は英語版と日本語版では異なるということです。この記事を読んでいる人がこの問題にぶつかることは稀だと思いますが、私はモロにこの問題にぶち当たりました。正確な数字かどうかは確認が必要ですが、
英語版：　　最大長8,755バイト
日本語版：　最大長8,066バイト
だったと私の紙のノートに書いてありました。これが正しいとすると、日本語版MSDEでnvarchar(4000)などという列を持ったテーブルは、残り66バイトしか使えないのです。ちなみにSQL ServerのBooks Onlineを見ると、行ごとのバイト数は「8,060」と書いてあります。うーむ・・・。なんにせよ、もし英語版で１行が8,060バイトを超えるテーブルのスキーマがあってそれを日本語版に移植すると、どこかで妥協が必要になります。また、英語版で英語対応からUnicode対応にする場合も、varcharで宣言されている文字数は妥協を強いられます。私の場合、nvarchar(5000)なんてスキーマがあったものでしたから、「5,000文字もいらないだろう。3,000でいいや（^^ヾ」と妥協した記憶があります。実際に考えていただければわかりますが、たとえば、3,000文字は、400×7+200。つまり400字の原稿用紙に換算すれば７枚半です。業務系のアプリケーションでそんな大量の文字を入力してクエリする必要が本当にあるんでしょうか・・・。全文検索なら別の方法を考えるべきだと・・・。
話は戻り、UTF-8対応Webアプリケーションを構築するにあたり、バックエンドのMSDEでは、nchar、nvarchar、ntextの何れかを使って文字列を格納する必要があります。
たとえば、Unicodeキーワードとメモを格納する簡単なデータベースを考えてみたいと思います。テーブルをutextとした場合、定義は次のようになります。

create table utext (
 Keyword nvarchar(30),
 note ntext
)

このutextに対して、選択・追加・更新・削除を行うストアドプロシージャを考えて見ましょう。現在、使っているデータベース名がuni01と仮定します。Transact SQLを見慣れていないと「何じゃこりゃ！」となるかもしれませんが、AS句の後のSQL文さえご覧いただければ何がしたいか理解していただけると思います。

---

-- 全選択
use uni01
go
if exists (select Null from sysobjects where id = object_id('spUtextAll'))
 drop procedure dbo.spUtextInfo
GO
Create Procedure spUtextAll
With Encryption
AS
SELECT Keyword, note
FROM  Utext
Return 0
Go

---

-- キーワードで選択
use uni01
go
if exists (select Null from sysobjects where id = object_id('spUtextInfo'))
 drop procedure dbo.spUtextInfo
GO
Create Procedure spUtextInfo
 @UtextID nvarchar(30)
With Encryption
AS
SELECT Keyword, note
FROM  Utext
where Keyword = @UtextID
Return 0
Go

---

-- 追加
use uni01
go
if exists (select Null from sysobjects where id = object_id('spUtextInsert'))
 drop procedure spUtextInsert
GO
CREATE Procedure spUtextInsert
 @Keyword nvarchar(30),
 @note ntext
With Encryption
AS
insert Utext
( Keyword,
 note
)
values
( @Keyword,
 @note
)
Return 0
GO

---

-- キーワードで更新
use uni01
go
if exists (select Null from sysobjects where id = object_id('spUtextUpdate'))
 drop procedure spUtextUpdate
GO
CREATE  Procedure spUtextUpdate
 @Keyword nvarchar(30),
 @note ntext
With Encryption
AS
Update Utext
Set  
 Keyword = @Keyword,
 note = @note
Where Keyword = @Keyword
Return 0
Go

---

-- キーワードで削除
use uni01
go
if exists (select Null from sysobjects where id = object_id('spUtextDelete'))
 drop procedure spUtextDelete
GO
Create Procedure spUtextDelete
 @Keyword nvarchar(30)
With Encryption
AS
Delete From Utext
Where Keyword = @Keyword
Return 0
Go

・・・

ちなみに、これらのストアドプロシージャの呼び出しにあたり、文字列リテラルを指定する場合は、Nプレフィックスが必要になります。Nプレフィックスがないと・・・文字が化けますよー。

・MSDEの管理コンソールについて
MSDEには、SQL Serverのような立派なGUI管理アプリケーションは付属しません。Osqlと呼ばれるODBCアプリケーション(!)が唯一の対話手段です。Osqlユーティリティについては、KB325003 に詳しく書かれていますので、ご一読されることをお勧めします。なんにせよODBCは生きている、なぜにOLE DBアプリケーションでないのでしょうか。ODBC好きの私にはうれしいことなのですが、ADODBから入ってきている人にはなんのこっちゃ、という感じではないでしょうか。コンソールで対話することに慣れている世代からすれば不思議でもないのですが、Enterprise ManagerやQuery Analyzerに慣れきっている人がOsqlを触ると、最初は「面倒だなぁ」と思われるかもしれません。CUIベースとはいえ、データベースの作成、テーブルやストアドプロシージャの作成、バックアップなど、管理作業が実行できます。バッチ処理をする場合には最適かも。

投稿日時 : 2004年9月21日 16:10