Difference between Statement and Callable Stmt.

[banuchithrak]

Hello everybody,
Please clear my following doubt :
I use java.sql.Statement object to call a stored procedure.
Eg :
ResultSet res = stmt.executeQuery(\"select now()\");

This returns me the current database time.

If this is the case, why should I go for a Callable Statement?

It is used exclusively used to call stored procedures. I f I can call the stored procedure through a Statement object, why should there be a Callable Statement?

[Vasu]

Hi,
Since you are using statement.executeQuery() there will be a resultSet returned from it. But remember that it will be usefull only when we need a resultset with out outparameter.

Suppose if you have the code like this
CallableStatement cstmt = con.prepareCall( \" ?={proced name } \" );
cstmt.registerOutParameter ( 1, java.sql.Types.INTEGER );
cstmt.execute();
int id = cstmt.getInt(1);
You were trying to use a output parameter, so it should be mentioned
there with the \'?\'.

If you have output parameters, a CallableStatement is needed

About speed, you must have to choose callableStatements when you are
having output parameters, otherwise you can have statement.

Hope this helps.

[banuchithrak]

Okay Mr.Vasu,
But can you please explain me what output parameters mean? Maybe I should spend some more time on Callable Statements, but can\'t I get the output using a Statement object and a ResultSet.

If the Callable Statement is going to return me an o/p parameter as int, can\'t I get that as resultset.getInt(column name); ?

Why should I use a Callable Statement in such a case?

Thanks.

Banu\'s \"Back Again!\".. Awesome!

\"Why do you need a Callable Statement when you can use a getter method of a Prepared Statement ResultSet object to retrieve column data?\"

Like Vasu garu pointed out, Callable Statements are to be used in the event you use stored procedures to perform database queries! In large production environments, the goal/outcome of a development initiative is reusability. Stored procedures are a way for the database analyst/administrator to provide
a layer of abstraction for applications using the database (This responsibility is seldom borne by the DB Group). Nonetheless, your concept of using getter methods in the Resultset object will work fine if you have one resultset (which is normally the object returned after executing one SQL query). CallableStatements can be invoked to return multiple Resultset objects through stored procedures. Hence, to identify these multiple
ResultSet Objects, you would require a way to identify the output parameters/variables (Which are these ResultSet Objects).
To do so, you require CallableStatements!


HTH

[banuchithrak]

Hello Praveen, thank you for your enthusiastic welcome !

So Callable statements can be used when there are multiple returns. And at other times I can choose to use Statement instead of Callable Statement. I can use whichever I prefer.Am I right?

Again! Its your design decision! If you are aiming for performance, then taking a moment or two to figure out which would be the best is highly recommended! If you are using repeated SQL queries, then used PreparedStatements. If you are using stored procedures, use CallableStatements.

Good luck! and thank you for the enthusiastic acknowledgement..