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 have to do everything
through CGI. 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 did not work. The script and modified
lines (indicated by a '#' at the end of the lines) are provided below.
If anyone would tell me what is amiss in the coding, 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='alchemy';
$passwd='********';
$dbname='URW';
$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 ="CREATE TABLE test_0 (ID INTEGER(6), name VARCHAR(20),
address1 VARCHAR(40), address2 VARCHAR(40), city VARCHAR(25), state
VARCHAR(16), postalcode VARCHAR(12), country
VARCHAR(30)"; #
print "Command: $command<p>\n"; #
$command ="CREATE TABLE test_1 (ID INTEGER(6), sex CHAR(1), birth date,
weight SMALLINT(3), remarks VARCHAR(255)"; #
print "Command: $command<p>\n"; #
$command ="SHOW TABLES"; #
print "Command: $command<p>\n"; #
$command ="DESCRIBE test_0"; #
print "Command: $command<p>\n"; #
$command ="select * from test_1"; #
print "Command: $command<p>\n"; #
#
# create a statement handle and prepare to execute the SQL statement
#
$sth = $dbh->prepare($command);
if ( !defined $sth ) {
print "Can't create Statement Handle Object $dbh->errstr<BR>\n";
exit 1;
}
#
# execute the command
#
$rv = $sth->execute;
print "Return Code: <B>$rv</B>".$dbh->errstr." <BR><HR>\n";
exit;
-----------------------------------------------------------------------------------------------------------
Returned message in Netscape
Attempting to connect to the database...
Successful connection to database: DBI:mysql:URW
Command: CREATE TABLE test_0 (ID INTEGER(6), name VARCHAR(20), address1
VARCHAR(40), address2 VARCHAR(40), city VARCHAR(25), state
VARCHAR(16), postalcode VARCHAR(12), country VARCHAR(30)
Command: CREATE TABLE test_1 (ID INTEGER(6), sex CHAR(1), birth date,
weight SMALLINT(3), remarks VARCHAR(255)
Command: SHOW TABLES
Command: DESCRIBE test_0
Command: select * from test_1
Return Code: Table 'URW'.test_1' doesn't exist
