473,320 Members | 2,006 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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").n ewInstance();
con = DriverManager.getConnection("jdbc:oracle:thin:@dsn :portnumber:dbinstance","userid","passwd");
stmt = con.createStatement();
int recordkey = 999;
String sql = "SELECT * FROM db.db WHERE db.column = <%= recordkey %>";
rs = stmt.executeQuery(sql);
%>

Nelson Broat
ne**********@mail.cuny.edu
Jul 19 '05 #1
3 7703
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").n ewInstance(); %>

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").n ewInstance();
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" <ne**********@mail.cuny.edu> wrote in message
news:32*************************@posting.google.co m...
: 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").n ewInstance();
: 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
: ne**********@mail.cuny.edu
Jul 19 '05 #2
ne**********@mail.cuny.edu (Nelson Broat) wrote
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! :-)


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
Jul 19 '05 #3
You are most welcome.

Dave.

On 19 Aug 2003 13:18:02 -0700, ne**********@mail.cuny.edu (Nelson
Broat) wrote:
"Dave Milne" <jeep@_nospam_milne.info> wrote in message news:<jI********************@news-text.cableinet.net>...
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

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 ;


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


Dave Milne, Scotland
'99 TJ Sahara
Jul 19 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Antonio | last post by:
Good Morning, I've a Servlet with inside the doPost() method a connection to a dB, the problem is that is a long query that work ok if launched on the dB with something like TOAD and also works...
26
by: Dave Hammond | last post by:
In document "A.html" I have defined a function and within the document body have included an IFRAME element who's source is document "B.html". In document "B.html" I am trying to call the function...
5
by: wilson | last post by:
Dear all, In this time, I want to pass array to function. What should I declare the parameter in the function?i int array or int array? Which one is correct? ...
4
by: z_learning_tester | last post by:
I'm reading the MS press C# book and there seems to be a contradiction. Please tell me which one is correct, 1 or 2. Thanks! Jeff 1. First it gives the code below saying that it prints 0 then...
14
by: Robin Tucker | last post by:
Although I've been working on this project for 8 months now, I'm still not sure of the difference between ByVal and ByRef. As most objects in VB are reference types, passing ByVal I've discovered...
4
by: etuncer | last post by:
Hello All, I have Access 2003, and am trying to build a database for my small company. I want to be able to create a word document based on the data entered through a form. the real question is...
3
by: Nelson Broat | last post by:
In jsp land you can have the following: <% String name = "Nelson"; %> Hi, my name is <%= name %>. Such that, in your browser you see:
0
by: Marc S | last post by:
I have an application that queries my database through my DBML file generically: BindingSource _src = new BindingSource(); Type type = System.Type.GetType(string.Format("namespace.{0},...
0
by: Andrus | last post by:
I implemented cached DLinq ExecuteQuery method. However some queries are run against database always because keys are not found in cache. Maybe equality comparer or other parts of implementation...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.