public override ProcedureSchemaCollection GetProcedures()
{
ProcedureSchemaCollection procedures = new ProcedureSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
using (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 (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";
procedure.Definition = GetSource("[" + procedure.OwnerName + "].[" + procedure.Name + "]");
if (Array.Exists(system_procs, delegate(string s) { return(s == procedure.Name); }))
{
procedure.IsSystemProcedure = true;
}
else
{
procedure.IsSystemProcedure = false;
}
procedures.Add(procedure);
}
r.Close();
}
} catch (Exception e) {
QueryService.RaiseException(e);
}finally {
conn.Release();
}
}
return(procedures);
}
private void BuildChildNodesThreaded(object state)
{
BaseNode node = state as BaseNode;
ITreeBuilder builder = Context.GetTreeBuilder(state);
bool showSystemObjects = (bool)builder.Options["ShowSystemObjects"];
ProcedureSchemaCollection procedures = node.ConnectionContext.SchemaProvider.GetProcedures();
DispatchService.GuiDispatch(delegate {
foreach (ProcedureSchema procedure in procedures)
{
if (procedure.IsSystemProcedure && !showSystemObjects)
{
continue;
}
builder.AddChild(new ProcedureNode(node.ConnectionContext, procedure));
}
builder.Expanded = true;
});
}
// see: http://dev.mysql.com/doc/refman/5.1/en/routines-table.html
public override ProcedureSchemaCollection GetProcedures ()
{
ProcedureSchemaCollection procedures = new ProcedureSchemaCollection ();
using (IPooledDbConnection conn = connectionPool.Request ()) {
IPooledDbConnection conn2 = connectionPool.Request ();
IDbCommand command = conn.CreateCommand (string.Concat (
@"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).IndexOf ("system", StringComparison.OrdinalIgnoreCase) > -1);
procedure.IsFunction = (r.GetString (2).IndexOf ("function", StringComparison.OrdinalIgnoreCase) > -1);
IDbCommand command2;
if (!procedure.IsFunction)
command2 = conn2.CreateCommand (string.Concat ("SHOW CREATE PROCEDURE `",
procedure.Name, "`;"));
else
command2 = conn2.CreateCommand (string.Concat("SHOW CREATE FUNCTION `",
procedure.Name , "`;"));
using (IDataReader r2 = command2.ExecuteReader())
if (r2.Read ())
procedure.Definition = r2.GetString (2);
procedures.Add (procedure);
}
r.Close ();
}
} //else: do nothing, since procedures are only supported since mysql 5.x
}
} catch (Exception e) {
QueryService.RaiseException (e);
} finally {
conn.Release ();
if (conn2 != null)
conn2.Release ();
}
}
return procedures;
}
// see: http://dev.mysql.com/doc/refman/5.1/en/routines-table.html
public override ProcedureSchemaCollection GetProcedures()
{
ProcedureSchemaCollection procedures = new ProcedureSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
IPooledDbConnection conn2 = connectionPool.Request();
IDbCommand command = conn.CreateCommand(string.Concat(
@"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).IndexOf("system", StringComparison.OrdinalIgnoreCase) > -1);
procedure.IsFunction = (r.GetString(2).IndexOf("function", StringComparison.OrdinalIgnoreCase) > -1);
IDbCommand command2;
if (!procedure.IsFunction)
{
command2 = conn2.CreateCommand(string.Concat("SHOW CREATE PROCEDURE `",
procedure.Name, "`;"));
}
else
{
command2 = conn2.CreateCommand(string.Concat("SHOW CREATE FUNCTION `",
procedure.Name, "`;"));
}
using (IDataReader r2 = command2.ExecuteReader())
if (r2.Read())
{
procedure.Definition = r2.GetString(2);
}
procedures.Add(procedure);
}
r.Close();
}
} //else: do nothing, since procedures are only supported since mysql 5.x
}
} catch (Exception e) {
QueryService.RaiseException(e);
} finally {
conn.Release();
if (conn2 != null)
{
conn2.Release();
}
}
}
return(procedures);
}
public override ProcedureSchemaCollection GetProcedures ()
{
ProcedureSchemaCollection procedures = new ProcedureSchemaCollection ();
using (IPooledDbConnection conn = connectionPool.Request ()) {
// Exclude: Language Handler (2280) - Triggers (2279) - Void (2278)
using (IDbCommand command = conn.CreateCommand (
@"SELECT
pc.proname,
pc.oid::integer,
pl.lanname,
pc.prosrc,
pc.procost,
pc.provolatile,
pt.typname as rettypename,
pc.proargnames as argnames,
pc.proargmodes as argmodes,
pc.proallargtypes as argtypes
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
AND pc.prorettype not in (2278, 2279, 2280)
UNION
SELECT
pc.proname,
pt.oid::integer,
pl.lanname,
pc.prosrc,
pc.procost,
pc.provolatile,
pt.typname as rettypename,
pc.proargnames as argnames,
pc.proargmodes as argmodes,
pc.proallargtypes as argtypes
FROM
pg_proc pc,
pg_user pu,
pg_type pt,
pg_language pl
WHERE
pc.proowner = pu.usesysid
AND pc.prorettype not in (2278, 2279, 2280)
AND pc.prorettype = 0
AND pc.prolang = pl.oid;")) {
try {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read ()) {
ProcedureSchema procedure = new ProcedureSchema (this);
procedure.Name = r.GetString (0);
procedure.LanguageName = r.GetString (2);
if (!r.IsDBNull (1) && r.GetInt32 (1) < LastSystemOID)
procedure.IsSystemProcedure = true;
procedure.IsFunction = true;
// Procedure Definition
StringBuilder proc = new StringBuilder ();
if (procedure.IsFunction) {
string lang = r.GetString (2);
string proctype = string.Empty;
string retType = r.GetString (6);
switch (r.GetString (5)) {
case "s":
proctype = "STABLE";
break;
case "i":
proctype = "INMUTABLE";
break;
default:
proctype = "VOLATILE";
break;
}
float cost = r.GetFloat (4);
proc.AppendFormat ("CREATE OR REPLACE FUNCTION {0} (", r.GetString (0));
// Get parameters collection
ParameterSchemaCollection pars = GetParams (r.GetValue (7).ToString (),
r.GetValue (8).ToString (),
r.GetValue (9).ToString (), conn);
bool first = true;
// Set Parameters list
foreach (ParameterSchema p in pars) {
if (!first) {
proc.Append (", ");
}
first = false;
proc.Append (p.ParameterType.ToString ());
if (p.Name != "") {
proc.AppendFormat (" {0}", p.Name);
}
proc.AppendFormat (" {0}", p.DataTypeName);
}
proc.Append (")");
if (lang == "edbspl")
proc.AppendFormat ("\nRETURN {0} AS \n", retType);
else
proc.AppendFormat ("\nRETURNS {0} AS \n", retType);
proc.AppendFormat ("$BODY$ {0} $BODY$\n", r.GetString (3));
proc.AppendFormat ("LANGUAGE '{0}' {1}\n", lang, proctype);
proc.AppendFormat ("COST {0};\n", cost.ToString ());
}
procedure.Definition = proc.ToString ();
procedures.Add (procedure);
}
r.Close ();
}
} catch (Exception e) {
QueryService.RaiseException (e);
} finally {
conn.Release ();
}
}
}
return procedures;
public override ProcedureSchemaCollection GetProcedures ()
{
ProcedureSchemaCollection procedures = new ProcedureSchemaCollection ();
using (IPooledDbConnection conn = connectionPool.Request ()) {
using (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 (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";
procedure.Definition = GetSource ("[" + procedure.OwnerName + "].[" + procedure.Name + "]");
if (Array.Exists (system_procs, delegate (string s) {return s == procedure.Name; }))
procedure.IsSystemProcedure = true;
else
procedure.IsSystemProcedure = false;
procedures.Add (procedure);
}
r.Close ();
}
} catch (Exception e) {
QueryService.RaiseException (e);
} finally {
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;
}
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;
