Database Connectivity

There is no need to learn about different APIs for each database vendor. PowerWeb provides a single uniform interface to all supported databases, so that you can switch databases by simply editing a single field within the Database Connection setup screen - your HTML and application logic is completely unaffected.

PowerWeb uses native database interfaces so performance is optimal. The tight integration of the database query within your HTML allows for simple rapid application development and the direct interface to the database bypasses the overhead of CGI solutions. With PowerWeb's intuitive interface, it is easy to submit multiple queries within a single HTML page, even combining results from multiple database vendors' products.

Table of Contents

Introduction
Database WebMacro Attributes
Examples
Notes
Currently Supported Database Vendors Sybase and Microsoft SQL Server
IBM DB/2
SQL Anywhere
Oracle
Informix
ODBC

Custom User Authentication and Access Control can also be performed by means of your database, as the examples in the API Reference Manual show.

With the first method, you specify a "connect" attribute (defined below) and with the second method, you must define the "vendor", "server", "userid" and "password" attributes.

The Database macro stores its cursor control variables within a special WebObject directory. Its default location is "Request:/Loop/". There are several WebObject variables within that directory:

ResultNumber
the current result set number, starting at 1.

ColumnCount
the number of columns in the current result set.

Columns/
a directory of the column names in the current result set.

Values/
a directory of the column values of the current row of the current result set.

RowNumber
the current row number within the current result set.

RowCount
the total number of rows retrieved.

ErrorCode
the error number or status returned by the query.

ErrorText
the error text (if any) returned by the query.

Attributes:

connect = (name)
Specifies a pre-defined connection to use for the database query. If this attribute is given, don't define "vendor", "server", "userid" or "password" attributes.

vendor = (sybase/db2/sqlanywhere)
Defines the vendor, not needed if "connect" is used. This name is matched against the list specified in the "Config:/Query/Database/Vendor" WebObject directory to determine how to interface to the specified database vendor. If your database is not listed, you can create your own interface library in any supported PowerWeb language (Rexx, Perl or any compiled language) and define it for use within PowerWeb.

server = (name)
Defines the name of the database server to connect to, not needed if "connect" is used. For ODBC and IBM DB/2 it should be omitted because the Data Source Name implies both the server name and the database name. For Sybase and MicroSoft SQL Server, it is the name of the server.

datasource = (name)
Defines the name of the datasource or database to connect to, not needed if "connect" is used. For ODBC and IBM DB/2, this is the Data Source Name (DSN) (which implies both the server name and the database name), whereas for Sybase and MicroSoft SQL Server, it is the name of the database.

userid = (name)
Defines the name of the user to log on to the database with, not needed if "connect" is used. The special name "PowerWeb" uses the name authenticated by PowerWeb when the client accessed the HTML page.

password = (text)
Defines the password for that user, not needed if "connect" is used. The special name "PowerWeb" uses the password given by the client when accessing the HTML page.

query = (text)
Defines the query to send to the specified database. Note that some database vendors do not accept tabs or line breaks within queries. The query can be any legitimate database command, including the invocation of a database stored procedure.

If you wish to call several SQL queries within a single WebMacro (for example, to combine the results into a single table or to force the usage of the same connection for a several stage query), you can insert the word "go" on its own line in-between each SQL query statement. You will need to use a WebScript to achieve this. For example:

<!--#script multi -->
select * from master..sysobjects
go
select * from sales..sysobjects
<!--#endscript -->

<!--#database connect=sysadm query=multi -->

dict = (text)
A pre-defined set of data dictionary queries are available, which work across all database vendors. They all have the format dict="command(arg1,arg2,...argn)". The available sub-commands are:

DataSources()
Returns the set of DataSources (sometimes called "databases") available at the currently connected database server.

Tables(catalog,schema,table,type)
Returns the set of Tables within the specified catalog and schema that match the table name pattern and table type. Valid table types are 'TABLE', 'SYSTEM TABLE' and 'VIEW'. Note that the surrounding single quotes ARE required.

Columns(catalog,schema,table,column)
Returns the set of Columns within the specified catalog, schema and table that match the column name pattern.

PrimaryKeys(catalog,schema,table)
Returns the set of Columns that comprise the Primary Key for the specified table within the specified catalog and schema.

ForeignKeys(pk_catalog,pk_schema,pk_table,fk_catalog,fk_schema,fk_table)
Returns the set of Columns that comprise the Foreign Key to use for joins with the Primary Key.

All these commands are X/OPEN compliant (a superset of ODBC version 2.0) and return the columns in the order specified in that standard. The best way to work with these commands is to construct your own examples or to examine the sample queries contained within the /admin/dbaccess directory.

Terminology Legend:

DataSource
For ODBC and DB/2, this is the name of the "Data Source", whereas for Sybase and MicroSoft SQL Server, it is the name of the "Database".

Catalog
This is the name of the "Database". In most cases, it should be left empty because many database vendors do not support data dictionary queries outside of the current "DataSource".

Schema
This is also often called the "Owner" of a "Table". It is normally left empty.

Table
This is the name of a "Table" within the current "DataSource". Leave it empty to match all tables.

Column
This is the name of a "Column" or "Field" within a "Table". Leave it empty to match all columns.

maxresults = (number)
The maximum number of result sets that will be returned by the database query. Excess sets will be discarded. Multiple result sets are typically returned by stored procedures.

consolidate = (yes/no)
Whether to consolidate the rows from multiple result sets. This is equivalent to the SQL "union" command. The default is FALSE.

Using the default presentation attributes, you will see multiple tables if consolidate is FALSE, or a single table with one set of headings if consolidate is TRUE.

maxrows = (number)
The maximum number of rows that will be returned by the database query. Excess rows will be discarded.

method = (normal/store/image)
Specifies how the database results will be handled.

Using "normal" (the default) causes the results to be output as HTML.

The "store" method saves the first column of the first row of the first result into the WebObject variable specified by the "into" attribute. This is very useful for returning TRUE/FALSE results which can then be subsequently tested with the WebMacro #if command to present conditional sections of HTML to the user.

The "image" method is typically used for returning images to the client. Any existing HTML output is discarded and no further HTML text within the document will be considered. The data returned by the database query should be a single column, single row, single result set, such as a BLOB. This means you can serve GIFs or JPEGs directly from your database, as a result of a database query, such as serving different images according to the identity of the client.

When using the "image" method, you should set the MIME type prior to calling the database. For example:

<!--#set Request:/Header/Out/Content-Type=image/gif -->

heading = (script name/macro) The name of the script to execute, or macro text to execute, to display the column headings.

row = (script name/macro) The name of the script to execute, or macro text to execute, to display each returned result row. If you leave this attribute undefined, along with heading and trailer also, then a default macro is executed which formats your database query result into an HTML table, using the column names returned by the database.

trailer = (script name/macro) The name of the script to execute, or macro text to execute, after all result rows have been retrieved.

error = (script name/macro) The name of the script to execute, or macro text to execute, if an error is encountered processing the database query. Note that returning zero rows is not regarded as an error. The error number (or error state for DB/2 and ODBC) is stored within "Request:/Loop/ErrorCode" and the error text (if any) is stored within "Request:/Loop/ErrorText". If you are using a WebMacro script, you can reference these variables simply as @ErrorCode and @ErrorText. Note that error codes differ from one database to another, so if you use conditional statements to process the error notifications, these will not be portable across all databases supported by PowerWeb.

cursor = (name) By default, PowerWeb places all of its database query variables in the object variable directory "Request:/Loop". This can be overridden with the cursor attribute, thus enabling nested SQL queries through naming each level of nesting separately. For example, the outer loop could be named "Loop1" and the inner loop named "Loop2". When specifying the name, you should omit "Request:/" because the variables MUST be located within that location.

live = (yes/no)
Whether to access the variable in the live configuration, or in the parallel copy used for editing purposes. Default is "live".

into = (variable)
Defines the variable in which to place the result of the macro's execution. If omitted or empty, the output is sent with the current document being served.

varprefix = (character)
Translates embedded WebObject variable names into their text contents before executing the script. If this attribute is not present, an '@' character is assumed.
SQL result column names and WebObject variable names are recognised by the special character prefix specified by this attribute. To include a literal character and not perform name lookup, prefix the character by a backslash.

varformat = (EscapeURL/EscapeHTML/EscapeSingleQuotes/EscapeDoubleQuotes)
Specifies how to format the contents of an embedded WebObject variable. See the #echo WebMacro's format attribute for more information.

queryformat = (EscapeURL/EscapeHTML/EscapeSingleQuotes/EscapeDoubleQuotes)
Specifies how to format the contents of an embedded WebObject variable within a "query" or "dict" attribute's script or value text. The default is "EscapeSingleQuotes" which works for most database managers which use single quotes to surround constant text within a query.

resultformat = (EscapeURL/EscapeHTML/EscapeSingleQuotes/EscapeDoubleQuotes)
Specifies how to format the contents of an embedded WebObject variable within a "header", "trailer", "row" or "error" attribute's script or value text. The default is "EscapeHTML" unless an "into" attribute is specified in which case no variable formatting is performed.

Database Wizard and Browser The Database Wizard and Browser provide a simple way of exploring the databases available to you and of constructing queries. There are many good examples of Database WebMacros and WebMacros in general within the directory "/admin/dbaccess".

Using a Database Query to Populate a Listbox:

<select multiple name=companies size=20>
<!--#database
   query    =   "select company from customers where state=@state"
   connect  =   customerdb
   row      =   "<option>@company"
-->
</select>

Showing Conditional Text:

<!--#create name=salary type=float -->
<!--#database
   query  = "select salary from staff where employee_id=@employee"
   connect= personnel
   method = store
   into   = salary
-->
<!--#if @salary>100000 -->
This employee earns an above-average salary of <!--#echo var=salary -->.
<!--#else -->
This employee does not qualify for a free jet.
<!--#endif -->

Show a list of all databases available on a Sybase server:

<!--#database
   query  = "select * from master..sysdatabases"
   vendor = sybase
   server = inventory
   userid = sa
-->

OR, Similarly, using a data dictionary call:

<!--#database
   dict   = "datasources()"
   vendor = sybase
   server = inventory
   userid = sa
-->

Returning an Image:

<!--#set Request:/Header/Out/Content-Type=image/jpeg -->
<!--#database
   query  = "select photo from staff where employee_id=@employee"
   connect= personnel
   method = image
-->

Sybase and Microsoft SQL Server
PowerWeb includes an interface to the Sybase/Microsoft SQL Server, versions 4.2 or later (including Sybase version 10 and beyond). You must install DB-Client on your PowerWeb server machine, before using PowerWeb's database connectivity. This gives you access to the full range of Microsoft and Sybase databases, including OS/2, NT and Unix.

Under Windows NT, if you use a NETBIOS database connection to Sybase/Microsoft SQL Server and PowerWeb is running as an NT service, you should modify the startup settings of the PowerWeb service to specify a valid user id and password. For this reason, we recommend using a TCP/IP connection to your Sybase/Microsoft SQL Server.

The PowerWeb OS/2 edition also includes a 16-bit interface for compatibility with previous releases.

With PowerWeb and Sybase's OMNI-SQL, you can connect to 29 different database managers, all through the "Sybase32" driver provided with PowerWeb, so long as you install OMNI-SQL on your database server. OMNI-SQL provides superior performance to ODBC when accessing database managers such as ADABAS.

IBM DB/2 Server
PowerWeb includes an interface to the IBM DB/2 Database Server. It requires you to have the IBM DB/2 CAE (Client Access Enabler) product installed, including the database DLL "db2cli.dll" on your LIBPATH (OS/2) or PATH (NT).

Using IBM's DB/2 Client Access Enabler software you can connect to any DB/2 server from mainframe through to AIX to OS/2 or Windows NT.

PowerWeb has been tested with DB/2 version 2.1 and 2.2, but not 1.2, so please report your success or failure with those versions. IBM specifies that DB/2 version 2.x must run on OS/2 Warp with at least FixPack 10 installed (we recommend at least FixPack 17), and you should install the DB/2 8090 fixpack or later (IBM says they fixed many problems with this fixpack).

In our experience, if you do not set up DB/2 as a standalone server, you should choose TCP/IP as your remote access protocol instead of NETBIOS, as it is both faster and allows for more simultaneous connections.

SQL Anywhere Server
PowerWeb for OS/2 includes a native interface to the Sybase/Watcom SQLAnywhere Database Server. It requires you to have the drivers such as "wod502.dll" on your LIBPATH.

Under NT, PowerWeb supports SQL Anywhere via ODBC. There is a known problem with the SQL Anywhere ODBC driver that does not allow it to be used when PowerWeb runs as an NT service. Instead, run PowerWeb as a normal program.

PowerWeb has been tested with SQLAnywhere version 5.0. Note that SQLAnywhere strictly enforces the number of simultaneous users specified by your license agreement (unlike IBM's DB/2 for example).

Oracle
Supported under Windows 95/NT via ODBC.

Informix
Supported under Windows 95/NT via ODBC.

ODBC
Available with the Windows 95/NT version of PowerWeb. Allows connections to Oracle, Informix, SQL Anywhere, Excel, Text Files, etc with the appropriate ODBC drivers installed. Use the Control Panel's 32 bit ODBC Manager to install and setup drivers. ODBC drivers are obtainable from the individual database vendors and from companies such as Intersolv.

If you run PowerWeb as an NT service, you should install version 2.50 or later of the ODBC manager and use a "System DSN" (a System Data Source Name is one that is shared across multiple NT user accounts), otherwise you will not be able to connect to your database.