Table Joins in JDBC Adapter

[Former Member]

Hi all,

We are working on a SOAP-to-DB synchronous scenario where the DB is to be accessed using the following SQL query:

Select T1.field1,T2.field2

from table T1,table T2

where T1.field3=T2.field1

.

.

(..some arbitrary conditions)

There is a self cross-product,as well as renaming of the tables(aliasing).

Is it possible to implement this scenario in the receiver JDBC adapter?

What should be the format of the Data Type for the DB request?

Also,is there any other way of approaching this problem?

We are eagerly waiting for some suggestions.

Regards,

Puloma Chaudhuri/Sujatha Narayanan.

[Former Member]

Yes it is possible.

You have to create a Data Type with this format:

Table Name ( as Element )

action (as Attribute) type:string

table (as Attribute) type:string

access ( as Element) type: Data Type containing Table Structure

key( as Element) type: Data Type containing Table PK.

Let me know if you need further information.

We have similar Scenario here and it's working fine with JOINs and direct SQL statement.

Regards,

Juan José

[Former Member]

You can also use stored procedures to select the data for you, and you just call the stored procedure in the adapter config

Cheers,

Mark

[MichalKrawczyk]

>What should be the format of the Data Type for the DB request?

did you have a look at this:

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Regards,

michal

[Former Member]

Hi,

Have you tried using action="SQL_QUERY"?

The access element contains the select statement.

If action="SQL_DML", then access can contain update, insert or delete statement.

help.sap.com contains the details on its usage.

Regards,

Bill