asked on

How do I easily access just the first row of data returned by a SQL Query?

Hello,
I am learning JSP, and have a question about how to retrieve data from a SQL Result object. Here is what I've got:

<sql:query var="updateDB" dataSource="${ds}">
select url from campaigns where id = <%= campaignid %>;
</sql:query>

<c:forEach var="row" items="${updateDB.rows}">
URL:${row.url}<br>
<c:redirect url="${row.url}"></c:redirect>
</c:forEach>

Is there a more direct way to grab lets just say the first row of data than to do a loop? How do I do that? Please provide an example.

Worth 500 points.

Thanks,
Rick

suprapto45

Mm....

You only want to return the first row?

<sql:query var="updateDB" dataSource="${ds}" maxRows="1">
select url from campaigns where id = <%= campaignid %>;
</sql:query>

Try that...

David

richardsimnett

ASKER

sorry about the question being left unattended for a bit...
no, what I want to do is know how to access a single row from a query in a single statement as opposed to a for loop... also how do you tell how many rows there are in the result set in a statement?

Thanks,
Rick

richardsimnett

ASKER

here's a perfect example, I retrieved the query result set, which should only ever return 1 result, I test for presence of 1 row, then if its there, I try to access the resulting id column value as login.id but it fails... How do I retrieve just that value in a manner like ${login.id}?

<%-- manage a login attempt --%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>

<h1>Login TAG</h1>
<%-- set up the datasource --%>
<sql:setDataSource var="dblogin" url="jdbc:mysql://192.168.1.4/pdev" driver="com.mysql.jdbc.Driver" user="pdev" password="pdev2k7"></sql:setDataSource>

<%-- first query the database for the username and password --%>
<sql:query var="login" dataSource="${dblogin}">
select id from users where username='${param.username}' and password='${param.password}'
</sql:query>

RowCount: ${login.rowCount}<br>
<%-- now determine if the record count is == 1 --%>
<c:choose>
<c:when test="${login.rowCount == 1}">
<c:set var="userid" scope="session" value="${login.id}"></c:set>
</c:when>
<c:otherwise>
<c:set var="userid" scope="session" value="-1"></c:set>
</c:otherwise>
</c:choose>
SessionID:<c:out value="${sessionScope.userid}"/><br>

ASKER CERTIFIED SOLUTION

suprapto45

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

richardsimnett

ASKER

Thanks man.. That was exactly what I needed.

suprapto45

:),

Glad I could help.