asked on

JSP code help needed ...

Hi,

I have the following JSP code. This is for JSP with MySQL. But i want the code in JSP with ORACLE. I have changed ALL the code EXCEPT the following line:

long order_id = ((org.gjt.mm.mysql.PreparedStatement) stmt).getLastInsertID();

When we are inserting a record into "Orders" table, we are not passing this value (order_id). This is generated by SEQUENCE in ORACLE and a TRIGGER on "Orders" table is used to insert the SEQUENCE value into "Orders" table. Now i want to get the newly inserted value of (order_id) in "Orders" table. The below statement will work JSP with MySQL but not JSP with ORACLE.

long order_id = ((org.gjt.mm.mysql.PreparedStatement) stmt).getLastInsertID();

Can you please provide the code in JSP with ORACLE ?

JSP Code:
========

<%

Class.forName("org.gjt.mm.mysql.Driver");

java.sql.Connection cn = java.sql.DriverManager.getConnection("jdbc:mysql://localhost/shop");

String query = "INSERT INTO orders VALUES ('', ?, ?, ?, ?, ?, ?, ?, ?)";
java.sql.PreparedStatement stmt = cn.prepareStatement(query);

stmt.setString(1, request.getParameter("firstname"));
stmt.setString(2, request.getParameter("surname"));
stmt.setString(3, request.getParameter("address"));
stmt.setString(4, request.getParameter("city"));
stmt.setString(5, request.getParameter("postcode"));
stmt.setString(6, request.getParameter("card_number"));
stmt.setString(7, request.getParameter("card_type"));
stmt.setString(8, request.getParameter("totalvalue"));

stmt.executeUpdate();

long order_id = ((org.gjt.mm.mysql.PreparedStatement) stmt).getLastInsertID();

String order_info_query = "INSERT INTO order_info VALUES ('', ?, ?, ?)";

Enumeration products = basket.getProducts();

while (products.hasMoreElements())
{
Product product = (Product) products.nextElement();

stmt = cn.prepareStatement(order_info_query);

stmt.setLong(1, order_id);
stmt.setInt (2, Integer.parseInt(product.getId()));
stmt.setInt (3, product.getQuantity());

stmt.executeUpdate();
}

stmt.close();
cn.close();

basket.emptyBasket();

%>

Thanks

ASKER CERTIFIED SOLUTION

Chad Smith

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pramod Kumar

You may add below method to get the last id. This will work with all kinds of Database:

and call this method as:
long order_id = getLastInsertID();

public long getLastInsertID(Connection cn) throws Exception
    {
        Statement st = cn.createStatement();
        ResultSet rs = st.executeQuery("select max(order_id) from orders");
        return rs.getLong(1);
    }

Open in new window

SOLUTION

Pramod Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial