Scalabium Software
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 Spreadsheet Convert
	SMReport
	Viewer for MS Outlook Messages
	ABA Document Convert
	Mail parser (ActiveX)
	Paradox ActiveX
	ABA Picture Convert
	Metafile Convert
	Clarion to Text converter
	Database Information Manager

Contact to webmaster