aspose file tools*
The moose likes JDBC and the fly likes How to insert variable value in select? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to insert variable value in select?" Watch "How to insert variable value in select?" New topic
Author

How to insert variable value in select?

bob connolly
Ranch Hand

Joined: Mar 10, 2004
Posts: 204
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

Joined: Oct 18, 2004
Posts: 346
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

Joined: Mar 19, 2002
Posts: 405
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

Joined: Jan 10, 2002
Posts: 61654
    
  67

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.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: How to insert variable value in select?