Anonymous_User Absent Member.
Absent Member.
212 views

SQL query order by for JDBC driver


Hello,

We have a JDBC driver connected to our Peoplesoft Database, user has
multiple records in the table, currently we just have the schema mapping
rule that mapping each column from the table into eDirectory attribute.
I guess the JDBC driver just doing the select query and get whatever the
first result back and processing it.

We would like to sort the records based on certain columns before the
driver processing the records, so the first result got fetched in by
JDBC driver is always desired record. Is there a way to do something in
SQL such as select * from table where id=? order by column

Thanks

Yi


--
yiqian
------------------------------------------------------------------------
yiqian's Profile: https://forums.netiq.com/member.php?userid=9901
View this thread: https://forums.netiq.com/showthread.php?t=54012

Labels (1)
0 Likes
1 Reply
Knowledge Partner
Knowledge Partner

Re: SQL query order by for JDBC driver

yiqian wrote:

> We would like to sort the records based on certain columns before the
> driver processing the records, so the first result got fetched in by
> JDBC driver is always desired record. Is there a way to do something in
> SQL such as select * from table where id=? order by column


You will most likely have to map your queries into embedded SQL statements in
an output transform and map the search results coming back to instance docs in
an onput transform to do this. Here's an example to get you started:

<rule>
<description>Translate queries to SQL</description>
<conditions>
<and>
<if-xpath op="true">self::query[@class-name and
not(@scope="entry")]</if-xpath>
</and>
</conditions>
<actions>
<do-if>
<arg-conditions/>
<arg-actions>
<do-set-local-variable name="fieldnames" scope="policy">
<arg-string>
<token-replace-first regex="^$" replace-with="*">
<token-join delimiter=",">
<token-xpath expression="./read-attr/@attr-name"/>
</token-join>
</token-replace-first>
</arg-string>
</do-set-local-variable>
<do-for-each>
<arg-node-set>
<token-xpath expression="search-attr"/>
</arg-node-set>
<arg-actions>
<do-set-local-variable name="filter" scope="policy">
<arg-node-set>
<token-local-variable name="filter"/>
<token-replace-first regex=" = '\*'" replace-with=" IS NOT NULL">
<token-xpath expression="$current-node/@attr-name"/>
<token-text xml:space="preserve"> = '</token-text>
<token-xpath expression="$current-node/value/text()"/>
<token-text xml:space="preserve">'</token-text>
</token-replace-first>
</arg-node-set>
</do-set-local-variable>
</arg-actions>
</do-for-each>
<do-set-local-variable name="sqlstatement" scope="policy">
<arg-node-set>
<token-xml-parse>
<token-text xml:space="preserve"><input
xmlns:jdbc="urn:dirxml:jdbc"><jdbc:statement
jdbc:type="query"><jdbc:sql>SELECT $fieldnames$ FROM </token-text>
<token-class-name/>
<token-replace-first regex="(.+)" replace-with=" WHERE $1">
<token-join delimiter=" AND ">
<token-local-variable name="filter"/>
</token-join>
</token-replace-first>
<token-text
xml:space="preserve">;</jdbc:sql></jdbc:statement></input></token-text>
</token-xml-parse>
</arg-node-set>
</do-set-local-variable>
<do-set-xml-attr expression="/nds/input" name="xmlns:jdbc">
<arg-string>
<token-text xml:space="preserve">urn:dirxml.jdbc</token-text>
</arg-string>
</do-set-xml-attr>
<do-clone-xpath dest-expression=".."
src-expression="$sqlstatement/input/jdbc:statement"/>
<do-veto/>
</arg-actions>
<arg-actions/>
</do-if>
</actions>
</rule>

<rule>
<description>Translate jdbc:resultset into XDS instances</description>
<conditions>
<and>
<if-operation mode="case" op="equal">jdbc:result-set</if-operation>
</and>
</conditions>
<actions>
<do-for-each>
<arg-node-set>
<token-xpath expression="jdbc:row"/>
</arg-node-set>
<arg-actions>
<do-append-xml-element expression=".." name="instance"/>
<do-set-xml-attr expression="../instance[last()]" name="class-name">
<arg-string>User</string>
</do-set-xml-attr>
<do-for-each>
<arg-node-set>
<token-xpath expression="$current-node/jdbc:column[jdbc:value/text()]"/>
</arg-node-set>
<arg-actions>
<do-append-xml-element expression="../instance[last()]" name="attr"/>
<do-set-xml-attr expression="../instance[last()]/attr[last()]"
name="attr-name">
<arg-string>
<token-xpath expression="$current-node/@jdbc:name"/>
</arg-string>
</do-set-xml-attr>
<do-append-xml-element expression="../instance[last()]/attr[last()]"
name="value"/>
<do-if>
<arg-conditions>
<and>
<if-xpath
op="true">$current-node/@jdbc:type="java.sql.Types.TIMESTAMP"</if-xpath>
</and>
</arg-conditions>
<arg-actions>
<do-set-xml-attr
expression="../instance[last()]/attr[last()]/value[last()]" name="type">
<arg-string>
<token-text xml:space="preserve">time</token-text>
</arg-string>
</do-set-xml-attr>
<do-append-xml-text expression="../instance[last()]/attr[last()]/value">
<arg-string>
<token-convert-time dest-format="!CTIME" src-format="yyyy-MM-dd
HH:mm:ss.SSS">
<token-xpath expression="$current-node/jdbc:value/text()"/>
</token-convert-time>
</arg-string>
</do-append-xml-text>
</arg-actions>
<arg-actions>
<do-set-xml-attr
expression="../instance[last()]/attr[last()]/value[last()]" name="type">
<arg-string>
<token-text xml:space="preserve">string</token-text>
</arg-string>
</do-set-xml-attr>
<do-append-xml-text expression="../instance[last()]/attr[last()]/value">
<arg-string>
<token-xpath expression="$current-node/jdbc:value/text()"/>
</arg-string>
</do-append-xml-text>
</arg-actions>
</do-if>
</arg-actions>
</do-for-each>
</arg-actions>
</do-for-each>
<do-veto/>
</actions>
</rule>

Please note that this is not working code, you need to add scoping to only
intercept the queries you need and probably add an operation property to set
the class name, if you're using more than one in your driver.

Good luck,

Lothar
______________________________________________
https://www.is4it.de/identity-access-management
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.