on 04-06-2015 11:59 PM
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.
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>
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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?
>>>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.
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.
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>
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
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
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 PM | Information | Executing Response Mapping "http://test.com/poc2/OM_EMPLOYEESync" (SWCV d50ba3c0d2de11e4af67e5530a0f11bd) |
4/6/2015 8:25:06.746 PM | Information | Application attempting to send an XI message asynchronously using connection JDBC_http://sap.com/xi/XI/System |
4/6/2015 8:25:06.749 PM | Information | Trying to put the message into the send queue |
4/6/2015 8:25:06.753 PM | Information | Response message for message 8aa34cf3-dcbc-11e4-8398-0000003ad842(OUTBOUND) passed to the waiting "request" thread |
4/6/2015 8:25:06.761 PM | Information | The application sent the message asynchronously using connection JDBC_http://sap.com/xi/XI/System. Returning to application |
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
80 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.