Configure Asterisk and ODBC

Asterisk and ODBC provide the ability to easily update and retrieve data by defining SQL statements as special variables that can be called by dialplan. For example, I can charge for long-distance flights, accept credit cards, and allow asterisk to access other types of messages. This tutorial describes how to configure asterisk to dial the variables represented by an SQL statement. Of course, this can be achieved through the AGI script, but why should we do this?
Here the main talk about MS-SQL connection. Of course, once you have configured ODBC, you can use any SQL Server you want. This is what I want to talk about next. Before you start, make sure that ODBC is set correctly.
Step 2: Set Linux odbc for the MS-SQL 
A wise choice is the Unix ODBC driver. ODBC stands for Open Database Connectivity and open database connection. ODBC has been proven to be an API suitable for most platforms. However, the implementation details are different, and the application layer protocols are also different. In other words, ODBC is encapsulated when calling a database over the network. For example, the TDS protocol is used here.
Install the necessary packages. (Fedora/unbuntu software warehouse)

Reference # Yum list unixodbc *
Loaded plugins: refresh-packagekit
Installed packages
Unixodbc. i386 2.2.12-9. fc10 installed
UnixODBC-devel.i386 2.2.12-9. fc10 installed
# Yum list freetds *
Loaded plugins: refresh-packagekit
Installed packages
Freetds. i386 0.82-4. fc10 installed
Freetds-devel.i386 0.82-4. fc10 installed
Freetds-doc.i386 0.82-4. fc10 installed

 
Security
After installation, we can use freetds to test MS SQL
Server network authorization. If the network is not authorized, ODBC configuration may fail. If an error is reported, check the MS on the Windows server.
SQL log. By default, the remote terminal is disabled. Remember to set ms SQL Server to 'mixed
Mode ', restart sqlserver. Now, TDS is working properly.

Reference # tsql-s your.server.com-P 1433-u WINDOWS-SQL-USERNAME-P Password
Locale is en_US.UTF-8 ″
Locale charset is UTF-8 ″
1>

 
If you can see this information, it is good, not far from success. The next task is to configure freetds. conf, ODBC. ini, and odbcinst. ini.
First open the/etc/ODBC. ini file.

Copy code [Asterisk-connector] Description = VoIP today-asterisk Connector Driver = MS-SQL Servername = MS-SQL Uid = VIPs Port = 1433

 
Note
Set the driver value to '/etc/odbcinst. ini'
The environment name '[MS-SQL]' is very important. The driver information is pulled from that file. Set the value of 'servername' to '/etc/freeitds. conf'.
Environment name '[MS-MySQL]'. Many blogs did not emphasize these two points when introducing ODBC, which gave me a two-day detour. If the two parameters do not match, the following error message is displayed:

Reference # iSQL-v odbc-test your-username your-Password
[Im002] [unixodbc] [Driver manager] data source name not found, and no default driver specified
[ISQL] error: cocould not sqlconnect
#

 
Now
In the '/etc/odbcinst. ini'
The file defines the location of the ODBC driver. The ODBC/tDS driver paths for different Linux releases are different. 'Osdbcinst. ini'
File to the appropriate driver. As you can see above, I have the postgre and ms SQL portals.
The last step is to configure the freetds. conf file.
Freetds is an open-source version of the tabular data system protocol. TDS is an application layer protocol that connects to ODBC through a network. Different SQL servers may need different versions of freetds protocol. These versions are not different files, but are specified in '/etc/freetds. conf. It is usually set to 'tds version = 200 ′.
Then we use the iSQL command to connect to the remote ms SQL Server:

Reference [root @ voiptoday ~] # ISQL-v odbc-test VIPs P @ ssword1234
| Connected! |
|
| SQL-statement |
| Help [tablename] |
| Quit |
|
+ ------------- +
SQL>

 
Good luck. You can see ms SQL CLI. Remind me. The 'odbc-test' of iSQL is defined in the 'odbc. ini 'file. 'Isql 'can be added to any script (PHP, Bash, and so on ).
Step 2: Create MySQL tables and example data
We should be as simple as possible. Now you can connect asterisk to any SQL data table. In our example, an account table contains two fields: ID and balance.

Reference mysql> select * from accounts;
+ ----- + -- +
| Accountid | balance |
+ ----- + -- +
| 10001/5011.00 |
| 10002/4021.00 |
| 10003/2102.00 |
+ ----- + -- +
3 rows in SET (0.00 Sec)

 
Step 2: Configure res_odbc.conf 
The DSN must be defined for Asterisk. You can set it in the res_odbc.conf file under the '/etc/asterisk' directory:

Copy code [Asterisk] Enabled => Yes DSN => asterisk-connector Pre-connect => Yes

 
The dsn in the res_odbc.conf file refers to MSN in '/etc/ODBC. ini' to point to the driver file odbcinst. ini. The DSN information is displayed under asterisk CLI:

Copy code CLI> ODBC show Odbc dsn settings ------ Name: Asterisk DSN: asterisk-connector * CLI>

 
Step 2: Configure func_odbc.conf 
Func_odbc.conf is where we define SQL statements. You can perform data insertion or read/write. We can define special variables so that asterisk can access dynamic data without calling external AgI scripts. This is useful in many scenarios, such as emergency calls and credit card processing.
For example, set asterisk PBX to enable the Insurance Service Agent to obtain the incoming call number. Asteirsk can match CRM compliant with TAPI. When the insurance broker picks up the phone, it can see the customer's information. /Etc/asterisk/func_odbc.conf:

Copy code [Info] Perfix = Account DSN = Asterisk Readsql = select balance from accounts where accountid = '$ {arg1 }'

 
This
It defines the variable names that can be called in dialplan. 'Dsn = asterisk' references the context of res_odbc.conf.
'Asterisk '. 'Readsql ='
This is an interesting part. "Rub" sets the variable name that we call in Asterisk. When we call this variable, it will be named
'$ {Account_info ()}'. $ Arg1 is the dialing number in extensions. conf.
Step 2: extensions. conf configuration file 
Add the following content in extensions. conf:

Copy code [Macro-say-balance] Exten => S, 1, wait (2) Exten => S, N, saynumber ($ {account_info ($ {acountid })}) Exten => S, N, hangup [From-Internal] Exten => 101,1, read (acountid, pls-Enter-your-account-ID) Exten => 101, N, macro (say-balance, $ {acountid })

 
This article demonstrates how to call variables from the SQL database. This saves a lot of time to write AgI and PHP. We can use this method to create a phone card application. SQL databases do not need to be dwelling on Asterisk machines, which simplifies many applications.