Getting started with MySQL

There will be a very simple sample table set up in your database for initial testing. You can add any number of production tables in this database and, at any time, delete the sample. The sample table contains a list of people from Bedrock. The tablename is 'bedrock'. The fields in this table are:

userid	lastname	firstname
1	Flintstone	Fred
2	Flintstone	Wilma
3	Rubble	Barney
4	Rubble	Betty

Three SQL client programs are available:

1. mysql

   • mysql is a command line interface to the SQL server.

   • To connect to the SQL server:

     • Telnet or ssh into your CapeCom FTP/Web Site:
       • Verify your CapeCom username and password. Verify the address of your FTP/Web site , which is in the form vsX.cape.com or vmX.cape.com , where X could be a-z. See Adding/Accessing A Basic FTP/Web Site for further help.
       • Telnet or SSH into your FTP/Web Site using the command "telnet vsX.cape.com" and login with your CapeCom username and password.
     • Connect to the SQL server using the command "mysql -h [servername] -u [username] -p [database]" , then you will be prompted for your MySQL password.

   • Once you are connected to the SQL server:

     • To view the tables present, enter the command "show tables;" .
     • To view the fields present in the "bedrock" table, enter the command "show fields from bedrock;" .
     • To display the contents of the "bedrock" table, enter the command "select * from bedrock;" .
     • To exit the mysql interface, enter the command "exit;" .

   • For further help with the mysql interface, when you are telnetted into your CapeCom FTP/Web Site, enter the command "mysql --help" .

2. mysqlshow

   • mysqlshow is a command-line program which will display the form and content of any table in your database.

   • To utilize mysqlshow:

     • Telnet or ssh into your CapeCom FTP/Web Site:
       • Verify your CapeCom username and password. Verify the address of your FTP/Web site , which is in the form vsX.cape.com or vmX.cape.com , where X could be a-z. See Adding/Accessing A Basic FTP/Web Site for further help.
       • Telnet or SSH into your FTP/Web Site using the command "telnet vsX.cape.com" and login with your CapeCom username and password.
     • To display a list of the tables present, enter the command "mysqlshow -h [servername] -u [username] -p [database]" .
     • To display info on the fields present in the "bedrock" tables's fields, enter the command mysqlshow -h [servername] -u [username] -p [database] bedrock" .

   • For further help with mysqlshow, enter the command "mysqlshow --help" .

3. dbish

   • dbish is the DBI SHell interface. DBI is an Application Programming Interface (API) for use with Perl.

   • To connect to the DBI Shell interface:

     • Telnet or ssh into your CapeCom FTP/Web Site:
       • Verify your CapeCom username and password. Verify the address of your FTP/Web site , which is in the form vsX.cape.com or vmX.cape.com , where X could be a-z. See Adding/Accessing A Basic FTP/Web Site for further help.
       • Telnet or SSH into your FTP/Web Site using the command "telnet vsX.cape.com" and login with your CapeCom username and password.
     • Connect to the interface using the command "dbish dbi:mysql:[database]:[servername] [username] [password]" .

   • Once you are connected to the DBI Shell interface:

     • To display a list of the tables present, enter the command "show tables" , then enter the command "/go" .
     • To display the contents of the "bedrock" table, enter the command "select * from bedrock" then enter the command "/go".
     • To exit the interface, enter the command "/quit"

   • For help on usage, head to http://man.cape.com/ and enter "dbish" .

Example connection to MySQL using PHP:

function getDBConnection() {
	static $cnx;
	if (is_resource($cnx)) {
		return $cnx;
	} else {
		$servername = yourmysqlservername;
		$username = yourmysqlusername;
		$password = yourmysqlpassword;
		$dbName = yourmysqldatabase;
		/* make connection to database */
		$cnx = MYSQL_CONNECT($servername, $username, $password) OR DIE ("Unable to connect to database");
		@mysql_select_db($dbName, $cnx) or die ("Unable to select database");
		return $cnx;
	}
}

$cnx = getDBConnection();
$query = "SELECT * FROM bedrock";
$result = MYSQL_QUERY($query,$cnx);
IF ($result) {
	WHILE ($row = mysql_fetch_assoc ($result)) :
		print "$row[userid] $row[firstname] $row[lastname]";
	ENDWHILE;
}

For more help using MySQL in PHP, see http://us3.php.net/manual/en/ref.mysql.php .

Example connection to MySQL using Perl:

use DBI;
my $dbh = DBI->connect("DBI:mysql:yourmysqldatabase:yourmysqlservername",yourmysqlusername,yourmysqlpassword) or die("can't connect to db");
my $st = qq{SELECT * FROM bedrock};
my $sth = $dbh->prepare($st);
$sth->execute or warn $dbh->errstr ;
while(@record = $sth->fetchrow_array) {
    print "$record[0] $record[1] $record[2]";
}

For more help using MySQL in Perl, see http://www.perl.com/pub/a/1999/10/DBI.html .

Related Pages

Related Links

Printer Friendly Version