/// <summary> /// Read the initial version of the base version of the specified Spotfire SQL /// </summary> /// <param name="name"></param> /// <returns></returns> public static string ReadSpotfireSql( string name) { string spotfireSql = SpotfireDao.ReadSqlStatement(name, 0); return(spotfireSql); }
/// <summary> /// Read the initial version of the specified version of the named Spotfire SQL stmt /// </summary> /// <param name="name"></param> /// <param name="version"></param> /// <returns></returns> public static string ReadSpotfireSql( string name, int version) { string spotfireSql = SpotfireDao.ReadSqlStatement(name, version); return(spotfireSql); }
/// <summary> /// Save a list of keys for later inclusion in SQL statements /// </summary> /// <param name="listType">CIDLIST, ASSAYLIST</param> /// <param name="keyList"></param> /// <returns></returns> public static string SaveSpotfireKeyList( string keyColName, string listType, string keyList) { string subList; string listName = listType + "_"; // start building name int v1 = -2; // modify name with next version while (true) // break into 4000 char chunks for later use by Oracle function { if (keyList.Length < 4000) { subList = keyList; keyList = ""; } else { subList = keyList.Substring(0, 4000); int i1 = subList.LastIndexOf(","); subList = subList.Substring(0, i1); keyList = keyList.Substring(i1 + 1); } int v2 = SpotfireDao.InsertSpotfireSql(listName, v1, null, keyColName, subList, Security.UserName); if (v1 == -2) // first time? { listName += v2; // all sublists will have this list name v1 = -1; } if (keyList.Length == 0) { break; } } return(listName); }
/// <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); }