How to copy a table to another (firebird) database?

[kneighbour]

In an old entry on this forum you list some code to copy a table to another database. This code no longer works.

The old example you used (in part)

Code: Select all

SQL := 'CREATE TABLE ' + TableName + ' ('#13#10;
      while not UniMetaDataSrc.Eof do begin
        SQL := SQL + UniMetaDataSrc.FieldByName('COLUMN_NAME').AsString + ' ' +
                     UniMetaDataSrc.FieldByName('DATA_TYPE').AsString;
        if Pos('CHAR', UniMetaDataSrc.FieldByName('DATA_TYPE').AsString) > 0 then
          SQL := SQL + '(' + UniMetaDataSrc.FieldByName('DATA_LENGTH').AsString + ')';

The FieldByName('DATA_TYPE') returns a number and thus is no good in the script to create a new table. eg something like this is generated

Code: Select all

CREATE TABLE HTRXTBL ('#$D#$A'HTRX_ID 8,'#$D#$A'HTRX_TRX_DATE 35, .. ... etc

Can you provide an example where I can copy a table?

[ViktorV]

Data about the column data type is stored in Firebird system tables in numerical form: https://firebirdsql.org/file/documentat ... ields.html. Therefore, you should implement yourself the data type name obtaining according to their numeric code. For example:

Code: Select all

function GetDataName(FieldType: Integer): string;
begin
  case FieldType of
    7: Result := 'SMALLINT';
    8: Result := 'INTEGER';
    10: Result := 'FLOAT';
    12: Result := 'DATE';
    13: Result := 'TIME';
    14: Result := 'CHAR';
    16: Result := 'BIGINT';
    27: Result := 'DOUBLE PRECISION';
    35: Result := 'TIMESTAMP';
    37: Result := 'VARCHAR';
    261: Result := 'BLOB';
  end;
end;

[kneighbour]

Thanks, that is a big help. But it still does not "work" as so much is left out. ie
- field NOT NULL
- DEFAULT value
- BLOB types ie SUB_TYPE TEXT SEGMENT SIZE 256

If there some way I can get the whole SQL usable definition of a table? There is not much use trying to copy/create a table if the definition is incomplete.

This seems a pretty big effort, so if you have not already implemented such a function, might I suggest it as a future feature?

[ViktorV]

IBDAC does not support this functionality because Firebird architecture does not allow to obtain the original DDL of the table and it should be configured separately using system tables data and Firebird documentation. Therefore, to receive an answer to this question, forward it to Firebird tech support or ask an appropriate question on the specialized forums.