[asterisk-users] func_odbc creating records or best practice
David Van Ginneken
davevg at gmail.com
Fri May 9 16:04:58 CDT 2008
Sherwood McGowan wrote:
> David Van Ginneken wrote:
>
>> Al Baker wrote:
>>
>>
>>> 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 !!!
>>>
>>>
>>>
>>>
>> 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 http://www.api-digital.com --
>>
>> asterisk-users mailing list
>> To UNSUBSCRIBE or update options visit:
>> http://lists.digium.com/mailman/listinfo/asterisk-users
>>
>>
> 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 http://www.api-digital.com --
>
> asterisk-users mailing list
> To UNSUBSCRIBE or update options visit:
> http://lists.digium.com/mailman/listinfo/asterisk-users
>
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:
Asterisk Version: SVN-branch-1.4-r115517 (updated 2 days ago)
MySQL version 5.0.22
func_odbc.conf
[AGENT]
prefix=CNF
dsn=asterisk
read=call get_available_agent()
MySQL Stored Procedure:
CREATE PROCEDURE `get_available_agent`()
BEGIN
declare agentid varchar(10);
declare extension varchar(100);
select agents.agentid, agents.extension into agentid, extension from
agents 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;
select agentid, extension;
else
select '','';
end if;
END
Table Structure and sample data:
CREATE TABLE IF NOT EXISTS `agents` (
`agentid` varchar(10) NOT NULL,
`extension` varchar(10) NOT NULL,
`status` varchar(10) NOT NULL default 'FREE'
) ENGINE=MyISAM;
INSERT INTO `agents` (`agentid`, `extension`, `status`) VALUES
('1000', 'SIP/1000', 'FREE'),
('1001', 'SIP/1001', 'FREE');
extensions.conf
exten => 999,1,Answer
exten => 999,n,Set(ARRAY(DB_AGENTID,DB_EXTEN)=${CNF_AGENT()})
exten => 999,n,Noop(AID: ${DB_AGENTID})
exten => 999,n,Noop(EXT: ${DB_EXTEN})
Results:
-- Executing [999 at default:1] Answer("SIP/1223-086bc550", "") in new stack
-- Executing [999 at default:2] Set("SIP/1223-086bc550",
"ARRAY(DB_AGENTID|DB_EXTEN)=1000,SIP/1000") in new stack
-- Executing [999 at default:3] NoOp("SIP/1223-086bc550", "AID: 1000") in
new stack
-- Executing [999 at default:4] NoOp("SIP/1223-086bc550", "EXT: SIP/1000")
in new stack
== Auto fallthrough, channel 'SIP/1223-086bc550' status is 'UNKNOWN'
-- Executing [h at default:1] NoOp("SIP/1223-086bc550", "") in new stack
-- Executing [h at default:2] GotoIf("SIP/1223-086bc550", "1?end") in new stack
-- Goto (default,h,5)
-- Executing [h at default:5] NoOp("SIP/1223-086bc550", ""Done"") in new stack
-- Executing [999 at default:1] Answer("SIP/1223-086bc550", "") in new stack
-- Executing [999 at default:2] Set("SIP/1223-086bc550",
"ARRAY(DB_AGENTID|DB_EXTEN)=1001,SIP/1001") in new stack
-- Executing [999 at default:3] NoOp("SIP/1223-086bc550", "AID: 1001") in
new stack
-- Executing [999 at default:4] NoOp("SIP/1223-086bc550", "EXT: SIP/1001")
in new stack
== Auto fallthrough, channel 'SIP/1223-086bc550' status is 'UNKNOWN'
-- Executing [h at default:1] NoOp("SIP/1223-086bc550", "") in new stack
-- Executing [h at default:2] GotoIf("SIP/1223-086bc550", "1?end") in new stack
-- Goto (default,h,5)
-- Executing [h at default:5] NoOp("SIP/1223-086bc550", ""Done"") in new stack
[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)
[May 9 16:52:28] WARNING[26286]: res_odbc.c:113
ast_odbc_prepare_and_execute: SQL Execute error -1! Attempting a
reconnect...
[May 9 16:52:28] WARNING[26286]: res_odbc.c:490 odbc_obj_disconnect:
res_odbc: disconnected 0 from asterisk [asterisk]
[May 9 16:52:28] NOTICE[26286]: res_odbc.c:530 odbc_obj_connect:
Connecting asterisk
[May 9 16:52:28] NOTICE[26286]: res_odbc.c:544 odbc_obj_connect:
res_odbc: Connected to asterisk [asterisk]
-- Executing [999 at default:1] Answer("SIP/1223-086bc550", "") in new stack
-- Executing [999 at default:2] Set("SIP/1223-086bc550",
"ARRAY(DB_AGENTID|DB_EXTEN)=,") in new stack
-- Executing [999 at default:3] NoOp("SIP/1223-086bc550", "AID: ") in new stack
-- Executing [999 at default:4] NoOp("SIP/1223-086bc550", "EXT: ") in new stack
== Auto fallthrough, channel 'SIP/1223-086bc550' status is 'UNKNOWN'
-- Executing [h at default:1] NoOp("SIP/1223-086bc550", "") in new stack
-- Executing [h at default:2] GotoIf("SIP/1223-086bc550", "1?end") in new stack
-- Goto (default,h,5)
-- Executing [h at default:5] NoOp("SIP/1223-086bc550", ""Done"") in new stack
[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)
[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...
[May 9 16:52:30] WARNING[26289]: res_odbc.c:490 odbc_obj_disconnect:
res_odbc: disconnected 0 from asterisk [asterisk]
[May 9 16:52:30] NOTICE[26289]: res_odbc.c:530 odbc_obj_connect:
Connecting asterisk
[May 9 16:52:30] NOTICE[26289]: res_odbc.c:544 odbc_obj_connect:
res_odbc: Connected to asterisk [asterisk]
-- Executing [999 at default:1] Answer("SIP/1223-086c8148", "") in new stack
-- Executing [999 at default:2] Set("SIP/1223-086c8148",
"ARRAY(DB_AGENTID|DB_EXTEN)=,") in new stack
-- Executing [999 at default:3] NoOp("SIP/1223-086c8148", "AID: ") in new stack
-- Executing [999 at default:4] NoOp("SIP/1223-086c8148", "EXT: ") in new stack
== Auto fallthrough, channel 'SIP/1223-086c8148' status is 'UNKNOWN'
-- Executing [h at default:1] NoOp("SIP/1223-086c8148", "") in new stack
-- Executing [h at default:2] GotoIf("SIP/1223-086c8148", "1?end") in new stack
-- Goto (default,h,5)
-- Executing [h at default:5] NoOp("SIP/1223-086c8148", ""Done"") in new stack
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.digium.com/pipermail/asterisk-users/attachments/20080509/50e78301/attachment.htm
More information about the asterisk-users
mailing list