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-boun...@lists.digium.com
> [mailto:asterisk-users-boun...@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-boun...@lists.digium.com
> [mailto:asterisk-users-boun...@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-boun...@lists.digium.com
> [mailto:asterisk-users-boun...@lists.digium.com] On Behalf Of Gregory
> Malsack
> Sent: Thursday, December 18, 2008 20:13
> To: f...@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-boun...@lists.digium.com
> [mailto:asterisk-users-boun...@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
> f...@teamforrest.com
> Direct: +1 (503) 914-0999
>
> -----Original Message-----
> From: "Steve Wofford" <s...@uctrlit.com>
>
> Date: Thu, 18 Dec 2008 19:46:36
> To: Asterisk Users Mailing List - Non-Commercial
> Discussion<asterisk-users@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
_______________________________________________
-- 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
