[asterisk-users] Proper AGI use with MySQL

[l***@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

[l***@infoway.net]

Sorry, just to make sure this is clear, in #2 below, when I said "We would like for the AGI script to stay running for the life of the call...", I also meant after the call is transfered to the customer service queue. This is so because we need to note that the call ended (update callend = NOW()) regardless of whether the call stayed only in the IVR or the caller spoke with a customer service agent.

Thanks again

Post by l***@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.
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
_______________________________________________
--Bandwidth and Colocation provided by Easynews.com --
asterisk-users mailing list
http://lists.digium.com/mailman/listinfo/asterisk-users

[Michelle Dupuis]

How about forking the process when the AGI launches, and pass the PID back
to Asterisk in a variable. When the call ends (caught at the "h"), call
another AGI script to kill/stop that pid.

-----Original Message-----
From: asterisk-users-***@lists.digium.com
[mailto:asterisk-users-***@lists.digium.com] On Behalf Of
***@infoway.net
Sent: Monday, May 14, 2007 5:45 PM
To: ***@infoway.net; Asterisk Users Mailing List - Non-Commercial
Discussion
Cc: Asterisk Discussion
Subject: RE: [asterisk-users] Proper AGI use with MySQL

Sorry, just to make sure this is clear, in #2 below, when I said "We would
like for the AGI script to stay running for the life of the call...", I also
meant after the call is transfered to the customer service queue. This is so
because we need to note that the call ended (update callend = NOW())
regardless of whether the call stayed only in the IVR or the caller spoke
with a customer service agent.

Thanks again

Post by l***@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.

Post by l***@infoway.net
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()).

Post by l***@infoway.net
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.
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.

Post by l***@infoway.net
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?

Post by l***@infoway.net
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
_______________________________________________
--Bandwidth and Colocation provided by Easynews.com --
asterisk-users mailing list
http://lists.digium.com/mailman/listinfo/asterisk-users

_______________________________________________
--Bandwidth and Colocation provided by Easynews.com --

asterisk-users mailing list
To UNSUBSCRIBE or update options visit:
http://lists.digium.com/mailman/listinfo/asterisk-users

[l***@infoway.net]

We are not very strong perl programmers and we've been using the Asterisk::AGI library. Is there any sample you can point us that shows how to do this?

Thanks

Post by Michelle Dupuis
How about forking the process when the AGI launches, and pass the PID back
to Asterisk in a variable. When the call ends (caught at the "h"), call
another AGI script to kill/stop that pid.

[Tony Mountifield]

Post by l***@infoway.net
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
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.

IMHO, using DeadAGI in the 'h' extension is the correct way to do it. I use
that technique in many applications and have found it reliable. I'm not sure
why you feel it is not the "proper" way.

Post by l***@infoway.net
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.

You then have the (hopefully rare) problem that if the script, or
asterisk, or the box goes down before you do the final insert, you have
lost all the selections. I would do the updates as they happen. MySQL is
pretty good about caching records, so multiple updates to the same record
should get optimised quite well.

Post by l***@infoway.net
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?

Can't comment on this one, as I never use AGI to dial. My AGIs just
set the context, extension and priority, and exit to the dialplan to do
any dialling.

Post by l***@infoway.net
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.

An AGI is a separate process, so will need to create its own database
handle by calling mysql_connect(). For tidiness, it should release the
handle by calling mysql_close() before exiting, but if it doesn't, the
handle will be closed automatically as the process exits.

There is no way the DeadAGI script could inherit a database handle from
the original AGI acript.

Hope this helps.

Cheers
Tony