SQL query order by for JDBC driver

[Anonymous_User]

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

[lhaeger]

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