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.
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');