Data Converter







RM/Cobol to Firebird

Data Converter




Table of Contents

1. Introduction 5

2. Getting Started 6

2.1. Creating a new Project 6

2.2. Creating a Target from the Cobol Record Definition 7

2.3. Creating a Target by Importing an Existing Table Definition 9

2.4. Mapping Source Fields to Target Fields 10

2.5. Running Your Project 11

3. Working with Projects 13

3.1. Creating a new Project 13

3.2. Opening an Existing Project 14

3.3. Saving a Project 14

3.4. Saving a Project with a New Name 14

3.5. Running a Project 15

3.6. The Project Workspace 17

4. Working with the Data Source 18

4.1. Notes on Data Types 18

4.1.1. Alphanumeric to VarChar or Char 18

4.1.2. Alphanumeric to Numeric 18

4.1.3. Numeric to VarChar or Char 18

4.1.4. Numeric to Numeric 18

4.1.5. Dates and Times 18

4.2. Selecting Fields for Conversion 18

4.2.1. Selecting Individual Fields 18

4.2.2. Selecting All Fields 18

4.2.3. Selecting Group Items 19

4.2.4. Selecting Elementary Items 19

4.2.5. Deselecting All Fields 19

4.3. Mapping Source Fields to Target Fields 19

4.4. Unmapping Fields 19

4.4.1. Unmaping All Fields 19

4.4.2. Unmapping a Single Field 19

4.5. Using a Script 19

4.6. Using a Filter 20

4.7. Viewing the Source Data 21

5. Working with Targets 22

5.1. Creating a New Target 22

5.2. Deleting a Target 22

5.3. Renaming a Target 22

5.4. Importing a Table Definition 22

5.5. Viewing the Target's DDL 23

5.6. Unmapping Fields 23

5.6.1. Unmaping All Fields 23

5.6.2. Unmapping a Single Field 23

5.7. Working with Indices 23

5.7.1. Index Properties 24

5.7.2. Working with Index Fields 25

5.7.3. Adding a New Index 25

5.7.4. Renaming an Index 25

5.7.5. Deleting an Index 25

5.7.6. Saving the Changes 25

5.7.7. Discarding the Changes 25

5.8. Modifying a Field's Properties 25

5.9. Viewing the Target Data 26

6. Working with Scripts 27

6.1. Creating a New Script 27

6.2. Modifying an Existing Script 27

6.3. Deleting a Script 27

6.4. IGOR Plugin Functions 27

6.4.1. SetValue 27

6.4.2. SaveVar 28

6.4.3. LoadVar 28

7. Deploying Your Project 29

7.1. Files to Deploy 29

7.2. Running a Project from the Command Line 29



1. Introduction

In order to migrate RM/Cobol data from Cobol native file and data formats to a relational database, the data must pass through a conversion process. The record definition must be converted from Cobol syntax to relational data definition language (DDL). The data for each record must be extracted from the indexed, relative or sequential file and the data for each field must be converted from native Cobol data types, such as COMP-4 (binary) and COMP-3 (packed), to standard relational data types, such as Numeric and Date. In addition to standard data type conversion it may also be necessary to massage the data for particular fields to make them conform to standard relational data types. The data converter contains several features that reduce the amount of programming required as part of the migration process.

Cobol record definitions are imported directly from the Cobol record definition. The Cobol source code for the record definition is parsed and the result is used extract the data directly from the Cobol source file without the need to write any Cobol code. The parsed record definition can also be used to create the DDL required to create the corresponding relational table. The automatically generated relational table definition can be edited as needed to obtain the optimal relational table format. For instance, the data type of individual columns can be changed and indices can be created, modified or deleted as needed.

Each Cobol field can be mapped to (associated with) the corresponding relational column. The data converter supports many-to-many mapping. That is, multiple source fields can be mapped to one or more relational columns and a relational column can be mapped from one or more source fields. This allows great flexibility on the migration process. For instance, it is possible to convert the data contained in the source record to multiple relational tables. This allows arrays in the source record to be converted to individual rows in a relational table.

The data from the source fields is automatically converted from the Cobol native data type to the appropriate relational data type. For fields that have special requirements, scripts can be written to massage the data before it is written to the target table. Scripts can be attached to the source field or the target field or both. Scripts attached to source fields will be executed before scripts attached to target fields.

The data from the source file can be filtered, allowing only a subset of the records from source file to be converted. This allows files with multiple record types to be converted in a manner appropriate to the particular record type.



2. Getting Started

The information required by the data converter to convert a particular chunk of Cobol data is organized into a project. Each project can convert all, or some, of the records from a single Cobol file into one or more relational tables. So it follows that the first step in the conversion process is the creation of a project.

Once you have created a project you need to start defining your target tables. This can be done by either selecting fields from the source file and copying them to the target or by importing the definition of an existing relational table. If you choose to create a target from the Cobol record definition you will likely have to modify the target definition to suit your needs. At a minimum you will likely need to create one or more indices.

The following sections provide a brief overview of how to use the data converter for a simple file conversion. Later chapters of this manual provide a more detailed explanation of the data converter's advanced features.

2.1. Creating a new Project

Before you create a project you must have the Cobol source code which defines the record format, the source file name, its organization (indexed, relative, etc) and the connection parameters for the target database. To create a new project, select Project | New from the main menu. Enter the following information:



Source File Enter the path to the Cobol data file. You can either enter the path manually or browse for the file using the button to the right of the edit box. This path is used during design and testing but it can be overridden when the project is run in production mode.

File Type Select the appropriate file type from the list of choices. The type chosen must match the actual type of the source file.

Record Defn Enter the Cobol source code for the record definition. Enter only the code for the record definition, omit the FD and SELECT statements. The size of the record definition must match the record size of the source file. You can enter the record definition manually, copy and paste it from another application or import the copybook by using the button to the right of the edit box.

DB Path Enter the Firebird connection string for the target database. This database is used during design and testing but it can be overridden when the project is run in production mode.

User ID Enter the Firebird user id required to access the database.

Password Enter the Firebird password required to access the database.


Illustration 2.1: New Project Properties






Click the OK button. The project designer will be displayed with the Cobol data fields listed in the list box on the left side of the window.

Now that you have created a project and defined the data source you must define one or more targets.

2.2. Creating a Target from the Cobol Record Definition

If you have already created your relational tables you can skip this step and proceed with the next step.

Initially, the target pane on the right side of the window is empty. To create a new target, right click anywhere in the target pane and select New Target. This will create a new tab with an empty list box. To name your target, and hence the relational table, right click the target pane again and select Rename Target. Enter the target name when prompted and click OK.

If you are starting a conversion completely from scratch, that is you haven't defined your relational tables yet, you will probably want to create your table definition using the Cobol record definition as a template. To do this, simply select the Cobol fields that you want to have in your relational table by checking the box to the left of the desired fields. You can also choose to select all fields, group items or elementary items by right clicking in the source pane and selecting the appropriate menu item. Then click the “right arrow” button on the tool bar at the bottom of the window. This will copy the Cobol source fields to the target list box. As part of the copy process the fields will be renamed with SQL compliant column names and the data types will be converted to a compatible relational data type. For simple conversion projects this will be enough to get you started. For more complex projects you will probably need to massage the default data types for the relational columns and create some indices for the table. This will be covered later in this manual.




Illustration 2.2: Source pane with desired fields checked







At this point, it is worth pointing out a few properties of the source pane field list. If you look at the above illustration you will notice that the first few fields in the list a preceded by the letter 'I'. These fields are part of one or more of the Cobol file's indices. You will want to make note of these fields when designing the indices for the relational table. Also, some of the source fields are preceded by the letter 'R'. These fields REDEFINE another of the source fields. It is important to note that only the field corresponding to the line in the source code with the REDEFINES clause will be shown this way. Fields subsidiary to the redefining field will not be marked with the letter 'R'. You will also notice that some fields in the list are indented further than other fields. This helps to highlight fields that are subsidiary to group items. For example, AC-REC-TYPE is subsidiary to (is an elementary item of) the group field AC-KEY. You will also notice that the middle column provides the value of the Cobol PICTURE clause for each field and the last column provides the Cobol data type of each field.




Illustration 2.3: Target pane with fields created from Cobol source fields







2.3: Creating a Target by Importing an Existing Table Definition

If you created your target table definition in the previous step you can skip this step and proceed with the next step.

If you have already created the target table you can import the definition of that table into your project. You must first create a target in your project. To create a new target, right click anywhere in the target pane and select New Target. This will create a new tab with an empty list box.

To import your table definition into the newly created target, right click anywhere in the target pane and select Import Table. You will be prompted for the table name. Enter the table name and click OK. The field definitions for your table will appear in the target list box.


Illustration 2.4.: Target pane with field definitions from table ACCOUNTS







2.4. Mapping Source Fields to Target Fields

Once you have specified the source and target record definitions you will need to create a map telling the data converter which source fields get converted into which target fields. To do this you simply left click the desired field in the source pane and drag it to the target pane. When the matching target field is highlighted in the target pane, release the left mouse button. This will map the source field to the target field and a red line will be drawn from the source pane to the target pane showing that these fields have been mapped to each other.

If you make a mistake and decide that two fields should not be mapped to each other you can unmap them by right clicking the field in either the source or target panes and selecting Unmap from the pop up menu. You can unmap all fields by right clicking in either the source or target panes and selecting Unmap ALL from the pop up menu.




Illustration 2.5.: Source and target panes with mapped fields






2.5. Running Your Project

Now that you have defined your source and target fields and the relationships between them you can convert your file by running your project. To do this select Project | Run from the menu. This will cause the run time parameters form to be displayed. Choose appropriate values for the following parameters:

Create Target Tables If this box is checked the target tables will be created in the database if they do not already exist.

Empty Tables If this box is checked all records will be deleted from the target tables before the conversion process begins.

Max. Records If you want to convert a small subset of records for testing purposes, enter the number of records to be converted here. If this field is blank or zero all records in the source file will be converted.

Source File If you want to convert a different file from the one used when you created the project enter the path to that file here. This field will be pre-filled with the path of the file used to create the project.

DB Path If you want to convert the data into a database other than the one used when you created the project, enter the path to that database here. This field will be pre-filled with the path of the database used to create the project.

User ID If you want to connect to the database with a user id other than the one used when you created the project, enter that user id here. This field will be pre-filled with the user id used to create the project.

Password If you want to connect to the database with a password other than the one used when you created the project, enter that password here. This field will be pre-filled with the password used to create the project.

When all values have been set, click the Run button.




Illustration 2.6.: Run time parameters




3. Working with Projects

A data converter project encapsulates all of the information needed to convert some or all of the records in a single Cobol data file to one or more relational tables. A project stores information about the source file's record definition, the target table definitions, the scripts needed to perform specialized field conversion, and so on.

Projects are stored as files on disk with an extension of CCP. The project information is encoded within these files in XML format. If desired, project files and be viewed using any XML viewer, like a web browser, and can be modified manually using a text editor. Caution must be used when manually editing projects, since an error could render the project unusable.

3.1. Creating a new Project

To create a new project select Project | New from the menu or click the New Project button on the tool bar. When the New Project form appears enter the following information:

Source File Enter the path to the Cobol data file. You can either enter the path manually or browse for the file using the button to the right of the edit box. This path is used during design and testing but it can be overridden when the project is run in production mode.

File Type Select the appropriate file type from the list of choices. The type chosen must match the actual type of the source file.

Record Defn Enter the Cobol source code for the record definition. Enter only the code for the record definition, omit the FD and SELECT statements. The size of the record definition must match the record size of the source file. You can enter the record definition manually, copy and paste it from another application or import the copybook by using the button to the right of the edit box.

DB Path Enter the Firebird connection string for the target database. This database is used during design and testing but it can be overridden when the project is run in production mode.

User ID Enter the Firebird user id required to access the database.

Password Enter the Firebird password required to access the database.




Illustration 3.1: New Project Properties




When you have entered all of the necessary information, click the OK button to create the new project.

3.2. Opening an Existing Project

To open an existing project so that you can make changes to it or modify it, select Project | Open from the menu or click the Open Project button on the tool bar. You will be prompted for the project file name. You may either enter the file name manually or use the file explorer to browse to the file. To open the project click the Open button.




Illustration 3.2.: Project file selection dialog




3.3. Saving a Project

When you have finished creating a new project, or making changes to an existing project, you will want to save your changes. To do this, select Project Save from the menu or click the Save Project button on the tool bar. If you are saving a new project, you will be prompted to enter the file name of the new project file. See the following section for details about saving a project file with a new name.

3.4. Saving a Project with a New Name

To save a project file with a new name, select Project | Save As from the main menu. You will be prompted to enter the new name of the project file. You may use the file explorer to browse to the directory where you want your new file saved. Then enter the new name of the file in the File name edit box and click the Save button to save the file.




Illustration 3.3.: Project file save dialog






3.5. Running a Project

To convert your Cobol data into your targets you must run the project. To run your project, select Project | Run from the menu or by clicking the Run Project button on the tool bar. This will cause the run time parameters form to be displayed. Choose appropriate values for the following parameters:

Create Target Tables If this box is checked the target tables will be created in the database if they do not already exist.

Empty Tables If this box is checked all records will be deleted from the target tables before the conversion process begins.

Max. Records If you want to convert a small subset of records for testing purposes, enter the number of records to be converted here. If this field is blank or zero all records in the source file will be converted.

Source File If you want to convert a different file from the one used when you created the project enter the path to that file here. This field will be pre-filled with the path of the file used to create the project.

DB Path If you want to convert the data into a database other than the one used when you created the project, enter the path to that database here. This field will be pre-filled with the path of the database used to create the project.

User ID If you want to connect to the database with a user id other than the one used when you created the project, enter that user id here. This field will be pre-filled with the user id used to create the project.

Password If you want to connect to the database with a password other than the one used when you created the project, enter that password here. This field will be pre-filled with the password used to create the project.

When all values have been set, click the Run button.


Illustration 3.4.: Run time parameters








3.6. The Project Workspace

The main data converter window is referred to as the project workspace. The workspace is divided into three main areas; the source pane, the target pane and the field map. The source pane is the beveled area on the left side of the window, the target pane is the beveled area on the right side of the window and the field map is the area between the source and target panes. Right clicking in either pane will cause the context menu for that pane to appear. See the following chapters for detailed information about how to interact with the source and destination panes.




Illustration 3.5.: Project workspace








4. Working with the Data Source

4.1. Notes on Data Types

When converting data from Cobol data types to relational data types there are a few important considerations. Most of these will seem pretty obvious, but some are not. Please read the following sections carefully.

4.1.1. Alphanumeric to VarChar or Char

Alphanumeric data can be converted to VarChar or Char data types without restriction. It should be noted that data converted to VarChar data types will be trailing space truncated before being stored into the target table. In most cases this shouldn't make any difference but if your application depends on the trailing spaces in some arcane way then you would be better off using Char. For most purposes VarChar is preferred due to faster transfer speeds.

4.1.2. Alphanumeric to Numeric

It is completely possible to convert alphanumeric (PIC X) fields to a relational numeric data types. However, be aware that if non-numeric characters are encountered in the source field the conversion project will throw an error and terminate. If you feel the need to convert alphanumeric fields to numeric data types you need to be certain of the integrity of your data, either by preprocessing the source file with a Cobol program or using a script to massage the alphanumeric source fields at conversion time.

4.1.3. Numeric to VarChar or Char

It is completely possible to convert numeric source fields to VarChar or Char data types. The source data will be left justified into the target field with leading zeros suppressed an optional leading minus sign. The source field will NOT be moved to the target field using the Cobol rules for moving numeric to non-numeric fields.

4.1.4. Numeric to Numeric

RM/Cobol is pretty lax about what can and cannot be stored into a numeric field. The same caveats that apply to converting alphanumeric fields to numeric apply to converting numeric fields to numeric except that the conversion will convert non-numeric values as zero rather than throwing an error.

4.1.5. Dates and Times

To convert a Cobol field to a date data type the date must be in YYYY/MM/DD format. Since Cobol dates are almost never stored this way you will, in all likelihood, have to write a script to massage the source data in order to make it acceptable.

To convert a Cobol field to a time data type the time must be in HH:MM:SS format. Since Cobol times are almost never stored this way you will, in all likelihood, have to write a script to massage the source data in order to make it acceptable.

To convert a Cobol field to a date/time (timestamp) data type the data and time must be given as a single string in YYY/MM/DD HH:MM:SS format. Again you will probably need to write a script to massage the source data to make it acceptable.

4.2. Selecting Fields for Conversion

It is possible to create the field definitions for the target table by using the data fields in the Cobol source file as a template. To to this, you select fields from the source file to be included in the target table. Once you have selected the source fields that you want to include in the target table you copy them to the target by clicking the right facing arrow button in the tool bar. Source fields can be selected in various ways as described by the following sections.

4.2.1. Selecting Individual Fields

You can select individual fields from the source file by clicking the check box to the left of the field name. Any field with a check mark appearing in this box is considered selected.

4.2.2. Selecting All Fields

You can select all source fields for inclusion in the target table by selecting Select All Fields from the source pane context menu.

4.2.3. Selecting Group Items

Cobol data fields fall into two broad categories; group items and elementary items. Group items have no PICTURE clause. Their format is described by a number of data fields which are subsidiary to the group item. In the following example, GROUP-ITEM is a group item:

01 GROUP-ITEM.
03 ELEM-ITEM1 PIC X(5).
03 ELEM-ITEM2 PIC X(10).

You can select all group items for inclusion in the target table by selecting Select Group Items from the source pane context menu.

4.2.4. Selecting Elementary Items

You can select all elementary items for inclusion in the target by selecting Select Elementary Items from the source pane context menu. If you are uncertain what an elementary item is, please see the previous section for a discussion of group and elementary data items.

4.2.5. Deselecting All Fields

If at any time you need to deselect all fields, that is clear all the check boxes, select Deselect All Fields from the source pane context menu.

4.3. Mapping Source Fields to Target Fields

Once you have finalized the record definitions of your various targets you will need to map (associate) source fields to target fields. Only source fields that are mapped to a target field will be converted. To map a source field to a target field, left click the source field that you wish to map and drag it to the appropriate target field. When your mouse cursor is positioned over the appropriate target field (and the target field is highlighted) drop the source field onto the target field. A red line will then be drawn from the source field to the target field to show that these fields are mapped to each other.

It is possible to map a source field to several different target fields, either in the same or different targets. In this case the source field is simply copied once to each target field. It is also possible that a target field may be mapped to several source fields. In this case, a unique row will be created in the target table for each source field mapping.

4.4. Unmapping Fields

4.4.1. Unmaping All Fields

If you want to unmap all source fields select Unmap All Fields from the source pane context menu. NOTE: Unmapping all source fields unmaps all fields for all targets, not just the currently selected target.

4.4.2. Unmapping a Single Field

To unmap a single source field, right click on the field to be unmapped. Then select Unmap from the context menu. The field that you clicked on will be unmapped from all targets.

4.5. Using a Script

As mentioned in the section on data types, there are times when you will have to write a script to massage the source data before converting it. This may be to ensure that numeric data is actually numeric or to present a date or time field in a relational friendly format. If you need to use a script to preprocess a source field, right click on the field needing the script to display the source pane context. Either enter the name of the script or use the button to the right of the edit box to browse for the script file. When you have selected the appropriate script, click the Save button




Illustration 4.1.: Script name dialog






Please see the Working with Scripts chapter for detailed information about writing scripts.

4.6. Using a Filter

At times it may be useful to convert only a subset of the records contained in the source file. For example, when a file contains multiple record types it may be necessary to define separate conversion projects for each record type. You can do this by specifying a filter for the source file. To create a filter, select Filter from the source pane context menu. Enter the filter expression into the edit box and click the Save button.




Illustration 4.2.: Filter dialog




The filter expression is an SQL-like expression specifying which records are to be converted from the source file. If the filter expression returns TRUE then the source record will be converted, otherwise it will not.

A filter expression works by comparing the content of fields to constants or the content of other fields using a set of operators. There are two types of operators; unary operators and binary operators. Unary operators reference only one field or constant. Binary operators reference two fields or constants.

The available unary operators are:

IS BLANK returns TRUE if the operand is blank or NULL.
AC-REC-TYPE IS BLANK

IS NOT BLANK The opposite of IS BLANK. Returns TRUE if the operand is not blank and not NULL.
AC-REC-TYPE IS NOT BLANK

NOT Negates the result of the following expression. Returns TRUE if the following expression is FALSE and vice versa.
NOT AC-REC-TYPE = 'AR'

The available binary operators are:

AND Returns TRUE if the two operands are TRUE. Returns FALSE otherwise.
AC-REC-TYPE = 'AR' AND AC-SEQ = '00'

OR Returns TRUE if either of the two operands are TRUE. Returns FALSE otherwise.
AC-REC-TYPE = 'AR' OR AC-REC-TYPE = 'BR'

= Returns TRUE if the two operands are equal.
AC-REC-TYPE = 'AR'

<> Returns TRUE if the two operands are not equal.
AC-REC-TYPE <> 'AR'

> Returns TRUE if operand1 is greater than operand2.
AC-REC-TYPE > 'AR'

< Returns TRUE if operand1 is less than operand2
AC-REC-TYPE < 'AR'

>= Returns TRUE if operand1 is greater than or equal to operand2
AC-REC-TYPE >= 'AR'

<= Returns TRUE if operand1 is less than or equal to operand2
AC-REC-TYPE <= 'AR'

LIKE Returns TRUE if operand1 matches the wildcard pattern given by operand2. Operand2 is a string containing the two wildcard characters '_' (underscore) and '%' (percent). Underscore matches a single character from operand1 and percent matches an arbitrary number of character from operand1.
AC-NAME LIKE 'A_B%' would return TRUE for all records where the first character of AC-NAME is 'A', the second character is anything, the third character is 'B' and the rest of the field is any combination of characters. For example, 'AAB123', 'ABB234', etc.

IN Returns TRUE if operand1 matches any of the values given by operand2, which must be a list of values.
AC-REC-TYPE IN ('AR', 'BR')

Operators are evaluated in order of their precedence. Unary operators (BLANK, NOT BLANK and NOT) are evaluated first, then relational operators (=, <>, >, <, >=, <=, LIKE and IN) are evaluated next and logical operators (AND and OR) are evaluated last. Subexpressions can be enclosed in parentheses to change the evaluation order.

4.7. Viewing the Source Data

To preview the data contained in the source file, select View from the source pane context menu. This will open a window showing all records from the source file which match the current filter.




Illustration 4.3.: Source file preview dialog






5. Working with Targets

5.1. Creating a New Target

To add a new target table to your project select New Target from the target pane context menu. This will create an untitled, empty target. You will need to give the target a name by renaming it and you will have to add fields to the target by either using source fields as a template or by importing an existing relational table into the target.

5.2. Deleting a Target

You can remove a target table from your project by selecting Delete Target from the target pane context menu. This will remove the target from the project permanently. Once a target has been deleted there is no way to recover it.

5.3. Renaming a Target

To give a target a name or to change its name, select Rename Target from the target pane context menu. You will be prompted for the new target name. Enter it into the edit box and click the OK button.




Illustration 5.1.: Target name dialog




5.4. Importing a Table Definition

As an alternative to creating the target's fields by using the source fields as a template, in effect creating the target table from scratch, you can import the definition of an existing relational table into the target. This will create the target field list and indices from the fields and indices defined for the table in the database's meta data.

To import an existing table definition, select Import Table from the target pane context menu. You will be prompted for the table name. Enter it into the edit box and click the OK button.




Illustration 5.2.: Import table dialog




It is important to note that importing an existing table definition imports only the field and index definitions. Things such as field constraints and foreign key relationships, that are not important to the conversion process, are not imported and are not saved as part of the project. Hence, it is not a good idea to have the data converter recreate the table from the imported definition since it is possible that the newly created table will not be identical to the original.

5.5. Viewing the Target's DDL

You can preview the Data Definition Language (DDL) used to create the target table by selecting Show DDL from the target pane context menu. You can use cut and paste to copy the DDL to your favorite database management tool to create the table or you can create the table when the project is run.




Illustration 5.3.: Show DDL dialog




5.6. Unmapping Fields

5.6.1. Unmaping All Fields

If you want to unmap all target fields select Unmap All Fields from the target pane context menu.

5.6.2. Unmapping a Single Field

To unmap a single target field, right click on the field to be unmapped. Then select Unmap from the context menu. The field that you clicked on will be unmapped from the current target.

5.7. Working with Indices

If you are designing your target table from scratch you will probably want to create one or more indices for it. At the least you will want to have a primary key for your target table. The data converter provides an index designer for just that purpose. To start the index designer, select Indices from the target pane context menu.

The index designer has two main areas; a list of all of the table's fields on the left and a list of the fields comprising the index on the right. The order of the fields in the index fields pane determines the precedence of the fields in the index. The first field in the list is the most significant and so on.




Illustration 5.4.: Index designer dialog




5.7.1. Index Properties

Every index has the following properties:

Index Name The name of the index is displayed in the Index combo box. Every index in the database must have a unique name.

Primary Key If this box is checked, the index will be declared as the table's primary key. Primary keys are always unique so the setting of the Unique flag is ignored for primary keys.

Unique If this box is checked, the index will not allow duplicate keys.

Ascending If this box is checked, the index will be in ascending order. Otherwise, it will be in descending order.

5.7.2. Working with Index Fields

To add a field to an index, left click the desired field in the available fields pane and drag it to the index fields pane. Release the mouse button and the field will be added to the index. To remove a field from an index, simply reverse the process. That is, drag the field from the index fields pane to the available fields pane. To reorder the fields in an index, left click the field that you want to move in the index pane and drag it to the new position.

5.7.3. Adding a New Index

To add a new index, click the New button. You will be prompted for the name of the new index. Enter it into the edit box and click the OK button. The index's name will be displayed in the Index combo box and the index properties and index field list will be cleared, ready for you to begin designing the new index.

5.7.4. Renaming an Index

To change the currently displayed index's name, click the Rename button. You will be prompted for the new name for the index. Enter the new name into the edit box and click the OK button.

5.7.5. Deleting an Index

To delete the currently displayed index, click the Delete button. The index will be removed from the index designer.

5.7.6. Saving the Changes

To save your changes into the working copy of your project, click the Save button. This will copy any modifications that you may have made to the index definitions to the working copy of the project and close the index designer. This will not save your project. To do that you must use the File | Save menu.

5.7.7. Discarding the Changes

To discard your changes click the Cancel button. Any changes that you may have made to the index definitions will be discarded and the index designer will close.

5.8. Modifying a Field's Properties

If you are creating the target table using the source field definitions as a template it is likely that the default properties chosen for some of the target fields will not be suitable to your purposes. If you want to change the properties of a field, right click the field and select Field Properties from the target pane context menu. You can change the following properties:

Field Name You can change the field name to anything you want as long as the resulting name is unique within the table and the chosen field name conforms to SQL field naming conventions.

Data Type While it is possible to change the data type, care needs to be taken to ensure that the chosen data type is compatible with that of the source field. The data converter will perform many data type conversions automatically but the source data must be compatible with the target data type. Data destined to a numeric target must contain only numeric data, for example. Special care must be taken when declaring data and time targets since some scripting is likely to be required to make the source data compatible. You can specify any data type for the target as long as it is a recognized SQL data type.

Script You can specify a script to be used to preprocess the data before it is converted to the target data type. Either enter the name of the script file or use the button to the right of the edit box to browse for the script file.




Illustration 5.5.: Target field properties dialog






5.9. Viewing the Target Data

To preview the data contained in the target table, select View from the target pane context menu. This will open a window showing all records from the target table.




Illustration 5.6.: Target table preview dialog










6. Working with Scripts

Scripts can be used to preprocess data before it is given to the data converter for conversion to the target data type. You can specify a script on the source field, the target field or both. The data flow through the data converter is as follows:

  1. Data is read from the source file into a temporary, internal format.

  2. The source field's script is called to perform any necessary, custom conversions.

  3. The output of the source field's script is passed to the target field's script for further custom processing.

  4. The output of the target field's script is passed to the data converter for final conversion to the target data type.

When a script is called, it is passed two command line parameters; the name of the field being converted and the data. The data is converted to an ASCII string representation before being passed to the script. For example, if a PIC 9(6) field named TEST-NUMBER whose value was 000123 were being processed, the script would receive TEST-NUMBER as the first command line parameter and 123 as the second command line parameter.

The following chapters discuss the mechanics of creating, modifying and deleting scripts but they do not discuss script programming. Please see the IGOR scripting programmer's reference for details about script programming.

6.1. Creating a New Script

To create a new script, select Scripts | New from the menu. The IGOR scripting IDE will be displayed. Please refer to the IGOR scripting programmer's reference for details about script programming.

6.2. Modifying an Existing Script

To modify an existing script, select Scripts | Open from the menu. A standard Windows file selection dialog will be displayed. Select the script that you wish to modify and click the Open button. The IGOR scripting IDE will displayed. Please refer to the IGOR scripting programmer's reference for details about script programming.

6.3. Deleting a Script

To delete an existing script, select Scripts | Delete from the menu. A standard Windows file selection dialog will be displayed. Select the script that you wish to delete and click the Open button. The script file will be deleted from the hard drive. Please note that this deletes the script completely, not just from the currently open project.

6.4. IGOR Plugin Functions

The data converter provides several plugin functions for the IGOR scripting language. These provide specialized services above and beyond the basic IGOR functionality.

6.4.1. SetValue

The SetValue function is used to modify the value of the data being converted. Every script must call the SetValue function before it terminates if it wants to change the original value of the field to something different.

Calling Sequence
SetValue(new_value);

Where

new_value is the new value to be converted. This parameter can be a string, integer or float value.

Example

string fldName, origValue, newValue;

fldName = CmdArg(1);
origValue = CmdArg(2);
newValue = SubStr(origValue, 1, 9);
SetValue(newValue);

The above script modifies the original value by returning only the first 9 characters.

6.4.2. SaveVar

The SaveVar function is used to save a value from one script invocation to another. This can be useful for generating sequence numbers or combining the contents of multiple source fields into a single target field. The saved value can be retrieved using the LoadVar function.

Calling Sequence
SaveVar(name, value);

Where

name is the name of the variable to be saved.

value is the value to be associated with the variable named name. The value can be a string, integer or float.

6.4.3. LoadVar

The LoadVar function is used to recover a value saved by the SaveVar function in an earlier script invocation.

Calling Sequence
old_value = LoadVar(name);

Where

old_value is a variable which will receive the saved value. This variable must be the same type as the saved value.

name is the name of the saved variable that is to be recovered.

7. Deploying Your Project

Ok, so you have worked your fingers to the bone and you have created the perfect conversion project for your needs. It runs perfectly from the converter's workspace but now you need to deploy it. This section discusses what files you need to deploy and how to run your conversion project from the command line.

7.1. Files to Deploy

The data converter itself requires just two files be deployed. These are CobConvert.exe and IgorV11.dll. Place both of these files into the same directory.

You will also need to deploy your project file (.CCP) and any script files (.SCR) that are used by your project. The project and script files should be deployed in the same relative directory structure as was used on your development machine. For example, if your project files are in c:\MyProjects and your script files are in c:\MyProjects\Scripts you should install your scripts into a subdirectory, named Scripts, of the directory where your project files are installed.

7.2. Running a Project from the Command Line

You can run your project from the command line by supplying the appropriate parameters. The data converter command line accepts the following parameters:

CobConvert project_file -s path -d path -u userid -p password -m maxrecs -c -e -a

Where

project_file is the name of the project file to be opened. If the parameter is given without the -a option the project file will be opened and displayed without running the project.

-s path gives the path to the source file. This option is required if the -a option is given and is ignored otherwise.

-d path gives the connections string for the target database. It is of the form host:path where host is the host name of the database server and path is the path to the database file as it is known to the server. This option is required if the -a option is given and is ignored otherwise.

-u userid gives the user id required to connect to the database. This option is required if the -a option is given and is ignored otherwise.

-p password gives the password required to connect to the database. This option is required if the -a option is given and is ignored otherwise.

-m maxrecs gives the maximum number of source records to convert. If this option is omitted all source records will be converted. This option is required if the -a option is given and is ignored otherwise.

-c tells the data converter to create the target tables if they do not already exist. This option is ignored unless the -a option is also given.

-e tells the data converter to empty (delete all records from) the target tables before starting the conversion. This option is ignored unless the -a option is also given.

-a tells the data converter to automatically run the project after loading it. The data converter will terminate when the project completes.