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.

  • Thanks - I tried this. If I use the above SQL,it's the same error on the client. If I paste that SQL into the iSeries SQL it does not retrieve the ID either. If you paste the default SELECT MAX( MCUKEY ) FROM DEVTONY.MCWBTK in the iSeries SQL it does retrieve the ID just not from the client. Almost like its a commitment / timing issue but the commitment at all levels is set to NONE.

  • I tend to agree with your thinking.

    So, the official answer is that this isn't a provider that we support.  However, it may be worth working with our support department to at least enable logging for the external object type data source.  Perhaps this will give you the underlying error that iSeries is throwing, and maybe help guide in the correct direction?  Have you raised a support query for this at all?

  • Will do thanks.  The other theory is the data type or size that the SELECT statement in M-Files is expecting. i.e.  Max Char size, or Integer  or Binary etc. I can set the ID field to any size. I've tried experimenting as the default SELECT IDENTITY_VAL_LOCAL()   is    31,0    ....  Any suggestions  or is this on the wrong tack ? 

  • That's interesting.  The external ID is a string.  I know that the standard implementation deals with GUIDs and the like, but I haven't seen how it does that.  What's the data type of the MAX(MCUKey) query?

    I'm not convinced that this is the issue, but it's a definite possibility that I hadn't considered.

  • I've tried integer and decimal of various sizes. If I try CHAR the GENERATED is invalid. Not sure how we can auto generate an ID when it is expecting a STRING ?

Reply Children