Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Printing result Perl?

Posted on 2003-12-09
4
Medium Priority
?
741 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 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is becoming increasingly popular to have a front-page slider on a web site. Nearly every TV website,  magazine or online news has one on their site, and even some e-commerce sites have one. Today you can use sliders with Joomla, WordPress or …
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
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 while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
Suggested Courses

618 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