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

asked 07 Nov '11, 10:56

HBrener's gravatar image

HBrener
426212333
accept rate: 0%

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

(07 Nov '11, 11:00) Mark Culp

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.

(07 Nov '11, 11:06) HBrener
Replies hidden

Is there a commit/rollback in the Watcom procedure?

(07 Nov '11, 11:22) Elmi Eflov

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

(07 Nov '11, 11:26) HBrener
Replies hidden

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

(07 Nov '11, 11:58) Elmi Eflov

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

permanent link

answered 07 Nov '11, 13:03

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

edited 07 Nov '11, 13:10

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

(07 Nov '11, 14:17) HBrener

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

permanent link

answered 07 Nov '11, 11:48

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k568104
accept rate: 43%

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

(07 Nov '11, 11:53) HBrener
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×35

question asked: 07 Nov '11, 10:56

question was seen: 1,852 times

last updated: 07 Nov '11, 14:17