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

[Nelson Broat]

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.ut il.*,
java.text.*,
java.io.*,
java.sql.*"
contentType="te xt/html; charset=UTF-8"
%>
<%
Class.forName(" oracle.jdbc.dri ver.OracleDrive r").newInstance ();
con = DriverManager.g etConnection("j dbc:oracle:thin :@dsn:portnumbe r:dbinstance"," userid","passwd ");
stmt = con.createState ment();
int recordkey = 999;
String sql = "SELECT * FROM db.db WHERE db.column = <%= recordkey %>";
rs = stmt.executeQue ry(sql);
%>

Nelson Broat
nelson.broat@ma il.cuny.edu

[Dave Milne]

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.dri ver.OracleDrive r").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 PreparedStateme nt

<%
try {
Class.forName(" oracle.jdbc.dri ver.OracleDrive r").newInstance ();
Connection con =
DriverManager.g etConnection("j dbc:oracle:thin :@dsn:portnumbe r:dbinstance"," u
serid","passwd" );
String select = "SELECT * FROM db.db WHERE db.column = ?"
PreparedStateme nt ps = con.prepareStat ement(select);
ResultSet rs = null;

for(int recordKey =0;recordKey <10;recordKey ++) {
ps.setString(1, ""+recordKe y );
rs = ps.executeQuery ();
if (rs.next()) {
%>
We got back <%=rs.getString (1) %>
}
}
} catch (SQLException sqle) {sqle.printStac kTrace();}
%>


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@m ail.cuny.edu> wrote in message
news:32159d9d.0 308081416.7eada be@posting.goog le.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.ut il.*,
: java.text.*,
: java.io.*,
: java.sql.*"
: contentType="te xt/html; charset=UTF-8"
: %>
: <%
: Class.forName(" oracle.jdbc.dri ver.OracleDrive r").newInstance ();
: con =
DriverManager.g etConnection("j dbc:oracle:thin :@dsn:portnumbe r:dbinstance"," u
serid","passwd" );
: stmt = con.createState ment();
: int recordkey = 999;
: String sql = "SELECT * FROM db.db WHERE db.column = <%= recordkey %>";
: rs = stmt.executeQue ry(sql);
: %>
:
: Nelson Broat
: nelson.broat@ma il.cuny.edu

[Billy Verreynne]

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

nelson.broat@ma il.cuny.edu (Nelson Broat) wrote
[color=blue]
> I thought I had, but I bet I put that damn quote in the wrong place to
> start with. Then, of course, I traveled on my tagents while pulling my
> head out of my hair! :-)[/color]

Dear Nelson

Yeah.. well.. al least you have hair to pull out... Er.. or is
"pulling head from hair" not getting head/hair mixed up, but in fact
what you really doing..? Getting it stuck at the back there amoung the
butt hairs..? If so, does this in any contribute to you having more
hair on you head?

regards,
Confused

[Dave Milne]

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

You are most welcome.

Dave.

On 19 Aug 2003 13:18:02 -0700, nelson.broat@ma il.cuny.edu (Nelson
Broat) wrote:
[color=blue]
>"Dave Milne" <jeep@_nospam_m ilne.info> wrote in message news:<jI3Za.132 2$Ic.11076832@n ews-text.cableinet. net>...[color=green]
>> Hi Nelson
>>
>>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[/color]
>of much[color=green]
>>more help. There doesn't seem to be much point in your recordKey[/color]
>variable -[color=green]
>>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 ;[/color]
>
>Thanks Dave! That was it. I was trying so many different variations
>that I didn't try the most simplest one of all. :-) Well...actually ...
>I thought I had, but I bet I put that damn quote in the wrong place to
>start with. Then, of course, I traveled on my tagents while pulling my
>head out of my hair! :-)
>
>Thanks again,
>
>Nelson[/color]

Dave Milne, Scotland
'99 TJ Sahara