• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to insert variable value in select?

 
bob connolly
Ranch Hand
Posts: 204
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello!

I'm trying to insert a variable value in a SELECT statement, "cat", and having a real time of it!

I tried the "preparedstatement" approach, but it returned multiple copies of the result set, ie many repetitiions of the same data were returned, but when i use the "createstatement" approach, it returns the correct number of records!

So now, i'm trying to figure out a way to create the following statement using the "createstatement" approach!

"SELECT CAT_VALUE FROM HLP_CATG_VALUES WHERE CAT_NAME=cat"

where cat is the changing variable!

Any suggestions would be much appeciated!

Thanks!



public void getCategory(int i, String cat)
{
try
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection
("jdbc racle:thin:@" + "f15-g" + ":" + Integer.toString(1523) + ":" + "dws","xxxx","yyyyyy");

//String pString = new String("SELECT CAT_VALUE FROM HLP_CATG_VALUES WHERE CAT_NAME=(?)");
//String pString = new String("SELECT CAT_VALUE FROM HLP_CATG_VALUES");
//PreparedStatement stmt = conn.prepareStatement(pString);
//stmt.setString(1,cat);
//ResultSet rset = stmt.executeQuery();
//wrk
Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String q = "SELECT CAT_VALUE FROM HLP_CATG_VALUES WHERE CAT_NAME=";q=q.concat("+cat+");
System.out.println("q: "+q);
ResultSet rset = stmt.executeQuery (q);
System.out.println("stmt: "+rset.toString());

hs=new HashSet();
System.out.println("in getCat i:"+i);

try
{
while (rset.next()) {
String e=new String(); e=new String(rset.getString("CAT_VALUE"));
System.out.println("in getCat loop: "+cat+" "+e+" hs: "+hs);
hs.add(e);
};
}
catch(SQLException sqlException)
{
System.out.println("The following error occured in reading from the favoritefoods table: " + sqlException);
}

System.out.println("in getCat2");
hsa[i]=hs;

rset.close();
stmt.close();
conn.close();
 
Damanjit Kaur
Ranch Hand
Posts: 346
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

"SELECT CAT_VALUE FROM HLP_CATG_VALUES WHERE CAT_NAME=cat"


Use this instead :

String q = "SELECT CAT_VALUE FROM HLP_CATG_VALUES WHERE CAT_NAME="+cat;
 
Craig Jackson
Ranch Hand
Posts: 405
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Or you can take the above answer and do something like this:



This way you can use the select statement anywhere else in your code, if needed.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64620
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A PreparedStatement is the correct way to do this. Your time would be better spent trying to figure out what you were doing incorrectly with that approach rather than resorting to string concatentation.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic