Select from XI to MS SQL server DB

[Former Member]

Hi All,

Am implementing a synchronous scenario wherein i submit an HTTP request to do a database select which returns me multiple rows.Am pretty much aware of the canonical XML that the JDBC adapter understands. Have successfully executed the insert record to db scenario.Am only confused at a point where in how my request message type to db and response from db will be single LUW. Am also confused about the request and response mapping.

Can i also have an asynchronous database select and what will be buisness need for that??? Please tell me if there is a weblog that details how to go about select apart from what is available in help.sap.com

Waiting anxiously for a revert. Please help.

Regards,

Raghbir.

[Former Member]

Hi Sudhir/Siva

I have necessary changes as suggested by you...but now am getting the following error:

No implementing class registered for the interface (type ifmmessif, name http_jdbc_MI, namespace http://accenture.com/HTTP_JDBC )

Have checked the following in sxmb_adm

SXMB_ADM --> Integration engine config. with dest://(RFCDEST to int.server) as corr. int. server.

The RFC must have been created in sm59.

The properties were be:

type H (http dest)

target host: integration server

service no: port icm

path prefix: /sap/xi/engine?type=entry

logon security: SAP standard

username/password must be filled. Use the xisuper user or XIAPPLUSER for this. client = 100 (integration server client). Test the connection afterwards. Without filling username or password you should get response.

Empty HTTP request received

I dont know where the problem lies now.

Regards,

Raghbir Singh Kapoor.

[Former Member]

Hi Sudhir,

Yes i have the response datatype as you told. But now how do i send it back to HTTP adapter Result field. I am having another DT as HTTP_IN_DT to map the response from JDBC_IN_DT_response. In HTTP_IN_DT am having only the column names of the database which i map from the response from the database JDBC_IN_DT_response and leave the rest of the fields in the JDBC_IN_DT_response un mapped like table and action etc.

Regards,

Raghbir

[Former Member]

Hi Raghbir.

There is a error in your response Datatype.

As stated, u have created a datatype JDBC_IN_<b>DT</b>_response is <b>incorrect</b>. The name should be JDBC_IN_<b>MT</b>_response (Generally u create message_DT for datatypes but in case of response messages it different. Your data type should be same as your message type name.)

<b>Do not worry that your Data type is JDBC_IN_MT_response. But it works only that way.. )</b>

so ur datatype is :

<JDBC_IN_<b>MT</b>_response>

<Statement_response>

<row> </row>

</Statement_response>

</JDBC_IN_<b>MT</b>_response>

do not forget to make your _response <b>"r" small</b>. It will not work, if your make your _Response.

Now create a Message type JDBC_IN_MT_response for your data type JDBC_IN_MT_response .

Then U should have created two message mappings...

1) Request_MM

HTTP_OUT, JDBC_IN (source, target)

2) Response_MM

JDBC_IN_response, HTTP_IN (source, target)

U should have 1 Interface mapping

HTTP_JDBC_SELECT_MI, JDBC_HTTP_SELECT_MI (source, target)

When u select this, u will get a request and a response tab below. So assign your request and response messages there.

This should definately solve your problem if u are careful in creating ur datatype.

Cheers,

Siva Maranani.

[Former Member]

Hi Sudhir/Siva,

Thanks a million times for your valuable response. I was not aware of the response message type funda for response. I have created a MT same as the JDBCIN_MT as JDBC_IN_MT_response and i can see the response in the request message id field in sxmb_moni tcode in ABAP.

But am getting the same error as i am not able to send the response to the message type of plain HTTP adapter. What should its structure be. Should it contain the same fields as JDBC_IN_MT_response.

Error:

Result:

During the application mapping com/sap/xi/tf/_jdbc_http_MM_ a com.sap.aii.utilxi.misc.api.BaseRuntimeException was thrown: RuntimeException in Message-Mapping transformatio~

I am having two message interface:

For the above 4 data types i have 4 message types and two message interface.

HTTP_JDBC_SELECT_MI -> OUTBOUND,SYNCHRONOUS -> HTTP_OUT message type is the o/p message and HTTP_IN message type is the i/p message.

JDBC_HTTP_SELECT_MI -> INBOUND,SYNCHRONOUS -> JDBC_IN message type is the input message and JDBC_IN_response is the output message.

Am bit confused on the whole flow on the returning back the response to plain http adapter. Please revert as this scenario has become a bottleneck for me.

Regards,

Raghbir Kapoor.

[Former Member]

Hi,

As mentioned in my pervios mail, the response message in synchronous case should be similar to your request message type but for _response will be added.

Request :

Data type -- name_dt

message type -- name_mt

<name_mt>

<statement>

<row> </row>

</statement>

<name_mt>

Response :

Data Type -- <b>name_mt_response</b>

message type -- name_mt_response

(ur data type should be of name_mt_response).

<<b>name_mt_response</b>>

<<b>Statement_response</b>>

<row> </row>

</<b>Statement_response</b>>

</<b>name_mt_response</b>>

make sure your in Statement_response <b>"r"</b> is small.

U can also see the required message structure in your RWB. ( thought the message has failed in message mapping, u can see the required out put structure.)

This should solve your problem.

Cheers,

Siva Maranani.

[Former Member]

Hi Sudhir/Siva:

Thanks a lot for your response. I have got my scenario working to quite an extent. The only problem am facing am not able to handle the resultset as the result of select. The error i am getting is as follows:

Result:

During the application mapping com/sap/xi/tf/_JDBCtoHTTP_MM_ a com.sap.aii.utilxi.misc.api.BaseRuntimeException was thrown: RuntimeException in Message-Mapping transformatio~

Its quite clear that the mapping is not proper. I will list all the objects in the IR:

HTTP_OUT - The Request http that contains only the search term and i map it to key element of the db.

JDBC_IN - The input to the jdbc and contains the canonical xml.(table,access,key,action )

JDBC_OUT - Output as the result of select contains elements as the columns of the databasel.

HTTP_IN - same fields as the jadc_in for the response to be sent to http.

For the above 4 data types i have 4 message types and two message interface.

HTTP_JDBC_SELECT_MI -> OUTBOUND,SYNCHRONOUS -> HTTP_OUT message type is the o/p message and HTTP_IN message type is the i/p message.

JDBC_HTTP_SELECT_MI -> INBOUND,SYNCHRONOUS -> JDBC_IN message type is the input message and JDBC_OUT is the output message.

Two message mappings:

One for HTTP_OUT to JDBC_IN and the other for JDBC_OUT to HTTP_IN.

One Interface mapping containing request and response.

The directory configuration are okay.

Do we need have a specific format to hadle the resultset from the database

The following message in RWB tells tht it has well reached the db and executed the select query:

Receiver JDBC adapter entered. QoS required: BestEffort

JDBC adapter receiver channel JDBC_Receiver: Entered party , service TBIT40_SQLSERVER_BS

Success Structure "Select" processed successfully ("EmployeeDetails")

Success Database request processed successfully

Can you please tell me how to handle the resultset and should there be a specific structure to hadle the resultset???

Regards,

Raghbir Singh Kapoor

[Former Member]

Hi,

<b>Asynchronous Select statements do not serve any purpose</b>.

In synchronous case if the request messge is like,

Request:

<messagetype>

<statement>

<tablename action="SELCT">

<colname></colname>

</tablename>

</statement>

</messagetype>

The response should be like

<messagetype<b>_response</b>>

<statement<b>_response</b>>

<row>

<colname></colname>

</row>

</statement<b>_response</b>>

<messagetype<b>_response</b>>

When a request is sent to DB, the response from the DB has a different kind of message structure, like the one mentioned below. So u need to perform 2 message mappings..

1 request mapping (Requesthttp-->RequestDB)

1 response mapping ( Response DB-->ResponseHttp)

and 1 interface mapping in whihc you will select your request and response message mappings.

U can refer to my weblog to look at the response message in synchronous case.

/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

U can also refer to Weblogs on DB scenario's

/people/sap.user72/blog/2005/03/15/using-xslt-mapping-in-a-ccbpm-scenario

/people/saravanakumar.kuppusamy2/blog/2005/01/19/rdbms-system-integration-using-xi-30-jdbc-senderreceiver-adapter

U can refer to the folloing converstaion for more inputs on DB scenario.

Hope this solves your problem.

Cheers,

Siva Maranani.