Need help in webservice to JDBC scenario

[Former Member]

Hi All,

My scenario is a synchronous webservice to JDBC scenario. The SOAP request contains two fields one is ID & the other field is Date which is an optional field. I need to perform a SELECT statement on JDBC based on ID & Date. SOAP request will always contain ID field & Date may or maynot be there in the request. If Date present then search should be based on ID & Date , if Date not present search should be based on ID only. How to achieve this with the help of standard JDBC reciever structure or is there any other way to achieve this?

Thanks

Navin

[former_member200962 · Accepted solution]

This can be achieved in the mapping for SOAP Request --> JDBC Request:

SOAP_ID ---> JDBC_ID (under key tag)

SOAP_Date --> Exists -->
                                                  ---> And(to check if Date node exists and is not blank) --> if(SOAP_Date -->Then) --> JDBC_Date
SOAP_Date -->
                                  --> equalS -->not--> 
Constant(Keep it blank)-->

JDBC structure is shown in the previous post. JDBC_Date under key tag should be 0..1 while JDBC_ID should be 1..1

Regards,

Abhishek.

[Former Member]

You can create your target structure as shown below and check the existence of DATE field in the source(using standard function "Exists" ) and then only create the target DATE node.

<StatementName>
          <dbTableName action=u201DSELECTu201D>
              <table>realDbTableName</table>
              <access>
                  <col1/>
                 <col2/>
                 <col3/>
              </access>
              <key1>
                 <DATE/>
                 <ID/>
              </key1>
          </dbTableName> 
   </StatementName>

http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=%28J2EE3417700%29ID0591392850DB0143661345236723370...

Pooja

[Former Member]

I think you can acheive this using action as SQL_QUERY | SQL_DML.

Write down the SQL's for 2 differnt cases you have and map them to the <access> node based on your logic.

http://help.sap.com/saphelp_nw70/helpdata/EN/2e/96fd3f2d14e869e10000000a155106/content.htm

[rajasekhar_reddy14]

Hi,

Follow the same design what you mentioned,JDBC Adapter works as synchronous,but you have to write a query such a way that it take filed value is a much to retrive the data from DB and make date field is optional.

i want to know one thing if you use only filed is the key element to retrieve data from DB its going to be an issue??if you ignore date filed.??

WHy because if you use Filed value to retrieve the data its very easy to form a query,that is the reason.

Regards,

Raj

[Former Member]

Hi Navin,

This can be achieved easily by checking if the Value or Element of date exist in source structure.

You can use createif, exists node functions in message mapping to achieve this.

Regards,

Gautam Purohit