PROFESSORSPARKIE
Instructor
Following is a question I sent to mysql & was told to post here.
I am writing a sample program for my students. They have written a script-text file and executed it in a local PC and after it works there, I want them to run it against an online database with a perl program. The program works great, it reads the script file one SQL statement at a time and executes it. I have verified the table creations and inserts. I have added code to give feed back to the web screen but it does not recognize fetchrow_array, fetchrow_arrayref, fetchrow_hashref or fetchall_arrayref. I got this part of the code from a good reference book but I keep getting a message like the one at the end of this listing. I need help.
1 #!/usr/bin/perl -wT
2 # use strict;
3 use diagnostics;
4 use CGI;
5 use CGI ':standard';
6 use CGI::Carp qw(fatalsToBrowser); #use only for server testing
7 use DBI();
8
9 my $filename = 'MYSQL-PERL-101-SCRIPT-FILE.SCR';
10 my $row = " ";
11 my @row;
12 my $query = " ";
13 my $numrows = " ";
14 my $a = " ";
15 my $i = 0;
16 #######################################################
17 # PROGRAM SAVED AS "SQL-PERL-101-COMMAND-CODE.PL #
18 #######################################################
19 #******************************************************
20 # THE FOLLOWING PRINT STATEMENT MUST BE SENT WITH A *
21 # DOUBLE LINE FEED TO INFORM THE BROWSER THAT THE *
22 # cgi DATA IS IN THE FORM OF A "HTML WEB PAGE *
23 # - IN TEXT FORMAT". *
24 #******************************************************
25 # THE NEXT TWO LINES ARE TO DEFINE THE STANDARD *
26 # HEADER & BODY OF A WEB PAGE. *
27 #******************************************************
28 print "Content-type: text/html\n\n";
29 print "\n\n This is the listing for file ";
30 print "$filename"."<BR><BR>";
31 print "<HTML>\n<HEAD>\n<TITLE>Here comes the listing";
32 print " of $filename .</TITLE>\n</HEAD>\n";
33 print "<BODY><BR>\n";
34 #******************************************************
35 # THIS PROGRAM EXECUTES SPECIFIED FILE SQL SCRIPT AND *
36 # TO THE CLIENT THE RESULTS USING cgi PROGRAM *
37 # COMMUNICATION TECHNIQUES. *
38 #******************************************************
39 # THE FOLLOWING STATEMENT IDENTIFY THE NAME OF THE *
40 # FILE TO OPEN & PRINT. *
41 #******************************************************
42
43 if (open(Myfile, "<$filename"))
44 {
45 # run this listing if open succeeds
46 print 'FILE IS OPEN <br><br>';
47 }
48 else
49 {
50 print "Cannot open $filename !\n";
51 print STDERR "*** DEBUG *** \@ MYFILE = $filename \n";
52 exit 1;
53 }
54
55 #You will use Perl and the DBI Perl Module
56 # to access your mySQL database.
57 # See below for a commented example:
58 # Connect To Database
59 # * The DBI interface to MySQL uses the method "connect" to make a
60 # * connection to the database. It takes as it's first argument
61 # * the string "DBI:mysql:database:hostname", where database is equal
62 # * to the name of your database, and hostname to the server that it's
63 # * located on. The second and third arguments, respectively, should
64 # * be your account username and password. The connection is assigned.
65 # * to a variable that is used by most other methods in the module.
66
67 my $database = "xxxxxxxxx"; # Your database name
68 my $hostname = "localhost"; # Your database hostname
69 my $username = "zzzzzzzzzzz"; # Your username
70 my $password = "??????????"; # Your database password
71
72 # The following connects to the database server.
73 # It is equivalent to a file open.
74 my $db = DBI->connect("DBI:mysql:$database:$hostname", $username, $password);
75
76 # Next we process each SQL command they will be sent
77 # to the server for execution.
78
79 while(<Myfile>)
80 {
81 $i = o;
82 # print "start".$_."end \<BR\>\n";
83 $a = $_ ;
84 # print "rec read\<br\>";
85 print $a."\<BR\>\n";
86 # Execute a Query
87 # * executing a query is done in two steps. First,
88 # * the query is setup using the "prepare" method.
89 # * this requires the use of the variable used to
90 # * initiate the connection. Second, the "execute"
91 # * method is called, as shown below.
92 $query = ($db->prepare($a)) || (die "Error : $DBI::errstr");
93 $query->execute;
94
95 # How many rows in result?
96 # * the "rows" method using the variable name the
97 # * query was executed under returns the number
98 # * of rows in the result.
99 $numrows = $query->rows;
100
101 # Display Results
102 # * the fetchrow_array method executed on the
103 # * query returns the first row as an array.
104 # * subsequent calls return the other rows in
105 # * sequence. It returns zero when all rows have
106 # * been retrieved.
107 while (@row = $db->fetchall_arrayref())
108 {
109 print $row[1]." ".$row[1]."\<br\> \n";
110 }
111 }
112
113 # Cleaning Up
114 # * with the DBI module, it is a good idea to clean up by
115 # * explicitly ending all queries with the "finish" method,
116 # * and all connections with the "disconnect" method.
117
118 $query->finish;
119
120 $db->disconnect;
121 close(Myfile);
122 print "<br><BR>";
123 print "\n\n***** End of listing. *****\n";
124 print "</body></html>";
125 exit 0;
My web page feedback:
This is the listing for file MYSQL-PERL-101-SCRIPT-FILE.SCR
=================================================
FILE IS OPEN
USE wsparks_sitemagixprod;
Software error:
Can't locate object method "fetchall_arrayref" via package "DBI::db"
at MYSQL-PERL-101-COMMAND-CODE.PL line 107, <Myfile> line 1.
For help, please send mail to the webmaster (webmaster@professorsparks.com),
giving this error message and the time and date of the error.
===================================================
SINCE THIS IS MY OWN SITE I AM THE WEB MASTER(????).
My problem is that the terms [fetchrow_array, fetchrow_arrayref, f
etchrow_hashref or fetchall_arrayref] of DBI module are undefined
in package "DBI::db" .
Two different books show these terms to be valid. I am miss- representing
them or don't understand something. The program works great, creating the
table and inserting the records, but after I add the few lines to look at
the Mysql standard output to the screen/printer to feed back to the browser
I get the errors.
Any suggestions on how to fix this?
Computer thought: I teach a lot of programming so I can learn. You can never learn it all.
I am writing a sample program for my students. They have written a script-text file and executed it in a local PC and after it works there, I want them to run it against an online database with a perl program. The program works great, it reads the script file one SQL statement at a time and executes it. I have verified the table creations and inserts. I have added code to give feed back to the web screen but it does not recognize fetchrow_array, fetchrow_arrayref, fetchrow_hashref or fetchall_arrayref. I got this part of the code from a good reference book but I keep getting a message like the one at the end of this listing. I need help.
1 #!/usr/bin/perl -wT
2 # use strict;
3 use diagnostics;
4 use CGI;
5 use CGI ':standard';
6 use CGI::Carp qw(fatalsToBrowser); #use only for server testing
7 use DBI();
8
9 my $filename = 'MYSQL-PERL-101-SCRIPT-FILE.SCR';
10 my $row = " ";
11 my @row;
12 my $query = " ";
13 my $numrows = " ";
14 my $a = " ";
15 my $i = 0;
16 #######################################################
17 # PROGRAM SAVED AS "SQL-PERL-101-COMMAND-CODE.PL #
18 #######################################################
19 #******************************************************
20 # THE FOLLOWING PRINT STATEMENT MUST BE SENT WITH A *
21 # DOUBLE LINE FEED TO INFORM THE BROWSER THAT THE *
22 # cgi DATA IS IN THE FORM OF A "HTML WEB PAGE *
23 # - IN TEXT FORMAT". *
24 #******************************************************
25 # THE NEXT TWO LINES ARE TO DEFINE THE STANDARD *
26 # HEADER & BODY OF A WEB PAGE. *
27 #******************************************************
28 print "Content-type: text/html\n\n";
29 print "\n\n This is the listing for file ";
30 print "$filename"."<BR><BR>";
31 print "<HTML>\n<HEAD>\n<TITLE>Here comes the listing";
32 print " of $filename .</TITLE>\n</HEAD>\n";
33 print "<BODY><BR>\n";
34 #******************************************************
35 # THIS PROGRAM EXECUTES SPECIFIED FILE SQL SCRIPT AND *
36 # TO THE CLIENT THE RESULTS USING cgi PROGRAM *
37 # COMMUNICATION TECHNIQUES. *
38 #******************************************************
39 # THE FOLLOWING STATEMENT IDENTIFY THE NAME OF THE *
40 # FILE TO OPEN & PRINT. *
41 #******************************************************
42
43 if (open(Myfile, "<$filename"))
44 {
45 # run this listing if open succeeds
46 print 'FILE IS OPEN <br><br>';
47 }
48 else
49 {
50 print "Cannot open $filename !\n";
51 print STDERR "*** DEBUG *** \@ MYFILE = $filename \n";
52 exit 1;
53 }
54
55 #You will use Perl and the DBI Perl Module
56 # to access your mySQL database.
57 # See below for a commented example:
58 # Connect To Database
59 # * The DBI interface to MySQL uses the method "connect" to make a
60 # * connection to the database. It takes as it's first argument
61 # * the string "DBI:mysql:database:hostname", where database is equal
62 # * to the name of your database, and hostname to the server that it's
63 # * located on. The second and third arguments, respectively, should
64 # * be your account username and password. The connection is assigned.
65 # * to a variable that is used by most other methods in the module.
66
67 my $database = "xxxxxxxxx"; # Your database name
68 my $hostname = "localhost"; # Your database hostname
69 my $username = "zzzzzzzzzzz"; # Your username
70 my $password = "??????????"; # Your database password
71
72 # The following connects to the database server.
73 # It is equivalent to a file open.
74 my $db = DBI->connect("DBI:mysql:$database:$hostname", $username, $password);
75
76 # Next we process each SQL command they will be sent
77 # to the server for execution.
78
79 while(<Myfile>)
80 {
81 $i = o;
82 # print "start".$_."end \<BR\>\n";
83 $a = $_ ;
84 # print "rec read\<br\>";
85 print $a."\<BR\>\n";
86 # Execute a Query
87 # * executing a query is done in two steps. First,
88 # * the query is setup using the "prepare" method.
89 # * this requires the use of the variable used to
90 # * initiate the connection. Second, the "execute"
91 # * method is called, as shown below.
92 $query = ($db->prepare($a)) || (die "Error : $DBI::errstr");
93 $query->execute;
94
95 # How many rows in result?
96 # * the "rows" method using the variable name the
97 # * query was executed under returns the number
98 # * of rows in the result.
99 $numrows = $query->rows;
100
101 # Display Results
102 # * the fetchrow_array method executed on the
103 # * query returns the first row as an array.
104 # * subsequent calls return the other rows in
105 # * sequence. It returns zero when all rows have
106 # * been retrieved.
107 while (@row = $db->fetchall_arrayref())
108 {
109 print $row[1]." ".$row[1]."\<br\> \n";
110 }
111 }
112
113 # Cleaning Up
114 # * with the DBI module, it is a good idea to clean up by
115 # * explicitly ending all queries with the "finish" method,
116 # * and all connections with the "disconnect" method.
117
118 $query->finish;
119
120 $db->disconnect;
121 close(Myfile);
122 print "<br><BR>";
123 print "\n\n***** End of listing. *****\n";
124 print "</body></html>";
125 exit 0;
My web page feedback:
This is the listing for file MYSQL-PERL-101-SCRIPT-FILE.SCR
=================================================
FILE IS OPEN
USE wsparks_sitemagixprod;
Software error:
Can't locate object method "fetchall_arrayref" via package "DBI::db"
at MYSQL-PERL-101-COMMAND-CODE.PL line 107, <Myfile> line 1.
For help, please send mail to the webmaster (webmaster@professorsparks.com),
giving this error message and the time and date of the error.
===================================================
SINCE THIS IS MY OWN SITE I AM THE WEB MASTER(????).
My problem is that the terms [fetchrow_array, fetchrow_arrayref, f
etchrow_hashref or fetchall_arrayref] of DBI module are undefined
in package "DBI::db" .
Two different books show these terms to be valid. I am miss- representing
them or don't understand something. The program works great, creating the
table and inserting the records, but after I add the few lines to look at
the Mysql standard output to the screen/printer to feed back to the browser
I get the errors.
Any suggestions on how to fix this?
Computer thought: I teach a lot of programming so I can learn. You can never learn it all.
![[wiggle]](/data/assets/smilies/wiggle.gif "[wiggle] [wiggle]")
![[noevil]](/data/assets/smilies/noevil.gif "[noevil] [noevil]")