Newbie question: How to pass a value from a text box to a query and return the result.
Hi, I need some help with the basic syntax for retrieving user input, embedding it in a query, passing it to the DB (Oracle) and displaying the result.
I have provided some sample code here, and have been thinking along the lines of doing a postback and parsing the query string into the query. I am not sure if this can be done at all. I know the embedding of the query here is not the best method, at least there should be a stored procedure, so if anyone want to provide me with a better method please feel free.
<sql:transaction dataSource="${conn}">
<sql:query var="available_offer">
select distinct City, to_char(Date, 'dd.mm.yyyy')
from Offer
where Offer.Date = // The value from the text box //
and film.film_id = forestilling.film_id
order by Dato
</sql:query>
</sql:transaction>
<h1>Offer</h1>
<table border="1">
<%-- Get the column names for the header of the table --%>
<c:forEach var="columnName" items="${available_offers.
<th><c:out value="${columnName}"/></t
</c:forEach>
<%-- Get the value of each column while iterating over rows --%>
<c:forEach var="row" items="${available_offers.
<tr>
<c:forEach var="column" items="${row}">
<td><c:out value="${column.value}"/><
</c:forEach>
</tr>
</c:forEach>
</table>
<br />
<form method="post" action="index2.jsp?date=">
Velg dato: <input type="text" name="date" size="25">
<input type="submit" name="send" value="Send">
<br />
I have provided some sample code here, and have been thinking along the lines of doing a postback and parsing the query string into the query. I am not sure if this can be done at all. I know the embedding of the query here is not the best method, at least there should be a stored procedure, so if anyone want to provide me with a better method please feel free.
<sql:transaction dataSource="${conn}">
<sql:query var="available_offer">
select distinct City, to_char(Date, 'dd.mm.yyyy')
from Offer
where Offer.Date = // The value from the text box //
and film.film_id = forestilling.film_id
order by Dato
</sql:query>
</sql:transaction>
<h1>Offer</h1>
<table border="1">
<%-- Get the column names for the header of the table --%>
<c:forEach var="columnName" items="${available_offers.
<th><c:out value="${columnName}"/></t
</c:forEach>
<%-- Get the value of each column while iterating over rows --%>
<c:forEach var="row" items="${available_offers.
<tr>
<c:forEach var="column" items="${row}">
<td><c:out value="${column.value}"/><
</c:forEach>
</tr>
</c:forEach>
</table>
<br />
<form method="post" action="index2.jsp?date=">
Velg dato: <input type="text" name="date" size="25">
<input type="submit" name="send" value="Send">
<br />
Better yet, use a prepared statement
<sql:transaction dataSource="${conn}">
<sql:query var="available_offer">
select distinct City, to_char(Date, 'dd.mm.yyyy') from Offer where Offer.Date = ? and film.film_id = forestilling.film_id order by Dato
<sql:param value='${param.date}'/>
</sql:query>
NB both assume that the name of the parameter from the form (i.e. the name of the text box) is "date"
<sql:transaction dataSource="${conn}">
<sql:query var="available_offer">
select distinct City, to_char(Date, 'dd.mm.yyyy') from Offer where Offer.Date = ? and film.film_id = forestilling.film_id order by Dato
<sql:param value='${param.date}'/>
</sql:query>
NB both assume that the name of the parameter from the form (i.e. the name of the text box) is "date"
ASKER
Yes, it should be date, bad translation on my behalf from norwegian.
But, should I not use a postback? What should the action attribute in the form tag be?
But, should I not use a postback? What should the action attribute in the form tag be?
<form method="post" action=""> will post back to the original page
e.g.
<form method="post" action="index2.jsp"> will post to the index2.jsp page. You don't need to add the parameter name in the action as it's done for you.
e.g.
<form method="post" action="index2.jsp"> will post to the index2.jsp page. You don't need to add the parameter name in the action as it's done for you.
>>But, should I not use a postback?
It's up to you. You can do error checking on this page and check for when there is no parameter if it breaks your pages.
<c:if test='${empty param.date}' >
this will only be executed if there IS NO request parameter called date
</c:if>
<c:if test='${not empty param.date}' >
this will only be executed if there IS request parameter called date <c:out value='${param.date}'/>
</c:if>
You can also use a c:choose statement for if/else.....
It's up to you. You can do error checking on this page and check for when there is no parameter if it breaks your pages.
<c:if test='${empty param.date}' >
this will only be executed if there IS NO request parameter called date
</c:if>
<c:if test='${not empty param.date}' >
this will only be executed if there IS request parameter called date <c:out value='${param.date}'/>
</c:if>
You can also use a c:choose statement for if/else.....
ASKER
OK, looks great, I think I understand it, but: when I include the <sql:param value='${param.date}'/> in the query tag I get a "illegal column type" error. (I run this on Tomcat). I suppose the question mark indicates the parameter input. If more than one are they dealt with consecutively?
I've tried to put the query block in the if statement, without any luck.
S
I've tried to put the query block in the if statement, without any luck.
S
Probably as you are trying to enter a NULL into a Date type.
You haven't specified it but I should have guessed earlier that the coumn type is a "Date". In that case you need <sql:dateParam> rather than <sql:param> and also need to format the date using the fmt lib (which you need to include the taglib declaration):
-----------
<sql:transaction dataSource="${conn}">
<c:if test='${not empty param.date}' >
<fmt:parseDate var='javaDate' value='${param.date}' dateStyle='short' />
<sql:query var="available_offer">
select distinct City, to_char(Date, 'dd.mm.yyyy') from Offer where Offer.Date = ? and film.film_id = forestilling.film_id order by Dato
<sql:dateParam value='${javaDate}'/>
</sql:query>
</c:if>
You haven't specified it but I should have guessed earlier that the coumn type is a "Date". In that case you need <sql:dateParam> rather than <sql:param> and also need to format the date using the fmt lib (which you need to include the taglib declaration):
-----------
<sql:transaction dataSource="${conn}">
<c:if test='${not empty param.date}' >
<fmt:parseDate var='javaDate' value='${param.date}' dateStyle='short' />
<sql:query var="available_offer">
select distinct City, to_char(Date, 'dd.mm.yyyy') from Offer where Offer.Date = ? and film.film_id = forestilling.film_id order by Dato
<sql:dateParam value='${javaDate}'/>
</sql:query>
</c:if>
ASKER
Well, I see about the date format, but I've tried a very simple query:
<sql:transaction dataSource="${conn}">
<c:if test='${empty param.City}'>
<sql:query var="choose_city">
SELECT * FROM City WHERE City = ?
<sql:param value="${param.City}" />
</sql:query>
</c:if>
</sql:transaction>
<table border="1">
<%-- Get the column names for the header of the table --%>
<c:forEach var="columnName" items="${choose_city.colum
<th><c:out value="${columnName}"/></t
</c:forEach>
<%-- Get the value of each column while iterating over rows --%>
<c:forEach var="row" items="${choose_city.rows}
<tr>
<c:forEach var="column" items="${row}">
<td><c:out value="${column.value}"/><
</c:forEach>
</tr>
</c:forEach>
</table>
<br />
<form method="post" action="index2.jsp">
Velg by: <input type="text" name="City" size="25" value="City"><br />
<input type="submit" name="send" value="Send">
</form>
I get the same error. Can you run this?
Thanks
S
<sql:transaction dataSource="${conn}">
<c:if test='${empty param.City}'>
<sql:query var="choose_city">
SELECT * FROM City WHERE City = ?
<sql:param value="${param.City}" />
</sql:query>
</c:if>
</sql:transaction>
<table border="1">
<%-- Get the column names for the header of the table --%>
<c:forEach var="columnName" items="${choose_city.colum
<th><c:out value="${columnName}"/></t
</c:forEach>
<%-- Get the value of each column while iterating over rows --%>
<c:forEach var="row" items="${choose_city.rows}
<tr>
<c:forEach var="column" items="${row}">
<td><c:out value="${column.value}"/><
</c:forEach>
</tr>
</c:forEach>
</table>
<br />
<form method="post" action="index2.jsp">
Velg by: <input type="text" name="City" size="25" value="City"><br />
<input type="submit" name="send" value="Send">
</form>
I get the same error. Can you run this?
Thanks
S
ASKER
I have these taglibs included now, do I need more?
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I see, thanks!
Cool, glad to help. Thanks for the points and the grade :-)
ASKER
OK, the answer is great, but it only works if I post it to another page. Should it also work when I post it to the same page?
<sql:query var="available_offer">
select distinct City, to_char(Date, 'dd.mm.yyyy')
from Offer
where Offer.Date = <c:out value='${param.date}'/>
and film.film_id = forestilling.film_id
order by Dato
</sql:query>