Our Drupal 7.23 installation is using SQL Server 2008 R2. When drupalchat module is enabled (Normal AJAX mode) and user creates new message? we can see that the message is logged correctly in database, but it does not appear in UI. We have discovered that http://localhost/drupalchat/poll?drupalchat_last_timestamp=1384165580 AJAX call is returning 500 Error:

PDOException: SQLSTATE[07002]: [Microsoft][SQL Server Native Client 11.0]COUNT field incorrect or syntax error: SELECT m.message_id, m.uid1, m.uid2, m.message, m.timestamp FROM {drupalchat_msg} m WHERE (m.uid2 IN (:uid2,'c-0') OR m.uid1 = :uid2) AND m.timestamp > :timestamp ORDER BY m.timestamp ASC; Array( [:uid2] => 1 [:timestamp] => 1384165580) in drupalchat_poll() (line 821 of C:\inetpub\universal\profiles\workspace_universal\modules\contrib\drupalchat\drupalchat.module).

Looks like SQL Server driver does not like the db_query syntax.....

Rewriting the query:
$messages = db_query('SELECT m.message_id, m.uid1, m.uid2, m.message, m.timestamp FROM {drupalchat_msg} m WHERE (m.uid2 IN (:uid2,\'c-0\') OR m.uid1 = :uid2) AND m.timestamp > :timestamp ORDER BY m.timestamp ASC', array(':uid2' => $user->uid, ':timestamp' => $last_timestamp));

in the following:
$messages = db_select('drupalchat_msg', 't1')
->fields('t1')
->condition('t1.uid2', array('c-0', $user->uid), 'IN')
->condition('t1.timestamp', $last_timestamp, '>')
->execute()->fetchAll();

is fixing the problem, but there are other db_query in the module - we have not tried all options.

Comments

darklrd’s picture

darklrd CreditAttribution: darklrd commented

This is really strange because it seems to work fine in MySQL.

After introducing the above mentioned change, does everything work properly?

Thanks

meloks’s picture

meloks CreditAttribution: meloks commented

Yes, it works fine for us with the changes above.

I have not tried MySQL, as we are not using this DB platform in our product.

I am assuming that to make it DB vendor agnostic, all queries should be re-written in a similar way...