WMS Directory Services - Database Query errors

, , ,
1

Hi All,

I am trying to setup a database directory service.
myWebMethods >> Directory Services >> Create a new directory service >> Database >> Enter all the data it needs

While configuring all the quries, I kept getting errors saying the queries are not valid. The document does not explain very well what should be in those query lookups. My tables are very simple, USERS, GROUPS, USERS_GROUPS_MAP, that contain all the fields it needs.

Finally I got it all work and was able to save the directory service setup.
Then I am getting errors on User Management >> User page.
[POP.001.0002] A “java.lang.NullPointerException” occurred with the Message “at com.webMethods.portal.service.dir.DirectoryTools.getUID(DirectoryTools.java:95)”

This is one example of my query
Query Search Users
SELECT * FROM USERS WHERE USER_ID LIKE ‘%’ || ‘{query}’ || ‘%’
All the queries seem to be valid. What did I do wrong?

Any one has experience of setting up database directory service?
Please share some tips.

2

I faced the similar situation while setting up database directory service. I was able to solve this problem by prefixing ‘cn=’ to Attribute “dn” value. All the queries also need to be changed accordingly.

HTH.
Ovais

3

I have also faced this issue and found below solution.

All the queries should have uid and dc attribute for users and dn respectively. eg. Queries should look like below. This is valid for search query as well.

select user_id,
‘uid=’ || user_id,
‘dc=’ || user_id dn,
LAST_NAME_EN lastName,
FIRST_NAME_EN firstName,
(FIRST_NAME_EN || ’ ’ || LAST_NAME_EN) fullName,
EMAIL_ID email,
IS_ACTIVE,
group_id guid,
group_id gdn,
group_id gname,
email_id gemail
from users
where lower(user_id) = lower(‘{uid}’)