Marc Olivier Chouinard added a comment - 30/Jan/12 7:16 AM Try to run SELECT count(*) FROM agents WHERE name = 'AgentName'; Manually on your sql db. If the result is 0, then it mean it didn't found any entry... Now if it give this result and an full select does... Then your DB doesn't like the count(*).... But That look pretty standard SQL query.

Artem Vasilyev added a comment - 30/Jan/12 9:30 AM When I run this query manually it returns 1, as expected. I examined the code in cc_agent_update function /* Check to see if agent already exist */ sql = switch_mprintf("SELECT count(*) FROM agents WHERE name = '%q'", agent); cc_execute_sql2str(NULL, NULL, sql, res, sizeof(res)); switch_safe_free(sql); if (atoi(res) == 0) { result = CC_STATUS_AGENT_NOT_FOUND; goto done; } and made debug outputs of sql and res. The sql was correct, I even copied it and executed manualy. But res was empty string. Also tried to modify the query SELECT count(*) AS Count.. --maybe column shuld be named SELECT 1 .. None of them worked, always returned an empty string

Marc Olivier Chouinard added a comment - 30/Mar/12 10:26 PM I've hard there some problem with the ODBC connector depending the version you have, and the compile option used... check on the mailling list, I'm sure it came up before

Jeff Lenk added a comment - 31/Mar/12 3:08 PM Artem, have you set <param name="core-dbtype" value="MSSQL"/> from switch.conf.xml?

Artem Vasilyev added a comment - 02/Apr/12 8:09 AM Jeff, This only makes FreeSWITCH to put its system tables into MSSQL database. The error is still present.

Jeff Lenk added a comment - 02/Apr/12 9:14 AM what db engine are you using then? I thought you were using ms sql server.

Artem Vasilyev added a comment - 03/Apr/12 12:22 AM I used MS SQL for mod_callcenter only, other system databases were on SQLite. This is because I needed to integrate mod_callcenter with another system that uses MS SQL, and it would be easier if mod_callcenter was on MS SQL too. Since I found this bug, I switched back to default SQLite database.

Peter Olsson added a comment - 03/Apr/12 3:43 AM Artem, just for your information: FS needs the MSSQL-flag enabled to do things a little bit differently, specifically it's for using other methods for concat. However, if that didn't solve your problem, there might be other issues specific to the mod_callcenter module as well. Seems strange to me though that a simple count(*) would cause problems on MS SQL.

Artem Vasilyev added a comment - 03/Apr/12 5:39 AM If MSSQL flag is enabled, does it mean that all modules should use MS SQL as core-db without any additional configuration?

Ken Rice added a comment - 06/Apr/12 3:07 PM can you get an actual querylog of the failures and attach them to this bug?

Artem Vasilyev added a comment - 09/Apr/12 11:57 PM What is querylog and how to get it?

Artem Vasilyev added a comment - 10/Apr/12 12:53 AM This is function that is called by 'callcenter_config agent set' command. The SQL query is correct

Artem Vasilyev added a comment - 10/Apr/12 1:31 AM //The previous comment is for screenshot I uploaded. I thought it will be attached to that screenshot, not shown here. I was able to run this in the debugger. I set a breakpoint in cc_agent_update function (see debug-1.jpg). The SQL query looks good. Then I went inside cc_execute_sql2str, down to switch_odbc_handle_exec_string function (see debug-2.jpg). Here we have our query executed and we call SQLRowCount to check how many rows were returned. And suddenly it sets m = -1. I made a small modification to the code to show that SQLRowCount returns 0 (SQL_SUCCESS) and no ODBC errors occured. The next is 'if (m <= 0) goto done' condition that makes this function to return empty string. However, if we skip this condition, it will successfully fetch the query result and return "1". So, this is the place where things go wrong. I'm not a great specialist in C or ODBC, I don't know how to fix it.

Artem Vasilyev added a comment - 10/Apr/12 1:54 AM SQLRowCount documentation says -1 means that number of rows is not available, but that doesn't mean that there are no rows. I think there should be some way to handle this situation and get correct query result.

François added a comment - 10/Apr/12 8:19 AM Maybe some MSSQL config issue: You might have a configuration that disables the row count in MSSQL ("SET NOCOUNT ON"). Can you check that and see if it works when NOCOUNT is OFF? Google says it can be done like that:    1. Click Start, Programs, Microsoft SQL Server, Enterprise Manager.    2. Expand Microsoft SQL Servers.    3. Right-click on the required SQL server and select Properties.    4. Click the Connections tab.    5. Under Default connection options, deselect No Count, then click OK    6. Close the SQL Enterprise Manager.

Artem Vasilyev added a comment - 10/Apr/12 8:51 AM Just checked, No Count is disabled, and always was.

François added a comment - 10/Apr/12 9:05 AM Can you test this simple patch?

Artem Vasilyev added a comment - 10/Apr/12 11:50 PM The patch fixed my issue

François added a comment - 11/Apr/12 3:06 AM Just in case, does it also work when there are no agents?

Artem Vasilyev added a comment - 11/Apr/12 3:40 AM Yes, works. Actually, there's no difference, because COUNT(*) always returns something. I also tested it on custom query that returns no rows, it returned "", just as expected

Marc Olivier Chouinard added a comment - 29/Dec/12 8:03 PM Hi Tony, The patch attached is fairly simple... But I don't use odbc at all... And I didn't want to commit this before it go through you since it affect the core odbc code.

João Mesquita added a comment - 20/Jan/13 5:15 PM Moc, do you have new on this? :-)

Jeff Lenk added a comment - 24/Mar/13 10:44 PM Hi Tony, I can add an exception in the code for ms sql but it seems like the above patch may be fine on its own. Thoughts?