ASP is a powerful tool for generating dynamic interactive web pages. Below is a tutorial on using ASP storage that the editor has shared with you. Interested friends, let’s learn about it with the editor!
1. Pass parameters using Command objects and Parameter objects. This lecture will mainly use the Microsoft SQL Server 7.0 database. First, create a connection file AdoSQL7.asp for backup. I will not specify it when it is used later.
%''AdoSQL7.asp
OptionExplicit
Response.Expires=0
''Part 1: Establishing a Connection
DimCnn,StrCnn
SetCnn=Server.CreateObject("ADODB.Connection")
StrCnn="Provider=sqloledb;UserID=sa;Password=;InitialCatalog=pubs;DataSource=ICBCZJP"
Cnn.OpenStrCnn
%>
Note: When using it yourself, set DataSource to the machine name of your database server.
In addition, when using Access databases in the past, Microsoft Access97 can easily view fields and data. When using SQLServer databases, especially when debugging ASP scripts on another machine, you need to install additional tools to view fields and data. Here we provide you with a tool: Msqry32.exe (MicrosoftQuery). This file is installed with Office97 and is generally located in the directory "Microsoft Office/Office".
Example wuf70.asp:
%@LANGUAGE="VBSCRIPT"%>
!--#includefile="AdoSQL7.asp"-->
!--#includefile="adovbs.inc"-->
%''wuf70.asp
DimcmdTest,prmTest,rsTest
''Create Command object
SetcmdTest=Server.CreateObject("ADODB.Command")
'Recordset and Command objects can be connected to Connection objects through the ActiveConnection property.
cmdTest.ActiveConnection=Cnn
''SQL command - contains two parameters, express it with ?
cmdTest.CommandText="UpdatejobsSetjob_desc=?Wherejob_id=?"
''Suppose the command type is SQL statement
cmdTest.CommandType=adCmdText
''Prepared property determines whether to compile the SQL command first, set it to True to speed up the run
cmdTest.Prepared=True
''Create Parameter object
SetprmTest=cmdTest.CreateParameter("job_desc",adVarChar,adParamInput,50,"network")
''Append data to Parameters data collection
cmdTest.Parameters.AppendprmTest
SetprmTest=cmdTest.CreateParameter("job_id",adSmallInt,adParamInput,,"12")
cmdTest.Parameters.AppendprmTest
''Execute modification – no need to return the result, just use cmdTest.Execute
cmdTest.Execute
''Reset parameters to 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")="financial"
cmdTest.Execute
SetrsTest=Cnn.Execute("Selectjob_id,job_descFromjobs")
WhileNotrsTest.EOF
Response.WritersTest(0)&rsTest(1)&""
rsTest.MoveNext
Wend
Cnn.close:SetprmTest=Nothing
SetcmdTest=Nothing:SetCnn=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 (the five parameters are optional):
The first parameter: the name of the parameter object;
The second parameter: There are too many types of data types of parameter objects. Please refer to the ADO help, here adVarChar (string value), adSmallInt (2-byte signed integer);
The third parameter: parameter type. It can be: adParamInput (indicates input parameters), adParamOutput (indicates as output parameters), adParamReturnValue (indicates as return value), adParamUnknown (indicates that the parameter type cannot be determined), adParamInputOutput (indicates as input/output parameters);
The fourth parameter: The data length of the parameter is preferably specified to be equal to the corresponding field length in the database to avoid errors during use. Especially when the data type is VarChar, if it is an integer or a date type, the value is not necessary;
The fifth parameter: the initial value of the parameter setting.
2.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. From this example, we can see that you only need to reset the input parameters to perform modifications to other data, which is very convenient. This idea is also one of the most commonly used methods when programming.
4. Reset the parameters, you can either use cmdTest.Parameters or omit it to cmdTest("job_id").
2. Use 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), and how to create stored procedures does not belong to the content of this lecture. This lecture mainly illustrates how to call stored procedures in ASP.
The benefits of using stored procedures are great, which are more efficient than running SQL commands in ASP scripts; they can improve overall performance and reduce network load (reduce interaction between network servers and data servers); they can optimize ASP code and enhance code flexibility, and so on.
(I) Use input parameters in stored procedures
The stored procedure used in this example is "byroyalty" that comes with SQLServer 7.0. A SQL statement inside is very simple. It is nothing more than an additional CREATEPROCEDUREbyroyalty, and an input parameter is @percentage:
CREATEPROCEDUREbyroyalty@percentageint
AS
selectau_idfromtitleauthor
wheretitleauthor.royaltypeper=@percentage
servebywww.cidu.net
Example wuf71.asp
%@LANGUAGE=VBScript%>
!--#includefile="AdoSQL7.asp"-->
!--#includefile="adovbs.inc"-->
%''wuf71.asp
DimcmdTest,prmTest,rsTest
SetcmdTest=Server.CreateObject("ADODB.Command")
cmdTest.CommandText="byroyalty"''Stored procedure name
''Suppose the command type is a stored procedure
cmdTest.CommandType=adCmdStoredProc
''Create Parameter object
SetprmTest=Server.CreateObject("ADODB.Parameter")
'Type attribute corresponds to the second parameter in wuf70.asp
prmTest.Type=adInteger''4-byte signed integer
'Direction property 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.AppendprmTest
SetcmdTest.ActiveConnection=Cnn
'You need to return a record set, so use SetrsTest=cmdTest.Execute
SetrsTest=cmdTest.Execute
WhileNotrsTest.EOF
Response.WritersTest(0)&""
rsTest.MoveNext
Wend
Cnn.close
SetrsTest=Nothing:SetprmTest=Nothing
SetcmdTest=Nothing:SetCnn=Nothing
%>
The CommandText property can either specify SQL commands, or as stored procedures or table names.
In this example, creating a Parameter object is slightly different from wuf70.asp. In fact, if you look closely, the meaning is similar. There are two properties in this example that are not used: prmTest.Name, prmTest.Size, plus Type, Direction and Value, which correspond to the five parameters in wuf70.asp.
(II) Use output parameters
When obtaining a record from a database table or calculating a value, a stored procedure that returns the output parameters need to be used. To give an example, first create a new stored procedure OUTemploy in the SQLServer pubs library. The stored procedure needs to enter two dates and then output a maximum value.
CREATEPROCEDUREOUTemploy
(
@job_lvltinyintOUTPUT,
@hire_date1datetime,
@hire_date2datetime
)
AS
select@job_lvl=MAX(job_lvl)fromimployee
wherehire_date>=@hire_date1andhire_date=@hire_date2
There are several ways to create stored procedures:
1. Use Microsoft SQLServer's EnterpriseManager, open it in the tree directory on the left and open it in turn: ConsoleRoot–MicrosoftSQLServers–SQLServerGroup–ICBCZJP (WindowsNT)–databases–pubs–storedprocedure–Newsstoredprocedure. After entering the stored procedure, it can also be syntaxly detected;
2. Use Microsoft SQLServer's QueryAnalyzer, first connect to the database server and select the pubs database. Enter the stored procedure above and click ExecuteQuery (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 stored procedures, such as wuf75.asp:
%@LANGUAGE=VBScript%>
!--#includefile="AdoSQL7.asp"-->
!--#includefile="adovbs.inc"-->
%''wuf75.asp
DimStrSQL
''Note:&Chr(10)&Chr(13) can be ignored, mainly for the sake of good looks
StrSQL="CREATEPROCEDUREOUTemploy(@job_lvltinyintOUTPUT,"&Chr(10)&Chr(13)&_
"@hire_date1datetime,@hire_date2datetime)AS"&Chr(10)&Chr(13)&_
"select@job_lvl=MAX(job_lvl)fromimployee"&_
"wherehire_date>=@hire_date1andhire_date=@hire_date2"
Cnn.ExecuteStrSQL
Response.Write "Create stored procedure successfully"
Cnn.close:SetCnn=Nothing
%>
After the stored procedure is created, in addition to using the menu, you can also delete it using the SQL statement "DropProcedureOUTemploy".
Example wuf72.asp – Send the required input parameters into the stored procedure and obtain the output result.
%@LANGUAGE=VBScript%>
!--#includefile="AdoSQL7.asp"-->
!--#includefile="adovbs.inc"-->
%''wuf72.asp
DimcmdTest,prmTest
SetcmdTest=Server.CreateObject("ADODB.Command")
cmdTest.ActiveConnection=Cnn
cmdTest.CommandText="OUTemploy"''Stored procedure name
cmdTest.CommandType=adCmdStoredProc
''Create Parameter object
SetprmTest=cmdTest.CreateParameter("job_lvl",adTinyInt,adParamOutput)
cmdTest.Parameters.AppendprmTest
''adTinyInt-1 byte signed integer
''adDbDate-date value (yyyymmdd)
SetprmTest=cmdTest.CreateParameter("hiredate1",adDBDate,adParamInput,,"1993-05-09")
cmdTest.Parameters.AppendprmTest
SetprmTest=cmdTest.CreateParameter("hiredate2",adDBDate,adParamInput,,"1994-02-01")
cmdTest.Parameters.AppendprmTest
cmdTest.Execute
'The following three expressions mean the same
Response.Writecmdtest("job_lvl")&""
Response.WritecmdTest.Parameters("job_lvl")&""
Response.WritecmdTest.Parameters("job_lvl").Value
Cnn.close
SetprmTest=Nothing
SetcmdTest=Nothing:SetCnn=Nothing
%>
(III) Use return code parameters
Use the Return statement to return different return codes from stored procedures. For example, the stored procedure first obtains a record set, and then, if there is an employee named Margaret, it will return 1, otherwise it will return 0.
CreateProcedureReturnemploy
AS
selectemp_id,fnamefrommployee
IfExists(SelectfnameFromimployeeWherefname=''Margaret'')
Return(1)
Else
Return(0)
Example wuf73.asp
%@LANGUAGE=VBScript%>
!--#includefile="AdoSQL7.asp"-->
!--#includefile="adovbs.inc"-->
%''wuf73.asp
DimcmdTest,prmTest,rsTest
SetcmdTest=Server.CreateObject("ADODB.Command")
cmdTest.ActiveConnection=Cnn
cmdTest.CommandText="Returnemploy"''Stored procedure name
cmdTest.CommandType=adCmdStoredProc
SetprmTest=cmdTest.CreateParameter("ReturnValue",adInteger,adParamReturnValue)
cmdTest.Parameters.AppendprmTest
SetrsTest=cmdTest.Execute()
WhileNotrsTest.EOF
Response.WritersTest(0)&"]["&rsTest(1)&""
rsTest.MoveNext
Wend
rsTest.Close:SetrsTest=Nothing
''Before returning cmdtest("ReturnValue"), rsTest must be closed first, otherwise the result will be incorrect
Ifcmdtest("ReturnValue")=1Then
Response.Write "has this employee"
Else
Response.Write "No employee"
EndIf
Cnn.close
SetprmTest=Nothing
SetcmdTest=Nothing:SetCnn=Nothing
%>
3. How to deal with big data
The "big data" here mainly refers to the Text (large text) and image (image) fields. The data cannot be correctly obtained using the methods 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 use server resources reasonably, segmented reading is generally adopted. Example wuf74.asp:
%@LANGUAGE=VBScript%>
!--#includefile="AdoSQL7.asp"-->
!--#includefile="adovbs.inc"-->
%''wuf74.asp
DimStrSQL,rsTest
'pr_info is a text field
StrSQL="Selectpr_info,pub_idFrompub_info"
SetrsTest=Cnn.Execute(StrSQL)
DimBasicSize,BeginSize,LText
DoWhileNotrsTest.EOF
Response.WritersTest(1)&""
'1024 bytes per read
BasicSize=1024
BeginSize=0
WhileBeginSizersTest(0).ActualSize
LText=rsTest(0).GetChunk(BasicSize)
BeginSize=BeginSize+BasicSize
'Output segment by segment to client
Response.WriteLText
Wend
Response.Write""
rsTest.MoveNext
Loop
Cnn.close
SetrsTest=Nothing:SetCnn=Nothing
%>
In this example, a maximum of 1024 bytes are read each time and read them 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 tip about SQLServer database. If you have encountered this situation: the Chinese data in the database is displayed in garbled code, please don’t panic. You just need to go to my site to download sqlsrv32.dll overwrite the file with the same name under "C:/Windows/System". The source of the problem is the SQLServer driver, which is typically happening in Windows 98 version 2 (the version number of the SQLServer driver is 3.70.06.23) or Windows 2000 or MDAC2.5 is installed (version number is 3.70.08.20).
The above is a tutorial on using ASP storage. I believe you have learned it all. For more related content, please continue to follow the WoNew Technology Channel.