#!/usr/local/perl5.00503/bin/perl -w
#---------------------------------------------------------------------
+----------
# Script that extracts all the info from the csv file and inserts it i
+nto the
# oracle data table, the only thing it doesnt work out is all the page
+ category 
# codes, so you have to do that yourself.
#---------------------------------------------------------------------
+----------

use strict;
use lib '../lib';
use lib '/eld/lib/perl5/site_perl/5.005/sun4-solaris/';
use DBI;

my $csvdbh = DBI->connect('DBI:CSV:f_dir=../data','','', {RaiseError =
+> 1, PrintError => 1}) or die "cant connect $DBI::errstr";
my $oradbh = DBI->connect('DBI:Oracle:hloglive.world','uid','pwd', {
                           RaiseError => 1,
                           PrintError => 1,
                           AutoCommit => 0}) or die "cant do ora $DBI:
+:errstr";

my $sth3 = $csvdbh->prepare('select * from linkdata');
$sth3->execute or die "cant execute csv $DBI::errstr";

my $rownum = 1;
my $sth2 = $oradbh->prepare('select id from page_cat where name = ?');
my $ref;

while ($ref = $sth3->fetchrow_arrayref) {
    foreach (@$ref) { $_ = '' unless defined }
    my @row = @$ref;

    $owner = uc($row[1]);
    $row[3] = ' ' if ($row[3] eq '');

    my $page = '';      
    $sth2->execute($row[9]); 
    while (@_ = $sth2->fetchrow) {
        $page = $_[0];
    }
    $page = 1 unless ($page);
    $sth2->finish;


    $row[5] =~ s/^(.{30})?.+/$1/ if (length($row[5]) > 30);
    $row[6] =~ s/^(.{200})?.+/$1/ if (length($row[6]) > 200);
    eval {
        for (@row[3..8]) {
            $_ = $oradbh->quote($_);
        } 
        my $sql = qq%insert into DATA values 
                   ($rownum, '$owner', $page, SYSDATE, 
                   $row[3],$row[5],$row[6],$row[7], $row[8],'','','','
+','' )%;

        my $insth = $oradbh->prepare($sql);
        $insth->execute;
        $insth->finish;
    };
    $rownum++;
    if ($@) {
       print "$@";
       $oradbh->rollback;
    } else {
       print "row done";
       $oradbh->commit;
    }
    print "$rownum\n";
}

$sth3->finish or die "$DBI::errstr";

$oradbh->disconnect;
$csvdbh->disconnect;
[download]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.