- Previously, create .jar file. Example:
package procedure;
public class procfortrigger {
public static void procInOut(String prm, long[] result){
try{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
Statement st=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
String strSQL="SELECT VAL FROM MYSCHEMA.MYTABLE "
+ "WHERE LOWER(MYFIELD)='" + prm.toLowerCase() + "'";
ResultSet rs=st.executeQuery(strSQL);
long val=0L;
long addVal = val + 1;
if(rs.next()) {
val=rs.getLong(1);
addVal = val + 1;
String updVal="UPDATE MYSCHEMA.MYTABLE SET VAL=" + addVal
+ " WHERE LOWER(MYFIELD)='" + prm.toLowerCase() + "'";
conn.prepareStatement(updVal).execute();
} else {
String insSeq="INSERT INTO MYSCHEMA.MYTABLE (MYFIELD, VAL) VALUES ('"
+ prm.toLowerCase() + "', "
+ addVal + ")";
conn.prepareStatement(insSeq).execute();
}
rs.close();
st.close();
rs=null;
st=null;
conn.close();
conn=null;
result[0] = addVal;
}catch (Exception ex){
result[0]=0;
}
}
}
Compile to .jar file (example: DDA_derby.jar).
- Create procedure:
CREATE PROCEDURE APP.PROCINOUT(IN PRM1 VARCHAR(200), OUT RESULT BIGINT)
PARAMETER STYLE JAVA
MODIFIES SQL DATA
LANGUAGE JAVA
EXTERNAL NAME 'procedure.procfortrigger.procInOut';
GRANT EXECUTE ON PROCEDURE APP.PROCINOUT TO other_user;
Test:
public long intSeq(Connection dnCon, String dnSeqName){
try{
CallableStatement cs = dnCon.prepareCall("{call APP.PROCINOUT(?,?)}");
cs.setString(1, dnSeqName);
cs.registerOutParameter(2, Types.BIGINT);
cs.execute();
long num_ = cs.getLong(2);
cs.close();
cs=null;
return num_;
}catch (Exception ex){
System.out.println("err-" + dnSeqName + ": " + ex);
return 0L;
}
}
Tired? Confuse? May be we would better drink a glass of coffee. Just for refreshing...
