001: /*
002: * JdbcProcedureReader.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.db;
013:
014: import java.sql.DatabaseMetaData;
015: import java.sql.ResultSet;
016: import java.sql.SQLException;
017: import java.sql.Savepoint;
018: import java.sql.Statement;
019: import java.sql.Types;
020: import workbench.log.LogMgr;
021: import workbench.resource.Settings;
022: import workbench.sql.DelimiterDefinition;
023: import workbench.storage.DataStore;
024: import workbench.util.ExceptionUtil;
025: import workbench.util.SqlUtil;
026: import workbench.util.StringUtil;
027:
028: /**
029: * Retrieve information about stored procedures from the database.
030: * To retrieve the source of the Stored procedure, SQL statements need
031: * to be defined in the ProcSourceStatements.xml
032: *
033: * @see workbench.db.MetaDataSqlManager
034: *
035: * @author support@sql-workbench.net
036: */
037: public class JdbcProcedureReader implements ProcedureReader {
038: protected WbConnection connection;
039: protected boolean useSavepoint = false;
040:
041: public JdbcProcedureReader(WbConnection conn) {
042: this .connection = conn;
043: }
044:
045: public StringBuilder getProcedureHeader(String catalog,
046: String schema, String procName, int procType) {
047: return StringUtil.emptyBuffer();
048: }
049:
050: /**
051: * Checks if the given procedure exists in the database
052: */
053: public boolean procedureExists(String catalog, String schema,
054: String procName, int procType) {
055: boolean exists = false;
056: ResultSet rs = null;
057: Savepoint sp = null;
058: try {
059: if (useSavepoint) {
060: sp = this .connection.setSavepoint();
061: }
062: rs = this .connection.getSqlConnection().getMetaData()
063: .getProcedures(catalog, schema, procName);
064: if (rs.next()) {
065: int type = rs.getInt(8);
066: if (type == DatabaseMetaData.procedureResultUnknown
067: || procType == DatabaseMetaData.procedureResultUnknown
068: || type == procType) {
069: exists = true;
070: }
071: }
072: this .connection.releaseSavepoint(sp);
073: } catch (Exception e) {
074: this .connection.rollback(sp);
075: LogMgr.logError("JdbcProcedureReader.procedureExists()",
076: "Error checking procedure", e);
077: } finally {
078: SqlUtil.closeResult(rs);
079: }
080: return exists;
081: }
082:
083: public DataStore getProcedures(String aCatalog, String aSchema)
084: throws SQLException {
085: if ("*".equals(aSchema) || "%".equals(aSchema)) {
086: aSchema = null;
087: }
088:
089: Savepoint sp = null;
090: try {
091: if (useSavepoint) {
092: sp = this .connection.setSavepoint();
093: }
094: ResultSet rs = this .connection.getSqlConnection()
095: .getMetaData()
096: .getProcedures(aCatalog, aSchema, "%");
097: DataStore ds = fillProcedureListDataStore(rs);
098: this .connection.releaseSavepoint(sp);
099: return ds;
100: } catch (SQLException sql) {
101: this .connection.rollback(sp);
102: throw sql;
103: }
104: }
105:
106: public DataStore buildProcedureListDataStore(DbMetadata meta) {
107: String[] cols = new String[] { "PROCEDURE_NAME", "TYPE",
108: meta.getCatalogTerm().toUpperCase(),
109: meta.getSchemaTerm().toUpperCase(), "REMARKS" };
110: final int types[] = { Types.VARCHAR, Types.VARCHAR,
111: Types.VARCHAR, Types.VARCHAR, Types.VARCHAR };
112: final int sizes[] = { 30, 12, 10, 10, 20 };
113:
114: DataStore ds = new DataStore(cols, types, sizes);
115: return ds;
116: }
117:
118: public DataStore fillProcedureListDataStore(ResultSet rs)
119: throws SQLException {
120: DataStore ds = buildProcedureListDataStore(this .connection
121: .getMetadata());
122:
123: try {
124: while (rs.next()) {
125: String cat = rs.getString("PROCEDURE_CAT");
126: String schema = rs.getString("PROCEDURE_SCHEM");
127: String name = rs.getString("PROCEDURE_NAME");
128: String remark = rs.getString("REMARKS");
129: short type = rs.getShort("PROCEDURE_TYPE");
130: Integer iType = null;
131: if (rs.wasNull()) {
132: //sType = "N/A";
133: iType = new Integer(
134: DatabaseMetaData.procedureResultUnknown);
135: } else {
136: iType = new Integer(type);
137: }
138: int row = ds.addRow();
139:
140: ds.setValue(row,
141: ProcedureReader.COLUMN_IDX_PROC_LIST_CATALOG,
142: cat);
143: ds.setValue(row,
144: ProcedureReader.COLUMN_IDX_PROC_LIST_SCHEMA,
145: schema);
146: ds.setValue(row,
147: ProcedureReader.COLUMN_IDX_PROC_LIST_NAME,
148: stripVersionInfo(name));
149: ds.setValue(row,
150: ProcedureReader.COLUMN_IDX_PROC_LIST_TYPE,
151: iType);
152: ds.setValue(row,
153: ProcedureReader.COLUMN_IDX_PROC_LIST_REMARKS,
154: remark);
155: }
156: } catch (Exception e) {
157: LogMgr.logError("JdbcProcedureReader.getProcedures()",
158: "Error while retrieving procedures", e);
159: } finally {
160: SqlUtil.closeResult(rs);
161: }
162: return ds;
163: }
164:
165: public static String convertProcType(int type) {
166: if (type == DatabaseMetaData.procedureNoResult)
167: return ProcedureReader.PROC_RESULT_NO;
168: else if (type == DatabaseMetaData.procedureReturnsResult)
169: return ProcedureReader.PROC_RESULT_YES;
170: else
171: return ProcedureReader.PROC_RESULT_UNKNOWN;
172: }
173:
174: protected DataStore createProcColsDataStore() {
175: final String cols[] = { "COLUMN_NAME", "TYPE", "TYPE_NAME",
176: "java.sql.Types", "REMARKS" };
177: final int types[] = { Types.VARCHAR, Types.VARCHAR,
178: Types.VARCHAR, Types.INTEGER, Types.VARCHAR };
179: final int sizes[] = { 20, 10, 18, 5, 30 };
180: DataStore ds = new DataStore(cols, types, sizes);
181: return ds;
182: }
183:
184: private String stripVersionInfo(String procname) {
185: DbSettings dbS = this .connection.getMetadata().getDbSettings();
186: String versionDelimiter = dbS.getProcVersionDelimiter();
187: if (StringUtil.isEmptyString(versionDelimiter))
188: return procname;
189: int pos = procname.lastIndexOf(versionDelimiter);
190: if (pos < 0)
191: return procname;
192: return procname.substring(0, pos);
193: }
194:
195: public DataStore getProcedureColumns(String aCatalog,
196: String aSchema, String aProcname) throws SQLException {
197: DataStore ds = createProcColsDataStore();
198: ResultSet rs = null;
199: Savepoint sp = null;
200: try {
201: if (useSavepoint) {
202: sp = this .connection.setSavepoint();
203: }
204: rs = this .connection.getSqlConnection().getMetaData()
205: .getProcedureColumns(aCatalog, aSchema, aProcname,
206: "%");
207: while (rs.next()) {
208: int row = ds.addRow();
209: String colName = rs.getString("COLUMN_NAME");
210: ds
211: .setValue(
212: row,
213: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_COL_NAME,
214: colName);
215: int colType = rs.getInt("COLUMN_TYPE");
216: String stype;
217:
218: switch (colType) {
219: case DatabaseMetaData.procedureColumnIn:
220: stype = "IN";
221: break;
222: case DatabaseMetaData.procedureColumnOut:
223: stype = "OUT";
224: break;
225: case DatabaseMetaData.procedureColumnResult:
226: stype = "RESULTSET";
227: break;
228: case DatabaseMetaData.procedureColumnReturn:
229: stype = "RETURN";
230: break;
231: default:
232: stype = "";
233: }
234: ds
235: .setValue(
236: row,
237: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_RESULT_TYPE,
238: stype);
239:
240: int sqlType = rs.getInt("DATA_TYPE");
241: String typeName = rs.getString("TYPE_NAME");
242: int digits = rs.getInt("PRECISION");
243: int size = rs.getInt("LENGTH");
244: String rem = rs.getString("REMARKS");
245:
246: String display = SqlUtil.getSqlTypeDisplay(typeName,
247: sqlType, size, digits);
248: ds
249: .setValue(
250: row,
251: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_DATA_TYPE,
252: display);
253: ds
254: .setValue(
255: row,
256: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_JDBC_DATA_TYPE,
257: sqlType);
258: ds
259: .setValue(
260: row,
261: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_REMARKS,
262: rem);
263: }
264: this .connection.releaseSavepoint(sp);
265: } catch (SQLException sql) {
266: this .connection.rollback(sp);
267: throw sql;
268: } finally {
269: SqlUtil.closeResult(rs);
270: }
271:
272: return ds;
273: }
274:
275: public void readProcedureSource(ProcedureDefinition def)
276: throws NoConfigException {
277: if (def == null)
278: return;
279:
280: GetMetaDataSql sql = this .connection.getMetadata().metaSqlMgr
281: .getProcedureSourceSql();
282: if (sql == null) {
283: throw new NoConfigException();
284: }
285:
286: String procName = stripVersionInfo(def.getProcedureName());
287:
288: StringBuilder source = new StringBuilder(500);
289:
290: StringBuilder header = getProcedureHeader(def.getCatalog(), def
291: .getSchema(), procName, def.getResultType());
292: source.append(header);
293:
294: Statement stmt = null;
295: ResultSet rs = null;
296: Savepoint sp = null;
297:
298: int linecount = 0;
299:
300: try {
301: if (useSavepoint) {
302: sp = this .connection.setSavepoint();
303: }
304: sql.setSchema(def.getSchema());
305: sql.setObjectName(procName);
306: sql.setCatalog(def.getCatalog());
307: if (Settings.getInstance().getDebugMetadataSql()) {
308: LogMgr.logInfo("DbMetadata.getProcedureSource()",
309: "Using query=\n" + sql.getSql());
310: }
311:
312: stmt = this .connection.createStatement();
313: rs = stmt.executeQuery(sql.getSql());
314: while (rs.next()) {
315: String line = rs.getString(1);
316: if (line != null) {
317: linecount++;
318: source.append(line);
319: }
320: }
321: this .connection.releaseSavepoint(sp);
322: } catch (SQLException e) {
323: if (sp != null)
324: this .connection.rollback(sp);
325: LogMgr.logError("JdbcProcedureReader.getProcedureSource()",
326: "Error retrieving procedure source", e);
327: source = new StringBuilder(ExceptionUtil.getDisplay(e));
328: this .connection.rollback(sp);
329: } finally {
330: SqlUtil.closeAll(rs, stmt);
331: }
332:
333: boolean needsTerminator = this .connection.getDbSettings()
334: .proceduresNeedTerminator();
335: DelimiterDefinition delimiter = Settings.getInstance()
336: .getAlternateDelimiter(connection);
337: if (!StringUtil.endsWith(source, delimiter.getDelimiter())
338: && needsTerminator) {
339: if (delimiter.isSingleLine())
340: source.append('\n');
341: source.append(delimiter.getDelimiter());
342: if (delimiter.isSingleLine())
343: source.append('\n');
344: }
345:
346: String result = source.toString();
347:
348: String dbId = this .connection.getMetadata().getDbId();
349: boolean replaceNL = Settings.getInstance().getBoolProperty(
350: "workbench.db." + dbId + ".replacenl.proceduresource",
351: false);
352:
353: if (replaceNL) {
354: String nl = Settings.getInstance()
355: .getInternalEditorLineEnding();
356: result = StringUtil.replace(source.toString(), "\\n", nl);
357: }
358:
359: def.setSource(result);
360: }
361:
362: }
|