/// <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> /// 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); }
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> /// Add sql for query to list of total sql for query /// </summary> /// <param name="mb"></param> /// <param name="sql"></param> internal void AddSqlToSqlStatementList( GenericMetaBroker mb, string sql) { string sqlWithDbLinks = sql; DbConnectionMx mxConn = DbConnectionMx.MapSqlToConnection(ref sqlWithDbLinks); // add any dblinks as necessary if (SqlList == null) { SqlList = new List <List <string> >(); } List <string> sli = new List <string>(); if (Lex.IsDefined(mb.Label)) { sli.Add(mb.Label); } else { sli.Add(mb.Qt.ActiveLabel + " (" + mb.Qt.MetaTable.Name + ")"); } string formattedSql = OracleMx.FormatSql(sqlWithDbLinks); sli.Add(formattedSql); SqlList.Add(sli); return; }
/// <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> /// 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> /// 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> /// Get a metatable from a database catalog /// </summary> /// <param name="schemaDotTableName"></param> /// <returns></returns> public static MetaTable GetMetaTableFromDatabaseDictionary( string schemaDotTableName) { string[] sa = schemaDotTableName.Split('.'); if (sa.Length != 2) { return(null); } string schemaName = sa[0]; string tableName = sa[1]; DbSchemaMx schema = DbSchemaMx.GetSchemaInfoFromName(schemaName); if (schema == null) { return(null); // unknown schema } if (Lex.IsDefined(schema.AliasFor)) { schemaName = schema.AliasFor; } string dsName = schema.DataSourceName; DataSourceMx dataSource = DbConnectionMx.GetRootDataSource(schemaDotTableName); if (dataSource == null) { return(null); } DbConnectionMx conn = DbConnectionMx.GetConnection(dataSource.DataSourceName); if (dataSource.DbType == DatabaseType.MySql) { return(MySqlMx.GetMetaTableFromDatabaseDictionary(conn, schemaName, tableName)); } else if (dataSource.DbType == DatabaseType.Oracle) { return(OracleMx.GetMetaTableFromDatabaseDictionary(conn, schemaName, tableName)); } else if (dataSource.DbType == DatabaseType.ODBC) { return(OdbcMx.GetMetaTableFromDatabaseDictionary(conn, schemaName, tableName)); } throw new Exception("Database dictionary not supported for: " + schemaDotTableName); }
/// <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); }
/// <summary> /// Return true if this metatable maps to an ODBC source /// </summary> /// <param name="mt"></param> /// <returns></returns> public static bool IsOdbcMetatable(MetaTable mt) { if (DbConnectionMx.IsSqlFromOdbcSource(mt.TableMap)) { return(true); } else { return(false); } //if (Lex.Contains(mt.TableMap, "dcsgtr") || Lex.Contains(mt.TableMap, "knimeivdr")) return true; // hack for now // else return false; }
/// <summary> /// Be sure the table is truncated /// </summary> /// <param name="schema"></param> /// <param name="listName"></param> public static void TruncateListTable( string dsName, string listName) { DbCommandMx cmd = null; string fullTableName; DataSourceMx ds = GetDataSourceInfo(ref dsName, ref listName, out fullTableName); string sql = "truncate table " + fullTableName; try { DateTime t0 = DateTime.Now; cmd = new DbCommandMx(); DbConnectionMx dbc = DbConnectionMx.GetConnection(dsName); cmd.MxConn = dbc; cmd.Prepare(sql); cmd.ExecuteNonReader(); int tDelta = (int)TimeOfDay.Delta(t0); //DebugLog.Message("Truncate Table time: " + tDelta); } catch (Exception ex) // if truncate failed see if need to create { if (!ex.Message.Contains("ORA-00942")) { throw ex; // if other than already exists then throw exception } bool created = CreateListTableIfNeeded(dsName, listName); } finally { if (cmd != null) { cmd.Dispose(); } } return; }
public static List <DbColumnMetadata> GetTableMetadataFromMySqlDictionary( DbConnectionMx conn, string schema, string tableName) { string sql = String.Format( @"SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = '{0}' AND table_name = '{1}' ORDER BY ORDINAL_POSITION" , schema, tableName); DbCommandMx drd = new DbCommandMx(); drd.MxConn = conn; drd.PrepareUsingDefinedConnection(sql); DbDataReader rdr = drd.ExecuteReader(); List <DbColumnMetadata> md = new List <DbColumnMetadata>(); while (rdr.Read()) { DbColumnMetadata cmd = new DbColumnMetadata(); cmd.Name = drd.GetStringByName("column_name"); cmd.Type = drd.GetStringByName("data_type"); cmd.Length = drd.GetLongByName("character_maximum_length"); cmd.Precision = drd.GetIntByName("numeric_precision"); cmd.Scale = drd.GetIntByName("numeric_scale"); cmd.Nullable = drd.GetStringByName("is_nullable"); cmd.Comment = drd.GetStringByName("column_comment"); md.Add(cmd); } rdr.Close(); return(md); }
public static int SelectInt( DbConnectionMx mxConn, string sql) { int value; DbDataReader dr = null; DbCommandMx drd = new DbCommandMx(); drd.MxConn = mxConn; // set connection to use drd.Prepare(sql); dr = drd.ExecuteReader() as OracleDataReader; if (!dr.Read()) { throw (new Exception("SelectInt Read failed")); } value = drd.GetInt(0); dr.Close(); drd.Dispose(); return(value); }
/// <summary> /// Load data into PubChem database /// PubChem assay data files are downloaded from the PubChem site: /// http://pubchem.ncbi.nlm.nih.gov/ using a program like SmartFTP. /// The files are in GNU Zip (.gz) format and can be unzipped with /// the following gzip commands: /// c:\gzip\gzip -d c:\pubchem\bioassay\csv\description\*.gz /// c:\gzip\gzip -d c:\pubchem\bioassay\csv\data\*.gz /// After downloading and decompression this method can be called on the files. /// </summary> /// <param name="args"></param> /// <returns></returns> public static string LoadData( string aid) { int recCount = 0; string mtName = "PubChem_aid_" + aid; MetaTable mt = MetaTableCollection.Get(mtName); if (mt == null) { return("Failed to get metatable"); } // if (Math.Sqrt(4) == 2) goto UpdateCids; string fileName = PubChemAssayDirectory + @"\CSV\Data\" + aid + ".csv"; StreamReader sr; try { sr = new StreamReader(fileName); } catch (Exception ex) { return("File not found: " + fileName); } string header = sr.ReadLine(); // read headers line List <string> headers = Csv.SplitCsvString(header); int cidIdx = -1; for (cidIdx = 0; cidIdx < headers.Count; cidIdx++) { if (headers[cidIdx].ToUpper() == "PUBCHEM_CID") { break; } } if (cidIdx >= headers.Count) { sr.Close(); return("PUBCHEM_CID column not found in data headers"); } Dictionary <string, MetaColumn> mcd = new Dictionary <string, MetaColumn>(); foreach (MetaColumn mc2 in mt.MetaColumns) { mcd[mc2.Name.ToUpper()] = mc2; // build dict for quick metacolumn lookup } DbConnectionMx conn = DbConnectionMx.MapSqlToConnection(ref PubChemWarehouseTable); conn.BeginTransaction(); // do multiple updates per transaction GenericDwDao dao = new GenericDwDao( PubChemWarehouseTable, // table for results PubChemWarehouseSeq); // sequence to use dao.BufferInserts(true); // buffer inserts for better speed SequenceDao.SetCacheSize(PubChemWarehouseSeq, 100); // number of ids to cache locally from sequence //string progressMsg = "Deleting existing data..."; int i1 = dao.DeleteTable(Int32.Parse(mt.TableFilterValues[0]), true); //if (Progress.CancelRequested()) //{ // dao.Dispose(); // return "Cancelled during data delete"; //} //Progress.Show("Loading file..."); recCount = 0; int t1 = 0; while (true) { int t2 = TimeOfDay.Milliseconds(); if (t2 - t1 > 1000) { if (Progress.CancelRequested) { dao.ExecuteBufferedInserts(); conn.Commit(); conn.Close(); sr.Close(); Progress.Hide(); return(recCount.ToString() + " rows loaded"); } Progress.Show("Loading file (" + recCount.ToString() + ") ..."); t1 = t2; } string rec = sr.ReadLine(); if (rec == null) { break; } List <string> vals = Csv.SplitCsvString(rec); int cid; try { cid = Int32.Parse(vals[cidIdx]); } // get compound id catch (Exception ex) { string txtCid = vals[cidIdx]; if (txtCid == null) { txtCid = ""; } DebugLog.Message("Load PubChem bad CID " + txtCid + ", AID = " + aid); continue; } long rslt_grp_id = dao.GetNextIdLong(); // id to hold row together for (int vi = 0; vi < vals.Count; vi++) { string s = vals[vi]; if (s == "") { continue; } string[] sa = rec.Split(','); if (vi >= headers.Count) { continue; } string mcName = headers[vi].ToUpper(); if (mcName.Length > 26) { mcName = mcName.Substring(0, 26); // limit length to 26 } if (mcName == "PUBCHEM_CID") { continue; } if (Lex.IsInteger(mcName)) { mcName = "R_" + mcName; // result number } MetaColumn mc = mcd[mcName]; if (mc == null) { continue; } AnnotationVo vo = new AnnotationVo(); vo.rslt_grp_id = rslt_grp_id; if (mc.DataType == MetaColumnType.String) { vo.rslt_val_txt = s; } else if (mc.DataType == MetaColumnType.Number || mc.DataType == MetaColumnType.Integer) { try { vo.rslt_val_nbr = Convert.ToDouble(s); } catch (Exception e) { continue; } // just continue if bad } else if (mc.DataType == MetaColumnType.Date) { s = DateTimeMx.Normalize(s); if (s == null) { continue; } vo.rslt_val_dt = DateTimeMx.NormalizedToDateTime(s); } else if (mc.Name == "PUBCHEM_ACTIVITY_OUTCOME") // activity outcome is a dict value stored as an integer { try { vo.rslt_val_nbr = Convert.ToInt32(s); } catch (Exception e) { continue; } // just continue if bad } else if (mc.DataType == MetaColumnType.Hyperlink || mc.DataType == MetaColumnType.DictionaryId) { vo.rslt_val_txt = s; } else { continue; } vo.ext_cmpnd_id_nbr = cid; vo.ext_cmpnd_id_txt = cid.ToString(); vo.mthd_vrsn_id = Int32.Parse(mt.TableFilterValues[0]); vo.rslt_typ_id = Int32.Parse(mc.PivotValues[0]); vo.chng_op_cd = "I"; vo.chng_usr_id = Security.UserInfo.UserName; dao.Insert(vo); } // end of field loop recCount++; if (recCount % 100 == 0) { // commit after group of updates dao.ExecuteBufferedInserts(); conn.Commit(); conn.BeginTransaction(); // do multiple updates per transaction } } // end of record loop dao.ExecuteBufferedInserts(); conn.Commit(); conn.Close(); dao.Dispose(); sr.Close(); //UpdateCids: // Add any missing CIDs under method 1000000 Progress.Show("Updating CID table..."); string sql = "INSERT INTO " + PubChemWarehouseTable + "(ext_cmpnd_id_nbr,rslt_id,mthd_vrsn_id,rslt_typ_id,rslt_grp_id) " + "SELECT ext_cmpnd_id_nbr, " + PubChemWarehouseSeq + ".NEXTVAL,1000000,0,0 " + "FROM ( " + "SELECT UNIQUE ext_cmpnd_id_nbr " + "FROM " + PubChemWarehouseTable + " r1 " + "WHERE mthd_vrsn_id = " + aid + " " + "AND NOT EXISTS ( " + " SELECT * " + "FROM " + PubChemWarehouseTable + " r2 " + "WHERE mthd_vrsn_id = 1000000 " + "AND r2.ext_cmpnd_id_nbr = r1.ext_cmpnd_id_nbr) " + "and rownum <= 10000)"; DbCommandMx drd = new DbCommandMx(); drd.Prepare(sql); drd.BeginTransaction(); int newCids = 0; while (true) { int addedCids = drd.ExecuteNonReader(); if (addedCids == 0) { break; } newCids += addedCids; drd.Commit(); drd.BeginTransaction(); // do multiple updates per transaction Progress.Show("Updating CID table (" + newCids.ToString() + ")..."); } drd.Dispose(); Progress.Hide(); return(recCount.ToString() + " rows loaded for AID " + aid + " plus " + newCids.ToString() + " new CIDs"); }
/// <summary> /// Get metatable if name belongs to broker /// </summary> /// <param name="name"></param> /// <returns></returns> public MetaTable GetMetaTable( string name) { UserObject uo = null; int objectId, i1; string tok; Stopwatch sw = Stopwatch.StartNew(); if (!UserObject.IsAnnotationTableName(name) && !UserObject.IsUserDatabaseStructureTableName(name)) { return(null); } try { objectId = UserObject.ParseObjectIdFromInternalName(name); } catch (Exception ex) { return(null); } try { uo = UserObjectDao.Read(objectId); } catch (MobiusConnectionOpenException ex) { DbConnectionMx.ThrowSpecificConnectionOpenException(ex, "mbs_owner"); } if (uo == null) { return(null); } if (!Permissions.UserHasReadAccess(Security.UserName, uo)) { return(null); } MetaTable mt = MetaTable.Deserialize(uo.Content); if (mt == null) { return(null); // something wrong with the annotation table content } MetaColumn keyMc = mt.KeyMetaColumn; MetaColumn rootMc = mt.Root.KeyMetaColumn; if (rootMc.IsNumeric) // adjust key col mapping { keyMc.ColumnMap = "EXT_CMPND_ID_NBR"; } else { keyMc.ColumnMap = "EXT_CMPND_ID_TXT"; } while (mt.MetaColumns.Count > MetaTable.MaxColumns) // fixup for table with too many columns { mt.MetaColumns.RemoveAt(mt.MetaColumns.Count - 1); } foreach (MetaColumn mc in mt.MetaColumns) { // set DetailsAvailable for existing annotation tables */ if (mc.ResultCode != "") { mc.DetailsAvailable = true; } } mt.Label = uo.Name; // get latest name (may have changed if renamed after saved) if (MetaTableFactory.ShowDataSource) // add source to label if requested { mt.Label = MetaTableFactory.AddSourceToLabel(mt.Name, mt.Label); } long ms = sw.ElapsedMilliseconds; return(mt); }
/// <summary> /// Write temporary list /// </summary> /// <param name="dsName">Name of datasource</param> /// <param name="listName">Name of list</param> /// <param name="listItems">The list of items to write</param> /// <param name="numeric">Store list items in numeric column if true, text otherwise</param> public static string Write( string dsName, string listName, List <string> listItems, int firstKeyIdx, int keyCount, bool numeric) { DbCommandMx cmd = null; Oracle.DataAccess.Client.OracleDbType parmType; string fullTableName, sKey; int iKey, i1; object o; DataSourceMx ds = GetDataSourceInfo(ref dsName, ref listName, out fullTableName); TruncateListTable(dsName, listName); if (keyCount == 0) { return(null); // nothing to write } string sql = "insert into <schema.name> (<cols>) values(:0)"; sql = sql.Replace("<schema.name>", fullTableName); if (numeric) { sql = sql.Replace("<cols>", "intKey"); } else { sql = sql.Replace("<cols>", "stringKey"); } object[][] pva = DbCommandMx.NewObjectArrayArray(1, keyCount); // alloc insert row array if (numeric) { for (i1 = 0; i1 < keyCount; i1++) { if (int.TryParse(listItems[firstKeyIdx + i1], out iKey)) { pva[0][i1] = iKey; } else { pva[0][i1] = DBNull.Value; } } parmType = Oracle.DataAccess.Client.OracleDbType.Int32; } else // string keys { for (i1 = 0; i1 < keyCount; i1++) { sKey = listItems[firstKeyIdx + i1]; if (!Lex.IsNullOrEmpty(sKey)) { pva[0][i1] = sKey; } else { pva[0][i1] = DBNull.Value; } } parmType = Oracle.DataAccess.Client.OracleDbType.Varchar2; } try { DateTime t0 = DateTime.Now; cmd = new DbCommandMx(); DbConnectionMx dbc = DbConnectionMx.GetConnection(dsName); cmd.MxConn = dbc; cmd.Prepare(sql, parmType); int insCount = cmd.ExecuteArrayNonReader(pva, ref keyCount); int tDelta = (int)TimeOfDay.Delta(t0); if (Debug) { DebugLog.Message("Write TempDbList for KeyCount: " + insCount + ", Time: " + tDelta); } } catch (Exception ex) { throw ex; } finally { cmd.Dispose(); } return(fullTableName); }
/// <summary> /// Build and save SQL for use in Spotfire information link /// </summary> /// <param name="query"></param> /// <returns></returns> public static int SaveSpotfireSql( string sqlStmtName, Query query) { DbConnectionMx conn = null; string qtSql = "", sql, expr; string keys = null; string keyColName = ""; string t1KeyColExpr = "", keyColExpr; string rootDataSource = "DEV857"; string rootSchema = "MBS_OWNER"; //if (query.Tables.Count != 1) throw new Exception("Can only save Spotfire Sql for single-table queries"); Query q = query.Clone(); // Clean up query before generating SQL q.KeyCriteria = q.KeyCriteriaDisplay = ""; List <QueryTable> qtToRemove = new List <QueryTable>(); foreach (QueryTable qt0 in q.Tables) { if (qt0.SelectedCount <= 1 || !qt0.MetaTable.RetrievesDataFromQueryEngine) { qtToRemove.Add(qt0); continue; } foreach (QueryColumn qc0 in qt0.QueryColumns) // clear any criteria { if (Lex.IsDefined(qc0.Criteria)) { qc0.Criteria = qc0.CriteriaDisplay = ""; } } } foreach (QueryTable qt0 in qtToRemove) { q.RemoveQueryTable(qt0); } string selectList = ""; // top-level list of selected columns string fromList = ""; // sql for each QueryTable string joinCriteria = ""; // join criteria between QueryTables int remapCount = 0; q.AssignUndefinedAliases(); q.MarkDuplicateNamesAndLabels(); for (int ti = 0; ti < q.Tables.Count; ti++) { QueryTable qt = q.Tables[ti]; if (ti == 0) { keyColName = qt.MetaTable.KeyMetaColumn.Name; } QueryEngine qe = new QueryEngine(); ExecuteQueryParms eqp = new ExecuteQueryParms(qe, qt); eqp.ReturnQNsInFullDetail = false; qtSql = qe.BuildSqlForSingleTable(eqp); qtSql = Lex.Replace(qtSql, "/*+ hint */ ", ""); conn = DbConnectionMx.MapSqlToConnection(ref qtSql, rootDataSource, rootSchema); // convert SQL to use dblinks from root source/schema if (conn == null) { throw new Exception("Connection not found for: " + rootDataSource); } // Recast numeric cols that are integers as integers for Spotfire List <DbColumnMetadata> cmdList = OracleMx.GetColumnMetadataFromSql(qtSql, conn); string qtSelectList = ""; remapCount = 0; // number of cols remapped int sci = -1; foreach (QueryColumn qc in qt.QueryColumns) { if (!qc.Selected) { continue; } sci++; // synch with cmdList MetaColumn mc = qc.MetaColumn; string mcName = mc.Name; if (q.Tables.Count > 1) // if more than one table qualify by table name { mcName = qt.Alias + "." + mcName; } string colName = qc.UniqueName; //if (mc.Name == "CORP_SBMSN_ID") mc = mc; // debug //if (mc.DataType == MetaColumnType.CompoundId) mc = mc; // debug if (mc.IsNumeric && (mc.DataType == MetaColumnType.Integer || mc.DataType == MetaColumnType.CompoundId)) { DbColumnMetadata md = cmdList[sci]; expr = "cast (" + mcName + " as integer) " + colName; // integer same as number(22,0)--- " as number(38, 0)) " + expr; } else if (mcName != colName) { expr = mcName + " " + colName; remapCount++; } else { expr = mc.Name; } if (qtSelectList != "") { qtSelectList += ", "; } qtSelectList += expr; } if (selectList != "") { selectList += ", "; } selectList += qtSelectList; if (fromList != "") { fromList += ", "; } fromList += "(" + qtSql + ") " + qt.Alias; keyColExpr = qt.Alias + "." + qt.KeyQueryColumn.MetaColumn.Name; if (ti == 0) { t1KeyColExpr = keyColExpr; } else { if (joinCriteria != "") { joinCriteria += " and "; } joinCriteria += keyColExpr + " (+) = " + t1KeyColExpr; } } selectList += " "; // be sure last col name in list is delimited with a space if (q.Tables.Count == 1 && remapCount == 0) { sql = qtSql; // simple single table with no remapping of cols } else // combine list of elements { sql = "select " + selectList + " from " + fromList; if (joinCriteria != "") { sql += " where " + joinCriteria; } sql = "select * from (" + sql + ")"; // encapsulate the SQL } int v2 = SpotfireDao.InsertSpotfireSql(sqlStmtName, 0, sql, keyColName, null, Security.UserName); return(v2); }
/// <summary> /// Sync the Mobius CorpMoltable replicate used to retrieve Smiles /// Syntax: UpdateCorpDbMoltableMx [ ByDateRange | ByCorpIdRange | LoadMissing | <singleCorpId>] /// </summary> /// <returns></returns> public static string UpdateCorpDbMoltableMx( string args) { DateTime moleculeDateTime = DateTime.MinValue; double mw; string msg = "", sql = "", maxCorpIdSql, mf = "", chime = "", smiles = "", checkPointDate = "", helm = "", sequence = "", svg = ""; object[][] pva = null; int pvaCount = 0, CorpId, lowCorpId = 0, highCorpId = 0, srcMaxCorpId = 0; int SelectChunkSize = 20; // small chunks int InsertBufferSize = 10; //int SelectChunkSize = 100000; // big chunks //int InsertBufferSize = 1000; // Select data from corp_moltable by CorpId range const string SelectByCorpIdRange = @" SELECT m.corp_nbr, chime(m.ctab), m.molformula, m.molweight, null molsmiles, s.helm_txt, s.sequence_txt, m.molecule_date FROM corp_owner.corp_moltable m, corp_owner.corp_substance s where m.corp_nbr > 0 and s.corp_nbr = m.corp_nbr and (s.status_code is null or s.status_code = 'A') ORDER BY corp_nbr"; // Select data from corp_moltable by date range comparing to corp_moltable_mx const string SelectByDateRange = @" select m.corp_nbr, chime(m.ctab), m.molformula, m.molweight, null molsmiles, s.helm_txt, s.sequence_txt, m.molecule_date, m2.molecule_date from corp_owner.corp_moltable m, corp_owner.corp_substance s, corp_moltable_mx m2 where m.molecule_date > to_date('1-jan-1900 000000','DD-MON-YYYY HH24MISS') and s.corp_nbr = M.CORP_NBR and (s.status_code is null or s.status_code = 'A') and m2.corp_nbr (+) = m.corp_nbr and m2.molecule_date (+) != m.molecule_date order by m.molecule_date" ; // Select for missing smiles strings, ex: Update CorpDbMoltableMx LoadMissing mx.molecule_date > '1-jan-2014' const string SelectMissingSmilesFix = @" select /* check for CorpIds in corp_moltable not in corp_moltable_mx */ corp_nbr, chime(ctab), molformula, molweight, null molsmiles, helm_txt, sequence_txt, molecule_date from ( select m.*, s.helm_txt, s.sequence_txt, mx.molsmiles molsmiles_mx from corp_owner.corp_moltable m, corp_owner.corp_substance s, corp_moltable_mx mx where s.corp_nbr = M.CORP_NBR and (s.status_code is null or s.status_code = 'A') and mx.corp_nbr (+) = m.corp_nbr and 1=1 /* condition to substitute */ ) m where molsmiles_mx is null /* extra condition */ order by corp_nbr" ; // Insert missing helm info const string SelectMissingHelmFix = @" select /* check for CorpIds in corp_moltable not in corp_moltable_mx */ corp_nbr, chime(ctab), molformula, molweight, null molsmiles, helm_txt, sequence_txt, molecule_date from ( select m.*, s.helm_txt, s.sequence_txt, mx.molsmiles molsmiles_mx from corp_owner.corp_moltable m, corp_owner.corp_substance s, corp_moltable_mx mx where s.corp_nbr = M.CORP_NBR and (s.status_code is null or s.status_code = 'A') and mx.corp_nbr (+) = m.corp_nbr and 1=1 /* condition to substitute */ ) m where length(helm_txt) > 0 /* extra condition */ order by corp_nbr" ; // Secondary "large" structure table (~5k mols) const string SelectLargeMols = @" select corp_nbr, to_clob(molstructure), to_clob(molformula), molweight, molsmiles, null helm_txt, null sequence_txt, molecule_date from (select corp_srl_nbr corp_nbr, 'CompoundId=' || corp_srl_nbr molstructure, null ctab, mlclr_frml_txt molformula, mlclr_wgt molweight, null molsmiles, null molecule_date from rdm_owner.rdm_sbstnc where rdw_src_cd = 'LRG'" ; // Insert statement const string InsertSql = @" insert into mbs_owner.corp_moltable_mx ( corp_nbr, molstructure, molformula, molweight, molsmiles, molecule_date) values (:0, :1, :2, :3, :4, :5)" ; // Build select sql bool byDateRange = false, byCorpIdRange = false, missingFix = true, deleteExisting = true; string missingFixCriteria = ""; if (Lex.IsUndefined(args) || Lex.Eq(args, "ByDateRange")) { byDateRange = true; } else if (Lex.Eq(args, "ByCorpIdRange")) { byCorpIdRange = true; Progress.Show("Getting range of CorpIds to insert..."); maxCorpIdSql = "select max(corp_nbr) from corp_owner.corp_moltable"; // get highest CorpId in source db srcMaxCorpId = SelectSingleValueDao.SelectInt(maxCorpIdSql); if (srcMaxCorpId < 0) { srcMaxCorpId = 0; } maxCorpIdSql = "select max(corp_nbr) from mbs_owner.corp_moltable_mx"; // get highest CorpId in dest db highCorpId = SelectSingleValueDao.SelectInt(maxCorpIdSql); if (highCorpId < 0) { highCorpId = 0; } } else if (Lex.StartsWith(args, "LoadMissing")) { missingFix = true; if (args.Contains(" ")) { missingFixCriteria = args.Substring(10).Trim(); } } else if (int.TryParse(args, out srcMaxCorpId)) // single CorpId { byCorpIdRange = true; highCorpId = srcMaxCorpId - 1; // say 1 less is the max we have } else { return("Syntax: UpdateCorpDbMoltableMx [ ByDateRange | ByCorpIdRange | LoadMissing | <singleCorpId>]"); } Log("UpdateCorpDbMoltableMx started: " + args); int readCount = 0, insCount = 0, insertCount = 0, updateCount = 0, undefinedStructures = 0, smilesSuccess = 0, smilesFails = 0, helmStructures = 0; List <string> CorpIdList = new List <string>(); for (int chunk = 1; ; chunk++) // loop over chunks { if (byDateRange) // single chunk { if (chunk > 1) { break; // break 2nd time through } checkPointDate = UserObjectDao.GetUserParameter("MOBIUS", "UpdateCorpDbMoltableMxCheckpointDate", "01-sep-2013 000000"); //UserObjectDao.SetUserParameter("MOBIUS", "UpdateCorpDbMoltableMxCheckpointDate", checkPointDate); sql = Lex.Replace(SelectByDateRange, "1-jan-1900 000000", checkPointDate); msg = "Reading where date >= " + checkPointDate; } else if (byCorpIdRange) // by CorpId range { if (highCorpId >= srcMaxCorpId) { break; // done } lowCorpId = highCorpId + 1; // start of next chunk highCorpId = lowCorpId + SelectChunkSize; if (highCorpId >= srcMaxCorpId) { highCorpId = srcMaxCorpId; } sql = Lex.Replace(SelectByCorpIdRange, "corp_nbr > 0", "corp_nbr between " + lowCorpId + " and " + highCorpId); msg = "Reading: " + lowCorpId + " to " + highCorpId + ", Reads: " + readCount + ", Inserts: " + insertCount; } else if (missingFix) { if (chunk > 1) { break; // break 2nd time through } sql = SelectMissingHelmFix; if (Lex.IsDefined(missingFixCriteria)) // substitute any criteria { sql = Lex.Replace(sql, "1=1", missingFixCriteria); } msg = "Fixing missing data"; } Progress.Show(msg); DbCommandMx readCmd = new DbCommandMx(); readCmd.MxConn = DbConnectionMx.GetConnection("prd123"); readCmd.PrepareUsingDefinedConnection(sql, null); DbDataReader rdr = readCmd.ExecuteReader(); DbCommandMx insertCmd = new DbCommandMx(); OracleDbType[] pta = new OracleDbType[6]; pta[0] = OracleDbType.Int32; // corp_nbr pta[1] = OracleDbType.Clob; // molstructure pta[2] = OracleDbType.Clob; // molformula pta[3] = OracleDbType.Double; // molweight pta[4] = OracleDbType.Clob; // smiles pta[5] = OracleDbType.Date; // molecule_date insertCmd.Prepare(InsertSql, pta); insertCmd.BeginTransaction(); // be sure we have a transaction going pva = DbCommandMx.NewObjectArrayArray(6, InsertBufferSize); // alloc insert row array object[] vo = new object[6]; while (true) { bool readOk = rdr.Read(); if (readOk) { rdr.GetValues(vo); CorpId = readCmd.GetInt(0); // corp_nbr vo[0] = CorpId; CorpIdList.Add(CorpId.ToString()); if (!readCmd.IsNull(1)) // molstructure { chime = readCmd.GetClob(1); chime = OracleMx.ClearStringIfExceedsMaxStringSize(chime); vo[1] = chime; } else { chime = ""; } if (!readCmd.IsNull(2)) // molformula { mf = readCmd.GetClob(2); mf = OracleMx.ClearStringIfExceedsMaxStringSize(mf); vo[2] = mf; } if (!readCmd.IsNull(3)) // molweight { mw = readCmd.GetDouble(3); vo[3] = mw; } if (Lex.IsDefined(chime)) // molsmiles - calculate from chime string { MoleculeMx cs = new MoleculeMx(MoleculeFormat.Chime, chime); if (cs.AtomCount > 1) // need more than one atom { MoleculeMx cs2 = cs.ConvertTo(MoleculeFormat.Smiles); smiles = cs2.GetSmilesString(); if (Lex.IsDefined(smiles)) { smilesSuccess++; } else { Log("Smiles conversion failure for CorpId: " + CorpId); smilesFails++; } smiles = OracleMx.ClearStringIfExceedsMaxStringSize(smiles); vo[4] = smiles; } else { undefinedStructures++; } } else { undefinedStructures++; } if (!readCmd.IsNull(5)) { helm = readCmd.GetClob(5); if (Lex.IsDefined(helm)) { svg = HelmControl.GetSvg(helm); vo[1] = SvgUtil.CompressSvgString(svg); // store compressed svg in molstructure column for now helmStructures++; } } if (!readCmd.IsNull(6)) { sequence = readCmd.GetClob(6); if (Lex.IsDefined(sequence)) { // nothing yet } } moleculeDateTime = DateTime.MinValue; if (!readCmd.IsNull(7)) // molecule_date { moleculeDateTime = readCmd.GetDateTime(7); vo[5] = moleculeDateTime; } for (int pi = 0; pi < 6; pi++) // invert for insert { pva[pi][pvaCount] = vo[pi]; } if (Debug) { msg = String.Format("CorpId: {0}, mf: {1}, chime: {2}, smiles: {3}", CorpId.ToString(), mf.Length, chime.Length, smiles.Length); Log(msg); } pvaCount++; } if (pvaCount >= InsertBufferSize || (!readOk && pvaCount > 0)) // write if buffer full or at end { try { if (deleteExisting) { int delCount = DoDeletes(CorpIdList); updateCount += delCount; // count deletes as updates insertCount -= delCount; // subtract from inserts } CorpIdList.Clear(); insCount = insertCmd.ExecuteArrayNonReader(pva, ref pvaCount); insertCmd.Commit(); insertCmd.BeginTransaction(); insertCount += insCount; } catch (Exception ex) { throw new Exception(ex.Message, ex); } if (byDateRange) { string checkPointDate2 = String.Format("{0:dd-MMM-yyyy HHmmss}", moleculeDateTime); // format date time that will work with oracle UserObjectDao.SetUserParameter("MOBIUS", "UpdateCorpDbMoltableMxCheckpointDate", checkPointDate2); msg = "Processing where date >= " + checkPointDate + ", Reads: " + readCount + ", Inserts: " + insertCount + ", Updates: " + updateCount; } else if (byCorpIdRange) // CorpId range { msg = "Processing: " + lowCorpId + " to " + highCorpId + ", Reads: " + readCount + ", Inserts: " + insertCount; } else if (missingFix) { msg = "Fixing missing smiles, Updates: " + updateCount; } msg += String.Format(", Undefined structures: {0} , Smiles failures: {1}, Helms: {2}", undefinedStructures, smilesFails, helmStructures); Progress.Show(msg); } if (!readOk) { break; } readCount++; } readCmd.Dispose(); insertCmd.Dispose(); } // end for select chunk msg = "UpdateCorpDbMoltableMx - Inserts: " + insertCount + ", Updates: " + updateCount; msg += String.Format(", Undefined structures: {0} , Smiles failures: {1}, Helms: {2}", undefinedStructures, smilesFails, helmStructures); Log(msg); return(msg); }
/// <summary> /// Get a metatable from MySql catalog /// </summary> /// <param name="tableName">schema.table</param> /// <returns></returns> public static MetaTable GetMetaTableFromDatabaseDictionary( DbConnectionMx conn, string schema, string tableName) { int t0 = TimeOfDay.Milliseconds(); MetaTable mt = new MetaTable(); mt.MetaBrokerType = MetaBrokerType.Generic; mt.Name = tableName; mt.Label = MetaTable.IdToLabel(tableName); mt.TableMap = schema + "." + tableName; List <DbColumnMetadata> cmdList = GetTableMetadataFromMySqlDictionary(conn, schema, tableName); for (int ci = 0; ci < cmdList.Count; ci++) { DbColumnMetadata cmd = cmdList[ci]; MetaColumn mc = new MetaColumn(); mc.Name = cmd.Name; mc.ColumnMap = mc.Name; mc.Label = MetaTable.IdToLabel(cmd.Name); if (Lex.Contains(cmd.Type, "CHAR") || Lex.Contains(cmd.Type, "TEXT")) { mc.DataType = MetaColumnType.String; } else if (Lex.Contains(cmd.Type, "INT") || Lex.Contains(cmd.Type, "ENUM")) { mc.DataType = MetaColumnType.Integer; } else if (cmd.Type == "FLOAT" || cmd.Type == "REAL" || cmd.Type == "DOUBLE" || cmd.Type == "DECIMAL" || cmd.Type == "NUMERIC") { mc.DataType = MetaColumnType.Number; mc.Format = ColumnFormatEnum.Decimal; mc.Decimals = cmd.Scale; } else if (cmd.Type == "DATE" || cmd.Type == "DATETIME" || cmd.Type == "TIMESTAMP") { mc.DataType = MetaColumnType.Date; } else { continue; // unrecognized } mc.InitialSelection = ColumnSelectionEnum.Selected; mc.Width = 12; mc.MetaTable = mt; mc.Description = cmd.Comment; mt.AddMetaColumn(mc); } t0 = TimeOfDay.Milliseconds() - t0; return(mt); }
/// GetMetaTableFromOdbcDictionary /// </summary> /// <param name="tableName"></param> /// <returns></returns> public static MetaTable GetMetaTableFromDatabaseDictionary( DbConnectionMx conn, string schemaName, string tableName) { int t0 = TimeOfDay.Milliseconds(); MetaTable mt = new MetaTable(); mt.MetaBrokerType = MetaBrokerType.Generic; mt.Name = tableName; mt.Label = MetaTable.IdToLabel(tableName); mt.TableMap = tableName; string sql = "select * from " + tableName; DbCommandMx drd = new DbCommandMx(); drd.MxConn = conn; drd.PrepareUsingDefinedConnection(sql); drd.ExecuteReader(); DataTable st = drd.Rdr.GetSchemaTable(); for (int ri = 0; ri < st.Rows.Count; ri++) { DataRow dr = st.Rows[ri]; string column_name = dr["ColumnName"] as string; Type type = dr["DataType"] as Type; int data_length = (int)dr["ColumnSize"]; int data_precision = (short)dr["NumericPrecision"]; int data_scale = (short)dr["NumericScale"]; bool nullable = (bool)dr["AllowDBNull"]; MetaColumn mc = new MetaColumn(); mc.Name = column_name; mc.ColumnMap = mc.Name; mc.Label = MetaTable.IdToLabel(column_name); if (Lex.Eq(type.Name, "String")) { mc.DataType = MetaColumnType.String; } else if (Lex.StartsWith(type.Name, "Int")) { mc.DataType = MetaColumnType.Integer; } else if (Lex.Eq(type.Name, "Single") || Lex.Eq(type.Name, "Double")) { mc.DataType = MetaColumnType.Number; mc.Format = ColumnFormatEnum.SigDigits; // display with 3 sig figures by default mc.Decimals = 3; } else if (Lex.Eq(type.Name, "DateTime")) { mc.DataType = MetaColumnType.Date; } else { continue; // unrecognized } mc.InitialSelection = ColumnSelectionEnum.Selected; mc.Width = 12; mc.MetaTable = mt; mt.AddMetaColumn(mc); } drd.Dispose(); t0 = TimeOfDay.Milliseconds() - t0; return(mt); }
/// <summary> /// Get the next value for the sequence (MySQL) /// </summary> /// <param name="seqName"></param> /// <returns></returns> public static long NextValLongMySQL( string seqName) { string sql; long nextVal; SequenceDao seqDao = Lookup(seqName); Queue <long> seqQueue = seqDao.Queue; if (seqQueue.Count > 0) { nextVal = seqQueue.Dequeue(); return(nextVal); } int count = (seqDao.CacheSize > 0 ? seqDao.CacheSize : 1); int t0 = TimeOfDay.Milliseconds(); DbCommandMx seqCmd = new DbCommandMx(); seqCmd.MxConn = DbConnectionMx.GetConnection("MySql_Mobius"); // "MySql_Mobius_Sequences" sql = String.Format( @"update mbs_owner.mbs_sequences set value = last_insert_id(value) + {0} where name = '{1}'" , count, seqName.ToUpper()); seqCmd.PrepareUsingDefinedConnection(sql); int updCount = seqCmd.ExecuteNonReader(); if (updCount <= 0) { throw new Exception("Error updating sequence (may not exist): " + seqName); } sql = "select last_insert_id()"; // gets value before update above seqCmd.PrepareUsingDefinedConnection(sql, null); DbDataReader rdr = seqCmd.ExecuteReader(); bool readOk = rdr.Read(); AssertMx.IsTrue(readOk, "readOk"); long value = rdr.GetInt64(0); rdr.Close(); seqCmd.CloseReader(); nextVal = value + 1; // return this one now long v2 = value + 2; // next value long vn = value + count; // last value for (long vi = v2; vi <= vn; vi++) { seqQueue.Enqueue(vi); } t0 = TimeOfDay.Milliseconds() - t0; // DebugLog.Message("Read sequence, set size = " + seqQueue.Count.ToString() + ", Time(ms) = " + t0.ToString()); return(nextVal); }
/// <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> /// Get a metatable from Oracle catalog /// </summary> /// <param name="tableName">schema.table</param> /// <returns></returns> public static MetaTable GetMetaTableFromDatabaseDictionary( DbConnectionMx conn, string schemaName, string tableName) { int t0 = TimeOfDay.Milliseconds(); MetaTable mt = new MetaTable(); mt.MetaBrokerType = MetaBrokerType.Generic; mt.Name = tableName; mt.Label = MetaTable.IdToLabel(tableName); mt.TableMap = schemaName + "." + tableName; List <DbColumnMetadata> cmdList = GetTableMetadataFromOracleDictionary(conn, schemaName, tableName); for (int ci = 0; ci < cmdList.Count; ci++) { DbColumnMetadata cmd = cmdList[ci]; MetaColumn mc = new MetaColumn(); mc.Name = cmd.Name; mc.ColumnMap = mc.Name; mc.Label = MetaTable.IdToLabel(cmd.Name); if (cmd.Type == "VARCHAR" || cmd.Type == "VARCHAR2" || cmd.Type == "NVARCHAR2" || cmd.Type == "CHAR" || cmd.Type == "CHARACTER" || cmd.Type == "LONG") { mc.DataType = MetaColumnType.String; } else if (cmd.Type == "INTEGER") { mc.DataType = MetaColumnType.Integer; } else if (cmd.Type == "NUMBER" || cmd.Type == "FLOAT") { mc.DataType = MetaColumnType.Number; mc.Format = ColumnFormatEnum.SigDigits; // display with 3 sig figures by default mc.Decimals = 3; } else if (cmd.Type == "DATE" || cmd.Type.StartsWith("TIMESTAMP")) { mc.DataType = MetaColumnType.Date; } else { continue; // unrecognized } mc.InitialSelection = ColumnSelectionEnum.Selected; mc.Width = 12; mc.MetaTable = mt; mt.AddMetaColumn(mc); } t0 = TimeOfDay.Milliseconds() - t0; return(mt); }