callable statement notes

* For procs that write to DB without returning any OUT param, I found preparedStatmeent (PS) and CS interchangeable.
* For procs that only read from DB via SELECT, I found PS and CS interchangeable.

The only CS i can’t convert to a PS are those returning something via params “{? = call proc1 @param1=?, @param2=? }”

Now If you look at the get*() methods and set*() methods, you notice an interesting discrepancy.

* getFloat() can take a paramIndex or a paramName arg, but
* setFloat() only takes paramName. Here’s why:

callableStatement3.setFloat(1, 0.95); // this method is inherited from parent interface PreparedStatement. Sets first “?” to 0.95.

callableStatement3.setFloat(“id”, 0.95); // this method is not declared in PS, since PS doesn’t support param name.

callableStatement3.getFloat(1); // this method is declared in CallableStatement interface. Reads 1st OUT param. PS doesn’t let you read anything except via a ResultSet.

callableStatement3.getFloat(“id”); // ditto

Lastly, Do not confuse the get* methods with the ResultSet.get* methods. CS get*() reads params, not rows selected.

monitor jdbc calls received at sybase

snoop?
sybase log? realtime with no buffering?

throw-away PreparedStatement +! param

%% Answer: Unimportant question. If you must know the technical answer, it's usable but not recommended

 

–based on http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html :

The main feature of a PreparedStatement object is that, unlike a Statement object, it is given an SQL statement when it is created. The advantage to this is that in most cases, this SQL statement is sent to the DBMS right away, where it is compiled(in *DBMS*). As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first.

Although PreparedStatement objects can be used for SQL statements with no parameters, you probably use them most often for SQL statements that take parameters.

weblogic jdbc "query cache"

the first time a query is run, Liquid Data saves its results into a query results cache. The next time the query is run with the same parameters, Liquid Data checks the cache configuration and, if the results have not expired, quickly retrieves the results from the cache.

similar to EHcache read-only mode

callable-Statement to call a stored procedure

%% nlg pearls:
stored procedures can have some IN, OUT or INOUT params
stored procedures can return 1 or more rows

To call stored procedures, you invoke methods in the CallableStatement class. The basic steps are:

1. Invoke the Connection.prepareCall method to create a CallableStatement object.
2. Invoke the CallableStatement.setXXX methods to pass values to the input (IN) parameters.
3. Invoke the CallableStatement.registerOutParameter method to indicate which parameters are output-only (OUT) parameters, or input and output (INOUT) parameters.
4. Invoke one of the following methods to call the stored procedure:
CallableStatement.executeUpdate
Invoke this method if the stored procedure does not return result sets.

CallableStatement.executeQuery

Invoke this method if the stored procedure returns one result set.

CallableStatement.execute

Invoke this method if the stored procedure returns multiple result sets.

5. If the stored procedure returns result sets, retrieve the result sets. See Retrieve multiple result sets from a stored procedure in a JDBC application.
6. Invoke the CallableStatement.getXXX methods to retrieve values from the OUT parameters or INOUT parameters.

The following code illustrates calling a stored procedure that has one input parameter, four output parameters, and no returned ResultSets. The numbers to the right of selected statements correspond to the previously-described steps.

Figure 33. Using CallableStatement methods for a stored procedure call with parameter markers
int ifcaret;
int ifcareas;
int xsbytes;
String errbuff;
Connection con;
CallableStatement cstmt;
ResultSet rs;
…
cstmt = con.prepareCall(“CALL DSN8.DSN8ED2(?,?,?,?,?)”);  1 
// Create a CallableStatement object
cstmt.setString (1, “DISPLAY THREAD(*)”);  2 
// Set input parameter (DB2 command)
cstmt.registerOutParameter (2, Types.INTEGER);  3 
// Register output parameters
cstmt.registerOutParameter (3, Types.INTEGER);
cstmt.registerOutParameter (4, Types.INTEGER);
cstmt.registerOutParameter (5, Types.VARCHAR);
cstmt.executeUpdate(); // Call the stored procedure  4 
ifcaret = cstmt.getInt(2); // Get the output parameter values  6 
ifcareas = cstmt.getInt(3);
xsbytes = cstmt.getInt(4);
errbuff = cstmt.getString(5);
cstmt.close();  7 

hibernate transaction — a comprehensive intro

— from http://www.hibernate.org/42.html, which also covers ejb, cmt, jdbc, interceptor …

1-sentence intro: Usually you also flush a Session at the end of a
unit of work to execute the SQL DML operations (UPDATE, INSERT,
DELETE) that synchronize the in-memory Session state with the
database.

Hibernate disables or expects the environment (in J2EE/JEE) to disable
auto-commit mode

— sess vs tx
A single Hibernate Session might have the same scope as a single database transaction. DB server probably maintains a session for each connection. I would speculate

1 hib-session – 1 server-session

— hibernate && jta
Hibernate works in any environment that uses JTA, in fact, we recommend to use JTA whenever possible as it is the standard Java transaction interface. You get JTA built-in with all J2EE/JEE application servers, and each Datasource you use in such a container is automatically handled by a JTA TransactionManager.

— no jta@@
If you don’t have JTA and don’t want to deploy it along with your application, you will usually have to fall back to JDBC transaction demarcation. Instead of calling the JDBC API you better use Hibernate’s Transaction interface

jdbc conn pool — swimming pool

there’s some logic/intelligence involved in pool growth/shrinking, conn reclaim … That logic is somehow provided by the servlet container, within the same JVM. I think it’s provided by the “swimming pool manager”.

“container-managed conn pooling” is a common phrase. Servlet Container maintains a pool of connection objects — each a chunk of memory.

A primitive implementation is a hashmap in the container’s memory, A hashmap of physical (ie in-memory) PooledConnection objects.


“swimming pool manager” is a boundless (can’t bypass) wall between the servlets and the pool of PooledConnection objects, and exposes to the servlets essentially 2 methods — getConnection() and closeConnection() . Not sure about the method names. Reacting to these calls, the swimming pool manager hands out or reclaims a “physical” connection from the servlet to the pool.

“swimming pool manager” is the single “spokesman” and single point of access of the pool.

“swimming pool manager” is request-driven. I think a class will “send it a message” by calling poolManager.getConnection() or poolManager.closeConnection()

In Weblogic, a swimming pool manager (the hashmap of conn objects) may need 2 helpers — dataSource and a pool driver.
* i think u need to specify the driver in your jdbc calls
* You can choose to use dataSource when obtaining a connection from the swimming pool manager.