I've used this module on past sites and it is great! However, I installed this module for a new site using IIS and MSSQL database. When going to admin/config/system/cron I received the error:

PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'rule'.: select name, disable, rule, weight, context, running, last_run, last_aborted, abort_count, last_abort_function, last_execution_time, execution_count, avg_execution_time, max_execution_time, last_shutdown_time from {elysia_cron} where name = 'ctools_cron'; Array ( ) in elysia_cron_get() (line 424 of C:...\sites\all\modules\contrib\elysia_cron\elysia_cron.module)

So I started poking around, and playing with the code, but I couldn't see any out-right problem. When I opened the database in mssql I ran the query

SELECT * FROM [mydatabase].[dbo].[elysia_cron]

But this complains "Invalid object name mydatabase.dbo.elysia_cron".

As it turns out, "rule" is a reserved keyword by MS SQL. I changed the table column name and updated the queries/module in my version, and it works, but just an FYI that this is an issue.

CommentFileSizeAuthor
#9 elysia_cron-mssql-support-1969000-9.patch2.44 KBdmitriy.trt
#5 elysia_cron-changed-rule-to-pattern-1969000.patch53.52 KBkimberlydb

Comments

jorditr’s picture

jorditr commented

I'm facing exactly the same problem. I've been using this excelent module on some sites but I'm building a new one where running on a Microsoft environment is requirement. All in all, almost everything works perfectly but I've found that issue with Elysia Cron.

A quick search with my IDE has returned that the string "rule" appears 337 times (despite half of them are on some "rules" cases). It looks to me that it takes a lot of time to detect which one to change but on the other hand it means that it's not going to be easy to be up to date.

Is there any chance to see that table name to be corrected on the module in order to be compatible with more server platforms?

gotheric’s picture

gotheric commented
Assigned: Unassigned » gotheric

Curios problem... i'll try to replace that name in db

nwom’s picture

nwom commented

I'm having the same issue sadly.

@kimberlydb: Would you by chance have a patch that I could apply until a new dev is released? It would be greatly appreciated.

d3claes’s picture

d3claes commented

In line 213 of elysia_cron.module you need to undo a recent commit:

$GLOBALS['_ec_columns'] = array(
-  'name', ' disable', ' rule', ' weight', ' context', ' running', ' last_run', ' last_aborted', ' abort_count', ' last_abort_function', ' last_execution_time', ' execution_count', ' avg_execution_time', ' max_execution_time', ' last_shutdown_time'
+  'name', ' disable', ' [rule]', ' weight', ' context', ' running', ' last_run', ' last_aborted', ' abort_count', ' last_abort_function', ' last_execution_time', ' execution_count', ' avg_execution_time', ' max_execution_time', ' last_shutdown_time'

When placed in brackets, sqlsrv no longer threads it as a reserved keyword.

kimberlydb’s picture

kimberlydb commented
StatusFileSize
new elysia_cron-changed-rule-to-pattern-1969000.patch53.52 KB

@NWOM:This is my first time creating a patch, so hopefully this works?

if d3claes's solution works I'd stick with that!

gotheric’s picture

gotheric commented

@d3claes: Are you sure that solution works with mysql or other sql servers?

d3claes’s picture

d3claes commented

No, you are right, it is t-sql specific.
I believe double quotes are ANSI SQL compatible and they also work for MsSQL (I tested it).

gotheric’s picture

gotheric commented

Hmmm, i don't think double quotes (") are ok, with double quotes sql treats the word as a string.

In mysql i know that backticks (`) could be used for identifiers.
This is a working query:

select `column` from table;

I don't know if this works in MSSQL, and if this is compliant with D7 database layer (it should be that db layer that encloses the keywords, at least when used with dynamic queries).

However, googling around, it seems that backticks only works with mysql, and that the only way is to avoid reserved keywords of all SQL specifications, ANSI or not:
https://drupal.org/node/1426084
https://drupal.org/node/141051

So i think the only solution is to change the "rule" column name, as done by @kimberlydb.
(But i don't like to replace it with "pattern", i prefer something else, but i'll think about it).

gotheric’s picture

gotheric commented
Issue summary: View changes

minor edit

dmitriy.trt’s picture

dmitriy.trt commented
Version: 7.x-2.1 » 7.x-2.x-dev
Issue summary: View changes
Status: Active » Needs review
StatusFileSize
new elysia_cron-mssql-support-1969000-9.patch2.44 KB

The problem can be solved with much less changes, we should just use db_select() query builders instead db_query(). There is no sense in fixing this for D6, since SQL Server driver is only available since D7.

kala4ek’s picture

kala4ek
Primary language Russian
Location 🇷🇺 Novosibirsk
commented
Assigned: gotheric » kala4ek

Thanks @Dmitriy.trt, committed to latest dev.

  • kala4ek committed 3f4ea1c on 7.x-2.x authored by Dmitriy.trt 
    Issue #1969000 by kimberlydb, Dmitriy.trt: MSSQL Invalid Object Name
kala4ek’s picture

kala4ek
Primary language Russian
Location 🇷🇺 Novosibirsk
commented
Assigned: kala4ek » Unassigned
Status: Needs review » Fixed

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.