有关Delphi中使用ADOQuery带参数操作Mysql数据库中文乱码问题总结(数据库不同要求不同,以前操作Oracle就没有这些问题)
1、使用TObject作为传递参数
使用TObject作为传递参数,变量WideString, 赋值需指定ftWideString,需转换成WideString类型赋值。(变量定义为string出现中文乱码)
procedure TForm1.Button4Click(Sender: TObject);
var
paramType,paramValue:TStrings;
sql:string;
s1:widestring;
begin
s1:='直接拼接字符串qqq'+DateTimeToStr(now);
paramType :=TStringList.Create ;
paramValue:=TStringList.Create ;
sql:='insert into lh_test(area_code,datadesc,fielddesc) values('+QuotedStr('123456')+','+QuotedStr(s1)+',:p0)';
//构造查询语句参数
paramType.Clear ;
paramType.Add('Varchar');
s1:='带参数插入王家卫qqq'+DateTimeToStr(now);
paramValue.Clear ;
paramValue.AddObject('p0',TObject(s1));
dbHelper.RunSQL(sql,paramValue,paramType);
ADOTable1.Close ;
ADOTable1.TableName:='lh_test';
ADOTable1.Open ;
//总结:带参数插入
//参数变量WideString,参数传递TObject,参数赋值时需指定ftWideString,并使用WideString类型赋值
//query3.Parameters[i].DataType:=ftWideString; //新增2020-4-10 23:04
//query3.Parameters[i].Value:=WideString(paraValue.Objects[i]); //修改为WideString2020-4-10 23:04
end;
// 执行SQL命令,用于插入、更新、删除等
//带参数运行执行SQL命令,hash为参数值,参数名称从p0开始
// paramType为参数类型,Varchar,DateTime,Double,Integer,Blob,
function TDbHelper.RunSQL(sql:String;paraValue:TStrings;paraType:TStrings):integer;
var
i,paramcount:integer;
dt:TDateTime;
execNum:Integer;
begin
query3.Close ;
query3.SQL.Clear ;
query3.SQL.Add(sql);
paramcount := paraValue.Count-1;
for i:=0 to paramcount do
begin
if paraType.Strings[i]='Varchar' then
begin
query3.Parameters[i].DataType:=ftWideString; //新增2020-4-10 23:04
query3.Parameters[i].Value:=WideString(paraValue.Objects[i]); //修改为WideString2020-4-10 23:04
end else if paraType.Strings[i]='DateTime' then //日期型,实际存储的是字符型
begin
dt:=StrToDateTime(String(paraValue.Objects[i]));
query3.Parameters[i].Value:=dt;
end else if paraType.Strings[i]='Integer' then
begin
query3.Parameters[i].Value:=Integer(paraValue.Objects[i]);
end else if paraType.Strings[i]='Double' then//实数
begin
query3.Parameters[i].Value:=strtofloat(String(paraValue.Objects[i]));
end;
end;
execNum:=query3.ExecSQL;
query3.Close ;
result:=execNum;
end;
2使用TStringList作为传递参数。
使用TStringList传递参数,变量String或WideString,赋值时指定ftWideString,需转换成WideString类型赋值
procedure TForm1.Button8Click(Sender: TObject);
var
paramType,paramValue:TStrings;
sql:string;
s1:string;
s2:String;
i1:integer;
d1:double;
dt:TdateTime;
begin
s1:='直接拼接字符串qqq'+DateTimeToStr(now);
paramType :=TStringList.Create ;
paramValue:=TStringList.Create ;
sql:='insert into lh_test(area_code,datadesc,fielddesc,F101,F102,create_time) values('+QuotedStr('123456')+','+QuotedStr(s1)+',:p0,:p1,:p2,:p3)';
//构造查询语句参数
paramType.Clear ;
paramType.Add('Varchar');
paramType.Add('Double');
paramType.Add('Integer');
paramType.Add('DateTime');
s2:='带参数插入王家卫qqq'+DateTimeToStr(dbHelper.GetSystemDateTime);
d1:=123456789012345.1234;
i1:=1234567890;
dt:=now;
paramValue.Clear ;
paramValue.Add(s2);
paramValue.Add(FloatToStr(d1));
paramValue.Add(IntToStr(i1));
paramValue.Add(FormatDateTime('yyyy-mm-dd hh:MM:ss',dt));
dbHelper.RunSQLs(sql,paramValue,paramType);
ADOTable1.Close ;
ADOTable1.TableName:='lh_test';
ADOTable1.Open ;
DataSource1.DataSet :=ADOTable1;
end;
function TDbHelper.RunSQLs(sql:String;paraValue:TStrings;paraType:TStrings):integer;
var
i,paramcount:integer;
execNum:Integer;
begin
query3.Close ;
query3.SQL.Clear ;
query3.SQL.Add(sql);
paramcount := paraValue.Count-1;
for i:=0 to paramcount do
begin
if paraType.Strings[i]='Varchar' then
begin
query3.Parameters[i].DataType:=ftWideString; //新增2020-4-10 23:04
query3.Parameters[i].Value:=WideString(paraValue.Strings[i]); //修改为WideString2020-4-10 23:04
end else if paraType.Strings[i]='DateTime' then //日期型,实际存储的是字符型
begin
query3.Parameters[i].Value:=StrToDateTime(paraValue.Strings[i]);
end else if paraType.Strings[i]='Integer' then
begin
query3.Parameters[i].Value:=StrToInt(paraValue.Strings[i]);
end else if paraType.Strings[i]='Double' then//实数
begin
query3.Parameters[i].Value:=strtofloat(paraValue.Strings[i]);
end;
end;
execNum:=query3.ExecSQL;
query3.Close ;
result:=execNum;
end;