[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Printing result Perl?

Posted on 2003-12-09
4
Medium Priority
?
742 Views
Last Modified: 2013-12-25
how do i get perl to display rthe result of the sql:
 $uidpwd = $FORM{'username'}."/".$FORM{'password'};
   $stuid = " select spid,surname,forename,userid,faccode,course,slotday,tpid" .
          " from diz.students" .
          " where spid='" .
          $FORM{'id'} ."';";

I want to display the results of '$stuid' but when i use the 'PRINT' function it just displays the sql formula and not the result of the sql help!!
0
Comment
Question by:boffer
2 Comments
 
LVL 3

Accepted Solution

by:
mrh30 earned 100 total points
ID: 9904791
You will need to actually run the SQL query.  This will probably mean:

use DBI;  #Or whatever you're using

## You need to set up the variables you need here

my $dbh = DBI->Connect("DBI:mysql:database=".$db_name.";host=".$db_host, $db_user, $db_pass)
                      or die "Cannot connect to database";

print $dbh->selectrow_array(" ... your sql here ... ");
0
 
LVL 5

Assisted Solution

by:djplaistow
djplaistow earned 100 total points
ID: 9905743
Try this subroutine; the sub prolog has example usage. It relies on some global variables, so be sure they are set.

################################################################################
# Author:  Don Plaistow
# Purpose: This function will execute an arbitrary number of SQL queries on
#          the database specified in the first argument. If any of the queries
#          are a select, then a reference to an array of hashes will be returned.
#          Here is an example of executing a select statement:
#
#              my $sql = qq[SELECT * from myTable;];
#              my $raRows = Sql($dbName, $sql);
#              # Get the value from the first row of the column named "Id"
#              my $firstRowId = $raRows->[0]->{Id};
#              
#              # Loop through all the rows in the data set printing the value of
#              # the column named "Id" for every row
#              foreach my $rhRow (@$raRows)
#              {
#                print "$rhRow->{Id} \n";
#              }
#
# Arguments: $dbName: the database name
#            $sqlStatement: A valid SQL statement
# Global variables:
#             $host: The database host computer
#         $dbUser: The database user name to use for the connection to the DB
# $dbPassword: The password required for the user specified in $dbUser
#    $debugging: Set true if debugging data is desired
#       $debugDir: The directory where the debug text file,
#                          db_executeSQLStatement.txt, will be stored.
################################################################################
sub Sql
{
  my $dbName = shift(@_);
  my $sql = "";
  my $status = "1";
  my @rowArray = ();

  if (my $db = DBI->connect("dbi:mysql:$dbName:$host", $dbUser, $dbPassword, {PrintError => 0, RaiseError => 0}))
  {
    # The remaining function arguments should be valid SQL statements, so execute them
    # until there are no more.
    while (@_)
    {
      $sql = shift(@_);
      # If the query has any whitespace after the closing ';' it might fail
      # TODO: This might not be a problem.
      $sql =~ s/;\s*$/;/;

      if (my $sth = $db->prepare($sql))
      {
        if ($sth->execute())
        {
          # If the SQL statement was a select, then push a reference to the
          # results hash into an array so it can be returned to the caller
          if ($sql =~ m/^\s*select/io)
          {
            while(my $hashRef = $sth->fetchrow_hashref())
            { push (@rowArray, $hashRef); }
          }
        }
        else { $status = "ERROR: Can't execute sql statement: $sql\n\n $DBI::errstr\n";}
      }
      else { $status = "ERROR: Can't prepare sql statement: $sql\n\n $DBI::errstr\n";}
    }
    if (!$db->disconnect)
    { $status = "ERROR: disconnecting: $DBI::errstr\n";}
  }
  else
  { $status = "ERROR: Can't connect to database $DBI::errstr\n";}
   
  if(($debugging == 1) && ($status ne "1"))
  {
    open (DB_FILE, ">$debugDir/db_executeSQLStatement.txt");
    print DB_FILE "$sql\n";
    foreach my $rowHash (@rowArray)
    {
      print DB_FILE "*******************  ROW DATA  ************************\n";
      foreach $field (keys %$rowHash)
      { print DB_FILE "$field = $rowHash->{$field}\n"; }
    }
    print DB_FILE "status: $status";
    close(DB_FILE);
  }

  # If there was a problem and it wasn't just a blank database name
  if (($status ne "1") && ($dbName !~ m/^\s*$/))
  {
    my $envs = "";
    foreach $var (sort(keys(%ENV)))
    {
      $val = $ENV{$var};
      $val =~ s|\n|\\n|g;
      $val =~ s|"|\\"|g;
      $envs .= "${var}=\"${val}\"\n";
    }
    if ($sql eq "") { foreach $arg (@_) { $sql .= "$arg\n"; } }
    print "Content-type: text/html\n\n";
    print "<pre>Testing: Error during SQL call.\nDB: $dbName\nSQL: $sql\nStatus: $status\n ENVS:\n $envs";
  }

  # If at least one of the queries was a select, then there should be values in
  # the array that need to be returned. Otherwise, just return the
  if (@rowArray) { return(\@rowArray); }
  else           { return($status); }
} # end of Sql
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you get a (Blue Screen of Death), your system writes a small file called a minidump. Your first step is to make certain your computer is setup to record memory dumps. Right click My Computer, choose properties. Click on the advanced tab, an…
It is a general practice to get rid of old user profiles on a computer  in a LAN environment. As I have been working with a company in a LAN environment where users move from one place to some other place at times. This will make many user profil…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Suggested Courses
Course of the Month19 days, 14 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question