Perl format array elements compatible for mysql insert

Question

Wanted to do the following with the $ALL_Total_Attach_ENB_data[0]

Select specific strings from the CSV data stored in $ALL_Total_Attach_ENB_data[0].
Surround them with single quotes
Pass on that data as input to mysql insert into statement.

I have done Step-1 and not sure how to effectively do the Step-2 & 3. Possibly one liners would help.

Actual data $ALL_Total_Attach_ENB_data[0]= ",ITEM,=,ENB,INTERVAL,=,HOUR,DURATION,=,2013-10-16,11:00,~,2013-10-16,12:00,ENB,TYPE,ATTEMPT,ACCEPT,COMB_ACPT,CTXT_RSP,SUCCESS,HSS_FAIL,ERR_IMEI,ERR_MS,ERR_SVC,GW_FAIL,GW_TO,ENB_FAIL,ENB_TO,UE_FAIL,ESM_FAIL,SM_FAIL,SM_TO,S1_REL,RSC_FULL,SYS_FLT,HSS_TO,AUC_FAIL,AUC_TO,EIR_FAIL,EIR_TO,MS_TO,COLLIS,UNK_PLMN,APN_FAIL,ODB_BAR,DNS_FAIL,DNS_TO,CMP_TO,S1_REL1,ERR_ETC,CANCEL,DTCH,S_RATIO,C_RATIO,SET_TIME,AVG_CPS,PEAK_CPS,SS_RATIO,TOTAL,INIT,162797,150716,0,150134,149450,0,1,0,0,4458,0,10,0,525,245,0,0,39,0,1,0,51,0,0,0,314,18,0,5950,0,1025,0,605,2,2,0,80,92.58,91.80,583.20,45,48,97.25,COMB,17219,4601,0,4571,4560,0,0,0,0,1075,0,0,0,25,27,7,0,2,0,0,0,243,0,0,0,103,1,300,10691,0,163,0,7,0,0,0,12,26.72,26.48,584.81,5,6,93.72,EMG,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.00,0.00,0.00,0,0,0.00";

my @tmp = split(',', $ALL_Total_Attach_ENB_data[0]);
$val1=join(',' , @tmp[9,10,12,13,60 .. 103]);

$db ="4ghourly";
$user = "root";
$pass = "root";
$host="localhost";
$port="3307";

$dbh = DBI->connect("DBI:mysql:$db:$host:$port", $user, $pass);
$query = "INSERT INTO attach (startDate,startTime,endDate,endTime,type,attempt,accept,comb_acpt,ctxt_rsp,success,hss_fail,err_imei,err_ms,err_svc,gw_fail,gw_to,enb_fail,enb_to,ue_fail,esm_fail,sm_fail,sm_to,s1_rel,rsc_full,sys_flt,hss_to,auc_fail,auc_to,eir_fail,eir_to,ms_to,collis,unk_plmn,apn_fail,odb_bar,dns_fail,dns_to,cmp_to,s1_rel1,err_etc,cancel,dtch,s_ratio,c_ratio,set_time,avg_cps,peak_cps,ss_ratio) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

$sqlQuery  = $dbh->prepare($query) or die "Can't prepare $query: $dbh->errstr\n";

$rv = $sqlQuery->execute('2013-10-30','11:00','2013-10-30','12:00','INIT','162797','150716','0','150134','149450','0','1','0','0','4458','0','10','0','525','245','0','0','39','0','1','0','51','0','0','0','314','18','0','5950','0','1025','0','605','2','2','0','80','92.58','91.8','583.2','45','48','97.25') or die "can't execute the query: $sqlQuery->errstr";

if you have the right data in @tmp you can do $rv = $sqlQuery->execute(@tmp);. You could look at splice to set your array correctly or possibly search.cpan.org/~makamaka/Text-CSV-1.32/lib/Text/CSV.pm for a better solution dealing with quoted data containing commas — KeepCalmAndCarryOn
– KeepCalmAndCarryOn, Commented Oct 23, 2013 at 19:29
Consider adding RaiseError to your connect() — it'll take care of the ...or die... logic for you. — pilcrow
– pilcrow, Commented Oct 23, 2013 at 21:25

amon · Accepted Answer · 2013-10-23 19:46:28Z

4

Instead of this:

$val1=join(',' , @tmp[9,10,12,13,60 .. 103]);

You can pass that array slice directly into execute():

$rv = $sqlQuery->execute( @tmp[9,10,12,13,60 .. 103] ) 
    or die "can't execute the query: $sqlQuery->errstr"

There shouldn't be a need to surround them with quotes. The placeholders will take care of quoting things properly.

edited Oct 23, 2013 at 19:46

amon

57.8k2 gold badges93 silver badges152 bronze badges

answered Oct 23, 2013 at 19:27

frezik

2,31615 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Siva Over a year ago

Thanks frezik. This solution works. Since i have multiple array splice (i.e. @tmp[9,10,12,13,60 .. 103]), looking for a solution where i would define all of them in one single place in a var/array rather than hard coding in sql statements. is there a better way for this?

Collectives™ on Stack Overflow

Perl format array elements compatible for mysql insert

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related