Navigation

← Previous Changeset
Next Changeset →

Changeset 213

Timestamp:

12/28/07 19:51:35 (1 year ago)

Author:

chris

Message:

Hide more SQLExceptions inside DatabaseExceptions?.

Improve debugging when a change_log query fails: show the actual
query that failed and a more helpful error message.

Files:

lex/trunk/src/com/qwirx/db/sql/SqlChange.java (modified) (8 diffs)
lex/trunk/src/com/qwirx/db/sql/SqlDatabase.java (modified) (1 diff)

Legend:

: Unmodified
: Added
: Removed
: Modified
: Copied
: Moved

lex/trunk/src/com/qwirx/db/sql/SqlChange.java

r172	r213
81	81
82	82	private int insertRowChangeLog(int originalRowId)
83		throws SQLException
84		{
85		PreparedStatement stmt = prepareAndLogError(
86		"INSERT INTO changed_rows SET Log_ID = ?, " +
87		"Unique_ID = ?");
88		stmt.setInt(1, this.id.intValue());
89		stmt.setInt(2, originalRowId);
90		stmt.executeUpdate();
91		stmt.close();
92
93		stmt = prepareAndLogError(
94		"SELECT LAST_INSERT_ID()");
95		ResultSet rs = executeQueryAndLogError(stmt);
96		rs.next();
97		int logRowEntryId = rs.getInt(1);
98		rs.close();
99		stmt.close();
	83	throws DatabaseException
	84	{
	85	int logRowEntryId;
	86
	87	try
	88	{
	89	PreparedStatement stmt = prepareAndLogError(
	90	"INSERT INTO changed_rows SET Log_ID = ?, " +
	91	"Unique_ID = ?");
	92	stmt.setInt(1, this.id.intValue());
	93	stmt.setInt(2, originalRowId);
	94	stmt.executeUpdate();
	95	stmt.close();
	96
	97	stmt = prepareAndLogError(
	98	"SELECT LAST_INSERT_ID()");
	99	ResultSet rs = executeQueryAndLogError(stmt);
	100	rs.next();
	101	logRowEntryId = rs.getInt(1);
	102	rs.close();
	103	stmt.close();
	104	}
	105	catch (Exception e)
	106	{
	107	throw new DatabaseException("Failed to create changed_rows",
	108	e);
	109	}
100	110
101	111	return logRowEntryId;
…	…
103	113
104	114	private int findRowChangeLog(int originalRowId)
105		throws SQLException
106		{
107		PreparedStatement stmt = prepareAndLogError(
108		"SELECT ID FROM changed_rows WHERE Log_ID = ? AND " +
109		"Unique_ID = ?");
110		stmt.setInt(1, this.id.intValue());
111		stmt.setInt(2, originalRowId);
112		ResultSet rs = executeQueryAndLogError(stmt);
113		rs.next();
114		int logRowEntryId = rs.getInt(1);
115		rs.close();
116		stmt.close();
	115	throws DatabaseException
	116	{
	117	int logRowEntryId;
	118
	119	try
	120	{
	121	PreparedStatement stmt = prepareAndLogError(
	122	"SELECT ID FROM changed_rows WHERE Log_ID = ? AND " +
	123	"Unique_ID = ?");
	124	stmt.setInt(1, this.id.intValue());
	125	stmt.setInt(2, originalRowId);
	126	ResultSet rs = executeQueryAndLogError(stmt);
	127	rs.next();
	128	logRowEntryId = rs.getInt(1);
	129	rs.close();
	130	stmt.close();
	131	}
	132	catch (SQLException e)
	133	{
	134	throw new DatabaseException("Failed to find changed_rows entry",
	135	e);
	136	}
117	137
118	138	return logRowEntryId;
…	…
275	295
276	296	private PreparedStatement prepareAndLogError(String query)
277		throws ~~SQL~~Exception
	297	throws DatabaseException
278	298	{
279	299	try
…	…
283	303	catch (SQLException e)
284	304	{
285		System.err.println("Error preparing query ("+query+"): "+e);
286		throw(e);
	305	throw new DatabaseException("Failed to prepare query", e, query);
287	306	}
288	307	}
…	…
399	418	}
400	419
	420	boolean oldAutoCommit;
	421
	422	try
	423	{
	424	oldAutoCommit = conn.getAutoCommit();
	425	conn.setAutoCommit(false);
	426	}
	427	catch (SQLException e)
	428	{
	429	throw new DatabaseException("Failed to enable transactions", e);
	430	}
	431
	432	String tempQuery = "INSERT INTO change_log " +
	433	"SET User = ?, Date_Time = NOW(), DB_Type = 'SQL', " +
	434	" DB_Name = ?, Table_Name = ?, Cmd_Type = ?";
	435
401	436	try
402	437	{
403		boolean oldAutoCommit = conn.getAutoCommit();
404		conn.setAutoCommit(false);
405
406		PreparedStatement stmt = prepareAndLogError(
407		"INSERT INTO change_log SET User = ?, Date_Time = NOW(), "+
408		"DB_Type = 'SQL', DB_Name = ?, Table_Name = ?, Cmd_Type = ?");
	438	PreparedStatement stmt = prepareAndLogError(tempQuery);
409	439	stmt.setString(1, username);
410	440	stmt.setString(2, database);
…	…
413	443	stmt.executeUpdate();
414	444	stmt.close();
415
416		stmt = prepareAndLogError("SELECT LAST_INSERT_ID()");
	445	}
	446	catch (SQLException e)
	447	{
	448	throw new DatabaseException("Failed to create change_log entry",
	449	e, tempQuery);
	450	}
	451
	452	try
	453	{
	454	PreparedStatement stmt = prepareAndLogError("SELECT LAST_INSERT_ID()");
417	455	ResultSet rs = executeQueryAndLogError(stmt);
418	456	rs.next();
…	…
420	458	rs.close();
421	459	stmt.close();
422
423		if (type == UPDATE \|\| type == DELETE)
424		{
425		captureOldValues();
426		}
427
428		stmt = prepareAndLogError(sb.toString());
429
430		if (type == INSERT \|\| type == UPDATE)
431		{
432		int i = 0;
433		for (Iterator keys = fields.keySet().iterator(); keys.hasNext(); )
	460	}
	461	catch (SQLException e)
	462	{
	463	throw new DatabaseException("Failed to get last insert ID", e);
	464	}
	465
	466	if (type == UPDATE \|\| type == DELETE)
	467	{
	468	captureOldValues();
	469	}
	470
	471	try
	472	{
	473	PreparedStatement stmt = prepareAndLogError(sb.toString());
	474
	475	if (type == INSERT \|\| type == UPDATE)
	476	{
	477	int i = 0;
	478	for (Iterator keys = fields.keySet().iterator(); keys.hasNext(); )
434	479	{
435		String key = (String)( keys.next() );
436		Object value = fields.get(key);
	480	String key = (String)( keys.next() );
	481	Object value = fields.get(key);
437	482
438	483	if (value instanceof String)
…	…
444	489	stmt.setNull(++i, Types.VARCHAR);
445	490	}
446		}
447		}
448
449		stmt.executeUpdate();
450		// System.out.println(stmt.toString());
451		stmt.close();
452
453		if (type == INSERT \|\| type == UPDATE) {
454		captureNewValues();
455		}
456
457		conn.commit();
458		conn.setAutoCommit(oldAutoCommit);
459		}
	491	}
	492	}
	493
	494	stmt.executeUpdate();
	495	// System.out.println(stmt.toString());
	496	stmt.close();
	497	}
460	498	catch (SQLException e)
461	499	{
462		m_LOG.error(sb.toString(), e);
463		DatabaseException de = new DatabaseException("Failed to execute " +
	500	throw new DatabaseException("Failed to execute " +
464	501	"the requested operation. Please check your field names " +
465	502	"and values", e, sb.toString());
466		de.setStackTrace(e.getStackTrace());
467		throw de;
468		}
469
	503	}
	504
	505	if (type == INSERT \|\| type == UPDATE) {
	506	captureNewValues();
	507	}
	508
	509	try
	510	{
	511	conn.commit();
	512	conn.setAutoCommit(oldAutoCommit);
	513	}
	514	catch (SQLException e)
	515	{
	516	throw new DatabaseException("Failed to restore transaction state",
	517	e);
	518	}
	519
470	520	long totalTime = System.currentTimeMillis() - startTime;
471	521	m_LOG.info(totalTime+" ms to track "+sb.toString());

lex/trunk/src/com/qwirx/db/sql/SqlDatabase.java

r175	r213
52	52	new DbColumn("ID", "INT(11)", false,
53	53	true, true),
54		new DbColumn("User", "VARCHAR(20)", false),
	54	new DbColumn("User", "VARCHAR(40)", false),
55	55	new DbColumn("Date_Time", "DATETIME", false),
56	56	new DbColumn("DB_Type", "ENUM('Emdros','SQL')",

Navigation

Changeset 213

Legend:

lex/trunk/src/com/qwirx/db/sql/SqlChange.java

lex/trunk/src/com/qwirx/db/sql/SqlDatabase.java

Download in other formats: