Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: executeQuery Can't pass variable to SQL. :-( Why?
Hi Nelson
The <%= %> tags are for substitution into HTML as you identified in your example
Hi, my name is <%= name %>.
i.e. the PrintWriter outputs *to the browser* the text Hi etc + whatever 'name' evaluates to.
You can also use the <% %> tags to create java blocks as you also have
identified,
and they don't get output to the browser.
<% Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); %>
Ok, with that said, lets look at what are you doing here ..
<% .... snip
String sql = "SELECT * FROM db.db WHERE db.column = <%= recordkey %>";
You have got a <%= nested inside a <% - can't do that.
I'm afraid I don't understand what you are trying to do, so can't be of much more help. There doesn't seem to be much point in your recordKey variable - you could just do
String sql = "SELECT * FROM db.db WHERE db.column = 99"
just as well.
If you want to keep your variable, then do this:
String sql = "SELECT * FROM db.db WHERE db.column = " + recordKey ;
If you were trying to do 'bind variables' of something with your <%=
recordKey %>
use a PreparedStatement
<%
try {
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@dsn:portnumber:dbinstance","u
serid","passwd");
String select = "SELECT * FROM db.db WHERE db.column = ?" PreparedStatement ps = con.prepareStatement(select); ResultSet rs = null;
for(int recordKey =0;recordKey <10;recordKey ++) {
ps.setString(1,""+recordKey ); rs = ps.executeQuery(); if (rs.next()) {
%>
We got back <%=rs.getString(1) %> }
Caveats:
The above is unpleasant because you would be better constructing an String
containing "where db.column in ( 1,3,etc)"
rather than looping. Secondly, you are doing data access in a JSP which
isn't nice !
Cheers,
Dave Milne, Scotland
"Nelson Broat" <nelson.broat_at_mail.cuny.edu> wrote in message news:32159d9d.0308081416.7eadabe_at_posting.google.com...
: In jsp land you can have the following: : : <% : String name = "Nelson"; : %> : : Hi, my name is <%= name %>. : : Such that, in your browser you see: : : Hi, my name is Nelson. : : I am trying to do similiar with the executeQuery() method and it is : not working. Can anyone tell me what is wrong with the syntax I'm : using? : The jsp won't compile. It seems to not want to allow me to pass a : variable called recordkey, which in this example has a value of 999. : But in a loop structure which I'm looking to create, the value would : change. Thanks for any and all help. : : <%@ page import="java.util.*, : java.text.*, : java.io.*, : java.sql.*" : contentType="text/html; charset=UTF-8" : %> : <% : Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); : con =
: stmt = con.createStatement(); : int recordkey = 999; : String sql = "SELECT * FROM db.db WHERE db.column = <%= recordkey %>"; : rs = stmt.executeQuery(sql); : %> : : Nelson Broat : nelson.broat_at_mail.cuny.eduReceived on Sat Aug 09 2003 - 05:06:07 CDT