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