Manipulating data in the interface through scripts

On-Premise customers that have access to the administration of their database can run DML (Data Manipulation Language) commands directly in the DIINTERFACE table.

The primary key of the DIINTERFACE table is the OIDINTERFACE field, which has 32 positions. When data is imported through the CM015 menu, this field is automatically populated and does not require user intervention.

However, if a customer chooses to manipulate the data directly in the DIINTERFACE table, since the database is in their own infrastructure, it is important to consider some guidelines. To help in this process, we present below some examples of how to insert, edit and delete records in the DIINTERFACE table for databases supported by SoftExpert Suite.

Remember that incorrect data manipulation can lead to inconsistencies or errors in the system. Therefore, we recommend that customers that choose to manipulate data directly in the DIINTERFACE table have experience with DML commands and take appropriate precautions to ensure data integrity.

info

Customers hosted in SoftExpert Hosting do not have direct access to the database. Therefore, if it is necessary to import electronic files using DIINTERFACE resources, an S3 bucket (Amazon service) must be enabled for the customer's domain. If you are a SoftExpert Hosting customer and want to use this resource, we suggest contacting the SoftExpert sales team to request information on this service.

Here are the script templates for creating OID in the databases supported by SoftExpert Suite:

SQL Server:

replace(NEWID(),'-','')

PostgreSQL:

replace(cast (uuid_in(md5(random()::text || clock_timestamp()::text)::cstring) as text),'-','')

Oracle Database:

DBMS_RANDOM.STRING('x',32) or SYS_GUID()

Considering the script templates for creating OID, see below examples of scripts for inserting, editing, and deleting data in the DIINTERFACE table:

SQL Server:

INSERT INTO DIINTERFACE (OIDINTERFACE, CDISOSYSTEM, FGIMPORT, FGOPTION, NMFIELD01, NMFIELD02, NMFIELD03, NMFIELD04, NMFIELD07) VALUES (replace(NEWID(),'-',''), 104, 1, 1, 'Process ID #', 'Instance title', 'Starter ID #', 'Requester ID #', '1');

PostgreSQL:

INSERT INTO DIINTERFACE (OIDINTERFACE, CDISOSYSTEM, FGIMPORT, FGOPTION, NMFIELD01, NMFIELD02, NMFIELD03, NMFIELD04, NMFIELD07) VALUES (replace(cast (uuid_in(md5(random()::text || clock_timestamp()::text)::cstring) as text),'-',''), 104, 1, 1, 'Process ID #', 'Instance title', 'Starter ID #', 'Requester ID #', '1');

Oracle Database:

INSERT INTO DIINTERFACE (OIDINTERFACE, CDISOSYSTEM, FGIMPORT, FGOPTION, NMFIELD01, NMFIELD02, NMFIELD03, NMFIELD04, NMFIELD07) VALUES (DBMS_RANDOM.STRING('x',32), 104, 1, 1, 'Process ID #', 'Instance title', 'Starter ID #', 'Requester ID #', '1');