Sponsor: VoiceMeUp - Corporate & Wholesale VoIP Services

VoIP Mailing List Archives
Mailing list archives for the VoIP community
 SearchSearch 

[asterisk-users] func_odbc creating records or best practice


 
Post new topic   Reply to topic    VoIP Mailing List Archives Forum Index -> Asterisk Users
View previous topic :: View next topic  
Author Message
asteriskator at gmail.com
Guest
PostPosted: Mon Apr 28, 2008 5:30 pm    Post subject: [asterisk-users] func_odbc creating records or best practice Reply with quote
Hi,

I am trying to write a custom application which will integrate with an
existing MSSQL crm system.

We need to get ahold of the CDR(uniqueid) field in during call-time -
I see from doing a DumpChan(), the CDR unique ID is available as soon
as the call is created. CDRs usind odbc are only written once the
call is completed. Does anyone know if it is possible to use
func_odbc to create a temporary record then delete it so that this
information is available to MSSQL. I was not sure if func_odbc was
limited to just using UPDATE/SELECT queries.

Would there be a better way to do this using the AMI or AGI? It just
seems a little strange to use a database for storing temporary data
such as this?

Thanks in Advance

Robert McNaught
Back to top
tilghman at mail.jeffa...
Guest
PostPosted: Mon Apr 28, 2008 6:17 pm    Post subject: [asterisk-users] func_odbc creating records or best practice Reply with quote
On Monday 28 April 2008 17:30, Robert McNaught wrote:
Quote:
I am trying to write a custom application which will integrate with an
existing MSSQL crm system.

We need to get ahold of the CDR(uniqueid) field in during call-time -
I see from doing a DumpChan(), the CDR unique ID is available as soon
as the call is created. CDRs usind odbc are only written once the
call is completed. Does anyone know if it is possible to use
func_odbc to create a temporary record then delete it so that this
information is available to MSSQL. I was not sure if func_odbc was
limited to just using UPDATE/SELECT queries.

func_odbc can do whatever queries you give it. SELECT/UPDATE are
simply the simplest cases that make it easy to understand the functionality.

Quote:
Would there be a better way to do this using the AMI or AGI? It just
seems a little strange to use a database for storing temporary data
such as this?

I'd agree with you on that. I would tend to set variables directly in the
channel, then query them out using AMI.

--
Tilghman
Back to top
Guest
PostPosted: Tue Apr 29, 2008 8:14 am    Post subject: [asterisk-users] func_odbc creating records or best practice Reply with quote
Robert,

You can access CDR information within the dialplan using the CDR
variable. I'm doing something very similar with a DISA feature for our
employees. We use ODBC to validate them against an existing MSSQL server
(check their employee ID & pin number) then when all is well, I write
some information about the call (including the uniqueid field) out to a
'tracking' table I setup. Then I can join the tracking table and the cdr
table on the uniqueid column and associate employees with calls.

In my dialplan, I use the following snippet for setting the values in
the tracking table: (The DBNIS= line is where I do the insert)

exten => valid_login,1,NoOp()
exten => valid_login,n,Set(CALLDATE=${STRFTIME(${EPOCH},GMT+5,%x %X)})
exten => valid_login,n,Set(CLID=${CALLERID(num)})
exten => valid_login,n,Set(UNID=${CDR(uniqueid)})
exten => valid_login,n,Set(DBINS =
${ODBC_DISA(${CALLDATE},${CLID},${ID_ENTERED},${UNID})})
exten =>
valid_login,n,Playback(/var/lib/asterisk/sounds/custom/disa_greet3)
exten => valid_login,n,DISA("no-password",from-disa,"XXXXXXX"
<614xxxxxxxx>)
exten => valid_login,n(end),Hangup

HTH!
Jason

-----Original Message-----
From: asterisk-users-bounces at lists.digium.com
[mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of Robert
McNaught
Sent: Monday, April 28, 2008 6:31 PM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: [asterisk-users] func_odbc creating records or best practice

Hi,

I am trying to write a custom application which will integrate with an
existing MSSQL crm system.

We need to get ahold of the CDR(uniqueid) field in during call-time -
I see from doing a DumpChan(), the CDR unique ID is available as soon
as the call is created. CDRs usind odbc are only written once the
call is completed. Does anyone know if it is possible to use
func_odbc to create a temporary record then delete it so that this
information is available to MSSQL. I was not sure if func_odbc was
limited to just using UPDATE/SELECT queries.

Would there be a better way to do this using the AMI or AGI? It just
seems a little strange to use a database for storing temporary data
such as this?

Thanks in Advance

Robert McNaught

_______________________________________________
-- 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
Back to top
bwentdg at pipeline.com
Guest
PostPosted: Fri May 09, 2008 1:39 am    Post subject: [asterisk-users] func_odbc creating records or best practice Reply with quote
Quote "

func_odbc can do whatever queries you give it. SELECT/UPDATE are
simply the simplest cases that make it easy to understand the functionality"

*OK - but are the Limited to SINGLE STATEMETS or can you have a Muli-Statemnt Transaction ?*?

Tilghman Lesher wrote:
Quote:
On Monday 28 April 2008 17:30, Robert McNaught wrote:

Quote:
I am trying to write a custom application which will integrate with an
existing MSSQL crm system.

We need to get ahold of the CDR(uniqueid) field in during call-time -
I see from doing a DumpChan(), the CDR unique ID is available as soon
as the call is created. CDRs usind odbc are only written once the
call is completed. Does anyone know if it is possible to use
func_odbc to create a temporary record then delete it so that this
information is available to MSSQL. I was not sure if func_odbc was
limited to just using UPDATE/SELECT queries.


func_odbc can do whatever queries you give it. SELECT/UPDATE are
simply the simplest cases that make it easy to understand the functionality.


Quote:
Would there be a better way to do this using the AMI or AGI? It just
seems a little strange to use a database for storing temporary data
such as this?


I'd agree with you on that. I would tend to set variables directly in the
channel, then query them out using AMI.

Back to top
tilghman at mail.jeffa...
Guest
PostPosted: Fri May 09, 2008 8:39 am    Post subject: [asterisk-users] func_odbc creating records or best practice Reply with quote
On Friday 09 May 2008 01:39:53 Al Baker wrote:
Quote:
Quote "

func_odbc can do whatever queries you give it. SELECT/UPDATE are
simply the simplest cases that make it easy to understand the
functionality"

*OK - but are the Limited to SINGLE STATEMETS or can you have a
Muli-Statemnt Transaction ?*?

As we don't isolate connections to a single channel, we do not support
multi-statement transactions, no. It's an interesting idea, though. Could
you expound on what you would like to see? It may wind its way into a
future version of func_odbc.

Perhaps only three extra statements, one to start a transaction, which
also reserves the connection handle to the channel (note that this would
require turning off connection sharing, which is the default, except for TDS
databases), one to commit, and one to rollback (the last two here would
also release the connection handle back to the pool). Would that be
sufficient?

--
Tilghman
Back to top
bwentdg at pipeline.com
Guest
PostPosted: Fri May 09, 2008 9:19 am    Post subject: [asterisk-users] func_odbc creating records or best practice Reply with quote
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 !!!

Tilghman Lesher wrote:
Quote:
On Friday 09 May 2008 01:39:53 Al Baker wrote:

Quote:
Quote "

func_odbc can do whatever queries you give it. SELECT/UPDATE are
simply the simplest cases that make it easy to understand the
functionality"

*OK - but are the Limited to SINGLE STATEMETS or can you have a
Muli-Statemnt Transaction ?*?


As we don't isolate connections to a single channel, we do not support
multi-statement transactions, no. It's an interesting idea, though. Could
you expound on what you would like to see? It may wind its way into a
future version of func_odbc.

Perhaps only three extra statements, one to start a transaction, which
also reserves the connection handle to the channel (note that this would
require turning off connection sharing, which is the default, except for TDS
databases), one to commit, and one to rollback (the last two here would
also release the connection handle back to the pool). Would that be
sufficient?

Back to top
davevg at gmail.com
Guest
PostPosted: Fri May 09, 2008 9:42 am    Post subject: [asterisk-users] func_odbc creating records or best practice Reply with quote
Al Baker wrote:
Quote:
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.
Back to top
sherwood.mcgowan at gm...
Guest
PostPosted: Fri May 09, 2008 11:40 am    Post subject: [asterisk-users] func_odbc creating records or best practice Reply with quote
David Van Ginneken wrote:
Quote:
Al Baker wrote:

Quote:
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
Back to top
davevg at gmail.com
Guest
PostPosted: Fri May 09, 2008 4:04 pm    Post subject: [asterisk-users] func_odbc creating records or best practice Reply with quote
Sherwood McGowan wrote:
Quote:
David Van Ginneken wrote:

Quote:
Al Baker wrote:


Quote:
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
Back to top
tilghman at mail.jeffa...
Guest
PostPosted: Fri May 09, 2008 5:30 pm    Post subject: [asterisk-users] func_odbc creating records or best practice Reply with quote
On Friday 09 May 2008 16:04:58 David Van Ginneken wrote:
Quote:
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.
<snip>

OS error 84 is "Invalid or incomplete multibyte or wide character", so
MySQL is choking on some of the input you've given it.

--
Tilghman
Back to top
Display posts from previous:   
Post new topic   Reply to topic    VoIP Mailing List Archives Forum Index -> Asterisk Users All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group

VoiceMeUp - Corporate & Wholesale VoIP Services