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()));
}