# Creating database applications

ith LiveWire you can develop applications that can access databases on servers from Informix, Oracle, Sybase, and many others through the Open DataBase Connectivity (ODBC) standard. With LiveWire you can create client-server applications that run over the Internet or internal networks. This chapter discusses how to create database applications with LiveWire.

Note: This chapter assumes you are familiar with relational databases and Structured Query Language (SQL).

## The LiveWire three-tier architecture

The LiveWire architecture is intrinsically a three-tier client-server architecture, as illustrated in Figure 6.1. The three tiers are

- Netscape Navigator clients (WWW clients): This tier provides a cross-platform end-user interface to the application. This tier can also contain some application logic, such as data validation rules implemented in JavaScript. Clients can be inside or outside the corporate firewall.
- Netscape WWW server/database client: This tier consists of a Netscape server with LiveWire. It contains the application logic, manages security, and controls multiuser access to the application. This tier allows clients both inside and outside the firewall to access the application. The WWW server also acts as a client to the database servers.
- Database server: This tier consists of SQL database servers, typically running on high-performance workstations. It contains all the data, metadata, and referential integrity rules required by the application. This tier typically will be inside the corporate firewall and can provide a layer of security in addition to that provided by the WWW server.

The LiveWire three-tier architecture

## Installing database components

Before you can run a LiveWire database application, you must install all the components the application will use. Specifically, you must install the following:

- Relational database server software on your database server machine. For more information, refer to your database server documentation.
- A Netscape Web server on the WWW server machine. For more information, refer to the Netscape server documentation. In some cases, you may want to install the Web server and the database server on the same machine. To build a LiveWire application, you must also install LiveWire on your development machine. For more information, see "Installing LiveWire."
- Your database's client and networking software on your WWW server machine. If you are using one machine as both your database server and Web server, then installing the server typically will also install the client software. Otherwise, you must ensure that the client software is installed, so the Web server can access the database as a client. On some platforms, LiveWire will install database client libraries (see release notes for details). For more information on client software requirements, refer to the database vendor's documentation.

Users must have Netscape Navigator (or another Web client) on client machines. For more information, refer to the Netscape Navigator Handbook.

## An example: the Video application

LiveWire comes with a sample database application called Video, which uses a SQL database for tracking video rentals at a fictional video store. This sample is a good introduction to LiveWire's database capabilities.

### Creating the Video database

Before you can run Video you must create the video database on your database server. Refer to the release notes for your platform and database type for instructions on creating the video database. LiveWire release notes are available online at

    http://home.netscape.com/eng/LiveWire/relnotes

Each platform has a separate file, for example, `win-1.0.html` for Windows NT, `sol-1.0.html` for Solaris, and so on.

### Connecting to the database

You must also change some parameters to match you server and database name. In the LiveWire samples directory (`livewiresamples` in your server root directory), edit the file `start.htm`. You will see the line:

    if(!database.connected())

    	database.connect("INFORMIX", "yourserver", "informix",

    "informix", "livewire")

If you are not using Informix, change the first parameter to your database type: "SYBASE", "ORACLE", or "ODBC". Change the second parameter to the name of your database server. For Informix, this is typically "ol_serverName." See your database server documentation if in doubt. Change the third and fourth parameters to the username and password for your server (the Informix defaults are given). Leave the last parameter as "livewire", the name of the video database.

See "Connecting to a database" for more information on connecting.

Save your changes and re-compile the application. For information on how to compile, see "Compiling applications." Then restart the application in Application Manager.

### Running Video

Once you have created the video database and changed the database connection parameters, you can access the application at

    http://server.domain/video

After connecting to the database, you will see the Video home page:

If you cannot connect to the database, you will see an error message. Make sure you have entered the correct database connection parameters, as described in the previous section, recompiled, and restarted the application.

You can use Video as a customer or an administrator. As a customer, you can:

- Rent a movie.
- Show all the movies you currently have rented.

As an administrator, you can:

- Show all movies and who has them rented, and return a video for a customer.
- Add a new customer
- Delete a customer.

Run the application and make a few choices to perform different actions.

### Looking at the source files

The source HTML files for Video are copiously commented. You are encouraged to look at them to learn LiveWire database programming by example. Video has the following source files:

- `home.htm`: the application default page with links to `pick.htm, status.htm, rentals.htm, customer.htm, delete.htm`. If not connected to the database, redirects client to `start.htm`.
- `start.htm`: connects the application to the database then redirects back to`home.htm`.
- `pick.htm`: page you use to rent a movie, containing frames for `category.htm` (displays video categories) and `videos.htm` (displays all videos in selected category).
- `status.htm`: displays the videos customer currently has rented. If customer has not selected an ID, redirects to `client.htm`, which lets them select their customer ID.
- `rentals.htm`: displays a list of all rented videos. When you click on one, it submits the choice to `return.htm`, which performs the logic to return the video, then redirects back to `rentals.htm`.
- `customer.htm`: page you use to add a new customer; submits form input to `add.htm`, which performs logic to add a customer, then redirects back to `customer.htm`.
- `delete.htm`: page you use to delete a customer; displays list of customers with links to `remove.htm`, which deletes specified row from customer table, then redirects back to `delete.htm`.

## Working with databases

Before you create a LiveWire database application, the database should already exist on the database server, and you should be familiar with its structure. If you are creating an entirely new application, including the database, you should design, create, and populate the database (at least in prototype form) before creating the LiveWire application to access it.

LiveWire has an object named database that has methods for working with relational databases. The database object has no predefined properties. LiveWire creates a database object when an application connects to a database server. Each application can have only one database object. This table describes the database object's methods.

Method | Description | Parameters
--- | --- | ---
beginTransaction | Begins a SQL transaction. | None.
commitTransaction | Commits the current transaction. | None.
connect | Connects application to a database and creates database object. | Database type, server name, user name, password, database name.
connected | Returns true if application is connected to a database. | None.
cursor | Creates a database cursor for the specified SQL SELECT statement. | String representing a SELECT statement, Boolean value indicating whether cursor is updatable.
disconnect | Disconnects application from a database. After disconnecting, cannot use any other methods, except connect and connected. | None.
execute | Performs the specified SQL statement. Use for SQL statements other than queries. | String representing a SQL statement that does not return a cursor..
majorErrorCode | Major error code returned by the database server or ODBC. | None.
majorErrorMessage | Major error message returned by database server or ODBC. | None.
minorErrorCode | Secondary error code returned by vendor library. | None.
minorErrorMessage | Secondary message returned by vendor library. | None.
rollbackTransaction | Rolls back the current transaction. | None.
SQLTable | Displays query results. Creates an HTML table for results of a SQL SELECT statement. | String representing a SELECT statement.

### Connecting to a database

To connect to a database, use the database object's connect method. This method has the following syntax:

    database.connect("type", "server", "user", "password", "database")

Each parameter is a JavaScript string; literals must be enclosed in double quotation marks. The parameters are

- type: database type, either "INFORMIX," "ORACLE," "SYBASE," or "ODBC."
- server: database server name. The server name typically is established when the database is installed. If in doubt, see your database or system administrator. For ODBC, this is the name of the ODBC service as specified in Control Panel.
- user: the user name to log in to the database. Some relational database management systems (RDBMS) require that this be the same as your operating system login name; others maintain their own collections of valid user names. See your system administrator if you are in doubt.
- password: the password to log in to the database. If the database does not require a password, use an empty string ("").
- database: If your database server supports the notion of multiple databases on a single server, supply the name of the database to use. If it does not, use an empty string ("").
For example, the following statement connects to an Informix database named "mydb" on a server named "myserver," with user name SYSTEM and password MANAGER:

    database.connect("INFORMIX", "myserver", "SYSTEM", "MANAGER", "mydb")

The connected method indicates if the application is currently connected to a database. If currently connected, then connected returns true; otherwise it returns false. For example,

    if(!database.connected())

    	{ // statements to execute if not connected to a database }

#### Approaches to connecting to a database

You can take two different approaches to establishing database connections:

- The standard approach: the application establishes a connection in an initial page. Each client automatically gets a copy of this shared connection, and depending on the configuration of the server, multiple clients may be connected to the database simultaneously.
- The serial approach: the application allows one client at a time to connect to the database.

In the standard approach, the application contains a single connection statement that all clients use to establish the database connection. Thus, all clients share the same database, user name, and so on. In general, this requires a connect in the initial startup page of the application. Other pages in the application do not have to do anything special to use the shared connection. This approach is used in the video store sample application.

The advantage of the standard approach is that LiveWire handles all the details of establishing database connections. The disadvantage is that all connections from the application share the same user name and access privileges. This approach also requires a vendor license to use multiple database connections.

Note: The standard approach could cause the number of simultaneous database connections to reach the maximum number of processes or threads specified in your Netscape server's configuration file; see the next section. In the serial approach, each page in the application that needs a database connection first locks the project object and then connects to the database; after it is finished with its database activity, it then disconnects from the database and unlocks the project object. For example,

    project.lock()

    	database.connect(...)

    	database.execute("SELECT * FROM EMPLOYEE")

    	database.disconnect(...)

    project.unlock()

In general, when using this approach, clients can use different databases, user names, and so on. The lock method ensures that only one application can be connected to the database at any time. For more information on lock and unlock, see "Locking the project object."

If several applications will connect to the same database, you must lock the server object to use the serial approach.

#### Limiting the number of database connections

Note: The LiveWire Pro Informix license allows 32 database connections

Some database vendors charge fees based on the number of connections made to the database server. You can constrain the maximum number of simultaneous database connections from LiveWire applications on your server by either a setting in Application Manager (for Windows NT servers) or a setting in Server Manager (for Unix servers).

##### Windows NT

Windows NT servers are single process, multi-threaded, so the maximum number of database connections is controlled by the threading behavior of the server. When you install or modify an application with Application Manager, you can set the maximum number of database connections that can be made by that application.

See "Installing a new application."

Note: Informix client libraries are not threadsafe, so the maximum number of database connections per application is always one.

Assuming you want each application to have an equal number of connections, you can determine the proper setting for this parameter, MaxDBConnections, using this formula

MaxDBConnections = NumLicense / numDBApps

where NumLicense is the number of database connections for which your server is licensed, and numDBApps is the number of database applications installed. In general, you may want to specify a different MaxDBConnections for each application, but you should ensure that the total of MaxDBConnections for all applications on your server is less than or equal to NumLicense.

For example, suppose you have three database applications and your server license allows 32 connections. You could install one application that can have 16 connections and the other two that can have 8 each. The total number of connections could never then exceed 32.

##### Unix

On Unix, Netscape servers are multi-process; you can limit the maximum number of processes through a setting in your Server Manager. Choose System Settings in the top button bar and then choose Performance Tuning. Enter the maximum number of processes and choose OK. For more information, see your server's Administrator's Guide.

You can determine the appropriate setting as follows: if MaxProcs is the maximum number of HTTP processes allowed and numDBApps is the number of database applications running on your server, then the maximum number of database connections is

    MaxConnections = numDBApps x MaxProcs

Legally, the maximum number of database connections is determined by the license you have purchased from your database vendor. Thus you can calculate the setting of MaxProcs as

    MaxProcs = NumLicense / numDBApps

where NumLicense is the number of connections for which your server is licensed.

For example, if you have a thirty-two-user database license, and you have two database applications running (numDBApps = 2), then set

    MaxProcs = 32 / 2 = 16

#### Optimizing database applications

Netscape servers are multi-threaded on both Unix and Windows NT. Unix servers are also multi-process. With Windows NT servers, you can optimize performance of database applications by setting the maximum number of database connections as high as possible, as described in the preceding section.

With Unix servers, the situation is more complicated. For non-database applications, you will generally achieve better performance by allowing a large number of threads per process. However, due to limits of database vendor client libraries, there can only be one database connection per process per application. So for database applications, you will generally get better performance by having a small number of threads per process, because this will enable more simultaneous database connections.

You can set the minimum and maximum number of processes and threads in your Server Manager. Choose System Settings in the top button bar and then choose Performance Tuning. For more information, see your server's Administrator's Guide.

Optimization is an inexact science, and may be affected by many factors, including server load, database server tuning, and so on. As a general guideline, to optimize your server for database applications, set the minimum number of threads to three or four and the maximum number of threads to four to eight. You can refine these settings through experimentation.

### Displaying queries with SQLTable

LiveWire provides several ways for you to display the results of database queries. One of the simplest and quickest is the SQLTable method of the database object. The SQLTable method takes as its argument a SQL SELECT statement and returns an HTML table, with each row and column in the query as a row and column of the table.

Although SQLTable does not give you any explicit control over how the output is formatted, it is the easiest way to display query results. If you want to customize the appearance of the output, use a database cursor to create your own display function.

Note: For more information on database cursors, see "Using cursors."

For example, if request.sql contains a SQL query, then the following JavaScript statements will display the result of the query in a table:

    write(request.sql)

    database.SQLTable(request.sql)

The first line simply displays the SELECT statement, and the second line displays the results of the query. This is the first part of the HTML generated by these statements:

    select * from videos

    <TABLE BORDER>

    <TR>

    <TH>title</TH>

    <TH>id</TH>

    <TH>year</TH>

    <TH>category</TH>

    <TH>quantity</TH>

    <TH>numonhand</TH>

    <TH>synopsis</TH>

    </TR>

    <TR>

    <TD>A Clockwork Orange</TD>

    <TD>1</TD>

    <TD>1975</TD>

    <TD>Science Fiction</TD>

    <TD>5</TD>

    <TD>3</TD>

    <TD> Little Alex, played by Malcolm Macdowell, and his droogies stop by

    the Miloko bar for a refreshing libation before a wild night on the

    town.  </TD>

    </TR>

    <TR>

    <TD>Sleepless In Seattle</TD>

    ...

As this example illustrates, SQLTable generates an HTML table, with column headings for each column in the database table and a row in the table for each row in the database table.

### Disconnecting from a database

An application can close a database connection with the disconnect method as follows:

    database.disconnect()

Once disconnected from a database, an application cannot create cursors or use any other database methods.

## Executing passthrough SQL statements

The database method execute enables an application to execute any SQL statement that does not return a cursor. Using execute is referred to as performing passthrough SQL, because it passes SQL directly to the server.

You can use execute for any statement supported by the database server that does not return a cursor, including INSERT, UPDATE, and DELETE statements, CREATE, ALTER, and DROP data definition language (DDL) statements, and other control statements supported by the server.

Note: For more information, see "Using cursors."

It is preferable to use cursors to perform data modification (INSERT, UPDATE, and DELETE statements) because your application will be more database-independent. Cursors also provide support for binary large object (BLOb) data.

To perform passthrough SQL statements, simply provide the SQL statement as the argument to the method, for example:

    database.execute("delete from rentals r where r.customerID = "

    	+ request.customerID + " AND r.videoID = " + request.videoID)

Important: When using execute, your SQL statement must strictly conform to the syntax requirements of the database server; for example, some servers require each SQL statement to be terminated by a semicolon. See your server documentation for more information.

If you have not explicitly started a transaction, the single statement will be committed automatically. For more information on transaction control, see the next section.

## Managing transactions

A transaction is a group of database actions that are performed together. Either all the actions succeed together or all fail together. When you attempt to perform all the actions, you are said to commit a transaction. You can also roll back a transaction that you have not committed, which cancels all the actions.

Transactions are important for maintaining data integrity and consistency. Although the various database servers implement transactions slightly differently, LiveWire provides a uniform interface for transaction management.

Primarily actions that modify a database come under transaction control. These actions correspond to SQL INSERT, UPDATE, and DELETE statements performed with the execute method.

### Using default transactions

If you do not control transactions explicitly, LiveWire performs each database update as a separate transaction. Essentially, LiveWire begins an implicit transaction before each statement and attempts to commit the transaction after each statement. Explicitly managing transactions overrides this default behavior.

### Using explicit transaction control

Use the following methods of the database object to manage transactions:

- beginTransaction starts a new transaction. All actions that modify the database are grouped with this transaction, known as the current transaction.
- commitTransaction commits the current transaction. This method will attempt to commit all the actions since the last call to beginTransaction.
- rollbackTransaction rolls back the current transaction. This method will undo all updates since the last call to beginTransaction.

An application must be connected to a database to use these methods. For example, to begin a transaction, write

    database.beginTransaction()

Likewise, to commit a transaction, write

    database.commitTransaction()

The scope of a transaction is limited to the current HTML page in an application. If the application exits the page before issuing a commitTransaction or rollbackTransaction, then the transaction is automatically committed.

If there is no current transaction (that is, if the application has not issued a beginTransaction), any commitTransaction or rollbackTransaction statements are ignored.

### Nested transactions

A transaction within another transaction is a nested transaction. Essentially, a nested transaction performs two beginTransaction methods in a row, before committing or rolling back the first transaction.

LiveWire does not support nested transactions, because only one major database server, Sybase, supports them. If you begin a transaction and then issue another beginTransaction before committing or rolling back the active transaction, the second beginTransaction is ignored. Then, the next commitTransaction or rollbackTransaction will commit or roll back all the actions since the first beginTransaction.

### Transaction isolation levels

Transaction isolation level refers to the interaction among multiple simultaneous transactions. Different servers handle transaction isolation levels in different ways.

For Informix, the default isolation level is used for transactions. The defaults are

- Dirty read for databases with no logging (hence no transactions).
- Committed read for non-American National Standards Institute (ANSI) databases with logging.
- Repeatable read for ANSI databases with logging.

For more information, refer to the Informix documentation.

For Oracle, all transactions are started with the default isolation level set at the server level. In most cases it is set to "serializable = FALSE." This means Oracle maintains no read locks on rows being read, and write locks are maintained on rows being updated or inserted. For more information, refer to the Oracle documentation.

For Sybase, the default isolation level is "dirty read." For more information, refer to the Sybase documentation.

## Using cursors

A database query is said to return a cursor. You can think of a cursor as a virtual table, with rows and columns specified by the query; this virtual table is sometimes referred to as an answer set. A cursor also has a notion of a current row, which is essentially a pointer to a row in the answer set. When you perform operations with a cursor, they affect the current row.

### Creating a cursor

Once an application is connected to a database, you can create a cursor with the cursor method of the database object by giving it a SELECT statement as its argument. Use the following syntax:

    cursorName = database.cursor("SELECT statement", updatable)

where

- cursorName is the name you give the cursor object.
- SELECT statement is a SQL SELECT statement supported by the database server. To ensure database independence, use SQL 89/92-compliant syntax.
- updatable is a Boolean parameter (true or false) indicating whether you want to request an updatable cursor. This parameter is optional.

Initially, the pointer, or current row, is positioned just before the first row in the answer set.

For example, the following statement creates a cursor consisting of records from the customer table containing the columns ID, NAME, and CITY, ordering the records returned by the value of the ID column:

    custs = database.cursor("SELECT ID, NAME, CITY FROM CUSTOMER ORDER BY

    ID")

This query might return the following rows:

    1 John Smith Anytown

    2 Fred Flintstone Bedrock

    3 George Jetson Spacely

You can construct the SELECT statement with the string concatenation operator (+) and string variables such as client or request property values. For example,

    custs = database.cursor("select * from customer where ID = " +

    request.customerID)

### Cursor methods and properties

The cursor object has a number of methods and properties, as summarized in this table.

Method or property | Description | Parameters
--- | --- | ---
colName | Property corresponding to each column in the cursor. The name of each property colName is the name of the column in the database. | N/A
close | Method that closes the cursor. | None.
columns | Method that returns number of columns in the cursor. | None.
columnName | Method that returns the name of the columns in the cursor. | Zero-based ordinal number of column in query.
next | Method that moves the current row to the next row in the cursor. Returns false if current row is the last row in the cursor; otherwise returns true. | None.
insertRow | Method that inserts a new row following the current row in the specified table. | String specifying name of table in which to insert row.
updateRow | Method that updates records in the current row of the specified table in the cursor. | String specifying name of table to update.
deleteRow | Method that deletes the current row in the specified table. | String specifying name of table from which to delete row.

#### Displaying record values

When you create a cursor, it acquires a property colName for each column in the answer set, as determined by the SELECT statement. In the example found in "Creating a cursor," the cursor would have properties for the columns ID, NAME, and CITY. So, you could display the values of the current row with the following:

    <SERVER>custs.next()</SERVER>

    <B>Customer ID:</B> <SERVER>write(custs.ID)</SERVER> <BR>

    <B>Customer Name:</B> <SERVER>write(custs.name)</SERVER> <BR>

    <B>City:</B> <SERVER>write(custs.city)</SERVER>

Initially, the current row is positioned before the first row in the table. The execution of next method moves the current row to the first row, so the preceding code would display something similar to this:

Customer ID: 1

Customer Name: John Smith

City: Anytown

Note: Unlike other properties in JavaScript, cursor properties corresponding to column names are not case-sensitive, because SQL is not case-sensitive and some databases are not case-sensitive.

You can also refer to properties of a cursor object (or any JavaScript object) as elements of an array. The zero-index array element corresponds to the first column, the one array element corresponds to the second column, and so on.

So, for example, you could use an index to display the same column values retrieved in the previous example:

    <B>Customer ID:</B> <SERVER>write(custs[0])</SERVER> <BR>

    <B>Customer Name:</B> <SERVER>write(custs[1])</SERVER> <BR>

    <B>City:</B> <SERVER>write(custs[2])</SERVER>

This technique is particularly useful inside a loop.

#### Closing a cursor

Use the close method to close a cursor and free the memory consumed. For example,

    custs.close()

closes the cursor named custs. LiveWire automatically closes all cursors at the end of each client request.

#### Determining the number of columns in a cursor

The cursor method columns returns the number of columns in a cursor. For example, you could determine the number of columns returned as

    custs.columns()

This method is useful if you need to iterate over each column in a cursor.

#### Displaying column names

The cursor method columnName returns the name of each column in the result set. This method takes an integer as a parameter, where the integer specifies the ordinal number of the column, starting with zero. The first column in the result set is zero, the second is one, and so on.

For example, the following expression would assign the name of the first column in the custs cursor to the variable header:

    header = custs.columnName(0)

#### Displaying expressions and aggregate functions

SELECT statements can retrieve values that are not columns in the database, such as aggregate values and SQL expressions. You can display these values by using the cursor's property array index for the value. That is, you can refer to the expression as cursor[n], where n is the ordinal position (starting at zero) of the expression in the SELECT list.

For example, suppose you create the following cursor named empData:

    empData = database.cursor("SELECT MIN(SALARY), AVG(SALARY), MAX(SALARY)

    FROM EMPLOYEES")

Then you could display the value retrieved by the aggregate function MAX as follows:

    write("Highest salary is ", empData[2])

### Navigating with cursors

Initially, the pointer, or current row, for a cursor is positioned just before the first row in the answer set. Use the next method to move the pointer through the records in the cursor answer set. This method moves the pointer to the next row and returns true as long as there is another row in the answer set. When the cursor has reached the last row in the answer set, next returns false.

For example, if an answer set has columns named title, rentalDate, and dueDate, then the following code iterates through the rows with next and displays the column values in a table:

    <SERVER>

    while (cursor.next()) {

    	write("<TR><TD>" + cursor.title + "</TD>)

    	write("<TD>" + cursor.rentalDate) + "</TD>"

    	write("<TD>" + cursor.dueDate + "</TD></TR>")

    }

### Using a cursor: an example

You can easily display all the values in a query result by using the properties and methods of cursor. If you have created a cursor named custs as in the preceding examples, then you can use the following loop to display the query results in an HTML table:

    // Display column names as headers

    write("<TR>")

    i = 0

    while( i < custs.columns() ) {

    	write("<TH>", cursor.ColumnName(i), "</TH>")

    	i++

    }

    write("</TR>")

    // Display each row in the result set

    while(cursor.next()) {

    	write("<TR>")

    	i = 0

    	while( i < custs.columns() ) {

    		write("<TD>", cursor[i], "</TD>")

    		i++

    	}

    write("</TR>")

    }

### Using updatable cursors

An updatable cursor enables you to modify a table based on the cursor's current row. To request an updatable cursor, add an additional parameter of true when creating the cursor. For example,

    custs = database.cursor("SELECT ID, CUST_NAME, CITY FROM CUSTOMER",

    true)

You can create an updatable cursor only for single-table queries, not for multiple-table queries or queries containing joins.

For a cursor to be updatable, the SELECT statement must be an updatable query, that is, one that allows updating. For example, it cannot retrieve rows from more than one table or contain a GROUP BY clause, and generally it must retrieve key values from a table. For more information on constructing updatable queries, consult your database vendor's documentation.

Using an updatable cursor is a two-step process:

1. Assign values to columns in the cursor. For example,`custs.city = "New York"`
2. If doing an updateRow, perform at least one next to establish the first row of the table as the current row.
3. Use the insertRow, updateRow, or deleteRow method to modify the specified table (when using deleteRow, you don't need to perform the first step). For example, the following statement will update the CUSTOMERS table to have the value "New York" for its city column:

    	custs.updateRow("CUSTOMERS")

The updateRow method will update the current row in the specified table, the insertRow method will insert a new row, and the deleteRow method will delete the current row in the specified table.

When you use insertRow, the values you assign to columns in the first step are used for the new row. If you have previously performed next with the cursor, then the values of the current row are used for any columns without assigned values, otherwise the unassigned columns are null. You must perform at least one next before you do an updateRow, so there will be a row to update.

When inserting, if the cursor has not performed next, then any columns that you have not assigned values to will be null. Also, when inserting values into a table, if some columns in the table are not in the cursor, then insertRow will insert null in these columns.

You do not need to assign values when you use deleteRow, because it simply deletes an entire row.

### Data-type conversion

Databases have a rich set of data types. LiveWire converts these data types to JavaScript values, which are either strings or numbers. A JavaScript number is stored with the precision of a double-precision floating-point value. In general, LiveWire converts character data types to strings, numeric data types to numbers, and dates to JavaScript Date objects. It converts null values to JavaScript null.

Note: LiveWire does not support packed decimal notation; therefore some degree of precision may be lost when reading and writing packed decimal data types. Be sure to check results before inserting values back into a database, and use appropriate mathematical functions to correct for any precision loss.

#### Working with dates

Date values retrieved from databases are converted to JavaScript Date objects. For more information on working with dates in JavaScript, see the JavaScript Guide.

To insert a date value in a database, use a JavaScript Date object, as follows:

    cursorName.dateColumn = dateObj

where cursorName is a cursor, dateColumn is a column corresponding to a date, and dateObj is a JavaScript Date object. You create a Date object using the new operator and the Date constructor, as follows:

    dateObj = new Date(dateString)

where dateString is a string representing a date. If this is the empty string, it will create a Date object for the current date. For example,

    invoiceDate = new Date("Jan 27, 1997")

    custs.orderDate = invoiceDate

You can combine these two statements and get the same result, as in the following example:

    custs.orderDate = new Date("Jan 27, 1997")

#### Informix data-type conversion

Informix data type | LiveWire data type
--- | ---
char or nchar, text, varchar, or nvarchar | string
decimal(p,s), double precision, float, integer, money(p,s), serial, smallfloat, smallint | number
date, datetime | date
byte | BLOb
interval | not supported

#### Oracle data-type conversion

Oracle data type | LiveWire data type
--- | ---
long, char or varchar2(n), rowid, mislabel | string
number(p,s), number(p,0), float(p) | number
date | date
raw(255), long raw | BLOb

#### Sybase data-type conversion

Sybase data type | LiveWire data type
--- | ---
char(n), varchar(n), nchar(n), nvarchar(n), text | string
bit, tinyint, smallint, int, float(p), double precision, real, decimal(p,s), numeric(p,s), money, smallmoney | number
datetime, smalldatetime | date
binary(n), varbinary(n), image | BLOb

#### ODBC data-type conversion

ODBC data type | LiveWire data type
--- | ---
SQL_LONGVARCHAR, SQL_VARCHAR, SQL_CHAR | string
SQL_SMALLINT, SQL_INTEGER, SQL_DOUBLE, SQL_FLOAT, SQL_REAL, SQL_BIGINT, SQL_NUMERIC, SQL_DECIMAL | number
SQL_DATE, SQL_TIME, SQL_TIMESTAMP | date
SQL_BINARY, SQL_VARBINARY, SQL_LONGBINARY | BLOb

### Working with binary data

Binary data for multimedia content such as images or sounds are stored in databases as binary large objects (BLObs). You can use two techniques to handle binary data in LiveWire applications: store filenames in the database and keep the data in separate files or store the data in the database as BLObs and access it with the BLOb methods LiveWire provides.

If you do not need to keep BLOb data in a database, you can store the filenames in the database and access them in your application with standard HTML tags. For example, if you want to display an image for each row in a database table, you could have a column in the table called imageFileName containing the name of the appropriate image file. You could then display the image for each row with this HTML:

    <IMG SRC=`mycursor.imageFileName`>

As the cursor navigates through the table, the name of the file in the IMG tag changes to reference the appropriate file.

If you need to store binary data itself in your database (or if it is already there), LiveWire provides methods for displaying and inserting BLOb data, as described in the following table.

Method or function | Description | Parameters
--- | --- | ---
blobImage | Displays BLOb data stored in a database. Returns an HTML IMG tag for the specified image type (GIF, JPEG, and so on). | String specifying image type, for example, GIF, JPEG, and so on.
blobLink | Displays a link that references BLOb data with a hyperlink. Creates an HTML hyperlink to the BLOb. | String specifying Multipurpose Internet Multimedia Extensions (MIME) type of BLOb data.
blob | Assigns BLOb data to a column in a cursor. Use to insert or update a row containing a BLOb using an updatable cursor. This is a top-level function, not a cursor method. | String specifying filename containing binary data.

#### Using blobImage

The blobImage method fetches a BLOb from the database, creates a temporary file (in memory) of the specified format, and generates an HTML image tag that refers to the temporary file. LiveWire removes the temporary file after the page is generated and sent to the client.

While creating the page, LiveWire keeps the binary data that blobImage fetches from the database in active memory, so requests that fetch a large amount of data can exceed dynamic memory on the server. Generally it is good practice to limit the number of rows retrieved at one time using blobImage to stay within the server's dynamic memory limits.

Use blobImage to create an HTML image tag for a graphic image in a standard format such as GIF or JPEG. For a cursor cursorName and a column containing BLOb data colName, the syntax for this method is

    cursorName.colName.blobImage(format [, altText ] [, align ]

    [, widthPixels] [, heightPixels] [, borderPixels] [,ismap ])

The parameters are

- format: a string specifying the image format. This can be "GIF," "JPEG," or any other MIME image format. The acceptable formats are specified in the "type=image" section of the file ns-homehttpd-80configmime.types in your Netscape server's root directory. The client browser must also be able to display the image format.
- altText: an optional parameter specifying the value of the ALT attribute of the image tag. This indicates text to display if the client browser does not display images.
- align: an optional parameter specifying the value of the ALIGN attribute of the image tag. This can be "left," "right," or any other value supported by the client browser.
- widthPixels and heightPixels: optional parameters specifying the width and height of the image in pixels.
- borderPixels: an optional parameter specifying the size of the outline border in pixels if the image is a hyperlink.
- ismap: an optional Boolean parameter specifying whether the image is a clickable map. If this parameter is "true," the image tag will have an ISMAP attribute; otherwise it will not.

All blobImage parameters except ismap are JavaScript string expressions.

#### Using blobLink

The blobLink method fetches BLOb data from the database, creates a temporary file in memory, and generates a hypertext link to the temporary file. LiveWire removes the temporary files that blobLink creates after the user clicks the link or sixty seconds after the request has been processed.

LiveWire keeps the binary data that blobLink fetches from the database in active memory, so requests that fetch a large amount of data can exceed dynamic memory on the server. Generally it is good practice to limit the number of rows retrieved at one time using blobLink to stay within the server's dynamic memory limits.

Use blobLink if you do not want to display graphics (to reduce bandwidth requirements) or to provide a link to an audio clip or other multimedia content not viewable inline. The syntax of this method is

    cursorName.colName.blobLink(mimeType, linkText)

where colName is the name of a column in the cursor containing BLOb data. The parameters of this method are

- mimeType: a string specifying the MIME type of the binary data. This can be "image/gif" or any other acceptable MIME type, as specified in the Netscape server configuration file ns-homehttpd-80configmime.types.
- linkText: the text to display in the link. This can be any JavaScript string expression.

The following example illustrates using blobImage and blobLink to create temporary files:

    cursor = database.cursor("SELECT * FROM BLOBTEST")

    while(cursor.next()) {

    	write(cursor.id)

    	write(cursor.picture.blobImage("gif"))

    	write(cursor.picture.blobLink("image/gif", "Link" + cursor.id))

    	write("<BR>")

    }

    cursor.close()

This example will produce the following HTML:

    1 <IMG SRC="LIVEWIRE_TEMP9"> <A HREF="LIVEWIRE_TEMP10">Link1 </A> <BR>

    2 <IMG SRC="LIVEWIRE_TEMP11"> <A HREF="LIVEWIRE_TEMP12">Link2 </A> <BR>

    3 <IMG SRC="LIVEWIRE_TEMP13"> <A HREF="LIVEWIRE_TEMP14">Link3 </A> <BR>

    4 <IMG SRC="LIVEWIRE_TEMP15"> <A HREF="LIVEWIRE_TEMP16">Link4 </A> <BR>

#### Using blob

The blob function assigns BLOb data to a column in a cursor. Use this function to insert or update a row containing a BLOb using an updatable cursor. To insert or update a row using SQL and the execute method, use the syntax supported by your database vendor. The blob function is a top-level function, not a cursor method, like blobImage and blobLink.

These statements illustrate using blob to update a row in a table with an updatable cursor:

    cursor.photo = blob("myphoto.gif")

    cursor.office = blob("myoffice.gif")

    cursor.updateRow("employee")

These statements update BLOb data from the specified GIF files in columns PHOTO and OFFICE of the EMPLOYEE table.

## Error handling

SQL statements can fail for a variety of reasons, including referential integrity constraints, lack of user privileges, record or table locking in a multiuser database, and so on. When an action fails, the database server returns an error message indicating the reason for failure. LiveWire provides two ways of getting error information: from the status code returned by database methods or from special database properties containing error messages and codes.

### Database status codes

Many database and cursor methods return a status code based on the error message generated by the database server. The execute, insertRow, updateRow, deleteRow, beginTransaction, updateTransaction, and rollbackTransaction methods all return a status code.

Status codes are integers between zero and twenty-seven, with zero indicating a successful execution of the statement and other numbers indicating an error, as shown in the following table:

Status code | Explanation
--- | ---
0 | No error
1 | Out of memory
2 | Object never initialized
3 | Type conversion error
4 | Database not registered
5 | Error reported by server
6 | Message from server
7 | Error from vendor's library
8 | Lost connection
9 | End of fetch
10 | Invalid use of object
11 | Column does not exist
12 | Invalid positioning within object (bounds error)
13 | Unsupported feature
14 | Null reference parameter
15 | Database object not found
16 | Required information is missing
17 | Object cannot support multiple readers
18 | Object cannot support deletions
19 | Object cannot support insertions
20, 21 | Object cannot support updates
22 | Object cannot support indices
23 | Object cannot be dropped
24 | Incorrect connection supplied
25 | Object cannot support privileges
26 | Object cannot support cursors
27 | Unable to open

### Database error methods

The database object contains four methods that return error codes and messages returned by the database server. The methods are

- majorErrorMessage: major error message returned by the database server or ODBC.
- minorErrorMessage: secondary message returned by the database vendor library.
- majorErrorCode: major error code returned by the database server or ODBC. For server errors, this typically corresponds to the server's SQLCODE.
- minorErrorCode: secondary error code returned by the database vendor library.
The results returned by these methods depend on the database server being used and the database status code.

Informix status code 7 (vendor library error)-database error methods

Method | Description
--- | ---
majorErrorMessage | "Vendor Library Error: string," where string is error text from Informix.
minorErrorMessage | "ISAM Error: string," where string is the text of the ISAM error code from Informix, or an empty string if there is no ISAM error.
majorErrorCode | Informix error code.
minorErrorCode | The ISAM error code, or zero if there is no ISAM error.

Oracle status code 5 (server error)-database error methods

Method | Description
--- | ---
majorErrorMessage | "Server Error: string," where string is translation of return code supplied by Oracle.
minorErrorMessage | Oracle server name.
majorErrorCode | Return code as reported by Oracle Call-level Interface (OCI).
minorErrorCode | Operating system error code as reported by OCI.

Sybase status code 7 (vendor library error)-database error methods

Method | Description
--- | ---
majorErrorMessage | "Vendor Library Error: string," where string is error text from DB-Library.
minorErrorMessage | Operating system error text, as reported by DB-Library.
majorErrorCode | DB-Library error number.
minorErrorCode | Severity level, as reported by DB-Library.

Sybase status code 5 (server error)-database error methods

Method | Description
--- | ---
majorErrorMessage | "Server Error string," where string is text from SQL server, unless severity and message number are both zero, in which case just the message text.
minorErrorMessage | SQL server name.
majorErrorCode | SQL server message number.
minorErrorCode | Severity level, as reported by SQL server.

## Performing data definition with DBAdmin

DBAdmin is a LiveWire sample application you can use to perform SQL data definition such as creating new databases and tables. Because you can use DBAdmin to modify and delete databases, access to it is automatically restricted if you choose to protect Application Manager. For more information, see "Protecting Application Manager."

The DBAdmin home page gives you the following choices:

- Connect to Database Server
- Execute SQL Query
- Create a New Database
- Delete a Database
- Create a New Table
- Delete a Table

Before you can do anything else, you must connect to a database. Choose Connect to Database Server. A form will appear in which you can enter your server name, database name, username, and password. Enter the parameters, and click Connect to attempt to connect to the server.

Note: To perform some actions, such as creating or deleting a table, you may need to have privileges granted by your database administrator. Refer to your database server documentation for more information, or ask your database administrator.