DELPHI XE用zeosdbo连接MYSQL获取字段显示乱码

fsmad 2019-04-04 06:20:31
DELPHI XE10.2，用zeosdbo-7.2.4-stable连接MYSQL数据库，获取字段显示乱码，以下为代码，能想到的转换方式都试过了，就是没一个能正常显示中文，以下为数据库设置、记录、显示乱码的格式和原代码，求各位大侠帮忙：





zqry1.Close;
zqry1.SQL.Text := 'set names UTF8';
zqry1.ExecSQL;

zqry1.SQL.Text := 'SELECT * FROM info_player1';
zqry1.Open;

ShowMessage(UTF8Encode(zqry1.FieldByName('ip').AsString));
ShowMessage(UTF8Decode(zqry1.FieldByName('ip').AsString));
ShowMessage(Utf8ToAnsi(zqry1.FieldByName('ip').AsString));
ShowMessage(UTF8ToString(zqry1.FieldByName('ip').AsString));
ShowMessage(UTF8ToWideString(zqry1.FieldByName('ip').AsString));
ShowMessage(UTF8ToUnicodeString(zqry1.FieldByName('ip').AsString));
ShowMessage(UTF8EncodeToShortString(zqry1.FieldByName('ip').AsString));

ShowMessage(UTF8Encode(zqry1.FieldByName('ip').AsWideString));
ShowMessage(UTF8Decode(zqry1.FieldByName('ip').AsWideString));
ShowMessage(Utf8ToAnsi(zqry1.FieldByName('ip').AsWideString));
ShowMessage(UTF8ToString(zqry1.FieldByName('ip').AsWideString));
ShowMessage(UTF8ToWideString(zqry1.FieldByName('ip').AsWideString));
ShowMessage(UTF8ToUnicodeString(zqry1.FieldByName('ip').AsWideString));
ShowMessage(UTF8EncodeToShortString(zqry1.FieldByName('ip').AsWideString));

ShowMessage(UTF8Encode(zqry1.FieldByName('ip').AsAnsiString));
ShowMessage(UTF8Decode(zqry1.FieldByName('ip').AsAnsiString));
ShowMessage(Utf8ToAnsi(zqry1.FieldByName('ip').AsAnsiString));
ShowMessage(UTF8ToString(zqry1.FieldByName('ip').AsAnsiString));
ShowMessage(UTF8ToWideString(zqry1.FieldByName('ip').AsAnsiString));
ShowMessage(UTF8ToUnicodeString(zqry1.FieldByName('ip').AsAnsiString));
ShowMessage(UTF8EncodeToShortString(zqry1.FieldByName('ip').AsAnsiString));

zqry1.Close;

ps:在DELPHI7中，把zqry1.SQL.Text := 'set names gbk'，然后 ShowMessage(zqry1.FieldByName('ip').AsString);是没有乱码的，XE10刚上手，完全不适应。
...全文
812 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
mihuozhe33 2019-05-22
  • 打赏
  • 举报
 回复
每一种连接方式编码不一样容易乱码，换连接方式试下
大肚肥肥 2019-05-22
  • 打赏
  • 举报
 回复
用TFDConnection把，还可以用连接池，好用的


unit fmDataModule;



interface



uses

  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes,

  Vcl.Graphics, Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Vcl.ExtCtrls,

  MidasLib, System.SyncObjs, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.ERROR,

  FireDAC.UI.Intf, FireDAC.Stan.Def, FireDAC.Stan.Pool, duTypes, FireDAC.Stan.Async,

  Data.DBXCommon, FireDAC.Phys, FireDAC.VCLUI.Wait, Data.DB, FireDAC.Comp.Client,

  Data.SqlExpr, Data.DBXMySQL, Data.DBXMSSQL, Data.FMTBcd, Datasnap.DBClient,

  Datasnap.Provider, FireDAC.Phys.MSSQLDef, FireDAC.Phys.ODBCBase, FireDAC.Phys.MSSQL,

  FireDAC.Phys.Intf, FireDAC.ConsoleUI.Wait, FireDAC.Stan.Param, FireDAC.DatS,

  FireDAC.DApt.Intf, FireDAC.DApt, FireDAC.Phys.MySQLDef, FireDAC.Phys.MySQL,

  FireDAC.Comp.DataSet;



type

  TLockMySQLDataModule = class(TDataModule)

    FDPhysMySQLDriverLink1: TFDPhysMySQLDriverLink;

    FDManager1: TFDManager;

    procedure DataModuleCreate(Sender: TObject);

    procedure DataModuleDestroy(Sender: TObject);

  private

    oParams: TStringList;

  protected



    { Private declarations }

  public

    function GetDate(ASql: string; AClientDataSet: TClientDataSet; var AErrStr: string): Boolean;

    function ExecSQL(ASql: string; var AErrStr: string): Boolean;

    function ExecSqlList(ASqlList: TStrings; var AErrStr: string): Boolean;

    function DBConnectedTest(var AErrStr: string): Boolean;

    procedure WriteHttpLog(Aurl, AContent, ARemoteIP, AErrMsg: string);

    procedure WriteTCPLog(Aurl, AContent, ARemoteIP, AErrMsg: string);



  end;



var

  G_DataModule: TLockMySQLDataModule;



implementation





{%CLASSGROUP 'System.Classes.TPersistent'}



{$R *.dfm}



uses

  duGlob, duTConfigClass;





procedure TLockMySQLDataModule.WriteHttpLog(Aurl, AContent, ARemoteIP, AErrMsg: string);

var

  ASql: string;

  AErrStr: string;

begin

  if Length(AErrMsg) > 200 then

    AErrMsg := Copy(AErrMsg, 1, 200);

  ASql := 'insert into httpErrList (' + 'url,content,errMsg,remoteIP,serverIP,createTime' + ')' + ' values(' + QuotedStr(Aurl) + ',' + QuotedStr(AContent) + ',' + QuotedStr(AErrMsg) + ',' + QuotedStr(ARemoteIP) + ',' + QuotedStr(G_ConfigClass.LocalIP) + ',' + QuotedStr(FormatDateTime('YYYY-MM-DD HH:MM:SS', Now)) + ')';

  if not G_DataModule.ExecSQL(ASql, AErrStr) then

  begin

    WriteStr('写入数据库日志失败：' + ASql);

    WriteStr('写入数据库失败原因：' + AErrStr);

  end;



end;



procedure TLockMySQLDataModule.WriteTCPLog(Aurl, AContent, ARemoteIP, AErrMsg: string);

var

  ASql: string;

  AErrStr: string;

begin

  if Length(AErrMsg) > 200 then

    AErrMsg := Copy(AErrMsg, 1, 200);

  ASql := 'insert into tcpErrList (' + 'url,content,errMsg,remoteIP,serverIP,createTime' + ')' + ' values(' + QuotedStr(Aurl) + ',' + QuotedStr(AContent) + ',' + QuotedStr(AErrMsg) + ',' + QuotedStr(ARemoteIP) + ',' + QuotedStr(G_ConfigClass.LocalIP) + ',' + QuotedStr(FormatDateTime('YYYY-MM-DD HH:MM:SS', Now)) + ')';

  if not G_DataModule.ExecSQL(ASql, AErrStr) then

  begin

    WriteStr('写入数据库日志失败：' + ASql);

    WriteStr('写入数据库失败原因：' + AErrStr);

  end;

end;



procedure TLockMySQLDataModule.DataModuleCreate(Sender: TObject);

begin

 // FDConnection1.LoginPrompt := False;

 // FDGUIxWaitCursor1.Provider := 'Console'; // 此属性的值必须是控制台

  FDPhysMySQLDriverLink1.VendorLib := 'libmysql.dll'; // MYSQL FOR WINDOWS驱动



    //*****初始化*****

  oParams := TStringList.Create;

  //********* 连接池

  oParams.Add('DriverID=MySQL');

  oParams.Add('CharacterSet=utf8');

  oParams.Add('Server=' + G_ConfigClass.DBHost);

  oParams.Add('Port=' + G_ConfigClass.DBPort);

  oParams.Add('Database=btlock');

  oParams.Add('User_Name=' + G_ConfigClass.DBUser);

  oParams.Add('Password=' + G_ConfigClass.DBPass);

    //  毫秒

  oParams.Add('POOL_CleanupTimeout=36000');

    //  毫秒

  oParams.Add('POOL_ExpireTimeout=600000');

   //最多连接数

  oParams.Add('POOL_MaximumItems=500');

  oParams.Add('Pooled=True');

//    //*******

  FDManager1.Close;

  FDManager1.AddConnectionDef('MySQL_Pooled', 'MySQL', oParams);

  FDManager1.Active := True;



end;



procedure TLockMySQLDataModule.DataModuleDestroy(Sender: TObject);

begin

  oParams.Free;

end;



function TLockMySQLDataModule.DBConnectedTest(var AErrStr: string): Boolean;

var

  AConn: TFDConnection;

begin

  Result := False;

//  FSQLConnection.DriverName := 'MySQL';

//  with FSQLConnection.Params do

//  begin

//    Clear();

// //   Add('DriverUnit=Data.DBXMySQL');

//  //  Add('DriverPackageLoader=TDBXDynalinkDriverLoader,DbxCommonDriver240.bpl');

//  //  Add('DriverAssemblyLoader=Borland.Data.TDBXDynalinkDriverLoader,Borland.Data.DbxCommonDriver,Version=18.0.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b');

//  //  Add('MetaDataPackageLoader=TDBXMySqlMetaDataCommandFactory,DbxMySQLDriver240.bpl');

//  //  Add('MetaDataAssemblyLoader=Borland.Data.TDBXMySqlMetaDataCommandFactory,Borland.Data.DbxMySQLDriver,Version=18.0.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b');

// //   Add('GetDriverFunc=getSQLDriverMYSQL');

//   // Add('LibraryName=dbxmys.dll');

//   // Add('LibraryNameOsx=libsqlmys.dylib');

//   // Add('VendorLib=LIBMYSQL.dll');

//    Add('VendorLibWin64=libmysql.dll');

// //   Add('VendorLibWin64='+ExtractFilePath(Application.ExeName) + 'libmysql.dll');

//    Add('VendorLibOsx=libmysqlclient.dylib');

//    Add('HostName=' + G_ConfigClass.DBHost);

//    Add('Port=' + G_ConfigClass.DBPort);

//    Add('Database=' + 'lock');

//    Add('User_Name=' + G_ConfigClass.DBUser);

//    Add('Password=' + G_ConfigClass.DBPass);

//    Add('MaxBlobSize=-1');

//    Add('LocaleCode=0000');

//    Add('Compressed=False');

//    Add('Encrypted=False');

//    Add('BlobSize=-1');

//    Add('ErrorResourceFile=');

//    Add('ServerCharSet=' + 'utf8');

//  end;

  AConn := TFDConnection.Create(nil);

  AConn.ConnectionDefName := 'MySQL_Pooled';

  try



    try

      AConn.Connected := True;

      Result := AConn.Connected;

    except

      on e: Exception do

      begin

        AErrStr := e.Message;

        Result := False;

      end;

    end;

  finally

    AConn.Free;

  end;

end;



function TLockMySQLDataModule.GetDate(ASql: string; AClientDataSet: TClientDataSet; var AErrStr: string): Boolean;

var

  AStart: DWORD;

  AQuery: TFDQuery;

  AConn: TFDConnection;

  ADataSetProvider: TDataSetProvider;

begin

  AStart := GetTickCount;

  AConn := TFDConnection.Create(nil);

  AConn.ConnectionDefName := 'MySQL_Pooled';

  AQuery := TFDQuery.Create(nil);

  ADataSetProvider := TDataSetProvider.Create(nil);

  ADataSetProvider.DataSet := AQuery;

  AQuery.Connection := AConn;

  AClientDataSet.Active := False;

  AClientDataSet.SetProvider(ADataSetProvider);

  try

    try

      AConn.Connected := True;

      if not AConn.Connected then

      begin

        AErrStr := '数据库连接失败！';

        Exit;

      end;

    except

      AErrStr := '数据库连接失败，原因：' + AErrStr;

      Result := False;

      Exit;

    end;

    try



      AQuery.SQL.Add(ASql);

      AClientDataSet.Active := True;

      Result := True;

    except

      on e: Exception do

      begin

        AErrStr := e.Message;

        Result := False;

      end;



    end;

  finally

    ADataSetProvider.Free;

    AQuery.Free;

    AConn.free;

  end;

  //WriteStr('SQL=' + ASql + '耗时=' + IntToStr(GetTickCount - AStart));

end;



function TLockMySQLDataModule.ExecSqlList(ASqlList: TStrings; var AErrStr: string): Boolean;

var

  AStart: DWORD;

  AQuery: TFDQuery;

  AConn: TFDConnection;

  ATran: TFDTransaction;

begin

  AStart := GetTickCount;

  AConn := TFDConnection.Create(nil);

  AConn.ConnectionDefName := 'MySQL_Pooled';

  ATran := TFDTransaction.Create(nil);

  AConn.Transaction := ATran;

  AQuery := TFDQuery.Create(nil);

  AQuery.Connection := AConn;



  try

    try

      AConn.Connected := True;

      if not AConn.Connected then

      begin

        AErrStr := '数据库连接失败！';

        Exit;

      end;

    except

      AErrStr := '数据库连接失败，原因：' + AErrStr;

      Result := False;

      Exit;

    end;

    AConn.StartTransaction;

    try

      AQuery.SQL.Text := ASqlList.Text;

      AQuery.ExecSQL;

      AConn.Commit;

      Result := True;

    except

      on e: Exception do

      begin

        AConn.Rollback;

        AErrStr := e.Message;

        Result := False;

      end;



    end;

  finally

    ATran.Free;

    AQuery.Free;

    AConn.free;

  end;

 // WriteStr('SQL=' + ASqlList.Text + '耗时=' + IntToStr(GetTickCount - AStart));

end;



function TLockMySQLDataModule.ExecSQL(ASql: string; var AErrStr: string): Boolean;

var

  ASqlList: TStringList;

begin

  ASqlList := TStringList.Create;

  ASqlList.Add(ASql);

  try

    Result := ExecSqlList(ASqlList, AErrStr);

  finally

    ASqlList.Free;

  end;

end;



initialization



finalization

  G_DataModule.Free;



end.
日立奔腾浪潮微软松下联想 2019-05-17
  • 打赏
  • 举报
 回复
Delphi高版本有UTF8String，可以：
var
s: UTF8String;
// ...
s := zqry1.FieldByName('ip').Value;
无聊的猪 2019-05-17
  • 打赏
  • 举报
 回复
这应该在连接上调整，不需要去set names或UTF8Decode， 试试调整Connection的属性，ClientCodepage:='utf8'和AutoEncodeStrings:=true
doloopcn 2019-05-17
  • 打赏
  • 举报
 回复
UTF8Encode(zqry1.FieldByName('ip').AsAnsiString)


AnsiString还有UTF8编码？

String才有UTF8编码吧

.AsAnsiString改为AsString试下吧
Frank.WU 2019-05-16
  • 打赏
  • 举报
 回复
话说，既然到了 XE10.2 了，干嘛不用 FireDAC ？
Frank.WU 2019-05-16
  • 打赏
  • 举报
 回复
你的 zqry1 应该有个 TZConnection，此 TZConnection 应该有个 ClientCodepage 属性，而 ClientCodepage 应该有个 utf8
Delphi2010解决ZEOSDBO-7.1.3a控件调用mysql中文乱码
Delphi2010解决ZEOSDBO-7.1.3a控件调用mysql中文乱码 ZConnection1.Database:=_Database1; ZConnection1.Port:=_Port; ZConnection1.User:=_User; ZConnection1.Password:=_Password; ZConnection1.HostName:=_HostName;
DELPHI 7 中安装 ZEOSDBO-6.6.6-STABLE 连接
DELPHI 7 中安装 ZEOSDBO-6.6.6-STABLE 连接 mysql
MySQL中文乱码问题解决方案
MySQL中文乱码问题解决方案，彻底解决mysql中的中文乱码问题
ZeosDBO 数据连接控件
zeosdbo是一套免费开源的Delphi数据库连接组件，可连接mssql、mysql、sybase、oracle、firebird、sqlite、postgresql等多种数据库。调用方法简单。 连接MySql数据库库的方法： 添加Zconnection组件，设置如下内容： Database：要连接的数据库名称； Hostname：MySQl数据库所在的主机的名称或者Ip地址； Password：MySql数据库的用户密码； Port：MySql数据库的端口号，默认的是3306，如果更改了就改成更改后的端口地址； Protocol：选中连接数据库的类型，比方说是Mysql5就选中mysql-5，需要说明的是编写的程序目录下应该有LibMysql5.dll动态库，否则会出错。 User：就是MySql数据库的用户名。 如果这些项都设置了，把connected设为True，连接成功！ 要访问MySql数据库，就可以用ZQuery或者Ztable组件了。非常的方便。
ZEOSDBO-for-SQLite.rar_Delphi sqlite_ZEOSDBO_sqlite delphi
ZEOSDBO访问SQLite 控件配置方法
数据库相关

2,497

社区成员

88,445

社区内容

发帖
与我相关
我的任务
社区描述
Delphi 数据库相关
社区管理员
  • 数据库相关社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章