Several common ways of accessing database with ASP

Several common ways of accessing database with ASP

ActiveX Data Objects (ADO) is an easy-to-use and extensible technique for adding database access to a Web page. You can use ADO to write compact and concise scripts to connect to Open database Connectivity (ODBC)-compliant databases and OLE DB-compliant data sources. If you are a scripting person with a certain understanding of database connections, you will find that the ADO command statements are not complex and easy to master. Similarly, if you are an experienced database programmer, you will correctly understand the advanced language-independent and query-processing capabilities of ADO.

Creating an ODBC DSN file
Before you create a database script, you must provide a way to locate, identify, and communicate with the database. The database driver uses data Source Name (DSN) to locate and identify specific ODBC-compliant databases and pass information from the WEB application to the database. Typically, a DSN contains database configuration, user security, and location information, and you can get a table in a Windows NT registry key or a text file.

With ODBC, you can select the type of DSN you want to create: User, System, or file. The user and system DSN are stored in the Windows NT registry. System DSN allows all users to log on to a specific server to access the database, and the user DSN uses the appropriate security credentials to restrict the database to a specific user's connection. File DSNs are used to get tables from text files, provide access to multiple users, and can be easily transferred from one server to another by replicating DSN files. For these reasons, the examples in this topic will use File DSNs.

You can create a DSN-based file by opening Control Panel on the Windows Start menu. Double-click the ODBC icon, and then select the File DSN property page, clicking Add, selecting the database driver, and then clicking Next. Follow the instructions later to configure the DSN that applies to your database software.

To configure a file DSN for a Microsoft Access database

In the Create New Data Source dialog box, select Microsoft Access Driver from the list box, and then click Next.
Type your DSN file name, and then click Next.
Click Finish to create the data source.
In the ODBC Microsoft Access 97 Setup dialog box, click Select. Select the Microsoft Access database file (*.mdb), and then click OK.
Note For performance and reliability reasons, we strongly recommend that you use the client-server database engine to configure data driven by such a Web application that must meet simultaneous access by more than 10 users. Although ASP can use any ODBC-compliant database, it is designed for use with client-server databases and has undergone rigorous testing, including Microsoft SQL Server, Oracle, and so on.

ASP supports sharing a file database (such as Microsoft Access or Microsoft FoxPro) as a valid data source. Although some examples in ASP documents use shared file databases, we recommend that you use this type of database engine only for development or limited configuration scenarios. Shared file databases may not be well suited for client-server databases that meet high demand, high-quality Web applications.

　

Configure SQL Server database file DSN

Note If the database resides on a remote server, contact your server administrator to obtain additional configuration information; The following procedure uses the ODBC default settings for SQL Server, which may not apply to your hardware configuration.

In the Create New Data Source dialog box, select SQL Server from the list box, and then click Next.
Type a name for the DSN file, and then click Next.
Click Finish to create the data source.
Type the name, login ID, and password of the server that is running the SQL service program.
In the Create a new data source for SQL Server dialog box, type the name of the server that contains the SQL Server database in the Server list box, and then click Next.
Select how you want to verify your login ID.
If you want to select SQL Server Authentication, enter a login ID and password, and then click Next.
In the Create new data source for SQL Server dialog box, set the default database, the driver for the stored procedure settings, and the ANSI identity, and then click Next. (To get more information, click Help.) ）
In the dialog box (also named "Create a new data source for SQL Server"), select a character conversion method, and then click Next. (For more information, click Help.) ）
In the next dialog box (also named "Create a new data source for SQL Server"), select the login settings.
Note Typically, you can only use logs to debug database access issues.

In the ODBC Microsoft SQL Server Setup dialog box, click Test Data source. If the DSN is created correctly, the test Results dialog box indicates that the test completed successfully.
SQL Server connection and security information
If you are developing an ASP database application to connect to a remote SQL Server database, consider the following issues:

Connection scenarios-You can select a method of TCP/IP sockets and Named Pipes to access a remote SQL Server database. When using named pipes, because database users must be confirmed by Windows NT before a connection is established, users who do not have Windows NT user accounts on the computer with the appropriate SQL Server access may be denied access to the named pipe. As an alternative, a connection that uses a TCP/IP socket can connect directly to the database server without having to use an intermediary computer that uses a named pipe. Because you can connect directly to the database server by using a TCP/IP socket connection, with SQL Server confirmation, the user can gain access without having to pass the confirmation of Windows NT.
Note Using TCP/IP sockets when connecting to a remote database can improve performance.

Security-If you are using the integrated or mixed security features of SQL Server and the SQL Server database is located on a remote server, you cannot use confirmation of Windows NT request/Response. That is, you cannot forward a Windows NT request/Response ID to a remote computer, only Basic authentication, which is based on the user's user name and password information.
For more information on this topic, see http://www.microsoft.com/sqlsupport/default.htm
The Microsoft SQL Server Technical Support Home page on.

　

Configuring Oracle Database File DSN

First, make sure that the Oracle user software is correctly installed on the computer on which you want to create the DSN. For more information, contact your server administrator or consult your database software documentation.

In the Create New Data Source dialog box, select Microsoft ODBC for Oracle from the list box, and then click Next.
Type a name for the DSN file, and then click Next.
Click Finish to create the data source.
Enter the user name, password, and server name, and then click OK.
Note The DSN file uses a. dsn extension, located in the \programs\common files\odbc\data Sources directory.

For more information about creating a DSN file, visit the Microsoft ODBC Web site: http://microsoft.com/odbc/.

Connecting to a database
The first step in accessing database information is to establish a connection with the database source. ADO provides a Connection object that you can use to establish and manage connections between applications and ODBC databases. The Connection object has a variety of properties and methods that you can use to open and close the database connection and issue a query request to update the information.

To establish a database connection, you should first create an instance of the Connection object. For example, the following script creates a Connection object, and then opens the database connection:


Note The DSN string cannot contain spaces, either before or after the equal sign (=).

In this case, the Connection object's Open method references a DSN-based file that contains information about the location and configuration of the database. You can also explicitly reference the provider, data source, user ID, and password without referencing DSN.

Executing a query with a Connection object
With the Execute method of the Connection object, you can send a Structured Query Language (SQL) query to the database source and retrieve the results. SQL is an industrial standard language for communicating with databases, and it has many commands to retrieve and update information.

The following script uses the Connection object's Execute method to issue a query in the table of the SQL Insert command that inserts data into a specific database table. In the following example, the script inserts the name Jose Lugo into a database table named Customers.


Note files that are based on a DSN path string should not contain spaces before or after the equal sign (=).

In addition to the SQL INSERT command, you can use the SQL UPDATE and delete commands to change and delete database information.

With the SQL UPDATE command, you can change the values of items in a database table. The following script uses the UPDATE command to change the FirstName field in the Customers table that contains the Smith record for each LastName field to Jeff.


To delete a specific record from a database table, you can use the SQL Delete command. The following script deletes all the rows of Smith from the Customers table:


Note that you must be cautious when using the SQL DELETE command. When you use the Delete command without a WHERE clause, it deletes all rows in the table. Be sure to include the SQL WHERE clause to specify the exact row you want to delete.

Working with a Recordset object to work with results
Although the Connection object simplifies the connection database and query tasks, there are still many deficiencies in the Connection object. To be exact, a Connection object that retrieves and displays database information cannot be used to create a script; You must know exactly what changes you want to make to the database before you can use the query to implement the changes.

ADO provides a Recordset object for retrieving data, checking results, and changing the database. As its name implies, the Recordset object has many features that you can use, and, depending on your query restrictions, retrieves and displays a set of database rows, that is, records. The Recordset object retains the location of the records returned by the query, allowing you to scan the results one at a time.

Depending on the pointer Type property setting of the Recordset object, you can scroll and update the record. A database pointer lets you navigate to a specific item in a set of records. Pointers are also used to retrieve and check records, and then perform actions on the basis of those records. The Recordset object has properties that can be used to precisely control the behavior of the pointer and improve your ability to examine and update the results. For example, you can use the CursorType and CursorLocation properties to set the type of the pointer, return the result to the client application (the results are usually kept on the database server), and display the other user's last change to the database.

Retrieving records
A successful database application uses the Connection object to establish a link and use the Recordset object to process the returned data. By coordinating the specific features of the two objects, you can open a database application that can perform almost any data processing task. For example, the following server-side script uses the Recordset object to execute the SQL SELECT command. The SELECT command retrieves a set of information based on query restrictions. The query also contains a SQL WHERE clause to narrow the scope of the query. In this example, the WHERE clause restricts the query to the records of the surname Smith that are contained in all Customers database tables.


Note that in the previous example, the Connection object and the Recordset object used to establish the database connection use the connection to retrieve the results from the database. This approach is useful when you need to precisely set the way in which a link is established with the database. For example, if you need to specify the time to wait before a connection attempt fails, you need to use the Connection object to set the property. However, if you only want to use the ADO default connection properties to establish a connection, you should use the Recordset object's Open method to establish the link:


When you use the Open method of a Recordset object to establish a connection, you must use the Connection object to secure the link.

Improve query with Command object
With the ADO command object, you can execute a query as you would with a Connection object and a Recordset object, except that you can prepare, compile, and repeatedly use a different set of values for the Command object to issue the query. The advantage of compiling queries this way is that you can minimize the time it takes to duplicate requests for modifications to existing queries. Alternatively, you can keep the SQL query partially undefined before executing the options for the variable portion of your query.

The parameter collection of Command objects reduces your hassle, making it unnecessary for you to re-establish the query each time the query is re issued. For example, if you need to regularly update supply and price information in a WEB system that is based on an inventory list, you can define the query in the following ways:


Check the example above, and you'll notice that the script builds and emits a SQL query repeatedly with different values, without redefining and resending the query to the database source. Compiling a query with a Command object also avoids the problem of merging strings and table variables caused by SQL queries. In particular, you can avoid problems with defining the type of string, date, and time variable by using the Parameter collection of the Command object. For example, a SQL query value that contains "'" may cause the query to fail:

strSQL = "INSERT into Customers (FirstName, LastName) VALUES (' Robert ', ' O ' Hara ')"
Note that the last name O ' Hara contains a "'" conflict with the "'" used to represent data in the SQL VALUES keyword. You can avoid such problems by binding the query values as Command object parameters.

Combining HTML tables and database access
Web pages that contain HTML tables enable users to query the database remotely and retrieve specific information. With ADO you can create very simple scripts to collect user table information, create custom database queries, and return information to the user. Using the ASP Request object, you can retrieve information entered into an HTML table and incorporate that information into an SQL statement. For example, the following script module inserts the information provided by the HTML table into the table. This script collects user information using the Form collection of the Request object.


For more information about tables and using ASP request objects, see Working with HTML tables.

Managing Database connections
The biggest challenge in designing a test-proof WEB database application, such as an online shopping application for thousands of customer services, is how to manage your database connections properly. Opening and maintaining a database connection, even when there is no information transfer, can severely deplete the resources of the database server and may cause connectivity problems. A well-designed WEB database application reclaims the database connection and compensates for delays due to network congestion.

Timeout the connection
A sudden increase in activity may make the database server unwieldy and greatly increase the time it will be to establish a database connection. As a result, too long connection delays will degrade the performance of the database.

With the connectiontimeout of the Connection object, you can limit the time that the application waits before discarding the connection attempt and sending an error message. For example, the following script sets the ConnectionTimeout property to wait 20 seconds before canceling the connection attempt:

Set cn = Server.CreateObject ("ADODB.") Connection ")
cn. ConnectionTimeout = 20
cn. Open "FILEDSN=MyDatabase.dsn"

The default ConnectionTimeout property is 30 seconds.

Note Before you incorporate the ConnectionTimeout attribute into your database application, make sure that the connection provider and data source support this property.

Shared connections
Web database applications that frequently establish and disrupt database connections may degrade the performance of the database server. ASP supports effective management of connections using the shared features of ODBC 3.5. Connection Sharing maintains open database connections and manages different users to share the connection to maintain their performance and reduce the number of idle connections. For each connection request, the connection pool first determines whether there is an idle connection in the pool. If present, the connection pool returns a connection instead of a new connection to the database.

If you want to add an ODBC driver to a connection share, you must configure the database driver and set the driver's CPTimeout property in the Windows NT registry. When ODBC disconnects, the connection is saved to the pool instead of being disconnected. The CPTimeout property determines the length of time that the connection is retained in the connection pool. If the connection in the pool is kept longer than the CPTimeout setting, the connection is closed and removed from the pool. The default value for CPTimeout is 60 seconds.

You can enable connection pooling for specific ODBC database drivers by creating a registry key that has the following settings to selectively set cptimeout properties:


\hkey_local_machine\software\odbc\odbcinst. Ini\driver-name\cptimeout = Timeout
(REG_SZ, units are in seconds)

For example, the following key sets the SQL Server driver's connection pool timeout to 180 seconds (3 minutes).

\hkey_local_machine\software\odbc\odbcinst. Ini\sql server\cptimeout = 180
Note By default, the WEB server activates the connection pool for SQL Server by setting CPTimeout to 60 seconds.

Using a cross-page connection
Although you can reuse a cross-page connection through a connection that stores an ASP's Application object, it is not necessary to always keep the connection open, nor does it take full advantage of the connection pool. If there are many users who need to connect to the same ASP database application, a good way to do this is to place a cross-page connection string in an ASP's Application object and reuse the database connection. For example, you can specify a connection string in the Application_OnStart event procedure for a Global.asa file, as shown in the following script:


Application.Lock
Application ("ConnectionString") = "FILEDSN=MyDatabase.dsn"
Application.UnLock

Then, write in each ASP file that accesses the database:


To create an instance of a Connection object, use the following script:

cn. Open application ("ConnectionString")
For an open connection, you can write the following script at the end of the page to close the connection:

cn. Close
With a single user needing to reuse a cross-page connection, it is better to use a Session object connection than to use a Application object.

Close connection
To better use connection pooling, you should close the database connection as soon as possible. By default, when the script finishes executing, the connection is terminated. When you no longer need a connection, you can reduce the requirements for the database server and enable other users to use the connection.

You can use the Close method of the Connection object to terminate the connection between the Connection object and the database. The following script opens the connection and then closes it:

Reprinted from: Connaught Gold software Computer network