JSP using Oracle Database

[Pinky.Rozita]

<%@ 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);
%>

<%
while(rs.next())
{
%> <%=rs.getString("prodno")%>
<%=rs.getString("prodname")%>
<%=rs.getString("price")%>
<%=rs.getString("ptype")%>

<% } %>

<%
rs.close();
con.close();
}

catch(Exception ex)
{
out.print(ex.getMessage());
}
%>

[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? ;-)

[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 :)

[jwenting]

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

[Pinky.Rozita]

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

Thanks :)

[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.

[Pinky.Rozita]

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

[awsl]

any sample codes on how to do this?

[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

[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.