Call a proc after a smartgwt INSERT

[tgilbert]

Dear Support,

I've got some difficulties to implement the following:
- An ADD request is issued from the client
- The Add is catched in a custom datasource, performed, and if successful we would like to call a stored procedure to do some additional work.

@Override
	public DSResponse executeAdd(DSRequest req) throws Exception {
		DSResponse response = super.executeAdd(req);
		
		if (response.getStatus() == DSResponse.STATUS_SUCCESS) {
			
			DSRequest request = new DSRequest(DSOperator.DS_NAME, "fetch", req.getRPCManager());
			request.setOperationId("checkSystem");
			request.setJoinTransaction(true);
			DSResponse resp = request.execute();
		}
		return response;
	}

Here is the operation binding:

 <operationBinding operationType="fetch" operationId="checkSystem">
	        <customSQL>{call SP_SYSTEM_CHECK}</customSQL>
	    </operationBinding>

The problem is that JDBC keeps telling me that the call did not return any rowset... I'm a bit surprised as the last line of my proc is a SELECT statement, and when I execute the proc in my SQL environement, it correctly shows me the returned data.

CREATE PROCEDURE SP_SYSTEM_CHECK
AS
BEGIN

	-- [...] proc code [...]
	
	SELECT 0 as ErrorCode
END
GO

Can you please advise me about what I have to change in order to achieve this ?

Many thanks,
Thomas

PS: We are using SmartGWT Power 4.0p

[Isomorphic]

Take a look at the Custom Querying overview in the docs - it explains what JDBC calls we're making. Since those aren't working, the next steps would be to troubleshoot this on your own with JDBC. Once you have the JDBC calls working and returning a RowSet, this will work with SmartGWT as well.

[tgilbert]

Thank you very much for your advice.
You were right, when directly using JDBC I had the same result, so I investigated further and found out that I had to add the following at the beginning of my proc:

SET NOCOUNT ON;

@see
http://technet.microsoft.com/en-us/library/ms189837.aspx

As I did not know that, I just wanted to share this with the community.

Regards,
Thomas

[Isomorphic]

Glad you have it working now, and thanks for posting the resolution.