asterisk users - Apr 2008 - 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

On Monday 28 April 2008 17:30, Robert McNaught wrote:
> 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.
> 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

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