[asterisk-users] func_odbc creating records or best practice

[David Van Ginneken]

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.