server/DBInterface/DBMultipleInsertTest.java

import java.sql.*;

/**
 * A main method to test how to insert MULTIPLE rows to a mySQL
 * database table in a single transaction.  This is required so
 * that the id references may be inserted to multiple tables
 * using an autoincrement field.
 *
 * CONCLUSION: This class makes use of transaction features on
 * the mySQL database.  Running the main method reveals that MySQL
 * versions older than 3.23.15 do not support transactions.
 * This is unfortunate, as raptor currently uses 3.22.32-log.
 * We shall have to find another way of doing things...
 *
 * IMPORTANT: This method obtains the database user and database
 * connection password from the Password class.  As such, this
 * class may be safely placed in a public CVS repository.
 *
 * @author  $Author: pjm2 $
 * @version $Id: DBMultipleInsertTest.java,v 1.1 2000/12/04 09:54:03 pjm2 Exp $
 */
class DBMultipleInsertTest {

    public static void main(String[] args){

        // Create an instance of the mySQL driver.
        try{
            Class.forName("org.gjt.mm.mysql.Driver").newInstance(); 
        }
        catch(Exception e){
            e.printStackTrace();
        }

        try {
            // Use the connection string for our group database.
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/co600_10_db?user="+Password.mySQLUser+"&password="+Password.mySQLPassword);

            // Create a statement object.
            Statement stmt = conn.createStatement();

            // Do NOT commit after each statement.
            conn.setAutoCommit(false);

            // A couple of SQL statements to try out...            
            String sql1 = "INSERT INTO cpu (id, cpu_no, cpu_load) VALUES (3, 4, 5);";
            String sql2 = "INSERT INTO cpu (id, cpu_no, cpu_load) VALUES (7, 8, 9);";
            
            // Execute the insert statements.
            stmt.executeUpdate(sql1);
            stmt.executeUpdate(sql2);
            
            // Now commit the results.
            conn.commit();
            
            // Restore autocommit.
            conn.setAutoCommit(true); 
            
            // Close the statement and database connection.
            stmt.close();
            conn.close();
        }
        catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState:     " + e.getSQLState());
            System.out.println("VendorError:  " + e.getErrorCode());
        }
    }
}   
Revision:	1.2
Committed:	Mon Dec 4 09:57:53 2000 UTC (23 years, 11 months ago) by pjm2
Branch:	MAIN
CVS Tags:	PROJECT_COMPLETION, HEAD
Changes since 1.1:	+8 -2 lines
Error occurred while calculating annotation data.
Log Message:	This class makes use of transaction features on the mySQL database. Running the main method reveals that MySQL versions older than 3.23.15 do not support transactions. This is unfortunate, as raptor currently uses 3.22.32-log. We shall have to find another way of doing things...
#	Content
1	import java.sql.*;
2
3	/**
4	* A main method to test how to insert MULTIPLE rows to a mySQL
5	* database table in a single transaction. This is required so
6	* that the id references may be inserted to multiple tables
7	* using an autoincrement field.
8	*
9	* CONCLUSION: This class makes use of transaction features on
10	* the mySQL database. Running the main method reveals that MySQL
11	* versions older than 3.23.15 do not support transactions.
12	* This is unfortunate, as raptor currently uses 3.22.32-log.
13	* We shall have to find another way of doing things...
14	*
15	* IMPORTANT: This method obtains the database user and database
16	* connection password from the Password class. As such, this
17	* class may be safely placed in a public CVS repository.
18	*
19	* @author $Author: pjm2 $
20	* @version $Id: DBMultipleInsertTest.java,v 1.1 2000/12/04 09:54:03 pjm2 Exp $
21	*/
22	class DBMultipleInsertTest {
23
24	public static void main(String[] args){
25
26	// Create an instance of the mySQL driver.
27	try{
28	Class.forName("org.gjt.mm.mysql.Driver").newInstance();
29	}
30	catch(Exception e){
31	e.printStackTrace();
32	}
33
34	try {
35	// Use the connection string for our group database.
36	Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/co600_10_db?user="+Password.mySQLUser+"&password="+Password.mySQLPassword);
37
38	// Create a statement object.
39	Statement stmt = conn.createStatement();
40
41	// Do NOT commit after each statement.
42	conn.setAutoCommit(false);
43
44	// A couple of SQL statements to try out...
45	String sql1 = "INSERT INTO cpu (id, cpu_no, cpu_load) VALUES (3, 4, 5);";
46	String sql2 = "INSERT INTO cpu (id, cpu_no, cpu_load) VALUES (7, 8, 9);";
47
48	// Execute the insert statements.
49	stmt.executeUpdate(sql1);
50	stmt.executeUpdate(sql2);
51
52	// Now commit the results.
53	conn.commit();
54
55	// Restore autocommit.
56	conn.setAutoCommit(true);
57
58	// Close the statement and database connection.
59	stmt.close();
60	conn.close();
61	}
62	catch (SQLException e) {
63	System.out.println("SQLException: " + e.getMessage());
64	System.out.println("SQLState: " + e.getSQLState());
65	System.out.println("VendorError: " + e.getErrorCode());
66	}
67	}
68	}