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@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@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@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@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@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@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@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@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@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@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@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@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