Ramblings from a Bihari!

Just another WordPress.com weblog

Sharing the same connection or transaction space between derby sql and java.

Think of a situation in which you want to write a stored procedure in Derby and the procedure is calling a static java method inside the same JVM. The important requirement here is that the java method is is being executed by the SQL stored procedure is modifiying some data in the database. So finally the scenario is :: SQL is modifying the data as well as the java method is also modifying the data in the same database. So one must be thinking of transaction safety…. e.g when u rollback both the changes(made by SQL and the corresponding java method) must get rollback. Following is the example…….

Trigger which calls a stored procedure.:::::

CREATE TRIGGER COMPOSITE_U_T AFTER UPDATE ON COMPOSITE REFERENCING NEW AS NEWROW FOR EACH ROW MODE DB2SQL CALL PRO_TRIGGER(‘ID1’, ‘ID2’, NEWROW.ID1, NEWROW.ID2, ‘COMPOSITE’, ‘U’);

Procedure which is called by trigger and which in turn calls the static java method::::

CREATE PROCEDURE PRO_TRIGGER(P_K_1 VARCHAR(50), P_K_2 VARCHAR(50), PK_VAL1 INTEGER, PK_VAL2 INTEGER, TABLE_NAME VARCHAR(100), FLAG VARCHAR(2)) PARAMETER STYLE JAVA MODIFIES SQL DATA LANGUAGE JAVA EXTERNAL NAME ‘com.pramati.bfly.sync.util.db.TriggerClass.executeTriggerLogic’;

The java method which shares the same transaction space as the Procedure which is calling it.

public static void executeTriggerLogic(String pKColName1,String pKColName2, int pKValue1, int pKValue2, String tableName,

String flag) {

String updateDashTableQry = “update “

+ tableName

+ “_dash set time = current_timestamp, flag = ‘”

+ flag

+ “‘, user_id = ‘error’ “

+ (flag.equalsIgnoreCase(“I”) ? “, inserted_time = current_timestamp”

: “”) + ” where “ + pKColName1 + ” = “ + pKValue1

+ ” AND “ + pKColName2 + ” = “ + pKValue2;String insertIntoDashTableQry =

“insert into “ + tableName + “_dash values( “ + pKValue1 + “, “ + pKValue2

+ “, current_timestamp, ‘” + flag + “‘, “

+ (flag.equalsIgnoreCase(“I”) ? “current_timestamp” : null)

+ “, ‘error’)”;

try {

Connection conn = DriverManager

.getConnection(“jdbc:default:connection”);

String query = “select count(*) from “ + tableName + “_dash where “

+ pKColName1 + ” = “ + pKValue1 + ” AND “ + pKColName2

+ ” = “ + pKValue2;

int count = 1;

PreparedStatement ps = conn.prepareStatement(query);

ResultSet rs = ps.executeQuery();

if (rs.next()) {

count = rs.getInt(1);

}

if (count > 0) {System.out.println(“update query ::” + updateDashTableQry);

PreparedStatement ps1 = conn

.prepareStatement(updateDashTableQry);

ps1.executeUpdate();

} else {System.out.println(“insert query ::” + insertIntoDashTableQry);

PreparedStatement ps2 = conn

.prepareStatement(insertIntoDashTableQry);

ps2.executeUpdate();

}

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

 

}

The most important line as per sharing the same transaction space or sharing the same connection instance between the procedure and the static java method is

Connection conn = DriverManager

.getConnection(“jdbc:default:connection”);

You even dont need to load the Driver as in normal JDBC routine. The above is a very good example which can solve a major migration issue from a Oracle  database trigger to the Java Database(derby) trigger.

 

Cheers!!!!

Advertisements

September 21, 2007 - Posted by | techie stuff

1 Comment »

  1. Awesome .. really cool subject. I am goin to blog about it also!!

    Comment by Prireelorylew | January 2, 2010 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: