Data Dictionary Maintenance

Database Import Utility

The Database Import Utility allows you to define a connection to an existing database (ODB, MySQL, OCI, ADO or DB2) and then selectively import table definitions from the selected database into the PxPlus data dictionary.

In addition, this utility can be used to:

Create Link Files that point to tables in the database
Create Link files that point to the database itself
Add entries to the Prefix File that point to tables in the database
Add entries to the Prefix File that point to the database itself
Create and load data from database tables to native PxPlus data files

The Create DB File button launches a separate window that allows you to create either a Link file or a Prefix file entry that points to the database itself.

(The Database Import Utility was added in PxPlus 2023.)
(The Create DB File button was added in PxPlus 2023 Update 1.)

The utility remembers database connection information after successfully connecting to the database when the Load Tables button is selected. Multiple database connections can be remembered, one connection for each database type. The connection information is remembered on a "per project" basis. This allows one project to use one database while another project can use a different database easily.

When the utility is invoked, the connection information for the last database that was successfully connected to is automatically loaded into the fields in the Source Database section. If you change the Database Type field and a database of that type previously connected successfully, the remembered database connection information will automatically be loaded.

(The remembering of database connection information was added in PxPlus 2023 Update 1.)

This utility lists all the tables in the database, sorted in alphabetical order by default. A check mark indicates which table definitions are currently in the data dictionary. Options for different Actions can be selected individually for each table, or the Edit All drop down can be used to select an option for all the tables. The selected actions are used to create, merge or replace data dictionary records, create native PxPlus data files, create database link files, and add database table entries to the prefix file.

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

Location	Method
From Data Dictionary Maintenance	Click the Import tool bar button in the *Database* section of the tool bar.
From Data Dictionary Maintenance	From the menu bar, select File > Database Import Utility.
From the PxPlus Command line	Enter: *RUN "dict/impdb"**
Call Import actions programmatically	See Calling Import Actions Programmatically.

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

This window consists of the following:

Source Database

Enter the database information.

The utility remembers the database connection information after successfully connecting to the database (when the Load Tables button is selected) and automatically loads this information into these fields when the utility is invoked.

(The automatic loading of database connection information was added in PxPlus 2023 Update 1.)

Database Type	Type of database. Selections are ODB, MySQL, OCI, DB2 and ADO. Defaults to ODB.
Data Source or Server	*(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: The Server 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)*
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.)

Create DB File

Button that launches the Create Database Connection File window that allows you to create either a Link file or a Prefix file entry that points to the database itself.

A direct connection to the database may be useful so that you can do a global OPEN to the database, providing the UserID and Password. This avoids needing the UserID and Password on every link/prefix entry.

It can also be used to get a list of tables from the database or if your program needs to make direct queries against the database.

This window consists of the following:

Database File Type	Select the database file type to create, either Link File (Default) or Prefix File Entry.
Link File Path	*(Applicable when Database File Type is Link File)* Enter the path for the link file or click the Query button.
Prefix File Entry Name	*(Applicable when Database File Type is Prefix File Entry)* Enter the prefix file entry name.
Create	Creates the link file in the specified path or creates the entry in the prefix file, depending on which Database File Type is selected.
Cancel	Closes the Create Database Connection File window with no further action taken.

(The Create DB File button was added in PxPlus 2023 Update 1.)

Load Tables

Button that is used to load tables from the selected database into the Tables grid. If changes have been made in the grid but not applied, a message will display.

When the database connection is successful, the connection information (one connection for each database type) is remembered on a "per project" basis and is automatically loaded when the utility is invoked.

(The remembering of database connection information was added in PxPlus 2023 Update 1.)

(Tables Grid)

Grid that lists all the tables in the selected database.

This list can be sorted by clicking on the column header for any of the columns except DD Action and File Action. Subsequent clicks toggle the sort between ascending/descending order. By default, the list is sorted by Table name in ascending order.

(Column sorting for applicable columns was added in PxPlus 2023 Update 1.)

This grid consists of the following:

Edit All

The changes made to the DD Action and File Action options in this row will be applied to all the tables in the list.

For DD Action, available selections are:

No Action	*(Default)* No action to apply.
Create or Merge Record	Sets the DD Action to either Create Record or Merge Record for each table in the list, depending on whether the table is in the data dictionary.
Create or Replace Record	Sets the DD Action to either Create Record or Replace Record for each table in the list, depending on whether the table is in the data dictionary.

Table

(Display Only) Displays the name of each table from the selected database.

In DD (DD = Data Dictionary)

(Display Only) Displays a check mark if the table is in the data dictionary or an X if it is not in the data dictionary.

File Type

(Display Only) Displays the current physical file type from the data dictionary.

Possible values are:

No File	No physical file exists.
Native	A native PxPlus data file exists.
Native Prefix	A prefix file entry points to a native PxPlus data file.
[ODB\|MySQL\|ADO\|DB2\|OCI] Prefix	A prefix file entry points to an external database table.
[ODB\|MySQL\|ADO\|DB2\|OCI] Link	A link file points to an external database table.

(The File Type column was added in PxPlus 2023 Update 1.)

Description

Displays the table description from the data dictionary. If it is not in the data dictionary, this will be the database table name in proper case with underscores replaced with spaces. A new description can be entered, if desired.

The description is updated only when DD Action is set to something other than No Action.

File Name

Displays the current physical file name from the data dictionary. If it is not in the data dictionary, this will be a lowercase version of the database table name. A new file can be entered, if desired.

The file name is updated only when DD Action is set to something other than No Action.

Group

Displays the current group name of the table from the data dictionary. If it is not in the data dictionary, this will be the database name, if specified. A new group name can be entered, if desired.

The group is updated only when DD Action is set to something other than No Action.

DD Action (DD = Data Dictionary)

Action options that can be set for loading the data dictionary. Click the drop down arrow for available selections. To edit this option for all the tables in the list, use the Edit All drop down (in top row).

If the table is in the data dictionary, available options are No Action, Merge Record or Replace Record.

If the table is not in the data dictionary, available options are No Action or Create Record.

No Action	*(Default)* No action to apply.
Merge Record	*(Available when table is in the data dictionary)* Updates an existing record in the data dictionary so that only what comes from the database is updated, and other details, such as class, description, etc., are maintained. If new elements are being added from the database, they will be added to the end. Keys/Indices will not be merged or changed.
Replace Record	*(Available when table is in the data dictionary)* Deletes an existing record in the data dictionary and creates a new record.
Create Record	*(Available when table is not in the data dictionary)* Creates a new record in the data dictionary.

Note:
Key names from the PxPlus data dictionary will have the #num_name_table format upon exporting to an external database. (See Database Export Utility.) If those tables are imported again, 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.

If an error is encountered:

If an error is encountered while populating the data dictionary record with columns and keys, the data dictionary record will not be deleted, and the DD Action will be considered incomplete. However, it is possible that the incomplete data dictionary record will be nearly complete. For example, the error could happen when trying to add the sixth key and your PxPlus application has no need for that sixth key.

A DD Action is considered a success if the record is created and populated with all columns and keys from the database table.

A DD Action is considered a failure if a DD record could not be created or replaced.

File Action

Action options that are related to creating/editing files - a prefix (PFX) file entry, a link file or a native (PxPlus) file. Click the drop down arrow for available selections. To edit this option for all the tables in the list, use the Edit All drop down (in top row).

No Action	*(Default)* No action to apply.
Add to PFX File	Creates a new entry in the prefix file to the table in the database. If no prefix file is defined, the user will be prompted to select or enter a name for a prefix file to be updated/created.
Create Link File	Creates a link file that points to the table in the database.
Create & Load Native File	Creates a native PxPlus keyed file and loads it with data from the database. This allows you to use a local copy of the data directly.

If a prefix file entry, link file or a native file already exists and this action is specified, it will remove the existing file/entry. If an existing native file is found, a message will display to warn about possible data loss and ask if you want to proceed unless the Disable Data Loss Warning check box is set.

Strip User/Password from Link Files and Prefix Entries

Controls whether the User and Password connection options are stripped from link files and prefix file entries.

By default, this check box is selected. For security reasons, it is strongly recommended not to save the User and Password information in plain text.

Disable Data Loss Warning

If selected, disables a warning message from displaying for each File Action that will delete a native file, causing possible data loss. By default, this check box is not selected to help prevent accidental data loss.

If you are applying several File Actions and are knowingly replacing native data files, you can set this option to avoid displaying a warning message for each table.

View Log

(Available when changes have been applied)

Displays a log of applied changes as a PDF, which also provides options for printing or saving the log, if desired.

Apply

(Available when DD Action or File Action is set to other than No Action)

Applies changes to all the tables that had a DD Action or File Action set to something other than No Action. A message displays that lists which changes were applied successfully, which changes were incomplete (partially successful), and which changes failed.

Exit

Closes the Database Import Utility. If changes have been made in the grid but not applied, a message will display. If records exist in the log file, a message will ask if you want to view the log.

Calling Import Actions Programmatically

The individual Import actions (i.e. CreateDDRecord, CreateAndLoadFile, etc.) can be invoked programmatically by calling the following routines:

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

Call "*dict/impdb;CreateDDRecord", db_prefix$, connect$, db_name$, db_user$, db_pass$, db_options$, tableName$, description$, fileName$, groupName$, replace, result$

Where:

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_user$	User login name.
db_pass$	User password.
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.
tableName$	Name of the table in the database. This will also be used as the data dictionary entry name.
description$	Desired data dictionary entry description.
fileName$	Desired physical file path of the data dictionary entry.
groupName$	Desired data dictionary entry group.
replace	Flag indicating if the data dictionary already exists, whether the entry should be merged or replaced. If set to 1, the entry will be replaced. If set to 0, the entry will be merged. If the entry is new, this flag will have no effect.
result$	After the call, check this string for the result of the call. If it worked without error, the result will be "SUCCESS". If there was an error, it will contain information about the error.

Add to Prefix File Entry for the External Database table

Call "*dict/impdb;AddToPfxFile", db_prefix$, connect$, db_name$, db_user$, db_pass$, db_options$, tableName$, fileName$, stripPswd, noDataLossWarning, result$

Where:

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_user$	User login name.
db_pass$	User password.
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.
tableName$	Name of the table in the database.
fileName$	Desired physical file path of the prefix file entry. This path can then be used to open the database connection as if it were a physical file.
stripPswd	Flag indicating whether the User and Password should be stripped from link files and prefix file entries. If set to 1, the User and Password will be stripped. If set to 0, nothing will be stripped out.
noDataLossWarning	Flag indicating whether the user should be warned about possible data loss if a native file exists that will be deleted because the prefix file entry replaces it. If set to 1, the user will not be warned. If set to 0, the user will be warned.
result$	After the call, check this string for the result of the call. If it worked without error, the result will be "SUCCESS". If there was an error, it will contain information about the error.

Create Link File for the External Database table

Call "*dict/impdb;CreateLinkFile", db_prefix$, connect$, db_name$, db_user$, db_pass$, db_options$, tableName$, fileName$, stripPswd, noDataLossWarning, result$

Where:

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_user$	User login name.
db_pass$	User password.
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.
tableName$	Name of the table in the database.
fileName$	Desired path of the created link file.
stripPswd	Flag indicating if the User and Password should be stripped from link files and prefix file entries. If set to 1, the User and Password will be stripped. If set to 0, nothing will be stripped out.
noDataLossWarning	Flag indicating whether the user should be warned about possible data loss if a native file exists that will be deleted because the link file replaces it. If set to 1, the user will not be warned. If set to 0, the user will be warned.
result$	After the call, check this string for the result of the call. If it worked without error, the result will be "SUCCESS". If there was an error, it will contain information about the error.

Create and Load Physical File from the External Database table

Call "*dict/impdb;CreateAndLoadFile", db_prefix$, connect$, db_name$, db_user$, db_pass$, db_options$, tableName$, fileName$, noDataLossWarning, result$

Where:

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_user$	User login name.
db_pass$	User password.
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.
tableName$	Name of the table in the database.
fileName$	Desired path of the native keyed file to create and load with data from the database.
noDataLossWarning	Flag indicating whether the user should be warned about possible data loss if a native file exists that will be deleted because the new native file replaces it. If set to 1, the user will not be warned. If set to 0, the user will be warned.
result$	After the call, check this string for the result of the call. If it worked without error, the result will be "SUCCESS". If there was an error, it will contain information about the error.

Data Dictionary Maintenance

Calling Import Actions Programmatically

See Also