#!/usr/local/bin/perl -w

# ^NOTE --- I had to change the perl path on sorrel above
#    so this would work!!!

#######################################################
# lect14_connect3_mysql
#
# playing with some more of DBI module,
# but now connecting to MySQL instead
#
# by Sharon Tuttle
#
# last modified: 11-30-04
#######################################################

use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser warningsToBrowser carpout);

use DBI;

warningsToBrowser(1);  # activate warnings

# PRINT a form for user to react to

print header, "\n";

print start_html(-title=>'lect14_connect3_mysql page'), "\n";

print h1('lect14_connect3_mysql page'), "\n";

print start_form, "\n",
      br, br, "\n",

      "MySQL username: \n", 
      textfield(-name=>'mysql_username',
                -size=>15,
                -maxlength=>15), "\n",
      
      br, br, "\n",

      "MySQL password: \n",
      password_field(-name=>'mysql_password',
                     -size=>10,
                     -maxlength=>10), "\n",

      br, br, "\n",

      "Which table's contents should be displayed: \n",
      textfield(-name=>'table_name',
                -size=>30,
                -maxlength=>30), "\n",

      br, br, "\n",

      checkbox(-name=>'show_tables',
               -label=>'Show names of all tables',
               -value=>'yes'), "\n",

      br, br, "\n",

      submit(-value=>'Submit'), "\n",
      endform, "\n";

print hr, "\n";

# if user has hit submit, try to connect to the database and
#    show the specified table's contents

if (param())
{
    # do not seem to need to set any environment variables
    #    for MySQL...

    # try to connect to MySQL database named mysql_username 
    #    in the account mysql_username with the password
    #    mysql_password, on sorrel
    # (and remember --- what you connect, REMEMBER TO DISCONNECT!!!

    my $username = param('mysql_username')
        or die "Must enter a MySQL username!\n";

    my $password = param('mysql_password')
        or die "Must enter a MySQL password!\n";        

    my $db_handle = DBI->connect("dbi:mysql:database=$username;host=www.humboldt.edu",
                    "$username", "$password")
        || die "Database connection not made: $DBI::errstr";

    # did user ask for the contents of a particular table?

    if (param('table_name'))
    {
        my $table = param('table_name');

        my $sql_cmd = "select * from $table";

        # feed the SQL statement to the database handle
        #    with a statement handle; first, need to create
        #    this statement handle object:

        my $stmt_handle = $db_handle->prepare( $sql_cmd )
            or die $db_handle->errstr . "\n";

        # now, execute the SQL command using the $stmt_handle object's
        #    execute() method

        $stmt_handle->execute();

        # how can I get the results? Well, one option is to call the
        #    fetchrow_array() method of the $stmt_handle object...

        my @row_values;

        # quick'n'sleazy table output --- yes, an HTML table would
        #    be slicker...
        print hr, "\n",
              "<PRE>\n",
              br, "\n";

        # grab the column names in this resulting relation/table

        my $num_cols = $stmt_handle->{NUM_OF_FIELDS};
        print "result has $num_cols columns\n\n";

        while (@row_values = $stmt_handle->fetchrow_array())
        {
            foreach (@row_values)
            {
                # because a NULL database cell value is seen as
                #    as undefined in this array;
                if (defined($_))
                {
                    printf "%10s", $_;
                }
                else
                {
                    print "      NULL";
                }
            }
            print "\n";
        }
        print "</PRE>\n";

        # IF you are not reading all of the rows of the executed statement,
        #    I am told that is is good to call finish for your statement
        #    handle BEFORE disconnecting the database handle.
        # SO, consider this a course style standard, too.
        # 
        # (I'm just playing it safe here...)

        $stmt_handle->finish;

        print hr, "\n";
    }

    # did user ask for the names of all tables?
    if (param('show_tables') eq "yes")
    {
        # need to change THIS to use MySQL metadata instead...

        my $stmt_handle = $db_handle->prepare( 'show tables' )
            or die $db_handle->errstr . "\n";

        $stmt_handle->execute();

        my $next_tbl;

        while ($next_tbl = $stmt_handle->fetchrow_array())
	{
            print "$next_tbl", br, "\n";
	}

        $stmt_handle->finish;
        print hr, "\n";
    }

    # DON'T FORGET THIS! Not only is it POOR STYLE to do so, but
    #   could cause REAL Oracle performance problems if you leave
    #   it off!!!!!!!!!!!!!

    $db_handle->disconnect;

}

print end_html, "\n";

# end of lect14_connect3_mysql.pl