请教高手: 我的系统使用 TOMCAT5.5.15+ SQL SERVER+ STRUTS1.2.6 开发的 本机调试时插入(修改)数据库没有问题,但是放到服务器上后插入(修改)数据库有问题,提示: ERROR: 系统出错[[Microsoft][SQLServer 2000 Driver for JDBC]The DBMS returned an unspecified error.] ERROR: queryFirstRow error: SQL(SELECT count(*) FROM (SELECT TOP 100 PERCENT hospID, hospName, hospProvince, hospCity FROM tbl_main_hospital ORDER BY hospID desc) sys_query_tmp)
请教高手: 我的系统使用 TOMCAT5.5.15+ SQL SERVER+ STRUTS1.2.6 开发的 本机调试时插入(修改)数据库没有问题,但是放到服务器上后插入(修改)数据库有问题,提示: ERROR: 系统出错[[Microsoft][SQLServer 2000 Driver for JDBC]The DBMS returned an unspecified error.] ERROR: queryFirstRow error: SQL(SELECT count(*) FROM (SELECT TOP 100 PERCENT hospID, hospName, hospProvince, hospCity FROM tbl_main_hospital ORDER BY hospID desc) sys_query_tmp)
我把源程序贴一下,总的来说就是用JSp调用一个JavaBean,用JavaBean来调用EJB,往数据库插入数据。
import java.sql.*;
import javax.sql.*;
import java.util.*;
import javax.ejb.*;
import javax.naming.*;public class AcEJB implements EntityBean { private String id;
private String date;
private String vc;
private String check;
private double amount;
private EntityContext context;
private Connection con;
private String dbName = "java:comp/env/jdbc/MyDataSource";
public String getDate() {
return date;
}
public String getVc() {
return vc;
}
public String getCheck() {
return check;
} public double getAmount() {
return amount;
} public String ejbCreate(String id, String date, String vc, String check, double amount) throws CreateException {
try {
System.out.println("Inside ejbcreate");
insertRow(id, date, vc, check, amount);
System.out.println("after insert row");
} catch (Exception ex) {
throw new EJBException("ejbCreate: " +
ex.getMessage());
} this.id = id;
this.date = date;
this.vc =vc;
this.check = check;
this.amount=amount;
return id;
}
public String ejbFindByPrimaryKey(String primaryKey)
throws FinderException {
boolean result;
try {
result = selectByPrimaryKey(primaryKey);
} catch (Exception ex) {
throw new EJBException("ejbFindByPrimaryKey: " +
ex.getMessage());
}
if (result) {
return primaryKey;
}
else {
throw new ObjectNotFoundException
("Row for id " + primaryKey + " not found.");
}
} public void ejbRemove() {
try {
deleteRow(id);
} catch (Exception ex) {
throw new EJBException("ejbRemove: " +
ex.getMessage());
}
}
public void setEntityContext(EntityContext context) {
this.context = context;
try {
System.out.println("Inside setEntityContext");
makeConnection();
} catch (Exception ex) {
throw new EJBException("Unable to connect to database. " +
ex.getMessage());
}
} public void unsetEntityContext() {
try {
con.close();
} catch (SQLException ex) {
throw new EJBException("unsetEntityContext: " + ex.getMessage());
}
}
public void ejbActivate() {
id = (String)context.getPrimaryKey();
}
public void ejbPassivate() {
id = null;
}
public void ejbLoad() {
try {
System.out.println("Inside ejbLoad()");
loadRow();
System.out.println("After loadrow() in ejbLoad()");
} catch (Exception ex) {
throw new EJBException("ejbLoad: " +
ex.getMessage());
}
}
public void ejbStore() {
try {
System.out.println("Inside ejbStore()");
storeRow();
System.out.println("After storeRow() in ejbLoad()");
} catch (Exception ex) {
throw new EJBException("ejbLoad: " +
ex.getMessage());
}
}
public void ejbPostCreate(String id, String date, String vc, String check, double amount) { }
/*** Routines to access database ***/ private void makeConnection() throws NamingException, SQLException {
InitialContext ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup(dbName);
con = ds.getConnection();
}
private void insertRow (String id, String date, String vc, String check, double amount) throws SQLException {
System.out.println("Inside sql insert row");
String insertStatement =
"insert into Account_Holder_Transaction values ( ? , ? , ? , ?, ? )";
PreparedStatement prepStmt =
con.prepareStatement(insertStatement);
System.out.println("Before id");
prepStmt.setString(1, id);
System.out.println("Before date");
prepStmt.setString(2, date);
System.out.println("Before vc");
prepStmt.setString(3, vc);
System.out.println("Before check");
prepStmt.setString(4, check);
System.out.println("Before amount");
prepStmt.setDouble(5, amount);
prepStmt.executeUpdate();
prepStmt.close();
}
private void deleteRow(String id) throws SQLException {
String deleteStatement =
"delete from Account_Holder_Transaction where cAccount_id = ? ";
PreparedStatement prepStmt =
con.prepareStatement(deleteStatement);
prepStmt.setString(1, id);
prepStmt.executeUpdate();
prepStmt.close();
}
private boolean selectByPrimaryKey(String primaryKey)
throws SQLException {
String selectStatement =
"select cAccount_id " +
"from Account_Holder where cAccount_id = ? ";
PreparedStatement prepStmt = con.prepareStatement(selectStatement);
prepStmt.setString(1, primaryKey);
ResultSet rs = prepStmt.executeQuery();
boolean result = rs.next();
prepStmt.close();
return result;
}
private void loadRow() throws SQLException {
String selectStatement =
"select dDate_of_transaction, vcParticulars, cCheck_no, mAmount from Account_Holder_Transaction where cAccount_id = ? ";
PreparedStatement prepStmt =
con.prepareStatement(selectStatement);
System.out.println("after con.prep");
prepStmt.setString(1, this.id);
ResultSet rs = prepStmt.executeQuery();
System.out.println("After executeQuery()");
if (rs.next()) {
System.out.println("Inside if");
this.date = rs.getString(1);
System.out.println("After 1");
this.vc = rs.getString(2);
System.out.println("After 2");
this.check = rs.getString(3);
System.out.println("After 3");
this.amount = rs.getDouble(4);
prepStmt.close();
}
else {
prepStmt.close();
throw new NoSuchEntityException("Row for id " + id + " not found in database.");
}
}
private void storeRow() throws SQLException {
String updateStatement =
"update Account_Holder_Transaction set dDate_of_transaction = ? , vcParticulars = ? , cCheck_no = ?, mAmount = ? where cAccount_id = ?";
PreparedStatement prepStmt =
con.prepareStatement(updateStatement);
prepStmt.setString(1, date);
prepStmt.setString(2, vc);
prepStmt.setString(3, check);
prepStmt.setDouble(4, amount);
prepStmt.setString(5, id);
int rowCount = prepStmt.executeUpdate();
prepStmt.close(); if (rowCount == 0) {
throw new EJBException("Storing row for id " + id + " failed.");
}
}
}
import javax.ejb.EJBObject;
import java.rmi.RemoteException;public interface Account extends EJBObject {
public String getDate() throws RemoteException; public String getVc() throws RemoteException;
public String getCheck() throws RemoteException; public double getAmount() throws RemoteException;}
怎么能建立呢
那你的home接口呢
还有
数据库连接,不是到哪里连一次
你写个DBHelper类
在里面写个方法
public Connection getConnection(){}不就行了吗
其他只要调用就可以了
你这样的写法根本不能实现业务,逻辑,数据的分离
在home接口里声明
在实体bean类里面要加上ejbHome
这样你的接口、实现类,商业方法很清楚了
现在你的实体bean把所有的东西混在一起了
import java.util.Collection;
import java.rmi.RemoteException;
import javax.ejb.*;public interface AcHome extends EJBHome
{
public Account create(String id, String date, String vc, String check, double amount) throws RemoteException, CreateException;
public Account findByPrimaryKey(String id)throws FinderException, RemoteException;
}JavaBean类:
import java.util.*;
import java.io.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.rmi.PortableRemoteObject;public class AccountBean { private String action;
private String id;
private String dateTran;
private String vc;
private String check;
private double amount; private AcHome accountHome;
private Account account; public AccountBean() {
try {
Context ic = new InitialContext();
Object objref = ic.lookup("java:comp/env/ejb/Account");
accountHome = (AcHome) PortableRemoteObject.narrow(objref, AcHome.class);
System.out.println("obtained accountHome object");
} catch (Exception re) {
System.err.println ("Couldn't locate Account Home");
re.printStackTrace();
}
reset();
} public String processRequest()
{
String message = "";
System.out.println("Process request called ");
System.out.println(this);
try {
if( action.equals("save") )
{
account = accountHome.create(id, dateTran, vc, check, amount);
message = "Recorded details for account '" + id + "'";
}
} // try
catch (Exception e) {
message = e.toString();
}
return message;
}
public String getAction() {
System.out.println("Getting action");
return action;
}
public void setAction(String a) {
System.out.println("Setting action : " + a);
action = a;
}
public String getId() {
System.out.println("Getting id");
return id;
}
public void setId(String i) {
System.out.println("Setting id : " + i);
id = i;
}
public String getDateTran() {
System.out.println("Getting transaction date");
return dateTran;
}
public void setDateTran(String f) {
System.out.println("Setting transaction date : " + f);
dateTran = f;
}
public String getVc() {
System.out.println("Getting particulars");
return vc;
}
public void setVc(String l) {
System.out.println("Setting particulars : " + l);
vc = l;
}
public String getCheck() {
System.out.println("Getting check number");
return check;
}
public void setCheck(String b) {
System.out.println("Setting check number : " + b);
check = b;
}
public double getAmount() {
System.out.println("Getting amount");
return amount;
}
public void setAmount(double a) {
System.out.println("Setting amount : " + a);
amount = a;
}
private void reset() {
System.out.println("Calling reset()");
final String emptyString = "";
final double zero = 0.0;
setAction(emptyString);
setId(emptyString);
setDateTran(emptyString);
setVc(emptyString);
setCheck(emptyString);
setAmount(zero);
}/* private void loadFromEJB() {
System.out.println("Calling loadFromEJB()");
try {
dateTran = account.getDate();
setDateTran(dateTran);
System.out.println(dateTran);
setVc(account.getVc());
setCheck(account.getCheck());
setAmount(account.getAmount());
} catch (Exception re) {
System.err.println ("Failed to load AccountBean from AcEJB.");
re.printStackTrace();
}
}*/
public String toString() {
StringBuffer output = new StringBuffer();
output.append("Action : " + action);
output.append( " Id : " + id);
output.append( "Transaction Date : " + dateTran);
output.append( " Particulars : " + vc);
output.append( " Check Number : " + check);
output.append( " Amount : " + amount);
return output.toString();
}
}
<jsp:useBean id="accountBean" scope="session" class="AccountBean" />
<jsp:setProperty name="accountBean" property="*" />
<%! String status; %>
<% status = accountBean.processRequest(); %>
<html>
<head>
<title>Account Interface</title>
</head>
<body bgcolor="pink">
<font size = 5 color="#CC0000">
<h1><b><center>Earnest Bank ATM</center></b></h1>
<br>
<form method=POST action=Account.jsp>
<BR>
<br>
<table border=0>
<tr>
<td>
Account ID
</td>
<td>
<INPUT type=text name="id" size="8" value="<jsp:getProperty name="accountBean" property="id" />">
</td>
<td>
Date of Transaction
</td>
<td>
<INPUT type=text name="dateTran" size="8" Value="<jsp:getProperty name="accountBean" property="dateTran" />" >
</td>
</tr>
<tr>
<td>
Particulars
</td>
<td>
<INPUT type=text name="vc" size="15" value="<jsp:getProperty name="accountBean" property="vc" />" >
</td>
<td>
Check Number
</td>
<td>
<INPUT type=text name="check" size="8" value="<jsp:getProperty name="accountBean" property="check" />" >
</td>
</tr>
<tr>
<td>
Amount
</td>
<td>
<INPUT type=text name="amount" size="8" value="<jsp:getProperty name="accountBean" property="amount" />" >
</td>
</tr>
</table>
<br>
<br> <INPUT type=hidden name="action" value="save">
<br>
<br>
<table border=0><tr>
<td>
<INPUT type=submit name="submit" value="Submit">
</td>
<td>
</td>
</tr>
</table>
</form>
</FONT>
</body>
</html>
<hr>
<h3><b>Status :</b></h3> <%= status %>
</html>
但是我试了,怎么都行不通。
我加了这2条语句
jdbc.drivers=com.microsoft.jdbc.sqlserver.SQLServerDriver:sun.jdbc.odbc.JdbcOdbcDriver:COM.cloudscape.core.RmiJdbcDriver
jdbc.datasource=jdbc/sqlServer1|jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=EarnestBank;user=sa;password=|jdbc/sqlServer|jdbc:odbc:MyDataSource|jdbc/Cloudscape|jdbc:Cloudscape:rmi:CloudscapeDB;create=true|我用cloudscape没错,证明代码没问题。
但是和sqlserver连接,2种方式都不行……郁闷阿
jdbcDataSource.5.name=jdbc/sqlServer
jdbcDataSource.5.url=jdbc:odbc:MyDataSource
jdbcDriver.1.name=sun.jdbc.odbc.JdbcOdbcDriver万事ok,注意的是数据源的用户名和密码如果设置了,JNDI必须也同样设置用户名和密码
我的系统使用 TOMCAT5.5.15+ SQL SERVER+ STRUTS1.2.6 开发的
本机调试时插入(修改)数据库没有问题,但是放到服务器上后插入(修改)数据库有问题,提示:
ERROR: 系统出错[[Microsoft][SQLServer 2000 Driver for JDBC]The DBMS returned an unspecified error.]
ERROR: queryFirstRow error: SQL(SELECT count(*) FROM (SELECT TOP 100 PERCENT hospID, hospName, hospProvince, hospCity FROM tbl_main_hospital ORDER BY hospID desc) sys_query_tmp)
我的系统使用 TOMCAT5.5.15+ SQL SERVER+ STRUTS1.2.6 开发的
本机调试时插入(修改)数据库没有问题,但是放到服务器上后插入(修改)数据库有问题,提示:
ERROR: 系统出错[[Microsoft][SQLServer 2000 Driver for JDBC]The DBMS returned an unspecified error.]
ERROR: queryFirstRow error: SQL(SELECT count(*) FROM (SELECT TOP 100 PERCENT hospID, hospName, hospProvince, hospCity FROM tbl_main_hospital ORDER BY hospID desc) sys_query_tmp)