perl to mysql

Hi All,

I am using the perl script below to connect to a mitel PBX to read the SMDR data, how would I insert the data into a MySQL db?

Regards,



#!/usr/bin/perl
use strict;
use IO::Socket;
use POSIX;
my $host = '10.25.0.5';
my $port = 1752;
my $sock;  #variable for connection socket
 
### Never ending loop
while(1){
if($sock){
      my $data;
      $sock->recv($data,86);
      print $data;
      undef $data;
 
}
else{
      $sock = &connect($host, $port);
      $sock->send(chr(2).chr(0).chr(0).chr(0).chr(132).chr(0));
 
}
#easy now... don't loop so fast...
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;
}
hellblazerukAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wilcoxonCommented:
You need to use DBI and DBD::mysql.  In general, the code will look like:
use DBI;
my $dbh = DBI->connect("DBI:mysql:database=$db;host=$host", $user, $password);
# for each record you want to insert, do
$dbh->do("insert into $table ($col1, $col2, ..., $colN) values ($val1, $val2, ..., $valN)");

Open in new window

0
hellblazerukAuthor Commented:
should it look something like this?

#!/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;
 
 }
 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;
 }

# Insert into db
  $dbh->do("INSERT INTO calls VALUES (chr, chr)";
0
wilcoxonCommented:
Yes.  That code looks reasonable (provided the connection details are correct and the calls table has two columns).

The one issue I see is with your INSERT statement:
You're missing the closing ).
The insert syntax is invalid (if you want literal chr, you need to quote it like 'chr' - if you want variable values then you need to use variables)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hellblazerukAuthor Commented:
The perl script is now running and I can see the data being captured but its not writing to the db

The data being captured is
 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
                        999
3300.txt
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.

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.