001: /*
002: * MySqlProcedureReader.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.mysql;
013:
014: import java.sql.PreparedStatement;
015: import java.sql.ResultSet;
016: import workbench.db.JdbcProcedureReader;
017: import workbench.db.ProcedureReader;
018: import workbench.db.WbConnection;
019: import workbench.log.LogMgr;
020: import workbench.resource.Settings;
021: import workbench.storage.DataStore;
022: import workbench.util.SqlUtil;
023: import workbench.util.StringUtil;
024:
025: /**
026: * @author support@sql-workbench.net
027: */
028: public class MySqlProcedureReader extends JdbcProcedureReader {
029: public MySqlProcedureReader(WbConnection con) {
030: super (con);
031: }
032:
033: public StringBuilder getProcedureHeader(String aCatalog,
034: String aSchema, String aProcname, int procType) {
035: StringBuilder source = new StringBuilder(150);
036:
037: String sql = "SELECT routine_type, dtd_identifier "
038: + "FROM information_schema.routines "
039: + " WHERE routine_schema like ? "
040: + " and routine_name = ? ";
041:
042: String nl = Settings.getInstance()
043: .getInternalEditorLineEnding();
044: PreparedStatement stmt = null;
045: ResultSet rs = null;
046: try {
047: stmt = this .connection.getSqlConnection().prepareStatement(
048: sql);
049: stmt.setString(1, (aSchema == null ? "%" : aSchema));
050: stmt.setString(2, aProcname);
051: rs = stmt.executeQuery();
052: String proctype = "PROCEDURE";
053: String returntype = "";
054: if (rs.next()) {
055: proctype = rs.getString(1);
056: returntype = rs.getString(2);
057: }
058: source.append("DROP ");
059: source.append(proctype);
060: source.append(' ');
061: source.append(aProcname);
062: source.append(';');
063: source.append(nl);
064: source.append(nl);
065: source.append("CREATE ");
066: source.append(proctype);
067: source.append(' ');
068: source.append(aProcname);
069: source.append(" (");
070:
071: DataStore ds = this .getProcedureColumns(aCatalog, aSchema,
072: aProcname);
073: int count = ds.getRowCount();
074: int added = 0;
075: for (int i = 0; i < count; i++) {
076: String ret = ds
077: .getValueAsString(
078: i,
079: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_RESULT_TYPE);
080: if (ret.equals("RETURN") || ret.equals("RESULTSET"))
081: continue;
082: String vartype = ds
083: .getValueAsString(
084: i,
085: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_DATA_TYPE);
086: String name = ds
087: .getValueAsString(
088: i,
089: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_COL_NAME);
090: if (added > 0)
091: source.append(',');
092: source.append(ret);
093: source.append(' ');
094: source.append(name);
095: source.append(' ');
096: source.append(vartype);
097: added++;
098: }
099: source.append(')');
100: source.append(nl);
101: if ("FUNCTION".equals(proctype)) {
102: source.append("RETURNS ");
103: source.append(returntype);
104: source.append(nl);
105: }
106: } catch (Exception e) {
107: LogMgr.logError("MySqlMetadata.getProcedureHeader()",
108: "Error retrieving procedure header", e);
109: source = StringUtil.emptyBuffer();
110: } finally {
111: SqlUtil.closeAll(rs, stmt);
112: }
113: return source;
114: }
115:
116: }
|