Symphony Framework
Symphony ISAM Manager
Symphony Utilities > Symphony ISAM Manager

The Symphony ISAM Manager allows you to query and modify data within an ISAM file using standard SQL-style syntax. By using Symphony data objects to map data stored in ISAM files, the Symphony ISAM Manager can display individual field elements in a grid format. Symphony data objects are code-generated classes that are built into a standard class library. Additionally, a code-generated table mapping routine is required to enable the table name being processed to be mapped to the physical ISAM filename.

 Installing Symphony ISAM Manager

The installation for the Symphony ISAM Manager utility can be found at here.  Download and run the Symphony ISAM Manager Installer file. This will create a program shortcut folder on your start menu called Symphony Utilities, and under this folder you will find the Symphony ISAM Manager shortcut.

Preparing to Use Symphony ISAM Manager you must first build a library of Symphony data objects that expose the underlying data structures and file information. These data objects are code-generated from your Repository structures. The minimum requirements for the Repository for code generation to work are;

To build the required data object class library;

  1. Create a new project in Visual Studio using the project template “Synergy/DE Class Library.” 
  2. Reference the required repository files.
  3. Add a NuGet reference to the Symphony Framework. 
  4. Create a script to perform the required code generation, as shown in the following example:
    Script
    setlocal
    set ROOT=%~dp0
    set CODEGEN_TPLDIR=%SYMPHONYTPL%
    rem set the required repository environment variables
    set RPSMFIL=%ROOT%Rps\rpsmain.ism
    set RPSTFIL=%ROOT%Rps\rpstext.ism
    set OPTS=-e -r
    codegen %OPTS% -s <sName> -t Symphony_Data_v3_2 -o %ROOT% -n <namespace>
    codegen %OPTS% -s <sName> -t Symphony_DataFieldMapper -o %ROOT% -n <namespace>
    codegen %OPTS% -s * -ms -t Symphony_TableMapper_v3_2 -o %ROOT% -n <namespace>
    endlocal 
    Replace <sName> with a list of space separated repository structure names. Replace <namespace> with the name of the Class Library project created in Visual Studio.
  5. Run the script to generate the required data objects and the table mapping routine. In Visual Studio, add all of the generated source files to the project and build.

 

 Running Symphony ISAM Manager

Double click the Symphony ISAM Manager desktop icon. The first requirement is to define the logic the program will use to connect to your ISAM data

Symphony ISAM Manager

Symphony ISAM Manager

If you have not implemented Symphony Harmony Authentication then both the user name and password can be any value. 

To define the data object assembly, you can type the full path specification or click the  button. Once you have located a valid library, the Table mapping method field becomes available. You can select the required mapping method by clicking the  button. This will display a list of available methods:

Symphony ISAM Manager - Finding table mapping routine

Symphony ISAM Manager - Finding table mapping routine

To select a method, highlight it and click the  button. To cancel the selection, click the  button.

On the connection form, once all the fields contain valid data, the  button will be available. Click it to accept the entries and take you to the main screen. You can click the  button at any time to cancel the form and close the program.

Click the  button to close the program.

 Main Symphony ISAM Manager Screen

This is the main Symphony ISAM Manager screen where you can execute standard SQL-style commands to interrogate and manage your ISAM data;

Symphony ISAM Manager - Main screen

Symphony ISAM Manager - Main screen

The toolbar contains the following options:

Below the toolbar is the data object selection list, where you can select the required response data object. When a selection is made, the available fields within the data object are displayed in the Field list. 

Although the command syntax is not case sensitive, the Where clause values are. You can force case insensitivity by checking the “Case sensitive search?” checkbox.

Commands entered into the “Command” windows will be parsed and executed when you click the  button or press F5. An invalid command will result in a message box similar to this:

Symphony ISAM Manager - Error dialog

Symphony ISAM Manager - Error dialog

Once a valid command has been accepted and processed the results window will contain the corresponding results:

Symphony ISAM Manager - Selection results

Symphony ISAM Manager - Selection results

The Field list window displays the available list of fields from the selected data object. Selecting or deselecting a field’s checkbox will include or exclude it from the results list. The command in the Command window will be updated to reflect the selected fields:

 Selecting and Displaying data from your ISAM file

The Select command allows you to select and display data from your ISAM file. You can limit the fields that are displayed by defining a list of fields to select. The results can be filtered with a Where clause, and the order of the data can be changed with the Order By clause. The Select command must start with the keyword SELECT. The case of the command keywords is not important, but the case of any Where clause text values is important. You can force case insensitivity by checking the “Case sensitive search?” checkbox.

You can manually enter the names of the fields, separated by a comma, or you can select the fields from the field list. As you enter field names into the command window, the field list will update to reflect the names entered. If you select fields using the field list, the command will change to reflect the fields selected. Typing an asterisk (*) denotes all fields, and each field in the field list will be checked.

 

 Select Syntax

The basic syntax of the Select command is:

Syntax
SELECT * | <field_name>[,<field_name] FROM table_name

FROM table_name allows you to define the table name containing the data to retrieve, where table_name is the name of the file as defined in the repository. It is your responsibility to ensure that the selected data object is valid for the requested table.

For example, the following command selects all the fields for all the records from the supplier file:

Example
SELECT * FROM supplier

The results would look similar to this:

Symphony ISAM Manager - Selecting form the supplier file

Symphony ISAM Manager - Selecting form the supplier file

You can scroll the results grid and expand the column widths to show/hide data.

The command:

Example
SELECT Supplier_id,Name FROM supplier

Will limit the results to display just the supplier ID and the name for all records in the supplier file.

 Top Syntax

The number of records returned from the file can be limited by using the TOP keyword. The syntax for the TOP keyword is:

Syntax
SELECT TOP <row_count>  * | <field_name> [,<field_name>] FROM table_name

For example, this command limits the number of returned rows to 10:

Example
SELECT top 10 * FROM part
 Where Syntax

The Select command accepts a Where clause that allows the selection of data to be returned. The Where clause has this format:

Syntax
WHERE <field_name> CONDITIONAL <value> [OPERATOR <field_name> CONDITIONAL <value>]

Specify a field name, which must be a valid field name for the selected data object, a conditional, and the value to test for. Valid conditionals are:

=

The field’s value is equal to the value defined

<> or !=

the field’s value is not equal to the value defined

>

the field’s value is greater than the value defined

>=

the field’s value is greater than or equal to the value defined

<

the field’s value is less than the value defined

<=

the field’s value is less than or equal to the value defined

BETWEEN

allows the range checking of numeric values to be between two defined values

LIKE

returns records when the value is contained within the field

Where clauses can be connected together using the AND or OR operators.

 Examples of selections using a Where clause

Note that setting the “Case sensitive search” checkbox determines if a case-sensitive or case-insensitive search is performed. Alpha values must be enclosed within single quotes (for example, ‘test value’).

Examples
SELECT * FROM supplier WHERE City = ‘Manchester’
SELECT * FROM part WHERE quantity > 10 AND quantity < 40
SELECT * FROM part WHERE quantity BETWEEN 20 and 40
SELECT * FROM group WHERE description like ‘device’

 

 Order By Syntax
                       

 

The results can be ordered based on a field in the data object using the ORDER BY keyword. The sequence can be defined with the ASC (ascending) or DESC (descending) operators. The syntax is:

Syntax
SELECT * | <field_name> [,<field_name>] FROM table_name ORDER BY field_name ASC|DESC

The example below shows how to order the results table in descending order of the cost_price field:

Example
SELECT top 10 * FROM part ORDER BY cost_price DESC
 Inserting new records into your ISAM File

The Insert command allows new records to be created in the file based on values passed within the command. The syntax for the Insert command has two formats: 

 Insert Syntax

The basic syntax of the Insert command is:

Syntax
INSERT INTO table_name VALUES (<value> [,<value>])
INSERT INTO table_name (<field_name> [,<field_name>]) VALUES (<value> [,<value>])

The first syntax requires that you provide all the field data values the data object expects, in the order in which the data object defines them, which is the order they are listed in the field list.  Failure to provide all field values will result in an exception being reported.

The second syntax enables you to set individual named fields. The order of the data values corresponds to the order of the data fields.

Alphanumeric values must be defined within single quotes.

The table name is the name of the table in which to insert the data. It is your responsibility to ensure that the selected data object is valid for the requested table.

The following command inserts a record into the group table and sets the two field values based on the fields defined in the data object:

Example
INSERT INTO group VALUES (‘g1’, ‘Group description 1’)

The command below inserts limited data into the part table:

Example
INSERT INTO part (id, description, cost_price) VALUES (‘id1’, ‘limited data’, 12.34)

Failing to provide enough data for the successful creation of a record (null key values, etc.) will result in an exception being displayed.

Once the command has been successfully executed, the status bar will display the number of records inserted:

 Updating existing records in your ISAM File

The Update command enables one or more fields within one or more records to be updated based on values defined within the command. The syntax for the Update command is: 

 Update Syntax

The basic syntax of the Update command is:

Syntax
UPDATE table_name
SET field_name=value [,field_name=value]
WHERE <field_name> CONDITIONAL <value> [OPERATOR <field_name> CONDITIONAL <value>]
The table name is the name of the table in which to update the data. It is your responsibility to ensure that the selected data object is valid for the requested table.

The SET keyword defines the fields to be set and their associated values. Alphanumeric values must be defined within single quotes.

The Where clause limits the number of records updated in the file. For a full explanation of the Where clause, refer to the Where Syntax section above. Executing an Update command without a Where clause will result in all records in the file being modified.

Once the command has been successfully executed, the status bar will display the number of records updated:

 Deleting records from your ISAM File

The Delete command provides the ability to delete records from the ISAM file. The basic syntax of the Delete command is: 

 Delete Syntax

The basic syntax of the Delete command is:

Syntax
DELETE FROM table_name 
WHERE <field_name> CONDITIONAL <value> [OPERATOR <field_name> CONDITIONAL <value>]

The Where clause limits the number of records deleted from the file. For a full explanation of the Where clause, refer to the Where Syntax section above. This operation is irreversible.  Executing a Delete command without a Where clause will result in all records in the file being deleted.

In the example below, the record with an associated part id of ‘BB01’ will be deleted from the file:

Example
DELETE FROM part WHERE id=’BB01’

Once the command has been executed successfully, the status bar will display the number of records deleted: