How ASP stored procedures are used

Stored Procedures use Command object and parameter object to pass parameters

The main use of the Microsoft SQL Server7.0 database, the first to establish a connection file adosql7.asp standby, in the future when the time is not specifically described.

<% ' adosql7.asp

Option Explicit

Response.Expires = 0

"The first part: establish the connection

Dim Cnn, strcnn

Set Cnn = Server.CreateObject ("ADODB.") Connection ")

STRCNN = "PROVIDER=SQLOLEDB; User Id=sa; password=; Initial catalog=pubs; Data SOURCE=ICBCZJP "

Cnn.open strcnn

%>

Note: When you use it, set the data source to the name of the machine where your database server is located.

In addition, when using an Access database, you can easily view fields and data with Microsoft ACCESS97, while using a SQL Server database, especially if you are debugging ASP scripts on another machine instead of the database server, To see the fields and data you need to install additional tools, here is a tool for you: Msqry32.exe (Microsoft Query), which is installed with OFFICE97, typically under the directory "Microsoft Office\Office".

Example wuf70.asp:

<%@ language= "VBSCRIPT"%>

<!--#include file= "adosql7.asp"-->

<!--#include file= "Adovbs.inc"-->

<% ' wuf70.asp

Dim cmdtest, Prmtest, rstest

' Create Command Object

Set cmdtest = Server.CreateObject ("Adodb.command")

' Recordset, Command object can connect connection object by ActiveConnection property

Cmdtest.activeconnection = Cnn

' SQL command-contains two parameters, with? Said

Cmdtest.commandtext = "Update jobs Set Job_desc =?" Where job_id =? "

' Set command type to SQL statement

Cmdtest.commandtype = adCmdText

The ' Prepared property determines whether the SQL command is precompiled, set to true, and can be run faster

cmdtest.prepared = True



' Create Parameter Object

Set prmtest=cmdtest.createparameter ("Job_desc", advarchar,adparaminput,50, "network")

"Append data to the Parameters data collection

CmdTest.Parameters.Append Prmtest

Set prmtest = Cmdtest.createparameter ("job_id", Adsmallint,adparaminput,, "12")

CmdTest.Parameters.Append Prmtest

' Perform the modification-no need to return the result, simply use the Cmdtest.execute.

Cmdtest.execute

' Reset the parameter run-you can modify another piece of data

Cmdtest.parameters ("job_id") = "1"

Cmdtest.parameters ("job_desc") = "Test"

Cmdtest.execute

' Reset parameter to run

Cmdtest ("job_id") = "14"

Cmdtest ("job_desc") = "Finance"

Cmdtest.execute

Set rstest = Cnn.execute ("Select Job_id,job_desc from Jobs")

While not rstest.eof

Response.Write rstest (0) & Rstest (1) & "<br>"

Rstest.movenext

Wend

Cnn.close:Set prmtest = Nothing

Set cmdtest = Nothing:set Cnn = Nothing

%>

Analysis:

1. The CreateParameter method of the Command object is used to establish a parameter object for the SQL command or stored procedure, with five parameters (five arguments are optional):

First parameter: The name of the parameter object;

The second parameter: Parameter object's data type, the kind is too many, still refer to ADO help, here adVarChar (string value), adSmallInt (2 byte signed integer);

Third parameter: parameter type. Can be: adParamInput (indicates input parameter), adParamOutput (indicated as output parameter), adParamReturnValue (indicated as return value), Adparamunknown (indicates argument type cannot be determined), adParamInputOutput (indicated as input/output parameters);

Fourth parameter: The data length of the parameter, preferably specified to be equal to the corresponding field length in the database to avoid error when using, especially when the data type is varchar, if it is an integral type or date type, it is not necessary to supply the value;

Fifth parameter: The setting initial value of the parameter.

2. The CmdTest.Parameters.Append method adds a Parameter object to the Parameters data collection, and you can see how to use multiple parameters in this example.

3. From this example, we can see that only need to reset the input parameters, you can perform changes to other data, very convenient, this idea in programming is also one of the most commonly used methods.

4. Reset parameters can either be cmdtest.parameters or omitted as Cmdtest ("job_id").

second, the use of stored procedures in ASP

What is a stored procedure (stored procedure in a database server, a collection of SQL statements that can contain one or more SQL statements), and how to create a stored procedure that is not part of this lecture is an example of how to invoke a stored procedure in ASP.

The benefits of using stored procedures are significant, and stored procedures are more efficient than running SQL commands in ASP scripts, improving overall performance and easing network load (reducing interaction between network servers and data servers), optimizing ASP code and enhancing code flexibility, and more.

(i) Use of input parameters in stored procedures

The stored procedure used in this example is the "byroyalty" of SQL Server7.0, where an SQL statement is simple, with one more create PROCEDURE byroyalty, and one input parameter is @percentage:

CREATE PROCEDURE byroyalty @percentage int

As

Select au_id from titleauthor

where Titleauthor.royaltyper = @percentage

Serve by www.cidu.net

Case wuf71.asp

<% @LANGUAGE = VBScript%>

<!--#include file= "adosql7.asp"-->

<!--#include file= "Adovbs.inc"-->

<% ' wuf71.asp

Dim cmdtest, Prmtest, rstest

Set cmdtest = Server.CreateObject ("Adodb.command")

Cmdtest.commandtext = "byroyalty" ' Stored procedure name

' Set command type as stored procedure

Cmdtest.commandtype = adCmdStoredProc

' Create Parameter Object

Set prmtest = Server.CreateObject ("ADODB. Parameter ")

The ' Type property corresponds to the second parameter in the wuf70.asp

Prmtest.type = Adinteger ' 4-byte signed integer

' Direction property corresponds to the third parameter in the wuf70.asp

Prmtest.direction = adParamInput

' Value property corresponds to the fifth parameter in the wuf70.asp

Prmtest.value = 30

CmdTest.Parameters.Append Prmtest

Set cmdtest.activeconnection = Cnn

' Need to return a recordset, so use set rstest = Cmdtest.execute

Set rstest = Cmdtest.execute

While not rstest.eof

Response.Write rstest (0) & "<br>"

Rstest.movenext

Wend

Cnn.close

Set rstest = Nothing:set Prmtest = Nothing

Set cmdtest = Nothing:set Cnn = Nothing

%>

The CommandText property can specify either a SQL command or a stored procedure or a table name.

In this case, creating a Parameter object is slightly different from wuf70.asp, which in fact takes a closer look, and in this case two properties are not used: Prmtest.name, prmtest.size, plus type, direction, and value. Corresponds to five parameters in the wuf70.asp.

(ii) Use of output parameters

When you get a record from a database table or calculate a value, you need to use a stored procedure that returns an output parameter. For example, first create a new stored procedure Outemploy in the pubs Library of SQL Server, which requires entering two dates and then outputting a maximum value.

CREATE PROCEDURE Outemploy

(

@job_lvl tinyint OUTPUT,

@hire_date1 datetime,

@hire_date2 datetime

)

As

Select @job_lvl = MAX (JOB_LVL) from employee

where Hire_date >= @hire_date1 and hire_date <= @hire_date2

There are several ways to establish a stored procedure:

1. Using Microsoft SQL Server's Enterprise Manager, open the tree directory on the left in turn: Console root–microsoft SQL Servers–sql Server Group–icbczj P (Windows NT) –databases–pubs–stored procedure–new stored procedure, the input stored procedures, you can also detect the grammar;

2. Using Query Analyzer for Microsoft SQL Server, first connect to the database server and select the pubs database. Enter the above stored procedure and click Execute Query (or press F5);

3. With VB6.0, when you open the menu view/Data View window, right-click the data link/new data link;

4. Use ASP script to create stored procedures, example wuf75.asp:

<% @LANGUAGE = VBScript%>

<!--#include file= "adosql7.asp"-->

<!--#include file= "Adovbs.inc"-->

<% ' wuf75.asp

Dim strSQL

' Note: & Chr & Chr (13) completely can not, mainly for good-looking

Strsql= "CREATE PROCEDURE outemploy (@job_lvl tinyint OUTPUT," & Chr (A) & Chr (&_)

"@hire_date1 datetime, @hire_date2 datetime) as" & Chr (A) & Chr (&_)

"Select @job_lvl = MAX (JOB_LVL) from employee" &_

"Where Hire_date >= @hire_date1 and hire_date <= @hire_date2"

Cnn.execute strSQL

Response.Write "Create stored procedure succeeded"

Cnn.close:Set Cnn = Nothing

%>

After the stored procedure is created, you can also delete it using the SQL statement "Drop Procedure Outemploy", in addition to using the menu.

The example wuf72.asp– the required input parameters into the stored procedure and obtains the output results.

<% @LANGUAGE = VBScript%>

<!--#include file= "adosql7.asp"-->

<!--#include file= "Adovbs.inc"-->

<% ' wuf72.asp

Dim Cmdtest, Prmtest

Set cmdtest = Server.CreateObject ("Adodb.command")

Cmdtest.activeconnection = Cnn

Cmdtest.commandtext = "outemploy" ' Stored procedure name

Cmdtest.commandtype = adCmdStoredProc

' Create Parameter Object

Set prmtest = Cmdtest.createparameter ("Job_lvl", Adtinyint,adparamoutput)

CmdTest.Parameters.Append Prmtest

' AdTinyInt-1 byte-signed integer

' adDBDate-Date value (YYYYMMDD)

Set prmtest = Cmdtest.createparameter ("Hiredate1", Addbdate,adparaminput,, "1993-05-09")

CmdTest.Parameters.Append Prmtest

Set prmtest = Cmdtest.createparameter ("Hiredate2", Addbdate,adparaminput,, "1994-02-01")

CmdTest.Parameters.Append Prmtest

Cmdtest.execute

' The following three expressions means the same

Response.Write Cmdtest ("Job_lvl") & "<br>"

Response.Write Cmdtest.parameters ("Job_lvl") & "<br>"

Response.Write Cmdtest.parameters ("Job_lvl"). Value

Cnn.close

Set prmtest = Nothing

Set cmdtest = Nothing:set Cnn = Nothing

%>

(iii) using return code parameters

You can return a different return code from a stored procedure, such as the following stored procedure, which takes a recordset and then returns 1 if the employee named Margaret, or 0.

Create Procedure Returnemploy

As

Select emp_id, fname from employee

If Exists (Select fname from employee Where fname= ' Margaret ')

Return (1)

Else

Return (0)

Case wuf73.asp

<% @LANGUAGE = VBScript%>

<!--#include file= "adosql7.asp"-->

<!--#include file= "Adovbs.inc"-->

<% ' wuf73.asp

Dim cmdtest, Prmtest, rstest

Set cmdtest = Server.CreateObject ("Adodb.command")

Cmdtest.activeconnection = Cnn

Cmdtest.commandtext = "returnemploy" ' Stored procedure name

Cmdtest.commandtype = adCmdStoredProc

Set prmtest = Cmdtest.createparameter ("returnvalue", Adinteger,adparamreturnvalue)

CmdTest.Parameters.Append Prmtest

Set rstest = Cmdtest.execute ()

While not rstest.eof

Response.Write rstest (0) & "[" & Rstest (1) & "<br>"

Rstest.movenext

Wend

RsTest.Close:Set rstest = Nothing

' Before returning to Cmdtest (' returnvalue '), the rstest must be closed before the result is wrong

If cmdtest ("returnvalue") = 1 Then

Response.Write "Have the employee"

Else

Response.Write "No such employee"

End If

Cnn.close

Set prmtest = Nothing

Set cmdtest = Nothing:set Cnn = Nothing

%>

Iii. How to deal with large data

Here the "big data" mainly refers to the text (large text) and image (image) fields, using the method described above can not get their data correctly. The size = rstest (0) must be used first. ActualSize gets the actual length of the field value and then uses Rstest (0). GetChunk (Size) gets the data. In the actual use of the process, because these fields are relatively large, in order to save, reasonable use of server resources, generally take a segmented reading method. Example wuf74.asp:

<% @LANGUAGE = VBScript%>

<!--#include file= "adosql7.asp"-->

<!--#include file= "Adovbs.inc"-->

<% ' wuf74.asp

Dim strSQL, Rstest

' Pr_info is a text field

strSQL = "Select pr_info,pub_id from pub_info"

Set rstest = Cnn.execute (strSQL)



Dim basicsize, Beginsize, Ltext

Do as not rstest.eof

Response.Write Rstest (1) & "<br>"

' Read 1024 bytes at a time

Basicsize = 1024

Beginsize = 0

While Beginsize < Rstest (0). ActualSize

Ltext = rstest (0). GetChunk (Basicsize)

Beginsize = beginsize + basicsize

' Segment output to client

Response.Write Ltext

Wend

Response.Write "<br><br>"

Rstest.movenext

Loop

Cnn.close

Set rstest = Nothing:set Cnn = Nothing

%>

In this example, read up to 1024 bytes at a time, and read them several times. Conversely, if you write large data to a database, the method is similar to the above, but instead of using the GetChunk method, you use the AppendChunk method:

Rstest (0). AppendChunk Ltext

Note: Finally, a tip about SQL Server database, if you have encountered this situation: the Chinese data in the database is garbled, please do not panic. You just go to my site to download sqlsrv32.dll overwrite "C:\Windows\System" under the same name file on it. The source of the problem is the SQL Server driver, which typically occurs in Windows98 second Edition (SQL The server driver has a version number of 3.70.06.23) or Windows2000, or MDAC2.5 is installed (version number is 3.70.08.20).