Up One Level

Lecture Slides available: PDF PowerPoint

Application Links

Contents

Some relational database products provide a full programming environment. Such systems include Access, ORACLE, Paradox. At one time these integrated environments were labelled as 4GLs. This term quickly became overused, and the term 4GL has largely fallen into disuse.

Typically such systems will include:

  • A database engine.
  • A mechanism for creating tables and entering raw data into the tables. Such a table editor will often provide a means of establishing foreign keys and simple format restrictions.
  • A tool for creating applications. Often the user will be isolated from the raw data entry mechanism. A protected environment can be built by a database designer; users are then presented with a simplified view of the data. Some kind of programming language is usually built in.
  • Various mechanisms for producing reports

The database engine is not normally visible to the user or even the programmer - indeed it should be possible for the programmer to switch between engines relatively painlessly.

Some concerns

4GL systems can lead to the rapid development of relatively powerful applications. However:

  • The very speed of development can cause long term difficulties - things that start as prototypes tend to become products.
  • The proprietary nature of the products they are based on can cause constant update problems.
  • A particular product may tie systems into specific operating systems (witness the number of dumb terminals sitting alongside PC’s)
  • Vendors bring out new versions regularly, developers rarely have the luxury of working on the latest version. Program maintenance on obsolete versions of a language can be irritating
  • As systems come and go it can be difficult to find expertise

Databases in other languages

Rather than developing in a proprietary, specialist language (VBA, dBase, PL) we can develop in a well established, general purpose language (C, C++, Pascal) and link to a database engine.

There are several common means of achieving this

  • SQL embedding
  • using an API (application programmer interface) such as ODBC
  • visual programming approach (Visual Basic, Delphi...)

Each of these approaches involves the notion of a cursor.

Cursors

A cursor may be viewed as a pointer into a relational table (or view). It will usually be possible for the programmer to step forwards and backwards through the table. Individual fields may be accessible as

  • Text boxes
  • Program variables
  • API function calls

API calls

A database API (application program interface) is a set of function definitions that allow an application program to connect to an SQL server. Typical API instructions include:

  • connect – identify the machine to connect to and the user name and password of the account to be used
  • execute – send an SQL statement to the server. This function often returns a “handle” or “cursor” if data is to be returned from the SQL statement
  • fetch – get one row of the data returned by a select statement
  • advance – move the cursor on to the next row
  • test – check if we are at the last row
  • close – close the connection to the database and release any resources used by the connection

Having a single standard API can give many advantages to the programmer. Ideally a programmer can write and compile a program using a particular database product (such as Microsoft SQL Server) then switch to another database vendor (such as Oracle) with only a trivial change to the code. As both manufacturers provide an implementation of the API the code should work equally well in both cases.

  • programmers who use non-standard SQL lose this flexibility
  • malevolent database vendors trick programmers into using non-standard SQL so that the code is “locked into” a single product.

The following example is for Delphi. A similar functionality is available in VB and other languages.

Table1.First;
while not Table1.EOF do
begin
  Memo1.lines.Add(Table1.FieldByName('NAME').AsString);
  Table1.Next;
end;

This is a typical routine for reading from a table. The cursor is placed at the beginning of the file, inside the loop two actions take place:

  • Data is read from the current record and processed in some way
  • The cursor is moved on to the next record

The loop terminates when the cursor attempts to move on from the final record.

Note

  • The order of the elements in the table is governed by an IndexFile property of the Table1 value
  • It is possible to change values by assigning the "pseudo-variable"; Table1.FieldByName('NAME').AsString
  • Table1.Fields[x] may be used in place of FieldByName('xyz')

Data Linked Visual Components

This example shows a data-linked text box in Visual Basic. Other components are possible. Other languages have similar mechanisms.

   

Figure : Visual Components

Notes:

  • The data source Data1 has it's "record source" property set to a pre-existing table "ANIMALS"
  • Data1 acts as a cursor - the arrows permit users to move backwards and forward through the table at run time
  • Text1 is a data-linked or data-aware component. The property Text1.DataSource is set to Data1, the property Text1.DataField is set to "NAME"
  • As the cursor moves the data displayed in Text1 is updated to reflect the current row
  • Text1 may also be set up to automatically update the database if the user performs an edit.

Using spreadsheets

Many spreadsheets permit primitive relational operators:

=VLOOKUP(B1, Sheet2!$A$1:$B$3, 2)

We can have many of the advantages of a relational database within a spreadsheet by sticking to a few rules:

  • Store one record per row (1NF)
  • Rely on VLOOKUP index into other tables
  • Maintain key order

Using PHP and MySQL

selene(63)% /usr/local/mysql/bin/mysql -h
zeus -u andrew –p
mysql> use andrew
mysql> show tables;
+------------------+
| Tables_in_andrew |
+------------------+
| one              |
| cia              |
+------------------+
2 rows in set (0.05 sec)

mysql> select * from cia where
population>200000000;

+---------------+----------------+---------+------------+---------------+
| name          | region         | area    | population | gdp           |
+---------------+----------------+---------+------------+---------------+
| China         | Asia           | 9596960 | 1261832482 | 4800000000000 |
| India         | Asia           | 3287590 | 1014003817 | 1805000000000 |
| Indonesia     | Southeast Asia | 1919440 |  224784210 |  610000000000 |
| United States | North America  | 9629091 |  275562673 | 9255000000000 |
+---------------+----------------+---------+------------+---------------+
4 rows in set (0.11 sec)
Figure : CGI Example
<?php
if ($country) {
  $link = mysql_connect("zeus","andrew","******") or die("Could not connect");
  mysql_select_db("andrew") or die("Could not select database");
  $query = "SELECT name, region, population FROM cia WHERE name='$country'";
  $result = mysql_query($query) or die("Query failed");
  while ($row = mysql_fetch_array($result)) {
    extract($row);
    print "name: $name<br>\n";
    print "region: $region<br>\n";
    print "population: $population<br>\n";
  }
  print "</table>\n";
  mysql_free_result($result);
  mysql_close($link);
}else{
  print "<form><input name='country'></form>\n";
}
?>

SQL Embedding

The following code is a simple embedded SQL program, written in C. The program illustrates many, but not all, of the embedded SQL techniques. The program prompts the user for an order number, retrieves the customer number, salesperson, and status of the order, and displays the retrieved information on the screen.

main()
{
   EXEC SQL INCLUDE SQLCA;
   EXEC SQL BEGIN DECLARE SECTION;
      int OrderID;         /* Employee ID (from user)         */
      int CustID;            /* Retrieved customer ID         */
      char SalesPerson[10]   /* Retrieved salesperson name      */
      char Status[6]         /* Retrieved order status        */
   EXEC SQL END DECLARE SECTION;

   /* Set up error processing */
   EXEC SQL WHENEVER SQLERROR GOTO query_error;
   EXEC SQL WHENEVER NOT FOUND GOTO bad_number;

   /* Prompt the user for order number */
   printf ("Enter order number: ");
   scanf ("%d", &OrderID);

   /* Execute the SQL query */
   EXEC SQL SELECT CustID, SalesPerson, Status
      FROM Orders
      WHERE OrderID = :OrderID
      INTO :CustID, :SalesPerson, :Status;

   /* Display the results */
   printf ("Customer number:  %d\n", CustID);
   printf ("Salesperson: %s\n", SalesPerson);
   printf ("Status: %s\n", Status);
   exit();

query_error:
   printf ("SQL error: %ld\n", SQLCA.SQLCODE);
   exit();

bad_number:
   printf ("Invalid order number.\n");
   exit();
}

Advantages of a standard API

An ideal API is one that connects many development platforms to many database implementations. It allows application designers to give their users access to many databases; it allows database manufacturers to provide an interface to many application platforms.

Missing ALT text
Figure : Standard API via ODBC

Without a standard each language would have to provide an interface to each database implementation.

Popular APIs

ODBC - Open Database Connectivity

Specified by Microsoft and principally associated with the MS Windows platform, ODBC includes a basic set of routines to connect to a database engine. ODBC connections can be set up from the control panel on a windows machine.

This is a popular and successful API. Most programming languages can form an ODBC connection without reference to the underlying database product. Most database products can fulfill the API.

Missing ALT text
Figure : ODBC Data Source Administrator

Using SQLBindCol (ODBC)

The application binds columns by calling SQLBindCol. This function binds one column at a time. With it, the application specifies:

  • The column number. Column 0 is the bookmark column; this column is not included in some result sets. All other columns are numbered starting with the number 1. It is an error to bind a higher numbered column than there are columns in the result set; this error cannot be detected until the result set has been created, so it is returned by SQLFetch, not SQLBindCol.
  • The C data type, address, and byte length of the variable bound to the column. It is an error to specify a C data type to which the SQL data type of the column cannot be converted; this error might not be detected until the result set has been created, so it is returned by SQLFetch, not SQLBindCol.
  • The address of a length/indicator buffer. The length/indicator buffer is optional. It is used to return the byte length of binary or character data or return SQL_NULL_DATA if the data is NULL.

When SQLBindCol is called, the driver associates this information with the statement. When each row of data is fetched, it uses the information to place the data for each column in the bound application variables.

For example, the following code binds variables to the SalesPerson and CustID columns. Data for the columns will be returned in SalesPerson and CustID. Because SalesPerson is a character buffer, the application specifies its byte length (11) so the driver can determine whether to truncate the data. The byte length of the returned title, or whether it is NULL, will be returned in SalesPersonLenOrInd.

Because CustID is an integer variable and has fixed length, there is no need to specify its byte length; the driver assumes it is sizeof(SQLUINTEGER). The byte length of the returned customer ID data, or whether it is NULL, will be returned in CustIDInd. Note that the application is only interested in whether the salary is NULL, because the byte length is always sizeof(SQLUINTEGER).


SQLCHAR         SalesPerson[11];
SQLUINTEGER     CustID;
SQLINTEGER      SalesPersonLenOrInd, CustIDInd;

SQLRETURN       rc;

SQLHSTMT        hstmt;

// Bind SalesPerson to the SalesPerson column and CustID to the CustID column.
SQLBindCol(hstmt, 1, SQL_C_CHAR, SalesPerson, sizeof(SalesPerson),
            &SalesPersonLenOrInd);
SQLBindCol(hstmt, 2, SQL_C_ULONG, &CustID, 0, &CustIDInd);

// Execute a statement to get the sales person/customer of all orders.
SQLExecDirect(hstmt, "SELECT SalesPerson, CustID FROM Orders ORDER BY SalesPerson",
            SQL_NTS);

// Fetch and print the data. Print "NULL" if the data is NULL. Code to check if rc
// equals SQL_ERROR or SQL_SUCCESS_WITH_INFO not shown.
while ((rc = SQLFetch(hstmt)) != SQL_NO_DATA) {
   if (SalesPersonLenOrInd == SQL_NULL_DATA) 
            printf("NULL"); 
   else 
            printf("%10s   ", SalesPerson);

   if (CustIDInd == SQL_NULL_DATA) 
         printf("NULL\n");
   else 
         printf("%d\n", CustID);
}

// Close the cursor.
SQLCloseCursor(hstmt);

JDBC

JDBC provides a similar level of functionality to ODBC but is specific to the Java programming language.

The following is an example of a Java program using JDBC:

/* CIA.java
  From http://sqlzoo.net By Andrew Cumming
*/
import java.sql.*;
public class CIA{
 public static void main(String[] args){
   Connection myCon;
   Statement myStmt;
   try{
     Class.forName("com.mysql.jdbc.Driver").newInstance();
     # Connect to an instance of mysql with the follow details:
     # machine address: pc236nt.napier.ac.uk
     # database      : gisq
     # user name     : scott
     # password      : tiger
     myCon = DriverManager.getConnection(
             "jdbc:mysql://pc236nt.napier.ac.uk/gisq"
             "scott","tiger");
     myStmt = myCon.createStatement();
     ResultSet result = myStmt.executeQuery(
        "SELECT name FROM cia WHERE population>200000000");
     while (result.next()){
       System.out.println(result.getString("name"));
     }
     myCon.close();
   }
   catch (Exception sqlEx){
     System.err.println(sqlEx);
   }
 }
}

Such a program may be compiled with the command:

javac CIA.java

It may be executed with the command:

 java -cp mysql-connector-java-2.0.14-bin.jar:. CIA

In the code shown a connection is made to the mysql database using the getConnection method – we specify the machine on which mysql is running (pc236nt.napier.ac.uk), the mysql database (gisq), the user name (scott) and the password (tiger).

Having created an SQL statement we get a ResultSet object by executing the SQL statement over the connection. For the statement given we get four rows each with a single column – these are the countries China, India, United States and Indonesia.

The ResultSet object is a cursor that points to a single row of the result table. Initially the cursor is considered to be pointing to before the first row, the method result.next() is the first instruction – this moves it on to the first row.

We can retrieve data from the ResultSet using the getString method. The getString method takes either an attribute name (as in this case) or an integer indicating the position of the attribute. In either case the value of that field is returned as a string. Similar methods such as getInteger are available.

DBI/DBD

An API which is growing in popularity is the DBI/DBD interface. This is an attempt to offer a standard programmers interface to executing SQL from a variety of languages. It has many similarities to ODBC, but without some of the complexities. It is a popular database linking API for Perl.

The following is a fragment of Perl example code for finding out the surname of employees with a particular department number. 

my $dbh = DBI->connect("dbname","username","password");
my $depno = 3;
my $cmd = $dbh->prepare("SELECT surname FROM employee where depno=?");
my $res = $cmd->execute($depno);
while (my ($name) = $res->fetchrow_array()) {
  prnt "The employee name is $name\n";
}

Using ASP

ASP programming allows data from a database to be displayed on web pages. The ASP script (often VBScript or JScript) is interpreted at the Web server. The web client (the browser) receives plain HTML.

ASP
Figure : Using ASP

Typically the following sequence of events takes place:

  • A web surfer requests an ASP page by linking to www.xyz.com/page1.asp, this user is likely to be using Netscape or IE or similar - the browser does not need any special plugins or applets
  • The Web Server (probably IIS or PWS) receives the request, examines page1.asp in the local file space and interprets it - the page makes reference to database db1.mdb - this causes a request to the RDBMS
  • The RDBMS (probably SQL Server) gets the SQL request and returns the results.

A sample ASP code

<%SQL="SELECT carName FROM Cars ORDER BY carName"
  set conn = server.createobject("ADODB.Connection")conn.open "parking"
  set cars=conn.execute(SQL) %>
<% do while not cars.eof %>
  <%= cars(0) %> <br>
  <%cars.movenext 
    loop%>
<% cars.close %>

If you are interested in trying out ASP yourself, you will need to get an account on an IIS server. You can try running your own server using PWS from Microsoft. Another option is to get a free account from an online site such as www.brinkster.com

Efficiency Issues

No matter how the connection to the database is made, care should be taken to ensure that the connection is handled efficiently. The application connecting to a database is typically executing on a different machine to the database server. This has many advantages – and is essential if the database is to be shared by more than one user machine. However having the application program and the database server on different machines introduces a significant performance cost.

Establishing a connection, including time to log on and verify passwords can be costly. Communication between the application machine and the server must go across a network – this is usually considerably slower than the disk transfer rate. Sending SQL statements to the server is relatively trivial however sending the rows of the results back may be significant. Programmers should take care to request only the data they need, lazy programmers may be tempted to request “SELECT * FROM table” when “SELECT id FROM table” would do. In the first case all fields may be sent across the network for every row examined – this can be very expensive especially if there are many fields or some of them are lengthy.

As an example consider a web site configured to dynamically create and serve web pages from stored database information. When the server program is generating web pages it may be that database connections are being created rapidly to solve relatively trivial requests. It is not uncommon for this type of web server to be spending far more time opening and closing connections than anything else. In such cases connection pooling may help. The web-server creates a pool of connections and keeps these open between requests. This may be transparent to the application programmer.

Up One Level
Database Notes
Online Notes
Reference Pages
Tutorial Activities
Online SQL
Online Quiz
Discussion Forum
Future Stuff   
Online Relational Algebra
News