Positive Internet

If you have Perl scripts which use the standard DBI::DBD methods to access MySQL databases, they will work, as we have the modules installed on our server.

If you are developing your own scripts, you might prefer to use our SQL module, created especially for our customers. Under the bonnet, it makes use of the DBI::DBD modules, but takes away some of the trog work of getting results from queries and executing statements.

At the end of the handbook is a description of all the module methods and how they may be used.

Back to top

Firstly, at the top of your script, you must have the directive

use SQL; 

Which tells Perl to make use of Positive's SQL module, which is installed, in places Perl knows to look, on all our servers.

You must then create a database object. It is through this object that you will enact your database transactions. Here is an example of creating such an object:

$DB=new SQL ('fred:mysql.positive-internet.com','fred','topsecret'); 

(This is in order of database:host, username, password)

This creates the $DB object. All further database transactions will take place through it. If you have provided the wrong database, host, username or password, an error will occur.

If your database was created prior to Jan 1st 2004 you should leave out the host name or set it to localhost otherwise for databases created after Jan 1st 2004 it must be present.

Back to top

To issue an SQL insert, delete, change or any other statement where you're acting on a table rather than querying it, use the "do" method, thus:

$DB->do("update products set cost='2.40' where item='fish'");

The argument of the "do" statement is the SQL statement that's meant to be done! Remember to enclose the whole thing in double quotes. To make things tidier, you might want to store the statement as a variable first, like this:

$statement=" update products set cost='2.40' where item='fish' "; 
    $DB->do($statement); 

Back to top

There are various methods available for querying your database, depending on the sort of data you need returned.

1) Returning the value of just one column from one row

If you require just one "cell" of the SQL table, use the "item" method. For example, if you wished simply to know the colour of a product with the unique ID "z123", and didn't need any other columns from the returned row, the statement might look something like this:

$colour=$DB->item(" select colour from products where ID='z123' "); 

Assuming that the product with the unique ID z123 exists in your table and that the colour column has been set for it, the returned $colour scalar variable will contain a simple string of the value. For example

print $colour; 

would result in the word "orange" being output (assuming that was what was returned!)

So, you use the item subroutine when you know only one row will be returned, and from that row you only wish to see the value of one column.

2) Returning all the column values from one row

The "item" method above returns the value of one column from one row. If your query is still meant to return just one row, but you wish use values from more than one column on that row, use the "line" method. All the columns from the returned row will be returned as a hash reference. If the term "hash reference" is partial or total gobbledegook to you, either go back to Perl school or discern its meaning from the examples below ;-)

The query used as an example when discussing the "item" method (above) returned the value of just one column from one row in the SQL table. The example here of the "line" method in use is querying the same table, and indeed the same row, but returns the value of all the columns and stores them as a hash reference called "$row".

$row=line->$DB->(" select * from products where ID='z123' "); 

If you tried to print the $row variable, you'd get a technically detailed but practically useless output - remember, it's a hash reference, and needs to be dereferenced. Again, don't worry if that's meaningless - just learn the following by rote:

To access the value of a column from a row returned by the "line" method, do the following:

$value=$row->{'column_name'};

where column_name is the name of the column whose value you wish to access from the $row hash-reference. It doesn't have to be called $row, by the way - the variable you use to receive the returned items from the line method may have any valid variable name. Assuming we've slurped the values into the $row variable, the following would print out a string which included the values of the returned colour and price columns.

print "The colour was $row->{colour}, priced £$row->{price}\n";

Assuming that z123's colour column is set to orange and its price column contains the value 48, the statement above would display the following:

The colour was orange, priced £48

3) Returning any number of rows and their columns at once

If your query may return more than one row from the table, use the "query" method. It returns an array-reference of all the rows returned. And each element of this referenced array represents a row, as a hash reference - in the same way that the line method represents individual rows above.

So, the "item" method returns a cell as a scalar variable, the "line" method returns a row as a hash reference variable and the "query" method returns a table as a referenced array of hash-reference rows.

You might be confused now. Don't worry - even if you're not complete new to Perl references, it can sometimes be hard to get your head around references to references - just look at the examples below and, by following their described actions, a little spark of intuition should ignite the eventual flames of understanding.

Let's assume that there are two orange products listed in your database: the product with the ID z123 and another with x579. This is how we'd select them:

$table=$DB->query(" select * from products where colour='orange' ");

The $table variable will be an array reference to the two returned rows. These rows will be hash-references to all the columns returned for that row. So you have to dereference two levels - first to "drill down" to the correct row (numbered from zero onwards in the order they were returned) and then to row and then to the required column value.

So, for example, to see the value of the price column in the second returned row (remember that Perl begins numbering array elements at zero, not one), you would type

print $table->[1]->{'price'};

If you need to learn it by rote, refer back to this if you get muddled: To get to your data, have the table variable, then the row-number in square brackets, then the column-label whose value you require in squiggly braces - all separated by "arrows" (produced by a dash and a greater-than sign).

Because the table variable is an array reference, it can be dereferenced into an array by placing the usual Perl @ sign in front of it - but remember to include the dollar sign too! This way, it can be used in foreach loops and the like. If you squint, you should be able to see what's going on here:

foreach $row (@$table) {
$code=$row->{ID};
print "An $row->{colour} product has the ID of $code\n";
}

Assuming the table has been set up as in all the above examples, this is what output you will get:

An orange product has the ID of z123
An orange product has the ID of x579

This is what has led to the output:

1) A query has returned a table of values, encapsulated in an array reference called $table.

$table=$DB->query(" select * from products where colour='orange' ");

2) We cycle through each row of that table: we access the array to which the $table variable references by putting an '@' sign before it, and then using that "dereferenced" array in a foreach loop:

foreach $row (@$table) {

3) The foreach loop is extracting each element of the table in turn and storing it in the $row variable. The value is a hash reference, representing a row of returned columns from the table. We store the value of the 'ID' column in a normal scalar variable, which we name $code:

$code=$row->{ID};

4) We print the output, including with the $code value set up above and the value of the "colour" column, which we "dereference" directly without storing its string in a scalar variable first:

print "An $row->{colour} product has the ID of $code\n";

Back to top

The following case study represents a typical use of the module: it presents a simple fill-in form for people to fill in with their web-browsers. The information is then stored in a database. The second example reports data from the table back to the web browser. The methods described above are used, as well as others - so following this case study and, indeed, actually replicating its steps, may be worthwhile if you are unsure about the way things fit together.

In the study, we use a hypothetical customer with the username jbloggs, who has a database with the same name and a password of "happy5". His website is www.jbloggs.co.uk.

Substitute as appropriate, of course, if you are replicating the study!

1) Creating the table

There are several ways to create a mysql table. We'll use a simple method from the command-line.

a. Joe uses ssh to connect to www.jbloggs.co.uk and enters his username and password.

b. At the prompt, he types
mysql -p jbloggs
and presses return. He types in his password and presses return again.

c. He is in the mysql "shell". He wishes to create a table, so types the following (he presses return as shown in the linebreaks here:

create table users (
firstname varchar(50) not null,
lastname varchar(50) not null,
age integer,
sex enum('male','female'),
date_entered date not null,
hostname varchar(60) not null,

index firstname(firstname),
index lastname(lastname),
index date_entered(date_entered)
);

Note the semicolon after the final parenthesis to tell mysql the statement, which has been split over several lines for clarity, is ready to be acted upon. The firstname, lastname and date_entered columns have been indexed to speed up searches with them.

d. He types exit and presses return.

Uploading the fill-in HTML form:

e. Joe has prepared the following HTML form, which he uploads into his public_html directory with FTP and calls userform.htm:

<HTML>
<HEAD><TITLE>User details</TITLE></HEAD>

<BODY>
<FORM METHOD="post" ACTION="insertuser.cgi">

Please type your firstname <INPUT TYPE="text" NAME="firstname"> <BR>

Please type your lastname <INPUT TYPE="text" NAME="lastname"> <BR>

Please type your age <INPUT TYPE="text" NAME="age" SIZE="3"> <BR>

Please select your sex
<SELECT NAME="sex">
<OPTION VALUE="male"> Male </OPTION>
<OPTION VALUE="female">Female</OPTION>
</SELECT>

<P><INPUT TYPE="submit" NAME="button" VALUE="Do it!">

</FORM>
</BODY>

</HTML>

So Joe's HTML page sends firstname, lastname, age and sex to the insertuser.cgi script (which he has not yet uploaded - see below).

f. Joe writes the following CGI script and uploads it to the same directory as userform.cgi. Try it yourself - read through the comments and you should get a pretty good idea of its operation. The comments make it look longer (and paradoxically, more complicated) than it actually is - if you remove the comments, you'll notice it is a strikingly short script for what it does.

#!/usr/bin/perl
# *** First, we tell the script we wish to use the SQL and standard CGI
# *** modules, and print the CGI header.

use SQL;
use CGI;

$CGI=new CGI;
print $CGI->header();

# *** Set up the database object ***
# *** The database name is jbloggs,as is the username.
# *** Finally, the password is sent as happy5

$DB=new SQL('jbloggs:mysql.positive-internet.com', 'jbloggs', 'happy5');

# *** Set up an array containing then name of all the fields which
# *** the user MUST fill in before we can allow him to
# *** continue

@required_fields=('lastname', 'firstname');

# *** Use the "obligatory" method to return the names
# *** of any fields whose values have not been filled in.
# *** The first argument is the CGI object. The next is
# *** the array of required fields constructed above.
# *** The method returns an array-reference to the names
# *** of any fields that should have been filled in but have not been.

$missing_fields=$DB->obligatory($CGI, @required_fields);

# *** Output the start of the HTML page to the browser

print <<EndBlock;
<HTML>
<HEAD><TITLE>Database insertion</TITLE></HEAD>
<BODY>
EndBlock

# NOTE There must not be a trailing space after word EndBlock above

# *** If the array to which the $missing_fields reference is pointing
# *** is populated, it means that some of the required fields have NOT
# *** been filled in. So a warning is output, and the script ended.

if (@$missing_fields) {
print "<H1>You must fill in both your first and last name</H1>";
print "Please go back and do so...";
print "</BODY></HTML>";
exit; # The script ends here if there are missing fields
}

# *** We use the "from_cgi" method to capture the HTML form's data,
# *** ready for insertion into our database.
# *** The first argument passed to this method is the CGI object.
# *** Afterwards is an optional list of values that should be ignored.
# *** The list is included as a hash-reference (or anonymous hash as
# *** below) with the key being the name of the form field that should
# *** NOT be included in the database. That's the key. The value is simply
# *** the word "ignore".
# *** Below, we specify that we must ignore the "submit" button and its value
# *** A hash-reference is returned, with all the fields (except those
# *** you have told it to ignore) from your HTML form as the keys and their
# *** contents as the values.

$data_to_input=$DB->from_cgi($CGI, {'button' => 'ignore'} );

# *** We now add to and change the "raw" data collected above as required,
# *** via the augment method.
# *** The first argument is the hash-reference containing
# *** the data that you wish to change or add. Usually, it'll be the one
# *** you constructed using the "from_cgi" method (above).
# *** The second argument is a list within a hash-ref (or anonymous hash ref)
# *** of the keys and their new values. If the key already exists in your
# *** original data, it'll be replaced. Otherwise it'll be added a-new.
# *** Below, we are adding the date field and the inputter's hostname.
# *** The date field's value here is represented as ending with a "¬" symbol
# *** (it's at the top left of your keyboard).
# *** This is a convention of the module and means "don't put this value
# *** in quotes when you insert it!" - and, indeed, because it is a command
# *** to the database to insert the current date, you don't want it quoted:
# *** otherwise all that will be attempted to be inserted into that column
# *** would be the word "curdate()", not the result of the curdate command!

$DB->augment($data_to_input, {
hostname => $ENV{'REMOTE_HOST'},
date_entered => "curdate()¬"
});

# *** So now we have a hash-reference of all the values from the
# *** form we want inserted, minus the ones we don't - plus a few
# *** extra. So let's insert them!
# *** We use the "insert" method. The first argument is the table to use.
# *** The second argument is the hash-reference containing the data we wish
# *** to insert - as slurped from the CGI form above. The SQL query that
# *** the method constructs and its success (true if successful) are
# *** returned.

($sql_query, $success)=$DB->insert('users', $data_to_input);

# *** If the insert was successful, we say so. Otherwise we say sorry.

if ($success) {
print "<H1>Thank you $data_to_insert->{firstname}!</H1><P>";
print "I inserted this query: <I>$sql_query</I>";
}

else

{ print "<H1>Whoops, something went wrong. Try again!</H1><P>";
print "Please report the problem if it continues.";
}

print "</FORM></BODY></HTML>";

# *** And that's the end of the script!

g. Once Joe has uploaded the script, he uses ssh to get into his account and types

chmod 700 public_html/insertuser.cgi

He's not sure why - but he read in some Positive Internet document that this was necessary to make CGI scripts runnable.

Using the webform to fill in some data

h. He types

http://www.jbloggs.co.uk/userform.html
Into his web-browser and fills in his form.

Verifying that the data is there

i. Joe ssh's back into his account

j. At the prompt, he types
mysql -p jbloggs
and presses return. He types in his password.

k. He types
select firstname, lastname from users;
and presses return.

Sure enough, the data that he input on the web-form appears.

l. He types exit to quit the mysql shell

Displaying the data on the web

m. Joe writes another CGI script and calls it viewusers.cgi. He uploads it to the same place he uploaded his other script:

#!/usr/bin/perl;

use SQL;
use CGI;

$CGI=new CGI;
print $CGI->header();

# *** Set up the database object ***

$DB=new SQL('jbloggs', 'jbloggs', 'happy5');

# *** Collect the data from the users table in an array reference

$returned_table=$DB->query(" select firstname, lastname, age from users ");

# *** Output the start of the HTML page to the browser

print <<EndBlock;
<HTML>
<HEAD><TITLE>Database insertion</TITLE></HEAD>
<BODY>
EndBlock

# Note the word EndBlock must not have trailing white space.

# *** Output the returned data to the screen

print "<TABLE BORDER=1>";
print "<TR>";
print "<TD>Name</TD> <TD>Age</TD>";

foreach $row (@$returned_table) {
print "<TR>";
print "<TD>$row->{firstname} $row->{lastname}</TD>";
print "<TD>$row->{age}</TD>";
print "</TR>";
}

print "</TABLE>";
print "</FORM></BODY></HTML>";

# *** And that's the end of the script!

n. Joe uses ssh to connect into his account and types
chmod 700 public_html/viewuser.cgi

o. Joe points his web browser to
http://www.jbloggs.co.uk/viewuser.cgi

And sees the output of his script through his browser.

p. Joe gets name changed by deed-poll to something less embarrassing.

Back to top

new

eg: $DB=new SQL('shopbase:mysql.positive-internet.com','fred','s3cr3t');
or $DB=new SQL('shopbase:mysql.positive-internet.com','fred','/home/fred/.db_password');

Creates the SQL object, which controls access to the database.
The first argument is the name of the database a colon and the mysql server hostname. (For accounts set up prior to 1/1/04 the hostname part is not needed)
The second argument is the name of the user to conntect to that database.
The third argument is either the cleartext password, or a path to a file containing the cleartext password. This file will be formatted thus:

database:password

So in this case, it will read
shopbase:s3cr3t

The file can therefore have more than one password for connecting to more than one database.

query

eg: $results=$DB->query("select * from shopbase where status='unpaid'");

Query runs the read-query on the database and returns the results. Each row is returned as a hash-reference, which is pushed into an array, which is returned as an array reference.

For example, to print out the firstname and lastname columns from all the returned rows above, you would use:

foreach $row (@$results) {
print "$row->{firstname} $row->{lastname}\n";
}

line

eg: $row=$DB->line("select * from customers where ID='1234'");

When you are issuing a read-query that is going to return just one row, this is the easiest method to use. The argument is the SQL query. The results are returned as hash reference, where the keys of the hash are the titles of the returned columns. With the above example, doing the following will now print that customer's lastname (assuming the "lastname" column is in the table and is set for this customer):

print $row->{lastname};

item

eg: $age=$DB->item("select age from customers where ID='1234'");

This is an easy method to use when your SQL read-query is only going to return one value (not a whole row - for which you'd use the "line" method - or a whole filtered table - for which you'd use the "query" method). The value is returned as a simple scalar. With the above query, the value of that particular age column on the selected row will be returned, assuming it is found and has been set.

do

eg: $DB->do("insert into customers values ('1234','Roger','Smith')");
or: $result=$DB->do("update toys set price='25.00' where ID='teddybear'");

Use this method for insert, update, delete and other SQL-write statements. The argument is that SQL statement, and the return value is true if the statement was executed sucessfully.

insert

eg: $DB->insert('customers',\%data);
or ($sql_statement, $result)=$DB->insert('toys',$hash_ref_toys, \@ignore);

This is a powerful method - it will convert your hashes into an SQL insert statement and will do the inserting, taking account of any keys in your hash you have told it to ignore.

The method has three arguments, the last of which is optional.
It returns two values.

The first argument is name of the table into which you wish the insertion to be made.
The second argument is a hash-reference containing the data you wish to insert. The keys to the hash will be used as the names of the columns into which to insert.
The optional third argument is a reference to an array of keys to ignore - if you data hash contains keys and values you don't want to be included in the SQL statement, include the names of those keys here.

The data will be quoted as appropriate. If the value ends with a ¬ symbol, the data will NOT be quoted. This is useful for SQL commands you don't wish to be quoted - for example curdate() and the like.

The returned values are a string representing the constructed SQL statement and a results token - if the statement was executed sucessfully, it will be set to true.

exists

eg: $item=$DB->exists('Smith', 'lastname', 'customers');

Used to determine whether a particular value exists in a particular column of a table. If the value exists, it will be returned.
First argument is the name of the value for which you wish to check.
Second argument is the name of the column in which to look.
Third argument is the name of the table to search.

In the above query, if there is a "Smith" in the database, $item will be true (by being set to the name "Smith"). This is useful, therefore, for checking for the existence of particular unique IDs and other singular items.

today

eg: print $DB->today();

Return as a string the date as produced by the SQL curdate function.

uid

eg: $id=$DB->uid('P');

This method creates a unque ID value, with the following components (separated with a dash): epoch number, a letter and three random characters.

The argument is the identifying letter to be inserted in the middle.

For the letters to be properly random, it may be necessary to call srand once at the top of the calling script.

obligatory

eg: $missing=$DB->obligatory($CGI, @required);

This method determines which obligatory fields from a CGI form have not been filled in.
The first argument is the standard CGI object.
The second argument is an array of the obligatory field names.

An array reference is returned containing the names of any fields that were found not to be filled.

from_cgi

eg: $data=$DB->from_cgi($CGI);
or $data=$DB->from_cgi($CGI, {submit => ignore, hidden_var => ignore});

This method returns your CGI variables as a hash-ref. The first argument is the standard CGI object. The second argument is optional. It is a hashref (or anonymous hash ref as in the example above) of the CGI fields to ignore. The key of this hash should be the field to ignore, and the value should be the word "ignore". It is done like this, so that in the future, there might be other modifications possible on the fields besides "ignore".

The returned value is a hash-reference of the non-ignored items where the keys are the field names and the values the field data.

display_timestamp

eg: print $DB->display_timestamp($stamp);

Returns a standard SQL timestamp in a more readable format.

augment

eg: $DB->augment($rh_data, {
ID => $db->uid($prefix_letter),
inputter => $inputter
});

This method is used to change or add data to a hash-reference, particularly one produced with methods like from_cgi.

The first argument is the hash reference containing the data.
The second argument is a hash-reference (or an anonymous hashref as above) containing the keys and their new values. If the key didn't exist already in the original data, it will be added afresh.

Back to top