karmst2 Absent Member.
Absent Member.
95 views

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
Labels (1)
0 Likes
1 Reply
karmst Contributor.
Contributor.

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

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'.

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.