CODEWITHSUNDEEP
javasqlsql-serverstored-proceduresjdbc
Timofei Davydik
Timofei Davydik

Reputation: 7303

JDBC PreparedStatement, how to select and insert?

I'm trying to execute stored procedure via JDBC, which has simple structure:

ALTER PROCEDURE test
AS BEGIN
SELECT ...
INSERT ...
END

The problem is that insert is not happened. When I execute this procedure in management studio, then it's all right.

String query = "EXEC test";
PreparedStatement st = conn.prepareStatement(query);
st.execute();
ResultSet rs = st.getResultSet(); //result set is correct
int count = st.getUpdateCount(); //returns -1

Any ideas? Thanks

Upvotes: 1

Views: 801

Answers (3)

maiklahoz
maiklahoz

Reputation: 135

you can try change the value of query to String query = "{call test()}" and then you can use

CallableStatement cs = connection.prepareCall(query);

cs.execute();

you can need commit to view your changes in the database.

conn.commit();

Upvotes: 1

Rahul
Rahul

Reputation: 45080

Since its a procedure you're trying to call, you need to use a CallableStatement.

The interface used to execute SQL stored procedures.

CallableStatement callableStatement = conn.prepareCall(query);

Also, your query needs to be

String query = "{call test}"; 
// exec is used to execute the procedure from the sql console as such
// To execute a procedure using the java code, use call proc.

Upvotes: 1

Thirumalai Parthasarathi
Thirumalai Parthasarathi

Reputation: 4671

you should be using a callable statement instead of prepared statement

CallableStatement cstmt = null;
try {
   String SQL = "{call procedureName}";
   cstmt = conn.prepareCall (SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   . . .
}

Upvotes: 1

Related Questions