SnapShot Connector EmbeddedSQL

Necessary namespaces:

    <namespace-def name="cmd" value="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.XdsCommandProcessor"/>
    <namespace-def name="query" value="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.XdsQueryProcessor"/>
    <namespace-def name="ssc" value="urn:dirxml:ssc"/>

Get a value from DB using EmbeddedSQL:

<rule>
 <description>Get current values from DB</description>
 <conditions>
  <and/>
 </conditions>
 <actions>
  <do-set-local-variable name="lv-query" scope="policy">
   <arg-node-set>
    <token-xml-parse>
     <token-text xml:space="preserve">&lt;nds dtdversion="2.0" ndsversion="8.x">
&lt;input xmlns:ssc="urn:dirxml:ssc">
&lt;ssc:sql ssc:class-cfg="Person">
&lt;ssc:statement ssc:query="true">select EMAIL from IST.EMAIL where PERSONID = '$lv.workforceid$'&lt;/ssc:statement>
&lt;/ssc:sql>
&lt;/input>
&lt;/nds></token-text>
    </token-xml-parse>
   </arg-node-set>
  </do-set-local-variable>
  <do-set-local-variable name="executeSQLquery" scope="policy">
   <arg-node-set>
    <token-xpath expression="query:query($destQueryProcessor,$lv-query)"/>
   </arg-node-set>
  </do-set-local-variable>
 </actions>
</rule>

Call an Oracle function using EmbeddedSQL:

<rule>
 <description>Prepare embedded SQL to run the update command</description>
 <conditions>
  <and/>
 </conditions>
 <actions>
  <do-set-local-variable name="lv.sqlquery.update" scope="policy">
   <arg-node-set>
    <token-xml-parse>
     <token-text xml:space="preserve">&lt;nds dtdversion="2.0" ndsversion="8.x">
    &lt;input xmlns:ssc="urn:dirxml:ssc">
        &lt;ssc:sql ssc:class-cfg="Person">
            &lt;ssc:statement event-id="0">
            DECLARE returnValue VARCHAR2(100); BEGIN returnValue := fnILMWrite('$lv.workforceid$', NULL,'$lv.email$',NULL,'$lv.date$'); END;
            &lt;/ssc:statement>
        &lt;/ssc:sql>
    &lt;/input>
&lt;/nds></token-text>
    </token-xml-parse>
   </arg-node-set>
  </do-set-local-variable>
 </actions>
</rule>
<rule>
 <description>Execute SQL UPDATE query</description>
 <conditions>
  <and/>
 </conditions>
 <actions>
  <do-set-local-variable name="executeSQLupdate" scope="policy">
   <arg-node-set>
    <token-xpath expression="cmd:execute($destCommandProcessor,$lv.sqlquery.update)"/>
   </arg-node-set>
  </do-set-local-variable>
  <do-veto/>
 </actions>
</rule>

Call function in Sybase using EmbeddedSQL while binding attributes:

Make sure the values are Base 64 encoded.

<rule>
	<description>Set variables</description>
	<conditions>
		<and/>
	</conditions>
	<actions>
		<do-set-local-variable name="lv.associations" scope="policy">
			<arg-node-set>
				<token-src-attr name="DirXML-Associations">
					<arg-dn>
						<token-src-attr name="cidPersonDN"/>
					</arg-dn>
				</token-src-attr>
			</arg-node-set>
		</do-set-local-variable>
		<do-set-local-variable name="lv.association" scope="policy">
			<arg-string>
				<token-base64-encode>
					<token-xpath expression="$lv.associations[component[@name='volume'] = $dirxml.auto.driverdn]/component[@name='path']"/>
				</token-base64-encode>
			</arg-string>
		</do-set-local-variable>
		<do-set-local-variable name="lv.userid" scope="policy">
			<arg-string>
				<token-base64-encode>
					<token-substring length="20">
						<token-src-attr name="cidUserID"/>
					</token-substring>
				</token-base64-encode>
			</arg-string>
		</do-set-local-variable>
		<do-set-local-variable name="lv.email" scope="policy">
			<arg-string>
				<token-base64-encode>
					<token-substring length="100">
						<token-src-attr name="cidEMailAddress"/>
					</token-substring>
				</token-base64-encode>
			</arg-string>
		</do-set-local-variable>
		<do-set-local-variable name="lv.password" scope="policy">
			<arg-string>
				<token-base64-encode>
					<token-text xml:space="preserve">-OSPEC-</token-text>
				</token-base64-encode>
			</arg-string>
		</do-set-local-variable>
		<do-set-local-variable name="lv.date" scope="policy">
			<arg-string>
				<token-base64-encode>
					<token-text xml:space="preserve">Comment </token-text>
					<token-time format="yyyyMMdd HH:mm:ss"/>
				</token-base64-encode>
			</arg-string>
		</do-set-local-variable>
	</actions>
</rule>

Bind the values

<do-set-local-variable name="lv.sqlquery.update" scope="policy">
	<arg-node-set>
		<token-xml-parse>
			<token-text xml:space="preserve">&lt;nds dtdversion="2.0" ndsversion="8.x">
    &lt;input xmlns:ssc="urn:dirxml:ssc">
        &lt;ssc:sql ssc:class-cfg="Person">
            &lt;ssc:statement>EXEC fnILMWrite  %(lv.association),%(lv.userid),%(lv.email),%(lv.password),%(lv.date)&lt;/ssc:statement>
	    &lt;ssc:bind attr-name="lv.association" ssc:syntax="binary">$lv.association$&lt;/ssc:bind>
	    &lt;ssc:bind attr-name="lv.userid" ssc:syntax="binary">$lv.userid$&lt;/ssc:bind>
	    &lt;ssc:bind attr-name="lv.email" ssc:syntax="binary">$lv.email$&lt;/ssc:bind>
	    &lt;ssc:bind attr-name="lv.password" ssc:syntax="binary">$lv.password$&lt;/ssc:bind>
	    &lt;ssc:bind attr-name="lv.date" ssc:syntax="binary">$lv.date$&lt;/ssc:bind>
        &lt;/ssc:sql>
    &lt;/input>
&lt;/nds></token-text>
		</token-xml-parse>
	</arg-node-set>
</do-set-local-variable>

Execute

<do-set-local-variable name="executeSQLupdate" scope="policy">
	<arg-node-set>
		<token-xpath expression="cmd:execute($destCommandProcessor,$lv.sqlquery.update)"/>
	</arg-node-set>
</do-set-local-variable>