public override ProcedureSchemaCollection GetProcedures()
{
ProcedureSchemaCollection procedures = new ProcedureSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(
"SELECT pc.proname, pc.oid::integer, pl.lanname, pc.prosrc "
+ "FROM "
+ " pg_proc pc, "
+ " pg_user pu, "
+ " pg_type pt, "
+ " pg_language pl "
+ "WHERE pc.proowner = pu.usesysid "
+ "AND pc.prorettype = pt.oid "
+ "AND pc.prolang = pl.oid "
+ "UNION "
+ "SELECT pc.proname, pt.oid::integer, pl.lanname, pc.prosrc "
+ "FROM "
+ " pg_proc pc, "
+ " pg_user pu, "
+ " pg_type pt, "
+ " pg_language pl "
+ "WHERE pc.proowner = pu.usesysid "
+ "AND pc.prorettype = 0 "
+ "AND pc.prolang = pl.oid;"
);
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ProcedureSchema procedure = new ProcedureSchema(this);
procedure.Name = r.GetString(0);
procedure.Definition = r.GetString(3);
procedure.LanguageName = r.GetString(2);
if (!r.IsDBNull(1) && r.GetInt32(1) <= LastSystemOID)
{
procedure.IsSystemProcedure = true;
}
procedures.Add(procedure);
}
r.Close();
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(procedures);
}
// see: http://dev.mysql.com/doc/refman/5.1/en/routines-table.html
public override ProcedureSchemaCollection GetProcedures()
{
ProcedureSchemaCollection procedures = new ProcedureSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(
"SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA ='"
+ ConnectionPool.ConnectionContext.ConnectionSettings.Database +
"' ORDER BY ROUTINE_NAME"
);
try {
using (command) {
if (GetMainVersion(command) >= 5)
{
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ProcedureSchema procedure = new ProcedureSchema(this);
procedure.Name = r.GetString(0);
procedure.OwnerName = r.GetString(1);
procedure.IsSystemProcedure = r.GetString(2).ToLower().Contains("system");
IPooledDbConnection conn2 = connectionPool.Request();
IDbCommand command2 = conn2.CreateCommand("SHOW CREATE PROCEDURE `" + procedure.Name + "`;");
using (IDataReader r2 = command2.ExecuteReader()) {
r2.Read();
procedure.Definition = r2.GetString(2);
}
conn2.Release();
procedures.Add(procedure);
}
r.Close();
}
} //else: do nothing, since procedures are only supported since mysql 5.x
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(procedures);
}
public override ProcedureSchemaCollection GetProcedures()
{
ProcedureSchemaCollection procedures = new ProcedureSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(
"SELECT su.name AS owner, so.name as proc_name, so.id as proc_id, " +
" so.crdate as created_date, so.xtype as proc_type " +
"FROM dbo.sysobjects so, dbo.sysusers su " +
"WHERE xtype = 'P' " +
"AND su.uid = so.uid " +
"ORDER BY 1, 2"
);
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ProcedureSchema procedure = new ProcedureSchema(this);
procedure.Name = r.GetString(1);
procedure.OwnerName = r.GetString(0);
procedure.LanguageName = "TSQL";
StringBuilder sb = new StringBuilder();
sb.AppendFormat("-- Procedure: {0}\n", procedure.Name);
sb.AppendFormat(" {0}\n);", GetSource("[" + procedure.OwnerName + "].[" + procedure.Name + "]"));
procedure.Definition = sb.ToString();
// FIXME : get sysproc or not
procedures.Add(procedure);
}
r.Close();
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(procedures);
}
public virtual ProcedureSchemaCollection GetProcedures()
{
ProcedureSchemaCollection collection = new ProcedureSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
try {
//restrictions: database, schema, name, type
DataTable dt = conn.GetSchema(proceduresCollectionString, null, connectionPool.ConnectionContext.ConnectionSettings.Database);
for (int r = 0; r < dt.Rows.Count; r++)
{
DataRow row = dt.Rows[r];
collection.Add(GetProcedure(row));
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(collection);
}