Re: executeQuery Can't pass variable to SQL. :-( Why?

From: Dave Milne <jeep_at__nospam_milne.info>
Date: Sat, 09 Aug 2003 10:06:07 GMT
Message-ID: <jI3Za.1322$Ic.11076832@news-text.cableinet.net>

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) %>
        }

    }
} catch (SQLException sqle) {sqle.printStackTrace();}
%>

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 =

DriverManager.getConnection("jdbc:oracle:thin:@dsn:portnumber:dbinstance","u serid","passwd");

: 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.edu

Received on Sat Aug 09 2003 - 05:06:07 CDT