001: /*
002: * WbCall.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.sql.wbcommands;
013:
014: import java.io.IOException;
015: import java.sql.CallableStatement;
016: import java.sql.ParameterMetaData;
017: import java.sql.ResultSet;
018: import java.sql.SQLException;
019: import java.sql.Savepoint;
020: import java.sql.Types;
021: import java.util.ArrayList;
022: import java.util.LinkedList;
023: import java.util.List;
024: import workbench.db.DbMetadata;
025: import workbench.db.ProcedureReader;
026: import workbench.log.LogMgr;
027: import workbench.util.ExceptionUtil;
028: import workbench.resource.ResourceMgr;
029: import workbench.sql.SqlCommand;
030: import workbench.sql.StatementRunnerResult;
031: import workbench.sql.formatter.SQLLexer;
032: import workbench.sql.formatter.SQLToken;
033: import workbench.storage.DataStore;
034: import workbench.util.NumberStringCache;
035: import workbench.util.SqlUtil;
036: import workbench.util.StringUtil;
037:
038: /**
039: * Support for running stored procedures that have OUT parameters. For this
040: * command to work properly the JDBC driver needs to either implement
041: * CallableStatement.getParameterMetaData() correctly, or return proper information
042: * about the columns of a procedure using DatabaseMetaData.getProcedureColumns()
043: *
044: * @author support@sql-workbench.net
045: */
046: public class WbCall extends SqlCommand {
047: public static final String EXEC_VERB_SHORT = "EXEC";
048: public static final String EXEC_VERB_LONG = "EXECUTE";
049: public static final String VERB = "WBCALL";
050: private List<Integer> refCursorIndex = null;
051:
052: public WbCall() {
053: }
054:
055: public String getVerb() {
056: return VERB;
057: }
058:
059: private String getSqlToPrepare(String cleanSql, boolean funcCall) {
060: if (funcCall)
061: return "{ ? = call " + cleanSql + "}";
062: return "{call " + cleanSql + "}";
063: }
064:
065: /**
066: * Converts the passed sql to an Oracle compliant JDBC call and
067: * runs the statement.
068: */
069: public StatementRunnerResult execute(String aSql)
070: throws SQLException, Exception {
071: StatementRunnerResult result = new StatementRunnerResult(aSql);
072:
073: String cleanSql = SqlUtil.stripVerb(aSql);
074: String realSql = getSqlToPrepare(cleanSql, false);
075:
076: try {
077: ArrayList<String> parameterNames = null;
078: refCursorIndex = null;
079:
080: result.addMessage(ResourceMgr
081: .getString("MsgProcCallConverted")
082: + " " + realSql);
083: CallableStatement cstmt = currentConnection
084: .getSqlConnection().prepareCall(realSql);
085: this .currentStatement = cstmt;
086:
087: boolean hasParameters = (realSql.indexOf('?') > -1);
088:
089: Savepoint sp = null;
090: if (hasParameters) {
091: try {
092: if (currentConnection.getDbSettings()
093: .useSavePointForDDL()) {
094: sp = currentConnection.setSavepoint();
095: }
096: parameterNames = checkParametersFromStatement(cstmt);
097: currentConnection.releaseSavepoint(sp);
098: } catch (Throwable e) {
099: // Some drivers do not work properly if this happens, so
100: // we have to close and re-open the statement
101: LogMgr.logWarning("WbCall.execute()",
102: "Could not get parameters from statement!",
103: e);
104: SqlUtil.closeStatement(cstmt);
105: currentConnection.rollback(sp);
106: } finally {
107: sp = null;
108: }
109: }
110:
111: // The called "procedure" could also be a function
112: if (parameterNames == null || parameterNames.size() == 0) {
113: // checkParametersFromDatabase will re-create the callable statement
114: // and assign it to currentStatement
115: // This is necessary to avoid having two statements open on the same
116: // connection as some jdbc drivers do not like this
117: try {
118: if (currentConnection.getDbSettings()
119: .useSavePointForDDL()) {
120: sp = currentConnection.setSavepoint();
121: }
122: parameterNames = checkParametersFromDatabase(cleanSql);
123: if (this .currentStatement != null) {
124: cstmt = (CallableStatement) currentStatement;
125: }
126: currentConnection.releaseSavepoint(sp);
127: } catch (Throwable e) {
128: LogMgr.logError("WbCall.execute()",
129: "Error during procedure check", e);
130: currentConnection.rollback(sp);
131: } finally {
132: sp = null;
133: }
134: }
135:
136: boolean hasResult = (cstmt != null ? cstmt.execute()
137: : false);
138: result.setSuccess();
139:
140: int startColumn = 1;
141:
142: if (refCursorIndex != null) {
143: for (Integer index : refCursorIndex) {
144: try {
145: ResultSet rs = (ResultSet) cstmt
146: .getObject(index.intValue());
147:
148: // processResults will close the result set
149: if (rs != null)
150: processResults(result, true, rs);
151: startColumn++;
152: } catch (Exception e) {
153: result.addMessage(ExceptionUtil.getDisplay(e));
154: }
155: }
156: } else {
157: processResults(result, hasResult);
158: }
159:
160: // Now process all single-value out parameters
161: if (parameterNames != null
162: && parameterNames.size() >= startColumn) {
163: String[] cols = new String[] { "PARAMETER", "VALUE" };
164: int[] types = new int[] { Types.VARCHAR, Types.VARCHAR };
165: int[] sizes = new int[] { 35, 35 };
166:
167: DataStore resultData = new DataStore(cols, types, sizes);
168: for (int i = startColumn; i <= parameterNames.size(); i++) {
169: if (refCursorIndex != null
170: && refCursorIndex.contains(new Integer(i)))
171: continue;
172:
173: Object parmValue = cstmt.getObject(i);
174: if (parmValue instanceof ResultSet) {
175: processResults(result, true,
176: (ResultSet) parmValue);
177: } else {
178: int row = resultData.addRow();
179: resultData.setValue(row, 0, parameterNames
180: .get(i - 1));
181: resultData.setValue(row, 1,
182: parmValue == null ? "NULL" : parmValue
183: .toString());
184: }
185: }
186: result.addDataStore(resultData);
187: }
188: } catch (Exception e) {
189: LogMgr.logError("WbOraExcecute.execute()",
190: "Error calling stored procedure", e);
191: result.addMessage(ResourceMgr.getString("MsgExecuteError"));
192: result.addMessage(ExceptionUtil.getDisplay(e));
193: result.setFailure();
194: } finally {
195: done();
196: }
197:
198: return result;
199: }
200:
201: public void done() {
202: super .done();
203: if (this .refCursorIndex != null)
204: this .refCursorIndex.clear();
205: this .refCursorIndex = null;
206: }
207:
208: private ArrayList<String> checkParametersFromStatement(
209: CallableStatement cstmt) throws SQLException {
210: ArrayList<String> parameterNames = null;
211:
212: ParameterMetaData parmData = cstmt.getParameterMetaData();
213: if (parmData != null) {
214: parameterNames = new ArrayList<String>();
215: int parameterCount = 0;
216: for (int i = 0; i < parmData.getParameterCount(); i++) {
217: int type = parmData.getParameterType(i + 1);
218: if (type == ParameterMetaData.parameterModeOut
219: || type == ParameterMetaData.parameterModeInOut) {
220: parameterCount++;
221: cstmt.registerOutParameter(parameterCount, type);
222: parameterNames.add("$"
223: + NumberStringCache.getNumberString(i + 1));
224: }
225: }
226: }
227:
228: return parameterNames;
229: }
230:
231: private ArrayList<String> checkParametersFromDatabase(String sql)
232: throws SQLException {
233: // Try to get the parameter information directly from the procedure definition
234: SQLLexer l = new SQLLexer(sql);
235: SQLToken t = l.getNextToken(false, false);
236:
237: // the first token could also be a schema/user name
238: String schema = null;
239: String procname = null;
240:
241: try {
242: SQLToken n = l.getNextToken(false, false);
243: if (n != null && n.getContents().equals(".")) {
244: n = l.getNextToken();
245: procname = (n == null ? "" : n.getContents());
246: schema = (t == null ? "" : t.getContents());
247: } else {
248: procname = (t == null ? "" : t.getContents());
249: }
250: } catch (IOException e) {
251: LogMgr.logError("WbCall.checkParametersFromDatabase",
252: "Error checking SQL", e);
253: return null;
254: }
255:
256: DbMetadata meta = this .currentConnection.getMetadata();
257: ArrayList<String> parameterNames = null;
258:
259: DataStore params = meta.getProcedureColumns(null, meta
260: .adjustSchemaNameCase(schema), meta
261: .adjustObjectnameCase(procname));
262:
263: boolean needFuncCall = meta.isPostgres()
264: && returnsRefCursor(params);
265: CallableStatement cstmt = currentConnection.getSqlConnection()
266: .prepareCall(getSqlToPrepare(sql, needFuncCall));
267: this .currentStatement = cstmt;
268:
269: int parameterCount = 0;
270:
271: if (params.getRowCount() > 0) {
272: parameterNames = new ArrayList<String>(params.getRowCount());
273: for (int i = 0; i < params.getRowCount(); i++) {
274: int dataType = params
275: .getValueAsInt(
276: i,
277: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_JDBC_DATA_TYPE,
278: -1);
279: String typeName = params
280: .getValueAsString(
281: i,
282: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_DATA_TYPE);
283: String resultType = params
284: .getValueAsString(
285: i,
286: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_RESULT_TYPE);
287: if (StringUtil.equalString(resultType, "OUT")
288: || (needFuncCall && StringUtil.equalString(
289: resultType, "RETURN"))) {
290: parameterCount++;
291: parameterNames
292: .add(params
293: .getValueAsString(
294: i,
295: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_COL_NAME));
296: if (isRefCursor(typeName)) {
297: // these parameters should not be added to the regular parameter list
298: // as they have to be retrieved in a different manner.
299: // type == -10 is Oracles CURSOR Datatype
300: int newType = currentConnection.getDbSettings()
301: .getRefCursorDataType();
302: if (newType != Integer.MIN_VALUE)
303: dataType = newType;
304: if (refCursorIndex == null) {
305: refCursorIndex = new LinkedList<Integer>();
306: }
307: refCursorIndex.add(new Integer(parameterCount));
308: }
309: cstmt
310: .registerOutParameter(parameterCount,
311: dataType);
312: }
313: }
314: }
315:
316: return parameterNames;
317: }
318:
319: private boolean isRefCursor(String type) {
320: String dbType = currentConnection.getDbSettings()
321: .getRefCursorTypeName();
322: return StringUtil.equalString(type, dbType);
323: }
324:
325: private boolean returnsRefCursor(DataStore params) {
326: // A function in Postgres that returns a refcursor
327: // mus be called using { ? = call('procname')} in order
328: // to be able to retrieve the result set from the refcursor
329: for (int i = 0; i < params.getRowCount(); i++) {
330: String typeName = params.getValueAsString(i,
331: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_DATA_TYPE);
332: String resultType = params
333: .getValueAsString(
334: i,
335: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_RESULT_TYPE);
336: if (isRefCursor(typeName) && "RETURN".equals(resultType))
337: return true;
338: }
339: return false;
340: }
341: }
|