FACTS can use the inbox/outbox connection point methodology to connect to the Infor OS interface. Stored procedures are used to maintain the inbox/outbox tables of a database created on SQL or PostgreSQL servers. This topic is a guide for maintaining the stored procedures within FACTS and addresses behind the scenes operations that take place to keep the systems inline.
To establish a connection to a SQL server or PostgreSQL database, create a corresponding database definition in Database Definition (DOE400). Complete the connection string with valid parameters. Click Test to update the stored procedures on the SQL or PostgreSQL server for the indicated connection string. If the stored procedures do not exist within the SQL or PostgreSQL database, they are created when you click Test. If the stored procedure version number within the SQL or PostgreSQL database does not match the version number on the stored procedure in FACTS, clicking Test updates the stored procedures in the SQL or PostgreSQL database with the ones maintained in FACTS.
All sequel statements from FACTS to SQL or to PostgreSQL are performed using the CSSQL.pvc object to execute the stored procedures or manipulate variable values. To make the CSSQL.pvc object available to any running procedure, this object is added as a property to the %CSYS object. The property name is %CSYS’sql_object. By default, the property is instantiated to the class to access the routines and variables is via syntax.
Refer to these examples:
To set the desired database: %csys’sql_object’database_id$=<the desired database ID>
To use the stored procedure to clean the database inbox for PostgreSQL: ret_val=%csys'sql_object'execute_sp("pgs_facts_clean_inbox").
All sequel statements to manage and maintain the SQL or PostgreSQL databases first implement this Connect routine: ret_val=%csys'sql_object'connect()>.
The Connect request uses the selected Database Definition’s connection string to obtain a valid connection to the SQL or PostgreSQL database. Then, the version number within FACTS for the stored procedures is validated for the given database. If the version number is out of sync, the stored procedures in the database are deleted and the ones from FACTS are uploaded and saved for use.