/// <summary>
/// Get list of ACL roles
/// </summary>
/// <param name="username"></param>
/// <returns></returns>
public static string[] GetACLRoles(string username)
{
string[] roles = null;
try
{
DbCommandMx drd = new DbCommandMx();
drd.PrepareMultipleParameter("SELECT acl_api_pkg.acl_get_user_roles_fnc(:0) FROM DUAL", 1);
DbDataReader odr = drd.ExecuteReader(username.ToUpper());
string response = null;
if (odr.Read())
{
response = odr.GetString(0);
}
drd.CloseReader();
drd.Dispose();
if (response != null && response.StartsWith("OK") && response.IndexOf("|") > 0)
{
roles = response.Substring(response.IndexOf("|") + 1).Split(new char[] { ' ', ';' });
}
}
catch (Exception ex)
{
//do nothing
}
return(roles);
}
/// <summary>
/// Select an Oracle Blob value
/// </summary>
/// <param name="table"></param>
/// <param name="matchCol"></param>
/// <param name="typeCol"></param>
/// <param name="contentCol"></param>
/// <param name="matchVal"></param>
public static void SelectOracleBlob(
string table,
string matchCol,
string typeCol,
string contentCol,
string matchVal,
out string typeVal,
out byte[] ba)
{
typeVal = null;
ba = null;
string sql =
"select " + typeCol + ", " + contentCol + " " +
"from " + table + " " +
"where " + matchCol + " = :0";
DbCommandMx drd = new DbCommandMx();
drd.PrepareMultipleParameter(sql, 1);
for (int step = 1; step <= 2; step++) // try two different forms of matchVal
{
if (step == 2) // try alternate form of spaces
{
if (matchVal.Contains("%20"))
{
matchVal = matchVal.Replace("%20", " "); // convert html spaces to regular spaces
}
else
{
matchVal = matchVal.Replace(" ", "%20"); // convert regular spaces to html spaces
}
}
drd.ExecuteReader(matchVal);
if (!drd.Read())
{
continue;
}
typeVal = drd.GetString(0);
if (drd.Rdr.IsDBNull(1))
{
break;
}
OracleBlob ob = drd.OracleRdr.GetOracleBlob(1);
if (ob != null && ob.Length >= 0)
{
ba = new byte[ob.Length];
ob.Read(ba, 0, (int)ob.Length);
}
break; // have value
}
drd.Dispose();
return;
}
/// <summary>
/// Get dict of assay result types
/// </summary>
/// <param name="assayId"></param>
/// <returns></returns>
public static Dictionary <int, int> GetExpectedAssayResultTypes(int assayId)
{
Dictionary <int, int> dict = new Dictionary <int, int>();
DbCommandMx drd = new DbCommandMx();
string sql = AssayResultType.SelectExpectedResultTypesSqlTemplate;
drd.PrepareMultipleParameter(sql, 1); // (takes ~700 ms first time)
drd.ExecuteReader(assayId);
while (drd.Read())
{
int resultType = drd.GetIntByName("<resultTypeId>");
int secondaryResultType = drd.GetIntByName("<secondaryResultTypeId");
dict[secondaryResultType] = resultType;
}
drd.Dispose();
return(dict);
}
/// <summary>
/// Retrieve theoretical elemental composition value from Cassper database
/// </summary>
/// <param name="args"></param>
/// <returns></returns>
public object GetElementalAnalysisPct(
string cassperId,
string aSymbol)
{
DbCommandMx drd = new DbCommandMx();
string sql =
"select comp_pct " +
"from cas_owner.cas_structure " +
"where cassper_id = :0";
drd.PrepareMultipleParameter(sql, 1);
drd.ExecuteReader(cassperId);
if (!drd.Read() || drd.IsNull(0))
{
drd.Dispose();
return(null);
}
string s = drd.GetString(0);
drd.Dispose();
int i1 = s.ToLower().IndexOf(aSymbol.ToLower()); // find atom symbol
if (i1 < 0)
{
return(null);
}
s = s.Substring(i1 + 2); // get following number
i1 = s.IndexOf(" "); // and any following space
if (i1 >= 0)
{
s = s.Substring(0, i1);
}
return(Double.Parse(s));
}
/// <summary>
/// GetTableMetadataFromOracleDictionary
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static List <DbColumnMetadata> GetTableMetadataFromOracleDictionary(
DbConnectionMx conn,
string schemaName,
string tableName)
{
int t0 = TimeOfDay.Milliseconds();
DbColumnMetadata cmd;
List <DbColumnMetadata> cmdList = new List <DbColumnMetadata>();
string sql = "select column_name,data_type,data_length,data_precision,data_scale,nullable " +
"from sys.all_tab_columns where owner=:0 " +
"and table_name=:1 order by column_id";
if (conn == null)
{
throw new Exception("Connection not found for tableName: " + tableName);
}
DbCommandMx drd = new DbCommandMx();
drd.MxConn = conn;
int parmCount = 2;
drd.PrepareMultipleParameter(sql, parmCount);
string[] sa = tableName.Split('.');
if (sa.Length != 2)
{
throw new Exception("TableName not in owner.tableName form: " + tableName);
}
string creator = sa[0];
string tname = sa[1];
if (Lex.Eq(creator, "mbs_user")) // workaround to allow tables owned by dev mbs_owner
{
creator = "mbs_owner"; // to be accessed via synonyms defined on dev mbs_user
}
object[] p = new object[2];
p[0] = creator.ToUpper();
p[1] = tname.ToUpper();
drd.ExecuteReader(p);
while (drd.Read())
{
cmd = new DbColumnMetadata();
cmd.Name = drd.GetStringByName("column_name");
cmd.Type = drd.GetStringByName("data_type");
cmd.Length = drd.GetIntByName("data_length");
cmd.Precision = drd.GetIntByName("data_precision");
cmd.Scale = drd.GetIntByName("data_scale");
cmd.Nullable = drd.GetStringByName("nullable");
cmdList.Add(cmd);
}
drd.Dispose();
t0 = TimeOfDay.Milliseconds() - t0;
return(cmdList);
}
/// <summary>
/// See if a compound id exists
/// </summary>
/// <param name="cid"></param>
/// <param name="mt">MetaTable to check in or if null check based on prefix</param>
/// <returns></returns>
public static bool Exists(
string cid,
MetaTable mt)
{
DbDataReader dr = null;
MetaTable keyMt;
object obj;
bool result;
string table, prefix, suffix, cid2;
int number;
if (cid == null || cid == "")
{
return(false);
}
if (RestrictedDatabaseView.KeyIsRetricted(cid))
{
return(false);
}
if (mt != null && mt.Root.IsUserDatabaseStructureTable) // if root metatable is user database then normalize based on key
{
keyMt = mt.Root; // be sure we have root
cid = CompoundId.Normalize(cid, keyMt);
}
else // from non-user database, get key table based on prefix
{
cid = CompoundId.Normalize(cid, mt);
keyMt = CompoundId.GetRootMetaTableFromCid(cid, mt);
if (keyMt != null && Lex.Eq(keyMt.Name, "corp_structure") && MetaTableCollection.Get("corp_structure2") != null)
{
keyMt = MetaTableCollection.Get("corp_structure2"); // hack to search both small & large mols for Corp database
}
}
if (keyMt == null)
{
return(false);
}
if (cid.StartsWith("pbchm", StringComparison.OrdinalIgnoreCase))
{
return(true); // hack for pubchem until full set of compound ids available in Oracle
}
cid = CompoundId.NormalizeForDatabase(cid, keyMt); // get form that will match database
if (cid == null)
{
return(false);
}
string keyCol = keyMt.KeyMetaColumn.ColumnMap;
if (keyCol == "")
{
keyCol = keyMt.KeyMetaColumn.Name;
}
string sql =
"select " + keyCol +
" from " + keyMt.GetTableMapWithAliasAppendedIfNeeded() +
" where " + keyCol + " = :0";
DbCommandMx drd = new DbCommandMx();
try
{
drd.PrepareMultipleParameter(sql, 1);
dr = drd.ExecuteReader(cid);
result = drd.Read();
}
catch (Exception ex)
{
// Don't log error since usually because of invalid format number
// DebugLog.Message("CompoundIdUtil.Exists - SQL Error:\n" + sql + "\n" + ex.Message);
drd.Dispose();
return(false);
}
if (result == true)
{
obj = dr.GetValue(0);
cid2 = CompoundId.Normalize(obj.ToString()); // need to normalize if number
// if (cid2 != CompoundId.Normalize(cid)) result=false;
}
dr.Close(); // need to close cursor
drd.Dispose();
return(result);
}