ASP Lecture 9: ASP and Database (4)

Author：Eve Cole Update Time：2009-05-30 19:54:56

In this lecture, we will mainly introduce how to use parameters and stored procedures.

1. Use the Command object and Parameter object to transfer parameters. This lecture will mainly use the Microsoft SQL Server7.0 database. First, create a connection file AdoSQL7.asp for backup. No special instructions will be given when it is used in the future.
<% 'AdoSQL7.asp
Option Explicit
Response.Expires = 0

'Part 1: 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 using it yourself, set the Data Source to the machine name of your database server.
In addition, when using Access database in the past, it was very convenient to use Microsoft Access97 to view fields and data. However, when using SQL Server database, especially when debugging ASP scripts not on the database server but on another machine, it is necessary to view fields and data. Data needs to be installed separately. Here is a tool for you: Msqry32.exe (Microsoft Query). This file is installed with Office97 and is generally located in the directory "Microsoft OfficeOffice".
Example wuf70.asp:
<%@ LANGUAGE="VBSCRIPT" %>


<% ' wuf70.asp
Dim cmdTest, prmTest, rsTest
'Create Command object
Set cmdTest = Server.CreateObject("ADODB.Command")
'Recordset and Command objects can connect to Connection objects through the ActiveConnection property.
cmdTest.ActiveConnection = Cnn
'SQL command - contains two parameters, use? express
cmdTest.CommandText = "Update jobs Set job_desc = ? Where job_id = ?"
'Set the command type to be SQL statement
cmdTest.CommandType = adCmdText
The 'Prepared attribute determines whether to compile the SQL command first. Setting it to True can speed up the execution.
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

'Execute modifications - no need to return results, simply use cmdTest.Execute
cmdTest.Execute

'Reset parameters and run – you can modify another piece of data
cmdTest.Parameters("job_id") = "1"
cmdTest.Parameters("job_desc") = "Test"
cmdTest.Execute

'Reset parameters 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) & " "
rsTest.MoveNext
Wend

Cnn.close : Set prmTest = Nothing
Set cmdTest = Nothing: Set Cnn = Nothing
%>
analyze:
1. The CreateParameter method of the Command object is used to create parameter objects for SQL commands or stored procedures. There are five parameters in total (all five parameters are optional):
The first parameter: the name of the parameter object;
The second parameter: the data type of the parameter object, there are too many types, please refer to the ADO help, here adVarChar (string value), adSmallInt (2-byte signed integer);
The third parameter: parameter type. Can be: adParamInput (indicates an input parameter), adParamOutput (indicates an output parameter), adParamReturnValue (indicates a return value), adParamUnknown (indicates that the parameter type cannot be determined), adParamInputOutput (indicates an input/output parameter);
The fourth parameter: the data length of the parameter. It is best to specify it as equal to the length of the corresponding field in the database to avoid errors when using it, especially when the data type is VarChar. If it is an integer or date type, you do not need to provide this value;
The fifth parameter: the initial value of the parameter setting.
2. The cmdTest.Parameters.Append method adds a Parameter object to the Parameters data collection. From this example, you can also see how to use multiple parameters.
3． As you can see from this example, you only need to reset the input parameters to modify other data, which is very convenient. This idea is also one of the most commonly used methods in programming.
4. To reset the parameters, you can use cmdTest.Parameters or omit it as cmdTest("job_id").

2. Using stored procedures in ASP. What is a stored procedure (a stored procedure is located in the database server and is a collection of SQL statements that can contain one or more SQL statements). How to create a stored procedure is not part of the content of this lecture. This lecture Mainly give examples to illustrate how to call stored procedures in ASP.
The benefits of using stored procedures are great. Stored procedures are more efficient than running SQL commands in ASP scripts; they can improve overall performance and reduce network load (reducing the interaction between the network server and the data server); they can optimize ASP code and enhance code flexibility, etc.
(1) Using input parameters in the stored procedure The stored procedure used in this example is "byroyalty" that comes with SQL Server 7.0. The SQL statement in it is very simple. It is nothing more than an additional CREATE PROCEDURE byroyalty and an input. The parameter is @percentage:
CREATE PROCEDURE byroyalty @percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage
Example wuf71.asp
<% @LANGUAGE = VBScript %>


<% ' wuf71.asp
Dim cmdTest, prmTest, rsTest
Set cmdTest = Server.CreateObject("ADODB.Command")
cmdTest.CommandText = "byroyalty" 'Stored procedure name
'Set the command type as stored procedure
cmdTest.CommandType = adCmdStoredProc

'Create Parameter object
Set prmTest = Server.CreateObject("ADODB.Parameter")
'Type attribute corresponds to the second parameter in wuf70.asp
prmTest.Type = adInteger '4-byte signed integer' Direction attribute corresponds to the third parameter in wuf70.asp
prmTest.Direction = adParamInput
'Value attribute corresponds to the fifth parameter in wuf70.asp
prmTest.Value = 30
cmdTest.Parameters.Append prmTest

Set cmdTest.ActiveConnection = Cnn
'You need to return a record set, so use Set rsTest = cmdTest.Execute
Set rsTest = cmdTest.Execute

While Not rsTest.EOF
Response.Write rsTest(0) & " "
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, a stored procedure, or a table name.
In this example, creating a Parameter object is slightly different from wuf70.asp. In fact, if you look carefully, the meaning is similar. There are two unused attributes in this example: prmTest.Name, prmTest.Size, plus Type, Direction and Value, corresponding to the five parameters in wuf70.asp.

(2) When using output parameters to obtain a record or calculate a value from a database table, you need to use a stored procedure that returns output parameters. For the sake of example, first create a new stored procedure OUTemploy in the pubs library of SQL Server. This stored procedure needs to input two dates and then output 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 create stored procedures:
1. Use the Enterprise Manager of Microsoft SQL Server. After opening it, open it in the tree directory on the left: Console Root – Microsoft SQL Servers – SQL Server Group – ICBCZJP (Windows NT) – databases – pubs – stored procedure – New stored procedure. Enter the stored procedure. Finally, grammar detection can also be performed on it;
2. Using Microsoft SQL Server's Query Analyzer, first connect to the database server and select the pubs database. Enter the stored procedure above and click Execute Query (or press F5);
3． Using VB6.0, after opening the menu "View"/"Data View Window", right-click "Data Link"/"New Data Link";
4. Use ASP script to create a stored procedure, for example wuf75.asp:
<% @LANGUAGE = VBScript %>


<% ' wuf75.asp
DimStrSQL
'Note: & Chr(10) & Chr(13) are completely unnecessary, mainly for good looks.
StrSQL="CREATE PROCEDURE OUTemploy ( @job_lvl tinyint OUTPUT, " & Chr(10) & Chr(13) &_
"@hire_date1 datetime, @hire_date2 datetime) AS " & Chr(10) & Chr(13) &_
"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 successfully"
Cnn.close: Set Cnn = Nothing
%>
After the stored procedure is created, in addition to using the menu, you can also use the SQL statement "Drop Procedure OUTemploy" to delete it.
Example wuf72.asp – Send the required input parameters to the stored procedure and obtain the output results.
<% @LANGUAGE = VBScript %>


<% ' 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 have the same meaning
Response.Write cmdtest("job_lvl") & " "
Response.Write cmdTest.Parameters("job_lvl") & " "
Response.Write cmdTest.Parameters("job_lvl").Value

Cnn.close
Set prmTest = Nothing
Set cmdTest = Nothing: Set Cnn = Nothing
%>
(3) Use the return code parameter to use the Return statement to return different return codes from the stored procedure. For example, the following stored procedure first obtains a record set, and then returns 1 if there is an employee named Margaret, otherwise it returns 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)
Example wuf73.asp
<% @LANGUAGE = VBScript %>


<% ' 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) & " "
rsTest.MoveNext
Wend
rsTest.Close: Set rsTest = Nothing
'Before returning cmdtest("ReturnValue"), rsTest must be closed first, otherwise the result will be wrong

If cmdtest("ReturnValue") = 1 Then
Response.Write "There is this employee"
Else
Response.Write "No such employee"
End If

Cnn.close
Set prmTest = Nothing
Set cmdTest = Nothing: Set Cnn = Nothing
%>

3. How to process big data The "big data" here mainly refers to the Text (large text) and image (image) fields, whose data cannot be obtained correctly using the method described above. You must first use Size = rsTest(0).ActualSize to get the actual length of the field value, and then use rsTest(0).GetChunk(Size) to get the data. In actual use, since these fields are relatively large, in order to save and rationally use server resources, segmented reading is generally adopted. Example wuf74.asp:
<% @LANGUAGE = VBScript %>


<% ' 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 While Not rsTest.EOF
Response.Write rsTest(1) & " "
'Read 1024 bytes each time
BasicSize = 1024
BeginSize = 0
While BeginSize < rsTest(0).ActualSize
LText = rsTest(0).GetChunk(BasicSize)
BeginSize = BeginSize + BasicSize
'Output to the client segment by segment
Response.Write LText
Wend

Response.Write " "
rsTest.MoveNext
Loop

Cnn.close
Set rsTest = Nothing: Set Cnn = Nothing
%>
In this example, a maximum of 1024 bytes are read each time, and the data is read in multiple times. On the contrary, if you write big data to the database, the method is similar to the above, but instead of using the GetChunk method, you use the AppendChunk method:
rsTest(0).AppendChunkLtext

Note: Finally, I will introduce a little trick about SQL Server database. If you have encountered this situation: the Chinese data in the database is displayed as garbled characters, please do not panic. You only need to go to my site to download sqlsrv32.dll and overwrite the file with the same name under "C:WindowsSystem". The source of the problem is the SQL Server driver, which typically occurs in Windows 98 Second Edition (version number of the SQL Server driver is 3.70.06.23) or Windows 2000 or when MDAC2.5 (version number is 3.70.08.20) is installed.