Stored Procedure | access | data | database one, ADO overview
ActiveX data Object (ADO) is a technology that is both easy to use and extensible, use to add database access to your Web page you can use ADO to write concise and scalable scripts to connect to OLE DB-compliant data sources, such as databases, spreadsheets, sequential data files, or e-mail directories. OLE DB is a system-level programming interface that provides a standard set of COM interfaces to showcase the capabilities of a database management system. With the object model of ADO, you can easily access these interfaces (using scripting languages such as VBScript or JScript) and add database functionality to your WEB application. In addition, you can use ADO to access databases that are compatible with open Database interconnection (ODBC).
If you're a scripting writer who knows a lot about database interconnect, you'll find that the command syntax for ADO is simple and easy to use. If you are an experienced developer, you will appreciate the scalable, high-performance access to a variety of data sources that ADO provides.
Ii. general methods of accessing the database
General Web Access to access the database is done in accordance with the following steps, first set up an object of the Adodb.connection interface, and then bind the corresponding data source on this object (you can use a well-known data source and unknown data source), as needed to establish or not set up a recordset, Then, on the data source, the link is used to manipulate the corresponding table using the method that is executed or opened.
General methods for ASP access to databases:
<
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.execute ("SELECT * FROM Test")
>
The above describes the anonymous link to the use of OLE DB, if it is a well-known link, set to data source test, user name and password are empty, then only need to rewrite the above statement oconn.open "test", "", "" "
This is a brief introduction to the general approach to database access in Web pages, these are already very mature, and very useful, if in fact for some data access is too complex, need nearly 10 SQL statements to finish, then this method is a bit deficient, and sometimes need to use the same process and used in different Web pages, which is most advantageous with stored procedures, and one of the biggest features is that stored procedures are relatively confidential to technology and are stored in the database of remote servers.
Third, the use of stored procedures in ASP
The access to a stored procedure is provided in ADO, which requires a command object on which the user can execute the SQL Server's stored procedure directly, and the parameters required in the command can be processed by virtue of its property pamaters.
Note that a command object must be associated with a Connection object in order to be valid, by means of the ActiveConnection property of the Command object that is set to the Connection object. If a Connection object cannot be identified, the Command object is not valid until 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, use the User ID SA, the password is null, connect as a 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, user is TMP, password is 123,
' You can use the following methods
' 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 active Link property of the command
Set ocmd.activeconnection = oconn
' Set call stored procedure byroyalty and parameters, parameter by? Introduced
Ocmd.commandtext = "{call byroyalty (?)}"
OCmd.Parameters.Append ocmd.createparameter ("@Percentage", adinteger, adParamInput)
' Provide the input parameters
Ocmd ("@Percentage") = 75
In ASP, the above input parameter can be used without parameter attribute to form the corresponding data in the command text by using the & in VB directly.
' The use of parameters is particularly useful in the output
Set ORs = Ocmd.execute
Ors1.activeconnection=oconn
' The Command object can also apply a generic SQL statement using the source and open properties, where source indicates that 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
The SQL language allows you to write stored procedures for database access, with the following syntax:
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 the required option,
Example: If a user wants to create a stored procedure that deletes records from table tmp Select_delete can be written as:
Create Proc Select_del as
Delete tmp
Example: A stored procedure that a user wants to query data for a year 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: The stored procedure is to start from a node n to find the highest level of the Father node, this often used process can be played by the stored procedures, in the Web page reuse to share.
Null: Indicates that the node is a top-level node.
Fjdid (parent node number)
Node n non-null: The Father node number representing the node
DWMC (unit name)
CREATE proc SEARCH_DWMC @dwidold int, @dwmcresult varchar (MB) 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)
Fully and rationally utilize stored procedures, can improve the capacity of the server throughput, the author uses stored procedures to obtain data from nearly 20 tables into a common table, resulting in nearly 20,000 records, and the time required about 7 seconds, if this operation with the help of more reasonable dynamic web pages can be increased to a high level of server development, Take full advantage of stored procedures, can reduce the heavy processing of web page design, and so that the code is written to be shared and reasonable use, and the code hidden in the database inside the server, so that some technology is confidential, which is a major feature of the storage process, I hope that readers can get the benefit.
Turn from: http://goaler.xicp.net/ShowLog.asp?ID=504
