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

	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