lundi 27 avril 2015

Stored procedure execution issue

I have few stored procedures at not my MS SQL Server 2005. From my Java project via jdbc I can execute most of them. They did only read from database that's why I used Statement.executeQuery() method. Now I want to insert new data via one of this procedures.

Method Statement.executeQuery() returned me an error sqlserverexception the statement did not return a result set. I found that I have to use Statement.execute(). Now I have no exceptions, but new data wasn't added to database. Also as result I have to get dataset but I can't get it.

Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        JSONObject resultObject = new JSONObject();
        int code = 0;
        try{
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
            String connectionUrl = "jdbc:sqlserver://" + Utils.getServerName() + ":1433;" + "DatabaseName=" + Utils.getDatabaseName(); 
            con = DriverManager.getConnection(connectionUrl, Utils.getUserName(), Utils.getUserPassword());
            ps = con.prepareStatement("EXEC PDA_WorkTimeRegister ?,?,?,?");
            ps.setEscapeProcessing(true);
            ps.setInt(1, workId);
            ps.setInt(2, workerId);
            ps.setTimestamp(3, new java.sql.Timestamp(Utils.fromStringToMilliseconds(startDate)));
            ps.setTimestamp(4, new java.sql.Timestamp(Utils.fromStringToMilliseconds(endDate)));
//          rs = ps.executeQuery();
            ps.execute();
            if(ps.getMoreResults()){
                rs = ps.getResultSet();
                if(rs != null)
                    while(rs.next()){
                        code = rs.getInt("wtr_code");
                    }
            }
        }catch(Exception e){
            e.printStackTrace();
            return Response.status(200).entity(Utils.getErrorJSONString(e.toString())).build();
        }finally{
            try{
                if(rs != null){
                    rs.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
            try{
                if(ps != null){
                    ps.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }

Stored procedure looks like this.

CREATE PROCEDURE PDA_WorkTimeRegister
(
 @JobID   int,
 @StaffID int,
 @DateFrom DateTime,
 @DateTo   DateTime
) 
as
SELECT
        wtr_code,   -- int,  код строки в таблице(ключ)
        wtr_date = datetime

How can I do it right?

Aucun commentaire:

Enregistrer un commentaire