C#EF6からMySQL-BLOBにデータを登録

C# MySQL

最近、肩こりの酷い和朗です。今回は、業務で利用している、MySQLで陥った問題について行った、回避策を書きたいと思います。

環境

要件

  • DBにはBLOB型にファイルを保存する
    • 他システムから利用しているため、BLOB型から変えられない
  • アプリからDBへアクセスはEntity Frameworkで統一したい

発生したエラー

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line X

以下の「db.SaveChanges();」でExceptionとなる

using (var db = new SanwaSystem.XXXXXXEntities())
{
    db.Database.Log = x => System.Diagnostics.Debug.WriteLine(x);
    var data = db.XXXXXXXXX
        .Where(x => x.id == id).First();
    data.note = note;
    if (!string.IsNullOrEmpty(note_file_name))
    {
        data.note_file_name = note_file_name;
        data.note_file_data = note_file_data;
    }
    data.modified = DateTime.Now;
    db.SaveChanges();
}

UPDATEの方法については、SqlCommandを利用してみたりしましたが同様にエラーとなってしまいました。

以下はPHPを利用した場合のエラー。 stackoverflow.com

回避方法

C#から更新する用の別カラム(TEXT型)を設け、byte[]をBase64文字列に変換したうえで保存し、DBのTRIGGERを利用してBLOB型に変換する。

  • BLOB型のサイズに合わせて、TEXT型の別カラムを作成
  • TRIGGERを作成
DELIMITER $$
CREATE TRIGGER XXXXXXXXX_before_insert BEFORE INSERT
 ON XXXXXXXXX FOR EACH ROW
 BEGIN
   #base64からblobに変換
   SET NEW.note_file_data=FROM_BASE64(NEW.note_file_data_base64string);
 END;
$$
CREATE TRIGGER XXXXXXXXX_before_update BEFORE UPDATE
 ON XXXXXXXXX FOR EACH ROW
 BEGIN
   #base64からblobに変換
   SET NEW.note_file_data=FROM_BASE64(NEW.note_file_data_base64string);
 END;
$$
DELIMITER ;
  • C#でbyte[]をBase64文字列に変換しテキストに保存
using (var db = new SanwaSystem.XXXXXXEntities())
{
    db.Database.Log = x => System.Diagnostics.Debug.WriteLine(x);
    var data = db.XXXXXXXXX
        .Where(x => x.id == id).First();
    data.note = note;
    if (!string.IsNullOrEmpty(note_file_name))
    {
        data.note_file_name = note_file_name;
        data.note_file_data_base64string = Convert.ToBase64String(note_file_data);
    }
    data.modified = DateTime.Now;
    db.SaveChanges();
}

注意点

まとめ

MySQLのBLOB型を更新する際のシンタックスエラーの情報は割とWEB上にあるのですが、SQL内に改行を入れて対応しているものや、そもそもBLOB型を利用しないようにしているもので今回の要件では利用できず困っていたのですが、MySQLのFROM_BASE64()を見つけたことでTRIGGERを利用する方向で考えました。ベストプラクティスとはいえませんが、現状の環境においては良い方法だったのではないかと考えています。

もっと良い方法があれば、コメントいただけると勉強になります。