Using with Returned Info

Capturing Results of SQL Call into Variables

To use the sql_results routine, you must
  1. return the variables separated by a distinct character
  2. then split the vars inside Perl


You can capture a single field, a row of fields, or multiple rows. Each approach has some of the same requirements:

A Single Value Returned

In this example, I check the number of distinct records (which in this case translate into test dates) for a specific Prem_ID and Disease.
@sql_nr_dates = <<EOT;
    select count(seq_nr)
    from event_summary es, sample smp
    where es.es_nr=smp.es_nr
      and es.prem_id='$prem_id' $and_prem_state1
      and event_date between SYSDATE - (4*365) and SYSDATE
      and smp.disease='$disease'
I run the stmt and return the single value into the $nr_dates var:
Because there is only one value, I don't have to parse it, but I must remove the trailing cr/lf, ESPECIALLY IF I WANT TO USE THE VALUE TO VERIFY STUFF IS PASSED IN. Use the chop function.
and then you can use the variable however you wish:
if ($nr_dates == 0) { do stuff }
elsif ($nr_dates == 1) { do other stuff }
elsif ($nr_dates > 1) { do other stuff }
else # nr_dates < 0 or undefined
{ display error}

To Verify Passed in Values

This subroutine checks that the user entered a valid Prem_ID:
sub CheckPremID {
  if (!defined $prem_state || length($prem_state) < 2)   {
    $and_state = " ";
  else {$and_state = " and prem_state=upper('$prem_state')";}
  @sql_prem = <<EOT;
    select prem_id from premises
    where prem_id = '$prem_id' $and_state;

  if ($db_prem eq $prem_id) {$check=0;}
  else {$check=1;}
Notice that I test not just for equality, but for existence of the variable to begin with.

Returning a Single Row

This is almost identical to returning multiple rows so I'll use the same example presented in the overview:
@sql_stmt= <<EOM;
  select student_name, '~', student_id, '~', class_name, '~', grade, '~'
  from tables .....;


This separates the array $new_fields into 5 separate variables. Merely ignore $remainder and you can process the first row of variables returned.

Returning Multiple Rows

The trick to the logic of returning multiple rows is to assign the initial collection of fields to the $remainder var, then do a while (remainder) {}[which operates as long as remainder is not null which means there will be one extra process for the final cr/lf]:
  while ($remainder) {
    if ($remainder) { # avoid processing final bogus record 
      process the individual record
    $rec_nr +=1;