[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-04-29
19
Medium Priority
?
1,420 Views
Last Modified: 2013-11-24
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

0
Comment
Question by:Ramakanta Sahoo
  • 8
  • 7
  • 4
19 Comments
 
LVL 19

Assisted Solution

by:Kuldeepchaturvedi
Kuldeepchaturvedi earned 180 total points
ID: 24267190

<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
 
LVL 4

Author Comment

by:Ramakanta Sahoo
ID: 24269272
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
 
LVL 12

Expert Comment

by:Gibu George
ID: 24269304
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 24269321
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
 
LVL 12

Expert Comment

by:Gibu George
ID: 24269355
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
 
LVL 12

Expert Comment

by:Gibu George
ID: 24269380
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
 
LVL 4

Author Comment

by:Ramakanta Sahoo
ID: 24269446
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
 
LVL 12

Expert Comment

by:Gibu George
ID: 24269490
this will be  null as the  t66 text area is in the second page. and is not in the first one
0
 
LVL 12

Expert Comment

by:Gibu George
ID: 24269499
If you want it s two pages, you have to follow my 3rd comment
0
 
LVL 4

Author Comment

by:Ramakanta Sahoo
ID: 24269525
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
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 24269573
you can move your t66 in session and then fetch it back on page 2 if you want to work it that way.
0
 
LVL 12

Expert Comment

by:Gibu George
ID: 24269629
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
 
LVL 4

Author Comment

by:Ramakanta Sahoo
ID: 24269643
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
 
LVL 4

Author Comment

by:Ramakanta Sahoo
ID: 24269665
>>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
 
LVL 12

Accepted Solution

by:
Gibu George earned 320 total points
ID: 24269715
like this
<input type="hidden" name="t11" value='<c:out value="${param.t11}"/>' />
0
 
LVL 12

Expert Comment

by:Gibu George
ID: 24269723
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
 
LVL 4

Author Comment

by:Ramakanta Sahoo
ID: 24270046
Ok I will try and let you know . Need to go home for now
0
 
LVL 4

Author Comment

by:Ramakanta Sahoo
ID: 24274189
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
 
LVL 19

Assisted Solution

by:Kuldeepchaturvedi
Kuldeepchaturvedi earned 180 total points
ID: 24276654
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
The viewer will learn how to implement Singleton Design Pattern in Java.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

834 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