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" jdbcosition="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\dirxmlscript 4.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">&lt;input
xmlns:jdbc="urn:dirxml:jdbc">&lt;jdbc:statement
jdbc:type="query">&lt;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">&lt;/jdbc:sql>&lt;/jdbc:statement>&lt;/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">&lt;input
xmlns:jdbc="urn:dirxml:jdbc">&lt;jdbc:statement
jdbc:type="query">&lt;jdbc:sql></token-text>
<token-text xml:space="preserve">SELECT * FROM
dbo.SystemUserRoles;</token-text>
<token-text
xml:space="preserve">&lt;/jdbc:sql>&lt;/jdbc:statement>&lt;/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