Cookbook example
Hi, I need a little help. I am trying to use example for inserting XML to mysql cdrtable. I have add id before caller_id_name as I need Id in mysql table. Problem is that I am getting random numbers in ID field, sometimes values for ID are the same, so sql perl script return error and values are not inserted. Can you help me how can I modify so that this will work properly. use strict; use warnings; use CGI; use DBI; use Data::Dump qw(dump); $|++; my $q = CGI->new; my $raw_cdr = $q->param('cdr'); my @all_fields = qw(id caller_id_name caller_id_number destination_number context start_stamp answer_stamp end_stamp duration billsec hangup_cause uuid bleg_uuid accountcode domain_name); my @fields; my @values; foreach my $field (@all_fields) { next unless $raw_cdr =~ m/$field>(.*?)</; push @fields, $field; push @values, "'" . urldecode($1) . "'"; } my $cdr_line; my $query = sprintf( "INSERT INTO %s (%s) VALUES (%s);", 'radacct_1', join(',', @fields), join(',', @values) ); my $db = DBI->connect('DBI:mysql:dbname=voipbill_slo;host=xxx.xxx.xxx.xxx','xxxxxx', 'xxxxx'); $db->do($query); print $q->header(); sub urldecode { my $url = shift; $url =~ s/%([a-fA-F0-9]{2,2})/chr(hex($1))/eg; return $url; } [root@fs1 cgi-bin]# cat /var/log/secure Thank you! Regards, Miha
On 4/10/2012 9:20 AM, Miha wrote:
Hi,
I need a little help. I am trying to use example for inserting XML to mysql cdrtable. I have add id before caller_id_name as I need Id in mysql table. Problem is that I am getting random numbers in ID field, sometimes values for ID are the same, so sql perl script return error and values are not inserted.
Can you help me how can I modify so that this will work properly.
use strict; use warnings; use CGI; use DBI; use Data::Dump qw(dump); $|++; my $q = CGI->new; my $raw_cdr = $q->param('cdr');
my @all_fields = qw(id caller_id_name caller_id_number destination_number context start_stamp answer_stamp end_stamp duration billsec hangup_cause uuid bleg_uuid accountcode domain_name); my @fields; my @values; foreach my $field (@all_fields) { next unless $raw_cdr =~ m/$field>(.*?)</; push @fields, $field; push @values, "'" . urldecode($1) . "'"; } my $cdr_line; my $query = sprintf( "INSERT INTO %s (%s) VALUES (%s);", 'radacct_1', join(',', @fields), join(',', @values) ); my $db = DBI->connect('DBI:mysql:dbname=voipbill_slo;host=xxx.xxx.xxx.xxx','xxxxxx', 'xxxxx'); $db->do($query); print $q->header(); sub urldecode { my $url = shift; $url =~ s/%([a-fA-F0-9]{2,2})/chr(hex($1))/eg; return $url; } [root@fs1 cgi-bin]# cat /var/log/secure
Thank you!
Regards, Miha
_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org HI,
I was having same problem with auto increment in mysql. Now it works. Regards, Miha
Hi, If the ID field is set as primary index with auto increment, you should not set it in the insert query. For your script, it looks like just removing the "id" entry @all_fields array should solve the issue. Best Regards, Vallimamod. On Apr 10, 2012, at 9:20 AM, Miha wrote:
Hi,
I need a little help. I am trying to use example for inserting XML to mysql cdrtable. I have add id before caller_id_name as I need Id in mysql table. Problem is that I am getting random numbers in ID field, sometimes values for ID are the same, so sql perl script return error and values are not inserted.
Can you help me how can I modify so that this will work properly.
use strict; use warnings; use CGI; use DBI; use Data::Dump qw(dump); $|++; my $q = CGI->new; my $raw_cdr = $q->param('cdr');
my @all_fields = qw(id caller_id_name caller_id_number destination_number context start_stamp answer_stamp end_stamp duration billsec hangup_cause uuid bleg_uuid accountcode domain_name); my @fields; my @values; foreach my $field (@all_fields) { next unless $raw_cdr =~ m/$field>(.*?)</; push @fields, $field; push @values, "'" . urldecode($1) . "'"; } my $cdr_line; my $query = sprintf( "INSERT INTO %s (%s) VALUES (%s);", 'radacct_1', join(',', @fields), join(',', @values) ); my $db = DBI->connect('DBI:mysql:dbname=voipbill_slo;host=xxx.xxx.xxx.xxx','xxxxxx', 'xxxxx'); $db->do($query); print $q->header(); sub urldecode { my $url = shift; $url =~ s/%([a-fA-F0-9]{2,2})/chr(hex($1))/eg; return $url; } [root@fs1 cgi-bin]# cat /var/log/secure
Thank you!
Regards, Miha _________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
participants (2)
-
Miha
-
Vallimamod ABDULLAH