Here is away ...
I like to keep all over my *IO* in sub's and treat the sub like a "cursor"
so I have the sub return an *undef* value when when the query is finished
...
here is a typical IO sub for me ...
sub MySchema_Charge {
sub dienice; # declare dienice in main
my($dbh, $initial, $acct, $schema, $platform, $instance, $order)=@_;
my($my_select, $sth, $rv);
if ($initial == 2) {
$my_select =
qq|SELECT OLD_ACCT ACCT, SCHEMA, LAST_UPDATED_DATE, LAST_UPDATED_BY,
PLATFORM_NM PLATFORM, INSTANCE_ALIAS_NM INSTANCE, NEW_ACCT
FROM DAILY_SCHEMA_CHARGE
where OLD_ACCT LIKE UPPER('$acct%')
and SCHEMA LIKE UPPER('$schema%')
and PLATFORM_NM LIKE UPPER('$platform%')
and INSTANCE_ALIAS_NM LIKE UPPER('$instance%')
order by |;
# ACCT, schema, PLATFORM, INSTANCE
foreach (@{$order}) {
$my_select .= " $_ ,";
}
$my_select = substr($my_select,0, -1);
$sth=$dbh->prepare($my_select)
or die "Could NOT prepare statement: " . $dbh->errstr;
# or dienice "Could NOT prepare statement: " . $dbh->errstr;
$rv=$sth->execute
or die "Select failed : ";
# or dienice "Select failed : ";
}
elsif ($initial == 1) {
$my_select =
qq|SELECT count(*)
FROM DAILY_SCHEMA_CHARGE
where OLD_ACCT LIKE UPPER('$acct%')
and SCHEMA LIKE UPPER('$schema%')
and PLATFORM_NM LIKE UPPER('$platform%')
and INSTANCE_ALIAS_NM LIKE UPPER('$instance%')|;
$sth=$dbh->prepare($my_select)
or die "Could NOT prepare statement: " . $dbh->errstr;
# or dienice "Could NOT prepare statement: " . $dbh->errstr;
$rv=$sth->execute
or die "Select failed : ";
# or dienice "Select failed : ";
}
else {
$sth=$initial;
}
my $ary_ref=$sth->fetch;
if ($initial == 1 or $initial == 2){
if (defined($ary_ref)) {
return ($sth, $ary_ref);
}
else {
return ($sth, undef);
}
}
elsif (defined($ary_ref)) {
return $ary_ref;
}
else {
return undef;
}
}
1;
The call looks like ....
...
@rv = MySchema_Charge($dbh,2, # -- open "cursor"
$REQUEST{ACCT},$REQUEST{SCHEMA},$REQUEST{PLATFORM},
$REQUEST{INSTANCE},$REQUEST{ORDER});
($sth, $charge)=@rv;
while ($REQUEST{START} > $skips) {
last unless ($charge); # if "cursor".not_found
($charge) = MySchema_Charge($dbh,$sth);
$skips++;
}
while ($displayed < $REQUEST{START} + $max_lines) {
last unless ($charge); # if "cursor".not_found
$assoc = Assoc_is($charge); # "fetch" next of "cursor"
my $checkbox;
...
I'm sure there are other ways to do this but this lets me test my IO outside
of CGI and make sure it's working before I start coding my CGI script ...
Hope this give you some ideas ....
PS ...
strict is in place, Curtis ...
-----Original Message-----
From: Daniel Falkenberg [mailto:[EMAIL PROTECTED]]
Sent: July 17, 2001 20:31
To: [EMAIL PROTECTED]
Subject: Perl checking for truth in SQL
Hi list,
Basically I want to be able to do an SQL query and if the query returns no
results I want my script to say Ok your query rerurned 0 results print this
message... "Sorry your seach returned no messages".
What is my best way of going about this? Would it work similar to this...
Do search ....
if (!$sql_search) {
print "Sorry your result returned no matches"';
else {
print "here are the results";
$sql_search;
}
}
Any suggestions will help imensly,
Regards,
df
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
