Strange JSTL recordset result problem (Question)

[jeremy.wilson]

I have what I believe to be some rather basic JSTL code and it is returning somewhat unreadable MS SQL database results back to my pages.

Specifically, I cannot explain why the Question and or the Answer column is returning the values you see below. That is not the 'text' column type data contained in the MS SQL database.

Here is the resulting data:

AID Question Answer Category Product dtCreated
393 net.sourceforge.jtds.jdbc.ClobImpl@2e7ace net.sourceforge.jtds.jdbc.ClobImpl@180a6ff 1 7 2009-11-06 10:09:34.92

Here is the JSTL code I'm using to connect and return SQL results:

<%@ page session="false" %>
<%@ taglib prefix="cms" uri="dubdubdub.opencms.org/taglib/cms" %>

<%@ taglib prefix="sql" uri="dubdubdub.java.sun.com/jstl/sql" %>
<%@ taglib prefix="c" uri="dubdubdub.java.sun.com/jstl/core" %>

<sql:setDataSource
  var="dataSource"
  driver="net.sourceforge.jtds.jdbc.Driver"
  url="jdbc:jtds:sqlserver://MyDBServer.somedomain.com:1433/MyDB;"
  user="user"
  password="pwd"
/>

<sql:query var="db" dataSource="${dataSource}">SELECT AID, Question, Answer, Category, Product, dtCreated FROM KB_FAQ  WHERE AID = 393</sql:query>

<table class="supporttable" border="1">

<tr>
<%-- Get the column names for the header of the table --%>
  <c:forEach var="columnName" items="${db.columnNames}">
    <td class="top"><c:out value="${columnName}"/></th>
  </c:forEach>
  </tr>

  <%-- Get the value of each column while iterating over rows --%>
  <c:forEach var="row" items="${db.rowsByIndex}">
    <tr>
      <c:forEach var="column" items="${row}">
        <td><c:out value="${column}"/></td>
      </c:forEach>
  </tr>
  </c:forEach>

</table>
<a href="#top">Back to top</a>

[jeremy.wilson]

Can anyone shed some light on why these 'text' type columns are spitting back error messages?

[jeremy.wilson]

For that matter, is there any solution still using primarily JSTL, to convert the 'text' field values into something readable, or am I just better off to ask my DBA to convert all these 'text' fields into some seriously long varchar fields?

[Tolls]

Because they're Clobs. You would normally (in JDBC) use getInputStream() to read them, and stick the result into some other String (possibly). It's one of the many reasons I never do stuff like this in JSP pages. I know there's tags and all, but it's really not the place for it, IMO.

Since the stream can only be used while the resultset is open, and the resultset is closed in the sql:query tag itself, you can't access the data. There may be something in the sql taglib documentation that'll explain whether or not this is doable.

[jeremy.wilson]

Well, thankfully the change from 'text' type fields to 'varchar' resolved the problem. For the amount of trouble this ultimately took to figure out and resolve, coupled with feels to me (someone with very little Java background) like a clearly lacking SQL toolset, I don't expect I'll be doing a lot of connections to our SQL database with Java. Instead I think I will opt for PHP since my past experience with it, seems like a walk in the proverbial park compared to trying to do simple select statements with either JSTL or JSP scriptlets.

Tolls, thanks for your response btw.

[Tolls]

JSP is not PHP.
Which is why I said earlier that you would not do this in a JSP page, but in a proper Java class. If you're using JSPs then you ought to have a proper setup, with Servlets, business layers, and presentation (ie JSP) layers. JSPs are only for that last bit.

[jeremy.wilson]

JSP is not PHP.

That much is clear. I did not intend to imply otherwise. My personal opinion in bringing up PHP at all is simply that, I as a non object-oriented programmer type, have found PHP (having now used both) to be far easier to pick up and learn than I have found to be the case with Java, and everything that goes with it, eg. classes, servlets, debugging cryptic Java runtime errors.

Which is why I said earlier that you would not do this in a JSP page, but in a proper Java class.

Understood, another minor dilemma I am chewing on, primarily because of the OpenCMS layer sitting on our Debian Linux/Apache/Tomcat webserver. Truthfully I look at the use of JSTL as a crutch to help me avoid having to develop and implement classes.