• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Having trouble with SELECT statement is JSP

 
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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");
}
%>
 
Ranch Hand
Posts: 2166
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
yeah I fixed it... it had to use alias on my sql statement..
 
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
reply
    Bookmark Topic Watch Topic
  • New Topic