Transfer parameters using the Command object and Parameter object
This lecture will mainly use the Microsoft SQL Server7.0 database, first create a connection file ADOSQL7.ASP standby, which will not be specifically described later.
<% 'AdoSQL7.ASP
Option expedition
Response.expires = 0
'First Part: establish 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 the Data Source to create the machine name where your database server is located.
In addition, when using the Access database, you can use the Microsoft Access 97 to view fields and data, and use the SQL Server database, especially if it is not in the database server, but when you debug an ASP script on another machine, you want to view the field and Data requires additional installation tools, here offers a tool: msqry32.exe (Microsoft Query), this file is installed with office97, which is generally located under the directory "Microsoft Office / Office".
Example wuf70.asp:
<% @ Language = "VBScript"%>
<% 'wuf70.asp
DIM CMDTEST, PRMTEST, RSTEST
'Creating a Command object
Set cmdtest = Server.createObject ("adodb.command")
'RecordSet, Command objects can be connected to the Connection object via the ActiveConnection property.
cmdtest.activeconnection = CNN
'Sql command - contain two parameters, used? Indicate
cmdtest.commandtext = "Update Jobs Set Job_Desc =? Where job_id =?"
'Set the command type to SQL statement
cmdtest.commandtype = adcmdtext
'Prepared attribute decides whether to compile the SQL command first, set it to true, can speed up
cmdtest.prepared = TRUE
'Creating Parameter objects
Set prmtest = cmdtest.createParameter ("Job_Desc", Advarchar, Adpaaminput, 50, "Network")
'Add data to Parameters data collection
Cmdtest.Parameters.Append prmtest
Set prmtest = cmdtest.createParameter ("job_id", adsmallint, adpaaminput, "12")
Cmdtest.Parameters.Append prmTest 'performs modification - do not need to return results, simple use cmdtest.execute is OK
Cmdtest.execute
'Resetting the parameters run - you can modify another data
Cmdtest.Parameters ("job_id") = "1"
cmdtest.parameters ("job_desc") = "Test"
Cmdtest.execute
'Reset parameters 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
%>
analysis:
1. The CreateParameter method for the Command object is used to establish a parameter object for the SQL command or stored procedure, and there are five parameters (five parameters are optional):
The first parameter: the name of the parameter object;
The second parameter: the data type of the parameter object, too much type, or refer to the ADO help, here Advarchar, Adsmallint (2 byte band symbol integer);
The third parameter: parameter type. Can be: adParaminput, adParamoutput (indicated as output parameters), adParamReturnValue (indicated as return value), adParamunkNown (indicating the parameter type cannot be determined), AdParaminputoutput (indicated as an input / output parameter);
The fourth parameter: the data length of the parameter, it is preferable to be equal to the length of the corresponding field in the database to avoid error, especially the data type is VARCHAR, if it is an integer or date type, it is not necessary to provide this value;
The fifth parameter: setting the initial value of the parameters.
2. CMDTest.Parameters.Append method Add a parameter object to the Parameters data collection, which can also see how to use multiple parameters from this example.
3. As can be seen from this example, it is only necessary to reset the input parameters, it is very convenient to perform the modification of other data, which is also one of the most common methods when programming.
4. Reset parameters, can be used either CMDTest.Parameters, or cmdtest ("JOB_ID").
Second, use stored procedures in ASP
What is a stored procedure (stored procedure is a collection of SQL statements, can contain one or more SQL statements), how to create a stored procedure does not belong to this lecture, this lecture is mainly illustrative in ASP Call the stored procedure. The advantage of using the stored procedure is great, the stored procedure is more efficient than running SQL commands in the ASP script; how to improve overall performance and reduce network load (reduce interaction between network servers and data servers); can optimize ASP code And enhanced code flexibility, etc.
(1) Use input parameters during storage
The stored procedure used in this example comes with "Byroyalty" with SQL Server7.0, one of the SQL statements in this example is very simple, nothing more than a create processure Byroyalty, and there is an input parameter @Percentage:
Create Procedure Byroyalty @Percentage Int
AS
SELECT AU_ID from Titleauthor
Where title = @PERCENTAGE
Serve by www.cidu.net
Example wuf71.asp
<% @Language = VBScript%>
<% 'wuf71.asp
DIM CMDTEST, PRMTEST, RSTEST
Set cmdtest = Server.createObject ("adodb.command")
cmdtest.comMandtext = "Byroyalty" 'Store process name
'Set the command type to the stored procedure
cmdtest.comMandType = adcmdstoredProc
'Creating Parameter objects
Set prmtest = server.createObject ("adodb.parameter")
'Type property corresponds to the second parameter in WUF70.ASP
PRMTEST.TYPE = Adinteger '4 bytes with symbol integer
The 'Direction property corresponds to the third parameter in WUF70.asp
prmtest.direction = adpaaminput
'Value property corresponds to the fifth parameters in 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) & "
"
Rstest.movenext
Wend
CNN.Close
SET RSTEST = Nothing: set prmtest = Nothing
Set cmdtest = Nothing: SET CNN = Nothing
%>
The CommandText property can specify the SQL command or specify a stored procedure or a table name.
In this example, the creation of the parameter object is slightly different from WUF70.asp. In fact, take a closer look, meaning is also almost, this example has two attributes unused: prmTest.name, prmTest.size, plus Type, Direction and Value, corresponding to five parameters in WUF70.asp. (2) use output parameters
When you get a record or calculate a value from the database table, you need to use the stored procedure for returning the output parameters. For example, first create a stored procedure OUTEMPLOY in the PUBS library of SQL Server, which requires two dates and then outputs a maximum.
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 many ways to establish a stored procedure:
1. Use of Microsoft SQL Server Enterprise Manager, after opening in order to open the directory tree 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 After it can be detected in grammar;
2. Use Microsoft SQL Server Query Analyzer, first connect the database server and select the PUBS database. Enter the stored procedure above and click Execute Query (or press F5);
3. Use VB6.0, Open Menu View / Data View Window, right-click on "Data Link" / "New Data Link";
4. Create a stored procedure using an ASP script, example wuf75.asp:
<% @Language = VBScript%>
<% 'wuf75.asp
DIM STRSQL
'Note: & chr (10) & chr (13) can do not, mainly for the best
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 strsqlresponse.write "Create a stored process success"
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 into the stored procedure and acquire 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
'Creating Parameter objects
Set prmtest = cmdtest.createParameter ("Job_LVL", ADTINT, ADPARAMOUTPUT)
Cmdtest.Parameters.Append prmtest
'ADTINYINT - 1 byte band symbol 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 ways to express the meaning
Response.write cmdtest ("job_lvl") & "
"
Response.write cmdte.Parameters ("Job_LVL") & "
"
Response.write cmdtest.parameters ("job_lvl"). Value
CNN.Close
Set prmtest = Nothing
Set cmdtest = Nothing: SET CNN = Nothing
%>
(3) Use return code parameters
With the return statement, you can return a different return code from the stored procedure. If the stored procedure first gets a recordset, then if you call the MARGARET, return 1, 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 rtest = Nothing
'Before returning CMDTest ("ReturnValue"), you must first turn off RSTest, otherwise the result is incorrect
IF cmdtest ("ReturnValue) = 1 THEN
Response.write "with this employee"
Else
Response.write "No Employee"
END IF
CNN.Close
Set prmtest = Nothing
Set cmdtest = Nothing: SET CNN = Nothing
%>
Third, how to handle big data
The "big data" here is mainly referred to as the Text (Big Text) and Image (Image) fields, and its data cannot be obtained correctly. You must first use size = rstest (0) .AlSize to get the actual length of the field value, and then use RSTest (0) .Getchunk (size) to obtain data. During the actual use, since these fields are relatively large, in order to save, reasonably use server resources, the method of segmentation read is generally taken. 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 = 1024BEGINSIZE = 0
While Beginsize LTEXT = RSTEST (0) .Getchunk (Basicsize) Beginsize = Beginsize Basicsize 'Output to the client Response.write ltext Wend Response.Write " Rstest.movenext Loop CNN.Close SET RSTEST = Nothing: SET CNN = Nothing %> In this example, 1024 bytes per time each time, multiple read multiple times. Conversely, if the big data is written to the database, the method is similar to, but not using the getChunk method, but use the Appendchunk method: Rstest (0) .appendchunk ltext Note: Finally introduces a small trick about the SQL Server database. If you have encountered this situation: the Chinese data of the database is garbled, please don't panic. As long as you go to my site to download SQLSRV32.DLL override the same name file under "c: / windows / system". The source of the problem occurs is the SQL Server driver, typically occurs in the Windows 98 Second Edition (version number of the SQL Server driver is 3.70.06.23) or in Windows 2000 or MDAC2.5 (version number 3.70.08.20).
"
