Use stored procedures to implement ASP access to databases

I. Ado Overview

ActiveX Data Object (ADO) is an easy-to-use and scalable technology, to add database access to your web page, you can use ADO to write simple and upgradeable scripts to connect to data sources compatible with ole db, such as databases, workbooks, ordered data files, or email directories. Ole db is a system-level programming interface that provides a set of standard com interfaces to demonstrate the functions of the database management system. With the ADO object model, you can easily access these interfaces (Using VBScript or JScript or other scripting languages) and add database functions to your web applications. In addition, you can use ADO to access databases compatible with Open Database Interconnection (ODBC.

If you are a script writer who has some knowledge about database interconnection, you will find that the ADO command syntax is very simple and easy to use. If you are an experienced developer, you will appreciate the upgraded high-performance access to various data sources provided by ADO.

II. General methods for accessing databases

Generally, the following steps are taken to access the database during webpage access. First, an ADODB is created. the connection interface object, and then bind the corresponding data source on this object (you can use the famous data source and the unknown data source), create or do not create a record set as needed, then, link to the data source and use the execution or open method to operate the corresponding table.

Common Methods for ASP database access:

<

Set oconn = server. Createobject ("ADODB. Connection ")

Set ors = server. Createobject ("ADODB. recordset ")

Set strconn = "provider = sqloledb; user id = sa; initial catalog = pubs; Data Source =" & request. servervariables ("SERVER_NAME ")

Oconn. Open strconn

Set ors‑oconn.exe cute ("select * from test ")

>

This section describes how to use oledb with an unknown link. If it is a famous link, set it as the data source test, and the user name and password are empty, you only need to rewrite the above statement oconn. open "test ","",""

The above briefly introduces the general methods for accessing databases on the web page. These methods are mature and useful. If the actual access to some data is too complex, this method requires nearly 10 SQL statements to be written. In addition, the same processing process needs to be used in different web pages, the stored procedure is the most advantageous, and the biggest feature is that the stored procedure has a relatively high technical confidentiality and is stored in the database of the remote server.

Iii. Use of stored procedures in ASP

ADO provides access to the stored procedure. It requires the command object. On this object, you can directly execute the stored procedure of the SQL Server, the parameters required in the command can be processed by pamaters.

Note that a command object must be associated with a connection object to be valid. The method is that the activeconnection attribute of the command object is set to this connection object. If a connection object cannot be identified, the command object is invalid before you associate it with a connection.

<Dim oconn

Dim strconn

Dim ocmd

Dim ors, ors1

Dim AA

Dim SQL

Set oconn = server. Createobject ("ADODB. Connection ")

Set ocmd = server. Createobject ("ADODB. Command ")

Set ors1 = server. Createobject ("ADODB. recordset ")

'Open the link and use the user id sa. The password is blank and the connection is the database on the local server.

Strconn = "provider = sqloledb; user id = sa; initial catalog = pubs; Data Source =" & request. servervariables ("SERVER_NAME ")

'If you connect to a remote database, the database address is 10.82.88.105, the user is TMP, And the password is 123,

'The following method is available

'Strconn = "provider = sqloledb; user id = TMP; Pwd = 123; initial catalog = tjbb; Data Source =" & "10.82.88.110"

Oconn. Open strconn

'Add the established link to the activity link attribute of the command.

Set ocmd. activeconnection = oconn

'Set the call Stored Procedure byroyalty and parameters. Which of the following parameters? Introduction

Ocmd. commandtext = "{call byroyalty (?)} "

Ocmd. Parameters. append ocmd. createparameter ("@ percentage", adinteger, adparaminput)

'Provide input parameters

Ocmd ("@ percentage") = 75

'The use of the preceding input parameters in ASP can also be achieved without using the parameter attributes to directly use & in VB to form the corresponding data in the Command text.

'Parameter usage is particularly useful in output

Set ors = ocmd. Execute

Ors1.activeconnection = oconn

'The Common SQL statements can also be applied to this command object to use the source and open attributes. The source indicates the data source.

Ors1.source = "select * from [tmptable] Where year = 2000 and month = 1"

Ors1.cursortype = adopenstatic

Ors1.open

>

Iv. Introduction to stored procedures in SQL

You can use the SQL language to write stored procedures for database access. The syntax is as follows:

Create proc [edure] procedure_name [; number]

[

{@ Parameter data_type} [varying] [= default] [Output]

]

[,... N]

[

{

Recompile

| Encryption

| Recompile, encryption

}

]

[For replication]

As

SQL _statement [... n]

The content in [] is optional, and the content in () is required,

For example, if you want to create a stored procedure for deleting records in Table TMP, select_delete can be written as follows:

Create proc select_del

Delete TMP

For example, you want to query the data stored in the TMP table for a certain year.

Create proc select_query @ year int

Select * from TMP where year = @ year

Here @ year is the parameter of the stored procedure

For example, the stored procedure starts from a node N and finds the parent node at the top. This frequently used process can be undertaken by the stored procedure and shared by repeated use on the webpage.

Null: indicates that the node is a top-level node.

Fjdid (parent node number)

Node N is not empty. It indicates the parent node number of the node.

Dwmc (unit name)

Create proc search_dwmc @ dwikidold int, @ dwmcresult varchar (100) Output

As

Declare @ stop int

Declare @ result varchar (80)

Declare @ dwmc varchar (80)

Declare @ dwid int

Set nocount on

Set @ stop = 1

Set @ dwmc = ""

Select @ dwmc = dwmc, @ dwid = convert (INT, fjdid) from jtdw where id = @ dwidold

Set @ result = rtrim (@ dwmc)

If @ dwid = 0

Set @ stop = 0

While (@ stop = 1) and (@ dwid <> 0)

Begin

Set @ dwidold = @ dwid

Select @ dwmc = dwmc, @ dwid = convert (INT, fjdid) from jtdw where id = @ dwidold

If @ rowcount = 0

Set @ dwmc = ""

Else

Set @ result = @ dwmc + @ result

If (@ dwid = 0) or (@ rowcount = 0)

Set @ stop = 0

Else

Continue

End

Set @ dwmcresult = rtrim (@ result)

The full and reasonable use of the stored procedure can improve the server throughput. The author uses the stored procedure to obtain data from nearly 20 tables and combine them into a common table, resulting in nearly 20 thousand records, however, it takes about 7 seconds. If such an operation uses a reasonable dynamic web page, the server development can be increased to a high level. taking full advantage of the stored procedure can reduce the heavy processing caused by web design, in this way, the written code is shared and reasonably used, and the code is hidden in the database of the server, so that some technologies are kept confidential. This is also a special feature of the stored procedure, hope that readers can get some benefits.

From: http://goaler.xicp.net/ShowLog.asp? Id = 504