Discussion:
problem executing anonymous block
(too old to reply)
jlyin
2018-02-07 17:34:12 UTC
Permalink
Raw Message
We have a TDI adapter for ITIM. The adapter is suppose to execute anonymous blocks to run stored procedures to create accounts in the native system.

1.We originally looked into using TDI adapter’s in built JDBC Connector to call SQL script. The code in essence something like:

var processString = new processbuildertest.ReadFileToString.readFile("D:\\dev\\SQL\\cmps_ins_wip_svr.sql");
try {
res =conCompassDB.connector.execSQL(processString);
}
The code never successfully executed.


2 so we used another approach to call the PL/SQL file thru a MS-DOS batch script. Thru the TDI adapter, we had a Java script, that did the DOS command calls. This led to native processes hanging and deadlock etc:

This is what the code looks like:
function runDOSCommand(runCommand, file) {
var p;

try {
p = java.lang.Runtime.getRuntime().exec(runCommand, null, file);


var br = new java.io.BufferedWriter(
new java.io.OutputStreamWriter(p.getOutputStream()));

br.close();

var bri = new java.io.BufferedReader(new java.io.InputStreamReader(p.getInputStream()));


var bre = new java.io.BufferedReader(new java.io.InputStreamReader(p.getErrorStream()));
var line = null;
while ((line = bri.readLine()) != null) {
task.logmsg("INFO",line);
}
bri.close();
while ((line = bre.readLine()) != null) {
task.logmsg("INFO",line);
}
bre.close();
var exitVal = p.waitFor();
task.logmsg("INFO","Exit Value:" + exitVal);

} catch (e) {
task.logmsg(e);
processError();
} finally {
// tmp.delete();
}
}

3. Then we tried we tried using Java application that uses ProcessBuilder to do the DOS command calls, and it looks like:


var runAddCommand = compassAddUserBatch + uidParameters;
task.logmsg("INFO","scAddJSBat: (runAddCommand)" + runAddCommand);
var processObj = new processbuildertest.JavaExec(compassSqlDir, runAddCommand);

During multiple commands calls, e.g. multiple new users being added, the TDI adapter makes iterative calls to these processes, we see in logs, the calls are made to the batch scripts, some of the calls are never executed.


Please give us some suggestions how we can make it work. Thanks
Franzw
2018-02-08 14:23:20 UTC
Permalink
Raw Message
Post by jlyin
We have a TDI adapter for ITIM. The adapter is suppose to execute anonymous blocks to run stored procedures to create accounts in the native system.
var processString = new processbuildertest.ReadFileToString.readFile("D:\\dev\\SQL\\cmps_ins_wip_svr.sql");
try {
res =conCompassDB.connector.execSQL(processString);
}
The code never successfully executed.
function runDOSCommand(runCommand, file) {
var p;
try {
p = java.lang.Runtime.getRuntime().exec(runCommand, null, file);
var br = new java.io.BufferedWriter(
new java.io.OutputStreamWriter(p.getOutputStream()));
br.close();
var bri = new java.io.BufferedReader(new java.io.InputStreamReader(p.getInputStream()));
var bre = new java.io.BufferedReader(new java.io.InputStreamReader(p.getErrorStream()));
var line = null;
while ((line = bri.readLine()) != null) {
task.logmsg("INFO",line);
}
bri.close();
while ((line = bre.readLine()) != null) {
task.logmsg("INFO",line);
}
bre.close();
var exitVal = p.waitFor();
task.logmsg("INFO","Exit Value:" + exitVal);
} catch (e) {
task.logmsg(e);
processError();
} finally {
// tmp.delete();
}
}
var runAddCommand = compassAddUserBatch + uidParameters;
task.logmsg("INFO","scAddJSBat: (runAddCommand)" + runAddCommand);
var processObj = new processbuildertest.JavaExec(compassSqlDir, runAddCommand);
During multiple commands calls, e.g. multiple new users being added, the TDI adapter makes iterative calls to these processes, we see in logs, the calls are made to the batch scripts, some of the calls are never executed.
Please give us some suggestions how we can make it work. Thanks
If I understand you correct you basically need to run a stored procedure on an Oracle Database ?

In that case look here: http://www.tdi-users.org/foswiki/Integrator/TDIFAQ#mozTocId67054 or search this group for "stored procedure".

HTH
Regards
Franz Wolfhagen

Loading...