/// <summary> /// Select structures for a list of compound ids using a single SQL cursor. /// </summary> /// <param name="notInCache"></param> /// <param name="mt"></param> /// <param name="strMc"></param> static Dictionary <string, MoleculeMx> SelectChemicalStructuresForCorpIdList( List <string> cidList, MetaTable mt) { List <string> notInCacheKeyList; Dictionary <Int64, string> intKeyToStringKey; DbType parmType; MoleculeMx cs; Int64 intCid; string cid; int li; Stopwatch sw = Stopwatch.StartNew(); Dictionary <string, MoleculeMx> csDict = new Dictionary <string, MoleculeMx>(); List <string> notInCacheList = new List <string>(); // Get structures already in the cache for (li = 0; li < cidList.Count; li++) { cid = cidList[li]; if (RestrictedDatabaseView.KeyIsRetricted(cid)) { continue; } cid = CompoundId.Normalize(cid, mt); if (!Int64.TryParse(cid, out intCid)) { continue; // make sure an integer } if (MoleculeCache.Contains(cid)) // see if in cache { csDict[cid] = MoleculeCache.Get(cid); continue; } else { notInCacheList.Add(cid); } } // Retrieve structures from the database for those not in cache if (notInCacheList.Count == 0) { return(csDict); } MetaColumn strMc = mt.GetMetaColumnByName("molstructure"); if (strMc == null) { return(null); } string tableMap = mt.GetTableMapWithAliasAppendedIfNeeded(); // some SQL (e.g. Postgres) requires an alias for subqueries) string keyColName = mt.KeyMetaColumn.ColumnMap; if (Lex.IsUndefined(keyColName)) { keyColName = mt.KeyMetaColumn.Name; } string 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 } string sql = "select " + keyColName + ", " + strColExpr + " " + "from " + tableMap + " " + "where " + keyColName + " in (<list>)"; DbCommandMx drd = new DbCommandMx(); bool isNumericKey = (mt.KeyMetaColumn.IsNumeric); bool isStringKey = !isNumericKey; if (isStringKey) { parmType = DbType.String; } else { parmType = DbType.Int64; } try { drd.PrepareListReader(sql, parmType); drd.ExecuteListReader(notInCacheList); while (drd.Read()) { if (drd.Rdr.IsDBNull(0)) { continue; } if (isNumericKey) { intCid = drd.GetLong(0); cid = intCid.ToString(); } else // string cid { cid = drd.GetString(0); } cid = CompoundId.Normalize(cid, mt); string molString = drd.GetString(1); cs = new MoleculeMx(MoleculeFormat.Chime, molString); csDict[cid] = cs; MoleculeCache.AddMolecule(cid, cs); } drd.Dispose(); int msTime = (int)sw.ElapsedMilliseconds; return(csDict); } catch (Exception ex) { if (drd != null) { drd.Dispose(); } throw new Exception( "SelectStructuresForCorpIdList, table: " + mt.Name + "\n" + "sql: " + OracleMx.FormatSql(sql) + "\n"); } }