/// <summary> /// Get image coordinates /// </summary> /// <param name="targetMapXDict"></param> /// <param name="targetMapYDict"></param> public static void GetImageCoords( out Dictionary <int, double> targetMapXDict, out Dictionary <int, double> targetMapYDict) { targetMapXDict = new Dictionary <int, double>(); targetMapYDict = new Dictionary <int, double>(); string sql = @" select entrezgene_id, x, y from mdbassay_owner.image_coord" ; DbCommandMx drd = new DbCommandMx(); drd.Prepare(sql); drd.ExecuteReader(); while (drd.Read()) { int geneId = drd.GetInt(0); double x = drd.GetDouble(1); targetMapXDict[geneId] = x; double y = drd.GetDouble(2); targetMapYDict[geneId] = y; } drd.CloseReader(); drd.Dispose(); return; }
/// <summary> /// Debug check for disallowed duplicate insert for a given method, cid, result group and result type /// </summary> /// <param name="voList"></param> /// <returns></returns> bool CheckForDuplicateInsert(List <AnnotationVo> voList) { AnnotationVo vo = voList[0]; string sql0 = // see if this result value already exists "SELECT * " + " FROM mbs_owner.MBS_ADW_RSLT " + " WHERE mthd_vrsn_id = " + vo.mthd_vrsn_id + " and ext_cmpnd_id_nbr = " + vo.ext_cmpnd_id_nbr + " and rslt_grp_id = " + vo.rslt_grp_id + " and rslt_typ_id = " + vo.rslt_typ_id + " and sts_id = 1"; DbCommandMx d0 = new DbCommandMx(); d0.Prepare(sql0); d0.ExecuteReader(); bool exists = d0.Read(); d0.Dispose(); if (exists) { return(true); } else { return(false); } }
/// <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); }
static int DoDeletes(List <string> CorpIdList) { StringBuilder sb = new StringBuilder(); int delCount = 0; int li = -1; while (li + 1 < CorpIdList.Count) { sb.Length = 0; int sbCorpIdCnt = 0; for (li = li + 1; li < CorpIdList.Count; li++) { if (sb.Length > 0) { sb.Append(","); } sb.Append(CorpIdList[li]); sbCorpIdCnt++; if (sbCorpIdCnt >= 1000) { break; } } string sql = "delete from mbs_owner.corp_moltable_mx " + "where corp_nbr in (" + sb + ")"; int delCount2 = DbCommandMx.PrepareAndExecuteNonReaderSql(sql); delCount += delCount2; } return(delCount); }
/// <summary> /// Read a sample of a table for previewing /// </summary> /// <returns></returns> List <string> GetPreviewSubset() { Dictionary <string, object> PreviewSubset = new Dictionary <string, object>(); MetaTable mt = Eqp.QueryTable.MetaTable; string sql = "select /*+ first_rows */" + mt.KeyMetaColumn.ColumnMap + " from " + GetSourceWithTableFilterCriteria(mt); DbCommandMx drd = new DbCommandMx(); drd.Prepare(sql); drd.ExecuteReader(); while (drd.Read()) { if (drd.IsNull(0)) { continue; } string id = drd.GetObject(0).ToString(); string cid = CompoundId.Normalize(id); // normalize cid adding prefix as needed PreviewSubset[id] = null; if (PreviewSubset.Count >= 100) { break; } } drd.Dispose(); return(new List <string>(PreviewSubset.Keys)); }
/// <summary> /// Insert the rows for a metatree node including its children into the metatree_nodes table /// Called via command: Update MetatreeNodeTable /// </summary> /// <param name="toks"></param> /// <param name="dao"></param> public static int InsertMetatreeNodeRows( MetaTreeNode mtn, DbCommandMx dao) { int insCnt = 0; foreach (MetaTreeNode cn in mtn.Nodes) { string names = ""; string values = ""; AddInsertColumn("parent_name", ref names, ref values, mtn.Name); AddInsertColumn("parent_label", ref names, ref values, mtn.Label); AddInsertColumn("parent_type", ref names, ref values, mtn.Type); AddInsertColumn("child_name", ref names, ref values, cn.Name); AddInsertColumn("child_label", ref names, ref values, cn.Label); AddInsertColumn("child_type", ref names, ref values, cn.Type); AddInsertColumn("child_size", ref names, ref values, cn.Size); AddInsertColumn("child_update_dt", ref names, ref values, cn.UpdateDateTime); string sql = "insert into " + MetatreeNodesTableName + " (" + names + ") " + "values (" + values + ")"; dao.Prepare(sql); dao.ExecuteNonReader(); insCnt++; } return(insCnt); }
/// <summary> /// Select /// </summary> /// <param name="projId"></param> /// <returns></returns> public static List <AfsTarget> Select(int projId) { List <AfsTarget> l = new List <AfsTarget>(); string sql = @" select * from <mbs_owner>.afs_target where afs_current = 1 and proj_id = " + projId + " order by upper(target_name)"; sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema); DbCommandMx dao = new DbCommandMx(); dao.Prepare(sql); dao.ExecuteReader(); while (dao.Read()) { AfsTarget t = new AfsTarget(); t.ProjId = dao.GetIntByName("PROJ_ID"); t.TargetName = dao.GetStringByName("TARGET_NAME"); t.TargetType = dao.GetStringByName("TARGET_TYPE"); l.Add(t); } dao.CloseReader(); return(l); }
/// <summary> /// Fetch next log object for reader opened by Select method /// </summary> /// <param name="drd"></param> /// <returns></returns> public static UserObject Read( DbCommandMx drd) { OracleDataReader dr = drd.OracleRdr; if (!dr.Read()) { return(null); } UserObject uo = new UserObject(); if (!dr.IsDBNull(0)) { uo.Id = (int)dr.GetOracleDecimal(0); } if (!dr.IsDBNull(1)) { uo.Type = (UserObjectType)(int)dr.GetOracleDecimal(1); } if (!dr.IsDBNull(2)) { uo.Owner = dr.GetString(2); } if (!dr.IsDBNull(3)) { uo.Name = dr.GetString(3); } if (!dr.IsDBNull(4)) { uo.Description = dr.GetString(4); } if (!dr.IsDBNull(5)) { uo.ParentFolderType = (FolderTypeEnum)(int)dr.GetOracleDecimal(5); } if (!dr.IsDBNull(6)) { uo.ParentFolder = dr.GetString(6); } if (!dr.IsDBNull(7)) { uo.AccessLevel = (UserObjectAccess)(int)dr.GetOracleDecimal(7); } if (!dr.IsDBNull(8)) { uo.Count = (int)dr.GetOracleDecimal(8); } if (!dr.IsDBNull(9)) { OracleClob ol = dr.GetOracleClob(9); uo.Content = ol.Value.ToString(); } if (!dr.IsDBNull(10)) { uo.UpdateDateTime = dr.GetDateTime(10); } return(uo); }
/// <summary> /// Copy PRD Annotation Table data to DEV by groups of result ids /// </summary> /// <returns></returns> public static string CopyPrdAnnotationTableDataToDevBy_Rslt_Id() { long minRi = 1; //long minRi = 1310251601; // appro half of rows, allows index on all cols to be built long maxRi = 4331096190; long chunkSize = 100000; DbCommandMx cmd = new DbCommandMx(); cmd.MxConn = DbConnectionMx.GetConnection("DEV857"); cmd.BeginTransaction(); long totalIns = 0; for (long ri = minRi; ri <= maxRi; ri += chunkSize) { string range = (ri + 1).ToString() + " and " + (ri + chunkSize); string sql = @"insert /*+ APPEND */ into mbs_owner.mbs_adw_rslt (select * from mbs_owner.mbs_adw_rslt where rslt_id between " + range + ")"; cmd.PrepareUsingDefinedConnection(sql); int insCnt = cmd.ExecuteNonReader(); cmd.Commit(); totalIns += insCnt; string msg = "Rows copied: " + totalIns + " (" + range + ")"; Mobius.UAL.Progress.Show(msg); DebugLog.Message(msg); } cmd.Dispose(); return("Rows copied: " + totalIns); }
/// <summary> /// Select /// </summary> /// <param name="projId"></param> /// <returns></returns> public static List <AfsProjMeta> Select(int projId) { List <AfsProjMeta> l = new List <AfsProjMeta>(); string sql = @" select * from <mbs_owner>.afs_proj_meta where afs_current = 1 and proj_id = " + projId + " order by upper(category_name)";; sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema); DbCommandMx dao = new DbCommandMx(); dao.Prepare(sql); dao.ExecuteReader(); while (dao.Read()) { AfsProjMeta m = new AfsProjMeta(); m.ProjId = dao.GetIntByName("PROJ_ID"); m.CategoryName = dao.GetStringByName("CATEGORY_NAME"); m.CategoryValue = dao.GetStringByName("CATEGORY_VALUE"); l.Add(m); } dao.CloseReader(); return(l); }
/// <summary> /// Open select statement /// </summary> /// <param name="where"></param> /// <param name="orderBy"></param> /// <returns></returns> public static DbCommandMx Select( string where, string orderBy) { string sql = @" select obj_id, obj_typ_id, ownr_id, obj_nm, obj_desc_txt, fldr_typ_id, fldr_nm, acs_lvl_id, obj_itm_cnt, obj_cntnt, crt_dt, upd_dt from mbs_owner.mbs_log " + "\r\n" + where + " \r\n" + //" where obj_id between 9300232 and 9301232 " + // debug orderBy; DbCommandMx drd = new DbCommandMx(); drd.Prepare(sql); drd.ExecuteReader(); return(drd); }
/// <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; }
public static string SelectString( DbConnectionMx mxConn, string sql) { string value; DbDataReader dr = null; DbCommandMx drd = new DbCommandMx(); drd.MxConn = mxConn; // set connection to use drd.Prepare(sql); dr = drd.ExecuteReader(); if (!dr.Read()) { throw (new Exception("SelectString Read failed")); } if (dr.IsDBNull(0)) { value = ""; } else { value = dr.GetString(0); } dr.Close(); drd.Dispose(); return(value); }
/// <summary> /// /// </summary> /// <param name="sql"></param> /// <param name="sourceName"></param> /// <param name="schemaName"></param> /// <returns></returns> public static List <DbColumnMetadata> GetColumnMetadataFromSql( string sql, DbConnectionMx conn) { int t0 = TimeOfDay.Milliseconds(); DbColumnMetadata md; List <DbColumnMetadata> mdList = new List <DbColumnMetadata>(); string sql2 = sql + " where 1=2"; // make execution fast DbCommandMx cmd = new DbCommandMx(); cmd.MxConn = conn; cmd.PrepareUsingDefinedConnection(sql2); OracleDataReader rdr = cmd.ExecuteReader() as OracleDataReader; for (int fi = 0; fi < rdr.FieldCount; fi++) { md = new DbColumnMetadata(); md.Name = rdr.GetName(fi); md.Type = rdr.GetDataTypeName(fi); // mdList.Add(md); } rdr.Dispose(); t0 = TimeOfDay.Milliseconds() - t0; return(mdList); }
/// <summary> /// Create the temp table for the list if needed /// </summary> /// <param name="schema"></param> /// <param name="name"></param> public static bool CreateListTableIfNeeded( string dsName, string listName) { DbCommandMx cmd = null; bool created = true; string fullTableName; DataSourceMx ds = GetDataSourceInfo(ref dsName, ref listName, out fullTableName); if (Lists.ContainsKey(fullTableName)) { return(false); } string sql = @" create global temporary table <schema.name> ( rowPos integer, intKey integer, stringKey varchar2(256)) on commit preserve rows" ; sql = sql.Replace("<schema.name>", fullTableName); try { DateTime t0 = DateTime.Now; cmd = new DbCommandMx(); DbConnectionMx dbc = DbConnectionMx.GetConnection(dsName); cmd.MxConn = dbc; cmd.Prepare(sql); cmd.ExecuteNonReader(); created = true; int tDelta = (int)TimeOfDay.Delta(t0); //DebugLog.Message("Create Table time: " + tDelta); } catch (Exception ex) { if (!ex.Message.Contains("ORA-xxx")) { throw ex; // if other than already exists then throw exception } created = false; // already exists } finally { if (cmd != null) { cmd.Dispose(); } } TempDbList list = new TempDbList(); list.DataSource = ds; list.Name = listName; Lists[fullTableName] = list; return(created); }
/// <summary> /// /// </summary> /// <param name="tableName"></param> public GenericDwDao( string tableName, string seqName) { DbCmd = new DbCommandMx(); TableName = tableName; SeqName = seqName; }
/// <summary> /// Prepare a query that takes a list as part of criteria /// </summary> /// <param name="sql"></param> /// <param name="parameterType"></param> public void PrepareListReader( DbCommandMx drd, string sql, DbType parameterType) { drd.PrepareListReader(sql, parameterType); return; }
/// <summary> /// Select basic project info with criteria /// </summary> /// <param name="criteria"></param> /// <returns></returns> public static List <AfsProject> SelectWithCriteria(string criteria) { MetaTreeNode mtn; string sql = @" select proj_id, mbs_project_code, proj_name, mbs_dht_folder_code, dht_folder_name, platform_name from <mbs_owner>.afs_project p where afs_current = 1 and mbs_project_code is not null and <criteria> order by upper(dht_folder_name), upper(proj_name) " ; sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema); sql = Lex.Replace(sql, "<criteria>", criteria); List <AfsProject> projects = new List <AfsProject>(); DbCommandMx dao = new DbCommandMx(); dao.Prepare(sql); dao.ExecuteReader(); while (dao.Read()) { AfsProject p = new AfsProject(); p.ProjId = dao.GetInt(0); p.MbsProjectName = dao.GetString(1).ToUpper(); p.ProjectLabel = dao.GetString(2); if (Lex.IsNullOrEmpty(p.ProjectLabel)) { p.ProjectLabel = p.MbsProjectName; } p.MbsDhtFolderName = dao.GetString(3).ToUpper(); p.DhtFolderLabel = dao.GetString(4).ToUpper(); if (Lex.IsNullOrEmpty(p.DhtFolderLabel)) { p.DhtFolderLabel = p.MbsDhtFolderName; } p.PlatformName = dao.GetString(5).ToUpper(); projects.Add(p); } dao.CloseReader(); return(projects); }
/// <summary> /// /// </summary> /// <param name="criteria"></param> /// <returns></returns> public static List <AfsAssay> SelectWithCriteria(string criteria) { MetaTreeNode mtn; string sql = @" select p.proj_id, p.mbs_project_code, proj_name, p.mbs_dht_folder_code, dht_folder_name, platform_name, assay_id, assay_name, assay_db, assay_use from <mbs_owner>.afs_project p, <mbs_owner>.afs_assay a where p.afs_current = 1 and p.mbs_project_code is not null and a.afs_current = 1 and a.proj_id = p.proj_id and <criteria> order by upper(dht_folder_name), upper(proj_name), upper(assay_name) " ; sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema); sql = Lex.Replace(sql, "<criteria>", criteria); List <AfsAssay> assays = new List <AfsAssay>(); DbCommandMx dao = new DbCommandMx(); dao.Prepare(sql); dao.ExecuteReader(); while (dao.Read()) { AfsAssay a = new AfsAssay(); a.ProjId = dao.GetInt(0); a.MbsProjectName = dao.GetString(1).ToUpper(); a.ProjectLabel = dao.GetString(2); a.MbsDhtFolderName = dao.GetString(3).ToUpper(); a.DhtFolderLabel = dao.GetString(4).ToUpper(); a.Platform = dao.GetString(5).ToUpper(); a.AssayId = dao.GetInt(6); a.AssayLabel = dao.GetString(7); a.AssayDb = dao.GetString(8).ToUpper(); a.AssayUse = dao.GetString(9).ToUpper(); assays.Add(a); } dao.CloseReader(); return(assays); }
/// <summary> /// Insert a row into the target-assay association table /// </summary> /// <param name="toks"></param> /// <param name="dao"></param> public static void InsertCommonAssayAttributes( AssayAttributes taa, DbCommandMx dao) { string names = ""; string values = ""; AddInsertColumn("id", ref names, ref values, taa.Id); AddInsertColumn("assy_db", ref names, ref values, taa.AssayDatabase); AddInsertColumn("assy_id2", ref names, ref values, taa.AssayId2); AddInsertColumn("assy_id_nbr", ref names, ref values, taa.AssayIdNbr); AddInsertColumn("assy_id_txt", ref names, ref values, taa.AssayIdTxt); AddInsertColumn("assy_nm", ref names, ref values, taa.AssayName); AddInsertColumn("assy_desc", ref names, ref values, taa.AssayDesc); AddInsertColumn("assy_src", ref names, ref values, taa.AssaySource); AddInsertColumn("assy_typ", ref names, ref values, taa.AssayType); AddInsertColumn("assy_mode", ref names, ref values, taa.AssayMode); AddInsertColumn("assy_sts", ref names, ref values, taa.AssayStatus); AddInsertColumn("assy_sum_lvl", ref names, ref values, taa.SummarizedAvailable ? 1 : 0); AddInsertColumn("assy_gene_cnt", ref names, ref values, taa.GeneCount); AddInsertColumn("rslt_typ", ref names, ref values, taa.ResultTypeConcType); AddInsertColumn("rslt_typ_nm", ref names, ref values, taa.ResultName); AddInsertColumn("rslt_typ_id2", ref names, ref values, taa.ResultTypeId2); AddInsertColumn("rslt_typ_id_nbr", ref names, ref values, taa.ResultTypeIdNbr); AddInsertColumn("rslt_typ_id_txt", ref names, ref values, taa.ResultTypeIdTxt); AddInsertColumn("rslt_uom", ref names, ref values, taa.ResultTypeUnits); AddInsertColumn("conc_uom", ref names, ref values, taa.ResultTypeConcUnits); AddInsertColumn("top_lvl_rslt", ref names, ref values, taa.TopLevelResult); AddInsertColumn("remppd", ref names, ref values, taa.Remapped); AddInsertColumn("mltplxd", ref names, ref values, taa.Multiplexed); AddInsertColumn("rvwd", ref names, ref values, taa.Reviewed); AddInsertColumn("cmpds_assyd", ref names, ref values, taa.CompoundsAssayed); AddInsertColumn("prflng_assy", ref names, ref values, taa.ProfilingAssay); AddInsertColumn("gene_id", ref names, ref values, taa.GeneId); AddInsertColumn("gene_symbl", ref names, ref values, taa.GeneSymbol); AddInsertColumn("gene_desc", ref names, ref values, taa.GeneDescription); AddInsertColumn("gene_fmly", ref names, ref values, taa.GeneFamily); AddInsertColumn("rslt_cnt", ref names, ref values, taa.ResultCount); AddInsertColumn("assy_updt_dt", ref names, ref values, taa.AssayUpdateDate); AddInsertColumn("assn_src", ref names, ref values, taa.AssociationSource); AddInsertColumn("assn_cnflct", ref names, ref values, taa.AssociationConflict); AddInsertColumn("x", ref names, ref values, taa.TargetMapX); AddInsertColumn("y", ref names, ref values, taa.TargetMapY); AddInsertColumn("z", ref names, ref values, taa.TargetMapZ); string sql = "insert into mbs_owner.cmn_assy_atrbts (" + names + ") " + "values (" + values + ")"; // sql = AssayAttributesDao.AdjustAssayAttrsTableName(sql); dao.Prepare(sql); dao.ExecuteNonReader(); return; }
/// <summary> /// Update copies of CMN_ASSY_ATRBTS in other Oracle instances /// </summary> /// <returns></returns> static string UpdateCmnAssyAtrbtsCopies() { DbCommandMx dao; string sql, msg = "", errMsg = ""; int cnt; string[] dbList = new string[] { //"AssayDB1", //"AssayDB2", //"...", }; foreach (string conName in dbList) { try { dao = new DbCommandMx(); dao.MxConn = DbConnectionMx.GetConnection(conName); sql = "delete from MBS_OWNER.CMN_ASSY_ATRBTS"; dao.PrepareUsingDefinedConnection(sql); cnt = dao.ExecuteNonReader(); sql = @" insert into MBS_OWNER.CMN_ASSY_ATRBTS select * from MBS_OWNER.CMN_ASSY_ATRBTS" ; dao.PrepareUsingDefinedConnection(sql); cnt = dao.ExecuteNonReader(); dao.Commit(); dao.Dispose(); if (msg != "") { msg += ", "; } msg += conName; } catch (Exception ex) { errMsg += "Error updating " + conName + ": " + ex.Message + "\r\n"; } } if (msg != "") { msg = "Updated copies of CMN_ASSY_ATRBTS in: " + msg; } if (errMsg != "") { msg += "\r\n" + errMsg; } return(msg); }
/// <summary> /// Establish Oracle connection for the Dao instance /// </summary> public void Connect() { DbCommandMx acd = new DbCommandMx(); this.DbCmd = acd; string tName = TableName; acd.MxConn = DbConnectionMx.MapSqlToConnection(ref tName); }
/// <summary> /// Calculate & persist library stats /// </summary> /// <returns></returns> public static string UpdateLibraryStatistics() { Dictionary <string, MetaTableStats> stats = new Dictionary <string, MetaTableStats>(); int libId; long cnt; DateTime dt; string libName, txt; try { DbCommandMx dao = new DbCommandMx(); string sql = // get count and date (must use crt_timestamp) for each library @" select l.lib_id, count(*), l.crt_timestamp from corp_owner.corp_library l, corp_owner.corp_library_substance ls where ls.lib_id (+) = l.lib_id group by l.lib_id, l.crt_timestamp order by l.lib_id" ; dao.Prepare(sql); dao.ExecuteReader(); while (dao.Read()) { libId = dao.GetInt(0); libName = "LIBRARY_" + libId; if (!stats.ContainsKey(libName)) { stats[libName] = new MetaTableStats(); } cnt = dao.GetLong(1); stats[libName].RowCount = cnt; dt = dao.GetDateTime(2); stats[libName].UpdateDateTime = dt; } dao.CloseReader(); dao.Dispose(); string fileName = MetaTableFactory.MetaTableXmlFolder + @"\LibraryStats"; MetaTableFactory.WriteMetaTableStats(stats, fileName); } catch (Exception ex) { throw ex; } return("Updated statistics for " + stats.Count + " libraries"); }
/// <summary> /// Store Spotfire SQL and associated fields returning the sequence number of the row /// </summary> /// <param name="sql"></param> /// <returns></returns> public static int InsertSpotfireSql( string name, int version, string sqlStmt, string keyColName, string keys, string owner) { int v0 = version; if (version < 0) { version = SequenceDao.NextVal("dev_mbs_owner.mbs_spotfire_sql_seq"); if (v0 == -2) // append version to name { name += version; } } else { DeleteSqlStatement(name, version); // delete if matching version } string sql = @" insert into dev_mbs_owner.mbs_spotfire_sql (name, version, sql, key_col_name, keys, ownr_id) values (:0,:1,:2,:3,:4,:5)" ; DbCommandMx drDao = new DbCommandMx(); OracleDbType[] pa = new OracleDbType[6]; pa[0] = OracleDbType.Varchar2; pa[1] = OracleDbType.Int32; pa[2] = OracleDbType.Clob; pa[3] = OracleDbType.Varchar2; pa[4] = OracleDbType.Varchar2; pa[5] = OracleDbType.Varchar2; drDao.Prepare(sql, pa); object[] p = new object[6]; p[0] = name; p[1] = version; p[2] = sqlStmt; p[3] = keyColName; p[4] = keys; p[5] = owner; int count = drDao.ExecuteNonReader(p); // insert the row drDao.Dispose(); return(version); }
/// <summary> /// Dispose of any associated DataReaderDao /// </summary> public void Dispose() { if (DbCmd == null) { return; } DbCmd.Dispose(); DbCmd = null; }
public DbDataReader ExecuteReader( DbCommandMx drd, object[] parmValues) { DateTime t0 = DateTime.Now; DbDataReader dr = drd.ExecuteReader(parmValues); double dt = TimeOfDay.Delta(t0); ExecuteReaderTime += dt; ExecuteReaderCount++; return(dr); }
/// <summary> /// Read dictionary from Oracle table /// </summary> /// <param name="dict"></param> /// <returns></returns> static DictionaryMx ReadDictionaryFromOracle( DictionaryMx dict) { DbCommandMx drd = null; int t0, t1, i1; try { //if (Lex.Eq(dict.Name, "DIT_PERSON")) dict = dict; // debug DbDataReader dr = null; drd = new DbCommandMx(); drd.Prepare(dict.Sql); dr = drd.ExecuteReader(); t0 = TimeOfDay.Milliseconds(); while (drd.Read()) { if (!dr.IsDBNull(0)) { string word = dr.GetValue(0).ToString(); if (Lex.IsNullOrEmpty(word)) { continue; } string definition = null; if (!dr.IsDBNull(1)) { definition = dr.GetValue(1).ToString(); } dict.Add(word, definition); t1 = TimeOfDay.Milliseconds(); // if (t1-t0 > 2000) break; // limit time for development } } drd.CloseReader(); drd.Dispose(); t1 = TimeOfDay.Milliseconds() - t0; // DebugLog.Message("ReadDictionaryFromOracle " + dict.Name + " Time: " + t1.ToString()); return(dict); } catch (Exception ex) { if (drd != null) { drd.Dispose(); } return(null); } }
/// <summary> /// Get list of CIDS that are in the oracle database but not the Mobius fingerprint files /// </summary> /// <returns></returns> static List <string> GetMissingCidList() { string sql = "", cid; int readCnt = 0; HashSet <string> knownCidsSet = FpDao.GetExistingCidSet(); // get list of cids in DB ExistingUndefinedStructureCids = FpDao.ReadUndefinedStructuresCids(); // cids that have undefined structures and aren't in DB knownCidsSet.UnionWith(ExistingUndefinedStructureCids); List <string> cidList = new List <string>(); if (CorpDatabase) { sql = SelectAllCorpIds; //sql = Lex.Replace(sql, "s.corp_nbr = m.corp_nbr", "s.corp_nbr = m.corp_nbr and s.corp_nbr = 3431641"); // debug //sql = Lex.Replace(sql, "s.corp_nbr = m.corp_nbr", "s.corp_nbr = m.corp_nbr and s.corp_nbr between 1000000 and 1100000"); // debug } else // chembl { sql = SelectAllChemblIds; } DbCommandMx rdr = DbCommandMx.PrepareAndExecuteReader(sql); while (rdr.Read()) { readCnt++; if (CorpDatabase) { int corpId = rdr.GetInt(0); // corp_nbr cid = corpId.ToString(); cid = CompoundId.NormalizeForDatabase(cid); } else { cid = rdr.GetString(0); // chembl } if (!knownCidsSet.Contains(cid)) { cidList.Add(cid); } } rdr.CloseReader(); return(cidList); }
/// <summary> /// Get dictionary of result types /// </summary> /// <returns></returns> public static Dictionary <int, AssayDbResultType> GetResultTypeDict() { if (ResultTypeDict != null) { return(ResultTypeDict); } string sql = @" select rt.assay_rslt_typ_id, rt.blgcl_rslt_typ_shrt_nm, rt.sum_md_txt, l.rslt_lvl_nm from metadata_owner.rslt_typ rt, metadata_owner.rslt_lvl l where l.rslt_lvl_id = rt.rslt_lvl_id and rt.sts_id = 1 and l.sts_id = 1"; Dictionary <int, AssayDbResultType> dict = new Dictionary <int, AssayDbResultType>(); ResultTypeDict = dict; DbCommandMx drd = new DbCommandMx(); drd.Prepare(sql); drd.ExecuteReader(); while (true) { if (!drd.Read()) { break; } AssayDbResultType rt = new AssayDbResultType(); rt.RsltTypeId = drd.GetInt(0); rt.Name = drd.GetString(1); rt.SumMdTxt = drd.GetString(2); rt.RsltLvl = drd.GetString(3); dict[rt.RsltTypeId] = rt; } drd.CloseReader(); return(dict); }
/// <summary> /// /// <summary> /// Copy PRD Annotation Table data to DEV reording and compressing the data by method /// </summary> /// <returns></returns> public static string CopyPrdAnnotationTableDataToDev() { string mvidSql = @" select mthd_vrsn_id, count from ( select mthd_vrsn_id, count(mthd_vrsn_id) count FROM MBS_OWNER.mbs_adw_rslt WHERE mthd_vrsn_id > 0 /* = 708070 */ group by mthd_vrsn_id) order by mthd_vrsn_id "; Mobius.UAL.Progress.Show("Getting list of annotation table mtht_vrsn_ids ..."); DbCommandMx c1 = new DbCommandMx(); c1.Prepare(mvidSql); c1.ExecuteReader(); DbCommandMx cmd = new DbCommandMx(); cmd.MxConn = DbConnectionMx.GetConnection("DEV857"); cmd.BeginTransaction(); long totalIns = 0; int vmIdCnt = 0; while (c1.Read()) { long mvId = c1.GetLong(0); string sql = @"insert /*+ APPEND */ into mbs_owner.mbs_adw_rslt select * from mbs_owner.mbs_adw_rslt where mthd_vrsn_id = " + mvId + @" order by ext_cmpnd_id_nbr, rslt_grp_id, rslt_id"; cmd.PrepareUsingDefinedConnection(sql); int insCnt = cmd.ExecuteNonReader(); cmd.Commit(); totalIns += insCnt; vmIdCnt++; string msg = "Mthd_Vrsn_id: " + mvId + ", Vmids: " + vmIdCnt + ", Rows: " + totalIns; Mobius.UAL.Progress.Show(msg); DebugLog.Message(msg); } c1.Dispose(); cmd.Dispose(); return("Rows copied: " + totalIns); }