File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

variable name for where clause in sql

 
dbk kumar
Greenhorn
Posts: 14
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2575
11
C++ Linux Netbeans IDE
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
dbk kumar
Greenhorn
Posts: 14
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2575
11
C++ Linux Netbeans IDE
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 14
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hey..thats working!!! great thank you very much
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic