permaculture playing cards*
The moose likes JDBC and the fly likes Trouble with retrieving dates after I create a column alias Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Trouble with retrieving dates after I create a column alias" Watch "Trouble with retrieving dates after I create a column alias" New topic
Author

Trouble with retrieving dates after I create a column alias

Scott Updike
Ranch Hand

Joined: Feb 16, 2006
Posts: 92
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
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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

Joined: Feb 16, 2006
Posts: 92
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
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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><cut ...></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

Joined: Feb 16, 2006
Posts: 92
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

Joined: Feb 16, 2006
Posts: 92
Please ignore my last post. I now know what you're saying ....
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Trouble with retrieving dates after I create a column alias