cancel
Showing results for 
Search instead for 
Did you mean: 

Error in RFC to JDBC Sync scenario.

former_member462539
Participant
0 Kudos

Need help or advice on what went wrong in RFC to JDBC sync scenario where I'm getting an error such as


"Unable to execute statement for table or stored procedure. "EMPLOYEE"(Structure 'selectStmt') due to com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'EMPLOYEE'. "

Here is my JDBC target structures(Request)

MT_SelectEmployee

     selectStmt

          dbTable

               action

               table

               access

                    empno

                    designation

               key1

                    empno   

action node is mapped to constant 'SELECT', table node is mapped to constant 'EMPLOYEE' and access node is mapped to constant '' (blank)

Response structure

MT_SelectEmployee_response

               row

                    empno

                    designation

               key1

                    empno  

        

Please suggest if there is anything went wrong? unfortuantley logSQL statement in entry in the communication channel is not showing the query formed by the adpater in the audit log.

Regards

Vishnu.

Accepted Solutions (1)

Accepted Solutions (1)

former_member462539
Participant
0 Kudos

This is what  I see in the payload as a response.

<?xml version="1.0" encoding="UTF-8"?>

<ns1:ZRFC_EMPLOYEE_QUERY.Response xmlns:ns1="urn:sap-com:document:sap:rfc:functions"><QUERY_LIST></QUERY_LIST></ns1:ZRFC_EMPLOYEE_QUERY.Response>

former_member184720
Active Contributor
0 Kudos

I don't think this is the response payload but it is the final (post response mapping xml)

As i said, if you want to see how the response structure looks like then just add some condition in your response mapping to fail.

constant(FALSE) -> create if ->  Target message type/any target field with 1.1 occurrence.

If you add above logic, your response mapping would fail and you should be able to compare the response payload vs the structure that you defined.

former_member462539
Participant
0 Kudos

Hello Hareesh,

Thanks a lot for being help. something is missing and teasing all my effort since some time. Sorry for asking this silly help(debugging) in this forum.

I have put up the CREATEIF node function condition TO the QUERY_LIST node which is having cardinal 1:1 by taking off the mapping of node selectStmt_response, obviously mapping failed such as "value missing in queue context. Target XSD requires a value for this element, but the target-field mapping does not create one... ".

selectStmt_response(1..Unbounded)  --> QUERY_LIST (1..1)


replaced with


Constant-->createif-->QUERY_LIST (1..1)  {mapping failed in ESR as well as at run time}


But still I  couldn't be able to compare the response payload vs the structure that I defined.


Here is my source/Request JDBC structure.


MT_SelectEmployee

     selectStmt

          dbTable

               action

               table

               access

                    empno

                    designation

               key1

                    empno 

RFC Request structure

ZRFC_EMPLOYEE_QUERY

     empno

     QUERY_LIST

          item

               empno

               designation

Here is Target/response JDBC structure.


MT_SelectEmployee_response

          SelectStmt_response

               row

                    empno

                    designation

RFC Response structure

ZRFC_EMPLOYEE_QUERY.Response

     QUERY_LIST

          item

               empno

               designation

Below is the Run time payload of request, and did not get response payload due to mapping error.


<ns1:MT_SelectEmployee xmlns:ns1="http://test.com/poc2"><selectStmt><dbTable action="SELECT"><table>demo.dbo.EMPLOYEE</table><access><empno></empno><designation></designation></access><key1><empno>897876</empno></key1></dbTable></selectStmt></ns1:MT_SelectEmployee>



What is missing in above structures?

former_member184720
Active Contributor
0 Kudos

>>>What is missing in above structures?

Make sure that both the names are in Sync (including Case)

MT_SelectEmployee

     selectStmt -> Lower case

MT_SelectEmployee_response (response should be in lower case)

          SelectStmt_response -> upper case

In addition -

Can you try enable the staging for Message preparation step as well? (ICo -> advanced tab -> Message Preparation) and see if it logs the payload.

former_member462539
Participant
0 Kudos

What does it mean in terms of JDBC adapter? (Response message for message <msgGUID>(OUTBOUND) passed to the waiting "request" thread)

former_member462539
Participant
0 Kudos

Thanks Hareesh,

Sorry it was typo in this thread. both source and target are sync.

MT_SelectEmployee

     selectStmt -> Lower case

MT_SelectEmployee_response (response also in lower case)

          selectStmt_response -> lowerr case

Enabled staging & Logging for Message preparation step in ICo as STORE, LOG. Checked all staging properties to STORE and Logging properties to LOG.


I don't see any promising entrees in the Log viewer. is there anything needs to be set in the trace level to see this staging & logging entrees in viewer?


Basically I wanted to see what DB is returning for the SELECT query? whether it returns zero rows or at least 5 rows as showing in studio.

former_member184720
Active Contributor
0 Kudos

This is not an issue. The response message is being sent to the requester as it is a synchronous interface.

former_member462539
Participant
0 Kudos

Have set the tracing level of JDBC adapter to "ALL". Did not find any clue in the log viewer such as what SQL DB is returning for the query executed, such as 0 rows or some rows. I suspect the problem is in mapping, or probably DB is NOT returning any rows.

former_member462539
Participant
0 Kudos

Hello Hareesh,

Issue has been fixed finally. Thanks a lot for providing promising ideas and in fact you have made corrected my target structure.

The problem is with Apostrophe in the WHERE condition...

Answers (2)

Answers (2)

former_member462539
Participant
0 Kudos

Dear Experts,

Now I see the below query in the audit log. Query Looks like weird and column names OR * is missing. What is missing in my mapping or data type Please?

SELECT FROM demo.dbo.EMPLOYEE WHERE (empno=897876)


This is the payload that I see.


<ns1:MT_SelectEmployee xmlns:ns1="http://test.com/poc2"><selectStmt><dbTable action="SELECT"><table>demo.dbo.EMPLOYEE</table><access></access><key1><empno>897876</empno></key1></dbTable></selectStmt></ns1:MT_SelectEmployee>

former_member184720
Active Contributor
0 Kudos

<access></access>


Check for your access segment mapping and the fields under it.


Test your mapping with input payload and make sure that those segments are being cerated. Set the access segment occurrence to 1..1

former_member462539
Participant
0 Kudos

Thanks a lot Hareesh for your responses, much appreciated. Looks like promising...Request is OK now.

response is having some issue such as getting blank node

<?xml version="1.0" encoding="UTF-8"?>

<ns1:ZRFC_EMPLOYEE_QUERY.Response xmlns:ns1="urn:sap-com:document:sap:rfc:functions"><QUERY_LIST></QUERY_LIST></ns1:ZRFC_EMPLOYEE_QUERY.Response>

where can I see DB rows returned, in PI? Please suggest. The query fomred by Adaper is executing fine wihtout any issues and I can see results in the SQL DB management studio,

former_member184720
Active Contributor
0 Kudos

Did you enable the logging/staging to see those message payloads. If you not you can try that.

Your response structure might not be in the expected format.

You can refer to the blog that i shared earlier and build your response structure accordingly.

MT_SelectEmployee_response

               row

                    empno

                    designation

               key1

                    empno 

to

MT_SelectEmployee_response

     selectStmt_response

               row

                    empno

                    designation

former_member462539
Participant
0 Kudos

Why does my JDBC Receiver Adapter Log is showing the message/application has sent the message asynchronously instead of synchronously? Do I need to set any parameter in the Receiver JDBC Adapter? And also, why the response message passed to the waiting "request" thread? I think due to some reason, I can't see any rows in PI which are returned from DB...Any idea or help please?

4/6/2015 8:25:06.719 PMInformationExecuting Response Mapping "http://test.com/poc2/OM_EMPLOYEESync" (SWCV d50ba3c0d2de11e4af67e5530a0f11bd)
4/6/2015 8:25:06.746 PMInformationApplication attempting to send an XI message asynchronously using connection JDBC_http://sap.com/xi/XI/System
4/6/2015 8:25:06.749 PMInformationTrying to put the message into the send queue
4/6/2015 8:25:06.753 PMInformationResponse message for message 8aa34cf3-dcbc-11e4-8398-0000003ad842(OUTBOUND) passed to the waiting "request" thread
4/6/2015 8:25:06.761 PMInformationThe application sent the message asynchronously using connection JDBC_http://sap.com/xi/XI/System. Returning to application
former_member462539
Participant
0 Kudos

is this selectStmt_response node mandatory above to row node as parent?,  kindly confirm?

former_member184720
Active Contributor
0 Kudos

I think so.. Why don't you give a try ?

If you want to make sure, just let your response mapping fail(define some logic in response mapping) so that you can view the xml formed by adapter.

former_member462539
Participant
0 Kudos

I have added selectStmt_response node as parent to node row but still no luck. any help please?

MT_SelectEmployee_response

     selectStmt_response

               row

                    empno

                    designation

former_member462539
Participant
0 Kudos

My response mapping is very simple.

MT_SelectEmployee_response --> ZRFC_EMPLOYEE_QUERY.Response

     selectStmt_response(1..Unbounded)                 --> QUERY_LIST (1..1)

               row(0..unbounded)                          --> item   (0..unbounded)

                    empno(0..1)                               --> Zempno(0..1)

                    designation(0..1)                         --> zdesignation(0..1)

Unfortunately I cant see any child elements under QUERY_LIST node in PI payload. I'm not sure whether PI is capturing  SQL DB rows returned. But Select QUERY is executing fine in SQL Management studio and returning 5 rows.

former_member184720
Active Contributor
0 Kudos

>>> due to com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'EMPLOYEE'. "

Do you have the table in your receiver database?

If so can you try adding a schema as prefix before the table name. <<schema>>.EMPLOYEE and see if that helps ?

You can also verify document format here -

former_member462539
Participant
0 Kudos

Yes,  We do have employee table in the DB. As per your advice, added demo.dbo.EMPLOYEE.

Still no luck.