Problem retrieving resultset in servlet

[alsaunde]

Hi,

I am having the following problem: I have an SQL statement that works fine in MS SQL Query Analyser, but when I try to call it from a servlet I get

'Exception in main try block exception: [JRun][SQLServer JDBC Driver]No ResultSet set was produced.'

When I run the code in the MS SQL Query Analyser(which contains a loop condition) I get the following output:

(1 row(s) affected)<this appears 60 times, once for each iteration of the loop, it is an insert into a temporary table>


dayNumber totalEntrants totalEntrantsPerm EnglishDate
----------- ------------- ----------------- --------------------------------------------------
0 69 31 Oct 8 2001
<snip>
59 12 6 Dec 6 2001

(60 row(s) affected)


Does anyone have any experience with this. I am wondering if it is possible to suppress the insert output, or if it is not possible to do what I am trying to do. Sorry for the lengthy post.

 

[carpeliam]

you say it's an insert table? well that wouldn't return a resultset... in jdbc, an insert just returns the number of rows affected. so perhaps you're fine? what code are you using to make the query from your servlet code? <p>Liam Morley<br><A HREF="mailto:"></A><br>&quot;light the deep, and bring silence to the world.<br>light the world, and bring depth to the silence.&quot;

 

[alsaunde]

Below is the SQL code being called, it does a series of inserts before doing a select on the temporary table. My apologies if the code below is very inefficient, but I have just started 'trying' to use more features available in transact sql. Underneath the SQL I have put the java code.

DECLARE @counter int, @daysIntoContest int, @totalforday int, @wordyDate VARCHAR(50)
SET @counter = 0
SET @daysIntoContest = DATEDIFF(day, '10/8/2001 10:27:59 AM', getdate())

IF EXISTS(SELECT count(*) FROM #MyTempTable)
BEGIN
DROP TABLE #MyTempTable
END
CREATE TABLE #MyTempTable (dayNumber INT, totalEntrants INT, EnglishDate VARCHAR(50))

WHILE @counter <= @daysIntoContest
BEGIN
SET @totalforday = (
SELECT count(*)
FROM answers
WHERE (DATEPART(day, entrydate) = DATEPART(day, DATEADD(day, @counter, '10/8/2001 10:27:59 AM')))
AND (DATEPART(month, entrydate) = DATEPART(month, DATEADD(day, @counter, '10/8/2001 10:27:59 AM')))
AND (DATEPART(year, entrydate) = DATEPART(year, DATEADD(day, @counter, '10/8/2001 10:27:59 AM'))))

SET @wordyDate = SUBSTRING(CONVERT(VARCHAR(50),DATEADD(day, @counter, '10/8/2001 10:27:59 AM')),1,11)

INSERT INTO #MyTempTable
VALUES (@counter,@totalforday,@wordyDate)

SET @counter = @counter + 1
END

SELECT * FROM #MyTempTable


Now the Java code,

// Define JNDI InitialContext object.
InitialContext ctx = new InitialContext();

// Look up data source in InitialContext.
DataSource ds = (DataSource)ctx.lookup(&quot;java:comp/env/jdbc/&quot; + dataSource);

dbConnection = ds.getConnection();

// Create Statement object.
dbStatement = dbConnection.createStatement();

// Execute the query.
dbResultSet = dbStatement.executeQuery(sqlStatement);

 

[ChrisCarroll]

Hi,

First thing that comes to mind is to try storing your procedure on the server, say using query analyzer:

Create Procedure myCounter
AS
.......

Then from your java program try:
// Execute the query.
dbResultSet = dbStatement.executeQuery(&quot;exec 'myCounter'&quot;

and see if that works. It may be that to get the statement to work it has to be prepared or compiled first?

I wouldnt worry about the query analyzer giving you 60 'insert' responses - I think the query analyzer sets its connections to be more verbose than a normal application