Javascript SQL did not return a result error using JDBC 2.0

how do I rewrite this for 2.0 jdbc to use the execute method instead of the executeQuery method?
 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()) {
%>

What do I have wrong?
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.

objectsCommented:
boolean success = stmt.execute(sql);
0
objectsCommented:
and either break up your sql and make multiple execute calls
or put everything into a stored procedure and call that
0
WineInstituteAuthor Commented:
I posted my code, what needs to change to what? Stored procedure, how to call from the code?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

WineInstituteAuthor Commented:
ok, so here is what I have now: Bit I am getting: 'try' without 'catch' or 'finally'
try { errors. Do I have the code correct below?


<%@ 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
CallableStatement cs = con.prepareCall("{call BookingInput}");
ResultSet rs = cs.executeQuery();
%>

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();
stmt.close();
} catch (Exception e) {
System.out.println("Service Error: "+e);
} finally {
if (conn!=null)
try { conn.close(); } catch (Exception ignore) {}
}
%>
0
objectsCommented:
> <% }

you don't need a brace there, should just be:

<%
0
WineInstituteAuthor Commented:
symbol  : class CallableStatement
cannot find symbol
CallableStatement cs = con.prepareCall("{call BookingInput}");
^
cannot find symbol
symbol  : variable con
CallableStatement cs = con.prepareCall("{call BookingInput}");
                                      ^
cannot find symbol
symbol  : class ResultSet
ResultSet rs = cs.executeQuery();
^
cannot find symbol
symbol  : variable stmt
stmt.close();
^
0
objectsCommented:
you need to import CallableStatement

> CallableStatement cs = con.prepareCall("{call BookingInput}");

CallableStatement cs = conn.prepareCall("{call BookingInput}");

> stmt.close();

should be:

cs.close();

0
WineInstituteAuthor Commented:
illegal start of expression
import java.sql.CallableStatement;

code:
<% java.sql.Connection conn=null;
import java.sql.CallableStatement;
try {
// Establish database connection
javax.naming.Context ctx = new javax.naming.InitialContext();
0
objectsCommented:
jsp imports don't go there

just use the fqn in your code

java.sql.CallableStatement cs = conn.prepareCall("{call BookingInput}");
0
WineInstituteAuthor Commented:
cannot find symbol
symbol  : class ResultSet
ResultSet rs = cs.executeQuery();
0
WineInstituteAuthor Commented:
changed:
ResultSet rs = cs.executeQuery();
to
java.sql.ResultSet rs = cs.executeQuery();
but now I get:

Service Error: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
0
WineInstituteAuthor Commented:
we've gone full circle back to the same place I was. How can I see what is being returned to the page?
0
WineInstituteAuthor Commented:
This is what I found, but I do not know if it applies to SP's or not:
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
objectsCommented:
sounds like your sp does not return a result set
0
WineInstituteAuthor Commented:
when I run it on the SQL server it returns 90 rows.
0
objectsCommented:
can you post the sp
0
WineInstituteAuthor Commented:
it's at the top of this post. I moved that code into an sp.
If you are talking about the result of the sp, here are a few rows w/ 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
0
objectsCommented:
looks like you need to use execute() and use getResultSet() method to get the result sets
http://java.sun.com/javase/6/docs/api/java/sql/Statement.html#getResultSet()
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:
how do I write that with what I have?
0
objectsCommented:
just call getResultSet() after calling execute() as I already showed you
0
WineInstituteAuthor Commented:
like this:
java.sql.CallableStatement cs = conn.prepareCall("{call BookingInput}");
java.sql.ResultSet rs = cs.execute(); <-CHANGED
then?
getResultSet(); ?
0
WineInstituteAuthor Commented:
with the above, I get incompatible type error and then can't find symbol for the getResultSet();
0
WineInstituteAuthor Commented:
ok, changed the code around a little and now I get: Service Error: java.lang.NullPointerException

conn = ds.getConnection();
// Execute SQL and get a result set
java.sql.CallableStatement cs = conn.prepareCall("{call BookingInput}");
cs.execute();
java.sql.ResultSet rs = cs.getResultSet();
0
WineInstituteAuthor Commented:
ok, I split the sp into 6 called each separately from the code and tested and received the same error of  The statement did not return a result set.
in this sp it it just a select * from #myTable  query. So now what?
0
WineInstituteAuthor Commented:
ok, back to square one:
how do I rewrite this for 2.0 jdbc to use the execute method instead of the executeQuery method?
 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()) {
%>

What do I have wrong?
this is what the sql query returns:
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
0
WineInstituteAuthor Commented:
If I put select * from bookings it works without error.
0
objectsCommented:
provided lots of assistance
0
WineInstituteAuthor Commented:
well then, lets get this solved.
0
objectsCommented:
well start by using the code I suggested, and check that the sp was actually called successfully
(by checking the return value)
0
WineInstituteAuthor Commented:
Here is the updated Code:
<%@ 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) {}
}
%>

the error is:
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

How do I see the actual return value? I put the Systemout's in places, but not sure.
0
WineInstituteAuthor Commented:
i opened up sql monitoring and the sp ran.
0
objectsCommented:
> How do I see the actual return value?

what return value?


0
objectsCommented:
if getResultSet() is returning null then it means it didn't run successfully, or it didn't return a result set
0
WineInstituteAuthor Commented:
if I run an sp with a select * from booking, that runs and gives results back to my page. It is just that I need the selected  split data back not the whole table.
0
objectsCommented:
use something like this to check if you have result sets

if (cs.getMoreResults()) {
   ResultSet rs = cs.getResultSet();
   ....
}

0
WineInstituteAuthor Commented:
I get errors, probably cause I just put the if.... in and don't know where to put the }
Catch with no try error

<%@ 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);
if (cs.getMoreResults()){
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
objectsCommented:
yes, you need it in there

<%
// Close db connection and error handling
rs.close();
}
cs.close();
} catch (Exception e) {


might be worth studying up on  some java basics. will save you lots of wasted time
0
WineInstituteAuthor Commented:
SQLServerCallableStatement:1 in the logs, but no errors.
what was that supposed to do?
0
objectsCommented:
sounds like your sp does not return a result set
0
WineInstituteAuthor Commented:
but it does when I set a simple query. so where in the code is the breakdown? How do I need to send all the commands to the sql server and get back what I want? We have tried sending the code and an sp, so its a coding issue. How about if you look at the code and post the fixed code so we can end this madness.

Here is the goal: Split the date range below so they are in their own field in a temp table:
2008      11      15      
2008      11      16
2008      11      17      
2008      11      18      
2008      11      19      
2008      11      20      
etc...
then add the following in their own field in that table:
Reserved      red      NULL      true      NULL      NULL      NULL      NULL

SPLIT THESE FIELDS:  2008-11-15 00:00:00.000      2008-11-27 00:00:00.000
0
objectsCommented:
problem would appear to be in your sp
0
WineInstituteAuthor Commented:
really? Where? runs fine on the server.

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;
0
objectsCommented:
sorry i'm a java guy, not a database guy
0
WineInstituteAuthor Commented:
I knew you were going to say that.
0
objectsCommented:
i have provided assistance with the java side of what is posted, and showed how to use the execute() method which is what was asked.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I'm a SQL guy, not a Java guy :)

The issue lies within the SP for sure.

The last commands you pertorm there are unnecessary drop statements. Since you are using temporary tables, those will be discarded automatically.

Further you should put a
set nocount on
on top of your SP. Else you will get an additional record with the number of records retrieved.

With that changes you should be able to use ExecuteQuery again, I hope.
0
WineInstituteAuthor Commented:
When I run it I get an error: Service Error: com.microsoft.sqlserver.jdbc.SQLServerException: The result set has no current row.

I attached what is displayed when run on the server too.
Untitled-1.jpg
0
WineInstituteAuthor Commented:
ok, found missing brackets
while (rs.next()){ <-- Missing
 
//// 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")%>');

<% } <---Missing
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
On the server you get multiple result sets. You see two grids, the first  one is your data, the second one a zero in column "Return Value". That is puzzling me. I'm researching why.

Nevertheless, you should get the first record set, which is your data, so the code might be ok now.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I assume you have used a function, not a stored procedure, and the function is returning nothing, which is interpreted as 0. If I run just the SQL code, I get a single result set.

Do you think Object did not help you with the syntax corrections he provided? If he did, you should have honoured that by giving him some points.
0
WineInstituteAuthor Commented:
I thought I hit the multiple correct answers thing, objects should of got 1/2 or whatever it does when you click that. I may have not clicked it by mistake.
How do I get it fixed?
Objects did help (even though drawn out over 50 posts, maybe to get me to think a little more about it, rather than just giving me the answer, I don't know), so he should get some points.
0
WineInstituteAuthor Commented:
Ah, got it. I resubmitted and gave each 250. THanks for pointing out the correct way. :)
0
WineInstituteAuthor Commented:
ok, did it again.
0
ModalotEE ModeratorCommented:
Giving up on training, I will close the question myself.
0
WineInstituteAuthor Commented:
I did reselect the 2 answers, one from objects and other other from qlemo. Did they get the 250 each?
doesn't it auto close it after I hit submit?
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.