• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

passing variable into sql query

 
lee kris
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 169
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 405
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There is a couple of options:

1.


or

2.
 
lee kris
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi Craig Jackson,
Perfect. works perfectly now.
Thank you.

Lee
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic