Does anybody know of a way to test whether a mySQL connection invoked
from the dialplan is current or not?
For example:
extensions.conf
==============[context]
exten => _X.,1,MYSQL(Connect connid localhost user pass db)
exten => _X.,n,MYSQL(Query resultid ${connid} SELECT `something` FROM
`table` WHERE `number` = ${EXTEN})
exten => _X.,n,MYSQL(Fetch foundRow ${resultid} something)
exten => _X.,n,MYSQL(Clear ${resultid})
exten => _X.,n,Wait(10) ; just for fun
exten => _X.,n,MYSQL(Disconnect ${connid})
exten => _X.,n,Hangup()
exten => h,1,MYSQL(Disconnect ${connid})
Now if the caller hangs up before the 10 second timeout - then all is
well. But, if they don't, Asterisk tries to disconnect an already
disconnected connection. I need a way of detecting that the connection
has already been disconnected - so I don't try and disconnect it again.
Something like: exten => h,1,ExecIf(CHECK IF CONNECTION STILL OPEN - IN
CASE CALLER HUNG UP AFTER TIME-OUT)?MYSQL(Disconnect ${connid}))
Any ideas?
Ta
If you have received this communication in error we would appreciate
you advising us either by telephone or return of e-mail. The contents
of this message, and any attachments, are the property of DataVox,
and are intended for the confidential use of the named recipient only.
If you are not the intended recipient, employee or agent responsible
for delivery of this message to the intended recipient, take note that
any dissemination, distribution or copying of this communication and
its attachments is strictly prohibited, and may be subject to civil or
criminal action for which you may be liable.
Every effort has been made to ensure that this e-mail or any attachments
are free from viruses. While the company has taken every reasonable
precaution to minimise this risk, neither company, nor the sender can
accept liability for any damage which you sustain as a result of viruses.
It is recommended that you should carry out your own virus checks
before opening any attachments.
Registered in England. No. 27459085.
Andrew Thomas wrote:> exten => _X.,n,MYSQL(Clear ${resultid}) > exten => _X.,n,Wait(10) ; just for fun > exten => _X.,n,MYSQL(Disconnect ${connid}) >You should be doing the wait after the disconnect. Doug -- Ben Franklin quote: "Those who would give up Essential Liberty to purchase a little Temporary Safety, deserve neither Liberty nor Safety."
The wait is there as a test. This gives the 'tester' the option of hanging up before the disconnect or not. Either way - a connection can still be left open if the caller hangs up before the first disconnect. This is my problem. See the 'h' line. -----Original Message----- From: asterisk-users-bounces at lists.digium.com [mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of Doug Lytle Sent: 03 March 2011 14:52 To: Asterisk Users Mailing List - Non-Commercial Discussion Subject: Re: [asterisk-users] mySQL connection testing Andrew Thomas wrote:> exten => _X.,n,MYSQL(Clear ${resultid}) > exten => _X.,n,Wait(10) ; just for fun > exten => _X.,n,MYSQL(Disconnect ${connid}) >You should be doing the wait after the disconnect. Doug -- Ben Franklin quote: "Those who would give up Essential Liberty to purchase a little Temporary Safety, deserve neither Liberty nor Safety." -- _____________________________________________________________________ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users If you have received this communication in error we would appreciate you advising us either by telephone or return of e-mail. The contents of this message, and any attachments, are the property of DataVox, and are intended for the confidential use of the named recipient only. If you are not the intended recipient, employee or agent responsible for delivery of this message to the intended recipient, take note that any dissemination, distribution or copying of this communication and its attachments is strictly prohibited, and may be subject to civil or criminal action for which you may be liable. Every effort has been made to ensure that this e-mail or any attachments are free from viruses. While the company has taken every reasonable precaution to minimise this risk, neither company, nor the sender can accept liability for any damage which you sustain as a result of viruses. It is recommended that you should carry out your own virus checks before opening any attachments. Registered in England. No. 27459085.
MYSQL_STATUS???
Is this documented anywhere (as I can't seem to find anything about this
variable)?
Remember, I need to test whether a specific {connid} is still connected
or not - not the whole mySQL connection.
As for the 'wait' command in my example - it is there purely for testing
purposes - otherwise you'd have to be damn quick to beat the disconnect
(but it's not impossible)!
-----Original Message-----
From: asterisk-users-bounces at lists.digium.com
[mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of Doug Lytle
Sent: 03 March 2011 15:03
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: Re: [asterisk-users] mySQL connection testing
Andrew Thomas wrote:> The wait is there as a test. This gives the 'tester' the option of
> hanging up before the disconnect or not.
>
And the purpose for that would be to share available connections?
I've always considered it bad to leave a connection open and have always
closed them down after a query.
Either way, I test for mysql connection errors by:
exten => s,n,GotoIf($["${MYSQL_STATUS}" =
"-1"]?mysql_failed,s,1)
Doug
--
Ben Franklin quote:
"Those who would give up Essential Liberty to purchase a little
Temporary Safety, deserve neither Liberty nor Safety."
-- _____________________________________________________________________
-- Bandwidth and Colocation Provided by http://www.api-digital.com --
New to Asterisk? Join us for a live introductory webinar every Thurs:
http://www.asterisk.org/hello
asterisk-users mailing list
To UNSUBSCRIBE or update options visit:
http://lists.digium.com/mailman/listinfo/asterisk-users
If you have received this communication in error we would appreciate
you advising us either by telephone or return of e-mail. The contents
of this message, and any attachments, are the property of DataVox,
and are intended for the confidential use of the named recipient only.
If you are not the intended recipient, employee or agent responsible
for delivery of this message to the intended recipient, take note that
any dissemination, distribution or copying of this communication and
its attachments is strictly prohibited, and may be subject to civil or
criminal action for which you may be liable.
Every effort has been made to ensure that this e-mail or any attachments
are free from viruses. While the company has taken every reasonable
precaution to minimise this risk, neither company, nor the sender can
accept liability for any damage which you sustain as a result of viruses.
It is recommended that you should carry out your own virus checks
before opening any attachments.
Registered in England. No. 27459085.
I found that after I typed :) Trouble is - that variable gets triggered after every MYSQL command - not just the disconnect one. So it's no good to me I'm afraid. Thanks for trying. -----Original Message----- From: asterisk-users-bounces at lists.digium.com [mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of Doug Lytle Sent: 03 March 2011 16:15 To: Asterisk Users Mailing List - Non-Commercial Discussion Subject: Re: [asterisk-users] mySQL connection testing Andrew Thomas wrote:> MYSQL_STATUS??? > > Is this documented anywhere (as I can't seem to find anything about > this variable)? >core show application mysql hylafax*CLI> -= Info about application 'MYSQL' =- [Synopsis] Do several mySQLy things [Description] MYSQL(): Do several mySQLy things Syntax: MySQL. On exit, always returns 0. Sets MYSQL_STATUS to 0 on success and -1 on error. Doug -- Ben Franklin quote: "Those who would give up Essential Liberty to purchase a little Temporary Safety, deserve neither Liberty nor Safety." -- _____________________________________________________________________ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users If you have received this communication in error we would appreciate you advising us either by telephone or return of e-mail. The contents of this message, and any attachments, are the property of DataVox, and are intended for the confidential use of the named recipient only. If you are not the intended recipient, employee or agent responsible for delivery of this message to the intended recipient, take note that any dissemination, distribution or copying of this communication and its attachments is strictly prohibited, and may be subject to civil or criminal action for which you may be liable. Every effort has been made to ensure that this e-mail or any attachments are free from viruses. While the company has taken every reasonable precaution to minimise this risk, neither company, nor the sender can accept liability for any damage which you sustain as a result of viruses. It is recommended that you should carry out your own virus checks before opening any attachments. Registered in England. No. 27459085.
On Thu, 3 Mar 2011, Andrew Thomas wrote:> Does anybody know of a way to test whether a mySQL connection invoked > from the dialplan is current or not?I've never been a fan of using database commands in the dialplan. I prefer to wrap up all the database cruft into a nice little black box, an AGI, where I have full access to the database API and real debugging tools. I think database commands in the dialplan are just ugly. -- Thanks in advance, ------------------------------------------------------------------------- Steve Edwards sedwards at sedwards.com Voice: +1-760-468-3867 PST Newline Fax: +1-760-731-3000
That's your opinion - and your entitled to it sir. However, this still doesn't answer my question. Cheers -----Original Message----- From: asterisk-users-bounces at lists.digium.com [mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of Steve Edwards Sent: 03 March 2011 16:23 To: Asterisk Users Mailing List - Non-Commercial Discussion Subject: Re: [asterisk-users] mySQL connection testing On Thu, 3 Mar 2011, Andrew Thomas wrote:> Does anybody know of a way to test whether a mySQL connection invoked > from the dialplan is current or not?I've never been a fan of using database commands in the dialplan. I prefer to wrap up all the database cruft into a nice little black box, an AGI, where I have full access to the database API and real debugging tools. I think database commands in the dialplan are just ugly. -- Thanks in advance, ------------------------------------------------------------------------ - Steve Edwards sedwards at sedwards.com Voice: +1-760-468-3867 PST Newline Fax: +1-760-731-3000 -- _____________________________________________________________________ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users If you have received this communication in error we would appreciate you advising us either by telephone or return of e-mail. The contents of this message, and any attachments, are the property of DataVox, and are intended for the confidential use of the named recipient only. If you are not the intended recipient, employee or agent responsible for delivery of this message to the intended recipient, take note that any dissemination, distribution or copying of this communication and its attachments is strictly prohibited, and may be subject to civil or criminal action for which you may be liable. Every effort has been made to ensure that this e-mail or any attachments are free from viruses. While the company has taken every reasonable precaution to minimise this risk, neither company, nor the sender can accept liability for any damage which you sustain as a result of viruses. It is recommended that you should carry out your own virus checks before opening any attachments. Registered in England. No. 27459085.
Gentlemen, can we please not turn this in to an Asterisk and DB commands bashing thread? All I want is a simple answer to a simple question - not a debate on using AGI/AMI or any other methods. Thanks for your co-operation. -----Original Message----- From: asterisk-users-bounces at lists.digium.com [mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of Doug Lytle Sent: 03 March 2011 16:32 To: Asterisk Users Mailing List - Non-Commercial Discussion Subject: Re: [asterisk-users] mySQL connection testing Danny Nicholas wrote:> Not to mention that Asterisk is "developmental" and "a moving target".And that's why I'm still on 1.4. And, I have no experience with AGI, nor have I had the time to tackle it in the last 6 months. When I finally do move over to 1.8 series, I plan on looking into ODBC. Doug -- Ben Franklin quote: "Those who would give up Essential Liberty to purchase a little Temporary Safety, deserve neither Liberty nor Safety." -- _____________________________________________________________________ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users If you have received this communication in error we would appreciate you advising us either by telephone or return of e-mail. The contents of this message, and any attachments, are the property of DataVox, and are intended for the confidential use of the named recipient only. If you are not the intended recipient, employee or agent responsible for delivery of this message to the intended recipient, take note that any dissemination, distribution or copying of this communication and its attachments is strictly prohibited, and may be subject to civil or criminal action for which you may be liable. Every effort has been made to ensure that this e-mail or any attachments are free from viruses. While the company has taken every reasonable precaution to minimise this risk, neither company, nor the sender can accept liability for any damage which you sustain as a result of viruses. It is recommended that you should carry out your own virus checks before opening any attachments. Registered in England. No. 27459085.
Danny - Thanks, but that wouldn't work either - as I am fetching
multiple rows (not in that example - but I do in a production
environment).
Steve - If mySQL in the dialplan is so bad - why did Digium include it
in the first place? JFYI - I use mySQL in the dialplan all the time -
and it always works a treat - first time, every time. I do use AGI for
'other' things (eg. I've completely re-written the
AgentCallbackLogin
feature in php) and that also works a treat. Each to their own I guess.
Anyway - back to the question (repeated in case it got lost amongst all
this) "Is there a way to check if a specific MYSQL connection id is
connected or not?".
BTW - using a 'disconnect {connid}' twice doesn't actually break
anything - it just causes an error on the console. So I can live with a
'no' answer.
Thanks
-----Original Message-----
From: asterisk-users-bounces at lists.digium.com
[mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of Steve
Edwards
Sent: 03 March 2011 17:23
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: Re: [asterisk-users] mySQL connection testing
On Thu, 3 Mar 2011, Andrew Thomas wrote:
> Gentlemen, can we please not turn this in to an Asterisk and DB
> commands
> bashing thread?
I'm just suggesting that maybe you are 'swimming upstream' trying to
use
MySQL within the dialplan.
Much the same as if you were proposing an office system using a 'tin
cans
and string' mesh with carrier pigeons for out of band call signaling and
having a problem with poop buildup on the endpoints -- I might propose
using Asterisk :)
--
Thanks in advance,
------------------------------------------------------------------------
-
Steve Edwards sedwards at sedwards.com Voice: +1-760-468-3867
PST
Newline Fax:
+1-760-731-3000
-- _____________________________________________________________________
-- Bandwidth and Colocation Provided by http://www.api-digital.com --
New to Asterisk? Join us for a live introductory webinar every Thurs:
http://www.asterisk.org/hello
asterisk-users mailing list
To UNSUBSCRIBE or update options visit:
http://lists.digium.com/mailman/listinfo/asterisk-users
If you have received this communication in error we would appreciate
you advising us either by telephone or return of e-mail. The contents
of this message, and any attachments, are the property of DataVox,
and are intended for the confidential use of the named recipient only.
If you are not the intended recipient, employee or agent responsible
for delivery of this message to the intended recipient, take note that
any dissemination, distribution or copying of this communication and
its attachments is strictly prohibited, and may be subject to civil or
criminal action for which you may be liable.
Every effort has been made to ensure that this e-mail or any attachments
are free from viruses. While the company has taken every reasonable
precaution to minimise this risk, neither company, nor the sender can
accept liability for any damage which you sustain as a result of viruses.
It is recommended that you should carry out your own virus checks
before opening any attachments.
Registered in England. No. 27459085.
On Thursday 03 March 2011 08:42:42 Andrew Thomas wrote:> Does anybody know of a way to test whether a mySQL connection invoked > from the dialplan is current or not?There is no way to test it. If you want this, you should track the information yourself or don't disconnect anywhere but in the "h" extension. BTW, the disconnect is not strictly needed in all versions of the addons since 1.4.9. Due to the possibility of a memory leak, the connections are tracked and deleted when the channel is destroyed. See this issue (and the patch) for more information: https://issues.asterisk.org/view.php?id=14757 -- Tilghman
On Friday 04 March 2011 02:47:56 Andrew Thomas wrote:> If mySQL in the dialplan is so bad - why did Digium include it > in the first place?Digium is not responsible for everything that appears in Asterisk. This is a community project, and community volunteers have written large swaths of Asterisk, including the MYSQL command. -- Tilghman
Thanks Tilghman - this is exactly what I wanted to hear. As for the 'inclusion' bit - true, but it's still infused in to the addons package at the Digium end (isn't it?). Anyway, I'll go create a mysql.conf file now :) Cheers -----Original Message----- From: asterisk-users-bounces at lists.digium.com [mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of Tilghman Lesher Sent: 04 March 2011 08:54 To: Asterisk Users Mailing List - Non-Commercial Discussion Subject: Re: [asterisk-users] mySQL connection testing On Thursday 03 March 2011 08:42:42 Andrew Thomas wrote:> Does anybody know of a way to test whether a mySQL connection invoked > from the dialplan is current or not?There is no way to test it. If you want this, you should track the information yourself or don't disconnect anywhere but in the "h" extension. BTW, the disconnect is not strictly needed in all versions of the addons since 1.4.9. Due to the possibility of a memory leak, the connections are tracked and deleted when the channel is destroyed. See this issue (and the patch) for more information: https://issues.asterisk.org/view.php?id=14757 -- Tilghman -- _____________________________________________________________________ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users If you have received this communication in error we would appreciate you advising us either by telephone or return of e-mail. The contents of this message, and any attachments, are the property of DataVox, and are intended for the confidential use of the named recipient only. If you are not the intended recipient, employee or agent responsible for delivery of this message to the intended recipient, take note that any dissemination, distribution or copying of this communication and its attachments is strictly prohibited, and may be subject to civil or criminal action for which you may be liable. Every effort has been made to ensure that this e-mail or any attachments are free from viruses. While the company has taken every reasonable precaution to minimise this risk, neither company, nor the sender can accept liability for any damage which you sustain as a result of viruses. It is recommended that you should carry out your own virus checks before opening any attachments. Registered in England. No. 27459085.