*
The moose likes JSP and the fly likes Having trouble with SELECT statement is JSP Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » JSP
Bookmark "Having trouble with SELECT statement is JSP" Watch "Having trouble with SELECT statement is JSP" New topic
Author

Having trouble with SELECT statement is JSP

Rich Barry
Greenhorn

Joined: Jan 28, 2002
Posts: 19
Hi,
I can seem to get this SELECT statement to work in the JSP... but it works fine in QUERY ANALYSER on SQL server, and it work work in JSP code... and I don't understand why it is doing that...
Here is the error:
javax.servlet.ServletException: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'select'.
I know that it is the second SELECT it does not like...
Here is the code:
<%
// Request the information from the url
String catid = request.getParameter("catid");
// Create a new instance of the DataAccess Class
DataAccess d = new DataAccess();
// This call the database connection method from the DataAccess Class
d.ConnectDatabase();
/* This gets the threads from database for the categories */
// This is initilzing the variables
String threadname = null;
String handle = "";
String messagedate = "";
String threaddate = "";
int threadid = 0;
int recordcount = 0;
// This sets the sql variable to nothing
String sql = "";
// This sets the rs variable to null
ResultSet rs = null;
// This is doing a SELECT on the thread table based on the category id
sql += "select max(m.fm_date) as date, count(m.fm_messageid) as rcount,";
sql += "ft.threadname as threadname, ft.handle, ft.threadid, ft.threaddate";
sql += "from (select t.ft_threadname 'threadname', p.fp_handle 'handle'";
sql += "t.ft_threadid 'threadid', t.ft_threaddate 'threaddate'";
sql += "from forum_threads t, forum_profile p";
sql += "where t.ft_profileid = p.fp_profileid";
sql += "and t.ft_catid = 2) as ft";
sql += "left join forum_messages m on m.fm_threadid = ft.threadid";
sql += "group by ft.threadid, ft.threadname, ft.handle, ft.threaddate";
rs = d.SQLQuery(sql);
/* End of categories */
%>
<%
while (rs.next()){
messagedate = rs.getString("date");
recordcount = rs.getInt("rcount");
threadname = rs.getString("threadname");
handle = rs.getString("ft.handle");
threadid = rs.getInt("ft.threadid");
threaddate = rs.getString("ft.threaddate");
}
%>
Axel Janssen
Ranch Hand

Joined: Jan 08, 2001
Posts: 2164
Rich,
as there seems to be some error in the SQL (I don't see it) you might insert a line
out.println(sql);
System.out.println(sql);
before the
rs = d.SQLQuery(sql);
Rich Barry
Greenhorn

Joined: Jan 28, 2002
Posts: 19
yeah I fixed it... it had to use alias on my sql statement..
martin samm
Ranch Hand

Joined: Nov 09, 2000
Posts: 31
make sure you have spaces at the end of the lines, so that your clauses, like 'where' are not joined to the end of the previous line.


martin samm<BR>m_sam@rroom.net
 
 
subject: Having trouble with SELECT statement is JSP