aspose file tools*
The moose likes JDBC and the fly likes variable name for where clause in sql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "variable name for where clause in sql" Watch "variable name for where clause in sql" New topic
Author

variable name for where clause in sql

dbk kumar
Greenhorn

Joined: Feb 01, 2010
Posts: 14
hi..
I am doing a jdbc program connecting a server and a client. I type in a value from client which is received by the server.Now the server reads in the value typed and stores in a variable. Now I use this variable to access the database(oracle 10g) with the help of select where clause which equates to a variable(not literal value that is there in table,but this variable has the literal value).But I am unable to fetch the result(which is accessed otherwise with a literal). how should I access the variable in the table.I tried &variable,$variable,@variable. but none really worked.
kindly reply back soon..
thank you in advance
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2497
    
    8

Hi Kumar,

Can you help us a bit by showing what you tried?
In JDBC, you bind a variable to a query by:
- putting a question mark in your sql statement for each variable you want to use (select item_description from mytable where item_number = ?)
- using a PreparedStatement, and its setXXX() methods to bind your variable.

Sun has examples in its JDBC Turtorial.

Regards, Jan


OCUP UML fundamental and ITIL foundation
youtube channel
dbk kumar
Greenhorn

Joined: Feb 01, 2010
Posts: 14
import java.sql.*;

public class tp{
public static void main(String[] args) {
try {
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

Connection conn = DriverManager.getConnection("jdbcracle:thin:@127.0.0.1:1521rcl","system","password");


Statement stmt = conn.createStatement();

String an="first";
System.out.println(an);

// a select query
ResultSet rset = stmt.executeQuery ("SELECT * FROM application where appname ='&an'");

while (rset.next()) {
System.out.println(rset.getString(1));
System.out.println(rset.getString(2));
System.out.println(rset.getString(3));
}

rset.close();
stmt.close();
conn.close();

} catch (Exception e) {
System.out.println("ERROR : " + e.getMessage());
}
}
}
this is the code which I tried to get the values from table application having appname='first' using a variable an but I failed... (this is just a part of server client simulation where you get the value 'first' from client which you assign to a variable 'an' in the server and try to access the database with variable 'an')
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2497
    
    8

change the query to: SELECT field1, field2, field3 FROM application where appname =?
Use a PreparedStatement in stead of a Statement
Bind the variable by calling stmt.setString(1, an); before calling execute...
Done

(note that I replaced * with field1, field2, field3. Makes your code more readable, and safer for future changes. You should replace field1, 2 and 3 with real column names)
dbk kumar
Greenhorn

Joined: Feb 01, 2010
Posts: 14
hey..thats working!!! great thank you very much
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: variable name for where clause in sql