20220621_02.md

How to list the store procedures and their DDLs in SAP ASE

作者

iihero

日期

2022-06-21

标签

SAP-ASE, DatabaseManagement

背景

When using ASE database, in some scenarios, we need list all the store procedures and dump their DDLs in java or some other programming language.

1. Get Store Procedure List

1.1 Using Java Code

		res = new ArrayList<>();
		try {
			log.info("catalog: {}, schema: {}", catalog, schema);
                        DatabaseMetaData meta = conn.getMetaData();
			ResultSet rset = meta.getProcedures(catalog, schema, null);
			String name = "";
			while (rset.next()) {
				// column 3 is the procedure name
				name = rset.getString("PROCEDURE_NAME");
				name = StringUtils.substringBefore(name, ";");
				res.add(name);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

We need remove some prefix in the name. There are some procedures with some prefixes there.

1.2. Using ASE related query statement:

select a.name from sysobjects a, sysusers b 
where a.type='P' and a.uid=b.uid and b.name=user_name();

Just call this SQL to get the full ResultSet.

2. Dump the DDL of the Target Store Procedure

There is no standard JDBC call to get the store procedure content. But we still be use ASE related SQL to get it.

2.1 using sp_help <proc_name>

Via this, normally, we can get it from command line. It can only get the parameter metadata information of the procedure.

2.2 use java code to call one SQL and concat the result set

public static String getProcDDL(Connection conn, DBMS dbType, String proc) {
		String sql = "select c.text from syscomments c, sysobjects o where o.id=c.id and o.name=? order by c.colid";
		StringBuilder sb = new StringBuilder(1024);
		if (dbType.equals(DBMS.ASE)) {
			try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
				pstmt.setString(1, proc);
				ResultSet rset = pstmt.executeQuery();
				while (rset.next()) {
					sb.append(rset.getString(1));
				}
				rset.close();
				return sb.toString();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

Here, we need use: order by c.colid to ensure the code line is ordered for the DDL.

Via this way, we can dump all the procedures and its DDL and exported to another schema for ASE database.