How to get sql query from text area and put it inside <sql:query> tag using jstl

Hi All,
I am using JSTL to query DB. All the values for connecting to db I am getting though a JSP which uses Form and on submit it forwards to the next page where I have a textarea where I want users be able to put there sql query and get the output on same page.

The problem is I am able to get all values from page 1 input.jsp and able to query DB in the next page output.jsp if i hardcode some query in between sql:query tags. I am not able to get how to get the sql query from the textarea form not hardcode it. So that some user can put his query in textbox and onclick submmit he will get the output on same page.

How to achive this please help!!
I am putting my sample code.
input.jsp
--------------------------------------------------------------------------------
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"  %>
<%@taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@page contentType="text/html; charset=iso-8859-1"%>
<%@page import="java.sql.*,java.io.*,java.text.*,java.util.*" %>
<html>
    <head>
           </head>
    <body>
        <form name="f1" method="post" action="output.jsp">
            <table width="100%" border="0" cellspacing="2" cellpadding="2">
                <tr>
                    <th align="right">
                        <font color="#000000"><label for="username">User Name</label></font>
                    </th>
                    <td align="left">
                        <input type="text" name="t11" size="16" id="username" />
                    </td>
                </tr>
                <p>
                <tr>
                    <th align="right">
                        <font color="#000000"><label for="password">Password</label></font>
                    </th>
                    <td align="left">
                        <input type="password" name="t22" size="16" id="password"/>
                    </td>
                </tr>
                <p>
                <tr>
                    <th align="right">
                        <font color="#000000"><label for="dbsid">DB SID</label></font>
                    </th>
                    <td align="left">
                        <input type="text" name="t33" size="16" id="dbsid" />
                    </td>
                </tr>
                <tr>
                <p>
                <tr>
                    <th align="right">
                        <font color="#000000"><label for="lport">Listener Port</label></font>
                    </th>
                    <td align="left">
                        <input type="text" name="t44" size="16" id="lport"  value="1522"/>
                    </td>
                </tr>
                <tr>
                <p>
                <tr>
                    <th align="right">
                        <font color="#000000"><label for="dbhost">DB Host Name</label></font>
                    </th>
                    <td align="left">
                        <input type="text" name="t55" size="16" id="dbhost" />
                    </td>
                </tr>
                <tr>
                    <p>
                    <td width="50%" valign="top"> <div align="right"></div> </td>
                    <td width="55%" valign="top">&nbsp;</td>
                </tr>
 
                <!-- login reset buttons layout -->
                <tr>
                    <td width="50%" valign="top">
                        <div align="right">
                            <input type="submit" name="b1" value='Submit'>&nbsp;
                        </div>
                    </td>
                    <td width="55%" valign="top">
                        &nbsp;<input type="reset" value='Reset'>
                    </td>
                </tr>
            </table>
        </form>
        <c:set  var="t11" value="${param.t11}" />
        <c:set  var="t22" value="${param.t22}" />
        <c:set  var="dburl" value="jdbc:oracle:thin:@//${param.t55}:${param.t44}:${param.t33}" />
        <c:set  var="t66" value="${param.t66}" />
</body>
</html>
-----------------------------------------
output.jsp
----------------------------------------
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%>
<%@page import="java.sql.*,java.io.*,java.text.*,java.util.*" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    </head>
    <body>
        <form name="f1" method="post" action="output.jsp">
            <table width="100%" border="0" cellspacing="2" cellpadding="2">
                <tr>
                    <th align="right">
                        <td>
                            <textarea cols="80"  name="t66"  rows="8"  wrap="off"></textarea>
                        </td>
                    </th>
                </tr>
                <!-- login reset buttons layout -->
                <tr>
                <td width="50%" valign="top">
                    <div align="right">
                        <input type="submit" name="b1" value='Submit'>&nbsp;
                    </div>
                </td>
                <td width="55%" valign="top">
                    &nbsp;<input type="reset" value='Reset'>
                </td>
            </table>
        </form>
        <c:set var="t66" value="${param.t66}" />
        <hr>
        <sql:setDataSource var="ldb" scope="session"
                           driver="oracle.jdbc.OracleDriver"
                           url="jdbc:oracle:thin:@//${param.t55}:${param.t44}/${param.t33}" user="${param.t11}"           password="${param.t22}" />
        <sql:query var="getresult" scope="application" dataSource="${ldb}">select DB_NAME FROM APPS_T2
<!- Here I want the code should come from the textarea form -->
        </sql:query>
        <table border="1">
            <!-- column headers -->
            <tr>
                <c:forEach var="columnName" items="${getresult.columnNames}">
                    <th><c:out value="${columnName}"/></th>
                </c:forEach>
            </tr>
            <!-- column data -->
            <c:forEach var="row" items="${getresult.rowsByIndex}">
                <tr>
                    <c:forEach var="column" items="${row}">
                        <td><c:out value="${column}"/></td>
                    </c:forEach>
                </tr>
            </c:forEach>
        </table>
    </body>
</html>

Open in new window

LVL 4
Ramakanta SahooAsked:
Who is Participating?
 
Gibu GeorgeChief Technology OfficerCommented:
like this
<input type="hidden" name="t11" value='<c:out value="${param.t11}"/>' />
0
 
KuldeepchaturvediCommented:

<c:set var="qry1" value="${param.t66}" />
<c:set var="temp" value="select DB_NAME FROM APPS_T2 "/>
<c:set var="qry" value=${temp}${qry1}"/>
<sql:setDataSource var="ldb" scope="session"
                           driver="oracle.jdbc.OracleDriver"
                           url="jdbc:oracle:thin:@//${param.t55}:${param.t44}/${param.t33}" user="${param.t11}"           password="${param.t22}" />
        <sql:query var="getresult" scope="application" dataSource="${ldb}" sql=${qry}">
 
        </sql:query>

Open in new window

0
 
Ramakanta SahooAuthor Commented:
Hi Kuldeep Thanks for the help.

but This is not what i want
I want the user to put the total select query in the textarea and should be able to get output on same page.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Gibu GeorgeChief Technology OfficerCommented:
You onlu need to have a single jsp, which takes the db parameters as well as the query, add the setdatasources tag and output in the same form adding a condition to check if none of the parameters and query are not null. Something like this
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"  %>
<%@taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@page contentType="text/html; charset=iso-8859-1"%>
<%@page import="java.sql.*,java.io.*,java.text.*,java.util.*" %>
<html>
    <head>
           </head>
    <body>
        <form name="f1" method="post" action="input.jsp">
            <table width="100%" border="0" cellspacing="2" cellpadding="2">
                <tr>
                    <th align="right">
                        <font color="#000000"><label for="username">User Name</label></font>
                    </th>
                    <td align="left">
                        <input type="text" name="t11" size="16" id="username" value='<c:value-of select="${param.t11}"/>' />
                    </td>
                </tr>
                <p>
                <tr>
                    <th align="right">
                        <font color="#000000"><label for="password">Password</label></font>
                    </th>
                    <td align="left">
                        <input type="password" name="t22" size="16" id="password" value='<c:value-of select="${param.t22}"/>'/>
                    </td>
                </tr>
                <p>
                <tr>
                    <th align="right">
                        <font color="#000000"><label for="dbsid">DB SID</label></font>
                    </th>
                    <td align="left">
                        <input type="text" name="t33" size="16" id="dbsid" value='<c:value-of select="${param.t33}"/>' />
                    </td>
                </tr>
                <tr>
                <p>
                <tr>
                    <th align="right">
                        <font color="#000000"><label for="lport">Listener Port</label></font>
                    </th>
                    <td align="left">
                        <input type="text" name="t44" size="16" id="lport"  value="1522"/>
                    </td>
                </tr>
                <tr>
                <p>
                <tr>
                    <th align="right">
                        <font color="#000000"><label for="dbhost">DB Host Name</label></font>
                    </th>
                    <td align="left">
                        <input type="text" name="t55" size="16" id="dbhost" value='<c:value-of select="${param.t55}"/>' />
                    </td>
                </tr>
                <tr>
                    <p>
                    <td width="50%" valign="top"> <div align="right"></div> </td>
                    <td width="55%" valign="top"> </td>
                </tr>
				<tr>
                    <th align="right">
                        <td>
                            <textarea cols="80"  name="t66"  rows="8"  wrap="off" value='<c:value-of select="${param.t66}"/>'></textarea>
                        </td>
                    </th>
                </tr>
 
                <!-- login reset buttons layout -->
                <tr>
                    <td width="50%" valign="top">
                        <div align="right">
                            <input type="submit" name="b1" value='Submit'> 
                        </div>
                    </td>
                    <td width="55%" valign="top">
                         <input type="reset" value='Reset'>
                    </td>
                </tr>
            </table>
			<c:if test="${param.t66!=null}">
			<sql:setDataSource var="ldb" scope="session"
                           driver="oracle.jdbc.OracleDriver"
                           url="jdbc:oracle:thin:@//${param.t55}:${param.t44}/${param.t33}" user="${param.t11}"           password="${param.t22}" />
        <sql:query var="getresult" scope="application" dataSource="${ldb}">
			<c:value-of select="${param.t66}"/>
        </sql:query>
        <table border="1">
            <!-- column headers -->
            <tr>
                <c:forEach var="columnName" items="${getresult.columnNames}">
                    <th><c:out value="${columnName}"/></th>
                </c:forEach>
            </tr>
            <!-- column data -->
            <c:forEach var="row" items="${getresult.rowsByIndex}">
                <tr>
                    <c:forEach var="column" items="${row}">
                        <td><c:out value="${column}"/></td>
                    </c:forEach>
                </tr>
            </c:forEach>
        </table>
		</c:if>
        </form>
       
</body>
</html>
-----------------------------------------
output.jsp
----------------------------------------
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%>
<%@page import="java.sql.*,java.io.*,java.text.*,java.util.*" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    </head>
    <body>
        <form name="f1" method="post" action="output.jsp">
            <table width="100%" border="0" cellspacing="2" cellpadding="2">
                <tr>
                    <th align="right">
                        <td>
                            <textarea cols="80"  name="t66"  rows="8"  wrap="off"></textarea>
                        </td>
                    </th>
                </tr>
                <!-- login reset buttons layout -->
                <tr>
                <td width="50%" valign="top">
                    <div align="right">
                        <input type="submit" name="b1" value='Submit'> 
                    </div>
                </td>
                <td width="55%" valign="top">
                     <input type="reset" value='Reset'>
                </td>
            </table>
        </form>
        <c:set var="t66" value="${param.t66}" />
        <hr>
        <sql:setDataSource var="ldb" scope="session"
                           driver="oracle.jdbc.OracleDriver"
                           url="jdbc:oracle:thin:@//${param.t55}:${param.t44}/${param.t33}" user="${param.t11}"           password="${param.t22}" />
        <sql:query var="getresult" scope="application" dataSource="${ldb}">select DB_NAME FROM APPS_T2
<!- Here I want the code should come from the textarea form -->
        </sql:query>
        <table border="1">
            <!-- column headers -->
            <tr>
                <c:forEach var="columnName" items="${getresult.columnNames}">
                    <th><c:out value="${columnName}"/></th>
                </c:forEach>
            </tr>
            <!-- column data -->
            <c:forEach var="row" items="${getresult.rowsByIndex}">
                <tr>
                    <c:forEach var="column" items="${row}">
                        <td><c:out value="${column}"/></td>
                    </c:forEach>
                </tr>
            </c:forEach>
        </table>
    </body>
</html>

Open in new window

0
 
KuldeepchaturvediCommented:
I thought you wanted to append the string that you had in code.. otherwise it can be as simple as

<sql:query var="getresult" scope="application" dataSource="${ldb}" sql=${param.t66}">
0
 
Gibu GeorgeChief Technology OfficerCommented:
Else you can move the

<sql:setDataSource var="ldb" scope="session"
                           driver="oracle.jdbc.OracleDriver"
                           url="jdbc:oracle:thin:@//${param.t55}:${param.t44}/${param.t33}" user="${param.t11}"           password="${param.t22}" />
        <sql:query var="getresult" scope="application" dataSource="${ldb}">
                  <c:value-of select="${param.t66}"/>
        </sql:query>
        <table border="1">
            <!-- column headers -->
            <tr>
                <c:forEach var="columnName" items="${getresult.columnNames}">
                    <th><c:out value="${columnName}"/></th>
                </c:forEach>
            </tr>
            <!-- column data -->
            <c:forEach var="row" items="${getresult.rowsByIndex}">
                <tr>
                    <c:forEach var="column" items="${row}">
                        <td><c:out value="${column}"/></td>
                    </c:forEach>
                </tr>
            </c:forEach>
        </table>

to the second page and submit it to first page
0
 
Gibu GeorgeChief Technology OfficerCommented:
else if you want to keep the same structure as your on two jsp, you need to keep all the fields taken in the first page as hidden parameters in the second page and store it when you do the first submit. then enter the query in the second page and you have to submit again to the same page to get the output
0
 
Ramakanta SahooAuthor Commented:
Hi All,

I tried but it gave me below error.

Gibu thanks for suggestion but i dont want like that for that only i splitted it into two so that a user will enter details of db to login and then he will be forwarded to the next page where he can fire sql query statements to the db and get the output on same page.

<c:set var="qry1" value="${param.t66}" />
<c:set var="qry" value="${qry1}"/>
<c:if test="${qry != ''"}>
<sql:setDataSource var="ldb" scope="session"
                           driver="oracle.jdbc.OracleDriver"
                           url="jdbc:oracle:thin:@//${param.t55}:${param.t44}/${param.t33}" user="${param.t11}"           password="${param.t22}" />
        <sql:query var="getresult" scope="application" dataSource="${ldb}" sql="${param.t66}" />
 
----
------
------
</c:if>
 
 
ERROR:
 
javax.servlet.jsp.JspTagException: No SQL statement specified
	org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doEndTag(QueryTagSupport.java:193)
	org.apache.jsp.getquery_jsp._jspx_meth_sql_query_0(getquery_jsp.java:342)
	org.apache.jsp.getquery_jsp._jspx_meth_c_if_0(getquery_jsp.java:272)
	org.apache.jsp.getquery_jsp._jspService(getquery_jsp.java:139)

Open in new window

0
 
Gibu GeorgeChief Technology OfficerCommented:
this will be  null as the  t66 text area is in the second page. and is not in the first one
0
 
Gibu GeorgeChief Technology OfficerCommented:
If you want it s two pages, you have to follow my 3rd comment
0
 
Ramakanta SahooAuthor Commented:
Yes that I know but how to avoid the error .
Because when a user is forwarded to next page the text area will be blank only.

And how i will be able to keep the parameters given by the user in 1st page in the 2nd page also as long as the user fires different select queries.

Can it be dynamic  ??
0
 
KuldeepchaturvediCommented:
you can move your t66 in session and then fetch it back on page 2 if you want to work it that way.
0
 
Gibu GeorgeChief Technology OfficerCommented:
for the check my third comment
>>
keep all the fields taken in the first page as hidden parameters in the second page and store it when you do the first submit
0
 
Ramakanta SahooAuthor Commented:
I didnt get you.

t66 is in the second page only.(i want it in 2nd page only)

all other are in page 1

0
 
Ramakanta SahooAuthor Commented:
>>keep all the fields taken in the first page as hidden parameters in the second page and store it when you do the first submit<<

How TO??

No Idea ?? :(
0
 
Gibu GeorgeChief Technology OfficerCommented:
and then you need to submit the second page to itself and get ${param.t66} to get the query and use it in the datasource
0
 
Ramakanta SahooAuthor Commented:
Ok I will try and let you know . Need to go home for now
0
 
Ramakanta SahooAuthor Commented:
Thank You All for the help extended its working now. :)

Before awarding points can some body please tell me how to disable null input in page1
so that no user can submit the page1 form with out filling the details.
I have care of exceptions by using c:catch tag in page 2.
0
 
KuldeepchaturvediCommented:
you can put in a javascript at 'onsubmit" of your form and check that the values are filled..

alternatively you can code the server side validations in your output.jsp to check for nulls.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.