Question Re: Error SQLCODE -267 from stored procedure in Technology Q&A

Error SQLCODE -267 from stored procedure

former_SQLA_member1694890 — Mon, 07 Nov 2011 10:56:14 GMT

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

Re: Error SQLCODE -267 from stored procedure

MarkCulp — Mon, 07 Nov 2011 11:00:41 GMT

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

Re: Error SQLCODE -267 from stored procedure

former_SQLA_member1694890 — Mon, 07 Nov 2011 11:06:51 GMT

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.

Re: Error SQLCODE -267 from stored procedure

Former Member — Mon, 07 Nov 2011 11:22:53 GMT

Is there a commit/rollback in the Watcom procedure?

Re: Error SQLCODE -267 from stored procedure

former_SQLA_member1694890 — Mon, 07 Nov 2011 11:26:01 GMT

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

Re: Error SQLCODE -267 from stored procedure

Former Member — Mon, 07 Nov 2011 11:48:03 GMT

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

Re: Error SQLCODE -267 from stored procedure

former_SQLA_member1694890 — Mon, 07 Nov 2011 11:53:24 GMT

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

Re: Error SQLCODE -267 from stored procedure

Former Member — Mon, 07 Nov 2011 11:58:54 GMT

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

Re: Error SQLCODE -267 from stored procedure

jeff_albion — Mon, 07 Nov 2011 13:03:17 GMT

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).

Re: Error SQLCODE -267 from stored procedure

former_SQLA_member1694890 — Mon, 07 Nov 2011 14:17:30 GMT

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