[asterisk-users] Authorize & Microsoft SQL

Mon Dec 22 12:26:01 CST 2008

One way to do this would be using
func_odbc.conf
This allows you to define dialplan functions that are based on ODBC  
queries.

Like this, which looks up a meetme room number based on the project
and the 'space' number within that project (sub-project if you like).

[SPACE]
prefix=MEETME
dsn=my_oracle_xe_dsn
read=SELECT confno from meetme where project = '${SQL_ESC(${ARG1})}'  
and spaceno = '${SQL_ESC(${ARG2})}'

I use this function in the dial plan like this:

exten => _[0-9].,n,Set(CNO=${MEETME_SPACE(${PROJECT},${EXTEN})})
exten => _[0-9].,n,NoOp(Conferenece no for  ${EXTEN} is ${CNO}.)
exten => _[0-9].,n,SetMusicOnHold(dumbout)
exten => _[0-9].,n,meetme(${CNO},1nTM)

If you are using a proper database (like MsSQL or Oracle),
then you can hide some of the business logic
by using a database view instead of  messy dialplan logic.

Tim.

On 19 Dec 2008, at 04:42, Steve Wofford wrote:

> There is some code somewhere on the Asterisk/Linux box getting the SQL
> data, be it a program, script or batch file.
>
> There is something initiating the T-SQL code...
>
> 	SELECT * FROM supportcases WHERE id = 123456789
>
> This code comes from the client, not the server. The Asterisk box will
> have the database drivers (ODBC...), but that just allows a  
> connection,
> there is something that tells the server to return data (via the  
> query).
>
> You are going to have to write the script (middleman) and pass it on
> from SQL to Asterisk. I don't know of anything like this ready-made.
>
> 1. DialPlan collect @number from caller
> 2. Call script, program etc and use the @number as a parameter
> 3. The script, program etc will the create the SQL Query to query the
> database:
>
> 	SELECT COUNT(*) FROM supportcases WHERE @number = 123456789
>
> 4. The script, program etc will then get the number of rows returned,
> hopefully 1 or 0 and assign it as a variable.
> 5. Your script, program etc with then use the following logic:
>
> 	If @variable = 0 Then
> 		"Play enter your case again" Voice Prompt
> 	ElseIf @variable = 1 Then
> 		Connect to Agent...
>
> HTH,
>
>
> Steve Wofford
> www.uctrlit.com
> P.(949)743-0233 Ext. 200
>
>
> -----Original Message-----
> From: asterisk-users-bounces at lists.digium.com
> [mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of Gregory
> Malsack
> Sent: Thursday, December 18, 2008 20:23
> To: Asterisk Users Mailing List - Non-Commercial Discussion
> Subject: Re: [asterisk-users] Authorize & Microsoft SQL
>
> Steve, my friends setup does not utilize perl/php code. His
> communication is directly between asterisk and mysql, there is no  
> middle
> man. This is what I was hoping for with ms sql. But it doesn't sound
> like that will be the case.
>
> Thanks for everything!
> Greg
>
> -----Original Message-----
> From: asterisk-users-bounces at lists.digium.com
> [mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of Steve
> Wofford
> Sent: Thursday, December 18, 2008 10:21 PM
> To: Asterisk Users Mailing List - Non-Commercial Discussion
> Subject: Re: [asterisk-users] Authorize & Microsoft SQL
>
> This is exactly what you need. Get your friends perl/php script and  
> the
> SQL code will be near identical, or at least you will have no problem
> changing it yourself even if you don't know SQL.
>
> -----Original Message-----
> From: asterisk-users-bounces at lists.digium.com
> [mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of Gregory
> Malsack
> Sent: Thursday, December 18, 2008 20:13
> To: fred at teamforrest.com; Asterisk Users Mailing List - Non-Commercial
> Discussion
> Subject: Re: [asterisk-users] Authorize & Microsoft SQL
>
> This much I already know. This information is easily found through a
> simple google search. What I'm looking for is if anyone knows what a
> dialplan would look like that would perform an ODBC query to an ODBC
> database. I've seen minuet documentation on ODBCget, which is what I'm
> thinking will do the trick, but as I said the documentation on this is
> so vague that I'm not quite understanding it.
>
> There's also the possibility that there is another option here that  
> I'm
> not seeing. One idea Steve gave me, was to create a perl/php script  
> that
> does the query and returns a result code. Basically acting like a  
> middle
> man between asterisk and the MS SQL database.
>
> Greg
>
> -----Original Message-----
> From: asterisk-users-bounces at lists.digium.com
> [mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of Fred
> Posner
> Sent: Thursday, December 18, 2008 9:54 PM
> To: Asterisk Users Mailing List - Non-Commercial Discussion
> Subject: Re: [asterisk-users] Authorize & Microsoft SQL
>
> All you need is odbc and freetds. Then it will integrate very  
> smoothly.
>
> Fred Posner
> fred at teamforrest.com
> Direct: +1 (503) 914-0999
>
> -----Original Message-----
> From: "Steve Wofford" <SW at uctrlit.com>
>
> Date: Thu, 18 Dec 2008 19:46:36
> To: Asterisk Users Mailing List - Non-Commercial
> Discussion<asterisk-users at lists.digium.com>
> Subject: Re: [asterisk-users] Authorize & Microsoft SQL
>
>
> _______________________________________________
> -- 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
>
>
> _______________________________________________
> -- 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
>
> No virus found in this incoming message.
> Checked by AVG.
> Version: 7.5.552 / Virus Database: 270.9.19/1853 - Release Date:
> 12/17/2008 8:31 AM
>
>
> No virus found in this outgoing message.
> Checked by AVG.
> Version: 7.5.552 / Virus Database: 270.9.19/1853 - Release Date:
> 12/17/2008 8:31 AM
>
>
>
> _______________________________________________
> -- 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
>
> _______________________________________________
> -- 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
>
> No virus found in this incoming message.
> Checked by AVG.
> Version: 7.5.552 / Virus Database: 270.9.19/1853 - Release Date:
> 12/17/2008 8:31 AM
>
>
> No virus found in this outgoing message.
> Checked by AVG.
> Version: 7.5.552 / Virus Database: 270.9.19/1853 - Release Date:
> 12/17/2008 8:31 AM
>
>
>
> _______________________________________________
> -- 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
>
> _______________________________________________
> -- 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