[asterisk-users] Proper AGI use with MySQL

[lists at infoway.net]

Hi,

We have a "simple" AGI script that provides some IVR functionality. It connects to a MySQL database in order to create a call record and capture IVR data.

During the IVR process, we need to store the time the call started, so basically we INSERT a new MySQL row with callstart = NOW(), uniqueid = AGI(agi_uniqueid). As the user selects different options, we update the row to reflect the user's selection. There are a couple of options within the IVR that allows the user to speak with a live customer service rep. So, in those cases, we do a AGI exec to Dial out to the customer service queue and transfer the caller there. In the dialplan, we have extension h, execute DeadAGI which basically looks up the agi_uniqueid and updates the time the call ended in MySQL (e.g. callend = NOW()).

All this seems to be working. However, we just don't feel we are doing things properly and reading up on the wiki more about AGI and dialing out, etc, just makes me feel we could be doing things better.

Here are some of the things we think we could be doing better but are not sure:

1) Ideally, we would like for the AGI script to know when the call hangs up so that it properly updates "callend" without having to run the DeadAGI command in the h extension.

2) We would like for the AGI script to stay running for the life of the call and keep in memory all the user's IVR selections until the call is hung up. At which point, we could actually INSERT the row in MySQL with all the data, instead of constantly hitting the database with updates.

3) We read on the wiki the following: "If the AGI application dials outward by executing Dial, control over the call returns to the dialplan and the script loses contact with the Asterisk server. The script continues to run in the background by itself and is free to clean up and do post-dial processing." In our IVR, we always exit with -1. So, this statement confused us. Does it mean that when we transfer the call to the queue, we should actually return 0 instead of -1 to indicate that the AGI is still running? Can anyone explain this further?

4) When should we close the database handle? Currently, we have it at the end of the AGI script and also as part of the DeadAGI script. However, which one is actually closing it, we don't know.

Comments are extremely welcomed and appreciated.

Thanks