const
UniqueFields: string = 'kenteken=kenteken';
function Empty(const s: string): Boolean;
begin
if Length(Trim(s)) = 0 then Result := True
else Result := False;
end;
function ExecuteCommand(ThisCommand: string; ThisConnection: TSQLConnection): Boolean;
begin
Result := False;
if (not Assigned(ThisConnection)) or Empty(ThisCommand) then Exit;
ThisConnection.ExecuteDirect(ThisCommand);
ThisConnection.Transaction.Commit;
Result := True;
end;
function FieldToStr(ThisField: TField): string;
begin
Result := '';
if not Assigned(ThisField) then Exit;
Result := ThisField.FieldName;
case ThisField.DataType of
ftFixedChar, ftFixedWideChar:
Result := 'char[' + IntToStr(ThisField.Size) + ']';
ftString, ftWideString:
begin
if ThisField.Size <= 50 then
Result := Result + ' char[' + IntToStr(ThisField.Size) + ']'
else
Result := Result + ' varchar[' + IntToStr(ThisField.Size) + ']';
end;
else
Result := Result + ' ' + FieldTypeNames[ThisField.DataType];
end;
if (ThisField.DataType = ftAutoInc) or (ThisField is TAutoIncField) then
Result := Result + ' Primary Key';
end;
function CreateSQLTable(TableName: string; SourceTable: TDataSet;
ThisConnection: TSQLConnection; AddConversionField: Boolean = False): Boolean;
var
q: string;
i: Integer;
f: TField;
PK, UF: TStringList;
s: string;
begin
Result := False;
if Empty(TableName) then Exit;
if not Assigned(SourceTable) then Exit;
if SourceTable.Fields.Count = 0 then Exit;
TableName := ChangeFileExt(TableName, '');
if SQLTableExists(TableName, ThisConnection) then
if not ExecuteCommand('drop table ' + TableName + ';', ThisConnection) then
Exit;
q := 'create table ' + TableName + ' (';
for i := 0 to SourceTable.Fields.Count - 1 do
begin
f := SourceTable.Fields[i];
q := q + FieldToStr(f);
if i < SourceTable.Fields.Count - 1 then q := q + ', ';
end;
// Field to keep the key from the exernal datasource
if AddConversionField then q := q + ', ' + ConversionFieldName + ' ' +
ConversionFieldType;
// No AutoInc, use lookup table
if Pos('primary key', LowerCase(q)) < 1 then
begin
PK := TStringList.Create;
PK.Text := PrimaryKeys;
PK.CaseSensitive := False;
s := PK.Values[TableName];
if Length(s) > 0 then q := q + ', Primary Key (' + s + ')';
PK.Free;
end;
// Another lookup table, just a single entry here
UF := TStringList.Create;
UF.Text := UniqueFields;
UF.CaseSensitive := False;
s := UF.Values[TableName];
if not Empty(s) then s := s + ', ';
s := s + ConversionFieldName;
q := q + ', Unique (' + s + ')';
UF.Free;
q := q + ');';
Result := ExecuteCommand(q, ThisConnection);
end;