Link to home
Start Free TrialLog in
Avatar of Mark
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?

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

Open in new window


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);
                    }
'

Open in new window

SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mark
Mark

ASKER

Éric Moreau:
can you get the exact "query" variable content?
exec hprs.dbo.addNewMember_sp @memberId='011780',@memberType='ACT',@lastName='Ashbaugh',@firstName='Brian',@middleInit='D'

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:
Inserting a row is not the same as returning a result set.
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?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mark

ASKER

Thanks for playing!