Using *43 with an external database

[urbanpeasant (Nathan)]

Hello,
Recently had a great bit of help (from Lorne) with the config for a context to speak back a calling extension where Feature Code *65 didn’t quite work for us.
The last post on that thread indicated that FPBX can do part of the next problem we’re trying to solve as we move off Cisco Call Manager and Unified Contact Centre Express:

IVR to read back the calling extension

The built in feature code *43 does all this for you: [image]

But we’re stuck again, so here’s proper info to see if anyone (Lorne?) can help.

We’re on a campus, and have roughly 650 analogue extensions that must remain up during power cuts. These extensions are tested on a rota to make sure there’s no visible damage to the device, and that two way audio works fine (Feature Code *43 works nicely for this bit).
The rota for testing lives in a database that is accessed by going to device/location that is about to be tested, dialing an extension for the service and logging in with DTMF tones. The caller is asked (by UCCX) if there is visible damage to the phone: press button 1 records ‘yes’, button 2 records ‘no’.

The caller is then asked to speak after a tone to record a message, which is played back. If the audio is good press 1, if bad, press 2. Then hang up.

If there are problems a ticket is raised by email for that phone/location, and a timer is set for the next test in a month’s time.

*43 works nicely for the audio test. But is there a way FPBX can be made to work with our existing database for recording that a test was carried out, and whether or not a problem exists?

It is feasible that we’ll have to adjust our process and lose the database and the web front end, and simply rely on *43 and the tester noting down physical and/or audio problems manually in a notepad, and later sending an email to the HelpDesk. But it’d be nice to be retain the automation of the database as we currently have it.
Something on this link makes us think that there’s a way to achieve it, but we’re struggling to join the dots.

Dial Plan Connect to External MSSQL Database

I’ve done this with a separate MySQL database on the FreePBX box, but the logic should be the same to connect to MS SQL. Add the ODBC connection information to /etc/odbc.ini [sb-custom] Description=MySQL connection for Custom database information driver=MySQL server=localhost database=sbcustom Port=3306 Socket=/var/lib/mysql/mysql.sock option=3 Charset=utf8 Add it to /etc/asterisk/res_odbc_custom.conf. Obviously, you will not use the AMPDB info… ;Custom Connection for Unique Database Queries…

Thanks for reading
Nathan.

[lgaetz (Lorne Gaetz)]

Do you have an API for the database where you can send the result of the audio test to the remote system using http(s)? If so, it’s pretty simple, building on your previous knowledge for some custom dialplan, you would prompt the caller for DTMF input, store the result and then curl the result to the API with a line like

exten => s,n,Set(curl_result=${CURL(http://domain.com?data1=${somevar1}&data2=${somevar2})})

If you have to connect to the remote database directly you can do that too, but the dialplan is more complicated. You will find examples to do that here in the forum.

[urbanpeasant (Nathan)]

Thanks Lorne,
It’s a direct connection to the database. I’ve had poor results searching for examples that might help (or any examples other than the one I linked to), but got some ideas from an AI search:
e.g.
“Here is an example of how you could use an Asterisk dialplan to connect to an ODBC database, ask the caller a question, and store their response using DTMF tones”

scssCopy code

exten => 123,1,Answer
exten => 123,2,Set(DB_HANDLE=ODBC:database_name)
exten => 123,3,Verbose(Connecting to ODBC database)
exten => 123,4,ODBCStart(DB_HANDLE)
exten => 123,5,Verbose(Connected to ODBC database)
exten => 123,6,Background(prompt_question)
exten => 123,7,WaitForDigit(10)
exten => 123,8,Set(RESPONSE=${DTMF_Digit})
exten => 123,9,Verbose(Received response ${RESPONSE})
exten => 123,10,ODBCQuery(DB_HANDLE, INSERT INTO responses (response) VALUES (${RESPONSE}))
exten => 123,11,Verbose(Stored response in database)
exten => 123,12,ODBCStop(DB_HANDLE)
exten => 123,13,Verbose(Closed connection to database)
exten => 123,14,Hangup

So I’ll see what sense I can make of that output, refine with further questions, try to incorporate the *43 audio test, while using the book at asteriskdocs.org to explain things.

Probably going to take a while, and I’ll fail, then figure out where to buy the help I need to get this completed - competing priorities, and only so long a person can go on beating their head against an immovable object.
I’ll post back what success, or otherwise, I have.
Cheers.
Nathan.