meta data for this page
ibec_ExtractMetadata
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.
Syntax
function ibec_ExtractMetadata(Connection : variant; ScriptFile :string; Options : string; CallbackProc : variant): string;
Parameters
Connection | Active database connection. |
---|---|
ScriptFile | Name of the resulting script file or directory name if the VCSFiles option is used. |
Options | List of options delimited with semicolon; possible options are: |
ServerVersion | You can use the following server version identifiers: IB5X, IB61, IB65, IB70, IB71, IB75, IB2007, FB10, FB15, FB20, FB21, FB25, YA10. |
AddKeyword | For example: FIRST, SECOND, START, TIME. |
RemoveKeyword | For example: FIRST, SECOND, START, TIME. |
VCSFiles | Each database object definition will be extracted into a separate file. |
SeparateFiles | Extracts 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. |
GenerateCreate | Determines whether a CREATE DATABASE statement should be included at the beginning of the generated script. |
GenerateConnect | Determines whether a CONNECT statement should be included at the beginning of the generated script. |
IncludePassword | Determines whether the password should be included into the CREATE DATABASE or the CONNECT statement in the resulting SQL script. |
SuppressComments | Use to suppress comments in the resulting script. |
IncludeCharset | This 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. |
SeparateComputedBy | Specifies whether computed fields should be extracted separately. |
SetGenerators | Use to set generator values. |
ExtractDescriptions | Determines whether database object descriptions should be included into the generated script. |
DescriptionsAsUpdate | Determines whether the raw UPDATE statement will be used for object descriptions instead of the IBExpert-specific DESCRIBE statement. |
ExtractPrivileges | Use to extract privileges. |
OnlySelectedPrivileges | If used only privileges of the selected objects will be included into the resulting script. Otherwise ALL privileges will be extracted. |
UseReinsert | Determines whether the IBExpert REINSERT command should be used to insert multiple data records. |
UseUpdateOrInsert | Allows extraction of data as UPDATE OR INSERT statements instead of INSERT. |
UseRecreate | If this option is specified, the RECREATE TABLE statement will be used instead of CREATE TABLE. |
UseBlocks | Implemented in IBExpert version 2021.02.09. |
CommitBlocks | Implemented in IBExpert version 2021.02.09. |
ExtractBLOBs | Determines whether blob values should be extracted. |
BlobsAsHex | If 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. |
ExcludeIBE | Use to omit database objects with the prefix IBE$. |
ExcludeTMP | Use to omit database objects with the prefix TMP$ (InterBase® 7.x). |
DecodeDomains | Determines whether domain definitions will be extracted as comments to the corresponding table fields. |
CommitAfter=X | This 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=X | Defines the maximum file size of script files (in megabytes). The default value is 0, this means that there will be no file splitting. |
DateTimeFormat= | 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. |
DelayedTriggerActivation | Delayed 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. |
Packages | Specifies list of packages (Firebird 3.0) to be extracted. Specify ALL, NONE or a comma-separated list, e.g. PACKAGE_1,PACKAGE_2,…;. |
Stored functions | Specifies list of stored functions (Firebird 3.0) to be extracted. Specify ALL, NONE or a comma-separated list, e.g. FUNCTION_1,FUNCTION_2,…;. |
ExtractDDLPrivileges | Allows 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. |
AlwaysQuoteIdents | When specified, all identifiers (except those in procedure/trigger bodies) will be quoted. |
OctetsAsHex | This option allows the extraction of CHAR(n) CHARACTER SET OCTETS values in hexadecimal format. |
ExplicitDDLCommit | Implemented in IBExpert version 2020.09.13, this option adds the COMMIT statement after each DDL statement in the result script. |
CallbackProc | A 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. |
UseComment | For support of the Firebird 2 COMMENT ON statement. |
UseSequence | For 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);