Skip to end of metadata
Go to start of metadata

VX communicates with VXgate using the optimized and high performance BDAP Protocol. Using this protocol allows complicated network queries to be performed using reduced network bandwidth, and permits the processing to occur on VXgate, with VXgate returning only an answer to the query instead of raw data. This section permits configuration of the SQL instructions that VXgate must execute to get the information it needs in order to answer the query. This example for Validate Account ID represent a fictitious database layout, and demonstrate that not all fields need be used in all queries. Your own requirements may be different.

For example, in FictionalUserTable, we have columns AccountID (varchar), Disabled (bool), ExpiryDate (datetime), MaximumBalance (money), CurrentBalance (money), and PIN (varchar). The query that we enter can use all these fields directly except for ExpiryDate, which must be converted into a count of days remaining (we use the SQL DateDiff'unction for this). This fictional example database is not interested in the Trunk Group, Node ID or Calling number, and does not require the PIN to perform the lookup. (However, VXgate will still compare the PIN from the script against the value we return.) The only column we match on is AccountID, and for that we use a string comparison against the Account passed by the script, represented as ~ACCOUNT inside quotes.

SELECT Disabled, DateDiff(dd,getdate(),ExpiryDate), MaximumBalance, CurrentBalance, PIN FROM FictionalUserTable
WHERE AccountID='~ACCOUNT'

We can then set the query result column identifiers very simply:

Disabled: 1 Expiry: 2 Max.Bal: 3 Cur.Bal: 4 PIN: 5


Other queries work the same way; some performing actions, and some merely returning data. Most are much simpler, for instance requiring only a single return column. In most cases you should ensure that your queries will return unambiguous results.

Field

Description

Database Test Query

This query is executed each time a thread connects to the database, and it's return is string-compared against 'Test Query Response'. This is to prevent the system from attempting to operate on the wrong database due to misconfiguration.

  • No return fields
  • No available fields
    Although the return field is not configureable, one column must be returned.

Test Query Response

  • No return fields
  • No available fields
    This is not a query, it is merely the expected result - how the user is notified about success or failure - to the Database Test Query.
    Verify is not available.

Validate Account ID

Validate Account ID is used by a script to quickly determine if basic criteria for account use is met. Given basic account information (including a pin or password if required), VXgate will use the information returned by this query to determine if an account is disabled, expired, or out of credit, and whether the provided PIN is correct. This query must return a single row for the specified account, and in that row provide fields as described below. If the account ID is not found, the query should return no rows. VXgate will return the decision to the script, not any of the data.

Return Fields:

  • Disabled - Field should return 0 if account is not disabled, 1 if it is (int suggested)
  • Expiry - Field should return the number of days remaining until account expiry, or NULL if none. (int suggested).
  • Max.Bal - Field should return the account's maximum balance, or NULL if none (money suggested)
  • Cur.Bal - Field should return the account's current balance (money suggested)
  • PIN - Field should return the account's PIN, or NULL if none (varchar suggested)
    Available Fields:
  • ~ACCOUNT - the requested account ID (char - string)
  • ~CALLINGNUM - The number of the calling party (char)
  • ~TRUNK - the trunk group that the call came in on (numeric)
  • ~NODEID - the node ID that received the call
  • (char - hexadecimal string)
  • ~PIN - user PIN code

Translate Calling Number

Translate Calling Number is used by a script to match a calling number (caller ID) to an account ID. Given basic information about the origin of the call, the query should look up a matching account in the database. This query must return a rowset to VXgate only if the match is unambiguous. The rowset must contain a field that includes the Account ID - this field will be returned to the script.

Return Fields:

  • Account ID - Field should return matched AccountID (varchar suggested)

    Available Fields:
  • ~CALLINGNUM - the number of the calling party (char)
  • ~CALLINGSUB - the subaddress of the calling party (char - optional)
  • ~NODEID - the node ID that received the call
  • (char - hexadecimal string)
  • ~TRUNK - the trunk group that the call came in on (numeric)

Get Account Balance

Get Account Balance is used by a script to retrieve the customer's current balance from the database. Given basic information about the caller, the query should look up a matching account in the database. This query must return a rowset to VXgate only if the match is unambiguous. The rowset must contain a field that includes the current balance - this field will be returned to the script.

Return Fields:

  • Balance - Field should return the account's current balance (money suggested)
    Available Fields:
  • ~ACCOUNT - the account ID being looked up (char)
  • ~CALLINGNUM - the number of the calling party (char)
  • ~NODEID - the node ID that received the call (char - hexadecimal string)
  • ~PIN - The user PIN code.
  • ~TRUNK - the trunk group that the call came in on (numeric)

Add Account Balance

Add Account Balance is used by a script to add a value to a customer's current balance. (The balance may be reduced by adding a negative value). Given basic information about the caller, the query should look up a matching account in the database, and add the provided amount to it. This operation must be performed only if the match is unambiguous. No data is returned to the script.
VX passes money values to queries as fixed-point integers with 4 decimal places (that is, divide by 10000 to get a floating point value).

Return Fields:

  • None
    Available Fields:
  • ~ACCOUNT - the account ID being updated (char)
  • ~AMOUNT - the amount to add to the balance
    (integer (money value * 10000))
  • ~NODEID - the node ID that received the call
    (char - hexadecimal string)
  • ~TRUNK - the trunk group that the call came in on (numeric)

Get Language

Get Language is used by a script to retrieve a user-defined numeric language setting from the database. Given basic information about the caller, the query should look up a matching account in the database. This query should return a rowset to VXgate only if the match is unambiguous. The rowset must contain a field that includes the language ID - this field will be returned to the script.

Return Fields:

  • Language - Field should return the account's language field
    (int suggested)

    Available Fields:
  • ~ACCOUNT - the account ID being looked up (char)
  • ~NODEID - the node ID that received the call (char - hexadecimal string)
  • ~TRUNK - the trunk group that the call came in on (numeric)

Set Language

Set Language is used by a script to set a user-defined numeric 'language' setting in the database. Given basic information about the caller, the query should look up a matching account in the database, and change the language field to the provided value. This query should perform this operation only if the match is unambiguous. No data is returned to the script.

Return Fields: None

Available Fields:

  • ~ACCOUNT - the account ID being looked up (char)\
  • ~LANGUAGE - the language value to set (int)\
  • ~NODEID - the node ID that received the call
    (char - hexadecimal string)\
  • ~TRUNK - the trunk group that the call came in on (numeric)

Get Flags

Get Flags is used by a script to retrieve a user-defined Flags value from the database, which is intended to be a value representing a set of user-defined bitflags. Given basic information about the caller, the query should look up a matching account in the database. This query should return a rowset to VXgate only if the match is unambiguous. The rowset must contain a field that includes the flags field - this field will be returned to the script.

Return Fields:

  • Flags - Field should return the account's flags field (int suggested)
    Available Fields:
  • ~ACCOUNT - the account ID being looked up (char)\
  • ~NODEID - the node ID that received the call (char - hexadecimal string)\
  • ~TRUNK - the trunk group that the call came in on (numeric)

Update Flags

Update Flags is used by a script to set a user-defined flags setting in the database, intended to be a value representing a set of user-defined bitflags. Given basic information about the caller, the query should look up a matching account in the database, and update the Flags field to the provided value, changing only those bits indicated by setting the same bits to '1' in the provided Mask. This query should perform this operation only if the match is unambiguous. No data is returned to the script.

Return Fields: None

Available Fields:

  • ~ACCOUNT - the account ID being looked up (char)
  • ~FLAGS - The new settings for the flags in the Mask
  • ~MASK - A bitmask for the flags that are being updated
  • ~NODEID - the node ID that received the call (char - hexadecimal string)
  • ~TRUNK - the trunk group that the call came in on (numeric)

Get Rate

Get Rate is used by a script to determine the cost per minute of placing a call to a particular destination. Sufficient information is available to make the decision based on the current time of day and day of week at the VX. Given information about the caller and current time, the query should look up a matching account in the database. This query should return a rowset to VXgate where the first row is the most relevant. This rowset should include a field that contains the rate as a cost per minute (up to 4 decimal places are supported) - this field will be returned to the script.

Return Fields:

  • Rate - Field should return the destination's cost / minute (money suggested)
    Available Fields:
  • ~ACCOUNTID - the provided Account Identifier
  • ~CALLEDNUM - the destination number (char)
  • ~CALLINGNUM - The calling party's number (char)
  • ~DAY - the current day 1-31 (int)
  • ~HOUR - the current hour of the day 1-24 (int)
  • ~MONTH - the current month 1-12 (int)
  • ~NODEID - the node ID that received the call (char - hexadecimal string)
  • ~PIN - the user PIN code.
  • ~TRUNK - the trunk group that the call came in on (numeric)
  • ~WEEKDAY - the current day of week 1-7 (int)

Get First Billing Interval

Get First Billing Interval is used by a script to determine the first billing period when placing a call to a particular destination. Many billing plans bill a fixed charge for the first period of time, then bill by a different time measurement thereafter. For example, a plan may bill in 30 second increments, with a 2 minute minimum. In this case, the First Billing Interval is 2 minutes, or 120 seconds. Sufficient information is available to make the decision based on the current time of day and day of week at the VX. Given information about the caller and current time, the query should look up a matching account in the database. This query should return a rowset to VXgate where the first row is the most relevant. This rowset should include a field that contains the first billing interval in seconds - this field will be returned to the script.

Return Fields:

  • Rate - Field should return the destination's first billing interval (int suggested)
    Available Fields:
  • ~CALLEDNUM - the destination number (char)
  • ~CALLEDSUB - the destination subaddress (char - optional)
  • ~DAY - the current day 1-31 (int)
  • ~HOUR - the current hour of the day 1-24 (int)\
  • ~MONTH - the current month 1-12 (int)
  • ~NODEID - the node ID that received the call
    (char - hexadecimal string)
  • ~TRUNK - the trunk group that the call came in on (numeric)
  • ~WEEKDAY - the current day of week 1-7 (int)

Get Next Billing Interval

Get Next Billing Interval is used by a script to determine the next billing period when placing a call to a particular destination. Many billing plans bill a fixed charge for the first period of time, then bill by a different time measurement thereafter. For example, a plan may bill in 30 second increments, with a 2 minute minimum. In this case, the Next Billing Interval is 30 seconds. Sufficient information is available to make the decision based on the current time of day and day of week at the VX. Given information about the caller and current time, the query should look up a matching account in the database. This query should return a rowset to VXgate where the first row is the most relevant. This rowset should include a field that contains the next billing interval in seconds - this field will be returned to the script.

Return Fields:

  • Rate - Field should return the destination's next (subsequent) billing interval (int suggested)
    Available Fields:
  • ~CALLEDNUM - the destination number (char)
  • ~CALLEDSUB--the destination subaddress (char - optional)
  • ~DAY - the current day 1-31 (int)
  • ~HOUR--the current hour of the day 1-24 (int)* ~MONTH - the current month 1-12 (int)
  • ~NODEID - the node ID that received the call (char - hexadecimal string)
  • ~TRUNK - the trunk group that the call came in on (numeric)
  • ~WEEKDAY - the current day of week 1-7 (int) | |

Get Account Limit

Get Account Limit is used by a script to determine the maximum credit limit for an account. Given information about the caller, the query should look up a matching account in the database. This query should return a rowset to VXgate only when the match is unambiguous. This rowset should include a field that contains the maximum balance for the account (up to 4 decimal places are supported) - this field will be returned to the script.

Return Fields:

  • Max. Bal. - Field should return the account ID's maximum balance, or NULL if none (money suggested)
    Available Fields:
  • ~ACCOUNT - the account ID being looked up (char)\
  • ~NODEID - the node ID that received the call(char - hexadecimal string)
  • ~TRUNK - the trunk group that the call came in on (numeric) | |

Get Account PIN

Get Account PIN is used by a script to retrieve the configured PIN for an account. Note that with this function, the actual PIN is transported across the network and is made available to the script. Given information about the caller, the query should look up a matching account in the database. This query should return a rowset to VXgate only when the match is unambiguous. This rowset should include a field that contains the PIN for the account - this field will be returned to the script.

Return Fields:

  • PIN - Field should return the account ID's PIN, or NULL if none (varchar suggested)
    Available Fields:
  • ~ACCOUNT - the account ID being looked up (char)
  • ~NODEID - the node id that received the call (char - hexadecimal string)
  • ~TRUNK - the trunk group that the call came in on (numeric) | |

Set Account PIN

Set Account PIN is used by a script to change the configured PIN for an account. Given information about the caller, the query should look up a matching account in the database, and update the PIN field with the passed string. This query should perform this action only when the match is unambiguous. No data is returned to the script.

Return Fields: None

Available Fields:

  • ~ACCOUNT - the account ID being looked up (char)
  • ~NODEID - the node ID that received the call (char - hexadecimal string)
  • ~TRUNK - the trunk group that the call came in on (numeric)
  • ~PIN - the new pin to set on the account (char) | |

Write RTCP VoIP Metrics

Invoked by VX at the end of a call provides information about the computed metrics related to the call.

Return Fields: None

Available Fields:

  • ~CSN-Call Sequence Number provided for this call
  • ~ROUNDTRIPDELAY - maximum two-way delay measured between RTCP end points in milliseconds
  • ~PACKETDISACRDRATE - fraction of packets discarded due to jitter (expressed as a fraction of out of 255)
  • ~BURSTLENGTH - mean length calculated from the burst density in milliseconds
  • ~BURSTDENSITY - fraction of packets lost and discarded within a burst (high loss rate) period (expressed as a fraction of out of 255)
  • ~GAPLENGTH - mean length calculated from the gap density in milliseconds
  • ~GAPDENSITY - fraction of packets lost and discarded within a gap (low packet loss rate) period (expressed as a fraction of out of 255)
  • ~MIN*JITTER - Minimum jitter value.
  • ~MAX*JITTER - Maximum jitter value
  • ~DEV*JITTER - Standard Deviation of the jitter value
  • ~PACKETLOSSRATE - fraction of packets lost within the network (expressed as a fraction of out of 255) | |

Write CDR Line

Write CDR Line is not called directly from a script, but rather is invoked by VX at the end of a call. A great deal of information about the completed call is made available for the query to log into the database. This query should take the information that it wishes to store, and append a new row into the appropriate database table. No data is returned from this query.

Return Fields: None

Available Fields:

  • ~ACCOUNT - account ID provided for this call
  • ~ALERTING - time at which this call entered an alerting state
  • ~AVGROUNDTRIPDELAY - Average of Round Trip Delay over a call (expressed in milliseconds).
  • ~BURSTLENGTH - Burst length indicates the mean length calculated from the burst density (expressed in milliseconds).
  • ~BURSTDENSITY - Burst density refers to the fraction of packets lost and discarded within a burst (high loss rate) period (expressed as a fraction of out of 255).
  • ~BWDINPACKETCOUNT - number of packets received from the backward direction
  • ~BWDINPACKETTYPE - type of packets received from the backward direction, as one of the following:0 = unset
    1 = VTP
    2 = RTP
    3 = RAW
  • ~BWDOUTPACKETCOUNT - number of packets sent in the backward direction
  • ~BWDOUTPACKETTYPE - type of packets sent in the backward direction, as one of the following:0 = unset
    1 = VTP
    2 = RTP
    3 = RAW
  • ~CALLDROP - call Drop Reason (See Call Failure Causes for a list of call drop causes.)
  • ~CALLEDNUM - destination number
  • ~CALLEDPLAN - destination number's plan
  • ~CALLEDTYPE - destination number type
  • ~CALLINGNUM - calling Number
  • ~CALLINGPLAN - calling Number's plan
  • ~CALLINGTYPE - calling Number's type
  • ~CALLTYPE - type of call made-1= unset,
    0 = voice,
    1 = T38Fax
    2 = STU
    3 = Modem
    4 = Uncompressed PCM
    5 = Secure Media Inbound only
    6 = Secure Media Outbound only
    7 = Secure Media Inbound and Outbound
    8 = Secure Media Pass-through
  • ~CALLSESSIONID - GUID that identifies this session of a particular call, which changes after each outbound leg completes.
  • ~CHARGEINDICATION: 0=no indicator, 1=no charge, 2=charge, 3=spare.
  • ~CHARGEPULSES: (R2 only) number of charge pulses detected during the call.
  • ~CONNECT - time at which this call entered the Connected state.
  • ~CREATION - creation time of call object
  • ~DEVJITTER - The standard deviation of the relative transit time between two packets in a given sequence.
  • ~DISCONNECT - time at which this call was disconnected
  • ~DNIS - original dialed number
  • ~DNISPLAN - numbering plan of the DNIS
  • ~DNISTYPE - numbering type of the DNIS
  • ~DOMAIN - calling Number's precedence domain (six hexadecimal characters)
  • ~FWDINPACKETCOUNT - number of packets received from the forward direction
  • ~FWDINPACKETTYPE - type of packets received from the forward direction, as one of the following:0 = unset
    1 = VTP
    2 = RTP
    3 = RAW
  • ~FWDOUTPACKETCOUNT - number of packets sent in the forward direction
  • ~FWDOUTPACKETTYPE - type of packets sent in the forward direction, as one of the following:0 = unset
    1 = VTP
    2 = RTP
    3 = RAW
  • ~GAPLENGTH - The mean length calculated from the gap density (expressed in milliseconds).
  • ~GAPDENSITY - The fraction of packets lost and discarded within a gap (low packet loss rate) period (expressed as fraction of 255).
  • ~INBOUNDREMOTENODEID - remote nodeID call was received from (BSP only). If not BSP, the value should be 00000000
  • ~INBOUNDSEIZETIME - sets when the channel detects an inbound seizure of the line.
  • ~INCHANNEL - - the channel that received this call
  • ~INCHASSIS - chassis that this call was received on
    (always 1; future use)
  • ~INIPADDRESS - IP address from which the call was received (applicable to SIP and H323 only). If BSP, the value should be 00000000
  • ~INITIALCONNECT - sets the first time the call or script connects the call. This is reset for each subsequent call.
  • ~INPORT - the port that received this call
  • ~INSLOT - the slot that received this call
  • ~INTRUNK - trunk group at which call entered the node
  • ~IVRSESSIONID - GUID that identifies this call. This GUID remains the same across multiple sessions.
  • ~LOCALCLEAR - time at which this call was locally cleared (except BSP)
  • ~LOCALCSN - the local node's CSN for this call
  • ~LOGTIME - time at which this record was received for recording
  • ~MAXJITTER - The maximum relative transit time between two packets in a given sequence.
  • ~METRIC - metric used to route this call
  • ~MINJITTER - The mean relative transit time between two packets in a given sequence.
  • ~NAMESPACE - calling Number's namespace (DSN, DRSN, Q735, or null for none)
  • ~NODEID - node ID which sent this record
  • ~ORIGINALCALLEDNUMBER - the called number before translation (unlike the DNIS, this will change when calls are made from IVR).
  • ~ORIGINALCALLEDPLAN - numbering plan of the ~ORIGINALCALLEDNUMBER
  • ~ORIGNALCALLEDTYPE - numbering type of the ~ORIGNALCALLEDNUMBER
  • ~OUTBOUNDSEIZE - time at which the outbound channel was seized for this call
  • ~OUTBOUNDREMOTENODEID - remote nodeID call was sent to (BSP only). If not BSP, the value should be 00000000
  • ~OUTCHANNEL - the channel that this call left the node on
  • ~OUTCHASSIS - the chassis that this call left the node on
  • ~OUTIPADDRESS - IP address from which the call was received (applicable to SIP and H323 only). If BSP, the value should be 00000000
  • ~OUTNUMBER - translated outgoing number
  • ~OUTPORT - the port that this call left the node on
  • ~OUTSLOT - the slot that this call left the node on
  • ~OUTTRUNK - the trunk group this call was routed out on
  • ~PACKETDISCARDRATE - Defines the fraction of packets discarded due to jitter (expressed as fraction of out of 255).
  • ~PACKETLOSSRATE - Defines the fraction of packets lost within the network (expressed as fraction of out of 255).
  • ~PRECEDENCE - calling Number's precedence (1-31)
  • ~PRESENTATION - presentation indicator
  • ~REMOTECSN - remote Node's CSN
  • ~REMOTENODEID - node ID that this call went to if BSP (This CDR is obsolete and is valid only if the VX sending the CDR is running VX software version 3.2 or earlier.)
  • ~ROUNDTRIPDELAY - Indicates the maximum two-way delay measured between RTCP end points expressed in msecs.
  • ~ROUTETABLE - routing table used to route this call
  • ~SCREENING - screening Indicator
  • ~SETUPACK - time that the outgoing setup acknowledgment was received (Except BSP)
  • ~SETUPSENT - time that the outgoing setup was sent
  • ~SHOUTGATEVERSION - VXgate Version string
  • ~SHOUTIPVERSION - VXIP Version string (maj.minVbuild)
  • ~TERMCHANNEL - channel that initiated termination of this call
  • ~TERMCHASSIS - chassis that initiated termination of this call (future use)
  • ~TERMPORT - port that initiated termination of this call
  • ~TERMREASON - reason call was terminated (0=Unknown,
    1=Not Available, 2=Internal Network Failure,
    3 = Release Reason (See ~CALLDROP for the cause of the call drop)
  • ~TERMSIDE - which side terminated the call
  • ~TERMSLOT - the slot that initiated termination of this call
  • ~TRANSFERCAP - transfer Capability
  • ~TRANSFERMODE - transfer Mode
  • ~TRANSFERRATE - transfer Rate
  • ~USER1 - user-provided information for the call, as defined with VX script.
  • ~USER2 - user-provided information for the call, as defined with VX script.
  • ~USER3 - user-provided information for the call, as defined with VX script.
  • ~USER4 - user-provided information for the call, as defined with VX script.
  • ~USER5 - user-provided information for the call, as defined with VX script.
  • ~USERRATE - user Rate | |