com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

Using JDBC 2.0 and SQL 2008

What do I need to do to get this to work?
It used to work with Windows 2003 and SQL 2000.
Here is the code:
 com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

<%@ page contentType="text/javascript" %>
<% // The above JSP code set the generated page to be recognized as a javascript source. %>
<% java.sql.Connection conn=null;
try {
// Establish database connection
javax.naming.Context ctx = new javax.naming.InitialContext();
javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("jdbc/bookingsSQL");
conn = ds.getConnection();
// Execute SQL and get a result set
String sql = "Create Table #Reservations (Checkin datetime not null, " +
                   "checkout datetime not null) " +

"Insert Into #reservations (Checkin,Checkout) " +
"select distinct checkin,checkout from bookings " +

"Create Table #myTable (      ag_year int,  " +
                  "ag_month smallint,  " +
                  "ag_day varchar(10), " +
                  "ag_message varchar(10), " +
                  "ag_bgcolor varchar(10),  " +
                  "ag_action varchar(10), " +
                  "ag_boxit varchar(10),  " +
                  "ag_fgcolor varchar(10),  " +
                  "ag_bgimg varchar(10), " +
                  "ag_html varchar(10), " +
                  "resid int ) " +

"Declare @Day int,@ID int ,@Start datetime ,@END datetime " +

"DECLARE mydates CURSOR " +
"READ_ONLY " +
"FOR " +
"Select Checkin,Checkout " +
"FROM #reservations " +

"OPEN mydates " +

"FETCH NEXT FROM mydates INTO @start,@end " +

"WHILE (@@FETCH_STATUS <> -1) " +
      "BEGIN " +
      "IF (@@FETCH_STATUS <> -2) " +
            "BEGIN " +
            "Select @Day = 0       " +
                        "While dateadd(dd,@day,@start) <= @end " +
                              "Begin " +
                              "insert #myTable (ag_year, ag_month, ag_day, ag_message, ag_bgcolor, " +
                               "ag_action, ag_boxit, ag_fgcolor, ag_bgimg, ag_html,resid) " +
                 
                              "Select year(dateadd(dd,  @day, @start)),month(dateadd(dd, @day,@start)), " +
                              "day(dateadd(dd, @day, @start)), 'Reserved', 'red', null, 'true', null, null, " +
                               "null,@ID " +
                              "Select @day = @day + 1       " +                  
      "END " +
                       
            "END " +
     
            "FETCH NEXT FROM mydates INTO @Start,@END " +
      "END " +

"CLOSE mydates " +
"DEALLOCATE mydates " +

"select * from #myTable " +

"drop table #reservations " +
"drop table #mytable";

java.sql.Statement stmt = conn.createStatement();
java.sql.ResultSet rs = stmt.executeQuery(sql);

// Loop through the result set to generate multiple fAddEvent() functions
while (rs.next()) {
%>
WineInstituteAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

VenabiliCommented:
On which line do you see the exception?
And had something else changed between SQL 2000 and SQL 2008?
Which Java version is used?
And what driver do you use in 2008?
Any other errors?
0
WineInstituteAuthor Commented:
No line number, but it has to do with the executeQuery method. I am now running Microsoft SQL JDBC 2.0 java drivers. The OLD version I used to use in SQL 2000 that worked. I am no Java expert, so I would need hel pin re-writing the code posted above to work with the new specs of JDBC 2.

This is what I found:
The method executeQuery is designed for statements that produce a single result set, such as SELECT statements.

The method executeUpdate is used to execute INSERT, UPDATE, or DELETE statements and also SQL DDL (Data Definition Language) statements like CREATE TABLE, DROP TABLE, and ALTER TABLE. The effect of an INSERT, UPDATE, or DELETE statement is a modification of one or more columns in zero or more rows in a table. The return value of executeUpdate is an integer (referred to as the update count) that indicates the number of rows that were affected. For statements such as CREATE TABLE or DROP TABLE, which do not operate on rows, the return value of executeUpdate is always zero.

The method execute is used to execute statements that return more than one result set, more than one update count, or a combination of the two. Because it is an advanced feature that the majority of programmers will never use, it is explained in its own section later in this overview.
0
VenabiliCommented:
Yeah, executeQuery is a bad idea here - you do not have anything to actually return a result set. I am not sure how this could have worked in 2000 although I might be missing something in there...

But then you expect ResultSet - what do you expect there?

Try using execute or even executeBatch (http://www.exampledepot.com/egs/java.sql/BatchUpdate.html )
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

WineInstituteAuthor Commented:
When I run it manually on the SQL server it returns: (I included the headers)
ag_year      ag_month      ag_day      ag_message      ag_bgcolor      ag_action      ag_boxit      ag_fgcolor      ag_bgimg      ag_html      resid
2008      1      15      Reserved      red      NULL      true      NULL      NULL      NULL      NULL
2008      1      16      Reserved      red      NULL      true      NULL      NULL      NULL      NULL
2008      1      17      Reserved      red      NULL      true      NULL      NULL      NULL      NULL
2008      1      18      Reserved      red      NULL      true      NULL      NULL      NULL      NULL
2008      1      19      Reserved      red      NULL      true      NULL      NULL      NULL      NULL
2008      1      20      Reserved      red      NULL      true      NULL      NULL      NULL      NULL
2008      5      30      Reserved      red      NULL      true      NULL      NULL      NULL      NULL
2008      5      31      Reserved      red      NULL      true      NULL      NULL      NULL      NULL

When I try execute it says incompatible type as the error.
0
WineInstituteAuthor Commented:
oh and the executeBatch error is:
executeBatch() in java.sql.Statement cannot be applied to (java.lang.String)
java.sql.ResultSet rs = stmt.executeBatch(sql);
0
VenabiliCommented:
Yes - that's why I gave you the link to see how to use executeBatch - it does not get a parameter.
>When I try execute it says incompatible type as the error
How exactly do you call it? If you left it java.sql.ResultSet rs = stmt.execute(sql); then the error is normal
execute returns a boolean.

The problem is that you cannot call the commands in the same way as in pure SQL... so you will need to rework it a bit. See the link above for execureBatch...

But again - what does your ResultSet need to have? Because neither of these two will give you a ResultSet
0
VenabiliCommented:
Just an idea but why don't you create a stored procedure in the SQL server and then just invoke it? It will be much cleaner...
0
WineInstituteAuthor Commented:
I have an SP there, but don't know how to call it correctly. I looked at the executeBatch example you provided, but was a little confused as to how I would go about rewriting my code to match that scenario. Is calling the sp easier? my sp is called BookingInput. can you provide help in calling it and returning the results? I am not sure as to you question about "what does your ResultSet need to have". In the code I am pulling certain data from a table and splitting it and then pulling just day, month and year values along with html code to display the content in red for those days pulled. Did I answer your question?
0
WineInstituteAuthor Commented:
Here is updated cide calling the sp, but get an error of:

SQLServerCallableStatement:1
[Sun Apr 04 16:21:08 PDT 2010] SQLServerCallableStatement:1
[Sun Apr 04 16:21:08 PDT 2010] null
[Sun Apr 04 16:21:08 PDT 2010] Service Error: java.lang.NullPointerException

<%@ page contentType="text/javascript" %>
<% // The above JSP code set the generated page to be recognized as a javascript source. %>
<% java.sql.Connection conn=null;
try {
// Establish database connection
javax.naming.Context ctx = new javax.naming.InitialContext();
javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("jdbc/bookingsSQL");
conn = ds.getConnection();
// Execute SQL and get a result set
java.sql.CallableStatement cs = conn.prepareCall("{call BookingInput}");
cs.execute();
System.out.println(cs);

java.sql.ResultSet rs = cs.getResultSet();

System.out.println(cs);
System.out.println(rs);

while (rs.next())

System.out.println(rs);
//// Loop through the result set to generate multiple fAddEvent() functions
%>

fAddEvent(<%=rs.getString("ag_year")%>,<%=rs.getString("ag_month")%>,<%=rs.getString("ag_day")%>,
'<%=rs.getString("ag_message")%>','<%=rs.getString("ag_action")%>','<%=rs.getString("ag_bgcolor")%>','<%=rs.getString("ag_fgcolor")%>','<%=rs.getString("ag_bgimg")%>',<%=rs.getString("ag_boxit")%>,'<%=rs.getString("ag_html")%>');

<%
// Close db connection and error handling
rs.close();
cs.close();
} catch (Exception e) {
System.out.println("Service Error: "+e);
} finally {
if (conn!=null)
try { conn.close(); } catch (Exception ignore) {}
}
%>
0
VenabiliCommented:
Does the stored procedure return a result? Because it does  not seem to do this - and then you cannot have a result set... Which seems to be the problem here
0
WineInstituteAuthor Commented:
do you mean if I run it on the sql server? cause it does display results there, just not when I call it with the code above.,
If I change the sql statement to select * from bookings it pulls data, on the original code at the very top of this post.

if I run the new code it gives the error of Service Error: java.lang.NullPointerException

0
WineInstituteAuthor Commented:
ok, I opened up monitoring on the SQL server and it shows that the sp ran.
0
VenabiliCommented:
It runs but does it return any result back to the invoking engine?

It seems like ResultSet remains a null

PS: It is getting a bit late here so I am off to bed. I will see if I can get somene to keep helping here.
0
WineInstituteAuthor Commented:
if I run an sp with select * from booking, that returns to the page fine. but, I need the selected split data returned and not the whole table.

0
VenabiliCommented:
Do you want to post both stored procedures - with the split data and with the select * so we can try to find out why one works and the other does not?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WineInstituteAuthor Commented:
It was a NO COUNT and drop table issue. once removed works. Plus I had missing brackets at the while(rs.next...
0
WineInstituteAuthor Commented:
pointed me in the right direction.
0
VenabiliCommented:
The brackets were there in the initial code.

Anyway - good that it works now.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java EE

From novice to tech pro — start learning today.