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

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:

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:

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:

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

Approaches to connecting to a database

You can take two different approaches to establishing database connections:

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:

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

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

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

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

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

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:

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.