Data Dictionary Maintenance

Database Conversion Utility

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

(The Database Conversion Utility was added in PxPlus 2019.)

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 invoke this utility, use any 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 Database Convert tool bar button.

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 Conversion Utility.

From the PxPlus Command line

Enter: Run "*dict/sqlmake

Call the CONVERT method programmatically

Call the *dict/sqlmake;convert method. See Calling the CONVERT Method Programmatically below.

The Database Conversion 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.

Database Type

Type of database. Selections are ODB, MySQL, OCI, DB2 and ADO. 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=YES for all Database Types except OCI.

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, the table will be removed and re-created.

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 successful, a message will indicate the number of records written to the table.

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.

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 trace

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 Conversion Utility. Any saved prefix file setting will be reset the next time the utility is accessed.

Calling the CONVERT Method Programmatically

Besides invoking the Database Conversion 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:

CALL "*dict/sqlmake;convert" [,ERR=stmtref],filename$, db_type$, connect$, db_name$, db_table$, db_altname$, db_user$, db_pass$, db_options$[,pfx_file$]

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.

 

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.

 

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.

 

db_pass$

User password.

 

db_options$

Options to be used as OPT= parameter when opening the database or updating the prefix file. If not specified, defaults to "NONULLS=YES" for all database types except OCI.

 

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.

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.)