Using SQL Scripts

The installer provides two sets of scripts. One set of customizable scripts to initially load data, and the other set for creating the TPM tables if you chose not to install them during installation. The iWay TPM installer creates a sql directory under the root installation (for example, C:\Program Files\tpm\tpm\sql) with an initload sub-directory and with sub-directories for each database (db) as shown in the following image.

Initload sub-directory:

The initload sub-directory contains sub-directories for each database containing sample customizable scripts to perform an initial load. You can update these customizable scripts to create new custom packaging that targets a specific type of client. Update the script with your SQL to provide an initial load specific to that type of client.

You can load initial data using a custom script as follows:

    Procedure

  1. Update the sample script for the database you are using.

  2. Edit the application.properties file found in tpm installation folder.

  3. Look for the section labeled Initial Load and uncomment, and if necessary, edit the relative or absolute path to the sql\initload directory. If you did not use the default load.sql name for your script, edit the name of the custom script as per the name used.

  • mssql>load.sql Update this customizable script with MSSQL to provide an initial load.
  • oracle>load.sql Update this customizable script with Oracle SQL to provide an initial load.
  • postgres>load.sql Update this customizable script with PostgreSQL to provide an initial load.

Database sub-directories:

The following three sub-directories provide database specific scripts to create or delete TPM tables.

MSSQL:

  • tpm_mssql.sql. Creates the iWay TPM tables on MSSQL.
  • tpm_mssql_drop_tables.sql. Drops (deletes) existing iWay TPM tables on MSSQL.
  • tpm_mssql_migration.sql. This SQL script is for an existing instance of iWay TPM, which migrates your database from an older version of iWay TPM to the latest version of iWay TPM. Use this script if you have not chosen the installer to handle the migration during installation. Contact Customer Support for additional post-migration steps.

Oracle:

  • tpm_oracle.sql. Creates the iWay TPM tables on Oracle.
  • tpm_oracle_drop_tables.sql. Drops (deletes) existing iWay TPM tables on Oracle.

PostgreSQL (Postgres):

  • tpm_postgres.sql. Creates the iWay TPM tables on PostgreSQL.
  • tpm_postgres_drop.sql. Drops (deletes) existing iWay TPM tables on PostgreSQL.

If you did not initialize the database during installation, run the appropriate database script to create TPM tables before starting iWay TPM.

The following image shows an SQL script being run using Microsoft SQL Server Management Studio.

The SQL script was pasted into a query window, the database focus was changed to the database that needed to be modified, and then the query was executed.