on 07-09-2012 10:33 PM
Hi,
I have a JDBC to Proxy scenario where PI is executing a stored procedure to fetch data from a SQL system. Even though there is data to be fetched, the PI sender JDBC Channel is still throwing the error "The statement did not return a result set".
Does anyone faced this issue and resolved it successfully.
Thanks,
Sarat
This reply is too late for you, but may help someone else who is now facing asimilar issue.
I am using Spring, Java and SQL Server 2012.
The task was to migrate from Weblogic 9.2 + Sybase ASE to Tomcat 7.0 and SQL Server 2012.
The stored procedures returned results in SQL server Mgmt Studio but not while using Java.
Solution :
Write SET NOCOUNT ON just after the BEGIN of the stored procedure
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Sarat,
Did you check with DB team for any solutions at Database side?
Refer the below threads if you haven't read earlier.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
have you tested stored procedure directly and checked the result using SQL editor
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Please check the following.
a) Check the stored procedure statement has any typo in the communication channel
b) Please check this thread http://scn.sap.com/thread/1791705
The jdbc drivers some of them for MS SQL Server might not deployed properly in the PI and so you get this error.
What is your PI version? I have installed oracle jdbc drivers in PI 7.1 before and not with SQL Server. The thread I refered shows the similar situation. I would first recommend you to see the deployment of jdbc drivers for SQL Server. Take help from BASIS.
Please see the SAP note 639702.
Also this link
Hi Baskar,
Thanks for your inputs. I checked with Basis Team and everything looks fine.
Let me throw some more light on the scenario.
Here is our requirement -
1. PI writes data into Database with status as "A".
2. There will be a job running in Database that updates the status to "B".
3. PI sender channel will execute the stored procedure. This is in the "Query Execute Statement" inside the channel.
4. The stored procedure will update the status to "C" from "B".
5. Then this stored procedure will select the required fields and passes it to PI.
6. Now PI will update the status to "D" from "C". This is in the "Update SQL Statement" inside the channel.
This is the entire process.
Please let me know yur thoughts on this.
Thanks,
Sarat
There are many interdependent interfaces/jobs around this interface. So if one step fails, it will be slightly hard to debug the cause of the error.
1) This step is carrried out through another PI interface. Is that right?
2) What kind of job it is? How frequent you run this job. This job is primarily input for the stored procedure to execute. Assume that your sender channel is always trying to execute (before the database job and after the job).. If your database job is not executed then the status would be A. If your stored procedure is looking for the status B to update to C. Since there are no records of B, you get no resultset message from the sender channel. This answers your question
Suggestion: You might create a script where you execute database job and then start the jdbc channels to execute your SP. This might be helpful.
You can control the communication channels externally. You can see Williams blog for this..
Hi Sarat,
Are you using the same Database user (what is used in PI) when checking stored procedure call from native SQL editor? The problem could be some transaction still open from SQL editor by end user and hence the data is not reflecting when executed externally using PI JDBC sender channel with Stored procedure call. Remember, in such situations the stored procedure will work from SQL editor and data will be reflected. I faced this issue in my previous projects. Ask your DB adminstrator if such instances still open.
Praveen
Hi Baskar,
1. Yes you are correct. The first piece i.e. insertion into the database happens by another PI Interface.
2. PI communication channel executes the stored procedure every 5 min and gets the required data. The stored proc will be modified to return empty resultset when the select query doesn't have anything to return. That ensures our PI communication channel not to be errored out. But in my case there is data that has to be pulled out through the select statement in the stored proc but still PI communication channel is erroring out with the issue "com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set" .
Any thoughts on this ???
Thanks,
Sarat
I understand Sarat, inserting data into database from PI will work. The only issue is happening in your case is while reading db tables/stored procedure from external client using API wrappers (in this case PI JDBC sender channel - Java API). So some transaction is not commited.
Can you perform these
- Praveen
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
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.