JDBC Driver with USER, GROUP, and USER -> GROUP Child

Hello!

IDM 4.7

It's been a while since I've tackled a JDBC driver to do synchronization with a child table, but it's not working :(

The situation is that we have 3 tables at the client. USER, GROUP, and USER_TO_GROUP. I have read in the documentation that I have to use VIEWS for this, because the client has the USER_TO_GROUP table defined with 3 columns: 1 PK (UserGroupID), 1 column for UserID, and 1 column for GroupID.

I created 2 views: VW_USER and VW_USER_GROUP. Definitions are below.

CREATE OR REPLACE
VIEW `VW_USER` AS select
`USER`.`UserID` AS `pk_UserID`,
`USER`.`UserName` AS `UserName`,
`USER`.`FirstName` AS `FirstName`,
`USER`.`LastName` AS `LastName`,
`USER`.`Email` AS `Email`,
`USER`.`DefaultTimeZone` AS `DefaultTimeZone`,
`USER`.`LastLogin` AS `LastLogin`
from
`USER`

CREATE OR REPLACE
VIEW `VW_USER_TO_GROUP` AS select
`User_to_Group`.`UserGroupID` AS `pk_UserGroup`,
`User_to_Group`.`UserID` AS `fk__UserID__UserID`,
`User_to_Group`.`GroupID` AS `GroupID`
from
`User_to_Group`

First thing to say is that I read the documentation, and it is very confusing around what to name of View columns so the Driver knows what to do with the references.

Anyway, the schema mapping is below...but when I synchronize (just testing) Telephone Number (mapping to VW_USER_TO_GROUP.GroupID), the Driver gives me a warning; and nothing happens.

@ SENDING @
<nds dtdversion="4.0" ndsversion="8.x">
<source>
<product edition="Advanced" version="4.5.3.0">DirXML</product>
<contact>NetIQ Corporation</contact>
</source>
<input>
<modify cached-time="20190611164333.656Z" class-name="VW_USER" event-id="bouvet-idm#20190611164333#2#1:45b56854-8235-41d6-5299-5468b5453582" qualified-src-dn="O=belkast\OU=Users\OU=Bouvet\CN=014_TEST_User" src-dn="\IDVAULT\belkast\Users\Bouvet\014_TEST_User" src-entry-id="188943" timestamp="1560271413#2">
<association state="associated">pk_UserID=4,table=VW_USER</association>
<modify-attr attr-name="VW_USER_TO_GROUP.GroupID">
<remove-value>
<value timestamp="1560270662#2" type="teleNumber">2</value>
</remove-value>
<add-value>
<value timestamp="1560271413#2" type="teleNumber">1</value>
</add-value>
</modify-attr>
</modify>
</input>
</nds>

@ RECEIVED @
17:43:33 7897C700 Drvrs: index_WorkStation ST:
<nds dtdversion="2.0" ndsversion="8.x" xmlns:jdbc="urn:dirxml:jdbc">
<source>
<product build="20141001_0706" instance="index_WorkStation" version="4.0.0.2">DirXML Driver for JDBC</product>
<contact>NetIQ Corporation</contact>
</source>
<output>
<status event-id="bouvet-idm#20190611164333#2#1:45b56854-8235-41d6-5299-5468b5453582" level="warning">Column 'GroupID' is undefined on table/view 'VW_USER' or is unsyncable.</status>
<status event-id="bouvet-idm#20190611164333#2#1:45b56854-8235-41d6-5299-5468b5453582" level="success"/>
</output>
</nds>

I seem to recall that the Driver should correctly interpret the fact that VW_USER_TO_GROUP.GroupID is mapping to a child table (in this case a view), and therefore insert in to the child View. This is not, however, happening.

Perhaps my View definitions are all mucked up!

Thanks in advance for any pointers to get me on my way.

-K
  • I was able to solve this in my VM, but now I have another issue.  The SQLServer DBAs have created tables with the same name as reserved words. So, they have USER and GROUP.

    How can I get the JDBC Driver to always put square brackets around the table names?  So, my select will be select userid from [user] where username = ? order by userid?  Do I need to do this in the descriptor file?

    Thanks, KA

    @ MATCH @

    10:11:32 1F663700 Drvrs: index_WorkStation ST: select userid from user where username = ? order by userid
    10:11:32 1F663700 Drvrs: index_WorkStation ST: IN @ index 1, field 'UserName', length: 6, value = 'keiarm'
    10:11:32 1F663700 Drvrs: index_WorkStation ST: ROLLBACK


    Incorrect syntax near the keyword 'User'.

    @ INSERT @

    10:11:32 1F663700 Drvrs: index_WorkStation ST: INSERT INTO User(UserName, FirstName, LastName, Email, DefaultTimeZone) VALUES (?, ?, ?, ?, ?)
    10:11:32 1F663700 Drvrs: index_WorkStation ST: IN @ index 1, field 'UserName', length: 6, value = 'keiarm'
    10:11:32 1F663700 Drvrs: index_WorkStation ST: IN @ index 2, field 'FirstName', length: 5, value = 'Keith'
    10:11:32 1F663700 Drvrs: index_WorkStation ST: IN @ index 3, field 'LastName', length: 9, value = 'Armstrong'
    10:11:32 1F663700 Drvrs: index_WorkStation ST: IN @ index 4, field 'Email', length: 26, value = 'Keith.Armstrong@nasdaq.com'
    10:11:32 1F663700 Drvrs: index_WorkStation ST: IN @ index 5, field 'DefaultTimeZone', length: 16, value = 'Europe/Stockholm'
    10:11:32 1F663700 Drvrs: index_WorkStation ST: ROLLBACK


    Incorrect syntax near the keyword 'User'.

    @ ENTITLEMENT / ROLE QUERY @

    <nds dtdversion="2.0">
    <input>
    <query class-name="dbo.Group" event-id="0" scope="subtree">
    <search-class class-name="dbo.Group"/>
    <read-attr attr-name="GroupID"/>
    <read-attr attr-name="Name"/>
    <read-attr attr-name="Description"/>
    <operation-data final-operation="query"/>
    </query>
    </input>
    </nds>

    10:15:03 1F663700 Drvrs: index_WorkStation ST:select groupid from group order by groupid
    10:15:04 1F663700 Drvrs: index_WorkStation ST:ROLLBACK


    Incorrect syntax near the keyword 'Group'.