Trouble with Database Attribute Mapping

[Ryan Richter]

I am attempting to map attributes based on database queries using org.apereo.services.persondir.support.jdbc.SingleRowJdbcPersonAttributeDao.

I have used the example located here: https://github.com/uPortal-Project/uPortal/blob/e9309a2ddbd5a8f4f7e47be718a35bd8ebb240ed/docs/implement/user-attributes/README.md

This successfully creates an attribute for my user called "layoutNodesCount" with an integer value when I login.

I have a custom table in the uPortal database with three columns (emplid, student_group, timestamp).

I have the following bean defined in personDirectoryContext.xml:

  <bean id="csucStudentGroupAttributesDao" class="org.apereo.services.persondir.support.jdbc.SingleRowJdbcPersonAttributeDao">
    <constructor-arg index="0" ref="PersonDB"/>
    <constructor-arg>
      <value>
        select * CUSTOM_TABLE where {0} and STUDENT_GROUP = 'BLAH'
      </value>
    </constructor-arg>
    <property name="usernameAttributeProvider" ref="usernameAttributeProvider"/>
    <property name="queryAttributeMapping">
      <map>
        <entry key="emplid" value="EMPLID"/>
      </map>
    </property>
    <property name="resultAttributeMapping">
      <map>
        <entry key="STUDENT_GROUP">
          <set>
            <value>
              student_group
            </value>
          </set>            
        </entry>
      </map>
    </property>
  </bean>

My emplid is in the table and it is an attribute successfully mapped by the LDAP Attribute mapper. The row with my student group is set to BLAH.

I have an attribute successfully created called "student_group" but it is blank instead of being set to BLAH (the value of the student_group column).

Anyone have any idea why this might be happening? I have even gone so far as to hard code the query with my emplid = 'my_emplid_here' instead of using {0} and I get the same result.

Thanks,

-Ryan

[jmt]

The one thing that I think the documentation is missing is that you need to merge the attribute sources via a CachingPersonAttributeDaoImpl bean. See https://apereo.atlassian.net/wiki/spaces/UPM43/pages/103948916/JDBC+User+Attribute+Sources

Although the documentation is for uP 4.3, the bean you need is there. Just put the bean in overlays/uPortal/src/main/resources/properties/contextOverrides/overridesContext.xml (or another XML file under overlays/uPortal/src/main/resources/properties/contextOverrides if you prefer)

Hope this helps!

[Benito Gonzalez]

Hi Ryan,

Take a look at https://github.com/uPortal-Project/uPortal/tree/master/docs/implement/user-attributes

You seem to have a valid definition. Have you confirmed that your account has an 'empIid' attribute in uPortal already defined and ready to use as a queryAttributeMapping key? You can check with the User Administration portlet. 

Best,

-bjagg

--
You received this message because you are subscribed to the Google Groups "uPortal Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to uportal-user...@apereo.org.
To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/uportal-user/90aaf99e-34f6-4ce6-8424-d6841a547db8n%40apereo.org.

[Ryan Richter]

Thanks jmt and bjagg. I've tried the suggestion regarding merging attributes and it didn't make a difference. emplid is an attribute successfully defined (I can see it in attribute swapper, is that enough to verify it's usable for this purpose?)

I think my root problem is at a lower level, I found the following error in catalina.out (this was an attempt to have a query not even be dependent on data in the table, however the original query and several other queries have met with the same error):

org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReport.reportFailedQuery Failed Query Report SQL=

                select 'blahs' as layoutNodesCount

                from my_custom_table

            ; time=1615608778770 ms;

  The result was a blank layoutNodesCount attribute.  

To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/uportal-user/CAJ_1GkQm2V5Oxwf8uoPAEyxd%2BpMK4fEGwHp8%3DMC_WOveRy1srA%40mail.gmail.com.