[Freeswitch-users] mod_lcr with mssql backend

Sat Oct 8 03:52:18 MSD 2016

Thanks David.

Looking though the code, I see that the LIMIT 1 clause is used several
times in contexts similar to this:

   /* Checking for cid field, adding if needed */
          if (db_check("SELECT cid FROM lcr LIMIT 1") == SWITCH_TRUE) {
              switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_DEBUG, "cid
field defined.\n");

I gather they are limiting to 1 record because there might be people that
have huge tables and it wouldn't make sense to bring back all those
records...

But as can be seen below, limiting records doesn't seem to be one of those
standard SQL things...

Returning only the first N records in a SQL query differs quite a bit
between database platforms. Here's some samples:

*Microsoft SQL Server*

SELECT *TOP 10* column FROM table

*PostgreSQL and MySQL*

SELECT column FROM table*LIMIT 10*

*Oracle*

SELECT column FROM table*WHERE ROWNUM <= 10*

*Sybase*

*SET rowcount 10*
SELECT column FROM table

*Firebird*

SELECT *FIRST 10* column
FROM table

I'm wondering if they couldn't do something like "select distinct XXXX.."
 to mitigate this, but still be compatible with all ODBC backends.  SELECT
DISTINCT seems to be standard (at least I checked ORACLE, POSTGRES, mySQL
and MSSQL.

Guillermo

On Fri, Oct 7, 2016 at 8:29 PM, David Villasmil <
david.villasmil.work at gmail.com> wrote:

> It shouldn't be too hard to patch it for MSSQL, maybe that's the way to go.
> I have no idea whether anyone's implemented that, sorry.
> ᐧ
>
> On Sat, Oct 8, 2016 at 1:26 AM, Guillermo Ruiz Camauer <
> grcamauer at gmail.com> wrote:
>
>> Yes, I have ODBC working on my FS box, and I have updated the
>> lcr.conf.xml file with the DSN.
>> The problem is that when I load the module I get errors like:
>>
>> 2016-10-06 15:35:35.560192 [ERR] switch_odbc.c:522 ERR: [SELECT codec
>> FROM carrier_gateway LIMIT 1]
>> [STATE: 42000 CODE 102 ERROR: [Microsoft][ODBC Driver 13 for SQL
>> Server][SQL Server]Incorrect syntax near '1'.
>> ]
>> 2016-10-06 15:35:35.560212 [ERR] switch_core_sqldb.c:587 ODBC SQL ERR
>> [STATE: 42000 CODE 102 ERROR: [Microsoft][ODBC Driver 13 for SQL
>> Server][SQL Server]Incorrect syntax near '1'.
>> ]
>> SELECT codec FROM carrier_gateway LIMIT 1
>>
>> MSSQL doesn't accept "LIMIT 1".  The equivalent syntax would be "SELECT
>> top 1 codec FROM carrier_gateway".
>>
>> I was wondering if I will have to make a custum version of mod_lcr (which
>> I must then maintain), or if there is some setting that I am not seeing to
>> make it compatible with MS SQL.
>> If anybody has gone down this road,  would like some feedback.
>>
>> Thanks!
>>
>> On Fri, Oct 7, 2016 at 8:12 PM, David Villasmil <
>> david.villasmil.work at gmail.com> wrote:
>>
>>> You can probably just use odbc, have you tried that?
>>> ᐧ
>>>
>>> On Sat, Oct 8, 2016 at 1:10 AM, Guillermo Ruiz Camauer <
>>> grcamauer at gmail.com> wrote:
>>>
>>>> Does anyone have mod_lcr running against a MSSQL database through
>>>> ODBC?  What modifications did you have to make?
>>>>
>>>> --
>>>> Guillermo Ruiz Camauer
>>>>
>>>> ____________________________________________________________
>>>> _____________
>>>> Professional FreeSWITCH Consulting Services:
>>>> consulting at freeswitch.org
>>>> http://www.freeswitchsolutions.com
>>>>
>>>> Official FreeSWITCH Sites
>>>> http://www.freeswitch.org
>>>> http://confluence.freeswitch.org
>>>> http://www.cluecon.com
>>>>
>>>> FreeSWITCH-users mailing list
>>>> FreeSWITCH-users at lists.freeswitch.org
>>>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>>>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/free
>>>> switch-users
>>>> http://www.freeswitch.org
>>>>
>>>
>>>
>>> ____________________________________________________________
>>> _____________
>>> Professional FreeSWITCH Consulting Services:
>>> consulting at freeswitch.org
>>> http://www.freeswitchsolutions.com
>>>
>>> Official FreeSWITCH Sites
>>> http://www.freeswitch.org
>>> http://confluence.freeswitch.org
>>> http://www.cluecon.com
>>>
>>> FreeSWITCH-users mailing list
>>> FreeSWITCH-users at lists.freeswitch.org
>>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>>> http://www.freeswitch.org
>>>
>>
>>
>>
>> --
>> Guillermo Ruiz Camauer
>>
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting at freeswitch.org
>> http://www.freeswitchsolutions.com
>>
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://confluence.freeswitch.org
>> http://www.cluecon.com
>>
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at lists.freeswitch.org
>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>> http://www.freeswitch.org
>>
>
>
> _________________________________________________________________________
> Professional FreeSWITCH Consulting Services:
> consulting at freeswitch.org
> http://www.freeswitchsolutions.com
>
> Official FreeSWITCH Sites
> http://www.freeswitch.org
> http://confluence.freeswitch.org
> http://www.cluecon.com
>
> FreeSWITCH-users mailing list
> FreeSWITCH-users at lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> http://www.freeswitch.org
>

-- 
Guillermo Ruiz Camauer
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20161007/c0b32496/attachment-0001.html 