[asterisk-users] Authorize & Microsoft SQL

Fri Dec 19 10:51:16 CST 2008

  Greg's question is this:

    - Does anybody has a sample on how to open and query a Microsoft SQL
database from the dialplan?(and which are the correct drivers/addons to
install?)

  Thanks

CS

-----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 11:42 PM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: Re: [asterisk-users] Authorize & Microsoft SQL

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