Mark
asked on
Stored procedure: Select Failed: 0 The statement did not return a result set.
SQL Server 2005
I have the stored procedure shown below. This has been working from within a Java program fine for months and just started giving me the error: "Select Failed: 0 The statement did not return a result set."
It does actually do the insert, however.
When I run this procedure in Server Management Studio as the same user I do get the memberId returned.
Any idea what could be wrong?
and the java statements:
I have the stored procedure shown below. This has been working from within a Java program fine for months and just started giving me the error: "Select Failed: 0 The statement did not return a result set."
It does actually do the insert, however.
When I run this procedure in Server Management Studio as the same user I do get the memberId returned.
Any idea what could be wrong?
ALTER procedure [dbo].[addNewMember_sp] (
@memberId varchar(11) = null,
@memberType varchar(10),
@lastName varchar(20),
@firstName varchar(15) = '',
@middleInit char(1) = null,
@retireeId varchar(11) = null,
@relationToRetiree varchar(3) = null
)
As
declare @newId varchar(11)
if @memberId is null select @newId = convert(varchar,convert(bigint,max(memberId) + 1)) from members
else set @newId = @memberId
insert into members (memberId, memberType, firstName, lastName, middleInit, retireeId, relationToRetiree)
values (@newId,@memberType,@firstName,@lastName,@middleInit,@retireeId,@relationToRetiree)
select @newId as memberId
and the java statements:
query = "exec hprs.dbo.addNewMember_sp @memberId='" + memberId +
"',@memberType='ACT',@lastName=" + mkDbString(LastName) +
",@firstName=" + mkDbString(FirstName) +
",@middleInit=" + mkDbString(MiddleInit);
try {
rs = stmt.executeQuery(query); // leave as executeQuery(), returns memberId
}
catch ( SQLException sqe) {
System.err.println("Select Failed: " + sqe.getErrorCode() + " "
+ sqe.getMessage() + "\n" + query);
}
'
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for playing!
ASKER
Yes, the mkDbString() function adds single quotes, doubles single quotes if the string contains them, or returns "null" if the string is empty.
Jim Horn: Right, but as I said, it used to do this (return the memberId) and still does in SQL Server Management Studio. If there is a different way to do this that might work more consistently I'm open to suggestions.
I was wondering if there was some update to SQL Server, or to java that could have changed things from April when this last ran successfully. The sqljdbc.jar file is dated 2010, so that hasn't changed.
What might be a better way to do the query in order to return the memberId?