For the ION interface for FACTs, the method of maintaining the tables on SQL Server is to use stored procedures versus ad hoc SQL statements. This allows for better control of inputs, validation and maintenance on various installations. This document will serve as a guide for maintaining the stored procedures within FACTs and also discuss the behind the scenes operations that take place to keep the systems inline.
All operations from FACTs to SQL are performed using the new CSSQL.pvc object. This object is added as a property to the %CSYS object in order to make it available to any running procedure. The property name is %CSYS’sql_object and by default the property is just instantiated to the class. A brief overview of the CSSQL object and the calling conventions will be given in this document.
In order to establish a connection to a SQL Server database, a database definition can be created in DOE400 and the object will then use this definition. In order to set the object to the database definition, the following call is used:
%csys’sql_object’database_id$=<database ID>
There are other methods for connecting to a database, any of which must be supported by the PxPlus [ODB] connection. For the purposes of this document, we will only focus on using the system defined database ID.
Connection to the database is achieved by the invoking the Connect() method of the CSSQL object. The object will return 0 for failure or 1 for success. If a failure status is returned, the Last_Err will be 15 (OS Error) and the property Last_Err_Message$ can be referenced for debugging / logging purposes.
Ret_Val = %csys’sql_object’connect()
If not(Ret_Val) then {
… perform any logging / debugging here …
}
From the Database Definition, highlight the record in the line browser and right click. Select the “Export Stored Proc” option. The Managed Stored Procedures screen is displayed and allows you to copy the window contents to the clipboard for editing in another program.
When finished editing the stored procedures, the updated contents can be imported. Using the Database Definition program, highlight the appropriate record, right-click and select “Import Stored Proc”. The Managed Stored Procedures screen is displayed so you can paste the contents of the stored procedures as a single XML formatted list.
Prior to importing, you will be prompted to confirm the replacement of the stored procedures on file for the selected database ID.
The stored procedures should be saved in the database in the following XML structure.
<StoredProcedures>
<ION_Configuration>
<IONDatabase> </IONDatabase>
<CurrentVersion> </CurrentVersion>
</ION_Configuration>
<StoredProcedure>
<Name> </Name>
<Body>
</Body>
</StoredProcedure>
</StoredProcedures>
The <StoredProcedure> block can be repeated as often as needed to populate the required stored procedures. The ION Database and Current Version are required to ensure the XML format is valid for importing.
This is a listing of the substitution values and the intended usage.