Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Prepared Statement vs Callable Statement

 
Nupur Gupta
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
     
    Nupur Gupta
    Ranch Hand
    Posts: 45
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 1364
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 11
    • Likes 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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.
     
    chiku patel
    Greenhorn
    Posts: 2
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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.




     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic