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 haven't seen this specifically, although I haven't used that driver.  Can you give us samples of the types of insert and "select latest ID" queries that you're using?

  • Hi Craig,

    Thanks for coming back.  

    The PF is

    CREATE TABLE DEVTONY/MCWBTK   

    (MCUKEY INTEGER NOT NULL      

    GENERATED ALWAYS AS IDENTITY 

    (START WITH 1                 

    ,INCREMENT BY 1               

    ,NO MAXVALUE                  

    ,NO CYCLE                     

    ,ORDER)                       

    ,MCTKNO CHAR(30)              

    ,MCWGHT CHAR(30)              

    ,MCMATD CHAR(1)               

    ,PRIMARY KEY (MCUKEY))        

    The MFiles INSET

    INSERT INTO DEVTONY.MCWBTK ( MCTKNO, MCWGHT, MCMATD ) VALUES( ?, ?, ? )

    The MFiles SELECT after INSERT

    SELECT MAX( MCUKEY ) FROM DEVTONY.MCWBTK

    The Error

    CoScriptObjectFactory.cpp, 465, Adding to the external database failed. (0x800400D2)
    ScriptErrorHelper.cpp, 96, Adding to the external database failed. (0x800400D2)
    MDispatchExImpl.h, 686, Adding to the external database failed. (0x800400D2)
    MDispatchExImpl.h, 833, Adding to the external database failed. (0x800400D2)
    MetadataCardAction.cpp, 386, Adding to the external database failed. (0x800400D2)
    MetadataCardAction.cpp, 570, Adding to the external database failed. (0x800400D2)
    MetadataEditor.cpp, 2816, Adding to the external database failed. (0x800400D2)
    MetadataModel.cpp, 4230, Adding to the external database failed. (0x800400D2)
    MetadataModel.cpp, 5154, Adding to the external database failed. (0x800400D2)
    CoVaultMountingDocumentOperations.cpp, 244, Adding to the external database failed. (0x800400D2)
    CoVaultMountingDocumentOperations.cpp, 364, Adding to the external database failed. (0x800400D2)
    DocumentCache.cpp, 4389, Adding to the external database failed. (0x800400D2)
    DocumentCache.cpp, 4458, Adding to the external database failed. (0x800400D2)
    DocumentCache.cpp, 4632, Adding to the external database failed. (0x800400D2)
    RPCMethodCallWithRetry.h, 29, Adding to the external database failed. (0x800400D2)
    RPCMethodCallWithRetry.h, 29, Adding to the external database failed. (0x800400D2)
    RPCDocumentOperations.cpp, 11215, Adding to the external database failed. (0x800400D2)
    RPCDocumentOperations.cpp, 5599, Adding to the external database failed. (0x800400D2)
    MCallInLoop.h, 652, Adding to the external database failed. (0x800400D2)
    RPCDocumentOperationsHelper.cpp, 206, Adding to the external database failed. (0x800400D2)
    RPCDocumentOperationsHelper.cpp, 413, Adding to the external database failed. (0x800400D2)
    RPCDocumentOperationsHelperPrivate.cpp, 625, Adding to the external database failed. (0x800400D2)
    RPCDocumentOperationsHelper.cpp, 10044, Adding to the external database failed. (0x800400D2)
    RPCDocumentOperationsHelper.cpp, 10167, Adding to the external database failed. (0x800400D2)
    RefreshExternalObjects.cpp, 922, Adding to the external database failed. (0x800400D2)
    RefreshExternalObjects.cpp, 922, Retrieving the ID of the new record in the external database failed. (0x80040101)
    RefreshExternalObjects_Private.cpp, 1623, Retrieving the ID of the new record in the external database failed. (0x80040101)
    ExtDBObjectTypeDS.cpp, 925, Retrieving the ID of the new record in the external database failed. (0x80040101)
    ExtDBObjectTypeDS.cpp, 925, No M-Files error description available. (0x80040E18)
    ExtDBObjectTypeDS.cpp, 1333, No M-Files error description available. (0x80040E18)
    ExtDBObjectTypeDS.cpp, 1189, No M-Files error description available. (0x80040E18)
    (M-Files 20.4.8986.4)

  • This StackOverflow answer seems to indicate that you should use "IDENTITY_VAL_LOCAL":

    sql - iSeries DB2 - Is there any way to select the identity value from an insert statement? - Stack Overflow

    If so then your SELECT query should be something like:

    SELECT IDENTITY_VAL_LOCAL() FROM DEVTONY.MCWBTK

    Have you tried anything like that?

    Regards,

    Craig.

Reply Children