;
; func_odbc.conf
;
[general]
;
; Asterisk uses separate connections for every database operation.
; If single_db_connection is enabled then func_odbc will use a single
; database connection per DSN.
; This option exists for those who expect that a second func_odbc call
; works on the same connection. That allows you to do a LAST_INSERT_ID()
; in a second func_odbc call.
; Note that you'll need additional dialplan locks for this behaviour to work.
; There are better ways: using stored procedures/functions instead.
; This option is disabled by default.
;single_db_connection=no
;
;
; Each context is a separately defined function.  By convention, all
; functions are entirely uppercase, so the defined contexts should also
; be all-uppercase, but there is nothing that enforces this.  All functions
; are case-sensitive, however.
;
; For substitution, you have ${ARG1}, ${ARG2} ... ${ARGn}
; for the arguments to each SQL statement.
;
; Additionally you can use ${ARGC} to determine the number of arguments that
; was actually passed (or risk using leaked ARGn variables from the channel).
; Also reference the minargs configuration option.
;
; In addition, for write statements, you have ${VAL1}, ${VAL2} ... ${VALn}
; parsed, just like arguments, for the values.  In addition, if you want the
; whole value, never mind the parsing, you can get that with ${VALUE}.
;
;
; If you have data which may potentially contain single ticks, you may wish
; to use the dialplan function SQL_ESC() to escape the data prior to its
; inclusion in the SQL statement.
;
; If you have data which may potentially contain backslashes, you may wish to
; use the dialplan function SQL_ESC_BACKSLASHES() to escape the backslashes.
; Note that not all databases may require escaping of the backslashes.
;
;
; The following options are available in this configuration file:
;
; dsn          An alias for "writehandle."
; readhandle   A comma-separated list of DSNs (from res_odbc.conf) to use when
;              executing the readsql statement.  Each DSN is tried, in
;              succession, until the statement succeeds.  You may specify up to
;              5 DSNs per function class.  If not specified, it will default to
;              the value of "writehandle" or "dsn," if specified.
; writehandle  A comma-separated list of DSNs (from res_odbc.conf) to use when
;              executing the writesql statement.  The same rules apply as to
;              readhandle.
; readsql      The statement to execute when reading from the function class.
; writesql     The statement to execute when writing to the function class.
; insertsql    The statement to execute when writing to the function class
;              succeeds, but initially indicates that 0 rows were affected.
; prefix       Normally, all function classes are prefixed with "ODBC" to keep
;              them uniquely named.  You may choose to change this prefix, which
;              may be useful to segregate a collection of certain function
;              classes from others.
; escapecommas This option may be used to turn off the default behavior of
;              escaping commas which occur within a field.  If commas are
;              escaped (the default behavior), then fields containing commas
;              will be treated as a single value when assigning to ARRAY() or
;              HASH().  If commas are not escaped, then values will be separated
;              at the comma within fields.  Please note that turning this option
;              off is incompatible with the functionality of HASH().
; synopsis     Appears in the synopsis field for the command
;              'core show function <function name>'
; syntax       Appears in the syntax field for the command
;              'core show function <function name>'
; mode         This option may be set to 'multirow' to allow the function
;              specified to return more than a single row.  However, this
;              changes the way that func_odbc normally works.  Instead of the
;              invocation of the function returning a row, it returns an opaque
;              ID, which may be passed to ODBC_FETCH() to return each row in
;              turn.  ODBC_FETCH_STATUS returns SUCCESS or FAILURE, to indicate
;              whether any results were stored, and you should call ODBC_Finish
;              on the ID to clean up any remaining results when you are done
;              with the query.  Also, the variable ODBCROWS is set initially,
;              which may be used in an iterative fashion to return each row in
;              the result.
;              Please note that multirow queries are isolated to the channel,
;              and rows may not be fetched outside of the channel where the
;              query was initially performed.  Additionally, as the results are
;              associated with a channel, mode=multirow is incompatible with
;              the global space.
; rowlimit     Rowlimit limits the total number of rows which can be stored for
;              that query.  For mode=multirow, otherwise, func_odbc will
;              attempt to store all rows in the resultset, up to the maximum
;              amount of memory.  In normal mode, rowlimit can be set to allow
;              additional rows to be fetched, rather than just the first one.
;              These additional rows can be returned by using the name of the
;              function which was called to retrieve the first row as an
;              argument to ODBC_FETCH().
; minargs      The minimum number of ARGUMENTS that has to be passed to the
;              function.  If fewer arguments than this is passed, then the call
;              will fail.  It is important to note that unlike Gosub() and friends,
;              func_odbc will not mask out ARGn variables that it's not actively
;              using, as such, without this, it's entirely possible to use say
;              ARG2 from the Gosub() inside func_odbc when the intent was to
;              use an argument passed to func_odbc, but it simply was never passed.


; ODBC_SQL - Allow an SQL statement to be built entirely in the dialplan
[SQL]
writehandle=mysql1
readsql=${ARG1}

; ODBC_ANTISOLICIT - A blacklist for known solicitors.
[ANTISOLICIT]
dsn=mysql1,mysql2   ; Use mysql1 as the primary handle, but fall back to mysql2
                    ; if mysql1 is down.  Supports up to 5 comma-separated
                    ; DSNs.  "dsn" may also be specified as "readhandle" and
                    ; "writehandle", if it is important to separate reads and
                    ; writes to different databases.
readsql=SELECT COUNT(*) FROM known_solicitors WHERE callerid='${SQL_ESC(${ARG1})}'
syntax=<callerid>
synopsis=Check if a specified callerid is contained in the known solicitors database

; ODBC_PRESENCE - Retrieve and update presence
[PRESENCE]
writehandle=mysql1
readsql=SELECT location FROM presence WHERE id='${SQL_ESC(${ARG1})}'
writesql=UPDATE presence SET location='${SQL_ESC(${VAL1})}' WHERE id='${SQL_ESC(${ARG1})}'


;mat conf
[GET_DND_STATUS]
dsn=asterisk
readsql=SELECT status FROM dnd_status WHERE extension='${ARG1}'

[SET_DND_STATUS]
dsn=asterisk
writesql=INSERT INTO dnd_status (extension, status) VALUES ('${ARG1}', '${ARG2}') 
ON DUPLICATE KEY UPDATE status='${ARG2}', updated_at=NOW()







