<%--
Author: He Zhiqiang [hhzqq@21cn.com]
Date: 2000-08-04
2000-08-16
Version: 1.1
Functions: JSP database Operations routines-stored procedures-Jdbc-odbc-sql Server
The stored procedures for SQL Server are as follows:
CREATE PROCEDURE Sp_jsptest
@yourname varchar (50),
@myname varchar (m) output
As
Select "Hello," + @yourname + ", very pleased to meet you, ^_^"
Set @myname = "He Zhiqiang"
Return 1
Go
--%>
<%@ page contenttype= "text/html;charset=gb2312"%>
<%
Variable declaration
Java.lang.String StrName; Name
Get data entered by the user
StrName = Request.getparameter ("name");
if (strname==null) {//user does not enter a name
%>
<meta http-equiv= "Content-type" content= "text/html; charset=gb2312 ">
<TITLE>JSP database Operations routines-stored procedures-Jdbc-odbc-sql server</title>
<body>
<form action= "jdbc-odbc.jsp" method= "POST" >
Your name: <input type= "text" name= "maxlength=" >
<input type= "Submit" value= "submitted" >
</form>
</body>
<%
}
else{
Make the necessary character encoding conversion for the data entered by the user
StrName = new Java.lang.String (strname.getbytes ("iso-8859-1"));
Variable declaration
Java.sql.Connection Sqlcon; Database Connection objects
Java.sql.CallableStatement sqlstmt; Callable Statement Object
Java.sql.ResultSet Sqlrst; Result set Object
Java.lang.String Strcon; Database connection string
Java.lang.String strSQL; SQL statement
Java.lang.String Strwelcome; Welcome words
Java.lang.String Strmyname; My name.
int intreturn; return value
Load JDBC-ODBC Driver
Class.forName ("Sun.jdbc.odbc.JdbcOdbcDriver");
To set the database connection string
Strcon = "Jdbc:odbc:jspdemo";
Connecting to a database
Sqlcon = Java.sql.DriverManager.getConnection (Strcon, "sa", "");
Preparing SQL statements
strSQL = "{? = Call Sp_jsptest (?,?)} ";
To prepare a callable statement object
sqlstmt = Sqlcon.preparecall (strSQL);
Set Input parameters
Sqlstmt.setstring (2,strname);
Register OUTPUT parameters
Sqlstmt.registeroutparameter (1,java.sql.types.integer);
Sqlstmt.registeroutparameter (3,java.sql.types.varchar);
Executes the stored procedure and returns the result set
Sqlrst = Sqlstmt.executequery ();
Get data from the result set
Sqlrst.next ();
Strwelcome = sqlrst.getstring (1);
Get the value of an output parameter
Strmyname = sqlstmt.getstring (3);
Get return value
Intreturn = Sqlstmt.getint (1);
Turn off recordsets
Sqlrst.close ();
To close a callable statement object
Sqlstmt.close ();
Close Database objects
Sqlcon.close ();
%>
<meta http-equiv= "Content-type" content= "text/html; charset=gb2312 ">
<TITLE>JSP database Operations routines-stored procedures-Jdbc-odbc-sql server</title>
<body>
<%=strWelcome%><br>
I'm a <%=strMyName%><br>
The return value is <%=intReturn%>
</body>
<%
}
%>
