aspose file tools*
The moose likes JDBC and the fly likes Difference between JDBC in 'normal' Java and JSP? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Difference between JDBC in Watch "Difference between JDBC in New topic
Author

Difference between JDBC in 'normal' Java and JSP?

Lex Wouda
Greenhorn

Joined: Mar 07, 2001
Posts: 22
Hi,
I'm going mad. the following code in 'main' works just fine and returns the wantend 4 rows.

public static void main(java.lang.String[] args) {
String product = "";
int aantal = 0;
double price = 0;
String id = "3";
double GrandTotal =0;
String dbuser = "user";
String dbpassword = "password";
String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
String url = "jdbc dbc:getsildb";
try{

Driver drv = (Driver) Class.forName(driver).newInstance();

Properties p = new Properties();
p.put("user", dbuser);
p.put("password", dbpassword);
Connection con = DriverManager.getConnection(url,dbuser,dbpassword);

Statement stmt = con.createStatement();
String query = "SELECT id,product,prijs,aantal FROM product ,buy WHERE kid="+id+" AND pid=productid ";

ResultSet rs = stmt.executeQuery(query);
while (rs.next())
{
product = rs.getString("product");
price = rs.getDouble("prijs");
aantal = rs.getInt("aantal");
double total = aantal*price;
GrandTotal = GrandTotal + total;

System.out.println(" id = "+Integer.toString(rs.getInt("id")));


}
//close statment and connection
stmt.close();
con.close();
}
catch(Exception se) {System.out.println(se);}
}
however the exact same code in a Jsp page (I copied the code from my jsp page, and added some html etc) gives the first row back and then says " sql execption: no data found"
If I remove the system.out.println("id = "+rs.getint("id")); statement the jsp works just fine...
TIA,
Lex
prabhat kumar
Ranch Hand

Joined: Apr 11, 2001
Posts: 114
Originally posted by Lex Wouda:
Hi,
String product = "";
int aantal = 0;
double price = 0;
String id = "3";
double GrandTotal =0;
String dbuser = "user";
String dbpassword = "password";
String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
String url = "jdbc dbc:getsildb";
try{

Driver drv = (Driver) Class.forName(driver).newInstance();

Properties p = new Properties();
p.put("user", dbuser);
p.put("password", dbpassword);
Connection con = DriverManager.getConnection(url,dbuser,dbpassword);

Statement stmt = con.createStatement();
String query = "SELECT id,product,prijs,aantal FROM product ,buy WHERE kid="+id+" AND pid=productid ";

ResultSet rs = stmt.executeQuery(query);
while (rs.next())
{
product = rs.getString("product");
price = rs.getDouble("prijs");
aantal = rs.getInt("aantal");
double total = aantal*price;
GrandTotal = GrandTotal + total;

System.out.println(" id = "+Integer.toString(rs.getInt("id")));


}
//close statment and connection
stmt.close();
con.close();
}
catch(Exception se) {System.out.println(se);}
}
Lex

System.out.println(), prints on console not the browser ..
u use out.println() or something like this
u try this ..

<%<br /> String product = "";<br /> int aantal = 0;<br /> double price = 0;<br /> String id = "3";<br /> double GrandTotal =0;<br /> String dbuser = "user";<br /> String dbpassword = "password";<br /> String driver = "sun.jdbc.odbc.JdbcOdbcDriver";<br /> String url = "jdbc dbc:getsildb";<br /> try{<br /> <br /> Driver drv = (Driver) Class.forName(driver).newInstance();<br /> <br /> Properties p = new Properties();<br /> p.put("user", dbuser);<br /> p.put("password", dbpassword);<br /> Connection con = DriverManager.getConnection(url,dbuser,dbpassword);<br /> <br /> Statement stmt = con.createStatement();<br /> String query = "SELECT id,product,prijs,aantal FROM product ,buy WHERE kid="+id+" AND pid=productid ";<br /> <br /> ResultSet rs = stmt.executeQuery(query);<br /> while (rs.next())<br /> {<br /> product = rs.getString("product");<br /> price = rs.getDouble("prijs");<br /> aantal = rs.getInt("aantal");<br /> double total = aantal*price;<br /> GrandTotal = GrandTotal + total;<br /> %>
<%=rs.getString("id")%>
<%<br /> <br /> }<br /> //close statment and connection<br /> stmt.close();<br /> con.close();<br /> }<br /> catch(Exception se) {System.out.println(se);}<br /> }<br /> %>
Lex

that's it ..
not much brains :>
Prabhat kumar
[This message has been edited by prabhat kumat (edited April 11, 2001).]
Lex Wouda
Greenhorn

Joined: Mar 07, 2001
Posts: 22
I use Visual Age for Java; System.out writes to VAJ's console...
so that is not the problem.
If I change the System.out.println(Integer.toString(rs.getInt("id")); to for example String test = Integer.toString(rs.getInt("id"));
I get the same results.
L.
prabhat kumar
Ranch Hand

Joined: Apr 11, 2001
Posts: 114
u can view the code that is not visible ..by clicking on the edit message above ..it will show u altered the jsp code
Prabhat
[This message has been edited by prabhat kumat (edited April 11, 2001).]
Lex Wouda
Greenhorn

Joined: Mar 07, 2001
Posts: 22
I tried that.
it gives the same result; after one row (it displays the id of the first retrieved recordset and then it throws the SQLExecption 'no data found', while it should show the second id.
again: when running this code in a java programm things work fine; that bothers me the most.
some other question strongly related:
I use getInt because the id field in the db is an number/integer.
Why does it work when I call getString on an number field?

Thomas Paul
mister krabs
Ranch Hand

Joined: May 05, 2000
Posts: 13974
Why does it work when I call getString on an number field?
Conversion occurrs automatically.


Associate Instructor - Hofstra University
Amazon Top 750 reviewer - Blog - Unresolved References - Book Review Blog
Marya Doery
Greenhorn

Joined: Dec 27, 2000
Posts: 15
I created a sample Access database to mimic your situation (just filled your columns with some dummy data). I ran your code in a little test java application (main method) - it returned two rows for the dummy data I was using. Then I copied your exact code into a jsp file and ran the jsp under Tomcat, running as a VAJ 3.5 application. The out statements printed to the console correctly, identically the same as they did in the application - in other words, it works fine either way! Perhaps the problem is with the jsp you wrote - how much did you change your code in the jsp file? If you want more help, show us your jsp.
Lex Wouda
Greenhorn

Joined: Mar 07, 2001
Posts: 22
Hi all,
thanks for the answers.
I�ve made the jsp from scratch again using the following code and now it works.
<%<br /> while (rs.next())<br /> {<br /> product = rs.getString("product");<br /> price = rs.getDouble("prijs");<br /> aantal = rs.getInt("aantal");<br /> double total = aantal*price;<br /> GrandTotal = GrandTotal + total;<br /> <br /> //System.out.println(" id = "+Integer.toString(rs.getInt("id")));<br /> <br /> %>
<tr>
<td><INPUT TYPE=CHECKBOX NAME="<%="chk_"+rs.getString("id")%>"></td>
<td><%=aantal%></td>
<td><%=product%></td>
<td><%=total%></td>
<td></td>
</tr>
<%<br /> }<br /> //close statment and connection<br /> stmt.close();<br /> con.close();<br /> <br /> }<br /> catch(Exception e) {System.out.println(e);}<br /> %>
<tr>

bye,
Lex
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Difference between JDBC in 'normal' Java and JSP?