GeeCON Prague 2014*
The moose likes JDBC and the fly likes Prepared Statement vs Callable Statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Prepared Statement vs Callable Statement" Watch "Prepared Statement vs Callable Statement" New topic
Author

Prepared Statement vs Callable Statement

Nupur Gupta
Ranch Hand

Joined: Aug 02, 2006
Posts: 45
Hi,
What is the difference between a PreparedStatement and a CallableStatement?

I have searched a lot on the internet for it, and all the replies are confusing, contradictory.

This is what I have gleaned so far -

Prepared statement is a set of SQL queries(or a call to a stored procedure). This is precompiled on the database. One can call it again and again, specifying different paramaters each time.

Callable Statement is also a call to a stoerd procedure, but it is permanently precompiled, and is called again and again , passing different parameters.

So it seems to be the difference between 'precompiled' and 'permanently precompiled'. What does this mean? Are they not both a set of SQL queries, ie a stored procedure? Further I read that a Callable Statement is a subclass of a Prepared statement . so the plot just gets thicker. Hmm..
Help much appreciated.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

At the top level you can go by this thought
  • Prepared Statement

  • Instances of PreparedStatement contain an SQL statement that has already been compiled. This is what makes a statement "prepared"

    Because PreparedStatement objects are precompiled, their execution can be faster than that of Statement objects.

    The prepared statement is used to execute sql queries
  • Callable Statement

  • A CallableStatement object provides a way to call stored procedures in a standard way for all RDBMSs. A stored procedure is stored in a database; the call to the stored procedure is what a CallableStatement object contains.



    call to a stoerd procedure, but it is permanently precompiled



    I am not sure what you have understood by statement, but here permanently precompiled referes to stored procedure, which is residing in database and nothing to do with database.

    I hope this explains the difference ...

    Thanks,
    Shailesh


    Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
    Nupur Gupta
    Ranch Hand

    Joined: Aug 02, 2006
    Posts: 45
    Thanks a lot for your help Shailesh.But I really dont understand.

    "Instances of PreparedStatement contain an SQL statement that has already been compiled. This is what makes a statement "prepared" "

    "A CallableStatement object provides a way to call stored procedures in a standard way for all RDBMSs. A stored procedure is stored in a database; the call to the stored procedure is what a CallableStatement object contains."

    So both of them seem to be the same thing. Both of them seem to consist of SQL statements which are already compiled, and hence faster to execute. What is the difference?

    Would much appreciate any feedback.
    Carol Enderlin
    drifter
    Ranch Hand

    Joined: Oct 10, 2000
    Posts: 1364
    A stored procedure is in the database (permanently compiled) and may be called by a CallableStatment.

    A PreparedStatement compiles a query in your java code. It may be re-used, therefore it is pre-compiled...compiled before you use it. Perhaps more important these days, you can use question marks (?) in your query to represent values you need to set using PreparedStatements (cannot do that with plain Statement).

    PreparedStatement tutorial
    Jeff Ash
    Greenhorn

    Joined: Apr 07, 2007
    Posts: 11
    CallableStatement extends the capabilities of a PreparedStatement to include methods that are only appropriate for stored procedure calls. For example, the registerOutParameter() method is something only applicable to stored procedures since they have parameters with a direction (out, in, or in/out). So, use CallableStatments only when calling a stored procedure on the database, and use PreparedStatement for executing other statements such as SELECT, INSERT, UPDATE, etc.


    Jeff Ash<br /><a href="http://www.excellentiasoftware.com" target="_blank" rel="nofollow">Excellentia Software</a>
    chiku patel
    Greenhorn

    Joined: May 06, 2011
    Posts: 2
    Hi Jeff,

    In my application, I need to use simple delete statement with store procedure using CallableStatement. Below is the scenario...

    String procedure =""
    oPB = new OracleProcedureBuilder();
    oPB.setAutoCommitOff();
    procedure = "BEGIN DELETE FROM XXX.ABC_XYZ WHERE X ='Y';

    XXX.CAB_ZYX_SP(?, ?, ?);

    END; ";
    oPB.prepareCall(procedure);
    oPB.setString(1, X);
    oPB.setString(2, Y);
    oPB.setString(3, Z);


    oPB.execute();
    oPB.commit();
    oPB.closeResources();

    Que: Can I use Delete, Select, Insert or Update statement with the Store procedure name with in BEGIN and END block??

    If I can, what are the pros and cons? And if not, why I can't use it?

    Please let me know if you need more input.

    I appreciated your time.




     
    GeeCON Prague 2014
     
    subject: Prepared Statement vs Callable Statement