<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=us-ascii" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Sherwood McGowan wrote:
<blockquote cite="mid:48247E79.6010306@gmail.com" type="cite">
<pre wrap="">David Van Ginneken wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Al Baker wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I would love to be able to issues the necessary Mysql commands to have
true TRANSACTIONS
Such as - Begin Transaction
Select @var=agent.id, agent.exstension where
agent.status='free'
Update agent.status='BUSY' where agent.id=@var
End Transaction
Of Course the syntax I used above is just psuedo-code and NOT correct MySQL
but I think you can see what I am trying to do. Which I think would be
darn handy !!!
</pre>
</blockquote>
<pre wrap="">I'm not sure if it supports it now as I've never had a need nor tried,
but being able to call stored procedures would be a great addition (If
its not already there) and solve many transaction problems as the
transactions could be done on the server side. Here is a psuedo example
based on your example.
CREATE PROCEDURE `get_available_agent`(
/* Field sizes are arbitrary just for example purposes */
OUT agentid varchar(10),
OUT extension varchar(100)
)
BEGIN
select agents.agentid, agents.extension from agents into agentid,
extension where agents.status = 'FREE' limit 0,1 FOR UPDATE;
if (agentid IS NOT NULL) then
update agents set agents.status = 'BUSY' where agents.agentid = agentid;
end if;
END
If the ODBC driver or implementation cannot read output parameters, just
do a select agentid, extension and have it read the resultset.
_______________________________________________
-- Bandwidth and Colocation Provided by <a class="moz-txt-link-freetext" href="http://www.api-digital.com">http://www.api-digital.com</a> --
asterisk-users mailing list
To UNSUBSCRIBE or update options visit:
<a class="moz-txt-link-freetext" href="http://lists.digium.com/mailman/listinfo/asterisk-users">http://lists.digium.com/mailman/listinfo/asterisk-users</a>
</pre>
</blockquote>
<pre wrap=""><!---->Actually, I don't know about the ODBC func, but there's a patch for
app_mysql_addon() that allows use of stored procedures, I use it quite often
_______________________________________________
-- Bandwidth and Colocation Provided by <a class="moz-txt-link-freetext" href="http://www.api-digital.com">http://www.api-digital.com</a> --
asterisk-users mailing list
To UNSUBSCRIBE or update options visit:
<a class="moz-txt-link-freetext" href="http://lists.digium.com/mailman/listinfo/asterisk-users">http://lists.digium.com/mailman/listinfo/asterisk-users</a>
</pre>
</blockquote>
Out of pure curiosity I tried it today using func_odbc on a test server
here. Short version: It worked with some warnings, which could be
just local to my server. Below are my test configurations and results:<br>
<br>
Asterisk Version: SVN-branch-1.4-r115517 (updated 2 days ago)<br>
MySQL version 5.0.22<br>
<br>
func_odbc.conf<br>
<br>
[AGENT]<br>
prefix=CNF<br>
dsn=asterisk<br>
read=call get_available_agent()<br>
<br>
<br>
<br>
<br>
MySQL Stored Procedure:<br>
<br>
CREATE PROCEDURE `get_available_agent`()<br>
BEGIN<br>
declare agentid varchar(10);<br>
declare extension varchar(100);<br>
select agents.agentid, agents.extension into agentid, extension from
agents where agents.status = "FREE" limit 0,1 FOR UPDATE;<br>
if (agentid IS NOT NULL) then<br>
update agents set agents.status = 'BUSY' where agents.agentid =
agentid;<br>
select agentid, extension;<br>
else<br>
select '','';<br>
end if;<br>
END<br>
<br>
Table Structure and sample data:<br>
<br>
CREATE TABLE IF NOT EXISTS `agents` (<br>
`agentid` varchar(10) NOT NULL,<br>
`extension` varchar(10) NOT NULL,<br>
`status` varchar(10) NOT NULL default 'FREE'<br>
) ENGINE=MyISAM;<br>
<br>
<br>
INSERT INTO `agents` (`agentid`, `extension`, `status`) VALUES<br>
('1000', 'SIP/1000', 'FREE'),<br>
('1001', 'SIP/1001', 'FREE');<br>
<br>
extensions.conf<br>
<br>
exten => 999,1,Answer<br>
exten => 999,n,Set(ARRAY(DB_AGENTID,DB_EXTEN)=${CNF_AGENT()})<br>
exten => 999,n,Noop(AID: ${DB_AGENTID})<br>
exten => 999,n,Noop(EXT: ${DB_EXTEN})<br>
<br>
<br>
Results:<br>
<br>
-- Executing [999@default:1] Answer("SIP/1223-086bc550", "") in new
stack<br>
-- Executing [999@default:2] Set("SIP/1223-086bc550",
"ARRAY(DB_AGENTID|DB_EXTEN)=1000,SIP/1000") in new stack<br>
-- Executing [999@default:3] NoOp("SIP/1223-086bc550", "AID: 1000")
in new stack<br>
-- Executing [999@default:4] NoOp("SIP/1223-086bc550", "EXT:
SIP/1000") in new stack<br>
== Auto fallthrough, channel 'SIP/1223-086bc550' status is 'UNKNOWN'<br>
-- Executing [h@default:1] NoOp("SIP/1223-086bc550", "") in new
stack<br>
-- Executing [h@default:2] GotoIf("SIP/1223-086bc550", "1?end") in
new stack<br>
-- Goto (default,h,5)<br>
-- Executing [h@default:5] NoOp("SIP/1223-086bc550", ""Done"") in
new stack<br>
-- Executing [999@default:1] Answer("SIP/1223-086bc550", "") in new
stack<br>
-- Executing [999@default:2] Set("SIP/1223-086bc550",
"ARRAY(DB_AGENTID|DB_EXTEN)=1001,SIP/1001") in new stack<br>
-- Executing [999@default:3] NoOp("SIP/1223-086bc550", "AID: 1001")
in new stack<br>
-- Executing [999@default:4] NoOp("SIP/1223-086bc550", "EXT:
SIP/1001") in new stack<br>
== Auto fallthrough, channel 'SIP/1223-086bc550' status is 'UNKNOWN'<br>
-- Executing [h@default:1] NoOp("SIP/1223-086bc550", "") in new
stack<br>
-- Executing [h@default:2] GotoIf("SIP/1223-086bc550", "1?end") in
new stack<br>
-- Goto (default,h,5)<br>
-- Executing [h@default:5] NoOp("SIP/1223-086bc550", ""Done"") in
new stack<br>
[May 9 16:52:28] WARNING[26286]: res_odbc.c:105
ast_odbc_prepare_and_execute: SQL Execute returned an error -1: HYT00:
[MySQL][ODBC 3.51 Driver][mysqld-5.0.22]Lost connection to MySQL server
during query (84)<br>
[May 9 16:52:28] WARNING[26286]: res_odbc.c:113
ast_odbc_prepare_and_execute: SQL Execute error -1! Attempting a
reconnect...<br>
[May 9 16:52:28] WARNING[26286]: res_odbc.c:490 odbc_obj_disconnect:
res_odbc: disconnected 0 from asterisk [asterisk]<br>
[May 9 16:52:28] NOTICE[26286]: res_odbc.c:530 odbc_obj_connect:
Connecting asterisk<br>
[May 9 16:52:28] NOTICE[26286]: res_odbc.c:544 odbc_obj_connect:
res_odbc: Connected to asterisk [asterisk]<br>
-- Executing [999@default:1] Answer("SIP/1223-086bc550", "") in new
stack<br>
-- Executing [999@default:2] Set("SIP/1223-086bc550",
"ARRAY(DB_AGENTID|DB_EXTEN)=,") in new stack<br>
-- Executing [999@default:3] NoOp("SIP/1223-086bc550", "AID: ") in
new stack<br>
-- Executing [999@default:4] NoOp("SIP/1223-086bc550", "EXT: ") in
new stack<br>
== Auto fallthrough, channel 'SIP/1223-086bc550' status is 'UNKNOWN'<br>
-- Executing [h@default:1] NoOp("SIP/1223-086bc550", "") in new
stack<br>
-- Executing [h@default:2] GotoIf("SIP/1223-086bc550", "1?end") in
new stack<br>
-- Goto (default,h,5)<br>
-- Executing [h@default:5] NoOp("SIP/1223-086bc550", ""Done"") in
new stack<br>
[May 9 16:52:30] WARNING[26289]: res_odbc.c:105
ast_odbc_prepare_and_execute: SQL Execute returned an error -1: HYT00:
[MySQL][ODBC 3.51 Driver][mysqld-5.0.22]Lost connection to MySQL server
during query (84)<br>
[May 9 16:52:30] WARNING[26289]DEFINER=`root`@`localhost` :
res_odbc.c:113 ast_odbc_prepare_and_execute: SQL Execute error -1!
Attempting a reconnect...<br>
[May 9 16:52:30] WARNING[26289]: res_odbc.c:490 odbc_obj_disconnect:
res_odbc: disconnected 0 from asterisk [asterisk]<br>
[May 9 16:52:30] NOTICE[26289]: res_odbc.c:530 odbc_obj_connect:
Connecting asterisk<br>
[May 9 16:52:30] NOTICE[26289]: res_odbc.c:544 odbc_obj_connect:
res_odbc: Connected to asterisk [asterisk]<br>
-- Executing [999@default:1] Answer("SIP/1223-086c8148", "") in new
stack<br>
-- Executing [999@default:2] Set("SIP/1223-086c8148",
"ARRAY(DB_AGENTID|DB_EXTEN)=,") in new stack<br>
-- Executing [999@default:3] NoOp("SIP/1223-086c8148", "AID: ") in
new stack<br>
-- Executing [999@default:4] NoOp("SIP/1223-086c8148", "EXT: ") in
new stack<br>
== Auto fallthrough, channel 'SIP/1223-086c8148' status is 'UNKNOWN'<br>
-- Executing [h@default:1] NoOp("SIP/1223-086c8148", "") in new
stack<br>
-- Executing [h@default:2] GotoIf("SIP/1223-086c8148", "1?end") in
new stack<br>
-- Goto (default,h,5)<br>
-- Executing [h@default:5] NoOp("SIP/1223-086c8148", ""Done"") in
new stack<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
</body>
</html>