• 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

Trouble with retrieving dates after I create a column alias

 
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have the following statement in my servlet that just retrieves three columns from a very simple table (MySQL) where I alias the column names so I can use the column aliases as HTML table headers. The result set is sent to my jsp file.

--Servlet code snippet

PreparedStatement stmtComm = conn1.prepareStatement("select comment_date 'Comment Date', user 'User', comment 'Comment' from issue_comments where issue_name = (?)");
stmtComm.setString(1,vvalue);
ResultSet rsltComment = stmtComm.executeQuery();
Result resultComment = ResultSupport.toResult(rsltComment);
request.setAttribute("resultComms",resultComment);


The request gets created without any issues, but once I try and view the results, I see the correct aliased column headers in my JSP, but the date field is blank (all other data is displayed). When I don't alias the columns in my query (and use the actual column names) then the date field displays. I can't seem to get the column alias and the date information to print out in the jsp file. Below is my jsp code:

<c:choose>
<c:when test="${resultComms.rowCount ne 0}" >

<table border=1>
<tr>
<c:forEach var="hl" items="${resultComms.columnNames}">
<th><c ut value="${hl}"/></th>
</c:forEach>
</tr>
<c:forEach var="el" items="${resultComms.rows}">
<tr><TD>
<b><c ut value="${el.comment_date}"/></b>
</TD><TD>
<c ut value="${el.user}"/>
</td><td>
<c ut value="${el.comment}"/>
</td></tr>
</c:forEach>
</table>
</c:when>
<c therwise>
<center><b>There are no comments recorded for this issue.</b></center>
</c therwise>
</c:choose>

My theory is that since I aliased my comment_date column to 'Comment Date', then the expression <c ut value="${el.comment_date}"/> is no longer valid - since the column name returned in the Result object is called 'Comment Date' and not 'comment_date'. Of course, this doesn't explain why the other columns printed out...

Cn anyone provide any resources to help me with this issue?

My bare-bones environment: WinXP, MySQL, Tomcat 5, Servlet 2.4, JSP 2.0, JSTL 1.1

Thanks in advance,
Scott
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
For the columns that did print out, the alias is the same as the column name except for case. And generally SQL databases don't treat column names in a case-sensitive way, so I think that's why the code still works. I don't see the point of using aliases in the situation you describe so I wouldn't use them.
 
Scott Updike
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
My desire is to create column headers that mimic the column names in the database, and in this case, the exact column name has underscores. The JSP is desgined so that if I ever tailor my query to return more (or less rows) then my JSP does not need to be changed. Is there another way to do this?

Thanks again!
 
Paul Clapham
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Your JSP can display whatever you choose for column headings. You don't have to use the column names from your SQL table. If you were writing a generic SQL table displayer in JSP then I could understand your point, but you aren't. If you can hard-codefor an entry in a certain column of the table I don't see why you can't hard-code "Comment Date" in your JSP as the heading of that column.

The number of rows has nothing to do with anything. If you meant you wanted to reduce the maintenance involved in adding a new column to the query, you still have to insert a new <td><c:out ...></td> entry in the JSP, so what's the problem with inserting a new table heading entry at the same time?
 
Scott Updike
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Actually the JSP code block is inside another <c:forEach> block (that's where the "el" in "${el.comment_date}" came into play). Sorry I forgot to include that outer loop.
So basically, the JSP can automatically generate the <td><c:...></td> statements according to the number of columns returned in my query. So the query result set dictates what's displayed. I just need to figure out how to tie alias with column value.
 
Scott Updike
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Please ignore my last post. I now know what you're saying ....
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic