Hello,
I have a database set up on my web site at my ISP server. Connection to
the DB can only be made from the localhost. So I am doing everything
through CGI, Perl. They have the perl DBI and php3 that I can use to
access the database. They provided a script to access the DBI which I
modified to create 'TABLES' to the DB, however it does not work. The
modified script and results returned are provided below. If anyone would
tell me what is amiss in the coding, where I am going wrong, and how to
correct it, I'll appreciate it. Thanks.
-- Joe --
Script ===>
#!/usr/local/bin/perl
###########################################################################
#
# mysql_connect.pl
#
# uses the perl DBI module
#
# replace <database name> <user name> and <password> with the correct
values
#
###########################################################################
#
# set output to unbuffered and print the correct content-type
#
$|=1;
print "Content-Type: text/html\n\n";
#
# we need to use the DBI module
#
use DBI;
#
# replace <database name> <user name> and <password> with the correct
values
# set up the variables to connect to the database
#
$user='domino';
$passwd='*******';
$dbname='XYZABC';
$dsn = "DBI:mysql:$dbname";
#
# this establishes the connection to the database and lets us know if it
worked
#
print "Attempting to connect to the database...<BR>";
$dbh = DBI->connect($dsn, $user, $passwd);
if ( !defined $dbh) {
print "Fatal Error!<BR>Could not connect to database.<BR>";
print "Database: $dbname<BR>";
print "User: $user<BR><HR>";
exit 1;
} else {
print "Successful connection to database: <B>$dsn</B><BR>\n";
}
#
# generate our SQL commmand
# any SQL command can go here (select, create, alter, etc)
# replace <table> with correct value
#
$command_1 ="CREATE TABLE test (name VARCHAR(20), address1 VARCHAR(40),
address2 VARCHAR(40), city VARCHAR(25), state VARCHAR(16), postalcode
VARCHAR(12), country VARCHAR(30)";
print "Command: $command_1<p>\n";
$command_2 ="INSERT INTO test (A. N. Y. Body, Full Circle, NULL,
ThisCity, A2Z, 12345 USA) VALUES (name, address1, address2, city, state,
postalcode, country))";
print "Command: $command_2<p>\n";
$command_3 ="SHOW test";
print "Command: $command_3<p>\n";
$command_4 ="select * from test";
print "Command: $command_4<p>\n";
print "command_1: $command_1<p>\n";
print "command_2: $command_2<p>\n";
print "command_3: $command_3<p>\n";
print "command_4: $command_4<p>\n";
#------------------------------------------------------------
#
# create a statement handles and prepare to execute the SQL statement
#
$sth = $dbh->prepare($command_1);
if ( !defined $sth ) {
print "Can't create Statement Handle Object $dbh->errstr<BR>\n";
exit 1;
}
#
# execute the command_1
#
$rv = $sth->execute;
print "Return Code: <B>$rv</B>".$dbh->errstr." <BR><HR>\n";
#------------------------------------------------------------
#
# create a statement handles and prepare to execute the SQL statement
#
$sth = $dbh->prepare($command_2);
if ( !defined $sth ) {
print "Can't create Statement Handle Object $dbh->errstr<BR>\n";
exit 1;
}
#
# execute the command_2
#
$rv = $sth->execute;
print "Return Code: <B>$rv</B>".$dbh->errstr." <BR><HR>\n";
#------------------------------------------------------------
#
# create a statement handles and prepare to execute the SQL statement
#
$sth = $dbh->prepare($command_3);
if ( !defined $sth ) {
print "Can't create Statement Handle Object $dbh->errstr<BR>\n";
exit 1;
}
#
# execute the command_3
#
$rv = $sth->execute;
print "Return Code: <B>$rv</B>".$dbh->errstr." <BR><HR>\n";
#------------------------------------------------------------
#
# create a statement handles and prepare to execute the SQL statement
#
$sth = $dbh->prepare($command_4);
if ( !defined $sth ) {
print "Can't create Statement Handle Object $dbh->errstr<BR>\n";
exit 1;
}
#
# execute the command_4
#
$rv = $sth->execute;
print "Return Code: <B>$rv</B>".$dbh->errstr." <BR><HR>\n";
#------------------------------------------------------------
$dbh->commit;
$dbh->disconnect;
exit;
__________________________________________________________________
The results returned ====>
Attempting to connect to the database...
Successful connection to database: DBI:mysql:XYZABC
Command: CREATE TABLE test (name VARCHAR(20), address1 VARCHAR(40),
address2 VARCHAR(40), city VARCHAR(25), state VARCHAR(16),
postalcode VARCHAR(12), country VARCHAR(30)
Command: INSERT INTO test (A. N. Y. Body, Full Circle, NULL, ThisCity,
A2Z, 12345 USA) VALUES (name, address1, address2, city, state,
postalcode, country))
Command: SHOW test
Command: select * from test
command_1: CREATE TABLE test (name VARCHAR(20), address1 VARCHAR(40),
address2 VARCHAR(40), city VARCHAR(25), state VARCHAR(16),
postalcode VARCHAR(12), country VARCHAR(30)
command_2: INSERT INTO test (A. N. Y. Body, Full Circle, NULL, ThisCity,
A2Z, 12345 USA) VALUES (name, address1, address2, city, state,
postalcode, country))
command_3: SHOW test
command_4: select * from test
Return Code: You have an error in your SQL syntax near '' at line 1
Return Code: You have an error in your SQL syntax near 'Full Circle,
NULL, ThisCity, A2Z, 12345 USA) VALUES (name, address1, a' at line 1
Return Code: You have an error in your SQL syntax near 'test' at line 1
Return Code: Table 'XYZABC.test' doesn't exist
