SQL query which doesn't work with MS SQL

[konus]

I have the follwing enviroment:

• Windows Server 2008 R2 with IIS 7.5
• PHP 5.3.13
• "Microsoft SQL Driver v2.0 for PHP v5.2 in IIS" (installed via Microsoft Web Plattform installer)
• My connection string is: $rcmail_configdb_dsnw? = 'sqlsrv://sa:password@server\\instance/database';
• hMailServer

After opening the addressbook, I get the following error in \logs\error

[31-May-2012 13:25:46 UTC] MDB2 Error: unknown error (-1): _doQuery: [Error message: Could not execute statement]
[Last executed query: SELECT TOP 40 * FROM contacts AS c WHERE c.del<>1 AND c.user_id=2 ORDER BY (c.surname + c.firstname + c.name + c.email) ASC]
[Native code: 402]
[Native message: SQLState: 42000
Error Code: 402
Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Die Datentypen varchar und text sind im add-Operator inkompatibel.

The translation of the german error is:
the data types text and varchar are incompatible in the boolean AND operator

A quick google search shows that this is a common problem with MSSQL-DB, but I am not capable of solve this by myself.

I also tested 0.7.2 with the same result.

[konus]

Link to forum thread ​http://www.roundcubeforum.net/index.php?topic=9750

[alec]

There's CONCAT() function in SQL Server 2012 which is free of this add-operator incompat. issue. It's not a solution for as. Another possibilities would be to use CAST/CONVERT or change column(s) type. I'm not sure which would be the best.

[alec]

Check this patch:

--- a/program/include/rcube_mdb2.php
+++ b/program/include/rcube_mdb2.php
@@ -648,6 +648,9 @@ class rcube_mdb2
             case 'mssql':
             case 'sqlsrv':
                 $delim = ' + ';
+                foreach ($args as $idx => $arg) {
+                    $args[$idx] = "CAST($arg AS text)";
+                }
                 break;
             default:
                 $delim = ' || ';

[konus]

I tried to apply the patch to 0.7.2 and got a slightly different error now:

Translation of the german error: Operand data type text is invalid for add operator

[04-Jun-2012 07:04:26 UTC] MDB2 Error: unknown error (-1): _doQuery: [Error message: Could not execute statement]
[Last executed query: SELECT TOP 40 * FROM contacts AS c WHERE c.del<>1 AND c.user_id=2 ORDER BY (CAST(c.surname AS text) + CAST(c.firstname AS text) + CAST(c.name AS text) + CAST(c.email AS text)) ASC]
[Native code: 8117]
[Native message: SQLState: 42000
Error Code: 8117
Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Der Operanddatentyp text ist für den add-Operator ungültig.
]

[04-Jun-2012 10:04:26 +0300]: DB Error: MDB2 Error: unknown error Query: _doQuery: [Error message: Could not execute statement] [Last executed query: SELECT TOP 40 * FROM contacts AS c WHERE c.del<>1 AND c.user_id=2 ORDER BY (CAST(c.surname AS text) + CAST(c.firstname AS text) + CAST(c.name AS text) + CAST(c.email AS text)) ASC] [Native code: 8117] [Native message: SQLState: 42000 Error Code: 8117 Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Der Operanddatentyp text ist für den add-Operator ungültig. ]  in C:\inetpub\wwwroot\roundcube\program\include\rcube_mdb2.php on line 722 (GET /?_task=addressbook&_action=list&_source=0&_remote=1&_unlock=loading1338793466183&_=1338793466184)

[alec]

Try to change "CAST($arg AS text)" with "CAST($arg AS varchar)" (or varchar(255)).

[konus]

At a first glance, it looks like that the problem is solved. Thank you very much!

I used:

--- a/program/include/rcube_mdb2.php
+++ b/program/include/rcube_mdb2.php
@@ -648,6 +648,9 @@ class rcube_mdb2
             case 'mssql':
             case 'sqlsrv':
                 $delim = ' + ';
+                foreach ($args as $idx => $arg) {
+                    $args[$idx] = "CAST($arg AS varchar)";
+                }
                 break;
             default:
                 $delim = ' || ';

[alec]

Fixed in 46b6c547.

[konus]

I am sorry, but there is at lease one more place, where data-types are not compatible. It happens during auto-complete when typing a mailaddress:

[04-Jun-2012 08:55:59 UTC] MDB2 Error: unknown error (-1): _doQuery: [Error message: Could not execute statement]
[Last executed query: SELECT TOP 15 * FROM contacts AS c WHERE c.del<>1 AND c.user_id=2 AND (([email] LIKE '%k%' OR [name] LIKE '%k%') AND [email] <> '') ORDER BY (CAST(c.surname AS varchar) + CAST(c.firstname AS varchar) + CAST(c.name AS varchar) + CAST(c.email AS varchar)) ASC]
[Native code: 402]
[Native message: SQLState: 42000
Error Code: 402
Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Die Datentypen text und varchar sind im not equal to-Operator inkompatibel.
]

[04-Jun-2012 11:55:59 +0300]: DB Error: MDB2 Error: unknown error Query: _doQuery: [Error message: Could not execute statement] [Last executed query: SELECT TOP 15 * FROM contacts AS c WHERE c.del<>1 AND c.user_id=2 AND (([email] LIKE '%k%' OR [name] LIKE '%k%') AND [email] <> '') ORDER BY (CAST(c.surname AS varchar) + CAST(c.firstname AS varchar) + CAST(c.name AS varchar) + CAST(c.email AS varchar)) ASC] [Native code: 402] [Native message: SQLState: 42000 Error Code: 402 Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Die Datentypen text und varchar sind im not e in C:\inetpub\wwwroot\roundcube\program\include\rcube_mdb2.php on line 722 (POST /?_task=mail&_action=autocomplete?_task=&_action=)

[alec]

I vote for SQL Server support removal. This sucks.

[alec]

I suppose changing 'email' column type from text to varchar(8000) would solve both these issues.

[alec]

Could you confirm that

ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (8000) COLLATE Latin1_General_CI_AI NOT NULL

solves the issue?

[konus]

Sorry beeing a noob. After trying that I got this german error in Microsoft SQl Server Management Studio:

Meldung 5074, Ebene 16, Status 1, Zeile 1
Das Objekt-Objekt 'DF_contacts_email' ist vom Spalte-Objekt 'email' abhängig.
Meldung 4922, Ebene 16, Status 9, Zeile 1
Fehler bei ALTER TABLE ALTER COLUMN email, da mindestens ein Objekt auf diese Spalte zugreift.

free translation:

Msg 5074, Level 16, State 1, Line 1
The object 'DF_contacts_email' is dependent on column 'email'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN email failed because one or more objects access this column.

What can I do about it?

[alec]

Try these commands:

ALTER TABLE [dbo].[contacts] DROP CONSTRAINT [DF_contacts_email]
ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (8000) COLLATE Latin1_General_CI_AI NOT NULL
ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email]

[konus]

Thank you, this worked for me!
I also can't reproduce the error during auto-complete anymore, so I think the case is finally solved.

[alec]

Fixed in 6237c947.