Hi,

I have a requirement to update two tables with the JDBC driverl. I need
to update one table first, from which an automatically generated field
value(the primary key of this table, the foreign key in another table),
needs to be fetched to be updated in another table. The update must be
completed without opening the publisher channel.

What I tried for the same was, I used SQL statements from my Output
Transformation policy for inserting a record. Then I tried retrieving
the same value. However, when I try to retrieve the value, I get a null.


Here's the policy Iam using, As shown, after an insert I retrievethe
auto generated field value, and insert that value.



*<do-append-xml-text
expression="/nds/input/jdbc:statement/jdbc:sql[1]">
<arg-string>
<token-text xml:space="preserve">INSERT INTO
dbo.tblSecurity_User</token-text>
<token-text xml:space="preserve">(UserLoginID)VALUES
('</token-text>
<token-attr name="Given Name"/>
<token-text xml:space="preserve">')</token-text>
</arg-string>
</do-append-xml-text>
<do-append-xml-text
expression="/nds/input/jdbc:statement/jdbc:sql[2]">
<arg-string>
<token-text xml:space="preserve">SET UserIDNum = (SELECT UserID
FROM dbo.tblSecurity_User WHERE UserLoginID='</token-text>
<token-attr name="Given Name"/>
<token-text xml:space="preserve">')</token-text>
</arg-string>
</do-append-xml-text>
<do-append-xml-text disabled="true"
expression="/nds/input/jdbc:statement/jdbc:sql[3]">
<arg-string>
<token-text xml:space="preserve">INSERT INTO
dbo.tblSecurity_RoleUser</token-text>
<token-text xml:space="preserve">(RoleID,UserID) VALUES
('</token-text>
<token-attr name="Given Name"/>
<token-text xml:space="preserve">',</token-text>
<token-text xml:space="preserve">(SELECT UserID FROM
dbo.tblSecurity_User WHERE UserLoginID='</token-text>
<token-attr name="UserIDNum"/>
<token-text
xml:space="preserve">')</token-text>
</arg-string>
</do-append-xml-text>*

Please HELP!!


--
varunece
------------------------------------------------------------------------
varunece's Profile: http://forums.novell.com/member.php?userid=42999
View this thread: http://forums.novell.com/showthread.php?t=359542