So does Documentum support unknown columns in DB table or not?

ilya basin
ilya basin Member
in Documentum #1
Documentum does not use the list of columns when it formats the INSERT SQL for a created object. Instead, it uses the alternate syntax with the values listed in the order in which the table columns are defined. For instance, if the table is defined like this:
CREATE TABLE mytype_s ( r_object_id, i_partition, myattr)
then the insert will look like this:
INSERT INTO mytype_s VALUES ('0b02254c814a181f',   0,'****')

After some experiments we found that if the DB table has columns that are not attributes (e.g. result of manual DDL manipulation), then Documentum adds the default value for this column (usually NULL).

CREATE TABLE mytype_s ( r_object_id, i_partition, old_column, myattr)<br>
_
INSERT INTO mytype_s VALUES ('0b02254c814a181f',&nbsp;&nbsp; 0, NULL, '****')

_

It's not clear at what moment Documentum reads the table schema from DB. Unlikely at server start only because it usually works fine after ALTER TABLE + ALTER TYPE.

But now we faced an issue that some object creations fail with Postgres error "INSERT has more expressions than target columns". After the last attribute change we restarted the server many times and the worst is that the error is not reproducible: the next object creation succeeds.


In our DB table we have one unknown column, but the INSERT SQL has two NULLs, one of them is positioned at wrong place!

Comments

  • Hicham Bahi
    Hicham Bahi E Member
    #2
    As far as I know, Documentum Server simply passes the INSERT statement to the underlying database and passes the result back.

    To avoid such issues, simply specify the columns you want to set, e.g. INSERT INTO my_type_s (col1, col2, col3) VALUES ('val1', 'val2', 'val3')
  • Tejraj
    Tejraj Member
    #3
    If you are creating an object, you should be using CREATE OBJECT DQL and not INSERT query.
  • ilya basin
    ilya basin Member
    #4
    bacham3, Tejraj
    The problem is with INSERT statements that are generated by Documentum when we run a CREATE OBJECT DQL or API call



  • Tejraj
    Tejraj Member
    #5
    Ideally it should not happen, are you getting error while executing CREATE OBJECT DQL or attributes are not populated correctly?
    I would suggest to publish the data dictionary and test again if OBJECT TYPE is updated.
    Can you please post the CREATE OBJECT DQL and object dump?
  • Hicham Bahi
    Hicham Bahi E Member
    #6
    You are right @Tejraj ! I misunderstood the post: I though @Basin was working with registered tables.
  • Tejraj
    Tejraj Member
    #7
    @bacham3 , in the original post query is mentioned like:
    CREATE TABLE mytype_s ( r_object_id, i_partition, myattr)

    this gives impression of registered table. So it is actually confusing if they are working with registered table or custom type.



  • ilya basin
    ilya basin Member
    edited November 16, 2020 #8
    I can't dump the object because it's not created. However, now I see that the error only happens on the second Content Server Node:

    Success:
    $idql efxpbase -Uefxpm -Pignored

            OpenText Documentum idql - Interactive document query interface
            Copyright (c) 2017. OpenText Corporation
            All rights reserved.
            Client Library Release 7.3.0190.0080


    Connecting to Server using docbase efxpbase
    [DM_SESSION_I_SESSION_START]info:  "Session 0102254c80675542 started for user efxpm."


    Connected to OpenText Documentum Server running Release 7.3.0210.0150  Linux64.Postgres
    1> create efx_component_digital object link '/Temp', set object_name = 'testpostgres', set dcmi_title = 'testpostgres', set dcmi_identifier_isbn = 'Not assigned'
    2> go
    object_created
    ----------------
    0b02254c814ed594
    (1 row affected)
    1> delete efx_component objects where object_name = 'testpostgres'
    go
    2> objects_deleted
    ---------------
                  1
    (1 row affected)

    Failure:
    $idql efxpbase.efxpbase2 -Uefxpm -Pignored


            OpenText Documentum idql - Interactive document query interface
            Copyright (c) 2017. OpenText Corporation
            All rights reserved.
            Client Library Release 7.3.0190.0080


    Connecting to Server using docbase efxpbase.efxpbase2
    [DM_SESSION_I_SESSION_START]info:  "Session 0102254c80675178 started for user efxpm."


    Connected to OpenText Documentum Server running Release 7.3.0210.0150  Linux64.Postgres
    1> create efx_component_digital object link '/Temp', set object_name = 'testpostgres', set dcmi_title = 'testpostgres', set dcmi_identifier_isbn = 'Not assigned'
    2> go
    [DM_OBJ_MGR_E_SAVE_FAIL]error:  "save failed for object with handle 0b02254c814ed440 of type efx_component_digital: table on which save failed was efx_component_s; error from database system was  STATE=42601, CODE=7, MSG=ERROR: INSERT has more expressions than target columns;
    Error while executing the query
    "

    1> create efx_component_digital object link '/Temp', set object_name = 'testpostgres', set dcmi_title = 'testpostgres', set dcmi_identifier_isbn = 'Not assigned'
    go
    2> [DM_OBJ_MGR_E_SAVE_FAIL]error:  "save failed for object with handle 0b02254c814ed441 of type efx_component_digital: table on which save failed was efx_component_s; error from database system was  STATE=42601, CODE=7, MSG=ERROR: INSERT has more expressions than target columns;
    Error while executing the query
    "





  • Tejraj
    Tejraj Member
    #9
    Can you clear the type cache on content server (node giving error), restart it and test again?
  • ilya basin
    ilya basin Member
    #10
    @Tejraj Do you mean
    API>flush,c,ddcache,object_type i.e. Where object Type is your custom type
    ?

  • Tejraj
    Tejraj Member
    #11
    Yes.
  • ilya basin
    ilya basin Member
    #12
    Thanks @Tejraj . That helped.

Categories