/// <summary>Returns the query for selecting the records from an Oracle DB.</summary> protected internal override string GetSelectQuery() { StringBuilder query = new StringBuilder(); DBConfiguration dbConf = GetDBConf(); string conditions = GetConditions(); string tableName = GetTableName(); string[] fieldNames = GetFieldNames(); // Oracle-specific codepath to use rownum instead of LIMIT/OFFSET. if (dbConf.GetInputQuery() == null) { query.Append("SELECT "); for (int i = 0; i < fieldNames.Length; i++) { query.Append(fieldNames[i]); if (i != fieldNames.Length - 1) { query.Append(", "); } } query.Append(" FROM ").Append(tableName); if (conditions != null && conditions.Length > 0) { query.Append(" WHERE ").Append(conditions); } string orderBy = dbConf.GetInputOrderBy(); if (orderBy != null && orderBy.Length > 0) { query.Append(" ORDER BY ").Append(orderBy); } } else { //PREBUILT QUERY query.Append(dbConf.GetInputQuery()); } try { DBInputFormat.DBInputSplit split = GetSplit(); if (split.GetLength() > 0) { string querystring = query.ToString(); query = new StringBuilder(); query.Append("SELECT * FROM (SELECT a.*,ROWNUM dbif_rno FROM ( "); query.Append(querystring); query.Append(" ) a WHERE rownum <= ").Append(split.GetEnd()); query.Append(" ) WHERE dbif_rno > ").Append(split.GetStart()); } } catch (IOException) { } // ignore, will not throw. return(query.ToString()); }
/// <summary> /// Returns the query for selecting the records, /// subclasses can override this for custom behaviour. /// </summary> protected internal virtual string GetSelectQuery() { StringBuilder query = new StringBuilder(); // Default codepath for MySQL, HSQLDB, etc. Relies on LIMIT/OFFSET for splits. if (dbConf.GetInputQuery() == null) { query.Append("SELECT "); for (int i = 0; i < fieldNames.Length; i++) { query.Append(fieldNames[i]); if (i != fieldNames.Length - 1) { query.Append(", "); } } query.Append(" FROM ").Append(tableName); query.Append(" AS ").Append(tableName); //in hsqldb this is necessary if (conditions != null && conditions.Length > 0) { query.Append(" WHERE (").Append(conditions).Append(")"); } string orderBy = dbConf.GetInputOrderBy(); if (orderBy != null && orderBy.Length > 0) { query.Append(" ORDER BY ").Append(orderBy); } } else { //PREBUILT QUERY query.Append(dbConf.GetInputQuery()); } try { query.Append(" LIMIT ").Append(split.GetLength()); query.Append(" OFFSET ").Append(split.GetStart()); } catch (IOException) { } // Ignore, will not throw. return(query.ToString()); }
/// <summary> /// Returns the query for selecting the records, /// subclasses can override this for custom behaviour. /// </summary> protected internal override string GetSelectQuery() { StringBuilder query = new StringBuilder(); DataDrivenDBInputFormat.DataDrivenDBInputSplit dataSplit = (DataDrivenDBInputFormat.DataDrivenDBInputSplit )GetSplit(); DBConfiguration dbConf = GetDBConf(); string[] fieldNames = GetFieldNames(); string tableName = GetTableName(); string conditions = GetConditions(); // Build the WHERE clauses associated with the data split first. // We need them in both branches of this function. StringBuilder conditionClauses = new StringBuilder(); conditionClauses.Append("( ").Append(dataSplit.GetLowerClause()); conditionClauses.Append(" ) AND ( ").Append(dataSplit.GetUpperClause()); conditionClauses.Append(" )"); if (dbConf.GetInputQuery() == null) { // We need to generate the entire query. query.Append("SELECT "); for (int i = 0; i < fieldNames.Length; i++) { query.Append(fieldNames[i]); if (i != fieldNames.Length - 1) { query.Append(", "); } } query.Append(" FROM ").Append(tableName); if (!dbProductName.StartsWith("ORACLE")) { // Seems to be necessary for hsqldb? Oracle explicitly does *not* // use this clause. query.Append(" AS ").Append(tableName); } query.Append(" WHERE "); if (conditions != null && conditions.Length > 0) { // Put the user's conditions first. query.Append("( ").Append(conditions).Append(" ) AND "); } // Now append the conditions associated with our split. query.Append(conditionClauses.ToString()); } else { // User provided the query. We replace the special token with our WHERE clause. string inputQuery = dbConf.GetInputQuery(); if (inputQuery.IndexOf(DataDrivenDBInputFormat.SubstituteToken) == -1) { Log.Error("Could not find the clause substitution token " + DataDrivenDBInputFormat .SubstituteToken + " in the query: [" + inputQuery + "]. Parallel splits may not work correctly." ); } query.Append(inputQuery.Replace(DataDrivenDBInputFormat.SubstituteToken, conditionClauses .ToString())); } Log.Debug("Using query: " + query.ToString()); return(query.ToString()); }