Data Dictionary Maintenance

Database Export Utility

The Database Export Utility simplifies the process of adding a PxPlus data file to an existing database by providing the following capability:

Add the tables to the database (based on their data dictionary definitions).
Populate tables using the data in the PxPlus data file.
Add tables to a specified prefix file.
Validate the existing data against the data dictionary definitions to uncover any potential issues once the tables are converted into the database.

The Bulk Database Export Utility allows you to selectively export multiple table definitions from the PxPlus data dictionary into the specified database. It can also be used to validate native PxPlus data files, load data from native PxPlus data files to the database table, create database link files and add database table entries to the prefix file.

(The Database Conversion Utility was added in PxPlus 2019 and renamed to Database Export Utility in PxPlus 2023.)
(The Bulk Database Export Utility was added in PxPlus 2023.)

Warning!
If you are using this utility to create a database table that already exists, the database table will be removed and re-created.

To prevent the loss of data, ensure that table names are unique and do not already exist in the selected database.

The following database types are supported: ADO, DB2, OCI, ODB and MYSQL. Note the following:

To use the DB2 interface, you must have either the DB2 database or the DB2 client installed on a workstation, and the path to the db2cli.dll file must be locatable by the PATH environment variable. When using the DB2 client, a db2cli.ini file is created in the user directory to specify such parameters as the Hostname (address), uid (user) and pwd (password).
To use the OCI interface, you must have either the Oracle Server or the Oracle client installed on a workstation, and the path to the oci.dll file must be locatable by the PATH environment variable. When a workstation is connecting to a remote server, the IP address of the server is included in the sid value in the following format: "ip_address/ORACLE_SID:table".
To use the MYSQL interface, you must have either the MySql database or the MySql client installed on a workstation, and the libmysql.dll file must be locatable by the PATH environment variable.

To invoke this utility, use one of the following methods:

Location	Method
From the Data Dictionary Maintenance Tool Bar	On the Data Dictionary Maintenance Main Panel, enter or select the name of an existing data file definition. Click the Export button in the *Database* section of the tool bar.
From the Data Dictionary Maintenance Menu Bar	On the Data Dictionary Maintenance Main Panel, enter or select the name of an existing data file definition. From the menu bar, select File > Database Export Utility.
From the PxPlus Command line	Enter: *Run "dict/sqlmake**
Call Export actions programmatically	See Calling Export Actions Programmatically.

The Database Export Utility is displayed below with a sample entry:

This utility consists of the following:

File to Convert

Data file to be converted into a database table. This file name should be relative to the current working directory.

If this utility is invoked from Data Dictionary Maintenance, the name of the selected data file definition will be displayed (and disabled) with its description shown to the right.

Bulk Export

Button that launches the Bulk Database Export Utility.

(The Bulk Export button was added in PxPlus 2023.)

Database Type

Type of database. Selections are "ODB", "MySQL", "OCI", "DB2" and "ADO". If not specified, defaults to "ODB".

Data Source or Server Address

(This field changes depending on the Database Type selected)

If Database Type is ODB: Enter the name of the Data Source. (Required for ODB)

If Database Type is MySQL, OCI or ADO: Enter a Server Address. If the database is running locally, this value may be omitted. (Required for ADO)

If Database Type is DB2: This field is disabled, as only the Database Name is used and the server is defined in the db2cli.ini file.

Database Name or System ID

(This field changes depending on the Database Type selected)

If Database Type is ODB, MySQL, DB2 or ADO: Enter the Database Name. (Required for MySQL or DB2)

If Database Type is OCI: Enter the Oracle System ID value. (Required for OCI)

Table Name

Name to be used for the table in the database. Defaults to the same name as the data dictionary entry.

Alternate File Name

Alternate file name to be added to the prefix file if any data dictionary elements have been defined with Alternate Names. Defaults to the File to Convert name+"_ALT".

If no data dictionary elements have alternate names, this field will be disabled.

User Name

User login name.

Password

User password.

Additional Options

Parameters to be included in the OPT= string when opening the database or updating the prefix file. All parameters are semi-colon separated.

Defaults to NONULLS=Y;NULLPADKEY=Y for all database types except OCI, which defaults to NULLPADKEY=Y.

(The additional option NULLPADKEY=Y was added in PxPlus 2023.)

Test Connection

This button is enabled when the minimum number of required fields have been entered. When selected, a message displays to indicate if the connection to the database is valid.

Database Setup Information

Override Database Setup Information	Select this check box to enable and manually edit the Pathname, Options and Create inputs, which have been populated based on the data entered in the top section of the panel.
Pathname	Pathname to the database file.
Options	Options to be specified when the file is opened and/or written to the prefix file.
Create	CREATE TABLE command to be used when creating the table in the database.
Errors	Displays any issues discovered when building the CREATE TABLE command from the data dictionary definition.

Create Table

(Available when Database Name is entered)

This button is used to create the table in the specified database. If successful, a message will confirm the creation of the table; otherwise, an error message will display if any issues are encountered with the CREATE TABLE command.

Important Note:
If the table already exists in the database, a message will display to warn that the table will be overwritten, which could lead to possible data loss, and to ask if you want to proceed. Responding Yes will remove and recreate the table.

Key names from the PxPlus data dictionary will have the #num_name_table format upon exporting to an external database. If those tables are imported again (see Database Import Utility), the key name will be converted back to just name. If the key has no name, it will just be #num_table. This is done to satisfy the external database requirement for a name and to ensure the proper key order.

Example:

Suppose that the Clients table has two keys - one has no name and the other is named byName. Upon exporting to an external database, these keys would be exported as #1_Clients and #2_byName_Clients. If the Clients table in the external database was then imported again, the keys would be imported as "no name" and byName.

(Support to check for existing database tables and display a message was added in PxPlus 2023.)

Load Table

(Available when Database Name is entered)

This button is used to populate the table added to the database with the data in the PxPlus data file. A progress bar will display if this process will take a few minutes.

If the load is successful, a message will indicate the number of records written to the table.

If the load failed, a message will indicate what the error was. If the failure happened while the load was in progress, the table in the database may have some but not all of the records from the PxPlus native file.

Important Note:
If the table already has records in the database, a message will display to warn that matching records will be overwritten, which could lead to possible data loss, and to ask if you want to proceed. Responding Yes will load the database table records.

(Support to check for existing database table records and display a message was added in PxPlus 2023.)

Update Prefix

(Available when Database Name is entered)

This button is used to update the database information to a specific Prefix File. When selecting this button the first time in any given PxPlus session, the prefix file can be selected.

If multiple tables will be added to a database, this prefix file will be remembered and used the next time this button is selected without having to select it each time.

Update Link File

(Available when Database Name is entered)

This button is used to update/create a specific link file with the database information. When this button is selected, the link file to update/create can be specified.

See Creating a Link File.

Verify Data

This button is used to validate the existing data in the PxPlus data file either before or after the conversion has been done.

Enable SQL

Select this check box to turn On the '!Q' parameter to display all ODBC SQL statements in a Windows message box for troubleshooting ODBC-related problems.

Exit

Closes the Database Export Utility. Any saved prefix file setting will be reset the next time the utility is accessed.

Calling Export Actions Programmatically

Besides invoking the Database Export Utility through Data Dictionary Maintenance, individual files, as well as multiple files, can be added to a database programmatically by calling the Convert method in the *dict/sqlmake program.

The Convert method creates a table, loads a table and optionally creates the prefix file entry and link file:

Call "*dict/sqlmake;Convert" [,ERR=stmtref],fileName$, db_type$, connect$, db_name$, db_table$, db_altname$, db_user$, db_pass$, db_options$[,pfx_file$[,link_file$[,noOverwrite[,noStripPrompt]]]]

Where:

fileName$

Pathname of the file to convert.

db_type$

Database type. Valid types are "ODB", "MySQL", "OCI", "DB2" and "ADO". If not specified, defaults to "ODB".

connect$

Data Source or System ID.

(The connect$ parameter was added in PxPlus 2019 Update 1.)

db_name$

Database Name or System ID.

db_table$

Name of the table in the database. If not specified, defaults to the name from the data dictionary.

(The db_table$ parameter was added in PxPlus 2019 Update 1.)

db_altname$

Name to use for a prefix file entry using the alternate file IOList. If not specified, defaults to filename$+"_ALT" if any columns have alternate names defined for any data dictionary elements.

db_user$

User login name.

(The db_user$ parameter was added in PxPlus 2019 Update 1.)

db_pass$

User password.

(The db_pass$ parameter was added in PxPlus 2019 Update 1.)

db_options$

Options to be used as OPT= parameters when opening the database or updating the prefix file. Defaults to NULLPADKEY=Y and NONULLS=Y, except if db_prefix$="OCI", in which case, it is just NULLPADKEY=Y.

pfx_file$

(Optional) Prefix file to be updated. If not specified, no prefix file will be updated. If the file does not yet exist, the prefix file will be created and then updated.

link_file$

(Optional) Link file to be updated. If not specified, a link file will not be updated. If the file does not exist, it will be created.

noOverwrite

(Optional) Flag indicating what to do if a table, table data, and/or link file exists in the database.

0	Table/data will be overwritten without warning if table and/or data exists in the database. *(Default)*
1	An Error 12 will be returned if table and/or data exists in the database.
2	User will be asked for confirmation before overwriting table and/or data.

(The noOverwrite parameter was added in PxPlus 2023.)

noStripPrompt

(Optional) Flag indicating how to handle User Name and Password within prefix/link file(s).

0	The user will be prompted if he/she wants to strip User Name and Password from the file. *(Default)*
1	User Name and Password will be stripped from the file without prompting.
2	User Name and Password will not be stripped from the file without prompting.

(The noStripPrompt parameter was added in PxPlus 2023.)

When the called method is used, the messages confirming file creation, loading, etc. are suppressed although progress bars will still appear. Any files that already exist in the database will be removed and re-created. Any error messages will be displayed using a message box prior to exiting the method with an ERR condition.

(The connect$, db_table$, db_user$ and db_pass$ parameters were added in PxPlus 2019 Update 1.)

The individual Export actions (i.e. Validate, CreateTable, LoadTable, etc.) can be invoked programmatically by calling the following routines:

Validate a PxPlus Native data file

Call "*dict/sqlmake;Validate", fileName$, result$

Where:

fileName$

Pathname of the PxPlus native data file to validate.

result$

After the call, check this string for the result of the validation.

If it worked without error, the result will be "SUCCESS".
If there was an error, it will not say "SUCCESS" and just contain information about the validation errors.

(The Validate call routine was added in PxPlus 2023.)

Create/Merge/Replace a Data Dictionary table into the Database

Call "*dict/sqlmake;CreateTable", fileName$, ddf_table$, db_prefix$, connect$, db_name$, db_table$, db_user$, db_pass$, db_options$, replace, result$

Where:

fileName$

Physical file path to a native PxPlus data file with an embedded dictionary.

If ddf_table$ is empty, the embedded dictionary will be used to create the database table.

ddf_table$

Name of the table in the PxPlus data dictionary. The data dictionary record will be used to create the database table. If this is empty, the fileName$ must point to a file with an embedded dictionary.

db_prefix$

Database type. Valid types are "ODB", "MySQL", "OCI", "DB2" and "ADO". If not specified, defaults to "ODB".

connect$

Data Source or Server Address.

db_name$

Database Name or System ID.

db_table$

Name of the table to create in the database. Defaults to the ddf_table$ if nothing is specified.

db_user$

User login name.

db_pass$

User password.

db_options$

replace

Flag indicating if the database already exists if the table should be replaced instead of merged. If the table is new, this has no effect.

0	Merge table.
1	Table will be replaced if the user answers Yes to the data loss warning message.
2	Table will be replaced without a warning message.
3	An error will be returned in results$ if table exists in the database.

result$

After the call, check this string for the result of the call.

If it worked without error, the result will be "SUCCESS".

If performing a merge, a successful merge result will be "SUCCESS", followed by two new lines, then information about the merge, including which columns were added/updated. It will also contain information about columns that could not be added or updated. A merge is successful when PxPlus can connect to the database and ask it to merge in what is allowed.

If there was an error, the result will not say "SUCCESS" and just contain information about the error.

(The CreateTable call routine was added in PxPlus 2023.)

Load an External Database table with data from a PxPlus Native data file

Call "*dict/sqlmake;LoadTable", fileName$, db_prefix$, connect$, db_name$, db_table$, db_user$, db_pass$, db_options$, overwrite

Where:

fileName$

Physical file path to a native PxPlus data file with an embedded dictionary.

If db_table$ is empty, the embedded dictionary will be used to create the prefix entry.

db_prefix$

Database type. Valid types are "ODB", "MySQL", "OCI", "DB2" and "ADO". If not specified, defaults to "ODB".

connect$

Data Source or Server Address.

db_name$

Database Name or System ID.

db_table$

Name of the table in the database.

The data dictionary record will be used to create the prefix file entry. If this is empty, the fileName$ must point to a file with an embedded dictionary.

db_user$

User login name.

db_pass$

User password.

db_options$

overwrite

Flag indicating what to do if a table and/or data exists in the database.

0	User will be asked for confirmation before overwriting a file.
1	Data overwrite is allowed without confirmation.
2	An Error 12 will be returned if table exists and has records.

(The LoadTable call routine was added in PxPlus 2023.)

Add to Prefix File Entry for the External Database table

Call "*dict/sqlmake;PrefixFile", fileName$, db_prefix$, connect$, db_name$, db_table$, db_altname$, db_user$, db_pass$, db_options$, pfx_file$, stripPswd

Where:

fileName$

Physical file path to a native PxPlus data file with an embedded dictionary.

If db_table$ is empty, the embedded dictionary will be used to create the prefix entry.

db_prefix$

Database type. Valid types are "ODB", "MySQL", "OCI", "DB2" and "ADO". If not specified, defaults to "ODB".

connect$

Data Source or Server Address.

db_name$

Database Name or System ID.

db_table$

Name of the table in the database.

The data dictionary record will be used to create the prefix file entry. If this is empty, the fileName$ must point to a file with an embedded dictionary.

db_altname$

Name to use for prefix file entry Alternate file IOList. (Will default to filename$+"_ALT" if any columns have alternate names and nothing is specified.)

db_user$

User login name.

db_pass$

User password.

db_options$

pfx_file$

Prefix file to be updated. If the file does not exist, it will be created.

If empty and there is a prefix file set, then it will be used. If empty and no prefix file is set, the user will be asked to select a prefix file to update.

stripPswd

Flag indicating how to handle User Name and Password within a prefix file.

0	User Name and Password will not be stripped from the file without prompting.
1	User Name and Password will be stripped from the file without prompting.
2	The user will be prompted if he/she wants to strip User Name and Password from the file.

(The PrefixFile call routine was added in PxPlus 2023.)

Create Link File for the External Database table

Call "*dict/sqlmake;LinkFile", fileName$, db_prefix$, connect$, db_name$, db_table$, db_user$, db_pass$, db_options$, link_file$, overwrite, stripPswd

Where:

fileName$

Physical file path to a native PxPlus data file with an embedded dictionary.

If db_table$ is empty, the embedded dictionary will be used to create the link file.

db_prefix$

Database type. Valid types are "ODB", "MySQL", "OCI", "DB2" and "ADO". If not specified, defaults to "ODB".

connect$

Data Source or Server Address.

db_name$

Database Name or System ID.

db_table$

Name of the table in the database.

The data dictionary record will be used to create the link file. If this is empty, the fileName$ must point to a file with an embedded dictionary.

db_user$

User login name.

db_pass$

User password.

db_options$

link_file$

Link file to be updated. If file does not exist, it will be created.

overwrite

Flag controlling what happens if the link file already exists.

0	User will be asked for confirmation before overwriting a file.
1	Data overwrite is allowed without confirmation.
2	An Error 12 will be returned if table exists and has records.

stripPswd

Flag indicating how to handle User Name and Password within a link file.

0	User Name and Password will not be stripped from the file without prompting.
1	User Name and Password will be stripped from the file without prompting.
2	The user will be prompted if he/she wants to strip User Name and Password from the file.

(The LinkFile call routine was added in PxPlus 2023.)

Data Dictionary Maintenance

Warning! If you are using this utility to create a database table that already exists, the database table will be removed and re-created.To prevent the loss of data, ensure that table names are unique and do not already exist in the selected database.

Important Note: If the table already has records in the database, a message will display to warn that matching records will be overwritten, which could lead to possible data loss, and to ask if you want to proceed. Responding Yes will load the database table records.

Calling Export Actions Programmatically

See Also

Warning!
If you are using this utility to create a database table that already exists, the database table will be removed and re-created.

To prevent the loss of data, ensure that table names are unique and do not already exist in the selected database.

Important Note:
If the table already has records in the database, a message will display to warn that matching records will be overwritten, which could lead to possible data loss, and to ask if you want to proceed. Responding Yes will load the database table records.