SQL Transactions in XI - limited to 1 insert and 1 update?

[Former Member]

I would like to implement the following SQL logic in XI:

Start Transaction (Serializable)

- Select Statement (Header Data)

- Select Statement (Line Item Data)

- Update Statement (Update Processed Flag)

End Transaction

The JDBC Sender Adapter allows SQL transactions but only allows 1 SQL Query statement and 1 SQL Update statement.

I could use BPM to make multiple SQL calls (multiple JDBC Sender Comm Channels) and then map the results together, but then each call would be in a separate transaction.

Is there a way to accomplish this without writing my own JDBC code in a Java Map?

Thanks,

Jesse

[Former Member]

hi,

>>Is there a way to accomplish this without writing my >>own JDBC code in a Java Map?

u could also use Java proxies instead of java mappings. Much more straight forward.

cheers,

naveen

[Former Member]

Hi Naveen,

I would like to know if this is possible without having to write custom JDBC code, whether it's a map or a proxy.

This is a pretty easy thing to do in other integration tools like webMethods.

Any ideas?

Thanks,

Jesse

[Former Member]

Hi Jesse,

You also call a stored procedure that opens a transaction and performs the tasks.

>>This is a pretty easy thing to do in other integration >>tools like webMethods.

IN XI to the best of my knowledge you can either do it in mapping or in proxy. It both of these options you have to write JDBC code for making the DB call.

Did you explore Stored procedure ??

cheers,

naveen

[Former Member]

Hi Naveen,

According to the JDBC Sender Adapter docs you can call a stored procedure but it can only contain 1 select statement.

"Specify an SQL EXECUTE statement to execute a stored procedure, which contains exactly one SELECT statement."

http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/frameset.htm

Thanks,

Jesse

[Former Member]

Hi Kevin,

>>Specify an SQL EXECUTE statement to execute a stored >>procedure, which contains exactly one SELECT statement."

this is true for a sender adapter.

But for a reciever JDBC adapter this rules doesnt apply. based on ur postings, i think u need a reciever JDBC adapter, where you can make a call to stored procedure, then in the stored procedure you could have all ur logic. hence from XI its just one call, but on DB you can do a lot of stuff with in a transaction.

cheers,

naveen

[Former Member]

Kevin,

I am sorry i miss read ur post!!! you are correct.

Naveen

[Former Member]

Does anyone else have ideas or am I stuck writing custom JDBC code?

Thanks,

Jesse