aspose file tools*
The moose likes JDBC and the fly likes MySQL Transactions Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQL Transactions" Watch "MySQL Transactions" New topic
Author

MySQL Transactions

K McGinley
Greenhorn

Joined: Mar 07, 2002
Posts: 5
Hello all,
I can't seem to get transactions to work using MySQL Connector/J 3.0 or mm.mysql-2.0.14-bin.jar and am asking for help. Setting AutoCommit to false and not committing doesn't seem to have any affect, it's as if AutoCommit is true. Anyone notice anything in the code that is wrong or have any suggestions?
Using MySQL for Windows version 3.23.54. I'll be happy to provide any additional information.
Thanks.

<%@ page contentType="text/xml"%>
<%@ page import="java.io.*" %>
<%@ page import="org.xml.sax.*" %>
<%@ page import="org.xml.sax.helpers.DefaultHandler" %>
<%@ page import="javax.xml.parsers.SAXParserFactory" %>
<%@ page import="javax.xml.parsers.ParserConfigurationException" %>
<%@ page import="javax.xml.parsers.SAXParser" %>
<%@ page import="java.util.*" %>
<%@ page import="com.hose.ejb.MyElement" %>
<%@ page import="com.hose.ejb.MySAXParserBean" %>
<%@ page import="java.sql.*, java.io.*, javax.servlet.*, javax.servlet.http.*" %>
<%@ page import="org.apache.regexp.*" %>
<jsp:useBean id="saxparser" class="com.hose.ejb.MySAXParserBean" />
<jsp:useBean id="dbuserinfo" class="com.hose.ejb.DBAccess" />
<%!
public void jspInit() {
try {
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
}
catch (ClassNotFoundException e) {
System.out.println("Can't load database driver.<br>" + e);
}
catch (Exception e) {
System.out.println("Can't instantiate driver.<br>" + e);
}
}
%>
<%
String xmldata = request.getParameter("message");
//System.out.println(xmldata);
Collection xmlcollection = saxparser.parse(xmldata);
Iterator ir = xmlcollection.iterator();
String tablename = "inventory";
String dbURL = "jdbc:mysql://localhost/513";
String output = null;
String dbusername = dbuserinfo.getDbusername();
String dbpassword = dbuserinfo.getDbpassword();
Connection connection = null;
connection = DriverManager.getConnection(dbURL, dbusername, dbpassword);
connection.setAutoCommit(false);
java.sql.Statement statement = null;
ResultSet resultset = null;
int productid = 0;
int quantityordered = 0;
boolean itemidbool = false;
boolean quantitybool = false;
boolean success = true;
float total = 0;
boolean transaction_complete = false;
String customerid = "";
String order = "";
order += "<?xml version='1.0' encoding='utf-8'?>\n";
order += "<orderinvoice orderid=\"\" totalprice=\"\" date=\"" + new java.util.Date() + "\" DateNeeded=\"\" customerid=\"\">\n";
int i = 1;
while(ir.hasNext()) {
MyElement element = (MyElement) ir.next();
String tag = element.getQname();
//out.println("uri: " + element.getUri());
//out.println("localname: " + element.getLocalName());
//out.println("tag = " + tag);
if (tag.equals("customerid")) {
//out.println("element="+element.getQname()+", value="+element.getValue()+"<br>");
customerid = element.getValue();
}
else if(tag.equals("itemid")) {
//out.println("element="+element.getQname()+", value="+element.getValue()+"<br>");
productid = Integer.parseInt(element.getValue());
itemidbool = true;
}
else if(tag.equals("quantity")) {
//out.println("element="+element.getQname()+", value="+element.getValue()+"<br>");
quantityordered = Integer.parseInt(element.getValue());
quantitybool = true;
}
if (itemidbool && quantitybool) {
try{
//connection = DriverManager.getConnection(dbURL, dbusername, dbpassword);
statement = connection.createStatement();
String queryinfo = "select * from " + tablename + " where productid='" + productid + "'";
resultset = statement.executeQuery(queryinfo);
String nameresult = "";
float priceresult = 0;
int quantityresult = 0;
if(resultset.next()){
nameresult = resultset.getString("product_name");
priceresult = Float.parseFloat(resultset.getString("price"));
quantityresult = Integer.parseInt(resultset.getString("quantity"));
}
int newquantity = quantityresult - quantityordered;
String updateinfo = "update " + tablename + " set quantity='" + newquantity + "' where productid='" + productid + "'";
//out.println(updateinfo);
statement.executeUpdate(updateinfo);
order += "\t<item>\n";
order += "\t\t<itemid>" + i++ + "</itemid>";
order += "\t\t<name>" + nameresult + "</name>";
order += "\t\t<unitprice>" + priceresult + "</unitprice>";
order += "\t\t<quantity>" + quantityordered + "</quantity>";
order += "\t\t<message>Ordered</message>";
order += "\t</item>\n";

total += priceresult * quantityordered;
itemidbool = quantitybool = false;
/*if (!success) {
connection.rollback();
}*/
} catch (SQLException e) {
out.println("Database access failed.<br>" + e + "<br>");
connection.rollback();
}
finally {
if (connection != null) {
try {
resultset.close();
statement.close();
if (transaction_complete)
//connection.commit();
connection.close();
}
catch (Exception e) {}
}
}
//connection.commit();
//connection.rollback();
}
}
order += "</orderinvoice>";
//String totalpricepattern = "totalprice=\"\"";
RE totalpriceregexp = new RE("totalprice=\"\"");
String totalpricesubst = totalpriceregexp.subst(order, "totalprice=\"" + total + "\"");
//String customeridpattern = "customerid=\"\"";
RE customeridregexp = new RE("customerid=\"\"");
String customeridsubst = customeridregexp.subst(totalpricesubst, "customerid=\"" + customerid + "\"");
out.println(customeridsubst);
%>

[ February 21, 2003: Message edited by: K McGinley ]
[ February 21, 2003: Message edited by: K McGinley ]
[ February 21, 2003: Message edited by: K McGinley ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MySQL Transactions
 
Similar Threads
JSP page wont call other jsp page!!
trouble calling a bean from JSP
problem with JSP
Part of the code is not working in jsp
creating a Loginpage.jsp in JSP with LOGIN id and Password.