protected override void BuildSelectClause(SelectQuery selectQuery) { if (selectQuery.HasUnion) { // need to set any column aliases as the same as the top level one var topquery = selectQuery; while (topquery.ParentSelect != null && topquery.ParentSelect.HasUnion) { topquery = topquery.ParentSelect; } var alia = selectQuery.Select.Columns.Select(c => c.Alias).ToArray(); selectQuery.Unions.ForEach((u) => { int colNo = 0; u.SelectQuery.Select.Columns .ForEach(c => { c.Alias = alia[colNo]; colNo++; }); }); } if (selectQuery.From.Tables.Count == 0) { AppendIndent().AppendLine("SELECT"); BuildColumns(selectQuery); AppendIndent().AppendLine($"FROM {DB2iSeriesTools.iSeriesDummyTableName()} FETCH FIRST 1 ROW ONLY"); } else { base.BuildSelectClause(selectQuery); } }
protected override void BuildInsertOrUpdateQuery(SqlInsertOrUpdateStatement insertOrUpdate) => BuildInsertOrUpdateQueryAsMerge(insertOrUpdate, $"FROM {DB2iSeriesTools.iSeriesDummyTableName()} FETCH FIRST 1 ROW ONLY");
protected override void BuildCommand(SqlStatement selectQuery, int commandNumber) => StringBuilder.AppendLine($"SELECT {DB2iSeriesTools.IdentityColumnSql} FROM {DB2iSeriesTools.iSeriesDummyTableName()}");
protected override List <TableInfo> GetTables(DataConnection dataConnection) { var sql = $@" Select CAST(CURRENT_SERVER AS VARCHAR(128)) AS Catalog_Name , Table_Schema , Table_Name , Table_Text , Table_Type , System_Table_Schema From {dataConnection.GetTableName("QSYS2", "SYSTABLES")} Where Table_Type In('L', 'P', 'T', 'V') And System_Table_Schema in ('{GetLibList(dataConnection)}') Order By System_Table_Schema, System_Table_Name " ; var defaultSchema = dataConnection.Execute <string>("select current_schema from " + DB2iSeriesTools.iSeriesDummyTableName(dataConnection)); Func <IDataReader, TableInfo> drf = (IDataReader dr) => new TableInfo { CatalogName = dr["Catalog_Name"].ToString().TrimEnd(), Description = dr["Table_Text"].ToString().TrimEnd(), IsDefaultSchema = dr["System_Table_Schema"].ToString().TrimEnd() == defaultSchema, IsView = new[] { "L", "V" }.Contains <string>(dr["Table_Type"].ToString()), SchemaName = dr["Table_Schema"].ToString().TrimEnd(), TableID = dataConnection.Connection.Database + "." + dr["Table_Schema"].ToString().TrimEnd() + "." + dr["Table_Name"].ToString().TrimEnd(), TableName = dr["Table_Name"].ToString().TrimEnd() }; var _list = dataConnection.Query(drf, sql).ToList(); return(_list); }
protected override List <ProcedureInfo> GetProcedures(DataConnection dataConnection) { var sql = $@" Select CAST(CURRENT_SERVER AS VARCHAR(128)) AS Catalog_Name , Function_Type , Routine_Definition , Routine_Name , Routine_Schema , Routine_Type , Specific_Name , Specific_Schema From {dataConnection.GetTableName("QSYS2", "SYSROUTINES")} Where Specific_Schema in('{GetLibList(dataConnection)}') Order By Specific_Schema, Specific_Name " ; //And {GetSchemaFilter("col.TBCREATOR")} var defaultSchema = dataConnection.Execute <string>("select current_schema from " + DB2iSeriesTools.iSeriesDummyTableName(dataConnection)); ProcedureInfo drf(IDataReader dr) { return(new ProcedureInfo { CatalogName = Convert.ToString(dr["Catalog_Name"]).TrimEnd(), IsDefaultSchema = Convert.ToString(dr["Routine_Schema"]).TrimEnd() == defaultSchema, IsFunction = Convert.ToString(dr["Routine_Type"]) == "FUNCTION", IsTableFunction = Convert.ToString(dr["Function_Type"]) == "T", ProcedureDefinition = Convert.ToString(dr["Routine_Definition"]).TrimEnd(), ProcedureID = dataConnection.Connection.Database + "." + Convert.ToString(dr["Specific_Schema"]).TrimEnd() + "." + Convert.ToString(dr["Specific_Name"]).TrimEnd(), ProcedureName = Convert.ToString(dr["Routine_Name"]).TrimEnd(), SchemaName = Convert.ToString(dr["Routine_Schema"]).TrimEnd() }); } var list = dataConnection.Query(drf, sql).ToList(); return(list); }
protected override void BuildCommand(int commandNumber) { StringBuilder.AppendLine(string.Format("SELECT {0} FROM {1}", DB2iSeriesTools.IdentityColumnSql, DB2iSeriesTools.iSeriesDummyTableName())); }
protected override void BuildInsertOrUpdateQuery() { BuildInsertOrUpdateQueryAsMerge(string.Format("FROM {0} FETCH FIRST 1 ROW ONLY", DB2iSeriesTools.iSeriesDummyTableName())); }