存储过程无法从java代码中提供准确的结果

I have stored procedure that searches persons on basis of any given inputs (any bio data e.g blood group, name, identity no. etc. taking almost 26 inputs).

我有存储过程,根据任何给定的输入搜索人(任何生物数据,例如血型,名称,身份号等,接受近26个输入)。

I am executing a stored procedure (sql server) from java using Callable Statement preparecall method. Previously I had a problem that my stored procedure was not giving any results (0 row result) whenever the string input was passed in one of its many input parameters. On other hand gave correct results when any integer type parameter value was passed but when I changed jdbc driver from jdbc:odbc driver to sqlserver driver (driver name=com.microsoft.sqlserver.jdbc.SQLServerDrive), the problem was solved.

我正在使用Callable Statement preparecall方法从java执行存储过程(sql server)。以前我遇到的问题是,只要字符串输入在其众多输入参数之一中传递,我的存储过程就不会给出任何结果(0行结果)。另一方面,当传递任何整数类型参数值但是当我将jdbc驱动程序从jdbc:odbc驱动程序更改为sqlserver驱动程序(驱动程序名称= com.microsoft.sqlserver.jdbc.SQLServerDrive)时,给出了正确的结果,问题得以解决。

Now my new problem is that when my procedure procSearch2, that is taking 16 inputs, is running it gives results perfectly fine with the same Connection variable of jdbc driver (changed one), but the other procedure, which have 27 inputs (procSearch1) is not giving any results even with int or string input values passed.

现在我的新问题是,当我的程序procSearch2(即16个输入)正在运行时,它使用相同的jdbc驱动程序的连接变量(已更改)得到完全正常的结果,但另一个有27个输入(procSearch1)的过程是即使传递了int或字符串输入值,也不会给出任何结果。

It does not give any error it just returns me 0 row result where it should return 3 rows result.

它没有给出任何错误它只返回0行结果,它应返回3行结果。

here is the code

这是代码

 CallableStatement stmt=connect.prepareCall("{CALL  procSearch1(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
 stmt.setInt(1,0);
 stmt.setString(2,"");
 stmt.setString(3,"");
 .
 .
 .
stmt.setInt(27,0);
 ResultSet result=stmt.executeQuery();

 if(result!=null)
{
   System.out.println(result.getRow()); 
  while(result.next)
  {
    System.out.println(result.getString("Name"));

   }

 }

1 个解决方案

#1

You need to set an output paramater to the CallableStatement to read the return value of the sp. Something like this (exception handling omitted for brevity):

您需要将一个输出参数设置为CallableStatement以读取sp的返回值。像这样的东西(为简洁省略了异常处理):

CallableStatement  stmt = connection.prepareCall("{? = call procSearch1(?,?,?) }");
stmt.registerOutParameter(1, java.sql.Types.INTEGER);
stmt.setString(2, "abc");
stmt.setString(3, "xyz");
stmt.execute();
Integer sp_return_value = stmt.getInt(1);

EDIT: Based on your comment, if your SP returns the result of a Select, ie, a resultSet, I think you need to use statement.execute() instead of the executeQuery().

编辑:根据您的评论,如果您的SP返回Select的结果,即resultSet,我认为您需要使用statement.execute()而不是executeQuery()。

See http://msdn.microsoft.com/en-us/library/ms378630.aspx & http://msdn.microsoft.com/en-us/library/ms378487.aspx.

请参阅http://msdn.microsoft.com/en-us/library/ms378630.aspx和http://msdn.microsoft.com/en-us/library/ms378487.aspx。

So, your code should be something like:

所以,你的代码应该是这样的:

CallableStatement stmt=connect.prepareCall("{CALL  procSearch1(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
stmt.setInt(1,0);
stmt.setString(2,"");
stmt.setString(3,"");
.
.
.
stmt.setInt(27,0);
ResultSet result = stmt.execute();

if(result!=null) {
    System.out.println(result.getRow()); 
    while(result.next) {
        System.out.println(result.getString("Name"));
    }
}

#1