001: /*
002: * Copyright (C) 2007 Rob Manning
003: * manningr@users.sourceforge.net
004: *
005: * This library is free software; you can redistribute it and/or
006: * modify it under the terms of the GNU Lesser General Public
007: * License as published by the Free Software Foundation; either
008: * version 2.1 of the License, or (at your option) any later version.
009: *
010: * This library is distributed in the hope that it will be useful,
011: * but WITHOUT ANY WARRANTY; without even the implied warranty of
012: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
013: * Lesser General Public License for more details.
014: *
015: * You should have received a copy of the GNU Lesser General Public
016: * License along with this library; if not, write to the Free Software
017: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
018: */
019: package net.sourceforge.squirrel_sql.plugins.oracle.exception;
020:
021: import java.sql.CallableStatement;
022: import java.sql.Connection;
023: import java.sql.SQLException;
024: import java.sql.Statement;
025:
026: import net.sourceforge.squirrel_sql.client.session.ISQLEntryPanel;
027: import net.sourceforge.squirrel_sql.client.session.ISQLPanelAPI;
028: import net.sourceforge.squirrel_sql.client.session.ISession;
029: import net.sourceforge.squirrel_sql.client.session.event.ISessionListener;
030: import net.sourceforge.squirrel_sql.client.session.event.SessionAdapter;
031: import net.sourceforge.squirrel_sql.client.session.event.SessionEvent;
032: import net.sourceforge.squirrel_sql.fw.sql.ISQLConnection;
033: import net.sourceforge.squirrel_sql.fw.sql.SQLUtilities;
034: import net.sourceforge.squirrel_sql.fw.util.DefaultExceptionFormatter;
035: import net.sourceforge.squirrel_sql.fw.util.ExceptionFormatter;
036: import net.sourceforge.squirrel_sql.fw.util.StringManager;
037: import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory;
038: import net.sourceforge.squirrel_sql.fw.util.log.ILogger;
039: import net.sourceforge.squirrel_sql.fw.util.log.LoggerController;
040:
041: /**
042: * An ExceptionFormatter for Oracle which adds the position of the syntax error
043: * and moves the caret position to that error position. It does nothing else
044: * to format the exception, beyond what the default formatter does.
045: *
046: * @author manningr
047: */
048: public class OracleExceptionFormatter extends SessionAdapter implements
049: ISessionListener, ExceptionFormatter {
050:
051: /**
052: * Logger for this class.
053: */
054: private final static ILogger s_log = LoggerController
055: .createLogger(OracleExceptionFormatter.class);
056:
057: /**
058: * Internationalized strings for this class.
059: */
060: private static final StringManager s_stringMgr = StringManagerFactory
061: .getStringManager(OracleExceptionFormatter.class);
062:
063: private static interface i18n {
064: //i18n[OracleExceptionFormatter.positionLabel=Position: ]
065: String POSITION_LABEL = s_stringMgr
066: .getString("OracleExceptionFormatter.positionLabel");
067: }
068:
069: /** The session that this formatter is associated with */
070: private ISession _session = null;
071:
072: private DefaultExceptionFormatter formatter = new DefaultExceptionFormatter();
073:
074: private boolean offsetFunctionAvailable = false;
075:
076: /** Interface to allow us to set the caret position in the SQL editor */
077: private ISQLEntryPanel sqlEntryPanel = null;
078:
079: public OracleExceptionFormatter() {
080: }
081:
082: /**
083: * Sets the Oracle session that this formatter is associated with.
084: *
085: * @param session the session
086: */
087: public void setSession(ISession session) {
088: _session = session;
089: this .sqlEntryPanel = session
090: .getSQLPanelAPIOfActiveSessionWindow()
091: .getSQLEntryPanel();
092:
093: try {
094: if (!isOffsetFunctionAvailable()) {
095: if (initOffsetFunction()) {
096: offsetFunctionAvailable = true;
097: }
098: }
099: } catch (SQLException e) {
100: s_log.error("setSession: Unexpected exception - "
101: + e.getMessage(), e);
102: }
103: }
104:
105: /**
106: * The name to give to our function. Oracle doesn't support anonymous
107: * function blocks.
108: */
109: public static final String OFFSET_FUNCTION_NAME = "SQUIRREL_GET_ERROR_OFFSET";
110:
111: /**
112: * The syntax error offset function to use to determine where the error
113: * occurred in a SQL statement.
114: */
115: private static final String OFFSET_FUNCTION = "create or replace function "
116: + OFFSET_FUNCTION_NAME
117: + " (query IN varchar2) "
118: + "return number authid current_user "
119: + "is "
120: + " l_theCursor integer default dbms_sql.open_cursor; "
121: + " l_status integer; "
122: + "begin "
123: + " begin "
124: + " dbms_sql.parse( l_theCursor, query, dbms_sql.native ); "
125: + " exception "
126: + " when others then l_status := dbms_sql.last_error_position; "
127: + " end; "
128: + " dbms_sql.close_cursor( l_theCursor ); "
129: + " return l_status; " + "end; ";
130:
131: /**
132: * Pass through to default formatter, detecting the error position and
133: * setting the caret position appropriately.
134: *
135: * @see net.sourceforge.squirrel_sql.fw.util.ExceptionFormatter#format(java.lang.Throwable)
136: */
137: public String format(Throwable t) throws Exception {
138: StringBuilder result = new StringBuilder(formatter.format(t));
139: String sql = getCurrentSql();
140: if (sql != null) {
141: int position = getErrorPosition(sql);
142: if (position != -1) {
143: result.append("\n");
144: result.append(i18n.POSITION_LABEL);
145: result.append(position);
146:
147: int[] bounds = sqlEntryPanel
148: .getBoundsOfSQLToBeExecuted();
149: int start = bounds[0];
150: int newPosition = start + position;
151: sqlEntryPanel.setCaretPosition(newPosition);
152:
153: }
154: }
155: return result.toString();
156: }
157:
158: /**
159: * @see net.sourceforge.squirrel_sql.fw.util.ExceptionFormatter#formatsException(java.lang.Throwable)
160: */
161: public boolean formatsException(Throwable t) {
162: return true;
163: }
164:
165: private String getCurrentSql() {
166: String result = null;
167: ISQLPanelAPI api = _session
168: .getSQLPanelAPIOfActiveSessionWindow();
169: result = api.getSQLEntryPanel().getSQLToBeExecuted();
170: return result;
171: }
172:
173: private int getErrorPosition(String sql) throws SQLException {
174: int result = -1;
175: ISQLConnection sqlcon = _session.getSQLConnection();
176: Connection con = sqlcon.getConnection();
177: CallableStatement cstmt = null;
178:
179: try {
180: String callSql = "{?=call " + OFFSET_FUNCTION_NAME + "(?)}";
181: if (s_log.isDebugEnabled()) {
182: s_log.debug("getErrorPosition: Executing sql: "
183: + callSql);
184: s_log.debug("getErrorPosition: errant SQL was: " + sql);
185: }
186: cstmt = con.prepareCall(callSql);
187: cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
188: cstmt.setString(2, sql);
189: cstmt.execute();
190: result = cstmt.getInt(1);
191: } catch (SQLException e) {
192: s_log.error("getErrorPosition: Unexpected exception - "
193: + e.getMessage(), e);
194: } finally {
195: SQLUtilities.closeStatement(cstmt);
196: }
197: return result;
198: }
199:
200: private boolean initOffsetFunction() throws SQLException {
201: ISQLConnection sqlcon = _session.getSQLConnection();
202: Connection con = sqlcon.getConnection();
203: CallableStatement cstmt = null;
204: Statement stmt = null;
205: boolean result = true;
206: try {
207: stmt = con.createStatement();
208: if (s_log.isDebugEnabled()) {
209: s_log.debug("initOffsetFunction: Executing sql: "
210: + OFFSET_FUNCTION);
211: }
212:
213: stmt.executeUpdate(OFFSET_FUNCTION);
214: } catch (SQLException e) {
215: result = false;
216: s_log.error("initOffsetFunction: Unexpected exception - "
217: + e.getMessage(), e);
218:
219: } finally {
220: SQLUtilities.closeStatement(cstmt);
221: SQLUtilities.closeStatement(stmt);
222: }
223: return result;
224: }
225:
226: private boolean isOffsetFunctionAvailable() throws SQLException {
227: // Don't try to find it if we have already created it.
228: if (offsetFunctionAvailable) {
229: return true;
230: }
231: boolean result = false;
232: String[] functionNames = _session.getMetaData()
233: .getStringFunctions();
234: for (String functionName : functionNames) {
235: if (OFFSET_FUNCTION_NAME.equals(functionName)) {
236: if (s_log.isDebugEnabled()) {
237: s_log
238: .debug("isOffsetFunctionAvailable: Found offset "
239: + "function: "
240: + OFFSET_FUNCTION_NAME);
241: }
242: result = true;
243: break;
244: }
245: }
246: if (s_log.isDebugEnabled()) {
247: s_log
248: .debug("isOffsetFunctionAvailable: Couldn't locate offset "
249: + "function: " + OFFSET_FUNCTION_NAME);
250: }
251: return result;
252: }
253:
254: // ISessionListener interface methods
255:
256: /*
257: * Since we depend upon the Connection class associated with the ISession,
258: * we need to keep a reference to the ISession we are associated with.
259: * However, this session could be closed, at which time we want to give up
260: * our reference so that it can be garbage collected.
261: */
262:
263: /**
264: * @see net.sourceforge.squirrel_sql.client.session.event.ISessionListener#allSessionsClosed()
265: */
266: public void allSessionsClosed() {
267: _session.getApplication().getSessionManager()
268: .removeSessionListener(this );
269: _session = null;
270: }
271:
272: /**
273: * @see net.sourceforge.squirrel_sql.client.session.event.ISessionListener#sessionClosed(net.sourceforge.squirrel_sql.client.session.event.SessionEvent)
274: */
275: public void sessionClosed(SessionEvent evt) {
276: if (evt.getSession() == _session) {
277: _session.getApplication().getSessionManager()
278: .removeSessionListener(this );
279: _session = null;
280: }
281: }
282:
283: /**
284: * @see net.sourceforge.squirrel_sql.client.session.event.ISessionListener#sessionClosing(net.sourceforge.squirrel_sql.client.session.event.SessionEvent)
285: */
286: public void sessionClosing(SessionEvent evt) {
287: if (evt.getSession() == _session) {
288: _session.getApplication().getSessionManager()
289: .removeSessionListener(this);
290: _session = null;
291: }
292: }
293:
294: }
|