You need to prepare and execute all of the SQL statements.
You're just assigning SQL strings to a variable and printing
them out -- you need to actually execute them. Only the
last statement, "select * from test_1," is actually being
run.
Read through the perl DBI manual:
<http://search.cpan.org/doc/TIMB/DBI-1.19/DBI.pm>
Consider setting the RaiseError attribute so you can
skip the error checking code.
On Sat, Jul 21, 2001 at 03:58:25AM -0700, Joseph Maxwell wrote:
> 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
--
Maurice Aubrey <[EMAIL PROTECTED]>
MySQL 3.22.32: up 2.1 days, processed 147,097,454 queries (801/sec. avg)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php