fixSQL

SQL - ODBC INITIALIZATION PROCEDURE

Before you can write data to the Oracle database you need to install the Oracle client software for NT and Matrix software.

OBTAINING ORACLE CLIENT SOFTWARE

This installation has 2 parts, the Oracle install (hard) and the Matrix install (easy). If you have trouble with the Oracle install, contact me (Dennis Box, 3145), Nelly Stanfield 3090 , or Dick Adamo 3180 and we will help you.

INSTALLING the ORACLE CLIENT

If you do not have Oracle installed on your NT you must first do the following:

1. Download oracle_clientv8_0_3_0WIN.zip to a working directory on your NT machine, unzip it, and run the Setup.exe program . It will ask you to do a custom installation, choose ALL of the possible components and install them. I found this step is confusing, You unzip the file, run an install shield executable, which in turn runs an oracle installer executable, and you have to remember to quit oracle installer, install shield, and winzip in that order when your done.

2. Once you have installed oracle client you need to install tns_names. Down load and install oracle_tnsnamesv3WIN.zip. Unzip it and execute it like you did the oracle client.

INSTALLING MATRIX

1.Download Matrix5 for NT and run the file SetupMatrix5.exe

2.Check to make sure ghostview is configured on your NT machine. If you do not have ghostview get a copy of the gs.exe archive from KITS. Double click on gs.exe to install.

Decompress the archive and follow the directions. It will make the necessary file associations if they haven't been made already.

CONFIGURE the ODBC DRIVER

From the Control panel double click on the ODBC icon.

Image166.gif (13490 bytes)

Select the System DSN tab.

Press the ADD button. This will display the Create New Data Source window.

Image167.gif (18558 bytes)

Select the Oracle ODBC Driver then press the Finish button. This will display the Oracle8 ODBC Drive Setup window.

Image168.gif (8326 bytes)

In the DataSource Name field enter a name for this driver. This is also called the Database ID.

In the Description field enter a description for this driver.

In the DATA SOURCE box:

In the Service Name field enter the name of the relational database to use. cdf_dev1

In the UserID field enter the name of the user. segler

Press the OK button.

Image169.gif (13361 bytes)

Press the OK button.

CONFIGURE THE SQL task and SQL login.

Run the Fix Dynamics System Configuration program.

Image98.gif (23728 bytes)

Select TASKS from the CONFIGURE menu.

Image187.gif (11523 bytes)

In the Filename field: enter the name of the task to run at start up or press the "?" button and select a filename from the list. For SQL select C:\DYNAMICS\WSQLODC.EXE

In the Command Line field: enter /NK to disable key check.

In the Start Up Mode field:

Select Background.

Press the OK button.

Image98.gif (23728 bytes)

Select SQL from the CONFIGURE menu.

Image163.gif (4760 bytes)

Highlight: CDFDEV1

Press the ADD button.

Image171.gif (5133 bytes)

Fill in the fields in the SQL Login Information window.

In the Database Type field:

Enter Oracle or press the "?" button and select the database type from the list.

In the User Name field:

Enter the name of the owner of the database. (dwalsh)

In the Password field:

Enter the owners password. (dwalsh)

In the Database Identifier field:

Enter the name of the database ID previously defined or press the "?" button and select an identifier name from the list

Press the OK button.

Image172.gif (4782 bytes)

Press the Configure SQL Task button.

Image165.gif (10932 bytes)

In the SQL Support Box:

Select Enable.

In the Backup Files Box

In the Primary field: Enter the path and filename of the primary backup file.

In the Secondary field: Enter the path and filename of the secondary backup file.

In the Relational Database box:

In the Database ID field: Enter the previously defined database ID (CDFDEV1) or press the "?" button and select a database ID from the list.

Fix Dynamics defines the SQLLIB for the SQL command. Table name and SQLERR for the Error Log table name. You can rename these table names

Press the OK button.

Image172.gif (4782 bytes)

Press the OK button.

Image98.gif (23728 bytes)

In the FILE menu select SAVE then EXIT.

ADDING SQT AND SQD BLOCKS TO THE FIX DATABASE.

Run the Intellution Fix Dynamics Database Manager.

Image138.gif (52305 bytes)

From the Blocks menu

Select ADD and select SQT block from the list.

Image174.gif (15388 bytes)

Enter a Tag Name for this SQL Trigger block.

In the Description field: What this SQL trigger block is used for.

In the SQL Definition box:

SQL Name field: enter the name of the SQL command to be executed when this block is triggered. This is the name entered into the SQL table sqlname field.

Database ID field: Enter the database ID name that was used in the SQL setup.

In the Scan Settings box:

Scan Time field: Enter a scan time for this trigger block.

Phase At field: Enter 1.

In the Time Events box:

Start Time: 12:00:00

End Time: 23:59:59

Event Period: 00:01:00

Press the Advanced Tab.

Image175.gif (15931 bytes)

In the SQL Options box:

Select SQL Command.

In the Select Parameters box:

Select Single Row

Columns: The number of columns to write.

Rows: The number of rows to write.

In the Alarm Priority box:

Select the alarm priority for the SQL trigger block.

In the Startup box:

Select On Scan and Auto.

If you are debugging then select Manual. You can create a button on your view page to trigger this SQL trigger block.

Press the Save button.

From the Blocks menu select ADD and select SQD block from the list.

Image176.gif (15695 bytes)

In the Tag Name field:

Enter a tag name for this block.

In the Previous field:

Enter the name of a SQT Trigger block.

In the Data Definitions Fields:

Enter the tagname.tag field to be written to the relational database.

Press the Save button.

CREATING DATABASE TABLES IN ORACLE

From the Start menu select Programs Oracle for Windows NT / SQL Plus 8.0.

Create three tables.

CDFCMS to hold the Fix dynamics data, SQL to hold the SQL commands to execute, and SQLERR for error messages. Tables are created using the CREATE TABLE command.

CDFCMS: Name of the Oracle database table to store the data.

CREATE TABLE CDFCMS (

td DATE NOT NULL, Time stamp

tagname varchar2(8) PRIMARY KEY NOT NULL, Tag Name.

tagvalue number (p,s) NOT NULL ) Number: p is total number of digits, s is number of digits to the right of the decimal point

SQLLIB: Contains the SQL commands that FIX will execute. SQLLIB is the Fix Dynamics default table name.

CREATE TABLE SQLLIB (

Sqlname varchar2(8) PRIMARY KEY, Name of this command

Sqlcmd varchar2(1000) NOT NULL) SQL command to execute

To insert commands into the SQLLIB table:

INSERT into SQL (sqlname,sqlcmd) values (‘fixdump’ , (‘INSERT into CDFCMS values (?,?,?);’))

When the FIX SQT block triggers it will look for the command ‘fixdump’ in the SQLLIB table. Then it will execute the command stored in the ‘sqlcmd’ field.

SQLERR: Fix Dynamics default name for the error table. The field names must be as shown.

CREATE TABLE SQLERR (

td DATE NOT NULL,

node varchar2(8) NOT NULL,

tag varchar2(8) NOT NULL,

sqlname varchar2(8) PRIMARY KEY,

fix_err varchar2(100),

sql_err varchar2(250),

prog_err varchar2(100) )

Stored Procedures

Stored procedures are compiled blocks of code in the relational database. They are useful since they can have conditional statements and flow statements. Stored procedures can perform INSERT, UPDATE, DELETE, and SELECT commands. Stored procedures can take arguments and returns results. The arguments may be values to insert or values to use in WHERE clauses.

Stored procedures can be much faster than SQL commands for the following reasons:

Executing a stored procedure requires only one call.
To execute an SQL command two calls are made to the relational database: one to retrieve the command and another to execute it. Using a stored procedure the reference is made by name and the name is passed to the database including any parameters.
A stored procedure is already compiled in the database.
When using an SQL command that is not stored both calls are queries, so the database must compile them at run time.

Example: Create a stored procedure from within SQL Plus 8.0.

Create or replace procedure fixdump (arg1 in varchar2, arg2 in number, …)

is begin

INSERT into CDFCMS (sysdate, arg1, arg2); end;

Files with SQL commands can be created and when read into SQL Plus 8.0 will create the tables and procedures you wish to create. See the examples at the end of this document.

Now create an SQT block and a SQD block in the Fix database editor:

Image177.gif (15427 bytes)

Enter the name of the procedure in the SQL Name Filed, using no more than eight characters. When this SQT block is triggered it will execute the stored procedure dump22.

In the Time Events box enter values for the Start Time, End Time and Event Period fields.

Select the Advanced tab.

Image178.gif (15989 bytes)

In the SQL Options box:

Select Procedure

In the Startup box:

Select On Scan and Auto.

Press the Save button.

Image179.gif (15855 bytes)

In the Tag Name field:

Enter a name for this SQD database block.

In the Description field:

Enter a description for this database block.

In the Previous field:

Select the SQT database block that will write this data.

In the Next field:

Chain this SQD database block to other SQD blocks.

Press the Save button.

SQL command files.

The following is the contents of file: makecdf100.sql When run within SQL Plus 8.0 it will create a table named CDF100 with 101 columns. The first column contains the time stamp followed by the tag name and tag value pair in columns 2 and 3 through column 100, 101.

create table CDF100 (td date,

c1 varchar2(8), c2 number(8,3), c3 varchar2(8), c4 number(8,3), c5 varchar2(8), c6 number(8,3), c7 varchar2(8), c8 number(8,3), c9 varchar2(8),c10 number(8,3), c11 varchar2(8),c12 number(8,3),c13 varchar2(8),c14 number(8,3),c15varchar2(8), c16 number(8,3),c17 varchar2(8),c18 number(8,3),c19 varchar2(8),c20 number(8,3), c21 varchar2(8),c22 number(8,3),c23 varchar2(8),c24 number(8,3),c25 varchar2(8), c26 number(8,3),c27 varchar2(8),c28 number(8,3),c29 varchar2(8),c30 number(8,3), c31 varchar2(8),c32 number(8,3),c33 varchar2(8),c34 number(8,3),c35 varchar2(8), c36 number(8,3),c37 varchar2(8),c38 number(8,3),c39 varchar2(8),c40 number(8,3), c41 varchar2(8),c42 number(8,3),c43 varchar2(8),c44 number(8,3),c45 varchar2(8), c46 number(8,3),c47 varchar2(8),c48 number(8,3),c49 varchar2(8),c50 number(8,3), c51 varchar2(8),c52 number(8,3),c53 varchar2(8),c54 number(8,3),c55 varchar2(8), c56 number(8,3),c57 varchar2(8),c58 number(8,3),c59 varchar2(8),c60 number(8,3), c61 varchar2(8),c62 number(8,3),c63 varchar2(8),c64 number(8,3),c65 varchar2(8), c66 number(8,3),c67 varchar2(8),c68 number(8,3),c69 varchar2(8),c70 number(8,3), c71 varchar2(8),c72 number(8,3),c73 varchar2(8),c74 number(8,3),c75 varchar2(8), c76 number(8,3),c77 varchar2(8),c78 number(8,3),c79 varchar2(8),c80 number(8,3), c81 varchar2(8),c82 number(8,3),c83 varchar2(8),c84 number(8,3),c85 varchar2(8), c86 number(8,3),c87 varchar2(8),c88 number(8,3),c89 varchar2(8),c90 number(8,3), c91 varchar2(8),c92 number(8,3),c93 varchar2(8),c94 number(8,3),c95 varchar2(8), c96 number(8,3),c97 varchar2(8),c98 number(8,3), varchar2(8),c100 number(8,3) )

The following is the contents of file: fixdump100.sql. When run within SQL Plus 8.0 it will create or replace procedure dump100. When the FIX Dynamics SQT block calls this procedure it will write 101 pieces of data to the table named in the INSERT command. The arguments to the procedure are declared in five SQD blocks that are chained together. The head of the chain is the SQT SQL trigger block.

create or replace procedure dump100 ( c1 in varchar2, c2 in number, c3 in varchar2, c4 in number, c5 in varchar2, c6 in number, c7 in varchar2, c8 in number, c9 in varchar2, c10 in number, c11 in varchar2, c12 in number, c13 in varchar2, c14 in number, c15 in varchar2, c16 in number, c17 in varchar2, c18 in number, c19 in varchar2, c20 in number, c21 in varchar2, c22 in number, c23 in varchar2, c24 in number, c25 in varchar2, c26 in number, c27 in varchar2, c28 in number, c29 in varchar2, c30 in number, c31 in varchar2, c32 in number, c33 in varchar2, c34 in number, c35 in varchar2, c36 in number, c37 in varchar2, c38 in number, c39 in varchar2, c40 in number, c41 in varchar2, c42 in number, c43 in varchar2, c44 in number, c45 in varchar2, c46 in number, c47 in varchar2, c48 in number, c49 in varchar2, c50 in number, c51 in varchar2, c52 in number, c53 in varchar2, c54 in number, c55 in varchar2, c56 in number, c57 in varchar2, c58 in number, c59 in varchar2, c60 in number, c61 in varchar2, c62 in number, c63 in varchar2, c64 in number, c65 in varchar2, c66 in number, c67 in varchar2, c68 in number, c69 in varchar2, c70 in number, c71 in varchar2, c72 in number, c73 in varchar2, c74 in number, c75 in varchar2, c76 in number, c77 in varchar2, c78 in number, c79 in varchar2, c80 in number, c81 in varchar2, c82 in number, c83 in varchar2, c84 in number, c85 in varchar2, c86 in number, c87 in varchar2, c88 in number, c89 in varchar2, c90 in number, c91 in varchar2, c92 in number, c93 in varchar2, c94 in number, c95 in varchar2, c96 in number, c97 in varchar2, c98 in number, c99 in varchar2, c100 in number)

is begin

INSERT into CDF100 values (sysdate, c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20, c21,c22,c23,c24,c25,c26,c27,c28,c29,c30,c31,c32,c33,c34,c35,c36,c37,c38,c39,c40, c41,c42,c43,c44,c45,c46,c47,c48,c49,c50,c51,c52,c53,c54,c55,c56,c57,c58,c59,c60, c61,c62,c63,c64,c65,c66,c67,c68,c69,c70,c71,c72,c73,c74,c75,c76,c77,c78,c79,c80, c81,c82,c83,c84,c85,c86,c87,c88,c89,c90,c91,c92,c93,c94,c95,c96,c97,c98,c99,c100); end;

Now create the fix Database SQT and SQD blocks in the Fix database to match the format of the table and procedure.