Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Having trouble with SELECT statement is JSP

 
Rich Barry
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2166
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yeah I fixed it... it had to use alias on my sql statement..
 
martin samm
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic