Scalabium Software

SMExport advertising
Knowledge for your independence'.
Home Delphi and C++Builder tips


#108: How can I generate the script for table creation?

As you know in DIM: Database Information Manager I realized the few typical autogenerated scripts.

For example, for some dataset to generate a standard SELECT-statement or INSERT-statement.

Today I want to post a sample code which allows to generate the CREATE TABLE-stetement.

 

function GetCreateTable(Dataset: TDataSet): TStrings;
var
  i: Integer;
  str: string;
begin
  Result := TStringList.Create;
  try
    for i := 0 to DataSet.FieldCount-1 do
    begin
      with DataSet.Fields[i] do
      begin
        str := '   ' + DataSet.Fields[i].FieldName + ' ' +
GetFieldTypeName(DataType);
        if DataType = ftString then
          str := str + '(' + IntToStr(Size) + ')';
        if Required then
          str := str + ' NOT';
        str := str + ' NULL';
      end;
      if (i <> DataSet.FieldCount-1) then
        str := str + ',';
      Result.Add(str);
    end;
  except
    Result.Free;
    Result := nil;
  end;
end;

where GetFieldTypeName is

function GetFieldTypeName(AType: TFieldType): string;
const
  FieldTypes: array [TFieldType] of PChar =
    ('?Unknown?', 'Char', 'Smallint', 'Integer', 'Word', 'Boolean',
     'Float', 'Currency', 'BCD', 'Date', 'Time', 'DateTime',
     'Bytes', 'VarBytes', 'AutoInc', 'Blob', 'Memo', 'Graphic',
     'Blob', 'Blob', 'Blob', 'Blob', 'Cursor',

     'FixedChar', 'WideString', 'Largeint', 'ADT', 'Array', 'Reference',
     'DataSet', 'OraBlob', 'OraClob', 'Variant', 'Interface',
     'IDispatch', 'Guid'
     );
begin
  if AType < Low(FieldTypes) then
    AType := Low(FieldTypes)
  else
    if AType > High(FieldTypes) then
      AType := Low(FieldTypes);
  Result := UpperCase(StrPas(FieldTypes[AType]));
end;

Few important notes:

1. this code uses the Delphi 5/C++Builder 5 declaration of TFieldType type. If you use the other version, you must change the declaration of FieldTypes array

2. syntax of CREATE TABLE-statement depends from database type. So you must modify it for own needs. You must do it in values of FieldTypes array items. For example, client-server databases haven't in-build logical type, so you must declare that CHAR(1) is boolean. Or to use the MONEY syntax for Currency fields in MS SQL.


Published: December 19, 2000

See also
 
DBISAM Password Recovery
Paradox to Text converter
dBase Viewer
Protected Storage Viewer
DBISAM Viewer
ExcelFile Viewer
Paradox ActiveX
Mail parser (ActiveX)
Metafile Convert
Clarion Viewer
 
 


Contact to webmaster

 

Borland Software Code Gear Scalabium Delphi tips

Copyright© 1998-2024, Scalabium Software. All rights reserved.
webmaster@scalabium.com

SMExport/SMImport suites