/// <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); }
StructSearchMatch ReadFingerprintRec_To_StructSearchMatch( ReadFingerprintRecArgs a) { StructSearchMatch sm = new StructSearchMatch(); sm.SearchType = StructureSearchType.MolSim; sm.SrcDbId = a.src; sm.SrcCid = AsciiEncodingInstance.GetString(a.cidBytes, 0, a.cidLength); if (sm.SrcDbId == 0) { sm.SrcCid = CompoundId.NormalizeForDatabase(sm.SrcCid); // be sure CorpIds are proper length } return(sm); }
/// <summary> /// Get a list of CorpIds that have been added or modified since the checkpoint date /// </summary> /// <returns></returns> static List <string> GetNewAndModifiedCorpIdList( out Dictionary <string, DateTime> cidUpdateDateDict) { int readCnt = 0; List <string> cidList = new List <string>(); cidUpdateDateDict = new Dictionary <string, DateTime>(); CheckpointDateTime = FpDao.ReadFingerPrintSimMxDataUpdateCheckpointDate(); // , "20-mar-2016 000000"); if (Lex.IsUndefined(CheckpointDateTime)) { throw new Exception("Mobius UpdateSimMxCorpIdDataCheckpointDate is not defined"); } //CheckpointDateTime = "20-mar-2016 000000"; // debug string sql = Lex.Replace(SelectCorpIdsByDateRange, "1-jan-1900 000000", CheckpointDateTime); DbCommandMx rdr = DbCommandMx.PrepareAndExecuteReader(sql); while (rdr.Read()) { readCnt++; int corpId = rdr.GetInt(0); // corp_nbr string cid = corpId.ToString(); cid = CompoundId.NormalizeForDatabase(cid); cidList.Add(cid); DateTime dt = rdr.GetDateTime(1); cidUpdateDateDict[cid] = dt; } rdr.CloseReader(); return(cidList); }
/// <summary> /// Create an annotation table from a DataTable /// </summary> /// <param name="fullyQualifiedName">Fully qualified name to assign to table</param> /// <param name="dataTable">DataTable containing table definition & data</param> /// <param name="showProgress">Display dialog box showing progress of creation</param> /// <returns>Internal name assigned to annotation table (ANNOTATION_12345)</returns> public static MetaTable CreateAnnotationTable( string fullyQualifiedName, DataTable dataTable, bool showProgress) { List <AnnotationVo> voList = new List <AnnotationVo>(); AnnotationVo avo; if (dataTable == null) { DebugMx.ArgException("DataTable is null"); } if (dataTable.Columns.Count == 0) { DebugMx.ArgException("No DataColumns are defined"); } if (dataTable.Columns[0].DataType != typeof(CompoundId)) { DebugMx.ArgException("The first column must be of type CompoundId"); } if (showProgress) { Progress.Show("Creating annotation table..."); } AnnotationDao aDao = new AnnotationDao(); UserObject auo = UserObjectUtil.ParseInternalUserObjectName(fullyQualifiedName); auo.Type = UserObjectType.Annotation; UserObjectTree.GetValidUserObjectTypeFolder(auo); UserObject auo2 = UserObjectDao.Read(auo); // see if there already MetaTable oldMt = null; if (auo2 == null) // get new identifier { auo.Id = UserObjectDao.GetNextId(); // id to store table under } else // reuse identifier { auo.Id = auo2.Id; // copy it over aDao.DeleteTable(auo.Id); // delete any existing data string oldMtName = "ANNOTATION_" + auo2.Id; oldMt = MetaTableCollection.Get(oldMtName); } MetaTable mt = new MetaTable(); mt.MetaBrokerType = MetaBrokerType.Annotation; mt.Name = "ANNOTATION_" + auo.Id; // name table by uo mt.Label = auo.Name; mt.Code = auo.Id.ToString(); // code for the metatable int mtCode = auo.Id; if (dataTable.ExtendedProperties.ContainsKey("ParentTableName")) { mt.Parent = MetaTableCollection.Get((string)dataTable.ExtendedProperties["ParentTableName"]); } foreach (DataColumn dc in dataTable.Columns) { MetaColumn mc = new MetaColumn(); mc.MetaTable = mt; mc.Name = dc.ColumnName; MetaColumn oldMc = null; if (oldMt != null) { oldMc = oldMt.GetMetaColumnByName(mc.Name); // see if column name exists } if (oldMc != null && oldMc.ResultCode != "") // use any existing code { mc.ResultCode = oldMc.ResultCode; } else { mc.ResultCode = aDao.GetNextIdLong().ToString(); } if (dc.Caption != null) { mc.Label = dc.Caption; } else { mc.Label = mc.Name; } if (dc.DataType == typeof(CompoundId)) { mc.DataType = MetaColumnType.CompoundId; if (dc.ExtendedProperties.ContainsKey("StorageType") && dc.ExtendedProperties["StorageType"] is MetaColumnType && ((MetaColumnType)dc.ExtendedProperties["StorageType"]) == MetaColumnType.String) { mc.ColumnMap = "EXT_CMPND_ID_TXT"; // text column } else { mc.ColumnMap = "EXT_CMPND_ID_NBR"; // numeric column otherwise } } else if (dc.DataType == typeof(int) || dc.DataType == typeof(Int16) || dc.DataType == typeof(Int32)) { mc.DataType = MetaColumnType.Integer; } else if (dc.DataType == typeof(float) || dc.DataType == typeof(double)) { mc.DataType = MetaColumnType.Number; } else if (dc.DataType == typeof(QualifiedNumber)) { mc.DataType = MetaColumnType.QualifiedNo; } else if (dc.DataType == typeof(string)) { mc.DataType = MetaColumnType.String; } else if (dc.DataType == typeof(DateTime)) { mc.DataType = MetaColumnType.Date; } else if (dc.DataType == typeof(MoleculeMx)) { mc.DataType = MetaColumnType.Structure; } else { throw new Exception("Invalid data type " + dc.DataType.ToString()); } if (dc.ExtendedProperties.ContainsKey("DisplayLevel")) { mc.InitialSelection = (ColumnSelectionEnum)dc.ExtendedProperties["DisplayLevel"]; } if (dc.ExtendedProperties.ContainsKey("DisplayWidth")) { mc.Width = (float)dc.ExtendedProperties["DisplayWidth"]; } if (dc.ExtendedProperties.ContainsKey("DisplayFormat")) { mc.Format = (ColumnFormatEnum)dc.ExtendedProperties["DisplayFormat"]; } if (dc.ExtendedProperties.ContainsKey("Decimals")) { mc.Decimals = (int)dc.ExtendedProperties["Decimals"]; } mt.MetaColumns.Add(mc); } ToolHelper.CreateAnnotationTable(mt, auo); aDao.BeginTransaction(); // insert all data in single transaction if (showProgress) { Progress.Show("Writing data to annotation table..."); } int t1 = TimeOfDay.Milliseconds(); int writeCount = 0; foreach (DataRow dr in dataTable.Rows) { if (dr.IsNull(0)) { continue; // shouldn't happen } string key = dr[0].ToString(); key = CompoundId.NormalizeForDatabase(key, mt.Root); long rslt_grp_id = aDao.GetNextIdLong(); for (int ci = 1; ci < dataTable.Columns.Count; ci++) // do columns after key { if (dr.IsNull(ci)) { continue; } DataColumn dc = dataTable.Columns[ci]; MetaColumn mc = mt.MetaColumns[ci]; int mcCode = Int32.Parse(mc.ResultCode); avo = new AnnotationVo(); avo.rslt_grp_id = rslt_grp_id; // keep row together if (dc.DataType == typeof(CompoundId)) // shouldn't happen since key processed already { avo.rslt_val_txt = dr[ci].ToString(); } else if (dc.DataType == typeof(int) || dc.DataType == typeof(Int16) || dc.DataType == typeof(Int32) || dc.DataType == typeof(float) || dc.DataType == typeof(double)) { avo.rslt_val_nbr = (double)dr[ci]; } else if (dc.DataType == typeof(QualifiedNumber)) { QualifiedNumber qn = (QualifiedNumber)dr[ci]; avo.rslt_val_nbr = qn.NumberValue; avo.rslt_val_prfx_txt = qn.Qualifier; avo.rslt_val_txt = qn.TextValue; avo.dc_lnk = qn.Hyperlink; } else if (dc.DataType == typeof(string)) { avo.rslt_val_txt = dr[ci].ToString(); } else if (dc.DataType == typeof(DateTime)) { avo.rslt_val_dt = (DateTime)dr[ci]; } else if (dc.DataType == typeof(MoleculeMx)) { avo.rslt_val_txt = dr[ci].ToString(); } AddAnnotationVoToList(avo, key, mtCode, mcCode, voList); } writeCount++; if (Progress.CancelRequested) // check for cancel { aDao.Commit(); aDao.Insert(voList); voList.Clear(); aDao.Commit(); aDao.Dispose(); if (showProgress) { Progress.Hide(); } MessageBoxMx.ShowError("Writing of annotation table cancelled."); return(null); } int t2 = TimeOfDay.Milliseconds(); if (showProgress && t2 - t1 >= 1000) { t1 = t2; Progress.Show("Writing data to annotation table " + writeCount.ToString() + " of " + dataTable.Rows.Count.ToString() + " ..."); aDao.Insert(voList); voList.Clear(); aDao.Commit(); } } aDao.Insert(voList); voList.Clear(); aDao.Commit(); aDao.Dispose(); if (showProgress) { Progress.Hide(); } return(mt); // return metatable name }
/// <summary> /// Select a Molecule object for a compound id /// </summary> /// <param name="cid"></param> /// <param name="mt"></param> /// <returns></returns> public static MoleculeMx SelectMoleculeForCid( string cid, MetaTable mt = null) { MetaColumn strMc = null; string mtName = null, keyColName, strColExpr, chimeString; MoleculeMx cs; if (cid == null || cid == "") { return(null); } if (RestrictedDatabaseView.KeyIsRetricted(cid)) { return(null); } //if (mt != null) mtName = mt.Name; // debug bool isUcdb = (mt != null && mt.Root.IsUserDatabaseStructureTable); // user compound database if (isUcdb) // if root metatable is user database then normalize based on key { mt = mt.Root; // be sure we have root cid = CompoundId.Normalize(cid, mt); } else { cid = CompoundId.Normalize(cid, mt); cs = MoleculeCache.Get(cid); // see if in cache if (cs != null) { return(cs); } mt = CompoundId.GetRootMetaTableFromCid(cid, mt); if (mt != null && Lex.Eq(mt.Name, "corp_structure") && MetaTableCollection.Get("corp_structure2") != null) { mt = MetaTableCollection.Get("corp_structure2"); // hack to search both small & large mols for Corp database } } if (mt == null) { return(null); } strMc = mt.FirstStructureMetaColumn; //.getmt.GetMetaColumnByName("molstructure"); if (strMc == null) { return(null); } cid = CompoundId.NormalizeForDatabase(cid, mt); if (String.IsNullOrEmpty(cid)) { return(null); } // Call external method to select structure if (strMc.ColumnMap.StartsWith("InternalMethod", StringComparison.OrdinalIgnoreCase) || strMc.ColumnMap.StartsWith("PluginMethod", StringComparison.OrdinalIgnoreCase)) { // call external method to get structure List <MetaColumn> selectList = new List <MetaColumn>(); selectList.Add(mt.KeyMetaColumn); selectList.Add(strMc); object[] vo = new object[2]; vo[0] = cid; try { GenericMetaBroker.CallLateBindMethodToFillVo(vo, 1, mt, selectList); } catch (Exception ex) { return(null); } if (vo[1] is MoleculeMx) { cs = (MoleculeMx)vo[1]; } else if (vo[1] is string) { cs = MoleculeMx.MolStringToMoleculeMx((string)vo[1]); } else { cs = null; } if (!isUcdb) { MoleculeCache.AddMolecule(cid, cs); } return(cs); } // Normal case //if (HelmEnabled) // temp till server back //{ // cs = new MoleculeMx(); // MoleculeMx.SetMoleculeToTestHelmString(cid, cs); // return cs; //} string tableMap = mt.GetTableMapWithAliasAppendedIfNeeded(); // some SQL (e.g. Postgres) requires an alias for subqueries) strColExpr = strMc.ColumnMap; if (strColExpr == "") { strColExpr = strMc.Name; } if (MqlUtil.IsCartridgeMetaTable(mt)) // selecting from Direct cartridge { if (!Lex.Contains(tableMap, "chime(")) // if no chime expression { strColExpr = "chime(ctab)"; // then create one (gets clob) } strColExpr += ", chime(ctab)"; // add 2nd column that gets clob in case first just gets first 4000 characters } keyColName = mt.KeyMetaColumn.ColumnMap; if (keyColName == "") { keyColName = mt.KeyMetaColumn.Name; } DbType parmType = DbType.String; object cidObj = cid; if (mt.KeyMetaColumn.IsNumeric) { if (!Lex.IsInteger(cid)) { return(null); // if numeric col be sure cid is numeric also } parmType = DbType.Int64; cidObj = Int64.Parse(cid); } string sql = "select " + strColExpr + " " + "from " + tableMap + " " + "where " + keyColName + " = :0"; DbCommandMx drd = new DbCommandMx(); try { drd.PrepareParameterized(sql, parmType); drd.ExecuteReader(cidObj); if (!drd.Read() || drd.Rdr.IsDBNull(0)) { drd.Dispose(); return(null); } string molString = drd.GetString(0); drd.Dispose(); MoleculeMx.TrySetStructureFormatPrefix(ref molString, strMc.DataTransform); // add molString type if indicated by data transform cs = MoleculeMx.MolStringToMoleculeMx(molString); cs.StoreKeyValueInMolComments(strMc, cid); if (!isUcdb) { MoleculeCache.AddMolecule(cid, cs); } //if (MoleculeMx.HelmEnabled == true && Lex.IsInteger(cid)) // MoleculeMx.SetMoleculeToTestHelmString(cid, cs); return(cs); } catch (Exception ex) { // just log message & return; DebugLog.Message("SelectMoleculeForCid Exception, Cid: " + cid + ", table: " + mt.Name + "\n" + "sql: " + OracleMx.FormatSql(sql) + "\n" + DebugLog.FormatExceptionMessage(ex)); if (drd != null) { drd.Dispose(); } return(null); } }
static ICdkMol CdkMolUtil => StaticCdkMol.I; // static molecule shortcut for utility methods /// <summary> /// UpdateCorpFingerprintDatabaseMx /// /////////////////////////////////////////////////////// /// Syntax: Update FingerprintDatabaseMx [Corp | ChEMBL] [MACCS | ECFP4] [Load | ByCidRange | SinceLastCheckpoint | LoadMissing | <SingleCorpId>] /// /// Corp Examples: /// Update FingerprintDatabaseMx Corp MACCS Load /// Update FingerprintDatabaseMx Corp MACCS LoadMissing /// Update FingerprintDatabaseMx Corp MACCS SinceLastCheckpoint /// /// Update FingerprintDatabaseMx Corp ECFP4 Load /// Update FingerprintDatabaseMx Corp ECFP4 LoadMissing /// Update FingerprintDatabaseMx Corp ECFP4 SinceLastCheckpoint /// /// ChEMBL Examples: /// Update FingerprintDatabaseMx Chembl MACCS Load /// Update FingerprintDatabaseMx Chembl MACCS LoadMissing /// /// Update FingerprintDatabaseMx Chembl ECFP4 Load /// Update FingerprintDatabaseMx Chembl ECFP4 LoadMissing /// /////////////////////////////////////////////////////// /// </summary> /// <param name="argString"></param> /// <returns></returns> static public string Update( string argString) { MoleculeMx mol; double mw; string chime, smiles, molString, molFile = ""; string msg = "", sql = "", chemblId, cid = "", maxCorpIdSql, maxIdSql2, mf, missingFixCriteria = "", CorpIdList = ""; int storeChunkCount = 0, CorpId, molregno, molId, lowId = 0, highId = 0, maxDestId = 0, maxSrcId = 0; int readCount = 0, storeCount = 0; ByCheckpoint = ByCidRange = ByCidList = LoadIfMissing = false; ReadChunkSize = DefaultReadChunkSize; WriteChunkSize = DefaultWriteChunkSize; Failures = new Dictionary <string, string>(); NewUndefinedStructureCids = new List <string>(); LastFailure = ""; FailureCount = 0; // global try loop try { //////////////////////// /// Parse Parameters /// //////////////////////// // See which database argString = argString.Trim(); if (Lex.StartsWith(argString, "Corp")) { Database = "Corp"; argString = argString.Substring(5).Trim(); } else if (Lex.StartsWith(argString, "Chembl")) { Database = "ChEMBL"; argString = argString.Substring(6).Trim(); } else { return(SyntaxMsg); } // See which fingerprint type FingerprintType = FingerprintType.MACCS; // default to MACCS if type not defined if (Lex.TryReplace(ref argString, "MACCS", "")) { FingerprintType = FingerprintType.MACCS; argString = argString.Trim(); } else if (Lex.TryReplace(ref argString, "ECFP4", "")) { FingerprintType = FingerprintType.Circular; // (i.e. ECFP4) argString = argString.Trim(); } FpDao = new FingerprintDao(Database, FingerprintType); List <FingerprintRec> fpRecList = new List <FingerprintRec>(); string args = argString.Trim(); string initialMsg = "Update FingerprintDatabase started: " + args; CidList = new List <string>(); // init empty list ////////////////////// /// Corp Database /// ////////////////////// if (CorpDatabase) { if (Lex.Eq(args, "Load")) { ByCidRange = true; ShowProgress("Getting range of CorpIds to insert..."); maxCorpIdSql = SelectMaxCorpId; // get highest id in source db maxSrcId = SelectSingleValueDao.SelectInt(maxCorpIdSql); if (maxSrcId < 0) { maxSrcId = 0; } maxDestId = GetMaxDestMolId(); //maxIdSql2 = "select max(src_compound_id_nbr) from dev_mbs_owner.corp_uc_xref where src_id = 0"; // get highest id in UniChemDb db //highCorpId = SelectSingleValueDao.SelectInt(maxIdSql2); if (maxDestId < 0) { maxDestId = 0; } } else if (Lex.Eq(args, "SinceLastCheckpoint")) { ByCheckpoint = true; ShowProgress("Getting list of CorpIds updated since last checkpoint..."); CidList = GetNewAndModifiedCorpIdList(out CidUpdateDateDict); //CidUpdateList = new List<string>(); // debug with single cmpd //CidUpdateList.Add("03435269"); if (CidList.Count == 0) { return("There have been no updates since the last checkpoint"); } initialMsg += ", CorpIds to add/update: " + CidList.Count; } else if (Lex.StartsWith(args, "ByCorpIdList")) { ByCidList = true; CorpIdList = args.Substring("ByCorpIdList".Length).Trim(); if (Lex.IsUndefined(CorpIdList)) { throw new Exception("Undefined CorpId list"); } } else if (Lex.StartsWith(args, "LoadMissing")) { LoadIfMissing = true; if (args.Contains(" ")) { missingFixCriteria = args.Substring("LoadMissing".Length).Trim(); } ShowProgress("Getting list of missing CorpIds..."); CidList = GetMissingCidList(); if (CidList.Count == 0) { return("There are no missing CorpIds"); } initialMsg += ", Missing CorpIds: " + CidList.Count; } else if (int.TryParse(args, out maxSrcId)) // single CorpId { ByCidRange = true; maxDestId = maxSrcId - 1; // say 1 less is the max we have } else { return(SyntaxMsg); } } /////////////////////// /// ChEMBL Database /// /////////////////////// else if (ChemblDatabase) { if (Lex.Eq(args, "Load")) { ByCidRange = true; ShowProgress("Getting range of MolRegNos to insert..."); sql = "select max(molregno) from chembl_owner.compound_struct_xxxxxx"; maxSrcId = SelectSingleValueDao.SelectInt(sql); if (maxSrcId < 0) { maxSrcId = 0; } maxDestId = GetMaxDestMolId(); if (maxDestId < 0) { maxDestId = 0; } } else if (Lex.StartsWith(args, "LoadMissing")) { LoadIfMissing = true; ShowProgress("Getting list of missing ChEMBL Ids..."); CidList = GetMissingCidList(); if (CidList.Count == 0) { return("There are no missing Ids"); } initialMsg += ", Missing Chembl Ids: " + CidList.Count; } else { return(SyntaxMsg); } } else { return(SyntaxMsg); } CidListOriginalCount = CidList.Count; Log(initialMsg); ///////////////////////////// // Loop over chunks of data ///////////////////////////// for (int chunk = 1; ; chunk++) { ////////////////////// /// Corp Database /// ////////////////////// if (CorpDatabase) { if (ByCheckpoint) // single chunk { string cidList = GetNextListChunk(); if (Lex.IsUndefined(cidList)) { break; } sql = SelectByCorpIdCriteria; sql = Lex.Replace(sql, "<CorpIdCriteria>", "in (" + cidList + ")"); string matchString = "order by m.corp_nbr"; if (!Lex.Contains(sql, matchString)) { throw new Exception(matchString + " not found"); } sql = Lex.Replace(sql, matchString, "order by m.molecule_date"); msg = "Processing " + CidListOriginalCount + " updates since " + CheckpointDateTime; // + " (" + Mobius.Data.CidList.FormatCidListForDisplay(null, chunkCidList) + ")"; } else if (ByCidRange) // by CorpId range { if (maxDestId >= maxSrcId) { break; // done } lowId = maxDestId + 1; // start of next chunk highId = lowId + ReadChunkSize; maxDestId = highId; //lowCorpId = highCorpId = 12345; // debug if (highId >= maxSrcId) { highId = maxSrcId; } sql = SelectByCorpIdCriteria; sql = Lex.Replace(sql, "<CorpIdCriteria>", "between " + lowId + " and " + highId); msg = "Processing CorpId range: " + lowId + " to " + highId; } else if (ByCidList) // by single user-supplied CorpId list { if (chunk > 1) { break; // break 2nd time through } sql = SelectByCorpIdCriteria; sql = Lex.Replace(sql, "<CorpIdCriteria>", "in (" + CorpIdList + ")"); msg = "Processing CorpId list: " + CorpIdList; } else if (LoadIfMissing) { string cidList = GetNextListChunk(); if (Lex.IsUndefined(cidList)) { break; // all done } sql = SelectByCorpIdCriteria; sql = Lex.Replace(sql, "<CorpIdCriteria>", "in (" + cidList + ")"); msg = "Processing missing CorpId Chunk: " + Mobius.Data.CidList.FormatAbbreviatedCidListForDisplay(null, cidList) + ", Total Ids: " + CidListOriginalCount; Log(msg); } else { return(SyntaxMsg); } } /////////////////////// /// ChEMBL Database /// /////////////////////// else if (ChemblDatabase) { if (ByCidRange) // by CID range { if (maxDestId >= maxSrcId) { break; // done } lowId = maxDestId + 1; // start of next chunk highId = lowId + ReadChunkSize; maxDestId = highId; //lowId = highId = 12345; // debug if (maxDestId >= maxSrcId) { maxDestId = maxSrcId; } sql = SelectChemblSql; sql = Lex.Replace(sql, "<molregnoCriteria>", "between " + lowId + " and " + highId); msg = "Processing ChEMBL MolRegNo range: " + lowId + " to " + highId; } else if (LoadIfMissing) { string cidList = GetNextListChunk(); if (Lex.IsUndefined(cidList)) { break; // all done } sql = SelectByCorpIdCriteria; sql = Lex.Replace(sql, "<CorpIdCriteria>", "in (" + cidList + ")"); msg = "Processing missing ChEMBL Id Chunk: " + Mobius.Data.CidList.FormatAbbreviatedCidListForDisplay(null, cidList) + ", Total Ids: " + CidListOriginalCount; } else { return(SyntaxMsg); } } else { return(SyntaxMsg); } ShowProgress(msg); // Execute the SQL to get the rows for the chunk DbCommandMx rdr = DbCommandMx.PrepareAndExecuteReader(sql); DateTime lastShowProgressTime = DateTime.MinValue; /////////////////////////////////////////// /// Loop over rows in the current chunk /// /////////////////////////////////////////// while (true) { // Update progress display if (DateTime.Now.Subtract(lastShowProgressTime).TotalSeconds > 1) // show progress { int storeTotalCount = storeCount + storeChunkCount; string msg2 = msg + "\r\n" + "Reads: " + readCount + "\r\n" + "Undefined: " + NewUndefinedStructureCids.Count + "\r\n" + "Insert/Updates: " + storeTotalCount + "\r\n" + "Failures: " + FailureCount + "\r\n" + "Failure Types: " + Failures.Count + "\r\n" + "Last Failure: " + LastFailure; ShowProgress(msg2); lastShowProgressTime = DateTime.Now; } // Read and process next compound bool readOk = rdr.Read(); if (readOk) { readCount++; try { double t1 = 0, t2 = 0, t3 = 0, t4 = 0; DateTime t0 = DateTime.Now; mol = null; //t2 = TimeOfDay.Delta(ref t0); ////////////////////// /// Corp Database /// ////////////////////// if (CorpDatabase) { CorpId = rdr.GetInt(0); // corp_nbr //Log("CorpId: " + CorpId); // debug molId = CorpId; cid = CorpId.ToString(); cid = CompoundId.NormalizeForDatabase(cid); if (!rdr.IsNull(1)) // be sure chime field isn't null { chime = rdr.GetClob(1); if (Lex.IsDefined(chime)) { molFile = MoleculeMx.ChimeStringToMolfileString(chime); // convert Chime to MolFile mol = new MoleculeMx(MoleculeFormat.Molfile, molFile); } } MoleculeDateTime = rdr.GetDateTimeByName("Molecule_Date"); // Date molecule was updated in the CorpDB cartridge DB } /////////////////////// /// ChEMBL Database /// /////////////////////// else // chembl { molId = molregno = rdr.GetInt(0); cid = chemblId = rdr.GetString(1); smiles = rdr.GetString(2); if (Lex.IsDefined(smiles)) { mol = new MoleculeMx(MoleculeFormat.Smiles, smiles); } } if (MoleculeMx.IsUndefined(mol) || mol.AtomCount <= 1) { NewUndefinedStructureCids.Add(cid); continue; //mol = new AtomContainer(); // write empty structure } bool includeOverallFingerprint = true; List <BitSetFingerprint> fps = CdkMol.BuildBitSetFingerprints(mol.MolfileString, includeOverallFingerprint, FingerprintType); //t3 = TimeOfDay.Delta(ref t0); foreach (BitSetFingerprint fp in fps) { FingerprintRec fpr = new FingerprintRec(); fpr.molId = molId; fpr.SrcId = SrcDbId; fpr.Cid = cid; fpr.Cardinality = fp.cardinality(); fpr.Fingerprint = fp.asBitSet().toLongArray(); fpRecList.Add(fpr); } //t4 = TimeOfDay.Delta(ref t0); t4 = t4; } catch (Exception ex) { if (!Failures.ContainsKey(ex.Message)) { Failures.Add(ex.Message, cid); } else { Failures[ex.Message] += ", " + cid; } LastFailure = "Cid: " + cid + " - " + ex.Message; Log(LastFailure); //ShowProgress(ex.Message + "\r\n" + ex.StackTrace.ToString()); // debug FailureCount++; continue; } storeChunkCount++; } bool commitTransaction = (storeChunkCount >= WriteChunkSize || (!readOk && storeChunkCount > 0)); if (commitTransaction) // end of chunk of data to store? { // if updating by CheckPoint date range then merge existing data with new/updated data if (ByCheckpoint) { if (readCount > 0 && (storeCount > 0 || FailureCount == 0)) // make sure not everything has failed) { MergeRecordsIntoFiles(fpRecList); } } // Simple append of records to files else { FpDao.OpenWriters("bin", FileMode.Append); // open bin files for append foreach (FingerprintRec fpr in fpRecList) // write out buffered recs { FpDao.WriteFingerprintRec(fpr); } FpDao.CloseWriters(); int cnt = fpRecList.Count; if (cnt > 0) { string cid1 = fpRecList[0].Cid; string cid2 = fpRecList[cnt - 1].Cid; Log("Records Appended: " + cnt + ", CIDS: " + cid1 + " - " + cid2); } else { Log("Records Appended: 0"); } } fpRecList.Clear(); storeCount += storeChunkCount; storeChunkCount = 0; } if (!readOk) { break; } } // end of read loop for rows in a chunk rdr.Dispose(); } // end for loop of chunks DeleteTempFiles(); if (LoadIfMissing) // update list of cids with missing structures { ExistingUndefinedStructureCids.UnionWith(NewUndefinedStructureCids); FpDao.WriteUndefinedStructuresCids(ExistingUndefinedStructureCids); } msg = "*** Update Complete ***\r\n\r\n" + msg; ShowProgress(msg); System.Threading.Thread.Sleep(100); string logMsg = "UpdateFingerprintDb - CIDs stored: " + storeCount + ", Undefined structures: " + NewUndefinedStructureCids.Count + ", failures: " + FailureCount + "\r\n"; foreach (string key in Failures.Keys) { logMsg += key + " - CIDs: " + Failures[key] + "\r\n"; } Log(logMsg); return(logMsg); } // end of main try loop catch (Exception ex) { Log(DebugLog.FormatExceptionMessage(ex)); throw new Exception(ex.Message, ex); } }
/// <summary> /// Convert a multipivot table into a set of tables where data exists for /// one or more of the compound identifiers in the list. /// </summary> /// <param name="qt">Current form of query table</param> /// <param name="q">Query to add transformed tables to</param> /// <param name="ResultKeys">Keys data will be retrieved for</param> public override void ExpandToMultipleTables( QueryTable qt, Query q, List <string> resultKeys) { MetaTable mt2; QueryTable qt2; string sql; int methodId, i1; int t0 = TimeOfDay.Milliseconds(); List <string> normalizedResultKeys = new List <string>(); for (i1 = 0; i1 < resultKeys.Count; i1++) // copy keys to parameter array properly normalized { string key = CompoundId.NormalizeForDatabase(resultKeys[i1], qt.MetaTable); if (key == null) { key = NullValue.NullNumber.ToString(); // if fails supply a "null" numeric value } normalizedResultKeys.Add(key); } sql = // todo: Make to work in general case (PubChem only now) "select mthd_vrsn_id " + "from " + "mbs_owner.mbs_pbchm_rslt" + " " + "where ext_cmpnd_id_nbr in (<list>) " + " and sts_id = 1 " + // active records only "group by mthd_vrsn_id"; DbCommandMx drd = new DbCommandMx(); drd.PrepareListReader(sql, DbType.Int32); drd.ExecuteListReader(normalizedResultKeys); if (drd.Cancelled) { // todo qe.Cancelled = true; drd.Dispose(); return; } Hashtable mtHash = new Hashtable(); int methodIdCount = 0; while (true) // convert list of methods to set of metatable names { if (!drd.ListRead()) { break; } methodId = drd.GetInt(0); string mtName = "pubchem_aid_" + methodId.ToString(); // todo: Make to work in general case (PubChem only now) if (QueryEngine.FilterAllDataQueriesByDatabaseContents && !MetaTableCollection.IsMetaTableInContents(mtName)) { continue; // metatable must be in contents } if (qt.MetaTable.UseSummarizedData) { mtName += MetaTable.SummarySuffix; } mtHash[mtName] = null; methodIdCount++; } drd.Dispose(); if (drd.Cancelled) { // todo qe.Cancelled = true; return; } ArrayList mtList = new ArrayList(); foreach (string mtName2 in mtHash.Keys) { // put metatable labels & names into a list for sorting mt2 = MetaTableCollection.Get(mtName2); if (mt2 == null) { continue; } if (mt2.Parent == null) { continue; // skip if no parent } mtList.Add(mt2.Label.ToLower().PadRight(64) + "\t" + mt2.Name); } mtList.Sort(); foreach (string mts in mtList) { // add new querytables/metatables to query string[] sa = mts.Split('\t'); mt2 = MetaTableCollection.Get(sa[1]); if (mt2 == null) { continue; } qt2 = new QueryTable(q, mt2); if (qt.HeaderBackgroundColor != Color.Empty) { qt2.HeaderBackgroundColor = qt.HeaderBackgroundColor; } } t0 = TimeOfDay.Milliseconds() - t0; return; }
/// <summary> /// Read in data, pivot & buffer for supplied set of rows. /// This is called for retrieval only, not for search /// </summary> /// <param name="eqp"></param> public override void ExecuteQuery( ExecuteQueryParms eqp) { DbCommandMx drd; int rowsFetched = 0, vosCreated = 0; MetaTable mt; MetaColumn mc = null; DateTime dt; PivotMetaBroker mb; List <GenericMetaBroker> mbList; string cid, pivotKey, tableFilter, s, txt, tok; int fci, mci, pvi, pci, si, i1; object[] vo = null; object o; if (!PivotInCode) // let Oracle do the pivoting? { base.ExecuteQuery(eqp); return; } // Self-pivot. Read & buffer data for all query tables from same Source/TableFilterColumns for key set if we are the first table for Source int t0 = TimeOfDay.Milliseconds(); Dictionary <string, MultiTablePivotBrokerTypeData> mbsi = eqp.Qe.MetaBrokerStateInfo; mt = eqp.QueryTable.MetaTable; string sourceKey = mt.TableMap + "," + Csv.JoinCsvString(mt.TableFilterColumns); // grouping based on source sql MultiTablePivotBrokerTypeData mpd = (MultiTablePivotBrokerTypeData)mbsi[sourceKey]; if (mpd.FirstTableName != mt.Name) { return; // retrieve data for all tables when we see first table } mpd.ClearBuffers(); // Build sql StringBuilder sb = new StringBuilder(); // build filter to select for desired metatable tableFilter = ""; if (mt.TableFilterColumns.Count == 1) { // build single in list if single filter column foreach (string mtName in mpd.MbInstances.Keys) { mt = MetaTableCollection.Get(mtName); if (sb.Length > 0) { sb.Append(","); } sb.Append(mt.TableFilterValues[0]); tableFilter = mt.TableFilterColumns[0] + " in (" + sb.ToString() + ")"; } } else // multiple table filter columns, build and/or expressions { foreach (string mtName in mpd.MbInstances.Keys) { mt = MetaTableCollection.Get(mtName); if (sb.Length > 0) { sb.Append(" or "); } tableFilter = "(" + GetTableFilterCriteria(mt) + ")"; sb.Append(tableFilter); } tableFilter = "(" + sb.ToString() + ")"; } string sql = "select * from " + mt.TableMap + " where "; if (tableFilter != "") { sql += tableFilter + " and "; } sql += mt.KeyMetaColumn.ColumnMap + " in (<list>) "; // Read unpivoted data, merge/pivot & buffer pivoted rows List <string> keySubset = eqp.SearchKeySubset; if (keySubset == null) { keySubset = GetPreviewSubset(); // assume previewing of single table if no subset } List <string> parmList = new List <string>(); for (i1 = 0; i1 < keySubset.Count; i1++) // copy keys to parameter array properly normalized { string key = CompoundId.NormalizeForDatabase((string)keySubset[i1], Qt.MetaTable); if (key != null) { parmList.Add(key); } } drd = new DbCommandMx(); drd.PrepareListReader(sql, DbType.String); drd.ExecuteListReader(parmList); while (drd.Read()) { rowsFetched++; string tableFilterKey = ""; // get column values to identify table for (fci = 0; fci < mt.TableFilterColumns.Count; fci++) { o = drd.GetObjectByName(mt.TableFilterColumns[fci]); if (o == null) { s = ""; } else { s = o.ToString(); } if (tableFilterKey != "") { tableFilterKey += ", "; } tableFilterKey += s; } mt = mpd.TableFilterValuesToMetaTableDict[tableFilterKey]; if (mt == null) { continue; // continue if don't know about this table } if (!mpd.MbInstances.ContainsKey(mt.Name)) { continue; // have row hash for broker? } int mbIdx = 0; mb = (PivotMetaBroker)mpd.GetFirstBroker(mt.Name, out mbList); while (true) // copy out for each metabroker { mt = mb.Qt.MetaTable; if (mt == null) { continue; } if (mb.MultipivotRowDict == null) { mb.MultipivotRowDict = new Dictionary <string, object[]>(); } string rowKey = ""; for (mci = 0; mci < mt.PivotMergeColumns.Count; mci++) { o = drd.GetObjectByName(mt.PivotMergeColumns[mci]); if (o == null) { s = "<null>"; } else { s = o.ToString(); } rowKey += "<" + s + ">"; } if (mb.MultipivotRowDict.ContainsKey(rowKey)) // have entry for row? { vo = (object[])mb.MultipivotRowDict[rowKey]; } else // new row, create vo for it & fill in merged column values { vo = new Object[mb.SelectList.Count]; for (si = 0; si < mb.SelectList.Count; si++) // transfer non-pivoted values { mc = mb.SelectList[si]; if (mc.PivotValues != null) { continue; // skip pivoted cols for now } for (mci = 0; mci < mt.PivotMergeColumns.Count; mci++) { if (Lex.Eq(mc.ColumnMap, mt.PivotMergeColumns[mci])) { o = drd.GetObjectByName(mt.PivotMergeColumns[mci]); if (mc.IsKey) // normalize cid adding prefix as needed { o = CompoundId.Normalize(o.ToString(), mt); } vo[si] = o; break; } } } mb.MultipivotRowDict[rowKey] = vo; vosCreated++; } // Pivot out data based on pivot column values if (mb.PivotKeys == null) { // build set of pivot keys for the pivoted columns in the table if not done yet mb.PivotKeys = new string[mb.SelectList.Count]; for (si = 0; si < mb.SelectList.Count; si++) { mc = mb.SelectList[si]; if (mc.PivotValues == null) { continue; // skip non-pivoted cols } pivotKey = ""; for (pvi = 0; pvi < mc.PivotValues.Count; pvi++) { pivotKey += "<" + mc.PivotValues[pvi].ToLower() + ">"; } mb.PivotKeys[si] = pivotKey; } } pivotKey = ""; for (pci = 0; pci < mt.PivotColumns.Count; pci++) { // build pivot key for this unpivoted row o = drd.GetObjectByName(mt.PivotColumns[pci]); if (o == null) { s = "<null>"; } else { s = o.ToString().ToLower(); } pivotKey += "<" + s + ">"; } for (si = 0; si < mb.SelectList.Count; si++) // transfer pivoted values { if (mb.PivotKeys[si] == null || // skip non-pivoted cols pivotKey != mb.PivotKeys[si]) { continue; // and non-matches } mc = mb.SelectList[si]; int ci = drd.Rdr.GetOrdinal(mc.ColumnMap); if (mc.DataType == MetaColumnType.Integer) { if (!mc.DetailsAvailable) // simple scalar value { vo[si] = drd.GetInt(ci); } else // value with possible resultId, linking information { txt = drd.GetString(ci); // todo: fix for annotation vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc); } } else if (mc.DataType == MetaColumnType.Number) { if (!mc.DetailsAvailable) // simple scalar value { vo[si] = drd.GetDouble(ci); } else // value with possible resultId, linking information { txt = Dr.GetString(ci); // todo: fix for annotation vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc); } } else if (mc.DataType == MetaColumnType.QualifiedNo) { // todo } else if (mc.DataType == MetaColumnType.String) { if (!mc.DetailsAvailable) { vo[si] = drd.GetString(ci); } else // value with possible resultId, linking information { txt = Dr.GetString(ci); // todo: fix for annotation vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc); } } else if (mc.DataType == MetaColumnType.Date) { if (!mc.DetailsAvailable) // simple scalar value { vo[si] = drd.GetDateTime(ci); } else // value with possible resultId, linking information { txt = Dr.GetString(ci); // todo: fix for annotation vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc); } } else if (mc.DataType == MetaColumnType.Structure) { // structures come in as compound identifiers (todo: fix for annotation) tok = Dr.GetValue(si).ToString(); cid = CompoundId.Normalize(tok, Qt.MetaTable); vo[si] = cid; } else if (mc.DataType == MetaColumnType.MolFormula) { vo[si] = drd.GetString(ci); } else if (mc.DataType == MetaColumnType.DictionaryId) { try // Id may be string or integer value { vo[si] = drd.GetString(ci); } catch (Exception ex) { vo[si] = drd.GetInt(ci); } } else if (mc.DataType == MetaColumnType.Image) { try // Id may be string or integer value { vo[si] = drd.GetString(ci); } catch (Exception ex) { vo[si] = drd.GetInt(ci); } } else if (mc.DataType == MetaColumnType.Hyperlink) { txt = drd.GetString(ci); Hyperlink hlink = new Hyperlink(); vo[si] = hlink; hlink.Text = txt; } } if (mbList == null) { break; // single broker } mbIdx++; // go to next broker if (mbIdx >= mbList.Count) { break; // at end of brokers? } mb = (PivotMetaBroker)mbList[mbIdx]; } // end of broker loop } // end of read loop drd.Dispose(); return; }
/// Build an unparameterized key list SQL predicate /// </summary> /// <param name="Qt"></param> /// <param name="keyName">Key column name qualified by table name/alias</param> /// <summary> /// Build an unparameterized key list SQL predicate /// </summary> /// <param name="eqp"></param> /// <param name="baseSql"></param> /// <param name="keyName">Key column name qualified by table name/alias</param> /// <param name="keyList"></param> /// <param name="firstKeyIdx"></param> /// <param name="keyCount"></param> /// <param name="keyCriteria">Full criteria including col name operator and list</param> /// <param name="keyListString">Just the list of keys</param> public void BuildUnparameterizedKeyListPredicate( ExecuteQueryParms eqp, ref string baseSql, string keyName, List <string> keyList, int firstKeyIdx, int keyCount, out string keyCriteria, out string keyListString) { StringBuilder sb; int i1; QueryTable qt = eqp.QueryTable; MetaTable mt = qt.MetaTable; bool integerKey = mt.IsIntegerKey(); List <StringBuilder> sublists = new List <StringBuilder>(); sb = new StringBuilder(); sublists.Add(sb); int sublistKeyCount = 0; // current keys in predicate keyCriteria = keyListString = null; for (i1 = 0; i1 < keyCount; i1++) { if (sublistKeyCount >= DbCommandMx.MaxOracleInListItemCount) { sb = new StringBuilder(); sublists.Add(sb); sublistKeyCount = 0; } if (sb.Length > 0) { sb.Append(","); } string key = CompoundId.NormalizeForDatabase((string)keyList[firstKeyIdx + i1], qt.MetaTable); if (key == null) { key = NullValue.NullNumber.ToString(); // if fails supply a "null" numeric value } if (!integerKey || !Lex.IsInteger(key)) // quote it if not integer column or value { key = Lex.AddSingleQuotes(key); // (note: quoted integers can cause mismatches for some database systems, e.g. Denodo) } sb.Append(key); sublistKeyCount++; } sb = new StringBuilder(); if (sublists.Count >= 2) { sb.Append("("); // wrap in parens if multiple sublists } for (int sli = 0; sli < sublists.Count; sli++) { if (sli > 0) { sb.Append(" or "); } sb.Append(keyName + " in (" + sublists[sli] + ")"); } if (sublists.Count >= 2) { sb.Append(")"); } keyCriteria = sb.ToString(); keyListString = sublists[0].ToString(); // return just the first sublist (adjust later for larger lists) return; }
/// <summary> /// Execute structure search for structures stored in an Oracle text column /// </summary> /// <param name="sql">Basic sql to select key & structure</param> public void ExecuteInternalOracleStructureColumnSearch( string sql, MoleculeFormat structureFormat) { string cid, molString; object[] vo; MoleculeMx cs; bool match = false; QueryColumn molsimQc = Eqp.QueryTable.GetSelectedMolsimQueryColumn(); // get any column to return similarity score in DbCommandMx drd = new DbCommandMx(); if (Eqp.SearchKeySubset == null) { drd.Prepare(sql); drd.ExecuteReader(); } else // limit to list { sql += " where " + Eqp.QueryTable.MetaTable.KeyMetaColumn.Name + " in (<list>)"; drd.PrepareListReader(sql, DbType.String); List <string> dbKeySubset = new List <string>(); foreach (string key in Eqp.SearchKeySubset) { string dbKey = CompoundId.NormalizeForDatabase(key, Eqp.QueryTable.MetaTable); dbKeySubset.Add(dbKey); } drd.ExecuteListReader(dbKeySubset); } drd.CheckForCancel = Eqp.CheckForCancel; // set cancel check flag StructureMatcher matcher = new StructureMatcher(); // allocate structure matcher List <object[]> results = new List <object[]>(); int matchCount = 0; while (drd.Read()) { cid = drd.GetObject(0).ToString(); cid = CompoundId.Normalize(cid, Eqp.QueryTable.MetaTable); // normalize adding prefix as needed molString = drd.GetString(1); if (String.IsNullOrEmpty(molString)) { continue; } cs = new MoleculeMx(structureFormat, molString); if (Pssc.SearchType == StructureSearchType.Substructure) { if (matcher.IsSSSMatch(Pssc.Molecule, cs)) { vo = new object[SelectList.Count]; vo[0] = CompoundId.Normalize(cid, Eqp.QueryTable.MetaTable); // normalize adding prefix results.Add(vo); } } else if (Pssc.SearchType == StructureSearchType.MolSim) { double score = matcher.Molsim(Pssc.Molecule, cs, Pssc.SimilarityType); if (score >= Pssc.MinimumSimilarity) { vo = new object[SelectList.Count]; vo[0] = CompoundId.Normalize(cid, Eqp.QueryTable.MetaTable); // normalize adding prefix if (vo.Length >= 2) { vo[1] = (float)score; } results.Add(vo); } } else if (Pssc.SearchType == StructureSearchType.FullStructure) { if (matcher.FullStructureMatch(Pssc.Molecule, cs, Pssc.Options)) { vo = new object[SelectList.Count]; vo[0] = CompoundId.Normalize(cid, Eqp.QueryTable.MetaTable); // normalize adding prefix results.Add(vo); } } matchCount++; // if (matchCount >100) break; // debug if (drd.Cancelled) { Eqp.Qe.Cancelled = true; drd.Dispose(); Results = null; return; } } drd.Dispose(); Results = results; return; }
/// <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); }