Hi monks i am getting invalid precision error while executing the following script
Is there any solution
use strict;
use File::Copy;
use Cwd;
use DBI;
#use DBD::Oracle; # Oracle
use DBD::ODBC;
use Time::Local;
use warnings;
use Carp;
my $dbh;
my %hdr_field_vars = (
tran_seq_nbr=>[167,"N12_0"],
org_number=>[28,"I"],
store_number=>[167,"I"],
tran_date=>['2006-12-08 00:00:00',"D"],
terminal_number=>[2, "I"],
tran_number=>[801,"I"],
# tran_time=>['2006-12-08 15:41:00',"D"],
# tran_type=>['02',"V4"],
sub_tran_type=>['C',"V4"],
clerk_code=>['116704444',"V15"],
tran_taxable_amount=>[-35.10,"N12_2"],
tran_tax_total=>[0,"N12_2"],
tran_gross_total=>[-35.10,"N12_2"],
coup_total=>[3.90,"N9_2"],
mkd_total=>[0,"N9_2"],
employee_number=>['NULL',"V15"],
tran_alttax_total=>[0,"N12_2"],
sale_amount=>[0, "N9_2"],
return_amount=>[39.00, "N9_2"],
tender_code=>['01',"V4"],
split_tender=>['N',"V2"],
rtn_with_rcpt=>['N',"V2"],
pv_time_lapse=>[0,"I"],
orig_store_nbr=>[0,"I"],
#orig_tran_date=>['',"D"],
orig_term_nbr=>[0,"I"],
orig_tran_nbr=>[0,"I"],
orig_tender_code=>['00',"V4"],
prev_tran_type=>['NULL',"V4"],
prev_sub_tran_type=>['NULL',"V4"],
phone_no=>[0,"N11_0"]
# kcpos_kth_user_403=>['2010-04-27 11:05:51',"D"]
);
my $JBM;
open( $JBM, ">>C:\\Installer\\ETL\\SQL\\output.txt");
# Correction
$dbh = DBI->connect( "DBI:ODBC:Navi_s7200_IndiaCOE",
"sa", "ABCabc123",
{ RaiseError => 1,
AutoCommit => 0,
PrintError => 0 } )
or die "Couldn't connect to database: $DBI::errstr";
$dbh->{odbc_ignore_named_placeholders} = 1;
# $dbh->{odbc_default_bind_type}=1;
print $JBM "connected\n";
print $JBM $dbh."\n";
my $first_key = 1;
my $field_var_hash_size;
my $tail = ") VALUES (" ;
#################################
# Creating the insertion statement
#################################
my $HEADER = "INSERT INTO kcpos_tran_header(";
foreach my $key (keys %hdr_field_vars) {
if ( !$first_key ) {
$HEADER .= ",";
$tail .= ",";
}
$tail .= prepareSQLDate( $hdr_field_vars{$key}[1]) ;
$HEADER .= "$key";
$first_key = 0;
}
$HEADER .= $tail . ")";
print $JBM $HEADER."\n";
my $sth_header = $dbh->prepare( $HEADER )
or die "Couldn't prepare statement: " . $dbh->errstr;
print $JBM $sth_header."\n";
my $counter = 1;
foreach my $key (keys %hdr_field_vars) {
my $data_ref = $hdr_field_vars{$key}[0];
my $form=$hdr_field_vars{$key}[1];
my $data;
if(($form=~/^V1$/ || $form=~/[DC]/ || $form=~/^V4$/ || $form=~
+/^V2$/ || $form=~/^V15$/) && defined($data_ref)){
if($data_ref ne 'NULL'){
$data = "'".$data_ref."'";
}
else
{
$data = $data_ref;
}
}
else{
$data = $data_ref;
}
print $JBM $data."\n";
# use Data::Dumper;
# print $JBM "<--------------------";
# print $JBM Dumper($data);
# print $JBM "-------------------->\n";
$sth_header->bind_param( $counter, $data);
$counter++;
}
print $JBM $HEADER;
$sth_header->execute();
$sth_header->finish();
$dbh->commit;
$dbh->disconnect;
############################################
# Function: prepareSQLDate
############################################
# add the convert function to the insert statements data type Date
sub prepareSQLDate {
my $format = $_[0];
# if(defined($format)|| !defined($format))
# {
chomp($format);
if ( $format =~ /D/ )
{
return ( " CAST(?,datetime,120)");
}
elsif ($format =~ /C/)
{
return ( " CAST(?,datetime,112)");
}
else{
return (" ? ");
}
# }
# else {
# return (" ? ");
# }
}
Thanks