Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

AnsiSqlGenerator causes SQL syntax error for "where ... in (new select...)" statement #8

Open
blankers opened this issue Nov 12, 2009 · 2 comments

Comments

@blankers
Copy link

Problem:
duplicate clause is being generated by the following SqlQuery object when using AnsiSqlGenerator:

SubSonic.SqlQuery q = new Select()
  .From(Views.VwSearchIndexQuery2Mtx)
  .Paged(pageNumber, maximumRows)
  .Where(VwSearchIndexQuery2Mtx.Columns.SearchIndexQueryId)
    .In(
        new Select(SearchIndexQueryGroupMap.Columns.SearchIndexQueryId)
          .From(Tables.SearchIndexQueryGroupMap)
          .Where(SearchIndexQueryGroupMap.Columns.SearchIndexQueryGroupId)
          .IsEqualTo(searchIndexQueryGroupId));

This statement works using the Sql2005 & Sql2008 generators.

Reference:
http://stackoverflow.com/questions/1711798/subsonic-2-2-sqlquery-object-generates-very-different-sql-for-where-in-stateme

@The-Running-Dev
Copy link

This is the same issue that's caused by the one documented in issue 7. The IsSQL2008 function returns false if you are using R2 or SP1 of SQL Server 2008.

@lenrock
Copy link

lenrock commented May 3, 2012

how to fix:

in ANSISqlGenerator.cs > virtual string BuildPagedSelectStatement()
change string tweakedWheres = wheres.Replace("WHERE", "AND");
to: string tweakedWheres = Sugar.Strings.ReplaceFirst(wheres, "WHERE", "AND", StringComparison.OrdinalIgnoreCase);

and add to sugar > String new method :

///

    /// Replace only first match
    /// </summary>


    /// <param name="originalString"></param>
    /// <param name="oldValue"></param>
    /// <param name="newValue"></param>
    /// <param name="comparisonType"></param>
    /// <returns></returns>
    public static string ReplaceFirst(string originalString, string oldValue, string newValue, StringComparison comparisonType)
    {
        int startIndex = 0;
            startIndex = originalString.IndexOf(oldValue, startIndex, comparisonType);
            if (startIndex != -1)
            {

                originalString = originalString.Substring(0, startIndex) + newValue + originalString.Substring(startIndex + oldValue.Length);

                startIndex += newValue.Length;
            }


        return originalString;
    }

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants