Parsing Arrays II

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

a more complete version of code from my previous write up ie parsing arrays Again any help would be greatly appreciated

my $dbh = DBI->connect("DBI:Sybase:CRAP", "sa", ""); #connects to
$sth4 = $dbh->prepare(qq{
                        SELECT NSS.dbo.SITE.SITE_#, SITE_NAME
                        FROM NSS.dbo.SITE, NSS.dbo.SITE_CONTACT, NSS.d
+bo.CONTACT
                        WHERE NSS.dbo.SITE.SITE_# = NSS.dbo.SITE_CONTA
+CT.SITE_#
                        AND NSS.dbo.CONTACT.CONTACT_# = NSS.dbo.SITE_C
+ONTACT.CONTACT_#
                        AND NSS.dbo.CONTACT.CONTACT_#  = $in{$modConta
+ctNumber}
                   }) or die "Can't prepare SQL statement: $DBI::errst
+r\n";
$sth4->execute
                or die "Can't execute: $DBI::errstr\n";
      my ($newSiteNumber, $newSiteName);
$sth4->bind_columns (undef, \$newSiteNumber, \$newSiteName);


@array=split( " ", $sites);
$count = 0;

while ( @row4 = $sth4->fetchrow() )
{
if (($newSiteNumber) eq ($array[$count]))
{
print "modify";
}
else
{
print "delete";
}
$count = ++$count;
}
[download]

thanks

Comment on Parsing Arrays II Download Code

Replies are listed 'Best First'.
Re: Parsing Arrays II by chromatic (Archbishop) on Dec 20, 2000 at 09:56 UTC
If you're using a version of DBI later than 1.08, you don't need the initial undef when you call bind_columns. You can also substitute $sth4->fetch() for $sth4->fetchrow() and get rid of @row4: `while ($sth->fetch()) { # do comparison }` [download] If i understand your code, you have a list of site numbers in @array that may or may not match results from the database. If the first item of the sites array matches the first row of the database result, you want to modify it. Otherwise, you want to delete. Where I'm confused is the relationship of the array and the db results. If there's an off-by-one in either, the rest of the results will be skewed. It seems to me to make more sense to test for the existence of database results in the sites array, which could be done in one of two ways. Keeping your code mostly similar, we build a hash instead of an array: `my %sites_list; @sites_list{split(" ", $sites)} = (); # hash slice for quicker +lookup while ($sth4->fetch()) { if (exists $sites_list{$newSiteNumber}) { print "modify!"; } else { print "delete!"; } }` [download] The other option would be to let your database do the work, splitting and joining $sites to form a VALUES IN ( ) clause for your SQL statement. Depending on how familiar you are with SQL and Perl, you may prefer one approach over the other. Or I could be completely misunderstanding your intent here, in which case you get double your money back.	[reply] [d/l] [select]
Re: Re: Parsing Arrays II by jasmine (Initiate) on Dec 20, 2000 at 10:13 UTC
Thanks for the help. I'll have a go.	[reply]
Re: Re: Parsing Arrays II by jasmine (Initiate) on Dec 20, 2000 at 11:43 UTC
tried and failed. Though the code is neater it still produces the same results. ie the first value works and the subsequent values don't match. I have printed the values concerned both from db and from array, they appear identical and have no spaces either side. however when compared using eq or exists only the first value returns true	[reply]
Re: Parsing Arrays II by chipmunk (Parson) on Dec 20, 2000 at 09:58 UTC
It looks like you're basically trying to compare two sets; one the set of sites split from $sites, and the other the set of new site numbers fetched from the database. I think the simplest way to compare sets is to use a hash or two. This example checks whether a new site number is in the set of sites: `my @sites = split ' ', $sites; my %sites; for (@sites) { # there are shorter ways to do this step $sites{$_} = 1; # but I'm going for clarity in this example } while ($sth4->fetchrow()) { if (exists $sites{$newSiteNumber}) { print "modify $newSiteNumber\n"; } else { print "delete $newSiteNumber\n"; } }` [download] I hope I'm understanding your question, and that this code sample is helpful. BTW, what is the undef in the call to bind_columns() for?	[reply] [d/l]
Re: Parsing Arrays II by a (Friar) on Dec 20, 2000 at 09:54 UTC
I'm guessing you're missing a loop (and you may want to work on indenting ;-) but: $count = ++$count; isn't, probably, what you mean; it increments count and assigns it to count but, of course, when is undeterministic. That's a different problem, but $count++; is probably enough. Still, you seem to be 'while fetchrow' through one loop and stepping through array at the same time. Did you mean to loop through array for each row? a	[reply]