Member Avatar for Pinky.Rozita

Hi all ..

this jsp code when we use database access, to get the information by entering the price , my problem is that now iam using oracle as database and i dont know what are the changes that can be made here only ofcorse the path of database this i have , but string will it change because in oracle its varchar ? .. !! .. plz help.. its my first time to do this ..

Thanks :)

<html>
<body>
<%@ page import="java.sql.*" %>
<%
Connection con = null;
Statement st;
ResultSet rs;
String sql = "";

int price;

price= Integer.parseInt(request.getParameter("price"));

sql = "SELECT * FROM itemTBL where price = "+ price;

try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:prodDSN");
st = con.createStatement();
rs = st.executeQuery(sql);
%>
<table border=2>
<%
while(rs.next())
{
%> <tr>
<td><%=rs.getString("prodno")%></td>
<td><%=rs.getString("prodname")%></td>
<td><%=rs.getString("price")%></td>
<td><%=rs.getString("ptype")%></td>
</tr>
<% } %>
</table>
<%
rs.close();
con.close();
}

catch(Exception ex)
{
out.print(ex.getMessage());
}
%>
</body>
</html>

  • 5 Contributors
  • 9 Replies
  • 239 Views
  • 6 Months Discussion Span
  • Latest Post Latest Post by lookof2day

Recommended Answers

All 9 Replies

Member Avatar for masijade

No. The query and setString statements can remain the same. It is, however, not recommended to use scriptlets this way. The DB code should be moved out to a bean. Scriptlets, AFAIK, are only still supported for backwards compatability reasons.

You don't want to be backwards do you? ;-)

Member Avatar for Pinky.Rozita

Thanks masijade :) ...

hehe no i dont want :P .. but this what they taught us in college !! ... basic things ..

however the most important thing is that the code doesnt fully change ... we have 4 weeks to finish the project :P ..

thanks again :)

Member Avatar for jwenting

tough luck. If they teach you to do it like that ask for your money back.

Member Avatar for Pinky.Rozita

well jwenting im still in a level they dont teach us advanced things :) ..

Thanks :)

Member Avatar for jwenting

they don't teach you the basics either the way they should.
What they are teaching you is the way things were done nearly 10 years ago which has been recognised as exactly the wrong way to do them for the last 7 or so.
If they insist in teaching it like that the course isn't worth the time and money you invest in it.

Member Avatar for Pinky.Rozita

Aha, well you know better than me :) ..
Thanks for your consideration.. i will talk to my supervisor about it :) ..

Member Avatar for awsl

any sample codes on how to do this?

Member Avatar for lookof2day

Hi,

You are using jdbc-odbc bridge to connect to Oracle db here

try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:prodDSN");
st = con.createStatement();

You might have successfully created the odbc data source. But it is the Tomcat runtime that needs to know about the odbc datasource. You'll have a tough time configuring it on Tomcat as you are still a student and migh not have played much with the Tomcat configuration files. I would suggest you to use Pure JDBC driver to connect to Oracle database. You can find the Oracle JDBC driver in the Oracle installation folder under jdbc directory. You can find some zip and jar files with name like oci12.zip oci12.jar, etc (Can't remember the names properly so you can send me the names which you find there if you are not sure which one to use). Copy the oci12.jar to either the lib directory of Tomcat Installation ("C:\Program Files\Apache Software Foundation\Apache Tomcat 6.0\lib") or to your web applications WEB-INF\lib (best practice to copy here) directory. Why we are doing it? The runtime environment of Tomcat i.e., the Tomcat's JVM needs the information at runtime about the Oracle related Jdbc drivers (remember the CLASSPATH setting you might have done earlier so that your program knows at runtime where to find specific classes). If you don't do this Tomcat will throw ClassNotFoundException at runtime.

Now, the JDBC specific parameters are need to be passed while establishing the Connection. So you'll have to make the following code changes

try {
              Class.forName ("oracle.jdbc.driver.OracleDriver");//pure jdbc driver
        } catch (ClassNotFoundException e) {
              e.printStackTrace();
        }

        Connection conn = DriverManager.getConnection
             ("jdbc:oracle:thin:@localhost:1526:orcl", "scott", "tiger");
                             // @machineName:port:SID,   userid,  password

        Statement stmt = conn.createStatement();

Please refer the following FAQ on more information
http://www.orafaq.com/faqjdbc.htm

Member Avatar for lookof2day

I'm sorry for my earlier reply, I thought you r facing Connectivity issues so please ignore it.

According to ur post, the price field is a VARCHAR in Oracle (Correct me if I'm wrong). In that case, the code will change to

int price;

price= Integer.parseInt(request.getParameter("price"));

sql = "SELECT * FROM itemTBL where price = "+ "'"+price+"'";

You will have to convert price to a String(VARCHAR/CHAR format) for comparison to a String (VARCHAR) in database. To avoid the "'" + field + "'", use PreparedStatements. Or JDBC 2.0 features.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.