sqlutils.pas

1.可查看/修改windows操作系统 可使用oledb或odbc的数据库 2.对于不需为用户安装管理工具的数据库,可方便程序员管理数据. 3.可以非常灵活地导出数据,甚至sql insert语句

unit sqlutils;


interface
uses db,adodb,sysutils,classes;
function gensqlbydset(fname:string;dset:Tadodataset):boolean;
function get_Table_syntax(dbcon:Tadoconnection;tablename:string):String;
implementation
function gensqlbydset(fname:string;dset:Tadodataset):boolean;
var tstrs:Tstrings;
    tstr,fstrs,tablename:string;
    i,j:integer;
  function fieldtostring(f:Tfield):string;
  begin
    if (f.DataType=ftSmallint) or
       (f.DataType=ftInteger) or
       (f.DataType=ftWord) or
       (f.DataType=ftBoolean) or
       (f.DataType=ftFloat) or
       (f.DataType=ftCurrency) then
      result:=f.asstring
    else if (f.DataType=ftDate) or
            (f.DataType=ftTime) or
            (f.DataType=ftDateTime) then
      result:=floattostr(f.AsFloat)
    else if (f.DataType =ftstring) then
      result:=''''+stringreplace(f.AsString,'''','''''',[rfreplaceall])+''''
    else
      result:='null';
  end;
begin
  result:=false;
  if not dset.Active then dset.Active :=true;
  if dset.Eof then exit;
  tablename:=stringreplace(dset.CommandText,'select * from ','',[rfIgnoreCase,rfreplaceall]);
  tablename:=stringreplace(tablename,'"','',[rfreplaceall]);
  tablename:='['+tablename+']';
  tstrs:=tstringlist.Create ;
  dset.First ;
  for i:=0 to dset.FieldCount-1 do
  begin
    if dset.Fields[i].DataType = ftAutoInc then continue;
    if fstrs='' then
      fstrs:=dset.Fields[i].FieldName
    else
      fstrs:=fstrs+','+dset.Fields[i].FieldName;
  end;
  while not dset.Eof do
  begin
    tstr:='';
    for i:=0 to dset.FieldCount-1 do
    begin
      if dset.Fields[i].DataType = ftAutoInc then continue;
      if tstr='' then
        tstr:=fieldtostring(dset.Fields[i])
      else
        tstr:=tstr+','+fieldtostring(dset.Fields[i]);
    end;
    tstrs.Add('insert into '+tablename+'('+fstrs+') VALUES('+tstr+');');
    dset.Next;
  end;
  tstrs.SaveToFile(fname);
  tstrs.Free;
end;
function get_Table_syntax(dbcon:Tadoconnection;tablename:string):String;
var dset:Tadodataset;
  tname:string;
  tret:string;
  tf:string;
  i:integer;
  {datatypes:array[1..10] of string=(
    'ftUnknown', 'ftString', 'ftSmallint', 'ftInteger', 'ftWord',
    'ftBoolean', ftFloat, ftCurrency, ftBCD, ftDate, ftTime, ftDateTime,
    ftBytes, ftVarBytes, ftAutoInc, ftBlob, ftMemo, ftGraphic, ftFmtMemo,
    ftParadoxOle, ftDBaseOle, ftTypedBinary, ftCursor, ftFixedChar, ftWideString,
    ftLargeint, ftADT, ftArray, ftReference, ftDataSet, ftOraBlob, ftOraClob,
    ftVariant, ftInterface, ftIDispatch, ftGuid, ftTimeStamp, ftFMTBcd); }
  label saftexit;
begin
  tname:=tablename;
  result:='';
  tret:='';
  if pos(' ',tname)>0 then tname:='"'+tname+'"';
  dset:=Tadodataset.Create(nil);
  try
    dset.Connection:=dbcon;
    dset.CommandText:='select top 1 * from '+tablename;
    dset.Active :=true;
    tret:='drop table '+tname+';'#$D#$A;
    tret:=tret+'create table '+tname+#$D#$A;
    if dset.fieldcount<1 then
    begin
      tret:=tret+'();';
      goto saftexit;
    end;
    tret:=tret+'(';
    for i:=0 to dset.FieldCount-1 do
    begin
    with dset.Fields[i] do
    begin
      tf:=fieldname;
      if (DataType=ftSmallint) or
      (DataType=ftInteger    ) or
      (DataType=ftWord       ) or
      (DataType=ftBCD        ) or
      (DataType=ftDate       ) or
      (DataType=ftTime       ) or
      (DataType=ftDateTime   ) or
      (DataType=ftAutoInc)
      then
      tf:=tf+' '+'integer'
      else if (DataType=ftString) then
      tf:=tf+' '+'char ('+inttostr(dset.Fields[i].DataSize)+')'
      else if (datatype=ftboolean) then
      tf:=tf+' '+'boolean'
      else if 
      (DataType=ftFloat) or
      (DataType=ftCurrency)
      then
      tf:=tf+' '+'double'
      else
        tf:=tf+' memo';
    end;
    if i=dset.FieldCount-1 then
        tret:=tret+tf
    else
      tret:=tret+tf+','+#$D#$A;
    end;
    tret:=tret+');'#$D#$A ;
saftexit:
    dset.active:=false;
  except
  end;
  dset.Free;
  result:=tret;
end;
end.