Accessing Databases

Accessing Databases
Prev	Chapter 9. Writing Web Applications	Next

Most major web applications use a database. RSP was designed with this in mind. In fact, its original purpose was as a language for developing a cross platform database administration web application.

Data Sources

Before an RSP application can access a database it must have been provided with the connection details. This is handled through a system of named data sources. Data sources are not defined in the RSP source code. Instead you must create a support file containing the relevant data source information. The format varies between targets.

Defining Data Sources for PHP

When you deploy your compiled web application you must place a file called databases.inc.php in the root directory of the web application.

Example 9.1. Example databases.inc.php file

<?php
// Define your data sources below.  The fields are:
//
// DBType       - one of the predefined constants below
//                  RSP_DB_IBMDB2
//                  RSP_DB_JET
//                  RSP_DB_MCKOI
//                  RSP_DB_MYSQL
//                  RSP_DB_ORACLE
//                  RSP_DB_POSTGRESQL
//                  RSP_DB_SQLSERVER
// Hostname     - computer on which the database is hosted.  Leave blank to
//                use the default, which is 'localhost'
// Port         - TCP port through which to connect.  Again leave blank for the
//                default '3306'.
// Database     - name of the database to connect to.
// Username     - username to use when logging in
// Password     - password to use when logging in
 
// Define a data source called 'Database 1'
$RSP_Data_Sources['Database 1']['DBType'  ] = RSP_DB_;
$RSP_Data_Sources['Database 1']['Hostname'] = '';
$RSP_Data_Sources['Database 1']['Port'    ] = '';
$RSP_Data_Sources['Database 1']['Database'] = '';
$RSP_Data_Sources['Database 1']['Username'] = '';
$RSP_Data_Sources['Database 1']['Password'] = '';
 
// Define a data source called 'Database 2'
$RSP_Data_Sources['Database 2']['DBType'  ] = RSP_DB_;
$RSP_Data_Sources['Database 2']['Hostname'] = '';
$RSP_Data_Sources['Database 2']['Port'    ] = '';
$RSP_Data_Sources['Database 2']['Database'] = '';
$RSP_Data_Sources['Database 2']['Username'] = '';
$RSP_Data_Sources['Database 2']['Password'] = '';

// Define as many data sources as you want, provided that they all have
// unique names.
?>

Defining Data Sources for ASP

When you deploy your compiled web application you must place a file called databases.inc.asp in the root directory of the web application.

Example 9.2. Example databases.inc.asp file

<%
// Define your data sources below.  The fields are:
//
// Name         - the data source name used when specifying a data source.
// DBType       - one of the strings below:
//                  RSP_DB_IBMDB2
//                  RSP_DB_JET
//                  RSP_DB_MCKOI
//                  RSP_DB_MYSQL
//                  RSP_DB_ORACLE
//                  RSP_DB_POSTGRESQL
//                  RSP_DB_SQLSERVER
// DSN          - name of the Windows DSN to connect to.
// Username     - username to use when logging in.
// Password     - password to use when logging in.

new RSPDataSource(
  'Database 1',
  RSP_DB_,
  'Connection String...');

new RSPDataSource(
  'Database 2',
  RSP_DB_,
  'Connection String...');
%>

Defining Data Sources for JSP

When you deploy your compiled web application you must place a file called databases.inc.jsp in the root directory of the web application.

Example 9.3. Example databases.inc.jsp file

<%!
/*

Define your data sources in this file.  The format is:

    new RSPDataSource(
      "Data source name (for people to read)",
      "Database-type (see below",
      "jdbc.driver.class.name",
      "connections:string",
      "username",
      "password");

Valid database type constants are:

    RSP_DB_IBMDB2
    RSP_DB_JET
    RSP_DB_MCKOI
    RSP_DB_MYSQL
    RSP_DB_ORACLE
    RSP_DB_POSTGRESQL
    RSP_DB_SQLSERVER

*/

new RSPDataSource(
  "Database 1",
  RSP_DB_,
  "",
  "jdbc:");

new RSPDataSource(
  "Database 2",
  RSP_DB_,
  "",
  "jdbc:");
%>

Enumerating Data Sources

Within an RSP program you can use the getdatasources() function to get a list of all data sources available to the web application. This is most useful when developing a database administration application, since it allows the administrator to deploy the application for use in administering multiple databases. In other types of application you would typically only want a single data source to be defined which would have a predefined name. For example, a bulleting board application would only want one data source which might be called "Bulleting Board Data".

Connections and Recordsets

An RSP program can connect to a data source by means of the Connection object. A connection is opened using the constructor new Connection() which takes the data source name as its argument. An open connection allows you to query the data source and to update it through SQL commands. Connections remain open until the Connection.close() method is called.

Database Metadata

You can use the Connection.opentablesrs() method to open a recordset listing all available database tables. Some data sources will also list views and sequences. The columns returned will depend on the data source; use the Connection.getmetacolumn() method to determine the names of specific column types e.g. the column containing the table name.