Connect to MySQL

Asterisk Asterisk Support
1

I’m newbie. I want to use asterisk connect mysql (not PostgreSQL). What files I must to config?

And in extensions.conf, What command I use to get values in database and put values in database?

Thanks for help!

Sorry for my English!

2

You need

  • asterisk-addons
  • edit cdr_mysql.conf (after building asterisk-addons)

voip-info.org/wiki/view/Asterisk+cdr+mysql
voip-info.org/wiki/view/Aste … mysql.conf

MySQL syntaxis in Asterisk dialplan :

voip-info.org/wiki/view/Asterisk+cmd+MYSQL

EDIT :
I forgot this :
cdr.conf

[mysql] usegmtime=no ; log date/time in GMT. Default is "no" loguniqueid=yes ; log uniqueid. Default is "no loguserfield=no ; log user field. Default is "no

3

[quote=“jonaskellens”]You need

  • asterisk-addons
  • edit cdr_mysql.conf (after building asterisk-addons)

voip-info.org/wiki/view/Asterisk+cdr+mysql
voip-info.org/wiki/view/Aste … mysql.conf

MySQL syntaxis in Asterisk dialplan :

voip-info.org/wiki/view/Asterisk+cmd+MYSQL

EDIT :
I forgot this :
cdr.conf

[mysql] usegmtime=no ; log date/time in GMT. Default is "no" loguniqueid=yes ; log uniqueid. Default is "no loguserfield=no ; log user field. Default is "no [/quote]

Thanks for helps. But I want to put values such as CALLERID, CONTEXT, DIALEDTIME… into database mysql, and I want to get these values to process. What do I have to do? And How to config in extension.conf file?

4

you can use perl agi to handle db interaction. For this you will have to install perl and asterisk-perl module. Here is the example

t20.agi

#!/usr/bin/perl

use Asterisk::AGI;
use DBI;

my $AGI = new Asterisk::AGI;

#Read parameters passed with agi
my %input = $AGI->ReadParse();

DB Connection params

%MYSQL = (
hostname => “localhost”,
username => “t20”,
password => “t20”,
database => “t20”
);

Create DB Connection

my $dbh = DBI->connect(“dbi:mysql:$MYSQL{database}:$MYSQL{hostname}”,"$MYSQL{username}","$MYSQL{password}")
|| die(“Couldn’t connect to database!\n”);

my $matchid = “”;
my $teama = “”;
my $teamb = “”;

$AGI->setcallback(&hu);

my $getRowsDB = "SELECT matchid,team1,team2 from t20 limit 1";
my $getRowsDBsth = $dbh->prepare($getRowsDB);
$getRowsDBsth->execute();
my $getRowsDBref = $getRowsDBsth->fetchall_arrayref();
my $getRowsDBrefsz = @{$getRowsDBref};               # get count of number of rows returned from db
if($getRowsDBrefsz > 0){
	$AGI->verbose("............... Rows returned from DB are  $getRowsDBrefsz ...........");
	my @row = @{${$getRowsDBref}[0]};
	$matchid = ${row[0]};
	$teama =  ${row[1]};
	$teamb = ${row[2]});
	$AGI->verbose("............... Matchid = $matchid , team1 is $teama, team2 is  $teamb ...........");
}else{
}

$getRowsDBsth->finish();

This function will be called on call hangup

sub hu(){
$AGI->verbose("----- CALL HANGUP ----- ");
$getRowsDBsth->finish();
$dbh->disconnect();
undef $dbh;
exit;
}

In extensions.conf

exten => s,1,AGI(t20.agi)

5

Or you can do this directly from the dial plan through func_odbc with Asterisk 1.4.x, see voip-info.org/wiki/view/Aste … +func_odbc .
Also 1.6.1 has the adaptive cdr feature, you can add to the cdr table the fields you need, see asterisk.org/node/48492 .

Cheers.

Marco Bruni
www.marcobruni.net

6

Like mbruni says, if you want to define your own columns then use odbc or the latest Asterisk 1.6.

7

Thanks for help!

8

Dialplan mysql is a very good feature and work fine
just start by
exten => x,1,MYSQL(Connect connid localhost login password database)
then you can do exactly same thing like all prog languages, no need to use perl or php agi
Agi is usefull to have a simple dialplan but you can do exatly same things
in dialplan.
I understain dialplan syntax is not very friendly but very powerfull.