[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-08-05
6
Medium Priority
?
300 Views
Last Modified: 2010-04-01
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
0
Comment
Question by:richardsimnett
  • 3
  • 3
6 Comments
 
LVL 16

Expert Comment

by:suprapto45
ID: 19636422
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
0
 

Author Comment

by:richardsimnett
ID: 19704666
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
0
 

Author Comment

by:richardsimnett
ID: 19705445
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>
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Accepted Solution

by:
suprapto45 earned 2000 total points
ID: 19744083
Sorry for any delay. I was extremely busy these few days.

Mmm.....not sure but try
<c:set var="userid" scope="session" value="${login.rows.id}"></c:set>

David
0
 

Author Comment

by:richardsimnett
ID: 19829470
Thanks man.. That was exactly what I needed.
0
 
LVL 16

Expert Comment

by:suprapto45
ID: 19830634
:),

Glad I could help.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I came across an unsolved Outlook issue and here is my solution.
Moore’s Law has proven itself time and time again since it was first introduced. So what’s next? Will Moore’s law continue to remain relevant, or will new technology take over and bring us the next big advancement in computing?
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question