Scalabium Software

#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.

Copyrightc 1998-2012, Scalabium Software. All rights reserved.
webmaster@scalabium.com

December 19, 2000