Roboticus, Finally I got it working. there were quite a few issues apart from the cleanliness:
- First, The chomp part that you said not working here although it was essential. Please find my discussion on below threads:
http://stackoverflow.com/questions/11645696/perls-chomp-chomp-is-removing-the-whole-word-instead-of-the-newline
Chomp is removing the whole word instead of the newline
- I had this chomping problem way before I created this thread. I have documented solution on the stackoverflow one. will post my solution on perlmonks as well
- Second, for whatever reason, I had to unassign "primary Key" column by logging to the SQL developer. Although my entries were not duplicates...still..I had to do it
- Third, I was uploading the values in a wrong way. The DBI documentation led to use the "$dbh->quote" method to upload strings.
- Finally, I used Text::CSV to retrieve individual values of my CSV and then uploaded them using the "$dbh->quote".
Here is the csv:
vm,farm,dmz
name1,farm1,z1
name2,farm2,z2
name3,farm3,z3
and here is my code:
#!/usr/bin/perl -w
use strict;
use Text::CSV;
use DBI;
my $csv = Text::CSV->new ( { binary => 1 } )
or die "Cannot use CSV: ".Text::CSV->error_diag ();
open my $fh, "<:encoding(utf8)", "test.csv" or die " $!";
<$fh>; ### this is to remove the column headers.
my $dbh = DBI->connect ("dbi:Oracle:host=<hostname>;sid=<SID>;port=152
+6", 'username', 'password',
{ RaiseError => 1, AutoCommit => 1, TraceLevel
+ => 0 })
or die "Cannot create Database Handle: $DBI::e
+rrstr()";
while ( my $row = $csv->getline ($fh))
{
my $col1 = $dbh->quote ("$row->[0]");
my $col2 = $dbh->quote ("$row->[1]");
my $col3 = $dbh->quote ("$row->[2]");
$dbh->do ("INSERT INTO CLUSTER_MAPPING (VM, FARM, DMZ) VALUES
+($col1, $col2, $col3)");
}
$dbh->disconnect();
close $fh;
Thank you very much for your advice and time.
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.