• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to insert variable value in select?

 
Ranch Hand
Posts: 204
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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();
 
Ranch Hand
Posts: 346
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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;
 
Ranch Hand
Posts: 405
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic