Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DBI Query/Processing problems 2

Status
Not open for further replies.
netman4u

netman4u

Technical User
Joined
Mar 16, 2005
Messages
176
Location
US
Hello all,

First time using the DBI module here. I have the following code:

Code: 
use DBI;
#use DBD::Oracle;
use strict;
#use warnings;
my $orauser = "xxxxx"
my $orapass = "xxxxx"
my $vendor;
my $ip;
my %db_results;
my $data_source = "dbi:Oracle:binvdp01";
  my $dbh = DBI->connect($data_source, $orauser, $orapass)
      or die "Can't connect to $data_source: $DBI::errstr";

  my $sth = $dbh->prepare( q{select T1.SVALUE, T2.SVALUE from twtpropvalues T1, twtpropvalues T2 where
								T1.PROPID=(select ID from twtproperties where WID='00000000000000000000000000000001')
								and T2.PROPID=(select ID from twtproperties where 
								WID='72B1577C795AB24888F4462B18D8F42D') and T1.INSID=T2.INSID}
  ) or die "Can't prepare statement: $DBI::errstr";

  my $rc = $sth->execute
      or die "Can't execute statement: $DBI::errstr";

  print "Query will return $sth->{NUM_OF_FIELDS} fields.\n\n";
  print "Field names: @{ $sth->{NAME} }\n";

  while (($ip, $vendor) = $sth->fetchrow_array) {
	  next unless ($ip and $vendor);
	  $db_results{$ip} = $vendor; 
  }
# check for problems which may have terminated the fetch early
  die $sth->errstr if $sth->err;

  $dbh->disconnect;
while ( my($k,$v) = each %db_results ) {
    $db_results{$v} = $k;
}
while ( my($k,$v) = each %db_results ) {
	print "$k => $v\n";
}

Here's what the query looks like in SQLPLUS

Code: 
SQL> select T1.SVALUE, T2.SVALUE from twtpropvalues T1, twtpropvalues T2 where T1.PROPID=(select ID 
from twtproperties where WID='00000000000000000000000000000001') and T2.PROPID=(select ID from twtpr
operties where WID='72B1577C795AB24888F4462B18D8F42D') and T1.INSID=T2.INSID;

SVALUE
--------------------------------------------------------------------------------
SVALUE
--------------------------------------------------------------------------------
205.152.47.32
Juniper

205.152.47.34
Juniper

205.152.47.40
Cisco


SVALUE
--------------------------------------------------------------------------------
SVALUE
--------------------------------------------------------------------------------
205.152.47.39
Cisco

205.152.47.34
Juniper

205.152.47.36
Juniper


SVALUE
--------------------------------------------------------------------------------
SVALUE
--------------------------------------------------------------------------------
205.152.47.40
Cisco

205.152.47.38
Juniper

205.152.47.37
Cisco


SVALUE
--------------------------------------------------------------------------------
SVALUE
--------------------------------------------------------------------------------
205.152.47.37
Cisco

205.152.47.37
Cisco

205.152.47.37
Cisco


SVALUE
--------------------------------------------------------------------------------
SVALUE
--------------------------------------------------------------------------------
205.152.47.37
Cisco

205.152.47.39
Cisco

205.152.47.41
Juniper


SVALUE
--------------------------------------------------------------------------------
SVALUE
--------------------------------------------------------------------------------
205.152.47.32
Juniper

205.152.47.35
Juniper

205.152.204.240
Cisco


SVALUE
--------------------------------------------------------------------------------
SVALUE
--------------------------------------------------------------------------------
205.152.204.240
Cisco

205.152.47.39
Cisco

205.152.47.38
Juniper


SVALUE
--------------------------------------------------------------------------------
SVALUE
--------------------------------------------------------------------------------
205.152.47.41
Juniper

205.152.207.210


205.152.47.35
Juniper


SVALUE
--------------------------------------------------------------------------------
SVALUE
--------------------------------------------------------------------------------
205.152.47.36
Juniper

205.152.47.40
Cisco

205.152.47.34
Juniper


SVALUE
--------------------------------------------------------------------------------
SVALUE
--------------------------------------------------------------------------------
205.152.47.33
Juniper

205.152.47.32
Juniper

205.152.47.37
Cisco


SVALUE
--------------------------------------------------------------------------------
SVALUE
--------------------------------------------------------------------------------
205.152.204.210
Cisco

172.29.98.3
Cisco

192.168.42.2
Cisco


SVALUE
--------------------------------------------------------------------------------
SVALUE
--------------------------------------------------------------------------------
192.168.40.2
Cisco

192.168.43.2
Cisco

205.152.204.210
Cisco


SVALUE
--------------------------------------------------------------------------------
SVALUE
--------------------------------------------------------------------------------
205.152.47.32
Juniper

205.152.204.210
Cisco


38 rows selected.

What I want is a hash like this:

Juniper => 205.152.47.32

If there is no ip associated with the vendor I want to skip it. If there is no vendor associated with the ip skip that as well. While I am at it I want to use the hash to de-duplicate the ip entries.

I have been trying many iterations of my script to no avail. Here is the output I am getting with my current code:

Code: 
D:\Stage>db_conn_test.pl
Query will return 2 fields.

Field names: SVALUE SVALUE
205.152.204.240 => Cisco
205.152.47.40 => Cisco
205.152.47.32 => Juniper
205.152.47.33 => Juniper
205.152.47.41 => Juniper
205.152.47.34 => Juniper
192.168.40.2 => Cisco
205.152.47.35 => Juniper
205.152.47.36 => Juniper
205.152.47.37 => Cisco
205.152.47.38 => Juniper
205.152.47.39 => Cisco
192.168.42.2 => Cisco
Juniper => 205.152.47.38
Cisco => 205.152.204.210
192.168.43.2 => Cisco
172.29.98.3 => Cisco
205.152.204.210 => Cisco

As you can see when there is no vendor associated with an ip it switches key for value for the rest of the entries.

How that explains it ok.

Thanks,

Nick



If at first you don't succeed, don't try skydiving.
 
Sort by date Sort by votes
Code: 
while ( my($k,$v) = each %db_results ) {
    $db_results{$v} = $k;
}

You are switching your key with your value in the above block.
Since you are storing in a hash, these 2 lines are the result of the above chunk of code I pasted:

Code: 
Juniper => 205.152.47.38
Cisco => 205.152.204.210

You de-duplicated based on Juniper and Cisco, and since the 2 ips "205.152.47.32", and "205.152.204.210" are the last 2 shown in your SQL*Plus query, they were the IPs that were associated with Juniper and Cisco.

Everything looks like it is working like you coded it to work...

Or maybe I'm misunderstanding
As you can see when there is no vendor associated with an ip it switches key for value for the rest of the entries.
Click to expand...
 
Upvote 0 Downvote
Your rigth Brian, thanks. I was de-duplicating twice when I switches the key with the value!

If at first you don't succeed, don't try skydiving.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

1DMF
  • Locked
  • Question Question
DBI using DBD::ODBC on MS SQL 2008 R2 corrupting Unicode characters
Replies
0
Views
228
1DMF
1DMF
1DMF
  • Locked
  • Question Question
Text::CSV::Unicode not processing CSV string?
Replies
2
Views
262
1DMF
1DMF
jcarrott
  • Locked
  • Question Question
Trying to use DBI::SQLite
Replies
6
Views
518
jcarrott
jcarrott
jcarrott
  • Locked
  • Question Question
DBI update function
Replies
1
Views
239
feherke
feherke
TSch
  • Locked
  • Question Question
Several Problems working with strings
Replies
4
Views
325
TSch
TSch

Part and Inventory Search

Sponsor

Back
Top