First, ADO Overview
ActiveX Data Object (ADO) is a useful technique that is used to add database access to your web page to use ADO to write a simple and upgraded script to connect to the OLE DB compatible data source. Such as databases, spreadsheets, sequential data files, or email directories. OLE DB is a system-level programming interface that provides a standard COM interface to show the functionality of the database management system. Using ADO's object model, you can easily access these interfaces (using scripting languages such as VBScript or JScript) and add database functions into your web application. Alternatively, you can use ADO to access the Open Database Interconnect (ODBC)-compatible database.
If you are a script writer with a certain understanding of database interconnect, you will find that ADO's command syntax is simple, and it is easy to use. If you are an experienced developer, you will enjoy this upgraded high-performance access to the ADO.
Second, the general method of accessing the database
The access database in general web-access is based on the following steps, first create an object of an AdoDb.Connection interface, followed by binding the corresponding data source (available data sources and non-name data sources) on this object. Establish or do not establish a recordset, and then operate on the corresponding table on the link to perform or open on the data source.
ASP access to the database:
<
Set Oconn = Server.createObject ("AdoDb.Connection")
SET ORS = Server.createObject ("AdoDb.Recordset")
SET STRCONN = "provider = SQLOLEDB; user ID = sa; initial catalog = pubs; data source =" & required.serverVariables ("server_name")
Oconn.open straconn
SET ORS = Oconn.execute ("SELECT * from test")
>
The above introduces the use of the OLEDB of the unnamed link, if it is a famous link, set to the data source TEST, the username, and passwords are empty, just rewrite the above statement oconn.open "test", "" "" "
The above brief introduction to the general method involving database access in the web page, which is already very mature, it is also useful, if in the actual access to some data is too complicated, need nearly 10 SQL statements to write After it, this method is a bit lack, and sometimes you need the same processing process, and use in different web pages, and this stored procedure is the most advantageous, and there is a maximum feature is that the stored procedure for technology Confidentiality is relatively high, it is stored in the database of remote servers.
Third, the stored procedure is used in ASP
Access to the stored procedure is provided in the ADO, which requires the Command object, and the user can directly execute the stored procedure of the SQL server directly, and the parameters required in the command can be processed by means of its attribute pamaters.
Note that a Command object wants to be valid, must be associated with a Connection object, the method is the COMMAND object's ActiveConnection property 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 STRCONNNNNNN DIM OCMD Dim Ors, Ors1Dim AA DIM SQL Set Oconn = Server.createObject ("AdoDb.Connection") Set Ocmd = Server.createObject ("AdoDb.command") SET ORS1 = Server.createObject ("AdoDb.Recordset") 'Open the link, use the user ID SA, password is empty, connected to the database on the local server StrConn = "provider = SQLOLEDB; User ID = sa; initial catalog = pubs; data source =" & required.serverVariables ("server_name") 'If you connect a remote database, the database address is: 10.82.88.105, the user is TMP, the password is 123, 'The following method can be used 'Strconn = "provider = SQLOLEDB; User ID = TMP; PWD = 123; Initial Catalog = TJBB; Data Source =" & "10.82.88.110" Oconn.open straconn 'In the activity link properties of the created link to the command Set Ocmd.activeConnection = Oconn 'Set the calling process Byroyalty and parameters, parameters Introduce Ocmd.commandtext = "{CALL BYROALTY (?)}" Ocmd.parameters.Append Ocmd.createParameter ("@ percentage", adINteger, adparaminput) 'Provide input parameters OCMD ("@ percentage" = 75 'The use of the above input parameters in the ASP can also be implemented without the parameter properties to implement the data corresponding to the data in the VB to form the data in the command text. 'Use of the use of parameters is particularly useful in output SET ORS = OCMD.EXECUTE Ors1.activeConnection = OCONN 'This command object can also be applied using the Source and Open properties in the command object, where SOURCE points out the data source Ors1.Source = "Select * from [tmptable] where year = 2000 and month = 1" Ors1.cursortype = AdopenStatic Ors1.open > 4. Introduction to the stored procedure in SQL Use SQL language to write a stored procedure for database access, which syntax is as follows: Create Proc [edure] procedure_name [; Number] [ {@Parameter Data_Type} [varying] [= default] [OUTPUT] ] [, ... n] [With { Recompile | Encryption | Recompile, Encryption } ] [For replication] AS SQL_STATEMENT [... N] The content within [] is optional, and the content within () is a must-have. Example: If the user wants to create a stored procedure in the recording in the table TMP, SELECT_DELETE can be written as: Create Proc SELECT_DEL Asdelete TMP Example: The user wants to query the stored procedure of the data in the TMP table Create Proc SELECT_QUERY @Year INT AS Select * from tmp where year = @ Year Here @Year is the parameter of the stored procedure Example: This stored procedure starts from a node N to find the uppermost father's node, which is often used by the stored procedure, reused in the web page. Empty: indicates that the node is the top layer FJDID (Father Node) Non-empty: indicates the father's node number of the node DWMC (Name) Create Proc Search_dwmc @dwidold 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) Using the stored procedure, the server throughput can be improved. The author uses the stored procedure to obtain data into a general table from nearly 20 tables, producing nearly 20,000 records, while the time required for about 7 seconds, if This operation can improve the server development to a height with a relatively reasonable dynamic web page, and make full use of the stored procedure, reducing the heavy processing of web design, and makes the written code get sharing and reasonable utilization, and will code Inside the server's database, some technologies are confidential, which is also a major feature of the stored procedure, and I hope the reader can get it from it.
