JDBC Adapter - Multiple Resultsets

[Former Member]

Hello,

Does anyone know if the JDBC adapter can return multiple resultsets?

So something like the following JDBC Code:

CallableStatement cstmt;

ResultSet rs;

int i;

String s;

...

cstmt.execute(); //Call the stored procedure

rs = cstmt.getResultSet(); //Get the first resultset

while (rs.next()) {

i = rs.getInt(1); System.out.println("Value from first set = " + i);

}

cstmt.getMoreResults();//Point to the second resultset rs = cstmt.getResultSet();// Get the second resultset

while (rs.next()) {

s = rs.getString(1);

System.out.println("Value from second resultset = " + s);

}

Greetings,

Bart

[Former Member · Accepted solution]

Hi Bart,

the JDBC sender adapter does a regular polling, executing the specified select and update statement. You could use a flag on the database to specify the rows already returned an set the flag in the update statement. I have tried to invoke a result set returning stored procedure using the JDBC sender adapter (call...) but had no success.

Regards,

Heiko

[Former Member · Accepted solution]

Hello Heiko,

Thx for your answer.

The procedure with the update statement is correct, but is only for one table.

I would like to read two different tables. Eg Sales Order header & Sales Order Line Items. I would like to do this in 1 roundtrip to the database. So I would like to avoid a BPM with 2 roundtrips to the database.

Another possibility would be to create a view/join on the two tables, but then I need to transport redundant data. So using two resultsets seems more elegant to me. I only hope it's possible.

Greetings,

Bart

[Former Member · Accepted solution]

Hello Bart,

you could create a process in the BPM designer receiving data from two different senders and merging them.

Cheers,

Heiko

[Former Member]

Hello Bart,

no, this is not supported.

Regards,

Thilo

[Former Member]

You can write your select statement with the join included. I return 1 resultset with information from 4 different tables. It just depends on how you write your select statement.

An easy way to write the select statement is to first model it in Microsoft Access. Create a quick access database and link the tables from SQL to your access database. The create the query you want in Access and verify those are the results that you desire. From the Query design area select view option and then select SQL view. This will show you the SQL statement that you can then use as your select statement in the JDBC adapter. You may have to change a " to a ' but it does work.

Hope this helps.

Jim

[Former Member]

Hi Bart,

sure you can select more than one table either with Stored Procedue or with Query Statement for example (also if you concate more than one SQL statement) like:

update table1 set STATUS = 4 where STATUS = 3 select *

from table1 bk inner join table2 on (KEY = BZ_KEY) where KEY = 449070

- or you create an View onb DB side that makes all of your Data selection:

select * from view where status = 4

- but you also can use stored procedure - in my opinion the most powerfuel tool...

If their is something left -keep asking

Regards Matt