delphi mysql blob_有用MyDAC连MySQL的Text或BLOB遭遇32KB文本超大难题的吗？

有用MyDAC连MySQL的Text或BLOB遭遇32KB文本超大难题的吗？ Delphi / Windows SDK/API

http://www.delphi2007.net/DelphiDB/html/delphi_20061221030445196.html

编一个资料库系统，客户强调采用UTF-8编码。

以配合以后的Web系统。

结果发现只有MyDAC支持UTF-8和Unicode。

但是在开发过程中发现，Memo或RichEdit中，

超过32KB的文本，无法存入MySQL数据库，

字段类型无论采用Text,Longtext,blob,longblob都不成。

怀疑是MyDAC本身的问题，用其附带的Text例子，也一样不能超出32KB。

特征解决方案。

试试最新版本的

我试过没问题

1.建表:

Create   table   t1   (a   longtest);

2.写数据:

procedure   TForm1.Button1Click(Sender:   TObject);

begin

with   mySQLQuery1   do

begin

SQL.Text:='insert   into   t1   values(:ltext)';

Params.ParamByName('ltext').LoadFromFile('D:\t.txt',ftMemo);   //t.txt为82K文本文件

ExecSQL;

end;

end;

3.读数据:

var

t:TMemoryStream;

begin

t:=TMemoryStream.Create;

with   mySQLQuery1   do

begin

SQL.Text:='select   *   from   t1';

Open;

(FieldByName('a')   as   TMemoField).SaveToStream(t);

t.Position:=0;

Memo1.Lines.LoadFromStream(t);

end;

t.Free;

end;

我采用的是Table.Append，结果就是没有通过。没想到去试Query。

因为columns太多了，用query太麻烦。

用的MyDAC是MySQL   Data   Access   Components   4.4

不是Scibit   MyComponent，

Scibit   MyComponent对UTF-8支持不行。

用Query还是一样啊，

Access   violation   at   address   00403287   in   module   'DBMan.exe',   Read   of   address   001338B4

PS.

MySQL   Server是   5.0版的，测试服务器是Win32版本的，

会不会有问题？

应该与版本没关系(我也用5.0的).

开发C/S结构,除桌面数据库以外(fox/access等)尽量不要用table的方法.要用sql来对数据库操作,对于longtext/blob字段,要用Stream方式

你的

Access   violation   at   address   00403287   in   module   'DBMan.exe',   Read   of   address   001338B4

错,要查你的程序是否有内存溢出

另外,你可先写一段测试程序(如我上面的)看看是否为控件的问题

我只用Table作Append，

Update和Del都用Query。

因为column太多了。

试过了，也一样。

看来是控件的问题。

MySQL   Data   Access   Components   4.4

自带的Demos，

用DBGrid+DBNav，写入也是一样的错误。

Demo源码：

Main.dfm

object   fmMain:   TfmMain

Left   =   245

Top   =   151

Caption   =   'MySQL   Data   Access   Demo   -   TEXT   fields'

ClientHeight   =   410

ClientWidth   =   593

Color   =   clBtnFace

Font.Charset   =   DEFAULT_CHARSET

Font.Color   =   clWindowText

Font.Height   =   -11

Font.Name   =   'MS   Sans   Serif'

Font.Style   =   []

OldCreateOrder   =   True

Position   =   poScreenCenter

PixelsPerInch   =   96

TextHeight   =   13

object   Splitter1:   TSplitter

Left   =   0

Top   =   233

Width   =   593

Height   =   2

Cursor   =   crVSplit

Align   =   alTop

end

object   DBGrid:   TDBGrid

Left   =   0

Top   =   25

Width   =   593

Height   =   182

Align   =   alTop

DataSource   =   DataSource

TabOrder   =   0

TitleFont.Charset   =   DEFAULT_CHARSET

TitleFont.Color   =   clWindowText

TitleFont.Height   =   -11

TitleFont.Name   =   'MS   Sans   Serif'

TitleFont.Style   =   []

end

object   ToolBar:   TPanel

Left   =   0

Top   =   0

Width   =   593

Height   =   25

Align   =   alTop

TabOrder   =   1

object   ToolButton1:   TToolButton

Left   =   362

Top   =   2

Width   =   16

Caption   =   'ToolButton1'

Style   =   tbsSeparator

end

object   btOpen:   TButton

Left   =   0

Top   =   2

Width   =   70

Height   =   22

Caption   =   'Open'

TabOrder   =   0

OnClick   =   btOpenClick

end

object   btClose:   TButton

Left   =   70

Top   =   2

Width   =   69

Height   =   22

Caption   =   'Close'

TabOrder   =   2

OnClick   =   btCloseClick

end

object   DBNavigator:   TDBNavigator

Left   =   139

Top   =   2

Width   =   220

Height   =   22

DataSource   =   DataSource

TabOrder   =   1

end

object   btCreate:   TButton

Left   =   451

Top   =   2

Width   =   69

Height   =   22

Caption   =   'Create'

Font.Charset   =   DEFAULT_CHARSET

Font.Color   =   clWindowText

Font.Height   =   -9

Font.Name   =   'MS   Sans   Serif'

Font.Style   =   [fsBold]

ParentFont   =   False

TabOrder   =   3

OnClick   =   btCreateClick

end

object   btDrop:   TButton

Left   =   520

Top   =   2

Width   =   70

Height   =   22

Caption   =   'Drop'

Font.Charset   =   DEFAULT_CHARSET

Font.Color   =   clWindowText

Font.Height   =   -9

Font.Name   =   'MS   Sans   Serif'

Font.Style   =   [fsBold]

ParentFont   =   False

TabOrder   =   4

OnClick   =   btDropClick

end

end

object   meComments:   TDBMemo

Left   =   0

Top   =   235

Width   =   593

Height   =   175

Align   =   alClient

DataField   =   'TextField'

DataSource   =   DataSource

MaxLength   =   65535

ScrollBars   =   ssBoth

TabOrder   =   2

end

object   ToolBar1:   TToolBar

Left   =   0

Top   =   207

Width   =   593

Height   =   26

ButtonHeight   =   24

EdgeInner   =   esNone

EdgeOuter   =   esNone

TabOrder   =   3

object   btLoad:   TButton

Left   =   0

Top   =   0

Width   =   75

Height   =   24

Caption   =   'Load   from   file'

TabOrder   =   0

OnClick   =   btLoadClick

end

object   btSave:   TButton

Left   =   75

Top   =   0

Width   =   75

Height   =   24

Caption   =   'Save   to   file'

TabOrder   =   1

OnClick   =   btSaveClick

end

object   ToolButton2:   TToolButton

Left   =   150

Top   =   0

Width   =   25

Caption   =   'ToolButton2'

Style   =   tbsSeparator

end

object   btClear:   TButton

Left   =   175

Top   =   0

Width   =   75

Height   =   24

Caption   =   'Clear'

TabOrder   =   2

OnClick   =   btClearClick

end

end

object   MyConnection:   TMyConnection

Database   =   'test'

IsolationLevel   =   ilSerializable

Options.UseUnicode   =   True

Options.Charset   =   'utf8'

Options.KeepDesignConnected   =   False

Username   =   'root'

Password   =   'xcvuht8m98qy'

Server   =   'localhost'

ConnectDialog   =   ConnectDialog

LoginPrompt   =   False

Left   =   344

Top   =   32

end

object   DataSource:   TDataSource

DataSet   =   Query

Left   =   440

Top   =   32

end

object   ConnectDialog:   TMyConnectDialog

DatabaseLabel   =   'Database'

PortLabel   =   'Port'

SavePassword   =   True

Caption   =   'Connect'

UsernameLabel   =   'User   Name'

PasswordLabel   =   'Password'

ServerLabel   =   'Server'

ConnectButton   =   'Connect'

CancelButton   =   'Cancel'

Left   =   376

Top   =   32

end

object   Query:   TMyQuery

SQLInsert.Strings   =   (

'INSERT   INTO   MYDAC_Text'

'     (MYDAC_Text.Name,   MYDAC_Text.TextField)'

'VALUES'

'     (:Name,   :TextField)')

SQLDelete.Strings   =   (

'DELETE   FROM   MYDAC_Text'

'WHERE'

'     UID   =   :Old_UID')

SQLUpdate.Strings   =   (

'UPDATE   MYDAC_Text'

'SET'

'     Name   =   :Name,   TextField   =   :TextField'

'WHERE'

'     UID   =   :Old_UID')

SQLRefresh.Strings   =   (

'SELECT   MYDAC_Text.Name,   MYDAC_Text.TextField   FROM   MYDAC_Text'

'WHERE'

'     MYDAC_Text.UID   =   :Old_UID')

Connection   =   MyConnection

SQL.Strings   =   (

'SELECT   *   FROM   MYDAC_Text')

FetchAll   =   True

Left   =   408

Top   =   32

end

object   OpenDialog:   TOpenDialog

Left   =   240

Top   =   259

end

object   SaveDialog:   TSaveDialog

Left   =   272

Top   =   259

end

object   sqCreateTable:   TMyCommand

Connection   =   MyConnection

SQL.Strings   =   (

'CREATE   TABLE   MYDAC_Text   ('

'     UID   INT   NOT   NULL   AUTO_INCREMENT   PRIMARY   KEY,'

'     Name   VARCHAR(50),'

'     TextField   BLOB'

')')

Debug   =   True

Left   =   344

Top   =   68

end

object   sqDropTable:   TMyCommand

Connection   =   MyConnection

SQL.Strings   =   (

'DROP   TABLE   MYDAC_Text')

Debug   =   True

Left   =   380

Top   =   68

end

end

Demo源码：

Main.pas

unit   Main;

interface

uses

{$IFNDEF   LINUX}

Windows,   Messages,   SysUtils,   Classes,   Graphics,   Controls,   Forms,   Dialogs,

DBCtrls,   ExtCtrls,   Grids,   DBGrids,   StdCtrls,   ToolWin,   ComCtrls,   MyDacVcl,

{$ELSE}

SysUtils,   Types,   Classes,   QGraphics,   QControls,   QForms,   QDialogs,   QStdCtrls,

QDBCtrls,   QComCtrls,   QExtCtrls,   QGrids,   QDBGrids,   MyDacClx,

{$ENDIF}

DBAccess,   MyAccess,   DB,   MemDS;

type

TfmMain   =   class(TForm)

MyConnection:   TMyConnection;

DBGrid:   TDBGrid;

DataSource:   TDataSource;

ToolBar:   TPanel;

btOpen:   TButton;

DBNavigator:   TDBNavigator;

btClose:   TButton;

ConnectDialog:   TMyConnectDialog;

meComments:   TDBMemo;

Query:   TMyQuery;

Splitter1:   TSplitter;

ToolBar1:   TToolBar;

btLoad:   TButton;

btSave:   TButton;

OpenDialog:   TOpenDialog;

SaveDialog:   TSaveDialog;

ToolButton1:   TToolButton;

btClear:   TButton;

ToolButton2:   TToolButton;

sqCreateTable:   TMyCommand;

sqDropTable:   TMyCommand;

btCreate:   TButton;

btDrop:   TButton;

procedure   btOpenClick(Sender:   TObject);

procedure   btCloseClick(Sender:   TObject);

procedure   btLoadClick(Sender:   TObject);

procedure   btSaveClick(Sender:   TObject);

procedure   btClearClick(Sender:   TObject);

procedure   btCreateClick(Sender:   TObject);

procedure   btDropClick(Sender:   TObject);

private

{   Private   declarations   }

public

{   Public   declarations   }

end;

var

fmMain:   TfmMain;

implementation

{$R   *.dfm}

procedure   TfmMain.btOpenClick(Sender:   TObject);

begin

Query.Open;

end;

procedure   TfmMain.btCloseClick(Sender:   TObject);

begin

Query.Close;

end;

procedure   TfmMain.btLoadClick(Sender:   TObject);

begin

if   Query.Active   and   OpenDialog.Execute   then   begin

if   Query.State   =   dsBrowse   then

Query.Edit;

TBlobField(Query.FieldByName('TextField')).LoadFromFile(OpenDialog.FileName);

end;

end;

procedure   TfmMain.btSaveClick(Sender:   TObject);

begin

if   not   Query.EOF   and   SaveDialog.Execute   then

TBlobField(Query.FieldByName('TextField')).SaveToFile(SaveDialog.FileName);

end;

procedure   TfmMain.btClearClick(Sender:   TObject);

begin

if   Query.Active   then   begin

if   Query.State   =   dsBrowse   then

Query.Edit;

Query.FieldByName('TextField').Clear;

end;

end;

procedure   TfmMain.btCreateClick(Sender:   TObject);

begin

sqCreateTable.Execute;

end;

procedure   TfmMain.btDropClick(Sender:   TObject);

begin

sqDropTable.Execute;

end;

end.

很奇怪，我用MySQLFont写入的Longtext，读出没问题。

可是就是写入就Fail出那个内存溢出的错。

用UTF-8＋Unicode的选项。