|
ColdFusion
The CFOUTPUT tag is an interesting ColdFusion phenomenon. When used with
the QUERY attribute it will loop automatically through each record in
the record set returned by the query. The GROUP attribute can be used
to nest and group output much as report generators do. For example,
you could group output of a query of an Employees table on a Department
field. The department name will only come up once. If you nest another
CFOUTPUT tag within the department group CFOUTPUT tags, the inner one
will loop through all of the individual records for the department group.
You can nest output more than one level deep, for example if within the
department you wanted to group by gender.
CFOUTPUT and CFLOOP can both be used to loop over a query. Both have a
STARTROW parameter to define which row in the query to start the loop at.
CFOUTPUT has MAXROWS and CFLOOP has ENDROW to define when to stop the
looping if it should not continue to the end of the query results.
ASP
The native databases for ASP are Microsoft SQL Server and Microsoft Access. All
of the database functions provided by ASP are supported in these databases. ASP
can also use other databases that have suitable ODBC drivers, but not all of the
ASP database access capabilities (such as jumping freely from record to record
within a Recordset or "bookmarking" records in the Recordset) will be supported
by other databases.
Through a server component called the Database Access Component (DAC) you
access the ActiveX Data Objects (ADO). The ADO are a set of objects that
you can use to access databases. To facilitate working with the methods
and properties of these objects, Microsoft provides a VBScript and a
Javascript version of a common file which will declare a set of related
constants. You can simply include the appropriate file with the ASP #include command.
You set up to connect to the database from your ASP program by creating
a connection object. You pass the ODBC Data Source Name (DSN) to the
Open method of the connection object to open the database for use. If
necessary, you can also pass a user ID and password as arguments to the
Open after the DSN.
The connection object has an Execute method to which you can pass SQL
commands, and a Recordset object is returned. Alternately, you can
create your own Recordset object explicitly and then retrieve records
from the database. In this case, rather than using the connection
object’s Execute method, you will pass the SQL and the connection
object to the Recordset object’s Open method. The former approach
provides a Recordset with the default access characteristics. The
latter allows you to set a parameter which will define the access
methods which may be applied to the Recordset. The options are:
- Your set of records remain untouched by subsequent updates of the
same records in the origional source data, and you can only start at
the beginning and process records one by one to the end. This is the
default type of access
- No updating as above, but you can freely move backward as well as
forward and jump to different records.
- Any records modifications, adds and deletes done to the origional
source records while you’re using your Recordset will, if they were
covered by your search criteria, be automatically replicated in your
Recordset. Movement between records as in #2 above. This type of access,
while more flexible, requires the most overhead and resources.
If you choose one of the access options that provides free movement
between records, then in addition to the default MoveNext, the following
move methods are available: MoveFirst, MoveLast and MovePrevious as well
as Move which lets you go a specified number of records forward or backward
from the current record or from an explicitly designated noncurrent one
(first, last or a bookmarked record). The Bookmark property of the
Recordset object is an interesting ASP capability. You can tag any
number of rows in the Recordset with identifiers that will allow you
to jump directly to them by name.
The ASP Recordset object has a Filter property with which you can
temporarily limit the visible records in your Recordset to those
with specified value ranges, much like performing a SQL subsearch.
You can remove the filter to see all records again or apply a different
one, all without requerying the database.
Requerying options include the Requery and Resync methods of the
Recordset object. The Requery executes the origional query again.
Resync (lowest overhead) refreshes the records that are in the Recordset with any applicable changes, but does not add any new ones that would otherwise be included with a Requery.
Using the Connection object’s Execute method you can use standard
SQL commands to add, change and delete records in the database. ASP provides another method for databases that support it (SQL Server and PWS do). The Recordset object has methods that let you add, change and delete records in the database itself through the records in a Recordset.
AddNew
| |
The Recordset’s AddNew method creates a new, blank row in the Recordset and positions itself on that row. First fill in the required fields and then call the Update method which will create a new record in the database to match the one you just created in your Recordset.
|
Update
| |
You navigate to the record you want to change in your Recordset. The Recordset’s fields can be referenced by name much as if it were an associative array. You change a field value in the current record with a command like
MyRecordsetName("ThisFieldName") = "The New Value"
After you’ve changed all the fields you want, you execute
MyRecordsetName.Update
and Update will perform changes to the fields of the corresponding record in the database.
|
Delete
| |
You navigate to the record you want to delete in your Recordset. Then a command like
MyRecordsetName.Delete
will delete the record in your Recordset and also the corresponding one in the database.
|
Database errors can be trapped using On Error Resume Next and the Err object (which holds information only about the one most recent error) or the Errors collection which is a property of the ADO Connection object. If more than one error results from a faulty statement sent to the database, the Errors collection will contain an Error object for each one of the errors.
The most efficient management of database resources for ASP is achieved at the server level. The normal practice is to create a database connection at the top of every page that uses the database. The default behavior of an ASP database connection is to close when the page that opened it finishes executing. This means that your application will be constantly opening and closing connections to the database. It may seem logical to create a session-level connection as a way to decrease that drain on resources. However, both IIS and PWS offer a better alternative, connection pooling, which provides much more efficient resource allocation. When connection pooling is on, the server doesn’t automatically close your connection to the database when the Connection object goes out of scope. The connection is kept open for a while in case your page or one in another session needs it in the near future. The server may even use the same connection among multiple concurrent processes.
PHP
PHP supports APIs for accessing a large number of different databases including Oracle, Sybase, MySQL and many more. Alternately, you can use ODBC with PHP to write generic database applications (the code will work for all databases with suitable ODBC drivers). However, some database features will be available only if you use the PHP language API for that database rather than ODBC.
As with ColdFusion and ASP, some database commands or functions offered by PHP may not be supported by a given database. For example, MySQL (a popular choice with PHP on UNIX) doesn’t support transactions (rollback, commit), stored procedures and triggers, views, sub-selects, or foreign keys.
PHP supports (optional) "persistent" database connections which is similar to ASP’s "connection pooling."
To process records in a record set returned by a query, PHP has API-specific and ODBC versions of functions to fetch the current row or to set the internal pointer in the record set to a specified row number (if the database supports this). For example, with MySQL, mysql_data_seek() moves the internal row pointer of the MySQL result associated with the specified result identifier to point to the specified row number. The next call to mysql_fetch_row() would return that row.
mysql_fetch_row gets a result row as an enumerated array. mysql_fetch_array() is an extended version of mysql_fetch_row(). In addition to storing the data in the numeric indices of the result array, it also stores the data in associative indices, using the field names as keys.
By default, each subsequent call to either of the fetch_row() or fetch_array() functions will get the next record, so you iterate forward through a dataset until the function returns false.
|
gallery
