Skip to Content
author's profile photo Former Member
Former Member

Select from XI to MS SQL server DB

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jun 01, 2005 at 03:09 AM

    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.

    jdbc-adapter-examples

    Hope this solves your problem.

    Cheers,

    Siva Maranani.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 03, 2005 at 01:21 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 03, 2005 at 01:49 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 04, 2005 at 11:20 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 04, 2005 at 12:14 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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.

  • author's profile photo Former Member
    Former Member
    Posted on Jun 06, 2005 at 07:03 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.