aspose file tools*
The moose likes Struts and the fly likes passing  variable into sql query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Frameworks » Struts
Bookmark "passing  variable into sql query" Watch "passing  variable into sql query" New topic
Author

passing variable into sql query

lee kris
Ranch Hand

Joined: Mar 30, 2005
Posts: 35
hi,
May be this is not the right place to ask but I think someone might have had this problem. I am getting a variable from a form (jsp page) and using this variable as part of my query.
here it is :
String lName = null;
...
then lName = (some value from a form);
...
String sqlString = "select * from personal where last_name =$lName";
how do I make sure that the lName in the sql statement is a variable?
Thanks guys,
Lee
Srilakshmi Vara
Ranch Hand

Joined: Jul 21, 2004
Posts: 169
Hi Lee,

write a jsp in which accept the value for the query put the target as the file(aaa.java) in which you run the query.

in aaa.java(this can be a action/servlet/http servlet)

accept the variable using servlet request or http servlet request

String aaa = request.getParameter("variable")
use aaa variable in your query and execute it.

I hope this helps you.

Srilakshmi
lee kris
Ranch Hand

Joined: Mar 30, 2005
Posts: 35
hi Srilakshmi,
Thank you for responding.
May be I did not explain my self clearly so accept my apologies. But the main part of my question is how do I use a variable in a mysql query? you said "....use aaa variable in your query and execute it." To make matters clear i am going to post a part of the java code here.



public static ArrayList getSearchList(String lName, DataSource dataSource)
{
PersonalVO person = null;
ArrayList searchRestult = new ArrayList();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{

String sqlString = "select * from personal where last_name = 'lName'";
//String sqlString = "select * from personal where last_name = 'Michael'";
conn = dataSource.getConnection();
pstmt = conn.prepareStatement(sqlString);
rs = pstmt.executeQuery();
System.err.println("The Search list result includes the following ...");
while (rs.next())
{
System.out.println("Inside the search while loop !\n");
person = new PersonalVO();
person.setSocSecNo(rs.getString(1))
.
.
.
.

person.setStableEmail(rs.getString(7));
searchRestult.add(person);

}
} catch (SQLException e)
{
System.err.println(e.getMessage());
} finally
{
if (rs != null)
{
try
{
rs.close();
} catch (SQLException sqle)
{
System.err.println(sqle.getMessage());
}
rs = null;
}
if (pstmt != null)
{
try
{
pstmt.close();
} catch (SQLException sqle)
{
System.err.println(sqle.getMessage());
}
pstmt = null;
}
if (conn != null)
{
try
{
conn.close();
} catch (SQLException sqle)
{
System.err.println(sqle.getMessage());
}
conn = null;
}
}
return searchRestult;
}



So you see whenever I use
String sqlString = "select * from personal where last_name = 'Michael'";

The code works perfecly but whenever I use the variable lName as in

String sqlString = "select * from personal where last_name = lName";
then the problem arises b/c lName is a variable. How do I pass as a variable so that the sql understands it?
lee
Craig Jackson
Ranch Hand

Joined: Mar 19, 2002
Posts: 405
There is a couple of options:

1.


or

2.
lee kris
Ranch Hand

Joined: Mar 30, 2005
Posts: 35
hi Craig Jackson,
Perfect. works perfectly now.
Thank you.

Lee
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: passing variable into sql query