Dismiss Notice
We would like to remind you that we’re updating our login process for all 3CX forums whereby you will be able to login with the same credentials you use for the Partner or Customer Portal. Click here to read more.

ODBC Search

Discussion in '3CX Phone System - General' started by mv.ape, Feb 16, 2015.

Thread Status:
Not open for further replies.
  1. mv.ape

    Joined:
    Feb 16, 2015
    Messages:
    24
    Likes Received:
    3
    Hello,

    I'm testing 3CX system, almost every things work well expect ODBC search.
    I've:
    -Created an DSN (tested, connexion successful)
    -Configurated in 3CX
    -Checked ODBC search prefered
    -Download 3CX test tool, query it's good and return result.

    But it's don't work. I tried to trace ODBC via windows and nothing happen on incoming call. Is there somthing else to do?

    Thanks
    Version: 3CX 12 Pro edition
     
  2. pj3cx

    pj3cx Active Member

    Joined:
    Aug 1, 2013
    Messages:
    646
    Likes Received:
    1
    Hi there,
    Kindly open a ticket on our support portal for further investigations on this matter.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. mv.ape

    Joined:
    Feb 16, 2015
    Messages:
    24
    Likes Received:
    3
    Hello,

    I've no maintenance yet. Prior to buy the product I want to test all fonctionnality.
    So I can't open a ticket.
     
  4. pj3cx

    pj3cx Active Member

    Joined:
    Aug 1, 2013
    Messages:
    646
    Likes Received:
    1
    Ok I though you had one because you said you were activated on pro license.
    Then few things to check :
    - in Settings / General / Global Options / "Resolved caller ID to phonebook entry", should be checked, with matching on "Minimum length" : X digits.
    - ODBC DSN should be declared in system DSN's (not users) and in the 64 bit version if OS is 64 bit
    - in Settings / Advanced / Company Directory Sync / ODBC Search / DSN name should match the one from control panel
    - query should have syntax similar to : SELECT field0,field1,field2 WHERE field3 LIKE '%%number%'
    - fields numbers should be mapped accordingly (at least 3 fields countring from 0, first name, last name, and mobile)
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. mv.ape

    Joined:
    Feb 16, 2015
    Messages:
    24
    Likes Received:
    3
    For the moment I'm using the demo prodcut.
    I've checked every things, but when I trace in obdc tool, I don't see anything (no connection, the file is empty, but with 3cx ODBC tool I see lines in this file).

    -The DSN name is correct (is 64bits system DSN, MySQL 5.3)
    -Resolved caller ID to phonebook entry is checked, I also restart services
    -Query is correct and return valid result
    -Fields are configured from 0 to 2 (0= Company, 1=Name, 2=Tel)
     
  6. pj3cx

    pj3cx Active Member

    Joined:
    Aug 1, 2013
    Messages:
    646
    Likes Received:
    1
    Hi there,

    Are you using LDAP phonebook aswell ?
    If not, you must ensure that the option Settings / Advanced / Company Directory Sync / ODBC Search / "Check this option to prefer ODBC results over LDAP or if LDAP is not configured" is checked.

    Also, please confirm that you are running latest v12.5 release (build 12.5.39117.982 - check on login page) not prior beta release.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  7. mv.ape

    Joined:
    Feb 16, 2015
    Messages:
    24
    Likes Received:
    3
    Hello,

    I'm not using LDAP or other phonebook, only ODBC search.
    I've checked version v12.5.39117.982

    I've also unchecked Check this option to prefer ODBC results over LDAP or if LDAP is not configured, but I do not seen anything in SQL trace
     
  8. pj3cx

    pj3cx Active Member

    Joined:
    Aug 1, 2013
    Messages:
    646
    Likes Received:
    1
    Calls must be picked for matching to be triggered, so you must in fact :
    - make a first call, and pick it without having name on screen, at the same time it will trigger matching through odbc and insert a new row in phonebook if any match
    - make a second call, this time you should see name displayed, then pick

    Additionally, you also have the log file C:\ProgramData\3CX\Data\Logs\3CXCompanyDirectory.trace.log which will give more infos on the phonebook matching. You'll need to be in Settings / Advanced / Logging level : verbose, and have restarted services, prior to see these traces.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  9. mv.ape

    Joined:
    Feb 16, 2015
    Messages:
    24
    Likes Received:
    3
    I made multiple calls from the same number without results.
    I've already activated verbose logging and analyzed logs, but i don't find any line about directory or odbc.

    In the file C:\ProgramData\3CX\Data\Logs\3CXCompanyDirectory.trace.log, I only can see these lines:
    17/02/2015 11:38:00 CalendarSync: Today is a holiday, do nothing
     
  10. mv.ape

    Joined:
    Feb 16, 2015
    Messages:
    24
    Likes Received:
    3
    In log on server I can found:

    18/02/2015 11:23:42:578 | Looking for phone number xxxxxxxx9 in tenant's 'default' phonebook
    18/02/2015 11:23:42:578 | Tenant's phonebook has failed to load!
    18/02/2015 11:23:42:578 | CallerNameAddr: "xxxxxxxx9"<sip:xxxxxxxx9;nf=e>
     
  11. mv.ape

    Joined:
    Feb 16, 2015
    Messages:
    24
    Likes Received:
    3
    I've tried to enter a contact in COmpany phone book (General Settings -> company phonebook) and the name displayed on phone
     
  12. mmj

    mmj

    Joined:
    May 11, 2015
    Messages:
    2
    Likes Received:
    0
    I experience the exact same problem.

    Is there any solution?

    - Lookup works using the CDM_ODBC_Test.exe example application
    - According to DSN Logs 3CX does not even try to query
    - 3CXCompanyDirectory.trace.log = only holiday message
    - I tried both, "Check this option to prefer ODBC results over LDAP or if LDAP is not configured" checked and unchecked -> BTW: In this forum pj3cx says it needs to be unchecked while 3CX says the opposite in http://www.3cx.com/docs/odbc-search-phonebook-directory/. - [PJ Edit : yes apologizes, I edited previous post the option must be checked]
    - I made calls and picked them up
    - I'm on PRO Version
    - If I enter my number manually in the company phonebook it is displayed.
    - DSN Name is correct, 64bit used...

    Somehow 3CX seems to fail to initiate the query.
    Very frustrating :(


    [​IMG]
    [​IMG]
     
  13. mv.ape

    Joined:
    Feb 16, 2015
    Messages:
    24
    Likes Received:
    3
    Hello,

    There is a "bug", the work around:
    Configure a fake LDAP
    Server: rubbish
    Port: 389
    TTL: 385

    And on ODBC Search, uncheck "prefer ODBC search".

    That worked for me
     
  14. mmj

    mmj

    Joined:
    May 11, 2015
    Messages:
    2
    Likes Received:
    0
    Thank you so much, that did the trick. It works now!

    The only thing I needed to change afterwards: +1 to each column because I did not know the first is counted as 0.
     
  15. art_gruby

    Joined:
    May 20, 2015
    Messages:
    1
    Likes Received:
    0
    I confirm malfunction and work around with fake LDAP and turning off ODBC preference :lol:

    Additionally, when incoming call goes through IVRs and Virtual Extensions (out Inbound Rules)
    PBX makes two separate query to ODBC (for each query got response).
    After the first ODBC query-response in Company Phonebook was entered entry for the incoming call identification.
    Why the second query was executed?
    Of course, as a result of the second query-response in Company Phonebook the same second entry was created.
    And at the result there is no name identification for the call.
    Name is presented correctly but for next calls.

    Cooperation functionality of Company Phonebook with ODBC support is full of bugs :S
     
  16. pj3cx

    pj3cx Active Member

    Joined:
    Aug 1, 2013
    Messages:
    646
    Likes Received:
    1
    Hi there,
    We already have a fix for those 2 bugs mentionned in this topic, if someone wants them at current they are provided through support tickets, but those will come with future update aswell.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  17. kerem.askin

    Joined:
    Jun 2, 2015
    Messages:
    2
    Likes Received:
    0
    I experience the exact same problem too. I read all your comment and try your solutions but the problem is not solved. I have a log in 3CXCompanyDirectory.trace file. This log:

    2.6.2015 16:50:46 Trying to match number against LDAP
    2.6.2015 16:50:51 No result found in LDAP for number: 05545411542, so search in SQL
    2.6.2015 16:50:51 Looking for 2 according to Minimal Matching Strategy in 3CX
    2.6.2015 16:50:51 Error while trying to establish database connection: ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''.
    ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''.


    Odbc testing tool connect sql server and select required field. But log says : "Login failed for user" :roll:

    thanks.
     
  18. ormondcollege

    Joined:
    Oct 29, 2014
    Messages:
    3
    Likes Received:
    0
    I found a way for the server to store the credentials for MS SQL server, go to Control Panel, Credential Manager. Click the Windows Credentials icon, then add a Windows Credential. Specify the server name as {fqdn}:1433 or {ip address}:1433 then enter the correct login DOMAIN\USER and password to access the SQL Server.

    The test tool works fine, using just the DSN name.....but the actual lookup process fails with a different SQL error...

    30/06/2015 3:15:36 PM Unknown incoming number: 0423406523
    30/06/2015 3:15:36 PM Looking for number: 0423406523 in SQL
    30/06/2015 3:15:36 PM Looking for 23406523 according to Minimal Matching Strategy in 3CX
    30/06/2015 3:15:36 PM Error while trying to establish database connection: ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
    ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
    30/06/2015 3:15:41 PM No result found in SQL for number: 0423406523, so search in LDAP

    Perhaps the service needs to run under the logged in user, rather than LocalSystem ??
     
  19. pj3cx

    pj3cx Active Member

    Joined:
    Aug 1, 2013
    Messages:
    646
    Likes Received:
    1
    Hi there,
    In DSN field you can specify authentication for MSSQL via syntax below :
    DSN_NAME;uid=USER;pwd=PASS;
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    daphi likes this.
Thread Status:
Not open for further replies.