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
 
ABA Picture Convert
MAPIMail
Metafile Convert
Protected Storage Viewer
SMDBGrid
Database Information Manager
Clarion to Text converter
Excel Reader (dll)
SMMsg suite
Paradox Password Recovery
 
 


Contact to webmaster

 

Borland Software Code Gear Scalabium Delphi tips

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

SMExport advertising