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

Microsoft SQL Server 2005Java

Avatar of undefined
Last Comment
Mark

8/22/2022 - Mon
SOLUTION
Éric Moreau

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Jim Horn

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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
Mark

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mark

ASKER
Thanks for playing!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck