ASP and Database (iv)

In this lecture, we will mainly describe how to use parameters and 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

' Part One: establishing a 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 a 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 to
Cmdtest.execute

' Reset parameter run--you can modify another piece of data
Cmdtest.parameters ("job_id") = "1"
Cmdtest.parameters ("job_desc") = "Test"
Cmdtest.execute

' Reset parameter 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
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 a 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 a 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).