Hello,

I am developing my silverstripe page with Microsoft SQL Server and there are some problems with the event calendar module.

1. It uses '`' hardcoded as field and table qualifiers. SQL Server only accepts '"' or square brackets '[' and ']'. The base silverstripe framework uses '"', so I think there should be some constant for this...

2. The query for getrecurrengEvents has a Sort Order Defined: "`CalendarDateTime`.StartDate ASC"
SqL Server won't accept this. The table CalendarDateTime is joined in the query, but no field of it is in the group by clause, so SQL Server won't allow this sort order.

The whole Query with replaced field qualifiers:
SELECT
"SiteTree_Live"."ClassName",
"SiteTree_Live"."Created",
"SiteTree_Live"."LastEdited",
"SiteTree_Live"."URLSegment",
"SiteTree_Live"."Title",
"SiteTree_Live"."MenuTitle",
"SiteTree_Live"."Content",
"SiteTree_Live"."MetaTitle",
"SiteTree_Live"."MetaDescription",
"SiteTree_Live"."MetaKeywords",
"SiteTree_Live"."ExtraMeta",
"SiteTree_Live"."ShowInMenus",
"SiteTree_Live"."ShowInSearch",
"SiteTree_Live"."HomepageForDomain",
"SiteTree_Live"."ProvideComments",
"SiteTree_Live"."Sort",
"SiteTree_Live"."HasBrokenFile",
"SiteTree_Live"."HasBrokenLink",
"SiteTree_Live"."Status",
"SiteTree_Live"."ReportClass",
"SiteTree_Live"."CanViewType",
"SiteTree_Live"."CanEditType",
"SiteTree_Live"."ToDo",
"SiteTree_Live"."Version",
"SiteTree_Live"."Priority",
"SiteTree_Live"."ParentID",
"Page_Live"."ImageID",
"CalendarEvent_Live"."Recursion",
"CalendarEvent_Live"."CustomRecursionType",
"CalendarEvent_Live"."DailyInterval",
"CalendarEvent_Live"."WeeklyInterval",
"CalendarEvent_Live"."MonthlyInterval",
"CalendarEvent_Live"."MonthlyRecursionType1",
"CalendarEvent_Live"."MonthlyRecursionType2",
"CalendarEvent_Live"."MonthlyIndex",
"CalendarEvent_Live"."MonthlyDayOfWeek",
"CalendarEvent_Live"."CalendarID",
"SiteTree_Live"."ID",
CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName" ELSE 'SiteTree' END AS "RecordClassName"
FROM "SiteTree_Live"
LEFT JOIN "Page_Live" ON "Page_Live"."ID" = "SiteTree_Live"."ID"
LEFT JOIN "CalendarEvent_Live" ON "CalendarEvent_Live"."ID" = "SiteTree_Live"."ID"
LEFT JOIN "CalendarDateTime" ON "CalendarDateTime".EventID = "CalendarEvent_Live".ID
WHERE
("SiteTree_Live"."ClassName" IN ('CalendarEvent'))
AND (Recursion = 1 AND ParentID = 12)
GROUP BY
"SiteTree_Live"."ClassName",
"SiteTree_Live"."Created",
"SiteTree_Live"."LastEdited",
"SiteTree_Live"."URLSegment",
"SiteTree_Live"."Title",
"SiteTree_Live"."MenuTitle",
"SiteTree_Live"."Content",
"SiteTree_Live"."MetaTitle",
"SiteTree_Live"."MetaDescription",
"SiteTree_Live"."MetaKeywords",
"SiteTree_Live"."ExtraMeta",
"SiteTree_Live"."ShowInMenus",
"SiteTree_Live"."ShowInSearch",
"SiteTree_Live"."HomepageForDomain",
"SiteTree_Live"."ProvideComments",
"SiteTree_Live"."Sort",
"SiteTree_Live"."HasBrokenFile",
"SiteTree_Live"."HasBrokenLink",
"SiteTree_Live"."Status",
"SiteTree_Live"."ReportClass",
"SiteTree_Live"."CanViewType",
"SiteTree_Live"."CanEditType",
"SiteTree_Live"."ToDo",
"SiteTree_Live"."Version",
"SiteTree_Live"."Priority",
"SiteTree_Live"."ParentID",
"Page_Live"."ImageID",
"CalendarEvent_Live"."Recursion",
"CalendarEvent_Live"."CustomRecursionType",
"CalendarEvent_Live"."DailyInterval",
"CalendarEvent_Live"."WeeklyInterval",
"CalendarEvent_Live"."MonthlyInterval",
"CalendarEvent_Live"."MonthlyRecursionType1",
"CalendarEvent_Live"."MonthlyRecursionType2",
"CalendarEvent_Live"."MonthlyIndex",
"CalendarEvent_Live"."MonthlyDayOfWeek",
"CalendarEvent_Live"."CalendarID",
"SiteTree_Live"."ID",
CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName" ELSE 'SiteTree' END
ORDER BY "CalendarDateTime".StartDate ASC

The Error message:

Column "CalendarDateTime.StartDate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

First I will try to get rid of the field qualifier thing... Perhaps anyone has an idea about the second thing...

BR
Markus Alexander

Hi Markus,

that was something that we went through for all the Silverstripe maintained modules. In BrokenLinksReport.php you can see what the core team did to maintain backwards compatibility with 2.3:

$q = DB::USE_ANSI_SQL ? '"' : '`';

Regarding the second half of your problem, have you tried just appending it to the list of returned columns? Like:

SELECT ..., "CalendarDateTime"."StartDate" FROM ...

If you fix Calendar for SQL Server it would be great if you'd share the patch and maybe send it to UC.

Cheers

Andy

Hi Andy,

yes I have seen this in the module as I have search for '`' through the whole SilverStripe source code. By now I have just done a search and replace with '"'. I see that your solution works, but in my opinion this has to be done on Database Module level. I would create an abstract function QualifyIdentifier at the base DB module which would do the job. Each other module has to implement this function and do the proper qualifying. Although SQL Server will accept '"' as qualifiers but the preferred way should be using square brackets. This could be handled within such a function and you only have to do it once, not in every file.
Last point: This problem is throughout all Modules from UncleCheese ;-)

Regarding the second problem: As I don't need DateTimes but only Announcements it is ok that I just deleted the sort order.

BR
Markus

Hi Markus,

you are right, we shouldn't have to bother. The whole problem is solved in 2.4: double quotes only, period. They are the only standard compliant enclosures for identifiers and work for all adapters (MySQL, SQLite, Postgres, SQL Server), no backticks, no brackets, they are proprietary.

The only reason for the proposed admittedly ugly DB::USE_ANSI_SQL approach is for legacy code where we can't fix it on the adapter level.

For me the idea of open source code is you get it for free and in return when you find a bug you fix it and give back, so everybody is benefitting. Save the next SQL Server dev from the same hassle you had and support UC ;-) That would be great.

Grüße

Andy

Hi Andy,

ok then lets use the double quotes ;-). I will have a look at the second problem, because this should be fixable too...
And then of course I will give this back to Uncle Cheese...

BR
Markus

awsome, that's the spirit ;) thx