Error SQLCODE -267 from stored procedure

[former_SQLA_member1694890]

We are used SA 12.0.1(EBF 3324(Win)/3311(Linux)). Stored procedure(SP) in T-SQL prepared data in temp table and return result set as select from this temp table. When preparing data used select from another SP written in WATCOM-SQL. Something like - INSERT INTO #TEMP_MOBILE_SP_TOP SELECT A,B,C FROM SP_WATCOM_PR(X,Y),CONV_TABLE Calling this SP from interactive SQL(dbisql) everything is working properly and we get required result. But when this SP is called from Java server code(based on JBOSS) in database log appears error SQLCODE = -267 COMMIT/ROLLBACK not allowed within atomic operation (error print from SP), in Java server log appears error SQL Anywhere Error -260: Variable '@result' not found. Any idea what could be reason for this difference from DBISQL it is OK, from Java code using JDBC not!? Thanks in advance, Hanan Brener

[MarkCulp]

Where in your code is the variable "@result" being referenced? Find that code and you have likely found your problem.

[former_SQLA_member1694890]

Problem is with error -267; The same SP when used without using seelect with WATCOM-SQL is working properly. Variable @result is declared as int and it not make problem.

[Former Member]

Is there a commit/rollback in the Watcom procedure?

[former_SQLA_member1694890]

No, any commit/rollback in Watcom SP. May be 'Autocommit' make problem !?

[Former Member]

No, the problem is likely caused by the variable not found error. I will take a look.

[jeff_albion]

By default, TDS (jConnect) connections have the "chained" option turned off, so a DML operation in the procedure will perform an autocommit. (This is turned on via the 'sp_tsql_environment' stored procedure).

The fix is to call: conn.setAutoCommit(false) prior to executing the CallableStatement and then turn it back on later. Better yet, it would be best to call conn.setAutoCommit(false) immediately after connecting and explicitly issuing commits as needed.

See: http://search.sybase.com/kbx/solvedcases?id_number=11435344 for more information about this situation.

(Aside: Interactive SQL uses the SQL Anywhere JDBC ("Type 2") driver, which is a Java/native hybrid, rather than the jConnect ("Type 4") driver which is just a pure Java driver - these are not the same JDBC drivers).

[former_SQLA_member1694890]

Thanks for answer, can we do this (AUTOCOMMIT false) inside stored procedure !?

[Former Member]

Which JDBC driver are you using from your JBOSS Java application?

[former_SQLA_member1694890]

We are using jdbc3 (com.sybase.jdbc3.jdbc.SybDataSource ) driver