ibec_ExtractMetadata

Extracts metadata (and data if specified) of a database into a script.

Syntax

 function ibec_ExtractMetadata(Connection : variant; ScriptFile :string;
                               Options : string; CallbackProc : variant): string;

Description

This function extracts metadata/data of a specified database into a script.

Note: In Firebird mixed case table names that are unquoted are stored in uppercase; in queries these object names are case insensitive. You need to use the table name as it is stored in the metadata tables for this function. This means: UPPERCASE for unquoted object names or the exact casing of quoted object names.

ibec_ExtractMetadata also supports the OctetsAsHex option which allows the extraction of CHAR(n) CHARACTER SET OCTETS values in hexadecimal format.

Firebird 3.0

You should specify packages in the Options string:

 PACKAGES=ALL;

or

 PACKAGES=PACKAGE_1,PACKAGE_2,...;

or

 PACKAGES=NONE;


The list of stored functions to be extracted should be specified in the Options string:

 FUNCTIONS=ALL;

or

 FUNCTIONS=FUNCTION_1,FUNCTION_2,...;

or

 FUNCTIONS=NONE;


The ExtractDDLPrivileges option allows extraction of privileges on data definition language statements such as CREATE/ALTER/DROP (Firebird 3).

The UseUpdateOrInsert option allows extraction of data as UPDATE OR INSERT statements instead of INSERT.

Parameters

ConnectionActive database connection.
ScriptFileName of the resulting script file or directory name if the VCSFiles option is used.
OptionsList of options delimited with semicolon; possible options are:
ServerVersionYou can use the following server version identifiers: IB5X, IB61, IB65, IB70, IB71, IB75, IB2007, FB10, FB15, FB20, FB21, FB25, YA10.
AddKeywordFor example: FIRST, SECOND, START, TIME.
RemoveKeywordFor example: FIRST, SECOND, START, TIME.
VCSFilesEach database object definition will be extracted into a separate file.
SeparateFilesExtracts metadata (and data if specified) into a set of files: two files with metadata (_ibe$start_.sql and _ibe$finish_.sql), files containing table data (one or more files for each database table) and a runme.sql file, that consists of a number of INPUT <file_name> statements in the correct order.
GenerateCreateDetermines whether a CREATE DATABASE statement should be included at the beginning of the generated script.
GenerateConnectDetermines whether a CONNECT statement should be included at the beginning of the generated script.
IncludePasswordDetermines whether the password should be included into the CREATE DATABASE or the CONNECT statement in the resulting SQL script.
SuppressCommentsUse to suppress comments in the resulting script.
IncludeCharsetThis option forces IBExpert/IBEScript to include the CHARACTER SET clause into the definition of all CHAR/VARCHAR/domains/columns/parameters, even if their CHARSET is equal to the default CHARSET of the database.
SeparateComputedBySpecifies whether computed fields should be extracted separately.
SetGeneratorsUse to set generator values.
ExtractDescriptionsDetermines whether database object descriptions should be included into the generated script.
DescriptionsAsUpdateDetermines whether the raw UPDATE statement will be used for object descriptions instead of the IBExpert-specific DESCRIBE statement.
ExtractPrivilegesUse to extract privileges.
OnlySelectedPrivilegesIf used only privileges of the selected objects will be included into the resulting script. Otherwise ALL privileges will be extracted.
UseReinsertDetermines whether the IBExpert REINSERT command should be used to insert multiple data records.
UseUpdateOrInsertAllows extraction of data as UPDATE OR INSERT statements instead of INSERT.
UseRecreateIf this option is specified, the RECREATE TABLE statement will be used instead of CREATE TABLE.
UseBlocksImplemented in IBExpert version 2021.02.09.
CommitBlocksImplemented in IBExpert version 2021.02.09.
ExtractBLOBsDetermines whether blob values should be extracted.
BlobsAsHexIf this option is enabled the values of blob fields will be written directly into the result script in hexadecimal notation. Hexadecimal notation for binary values is available in Firebird 2.5 and Firebird 3. The length of the binary value which may be encoded in hexadecimal notation is limited to 32765 bytes in Firebird 2.5 and to 65535 bytes in Firebird 3. If the blob value exceeds the limit it will be written in the lob-file as is.
ExcludeIBEUse to omit database objects with the prefix IBE$.
ExcludeTMPUse to omit database objects with the prefix TMP$ (InterBase® 7.x).
DecodeDomainsDetermines whether domain definitions will be extracted as comments to the corresponding table fields.
CommitAfter=XThis option defines the number of records before inserting the COMMIT statement into the script. The default value is 500, i.e. 500 insert commands are performed and then committed.
MaxFileSize=XDefines the maximum file size of script files (in megabytes). The default value is 0, this means that there will be no file splitting.
DateTimeFormat=
"dd.mm.yy hh:mm:ss.zzz"
Allows user-defined formatting of datetime (timestamp) values. Usage is similar to the DateFormat option.
DateFormat=<format>Specifies the format of date values and date part of timestamp values.
Domains=<objects_list>Specifies list of domains to be extracted. Items should be separated with comma. If this option is not defined all domains will be extracted.
Tables=<objects_list>Specifies list of tables to be extracted. Items should be separated with comma. If this option is not defined all tables will be extracted.
Views=<objects_list>Specifies list of views to be extracted. Items should be separated with comma. If this option is not defined all views will be extracted.
Triggers=<objects_list>Specifies list of triggers to be extracted. Items should be separated with comma. If this option is not defined all triggers will be extracted.
DelayedTriggerActivationDelayed activation of database events and DDL triggers: if this option is ON, DB event and DDL triggers will be first created as inactive, and activated at the end of the result script.
Procedures=<objects_list>Specifies list of procedures to be extracted. Items should be separated with comma. If this option is not defined all procedures will be extracted.
Generators=<objects_list>Specifies list of generators to be extracted. Items should be separated with comma. If this option is not defined all generators will be extracted.
Exceptions=<objects_list>Specifies list of exceptions to be extracted. Items should be separated with comma. If this option is not defined all exceptions will be extracted.
UDFs=<objects_list>Specifies list of UDFs to be extracted. Items should be separated with comma. If this option is not defined all UDFs will be extracted.
Roles=<objects_list>Specifies list of roles to be extracted. Items should be separated with comma. If this option is not defined all roles will be extracted.
PackagesSpecifies list of packages (Firebird 3.0) to be extracted. Specify ALL, NONE or a comma-separated list, e.g. PACKAGE_1,PACKAGE_2,...;.
Stored functionsSpecifies list of stored functions (Firebird 3.0) to be extracted. Specify ALL, NONE or a comma-separated list, e.g. FUNCTION_1,FUNCTION_2,...;.
ExtractDDLPrivilegesAllows extraction of privileges on data definition language statements such as CREATE/ALTER/DROP (Firebird 3).
DataTables=<objects_list>Specifies the list of tables from which data should be extracted. If this option is not defined NO data will be extracted. You can use the ALL keyword as a list of objects to specify that all objects of that type must be extracted. You can use the NONE keyword as a list of objects to omit all objects of that type.
AlwaysQuoteIdentsWhen specified, all identifiers (except those in procedure/trigger bodies) will be quoted.
OctetsAsHexThis option allows the extraction of CHAR(n) CHARACTER SET OCTETS values in hexadecimal format.
ExplicitDDLCommitImplemented in IBExpert version 2020.09.13, this option adds the COMMIT statement after each DDL statement in the result script.
CallbackProcA callback IBEBlock which will be executed for each record processed whilst comparing data. The callback IBEBlock must have at least one input parameter, which will be used to pass a number of processed records within it.
UseCommentFor support of the Firebird 2 COMMENT ON statement.
UseSequenceFor support of the Firebird 2.x CREATE / ALTER SEQUENCE.

Examples of usage

1. Extracting domain definitions in VCS-files:

   execute ibeblock
   as
   begin
      cbb = 'execute ibeblock (LogLine variant)
        as
        begin
          ibec_progress(LogLine);
        end';

      db = ibec_GetDefaultConnection();
 ibec_ExtractMetadata(db, 'E:\Domains\', 'Domains=ALL; Tables=NONE; Views=NONE;
 Triggers=NONE; Procedures=NONE; Generators=NONE;
 Exceptions=NONE; UDFs=NONE; Roles=NONE;
  VCSFiles', cbb);
    end;

2. Complete metadata extract:

   execute ibeblock
   as
   begin
      cbb = 'execute ibeblock (LogLine variant)
        as
        begin
          ibec_progress(LogLine);
        end';

   db = ibec_GetDefaultConnection();
 ibec_ExtractMetadata(db, 'E:\meta.sql', 'GenerateCreate; ExtractPrivileges; ExtractDescriptions',
 cbb);
   end;

3. Extracting data from specified tables:

   execute ibeblock
   as
   begin
      cbb = 'execute ibeblock (LogLine variant)
        as
        begin
          ibec_progress(LogLine);
        end';

      db = ibec_GetDefaultConnection();
 ibec_ExtractMetadata(db, 'E:\data.sql', 'Domains=NONE; Tables=NONE; Views=NONE; Triggers=NONE; 

 Procedures=NONE; Generators=NONE;
 Exceptions=NONE; UDFs=NONE; Roles=NONE;DataTables=IBE$$TEST_DATA, MY_TABLE;
 ExtractBLOBs;UseReinsert; CommitAfter=1000', cbb);
    end;

4. Using the IncludeCharset parameter:

 ibec_ExtractMetadata(db, 'E:\meta.sql', 'GenerateCreate;IncludeCharset;ExtractPrivileges; ExtractDescriptions',cbb);

5. Using a number of parameters:

 ibec_ExtractMetadata(MyDB, 'D:\MyDB.sql', 'ServerVersion=FB21; AddKeyword=FIRST,SECOND,KEY; 
 RemoveKeyword=START,TIME; ...', cbb);

See also:
Extract Metadata
Extract metadata using IBEBlock
Specifying WHERE clauses in ibec_ExtractMetadata
ibec_CompareMetadata
Using Extract metadata to repair databases

back to top of page
<< ibec_CompareMetadata | IBEBlock | Specifying WHERE clauses in ibec_ExtractMetadata >>