gpt4 book ai didi

Java - MySql 存储过程 "No data - zero rows fetched, selected, or processed"

转载 作者:行者123 更新时间:2023-11-30 22:55:54 26 4
gpt4 key购买 nike

在我的网络应用程序中,当我尝试通过 ResultSet 检索已保存数据和选择框的数据时,我在调用以下过程时遇到以下错误。请提供宝贵的解决方案谢谢

发现错误:

  Error: java.sql.SQLException: No data - zero rows fetched, selected, or processed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2818)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2157)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)
at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:920)
at com.schoolmanager.util.StoreProcHandler.accessStoredProcedure(StoreProcHandler.java:63)
at org.apache.jsp.home_jsp._jspService(home_jsp.java:187)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)

java :

StoreProcHandlerClass 的方法:

     public ResultSet accessStoredProcedure(List<Object> parameters,
String procedureCalled) throws CommunicationsException {
ResultSet rSet = null;
int noofParameters = parameters.size();
try {

System.out.println("parameters " + parameters);
System.out.println("Procedure Elements " + "{call " + procedureCalled + "("
+ getGenList(parameters.size()) + ")}");
callStat = connect.prepareCall( "{call " + procedureCalled + "("
+ getGenList(parameters.size()) + ")}");

for (int i = 1; i <= noofParameters; i++) {
Object obj = parameters.get(i-1);

if (obj.toString().contains("Types")) {
callStat.registerOutParameter(i, Types.VARCHAR);
} else {
callStat.setObject(i, obj);
}
}
callStat.execute();
rSet = callStat.getResultSet();
if(rSet != null){
rSet.next();
}

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
finally {
try {

logger.info("Procedure Executed from ASP ="+procedureCalled);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

return rSet;

}

错误部分:

    List<Object> parameters = null;
ResultSet rSet = null;
Connection con = (Connection) request.getAttribute("accessConnect");
int noOfCols = 4;
String qMsg = null;
System.out.println("session.getAttribute " + session.getAttribute("userType"));
parameters =
new ArrayList<Object>(
Arrays.asList(session.getAttribute("userMail"),
session.getAttribute("userType"),
Types.VARCHAR,
Types.VARCHAR, Types.VARCHAR,
Types.VARCHAR, Types.VARCHAR ));
StoreProcHandler sph = new StoreProcHandler(con);
rSet = sph.accessStoredProcedure(parameters,
"GET_USER_PROFILE");
if (rSet != null)
{
rSet.beforeFirst();
rSet.last();
System.out.println("rSet row " + rSet.getRow());
}

Mysql 存储过程:

  CREATE DEFINER=`user_name`@`%` PROCEDURE `GET_USER_PROFILE`(
IN p_user_id VARCHAR(150),
IN p_role VARCHAR(150),
OUT p_user_data VARCHAR(200),
OUT p_city VARCHAR(150),
OUT p_state VARCHAR(150),
OUT p_country VARCHAR(150),
OUT q_Msg VARCHAR(150))
BEGIN

DECLARE available INT DEFAULT 0;

DECLARE CUR_CITY CURSOR for SELECT CITY FROM COUNTRIES;
DECLARE CUR_STATE CURSOR for SELECT STATE FROM COUNTRIES;
DECLARE CUR_COUNTRY CURSOR for SELECT COUNTRY FROM COUNTRIES;
DECLARE CUR_U_DATA CURSOR FOR SELECT * from STAFF_PROFILE WHERE EMAIL = p_user_id AND ROLE = p_role;

SELECT COUNT(EMAIL) INTO available FROM STAFF_PROFILE WHERE EMAIL = p_user_id AND ROLE = p_role;


CALL sql_logger('user_profile', p_city);

OPEN CUR_CITY;
OPEN CUR_STATE;
OPEN CUR_COUNTRY;

get_list: LOOP
FETCH CUR_CITY INTO p_city;
FETCH CUR_STATE INTO p_state;
FETCH CUR_COUNTRY INTO p_country;
END LOOP get_list;

IF(available=1) THEN
OPEN CUR_U_DATA;
get_data: LOOP
FETCH CUR_U_DATA INTO p_user_data;
END LOOP get_data;
else
SET q_Msg = 'USER_LOGGED_FIRST';
END IF;

END

最佳答案

是的,现在我找到了这个问题的解决方案,我把结果集和 OUT 参数混淆了,还发现了 java.sql.ResultSet 的以下代码方式

java 代码部分:(在从 CallableStatement 调用过程后添加)

        callStat.execute();

int cols = rsmd.getColumnCount();
ResultSetMetaData metaData = null;
int resultRow = 1;
int col = 1;
boolean result = callStat.execute();

while(result){
ResultSet rs = callStat.getResultSet();
col=1;
while(rs.next()){
metaData = rs.getMetaData();
String colName = metaData.getColumnName(1); // column to be retrieved

String value = (String) rs.getObject(colName);;
System.out.println(value);
col++;
}
resultRow++;
rs.close();
result = callStat.getMoreResults(CallableStatement.CLOSE_CURRENT_RESULT);
}

MYSQL 存储过程:

CREATE DEFINER=`user_name`@`%` PROCEDURE `GET_USER_PROFILE`(
IN p_user_id VARCHAR(150),
IN p_role VARCHAR(150),
OUT q_Msg VARCHAR(150))
BEGIN

SELECT DISTINCT state FROM countries ;
SELECT DISTINCT city FROM countries ;
SELECT * FROM countries ORDER BY COUNTRY ASC;



SET q_Msg = 'EXECUTED';

关于Java - MySql 存储过程 "No data - zero rows fetched, selected, or processed",我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/26379410/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com