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剛上手，完全不適應。
uj5u.com熱心網友回復：
你的 zqry1 應該有個 TZConnection，此 TZConnection 應該有個 ClientCodepage 屬性，而 ClientCodepage 應該有個 utf8uj5u.com熱心網友回復：
話說，既然到了 XE10.2 了，干嘛不用 FireDAC ？uj5u.com熱心網友回復：
UTF8Encode(zqry1.FieldByName('ip').AsAnsiString)AnsiString還有UTF8編碼？
String才有UTF8編碼吧
.AsAnsiString改為AsString試下吧
uj5u.com熱心網友回復：
這應該在連接上調整，不需要去set names或UTF8Decode，試試調整Connection的屬性，ClientCodepage:='utf8'和AutoEncodeStrings:=true
uj5u.com熱心網友回復：
Delphi高版本有UTF8String，可以：var
s: UTF8String;
// ...
s := zqry1.FieldByName('ip').Value;
uj5u.com熱心網友回復：
用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.
uj5u.com熱心網友回復：
每一種連接方式編碼不一樣容易亂碼，換連接方式試下轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/29449.html
標籤：數據庫相關