Jump to content

- Functions

- # CheckCallerInDatabase

  Followers 0

  VBScript

  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
          else

              ' 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
              else

                  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"
                  else
                      ' if no dataset was found, the caller is not known
                      PBXScript.OutputTrace "No customer found"
                  end if

                  rs.Close
              end if
              Set rs = Nothing

              db.Close
          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 gives examples for any kind of database.
  You should use OLE DB Data Providers, and if possible Trusted Connection instead of Standard Security (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.

  For example, if you want to connect to an MS SQL Server, you select SQL Server from above linked Connection Strings page. Afterwards select Microsoft OLE DB Driver for SQL Server below OLE DB providers. That should bring you here.

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

  By Tom Wellige

  - June 8, 2022
  Share
  Share
  Followers 0
  Previous Page AnyRedirectionConfigured
  Next Page CheckCallerInTextFile

## 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.