PERL_fresher has asked for the wisdom of the Perl Monks concerning the following question:

hello Fellow monks need some help with the code. This code works fine though I am having some trouble with the data thats comings up. 
my $dbh = DBI->connect("dbi:CSV:f_dir=$_ENV::TempFilesDIR;csv_sep_char
+=\\~") or die $DBI::errstr;
$dbh->{'csv_tables'}->{'new_entrant'}   = { 'file' => 'Aviva_cimp_new_
+entrant.txt', col_names => [qw(emp_no  cat_no  forename  surname  ini
+tial  ni_no  ttl  gender  mrtl_sts  dob  hmeadd1  hmeadd2  hmeadd3  h
+ometown  county  homepost  emp_cntrbtn  emplyr_ctr  anl_sal  doj_schm
+  dojcmpny  exchanged  schm_jnr)]};



$dbh->{'csv_tables'}->{'cimp_monthly'}  = { 'file' => 'Aviva_cimp_mont
+hly_extract.txt'};
$dbh->{'csv_tables'}->{'rac_monthly'}   = { 'file' => 'rac_cimp_monthl
+y_extract.txt'};
$dbh->{'csv_tables'}->{'cimp_ops'}      = { 'file' => 'Aviva_pension_o
+pt_outs_extract.txt'};
$dbh->{'csv_tables'}->{'cimp_leavers'}  = { 'file' => 'Aviva_cimp_leav
+ers_extract.txt'};
$dbh->{'csv_tables'}->{'rac_leavers'}   = { 'file' => 'rac_cimp_leaver
+s_extract.txt'};
$dbh->{'csv_tables'}->{'cimp_money'}    = { 'file' => 'Aviva_cimp_mone
+y_positive.txt'};
$dbh->{'csv_tables'}->{'rac_money'}     = { 'file' => 'rac_cimp_money_
+positive.txt'};
$dbh->{'csv_tables'}->{'workday'}       = { 'file' => 'workday_mstr_fi
+nal.csv'};


my $query = "SELECT Employee_Number,Forename,Surname,Home_Address_Line
+_1,Home_Address_Line_2,Home_Address_Line_3,Home_Town_City,Home_County
+,Home_Post_Code FROM workday";
my $sth = $dbh->prepare ($query);
$sth->execute();
$sth->bind_columns (\my ($wrk_emp_no,$wrk_forename,$wrk_surname,$wrk_h
+me_add1,$wrk_hme_add2,$wrk_hme_add3,$wrk_hme_city,$wrk_hme_cnty,$wrk_
+postcode));
while (my $row = $sth->fetch) {

$dbh->do("UPDATE new_entrant SET forename='$wrk_forename',surname='$wr
+k_surname' FROM new_entrant WHERE emp_no='$wrk_emp_no'") or die $DBI:
+:errstr;
 }
$sth->finish();
$dbh->disconnect( );

[download]
<Error> Mismatched single quote before: <071444'> at /usr/local/share/perl5/SQL/Statement.pm line 88 Incomplete statement! at /usr/local/share/perl5/SQL/Statement.pm line 88 DBD::CSV::db do failed: Incomplete statement! for Statement "UPDATE new_entrant SET forename='Sebastian',surname='D'Costa' FROM new_entrant WHERE emp_no='071444'" at test.pl line 30. Incomplete statement! at test.pl line 30. </Error>

Replies are listed 'Best First'.
Re: Escape characters in DBD CSV
by afoken (Chancellor) on May 11, 2015 at 09:56 UTC
    $dbh->do("UPDATE new_entrant SET forename='$wrk_forename',surname='$wrk_surname' FROM new_entrant WHERE emp_no='$wrk_emp_no'") or die $DBI::errstr;

    NEVER put literal values <update>value literals</update> into SQL statements, use placeholders ($dbh->do('UPDATE sometable SET foo=?,bar=?,baz=? WHERE quux=?',undef,$foo,$bar,$baz,$quux);). See DBI, Re: Counting rows Sqlite, Re^2: Massive Memory Leak, Re^5: Variable interpolation in a file to be read in.

    Also note that there is no FROM in an UPDATE statement.

    And finally, enable autodie in DBI->connect(), that removes the need for or die $DBI::errstr for all DBI methods.

    Alexander

    Updated wording, thanks to soonix.

    --
    Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
[reply]
[d/l]
[select]

      Also note that there is no FROM in an UPDATE statement.

      That depends on the RDBMS. SQL Server allows it and its quite convenient too. (DB2 seems to allow it inside a fullselect sub-clause, but that's not what is being done here.)

[reply]
Re: Escape characters in DBD CSV
by Tux (Canon) on May 11, 2015 at 11:23 UTC

    Use the hashref version to connect, it makes it way more obvious what you want. As soonix already mentioned: "Is \~ really what you want?"

    my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
    f_dir              => $_ENV::TempFilesDIR,
    csv_sep_char       => "~", # not "\\~"

    # You might want to add
    f_ext              => ".csv",
    csv_auto_diag      => 1,

    # And if you want NULL support
    csv_null           => 1,

    # And add these to see what is going wrong
    RaiseError         => 1,
    PrintError         => 1,
    ShowErrorStatement => 1,
    }) or die $DBI::errstr;

    [download]

    Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
[select]
Re: Escape characters in DBD CSV
by soonix (Chancellor) on May 11, 2015 at 10:05 UTC
    Probably one of your workers has a wrk_surname of O'Reilly or some such. You should let DBI do the quoting, replace your UPDATE loop with something like
    my $upd = $dbh->prepare("UPDATE new_entrant SET forename=?,surname=? W
+HERE emp_no=?") or die $DBI::errstr;
while (my $row = $sth->fetch) {
    $upd->execute($wrk_forename, $wrk_surname, $wrk_emp_no) or die $DB
+I::errstr;
}
$upd->finish();

    [download]
    BTW is your sep_char really "\\~" i.e. '\~' (the two characters "backslash" and "tilde"?
[reply]
[d/l]
[reply]
[reply]

Back to Seekers of Perl Wisdom