hiteksoftware.com

[altman80]

We are currently using Automize 7 and are using it to execute some Oracle stored procedures. I am using the ojdbc14.jar from Oracle to connect. The procedure runs as it should but what we've just found out is that automize is not releasing the connection to Oracle. We found this as we reached the max number of connections this morning. The task log shows that the task is starting and ending with exit code 0. Is there something I have set up wrong or is this a bug? My settings are as follows:


Driver Class Name: oracle.jdbc.driver.OracleDriver
Database URL: jdbc:oracle:oci:@myDatabase
The SQL is a stored procedure that looks somewhat like this:
Begin; MySP(); End;

[Robert]

our code explicitly closes the DB connection.
is there any errors in the debug log or task logs?
attach snippets of your logs from the last task run.

[altman80]

Database SQL - Shift_Task - Schedule Title = Shift_Task , Scheduled execution time = Jul 7, 2008 5:00:00 AM

Jul 7, 2008 5:00:00 AM

Database SQL - Shift_Task - Start Instance = 94

Jul 7, 2008 5:00:31 AM

Database SQL - Shift_Task - Rows updated/inserted/deleted = -1

Jul 7, 2008 5:00:31 AM

Database SQL - Shift_Task - End Instance = 94 , Exit Code = 0 , Runtime (ms) = 31406

The task log looks like this for every instance it ran.
Upon further looking though, the problem must be intermittent as we have this run every 8 hours and different one run every 15 minutes to the same database. We have a maximum of 150 connections set up in Oracle and this has been running for a while until we reached the maximum connections. When we got the max connections error though there were numerous connections to Oracle still left open from one of these 2 tasks.

[Robert]

is there anything in the debug log around 5:00:xx or 5:01:xx ?

Jul 7, 2008 5:00:31 AM
Database SQL - Shift_Task - Rows updated/inserted/deleted = -1

[altman80]

There is nothing for that specific time but there are logs with errors in them for the stored procedure. This gave the following:
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:2349)

at oracle.jdbc.oci8.OCIDBAccess.executeFetch(OCIDBAccess.java:1757)

at oracle.jdbc.oci8.OCIDBAccess.parseExecuteFetch(OCIDBAccess.java:1915)

at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)

at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)

at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:961)

at com.hitek.engine.mods.database.DBSelectTask.executeSQL(DBSelectTask.java:216)

at com.hitek.engine.mods.database.DBSelectTask.dbSelectTask(DBSelectTask.java:85)

at com.hitek.engine.mods.database.DBSelectTask.run(DBSelectTask.java:45)


If the stored procedure crashes, does this leave the connection to the database open? We have since restarted our Oracle server so I cannot compare that the crash times are in line with the stranded connections.

[melvin]

the connection is always checked and closed, error or no error.

run the tasks as external process. (engine menu / external processes)
this could trick the DB into releasing the connection, since the process dies after completion.

[altman80]

Thanks for the help, I will try that.

[altman80]

I tried it as an external process and it doesn't run it at all as an external process. The following gets posted to the log.


External Process Runner - myJob - Error : java.lang.UnsatisfiedLinkError: no ocijdbc9 in java.library.path

Jul 15, 2008 2:30:03 PM

External Process Runner - myJob - Error : at java.lang.ClassLoader.loadLibrary(Unknown Source)

Jul 15, 2008 2:30:03 PM

External Process Runner - myJob - Error : at java.lang.Runtime.loadLibrary0(Unknown Source)

Jul 15, 2008 2:30:03 PM

External Process Runner - myJob - Error : at java.lang.System.loadLibrary(Unknown Source)

Jul 15, 2008 2:30:03 PM

External Process Runner - myJob - Error : at oracle.jdbc.oci8.OCIDBAccess.logon(OCIDBAccess.java:267)

Jul 15, 2008 2:30:03 PM

External Process Runner - myJob - Error : at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:371)

Jul 15, 2008 2:30:03 PM

External Process Runner - myJob - Error : at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:551)

Jul 15, 2008 2:30:03 PM

External Process Runner - myJob - Error : at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:351)

Jul 15, 2008 2:30:03 PM

External Process Runner - myJob - Error : at java.sql.DriverManager.getConnection(Unknown Source)

Jul 15, 2008 2:30:03 PM

External Process Runner - myJob - Error : at java.sql.DriverManager.getConnection(Unknown Source)

Jul 15, 2008 2:30:03 PM

External Process Runner - myJob - Error : at com.hitek.gui.mods.database.DBConnection.getConnection(DBConnection.java:52)

Jul 15, 2008 2:30:03 PM Database SQL - myJob - End Instance = 197 , Exit Code = 0 , Runtime (ms) = 984

Jul 15, 2008 2:30:03 PM

External Process Runner - myJob - Error : at com.hitek.engine.mods.database.DBSelectTask.connectToDBServer(DBSelectTask.java:139)

Jul 15, 2008 2:30:03 PM

External Process Runner - myJob - Error : at com.hitek.engine.mods.database.DBSelectTask.dbSelectTask(DBSelectTask.java:76)

Jul 15, 2008 2:30:03 PM

External Process Runner - myJob - Error : at com.hitek.engine.mods.database.DBSelectTask.run(DBSelectTask.java:45)

Jul 15, 2008 2:30:06 PM

[melvin]

have you included the odbc14.jar in both the main Automize7 directory as well as in Automize7\jre\lib\ext directory?


use the Utilities menu / commandline menu / Task Runner menu.

Select your task

Click on generate Code. working directory and commandline will be generated

create a new wincommand or command task using the above working directory and commandline.

in the commandline , make sure that odbc14.jar is in the class path as shown below .....
"C:\Program Files\Automize7\jre\bin\javaw.exe" -cp .;bin;help;activation.jar;bsh.jar;hitek7.jar;jakoro.jar;jh.jar;mail.jar;maverick-all.jar;net.jar;odbc14.jar com.hitek.engine.core.RunTask myJob

[altman80]

I did not have the ojdbc.jar in the main directory for automize. I put it there and restarted the engine. I then tried to run it again and had the same problem. I went to the task runner and this is what shows.

E:\Automize7\jre\bin\javaw.exe -cp .;bin;help;activation.jar;bsh.jar;hitek7.jar;jakoro.jar;jh.jar;mail.jar;maverick-all.jar;net.jar;ojdbc14.jar com.hitek.engine.core.RunTask TestTask

I copied that to the clipboard and tried to run it in the command prompt and windows gave a messagbox error

Messagebox Title:
"Java Virtual Machine Launcher"

Messagebox Description:
"Could not find the main class. Program will exit."

[Robert]

did you cd to E:\Automize7 first?

seems like the working directory is not set , hence it is not finding the classes.

[altman80]

I changed the directory in the command prompt and the task ran just fine. But it still won't run as an external process within the automize program.

[Robert]

The external process feature cannot dynamically append ojdbc14.jar to the class path and hence fails.

Use the working directory and commandline in a Command task. Then schedule the command task. This will run it as an external process (and with ojdbc.jar added to classpath).