[Home]

Summary:ASTERISK-10864: Problem with Like query on MS SQL
Reporter:Antonis Psaras (apsaras)Labels:
Date Opened:2007-11-22 16:37:04.000-0600Date Closed:2008-01-21 12:19:10.000-0600
Priority:MinorRegression?No
Status:Closed/CompleteComponents:PBX/pbx_realtime
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:( 0) 20071124__bug11353.diff.txt
Description:The problem is the MSSQL server does not support back slash "\" as escape character on select queries hence when you execute

SELECT * FROM extensions WHERE exten LIKE '\_%' AND context = 'default' AND priority = '1' ORDER BY exten

is not working. The correct and most commonly used syntax is

SELECT * FROM extensions WHERE exten LIKE '[_]%' AND context = 'default' AND priority = '1' ORDER BY exten

As far as I concern this is compatible with mySQL and PostgreSQL.

The solution I propose is to change the line

cfg = ast_load_realtime_multientry(table, "exten LIKE", "\\_%", "context", context, "priority", pri, NULL);

at pbx_realtime.c to

cfg = ast_load_realtime_multientry(table, "exten LIKE", "[_]%", "context", context, "priority", pri, NULL);
Comments:By: Tilghman Lesher (tilghman) 2007-11-22 17:35:32.000-0600

The fact that you quoted this syntax tells me that you aren't using 1.4.14.  Please upgrade to that version and try again.  This problem has already been fixed, in a different way.
By: Antonis Psaras (apsaras) 2007-11-23 02:56:09.000-0600

I am using 1.4.14. I downloaded yesterday, 30 min before I wrote the ticket. I tested the system, with exact match and it worked, then I changed that to reg match and it didn't. The query I am sending was grabbed by SQL Profiler.

Please recheck.
By: Digium Subversion (svnbot) 2007-11-23 11:02:45.000-0600

Repository: asterisk
Revision: 89534

U   branches/1.4/res/res_config_odbc.c

------------------------------------------------------------------------
r89534 | tilghman | 2007-11-23 11:02:44 -0600 (Fri, 23 Nov 2007) | 5 lines

Use ESCAPE clause for the first parameter, not just 2nd-Nth parameters.
Reported by: apsaras
Patch by: tilghman
(Closes issue ASTERISK-10864)

------------------------------------------------------------------------
By: Digium Subversion (svnbot) 2007-11-23 11:06:42.000-0600

Repository: asterisk
Revision: 89535

_U  trunk/
U   trunk/res/res_config_odbc.c

------------------------------------------------------------------------
r89535 | tilghman | 2007-11-23 11:06:41 -0600 (Fri, 23 Nov 2007) | 13 lines

Merged revisions 89534 via svnmerge from
https://origsvn.digium.com/svn/asterisk/branches/1.4

........
r89534 | tilghman | 2007-11-23 11:05:10 -0600 (Fri, 23 Nov 2007) | 5 lines

Use ESCAPE clause for the first parameter, not just 2nd-Nth parameters.
Reported by: apsaras
Patch by: tilghman
(Closes issue ASTERISK-10864)

........

------------------------------------------------------------------------
By: Antonis Psaras (apsaras) 2008-01-18 16:03:09.000-0600

I am sorry to report that the problem is not resolved. We installed 1.4.17 before a few days and we had to apply the patch we suggested in order to have the Like at MS SQL to work.
By: Tilghman Lesher (tilghman) 2008-01-18 16:18:59.000-0600

Did you set

backslash_is_escape => no

in res_odbc.conf, as is now documented that you need to do in the sample config?
By: Antonis Psaras (apsaras) 2008-01-18 16:43:42.000-0600

No I didn't. I will retest.

Thank you
By: Tilghman Lesher (tilghman) 2008-01-21 12:19:09.000-0600

Reopen if you're still having problems.