SUPPORT

3. Can I use a database with my shared Linux hosting account?

 

If your have a Linux account with Interland that is a Shared 2 (TrueAdvantage) Plan or higher, you have access to a MySQL Database server. To request a MySQL database setup, Contact Granite support.



By default, databases are setup so administration MUST be done from your database server account. Only minimum privileges are given for all other hosts. Meaning, unless otherwise specified, you can only create, delete, and alter tables from the database host itself.

Database access is with the Perl DBI::DBD modules. MyODBC is not available on Windows NT/2000 servers at this time for access to a MySQL database.

Once you have your database up and running, your going to need to know how to connect to it. Below is a small sample of code that will connect to a MySQL database, and select values from a field based on an ID number.

#!/usr/bin/perl
use DBI;
$dsn = "DBI:mysql:database=databasename";
$dbh=DBI->connect($dsn, "username", "password");
$sth = $dbh->prepare("SELECT * FROM table where ID=\'5\' ORDER BY date");
$sth->execute;
while( (@results = $sth->fetchrow) != NULL) {
if ($results[2] > 500) {
print "$results[0] - $results[1] - $results[2] - $results[4] - $results[6]\n";
}
}

Keep in mind that this code has not been designed for use with a Web server, and is only database access code. The CGI scripting part of it is entirely up to you. This code is also designed for a local MySQL server. In other words, the server is running on the same machine as the above script. If you are trying to access a server remotely, then you would need to alter your "dsn" line to look like the following:

$dsn = "DBI:mysql:database=databasename;host=serverhostnameoripaddress";
Further documentation can be found at www.mysql.net and www.cpan.org.


Perl Samples:
Printing Environmental Variables to the Web Browser

#!/usr/bin/perl

print "Content-type: text/html\n\n";

print "\n";

foreach $key (keys(%ENV)) {

print "$key = $ENV{$key}
\n";

}

print "\n";


Text based Counter

#!/usr/bin/perl


print "Content-type: text/html\n\n";

$counterfile = 'visitors.txt';

if (!( -e "$counterfile")) {

$counterbase = 0;

}

else {

open(CNT, "$counterfile") || die("Error opening counter file for read.\n");

$counterbase = ;

close(CNT);

}

$counterbase++;

open(CNT, ">$counterfile") || die("Could not open counter file for write.\n");

print CNT $counterbase;

close(CNT);

print "You are visitor number $counterbase.";

Connecting To the Server:
If you are located on a remote machine such as a web server with telnet access then the connection string you will be typing is different than the connection on the local server.


mysql -h -u -p


This connection string specifies the following:

mysql -- this is the program that you use to connect to the server, this program allows you to connect to the mysql servers.
-h -- this flag specifies the hostname or IP of the server that you are trying to connect to

-u -- this specifies the username

-p -- this flag says that the username that you specified with the -u option will be using a password, the password is not specified here but after pressing enter it will prompt you for a password.


The last entry is the database name parameter. No special flag is needed before it.
The connection string for connecting when logged into the mysql box directly is very similar to the string for connecting remotely. You do not have to specify the hostname of the server when connecting locally. The default goes to the local machine. The connection string is then:


mysql -u -p
After pressing enter you will receive a prompt similar to this:


Enter password:
If you enter the correct password then you will receive the following prompt(or something similar):


Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2980597 to server version: 3.22.30

Type 'help' for help.

mysql>
If you do not enter the correct password then you will receive the following error:


ERROR 1045: Access denied for user: '@localhost' (Using password: YES)
Replace username with the username that you specified with the -u option above.
Type "exit" or "quit" to exit.


Other Command-line Tools:
mysqladmin -- this program can be used to administer the mysql server. For the most part our customers do not need access or need to use this program because they do not have full permissions to the database server, just full permissions to their database.
mysqlimport -- this program is used to import data from a text file directly into the database specified on the command-line. The syntax for using this command:
mysqlimport -u -p
The option is the name or the file that contains the data that you want to import into parameter.

**When using this function directly on the server customer will mostly likely receive a permission denied error. This is because "file" permissions needed to be granted on the mysql server to make this work(more on permissions later). This is not granted by default for security reasons and will not be granted to customers unless they can prove a serious need for it. Users who have telnet access to their web server will be able to use this function without error.


mysqldump -- this command will allow you to back up your database to the location specified on the command line. The syntax to use this is:

mysqldump -u -p >>
This will dump the contents of your database to the file name specified as the last option. The format will be nothing more than a bunch of sql statements that you can use to recreate the tables and data within the database.

There are more command-line programs and options that can be used but this gives a general idea about what to use and what they mean.


Mysql Client Utilities (non-command-line):
Php: This is a web scripting language that is compiled on all Feature Plus and above servers that can be used to connect and administer databases. A sample script will be shown at the end.
Perl: Perl is perl. The modules necessary to connect and use the mysql services are the DBI module and DBD::mysql modules. Both of these modules are installed on all servers without question. Samples also at the end.
C Api: Customers can write mysql programs that utilize the C api functions. The important directories necessary for customers to use the api are:

/usr/local/lib/mysql -- this directory contains the c library functions
/usr/local/include/mysql -- this directory contains the header files to be included in all programs.

Connection Samples

These samples use the following information:


DatabaseServerName: unixdatabaseserver
DatabaseName: test
DatabaseUserName: test
DatabasePassword: test2

PHP:
#!/usr/local/bin/php

if($mysql_link){
print "Connected Successfully";
}
else{
print "Did not connect Successfully";
}
?>
- Updated: March 13, 2001

<< PREVIOUS PAGE : HOME