Navigation: Integration via database >

Manipulating records in the DIINTERFACE table through scripts

On-Premise customers with access to database administration can execute DML commands (Data Manipulation Language) directly in the DIINTERFACE table.

The primary key of DIINTERFACE is the OIDINTERFACE field, which has 32 positions. When importing data through the CM015 menu, this field is automatically filled out, without the need for user intervention.

However, if customers opt to manipulate data directly in the DIINTERFACE table, as the database is in its own infrastructure, we will display below some examples on how to add, edit, and delete records from the DIINTERFACE table for databases supported by SoftExpert Suite.

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, it will be necessary to enable an S3 Bucket (Amazon service) for the customer domain. If you are a SoftExpert Hosting customer and wish to use this resource, we suggest contacting the SoftExpert sales team to request information on this service.

See below the script templates to create OID in supported databases:

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 script templates to create OIDs, see below examples of scripts to add, edit, and delete data from 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');