Jump to content
  • CheckCallerInDatabase




    This function checks if the current caller (i.e. his phone number) can be found in a configured database. It can be used to create black or white lists for incoming calls on a user.

    Please see the Introduction chapter for some usage instructions.


    ' CursorTypeEnum Values
    Const adOpenForwardOnly = 0
    Const adOpenKeyset = 1
    Const adOpenDynamic = 2
    Const adOpenStatic = 3
    ' LockTypeEnum Values
    Const adLockReadOnly = 1
    Const adLockPessimistic = 2
    Const adLockOptimistic = 3
    Const adLockBatchOptimistic = 4
    ' CommandTypeEnum Values
    Const adCmdUnknown = &H0008
    Const adCmdText = &H0001
    Const adCmdTable = &H0002
    Const adCmdStoredProc = &H0004
    ' CheckCallerInDatabase
    ' Checks if the current callers phone number can be found in database.
    ' Parameter:
    ' Return:
    '   integer         0 - caller unknown
    '                   1 - caller known
    '                   2 - error accessing the database
    Function CheckCallerInDatabase
        PBXScript.OutputTrace "-----------> CheckCallerInDatabase"
        On Error Resume Next
        Dim nReturn
        nReturn = 0
        Dim sDsn
        sDsn = _
            "Provider=sqloledb;" & _
            "Data Source=***ServerName***;" & _
            "Initial Catalog=***DatebaseName***;" & _
            "Integrated Security=SSPI"
        ' open connection to database
        Dim db
        Set db = CreateObject("ADODB.Connection")
        db.Open sDsn
        if Err <> 0 then
            PBXScript.OutputTrace "Error opening database!"
            PBXScript.OutputTrace Err & ": " & Err.Description
            nReturn = 2
            ' open recordset
            Dim sSQL
            Dim rs
            sSQL = "SELECT * FROM Customers where Phone = '" & PBXCall.CallingPartyNumber & "'"
            PBXScript.OutputTrace "sSQL = " & sSQL
            Set rs = CreateObject("ADODB.Recordset")
            rs.Open sSQL, db, adOpenForwardOnly, adLockReadOnly, adCmdText
            if Err <> 0 then
                PBXScript.OutputTrace "Error accessing database!"
                PBXScript.OutputTrace Err & ": " & Err.Description
                nReturn = 2
                if not rs.EOF then
                    ' if at least one dataset was found, the caller is known
                    nReturn = 1
                    PBXScript.OutputTrace "At leat one customer found"
                    ' if no dataset was found, the caller is not known
                    PBXScript.OutputTrace "No customer found"
                end if
            end if
            Set rs = Nothing
        end if
        Set db = Nothing    
        CheckCallerInDatabase = nReturn
        PBXScript.OutputTrace "nReturn = " & nReturn
        PBXScript.OutputTrace "<----------- CheckCallerInDatabase"
    End Function


    This function makes use of the Server Script API functions PBXCall.CallingPartyNumber to get the callers number and PBXScript.OutputTrace to write trace information into the SwyxServer trace file.


    You need to make modifications to this example:


    Connection String (sDSN)

    The connection string defines the database and the access privileges you want to use to login into the database.
    The Connection Strings page of Carl Prothman gives examples for any kind of database.
    You should use OLE DB Data Providers, and if possible Windows Security (Trusted Connection) instead of username/password authentication.
    In this case the Windows user the Swyx Server service is running under (by default this is the "SwyxServiceAccount") needs at least read access to your database.



    SQL Statement (sSQL)


    Of course it depends on the structure of your database, how the SQL statement needs to look like.


    In this simple example it is assumed, that the phone number format which is stored in the database is the same as the one PBXCall.CallingPartyNumber is providing, i.e. the format you see in the display of your SwyxIt! client or IP phone. Things can get much more complicated in real world databases...



    Tom Wellige


    User Feedback

    Recommended Comments

    There are no comments to display.

    Create an account or sign in to comment

    You need to be a member in order to leave a comment

    Create an account

    Sign up for a new account in our community. It's easy!

    Register a new account

    Sign in

    Already have an account? Sign in here.

    Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use and have taken note of our Privacy Policy.
We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.