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

Having trouble with SELECT statement is JSP

Rich Barry

Joined: Jan 28, 2002
Posts: 19
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
/* 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
as there seems to be some error in the SQL (I don't see it) you might insert a line
before the
rs = d.SQLQuery(sql);
Rich Barry

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