三层结构及数据库在AW中的运用

Database tracking and recording with Authorware Web Player

Introduction
The Authorware Web Player can send data to a server-side database with the PostURL function. This TechNote describes how to collect, transfer, and record data to a database located in a remote location on a network

You need to use software on the server to connect a database to the Internet. This type of software is often referred to as "middleware" because it functions in between the server and the client. Examples include Microsoft Active Server Pages (ASP) and ColdFusion. The client in this case is the Authorware Web Player.

The topics in this article cover the following procedures: Setting up a data source on the server with database software and ODBC.
Setting up the middleware to connect the data source to the network.
Collecting the data that you wish to track using Authorware variables.
Using the PostURL function to transfer the data back to the server.


See Developing for the Web: A primer for more information about creating Web-Packaged Authorware pieces.

Creating the data source
The data source is the combination of the database file and the path to it. Regardless of the database software used, it only accepts data in an expected format. For example, if you are collecting and transferring the student's name, password, and score, your database file must have a table with a matching set of fields to store that information. A path to the database file, with read and write access, must be available to the Web server.

Consult the documentation for your database software to learn how data sources are defined and accessed.

Using ODBC
Open Database Connectivity (ODBC) is a widely accepted standard for database access. It uses Structured Query Language (SQL) as its database access language. Many database software products on the market today support ODBC. Active Server Pages (ASP), ColdFusion, and a number of Common Gateway Interface (CGI) programs support database queries through ODBC and SQL. The database examples in this article use an ODBC data source.

Choosing a database and middleware that supports ODBC allows you to change your database software at a later date without modifying the data collection or transfer methods. You can even use the same ODBC data source with Macromedia Generator and the Flash Xtra for Authorware to display customized graphics driven by the user's interactions.

Collecting data
Use variables to track any data in your piece that you wish to record. You can then send the values contained in variables to the database. For example, suppose you want to track the student's name, password, and score in a quiz. Use the following statements in a calculation icon in the Authorware piece:


UserName := EntryText@"TypeFullName"
Password := EntryText@"TypePassword"
Score := TotalScore

The student's name and password—stored in the UserName and Password variables—are taken from the student's responses to two text entry interactions set up in interaction icons named TypeFullName and TypePassword. The student's score—stored in the Score variable—is the sum of all interaction scores in the Authorware piece. See "System Variable" in the Authorware 5 Help Pages for more information about the EntryText and UserName variables.

Using PostURL to transfer data
Use the PostURL function to transfer the data to the server. Add the following statements to a calculation icon to send the collected data to the server:

-- This example uses ColdFusion as the middleware
URL_string := "http://cf.server.com/CFDOCS/InsertAction.CFM"
command_string := "username="^UserName^"&"^Return^"password="¬^password^"&"^Return^"score="^score^"&"^Return
result := PostURL( URL_string , command_string )
-- WriteExtFile("C:\\Windows\\Desktop\\result.htm", result)

In the first statement, URL_string contains the location of the file used by the middleware to communicate with the database. The InsertAction.CFM is a text file containing statements executed by the ColdFusion application on the server.

URL_string := "http://cf.server.com/CFDOCS/InsertAction.CFM"
In the second statement, command_string contains the data in the format expected by the middleware and database:

command_string := "username="^UserName^"&"^Return^"password="琟password^"&"^Return^"score="^score^"&"^Return
In the third statement, PostURL sends the contents of command_string to the location specified in URL_string:

result := PostURL( URL_string , command_string )
Remove the comment (--) from the last line to save the result of the call to PostURL in a local text file. Doing so is useful for testing or troubleshooting the database connection:

WriteExtFile("C:\\Windows\\Desktop\\result.htm", result)
Transferring data using ColdFusion
ColdFusion Application Server is a platform for building and deploying dynamic Web sites and applications that eliminates the need to create complicated CGI scripts. It works with any major Web server on Windows or Solaris. For information about full-featured and trial versions of the ColdFusion Application Server, visit the Allaire Web site.

As mentioned in the previous section, the URL_string variable contains the location of the file used by ColdFusion to communicate with the database. The file, InsertAction.CFM, contains the ColdFusion instructions to insert the tracked data into the database on the server. Add the following statements to the InsertAction.CFM file to insert the contents of the UserName, Password, and Score variables into the database:



INSERT INTO Quiz1
(username,
password,
score)
valueS ('#Form.username#',
'#Form.password#',
'#Form.score#')


Only the username, password, and score are passed back to the server to minimize the number of transactions that the server needs to execute. ColdFusion takes care of inserting the values into the username, password, and score fields in a database table named Quiz1. The Quiz1 table is defined in the data source named aw5quiz.

Transferring data using ASP
Microsoft IIS 3.0 and ASP allow Web applications to interact with ODBC-compliant databases. The methods required to use ASP for tracking Web-packaged Authorware pieces are similar to using the ColdFusion Application Server. ColdFusion uses CFM files to interface with the database. The basic differences are that an ASP file contains the database instructions and the URL_string variable is set to the location of the ASP file:


URL_string := "http://nt.server.com/scripts/Quiz1.asp"

The Quiz1.asp file contains the instructions to initialize a connection to the database. The ASP file opens a connection to a data source named aw5quiz. The data source is defined using the ODBC control panel on the server computer. Add the following statements to the Quiz1.asp file to initialize a connection to the database:


<%
If IsObject(Session("aw5quiz_conn")) Then

Set conn = Session("aw5quiz_conn")

Else

Set conn = Server.CreateObject("ADODB.Connection")
conn.open "aw5quiz","",""
Set Session("aw5quiz_conn") = conn

End If
%>

The Quiz1.asp file also contains the following instructions to insert the tracked data into the username, password, and score fields in a database table named Quiz1. The Quiz1 table is defined in the data source named aw5quiz. This example uses the same data source used in the ColdFusion example.

<%
username = Request.Form("username")
password = Request.Form("password")
score = Request.Form("score")
sqlstr= "INSERT INTO [Quiz1] "
sqlstr= sqlstr & "(username, password, score)"
sqlstr= sqlstr & "valueS "
sqlstr= sqlstr & "('" & username & "','" & password & "','" & score & "'" & ")"
Set rs = conn.execute(sqlstr)
Set RS = nothing
%>
Note that inserting values into the database is done with the same SQL INSERT command used with ColdFusion. ASP retrieves values from the command form with the following syntax:


value=Request.Form("fieldName")

The SQL command is held in a string variable named sqlstr. ASP executes the SQL command when it encounters this statement:


conn.execute(sqlstr)

For more information about ASP, visit www.learnasp.com/.

Transferring data with other transport methods
By modifying the URL_string used with the PostURL function, you can transfer data with any Web transport that accepts form data. Examples of such transports include Common Gateway Interface (CGI), Internet Database Connector (IDC), ActiveX Data Objects (ADO), and many others. With careful planning, you can isolate the database from the transport used to add, delete, and update records.

Testing your Authorware piece
You'll need to rigorously test your Web-packaged piece until you reach an acceptable level of performance. Only then can you be sure that your intended audience will interact with your piece and have their interactions recorded as you expect. Your testing should cover a variety of common network conditions:

Connection speed
Test your application over modem and higher-bandwidth connections.
Concurrent users
Test with multiple users accessing the server at the same time.
Low-memory conditions
Test your application with the server running at maximum capacity.