SEO

BACKGROUND
Our Java app runs in unchained mode against ASE 11.9.2 under Linux at all
times. All SProcs are set using sp_procxmode to "anymode". Our user
connections are handled via a connection pool.

The app runs in autocommit mode except when a non-select DML operation is to
be performed. Then we:
a) set autocommit off
b) begin a tran
c) send off the DML SQL
d) commit or rollback the tran as indicated by sqlcode
e) revert to autocommit on

We do this because the app is very much a DSS system, with heavy reporting
and light transactions; and we felt autocommit mode as the norm simplifies
transaction issues.

THE PROBLEM
We have 2 Procs that accept one integer input parm, perform some DML, and
output one output parm.
One SProc works fine using the a thru e transaction-technique mentioned
above.

The other does not. The app properly reads the Proc's output parm
(indicating successful execution), but the data does not change. The Proc
does work when executed via I-sql. And we can get the Proc to work from the
app if we run it in autocommit mode (not using technique a thru e above).

The Procs are very similar, except the one that does not work from our app
with autocommit = off does create a temporary table (Create Table
#temptbl...). But the database DOES have ddl in tran set to ON.

So somewhere is a combination of
- DDL on a temp table in a Proc,
- autocommit -vs- explicit transaction control,
- ddl in tran
- lord knows what else
that allows one Proc to run successfully, but not the other. And the one
that is not successful, still returns an output parameter that indicates it
was successful.

Any ideas??