PreparedStatement 偶然遇到问题

最近做了个项目。。测了好几遍了。。。然后突然测试那边的数据库环境执行一条SQL语句不行了。报错“ java.net.SocketException: Connection reset”
SQL语句为(

select ID as id,collageID as collageId,parentID as parentId ,orgName as orgName
from collage_orgGroups where ID IN(SELECT ID FROM dbo.F_GetChildren(?))
)其中F_GetChildren是一个函数,传个父节点的ID值返回所有全递归子节点。这sql语句绝对没问题,在本地和测试的查询分析器里执行是正常的。然后在本机测试了程序下可以。其他开发部门同事机子上也可以。就测试部门不可以。我把本地库分离下来附加到测试那边的环境。还是不行。我的猜测就是测试那边数据库环境出现错误了。。。但是却不知道是哪出错了。。。正要放弃时突然发现我如果不用PreparedStatement的预编译
pst.setInt(1,1);
直接拼接sql语句
select ID as id,collageID as collageId,parentID as parentId ,orgName as orgName from collage_orgGroups where ID IN(SELECT ID FROM dbo.F_GetChildren(1))
就可以。。。。很奇怪。。网上找了很多资料。但是没个准确的解释。希望大家知道了。。说声。。。。
问题补充
抛出异常的爱 写道
连接未关闭。
建议使用组件

抛哥不是吧。。。我贴下完整代码
public static void main(String[] args) throws IOException {
Connection conn=null;
PreparedStatement pst=null;
ResultSet rs=null;
try {
conn=getConnByParam("192.168.0.102", "sa", "123", "NewsDBTest");
System.out.println(conn);
String sql="select ID as id,collageID as collageId,parentID as parentId ,orgName as orgName"
+" from collage_orgGroups where ID IN(SELECT ID FROM dbo.F_GetChildren(?))";
pst=conn.prepareStatement(sql);
pst.setInt(1,1);
rs=pst.executeQuery();
while(rs.next()){
System.out.println(rs.getString("id")+"---"+rs.getString("orgName")+"---"+rs.getString("parentId"));
}
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        try {
            if(conn!=null){
                conn.close();
            }
            if(pst!=null){
                pst.close();
            }
            if(rs!=null){
                rs.close();
            }
        } catch (Exception e) {
        }
    }

}</pre><br /><br />异常:<pre name="code" class="java">java.sql.SQLException: I/O Error: Connection reset by peer: socket write error
at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java:1053)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:465)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:776)
at cn.com.shanli.collageNews.business.utils.CommUtils.main(CommUtils.java:164)

Caused by: java.net.SocketException: Connection reset by peer: socket write error
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
at java.io.DataOutputStream.write(DataOutputStream.java:90)
at net.sourceforge.jtds.jdbc.SharedSocket.sendNetPacket(SharedSocket.java:676)
at net.sourceforge.jtds.jdbc.RequestStream.putPacket(RequestStream.java:560)
at net.sourceforge.jtds.jdbc.RequestStream.flush(RequestStream.java:508)
at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java:1040)
... 3 more



如果我屏蔽掉 pst.setInt(1,1);然后把sql语句中的问号改成1,就可以了。。。难道是我函数原因。但是在我和我同事机子上也可以啊。
函数语句:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[F_GetChildren]') AND xtype in (N'FN', N'IF', N'TF'))
BEGIN
execute dbo.sp_executesql @statement = N'Create Function [dbo].F_GetChildren
Returns @Tree Table (ID Int)
As
Begin
Insert @Tree Select ID From collage_orgGroups Where parentID = @Pid

While @@Rowcount > 0
Insert @Tree Select A.ID From collage_orgGroups A Inner Join @Tree B On A.ParentID = B.ID And A.ID Not In (Select ID From @Tree)
Return
End
'
END
查看全部
xcc258
xcc258
2011/11/29 09:40
  • dao
  • 点赞
  • 收藏
  • 回答
    私信
满意答案
查看全部

0个回复