iSeries ODBC SQL Insert Error

We have successfully used the Client Access ODBC driver to integrate iSeries Physical Files and an external file type for UPDATE and DELETE. However the INSERT function throws when executing the SELECT to retrieve the objectID of the inserted record. The record is added to the physical file and can be seen from the iseries. The objectID has been auto incremented as the physical file has been created via a SQL command. If you copy and paste the SQL commands from the server configuration into the iseries they work perfectly. Has anybody seen this before or have any suggestions how to correct or remove the error?

Parents
  • I have found a solution:

    Create the PF on the iSeries using SQL with a generated Identity

    Journal the PF

    Create a System DSN on MFiles Server using ODBC Client Access - Ensure Commitment *NONE

    Create MFiles Type with Connection to External DB, using Microsoft OLE DB Provider

    Select the Client Access ODBC System DSN 

    Ensure that in Advanced Tab Read/Write permissions is checked

    All SQL statements work

    Beware of NULLS. The iSeries recognises these as NULL not blank.

Reply
  • I have found a solution:

    Create the PF on the iSeries using SQL with a generated Identity

    Journal the PF

    Create a System DSN on MFiles Server using ODBC Client Access - Ensure Commitment *NONE

    Create MFiles Type with Connection to External DB, using Microsoft OLE DB Provider

    Select the Client Access ODBC System DSN 

    Ensure that in Advanced Tab Read/Write permissions is checked

    All SQL statements work

    Beware of NULLS. The iSeries recognises these as NULL not blank.

Children