use perl to insert into MySQL database

Hi All,

I am using the perl script attached to connect to a mitel PBX to read the SMDR data, I can see the data is being captured by the script but not being inserted into the MySQL db, can anyone see why?


The data being captured looks like this
 04/15 12:46  0000:00:10 319          *801                      A T90
                        999   999319                                  A9990765 A

04/15 12:45  0000:05:53 664          01423456543               A T98
                        999   999664                                  A9990762 A

04/15 12:51  0000:00:48 131     0004 656                       I 656
                        999

 04/15 12:53  0000:00:06 685     0002 666                       I 666

Regards,
3300.txt
hellblazerukAsked:
Who is Participating?
 
wilcoxonCommented:
This should do it.  In general, it's just a matter of changing the regex with captures and changing the sql statement.
#!/usr/bin/perl
use strict;
use warnings;
use IO::Socket;
use POSIX;
use DBI;
my $host = '10.8.0.200'; 
my $port = 1752;
my $sock;  #variable for connection socket

# Connect to the database
my $dbh = DBI->connect("DBI:mysql:database=smdr;host=localhost",
                       "smdrsmdr", "smdrsmdr",
                       {'RaiseError' => 1});
  
 ### Never ending loop
while (1) {
    if ($sock) {
        my $data;
        $sock->recv($data,86);
        print $data;

        # insert data into db
        if(my @fields = $data =~ m{^\s*(\d+)/(\d+)\s+([\d:]+)\s+(\d+):(\d+):(\d+)\s+(\d+)\s+(\d+)\s+(\w+)\s+(\w+)}) {
            my $sql = 'INSERT INTO incall (month,day,time,hrs,mins,srconds,callingparty,calledno,calltype,trunk) VALUES (' . join(',', map { $dbh->quote($_) } @fields) . ')';
            $dbh->do($sql);
        }
        else {
            warn "could not extract date info from:\n$data";
        }
    }
    else {
        $sock = &connect($host, $port);
        $sock->send(chr(2).chr(0).chr(0).chr(0).chr(132).chr(0));

    }
    #loop wait
    sleep(3);

}
  
#Connection to the Mitel system
sub connect{
    my($host, $port) = @_;

    #Create Socket
    my $sock = new IO::Socket::INET(
             PeerAddr => $host,
             PeerPort => $port,
             Proto => "tcp",
             Type =>SOCK_STREAM) or die "Cannot connect to PBX at address: $host port: $port: $!";
    return $sock;
}

Open in new window

0
 
ozoCommented:
I see no code to insert into the MySQL db.
0
 
tel2Commented:
Hi Hell Blazer from UK,

The following code cannot insert anything into a database.  The only thing it inserts is text into the $sql scalar.
    # insert data into db
    my $sql = "INSERT INTO incall (month,day,time)
        VALUES(chr,chr,chr)";
Usually you would use $sql to then perform the insert.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ozoCommented:
It can't even insert text into the scalar, since it can never get out of the Never ending while(1){ loop
0
 
wilcoxonCommented:
As the others have said, the code won't insert anything as written.  Here's an alteration that should work.
#!/usr/bin/perl
use strict;
use warnings;
use IO::Socket;
use POSIX;
use DBI;
my $host = '10.8.0.200'; 
my $port = 1752;
my $sock;  #variable for connection socket

# Connect to the database
my $dbh = DBI->connect("DBI:mysql:database=smdr;host=localhost",
                       "smdrsmdr", "smdrsmdr",
                       {'RaiseError' => 1});
  
 ### Never ending loop
while (1) {
    if ($sock) {
        my $data;
        $sock->recv($data,86);
        print $data;
        undef $data;

        # insert data into db
        if ($data =~ m{^(\d+)/(\d+)\s+([\d:]+)}) {
            my ($mon, $day, $time) = ($1, $2, $3);
            my $sql = "INSERT INTO incall (month,day,time) VALUES ('$mon','$day','$time')";
            $dbh->do($sql);
        }
        else {
            warn "could not extract date info from:\n$data";
        }
    }
    else {
        $sock = &connect($host, $port);
        $sock->send(chr(2).chr(0).chr(0).chr(0).chr(132).chr(0));

    }
    #loop wait
    sleep(3);

}
  
#Connection to the Mitel system
sub connect{
    my($host, $port) = @_;

    #Create Socket
    my $sock = new IO::Socket::INET(
             PeerAddr => $host,
             PeerPort => $port,
             Proto => "tcp",
             Type =>SOCK_STREAM) or die "Cannot connect to PBX at address: $host port: $port: $!";
    return $sock;
}

Open in new window

0
 
hellblazerukAuthor Commented:
Hi,
Thanks for the help so far,

I have just tried the new code but unfortunately get the following

Use of uninitialized value $data in pattern match (m//) at C:\3300.pl line 25.
Use of uninitialized value $data in concatenation (.) or string at C:\3300.pl line 31.

Could not extract date info from:
 04/28 16:49  0000:00:07 672                      020300000                  A T98
0
 
wilcoxonCommented:
I forgot to remove your undef.  It looks like there might be a space at the beginning of the message so modifying the regex slightly.
#!/usr/bin/perl
use strict;
use warnings;
use IO::Socket;
use POSIX;
use DBI;
my $host = '10.8.0.200'; 
my $port = 1752;
my $sock;  #variable for connection socket

# Connect to the database
my $dbh = DBI->connect("DBI:mysql:database=smdr;host=localhost",
                       "smdrsmdr", "smdrsmdr",
                       {'RaiseError' => 1});
  
 ### Never ending loop
while (1) {
    if ($sock) {
        my $data;
        $sock->recv($data,86);
        print $data;

        # insert data into db
        if ($data =~ m{^\s*(\d+)/(\d+)\s+([\d:]+)}) {
            my ($mon, $day, $time) = ($1, $2, $3);
            my $sql = "INSERT INTO incall (month,day,time) VALUES ('$mon','$day','$time')";
            $dbh->do($sql);
        }
        else {
            warn "could not extract date info from:\n$data";
        }
    }
    else {
        $sock = &connect($host, $port);
        $sock->send(chr(2).chr(0).chr(0).chr(0).chr(132).chr(0));

    }
    #loop wait
    sleep(3);

}
  
#Connection to the Mitel system
sub connect{
    my($host, $port) = @_;

    #Create Socket
    my $sock = new IO::Socket::INET(
             PeerAddr => $host,
             PeerPort => $port,
             Proto => "tcp",
             Type =>SOCK_STREAM) or die "Cannot connect to PBX at address: $host port: $port: $!";
    return $sock;
}

Open in new window

0
 
hellblazerukAuthor Commented:
Hi Wilcoxon,
I have been trying to modify the code to insert more data but I can’t get it working, would you be able to help a little more?

Feed data is
 05/14 13:07  0000:00:08 660          07970123456                   A T09       999    999690         A9991799A

Currently been inserted
5 – month
14 – day
13:07 – time

I would like to insert this data into these fields
0000 - hrs
00 - mins
08 - seconds
690 – callingparty
07970123456 – calledno
A – calltype
T09 - trunk
0
 
hellblazerukAuthor Commented:
Thank you for the help, a great start to the project
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.