Big news! The community will be moving to a new platform April 21. Read more.
Big news! The community will be moving to a new platform April 21. Read more.
Admiral
Admiral
869 views

How to get value from SQL Query with jdbc drv


Hello,

I would like to update a object from SQL Query with the jdbc driver:

According the documentation, I make the following query:

<nds dtdversion="4.0" ndsversion="8.x">
<input xmlns:jdbc="urn:dirxml:jdbc">
<jdbc:statement event-id="0" jdbc:type="query">
<jdbc:sql>SELECT name FROM dbo.role WHERE roleid =
'C36CFFDF-F7A0-E211-9F51-005056A019E8';</jdbc:sql>
</jdbc:statement>
</input>
</nds>

Then I get the answer :

<nds dtdversion="2.0" ndsversion="8.x" xmlns:jdbc="urn:dirxml:jdbc">
<source>
<product build="20141001_0706" instance="MSSQL-Dynamics-CRM"
version="4.0.0.2">DirXML Driver for JDBC</product>
<contact>NetIQ Corporation</contact>
</source>
<output>
<jdbc:result-set event-id="0" jdbc:number-of-rows="1">
<jdbc:row jdbc:number="1">
<jdbc:column jdbc:name="name" jdbc:position="1"
jdbc:type="java.sql.Types.VARCHAR">
<jdbc:value>TEST - Standard User</jdbc:value>
</jdbc:column>
</jdbc:row>
</jdbc:result-set>
<status event-id="0" level="success"/>
</output>
</nds>

But I don't know how to get the value to set a edir attribut.

Here is the test rule:

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE policy PUBLIC
"policy-builder-dtd"
"C:\NetIQ\idm\apps\Designer\plugins\com.novell.idm.policybuilder_4.0.0.201410091552\DTD\dirxmlscript4.5.dtd"><policy>
<rule disabled="true">
<description>Test SQL Query</description>
<conditions>
<and>
<if-class-name mode="nocase" op="equal">User</if-class-name>
<if-op-attr mode="nocase" name="Description"
op="changing-to">sql</if-op-attr>
</and>
</conditions>
<actions>
<do-append-xml-element expression="/nds/input"
name="jdbc:statement"/>
<do-set-xml-attr expression="/nds/input/jdbc:statement"
name="jdbc:type">
<arg-string>
<token-text xml:space="preserve">update</token-text>
</arg-string>
</do-set-xml-attr>
<do-set-xml-attr expression="/nds/input/jdbc:statement"
name="jdbc:transaction-type">
<arg-string>
<token-text xml:space="preserve">manual</token-text>
</arg-string>
</do-set-xml-attr>
<do-set-xml-attr expression="/nds/input/jdbc:statement"
name="jdbc:transaction-id">
<arg-string>
<token-text xml:space="preserve">2</token-text>
</arg-string>
</do-set-xml-attr>
<do-append-xml-element expression="/nds/input/jdbc:statement"
name="jdbc:sql"/>
<do-append-xml-element expression="/nds/input/jdbc:statement"
name="jdbc:sql"/>
<do-append-xml-text
expression="/nds/input/jdbc:statement/jdbc:sql[1]">
<arg-string>
<token-text xml:space="preserve">SELECT * FROM
DBO.ROLE</token-text>
</arg-string>
</do-append-xml-text>
<do-append-xml-text disabled="true"
expression="/nds/input/jdbc:statement/jdbc:sql[2]">
<arg-string>
<token-text xml:space="preserve">UPDATE SCHEMA.TABLENAME2 SET
COLUMN1 = '</token-text>
<token-attr name="SomeSourceAttribute"/>
<token-text xml:space="preserve">' WHERE PK_SEQUENCE =
'</token-text>
<token-attr name="SomeSourceAttribute2"/>
<token-text xml:space="preserve">'</token-text>
</arg-string>
</do-append-xml-text>
<do-clear-src-attr-value name="Description"/>
</actions>
</rule>
<rule>
<description>generate SQL Select Statement - get RoleID - Name
relation</description>
<conditions>
<and>
<if-class-name op="equal">User</if-class-name>
<if-op-attr mode="nocase" name="Description"
op="changing-to">sql</if-op-attr>
</and>
</conditions>
<actions>
<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></token-text>
<token-text xml:space="preserve">SELECT name FROM dbo.role WHERE
roleid = 'C36CFFDF-F7A0-E211-9F51-005056A019E8';</token-text>
<token-text
xml:space="preserve"></jdbc:sql></jdbc:statement></input></token-text>
</token-xml-parse>
</arg-node-set>
</do-set-local-variable>
<do-trace-message color="yellow" level="1">
<arg-string>
<token-xpath expression="cmd:execute($destCommandProcessor,
$sqlstatement)"/>
</arg-string>
</do-trace-message>
<do-clear-src-attr-value name="Description"/>
<do-veto/>
</actions>
</rule>
<rule>
<description>generate SQL Select Statement - get SysrtemUserID - Name
relation</description>
<conditions>
<and>
<if-class-name op="equal">User</if-class-name>
<if-op-attr mode="nocase" name="Description"
op="changing-to">sql1</if-op-attr>
</and>
</conditions>
<actions>
<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></token-text>
<token-text xml:space="preserve">SELECT * FROM
dbo.SystemUserRoles;</token-text>
<token-text
xml:space="preserve"></jdbc:sql></jdbc:statement></input></token-text>
</token-xml-parse>
</arg-node-set>
</do-set-local-variable>
<do-trace-message color="yellow" level="1">
<arg-string>
<token-xpath expression="cmd:execute($destCommandProcessor,
$sqlstatement)"/>
</arg-string>
</do-trace-message>
<do-clear-src-attr-value name="Description"/>
<do-veto/>
</actions>
</rule>
</policy>

Thanks a lot for your help.

Regards

Sylvain


--
sma
------------------------------------------------------------------------
sma's Profile: https://forums.netiq.com/member.php?userid=174
View this thread: https://forums.netiq.com/showthread.php?t=53673

Labels (1)
0 Likes
2 Replies
Knowledge Partner Knowledge Partner
Knowledge Partner

sma wrote:

>
>> <do-trace-message color="yellow" level="1">

> <arg-string>
> <token-xpath expression="cmd:execute($destCommandProcessor,
> $sqlstatement)"/>
> </arg-string>
> </do-trace-message>
>


Instead of this line, use the following to store the entire result into a node set local variable.
<do-set-local-variable name="sqlResultNS" scope="policy">
<arg-node-set>
<token-xpath expression="cmd:execute($destCommandProcessor, $sqlstatement)"/>
</arg-node-set>
</do-set-local-variable>

Then you can use XPath to extract out any value you might want.

<do-set-local-variable name="sqlResult" scope="policy">
<arg-node-set>
<token-xpath expression="$sqlResultNS//jdbc:value[1]"/>
</arg-node-set>
</do-set-local-variable>


if you know you will always get one result value, you can combine the two.

<do-trace-message>
<arg-string>
<token-xpath expression="cmd:execute($destCommandProcessor, $sqlstatement)//jdbc:value[1]"/>
</arg-string>
</do-trace-message>
Alex McHugh - Knowledge Partner - Stavanger, Norway
Who are the Knowledge Partners
If you appreciate my comments, please click the Like button.
If I have resolved your issue, please click the Accept as Solution button.
0 Likes
Knowledge Partner Knowledge Partner
Knowledge Partner

On 6/12/2015 4:07 AM, Alex McHugh wrote:
> sma wrote:
>
>>
>>> <do-trace-message color="yellow" level="1">

>> <arg-string>
>> <token-xpath expression="cmd:execute($destCommandProcessor,
>> $sqlstatement)"/>
>> </arg-string>
>> </do-trace-message>
>>

>
> Instead of this line, use the following to store the entire result into a node set local variable.
> <do-set-local-variable name="sqlResultNS" scope="policy">
> <arg-node-set>
> <token-xpath expression="cmd:execute($destCommandProcessor, $sqlstatement)"/>
> </arg-node-set>
> </do-set-local-variable>
>
> Then you can use XPath to extract out any value you might want.
>
> <do-set-local-variable name="sqlResult" scope="policy">
> <arg-node-set>
> <token-xpath expression="$sqlResultNS//jdbc:value[1]"/>
> </arg-node-set>
> </do-set-local-variable>
>
>
> if you know you will always get one result value, you can combine the two.
>
> <do-trace-message>
> <arg-string>
> <token-xpath expression="cmd:execute($destCommandProcessor, $sqlstatement)//jdbc:value[1]"/>
> </arg-string>
> </do-trace-message>



and for some more examples of how to do this, check out these articles:
http://www.novell.com/communities/node/2786/calling-stored-procedures-idm-jdbc-driver
http://www.novell.com/communities/node/2569/using-jdbc-driver-and-direct-sql




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.