Solved

Printing result Perl?

Posted on 2003-12-09
4
727 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 3

Accepted Solution

by:
mrh30 earned 25 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 25 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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

Making a simple AJAX shopping cart Couple years ago I made my first shopping cart, I used iframe and JavaScript, it was very good at that time, there were no sessions or AJAX, I used cookies on clients machine. Today we have more advanced techno…
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
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 …

729 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