Invoking Sybase stored procedure via adapter service

1

Hi folks,

I have a stored procedure which its transaction mode is set to ‘Unchained’ upon creation.

However, I can not execute this stored procedure via adapter service. Below is the error thrown by Sybase:-

[ADA.1.316] Cannot execute the SQL statement "exec po_vas_search_member ?, ?, ?, ?, ?, ?, ?
". "
(ZZZZZ/7713) Stored procedure ‘po_vas_search_member’ may be run only in unchained transaction mode. The ‘SET CHAINED OFF’ command will cause the current session to use unchained transaction mode.

I also tried to execute ‘SET CHAINED OFF’ in custom SQL template prior to invoking the stored procedure. However, I get error below:-

[ADA.1.316] Cannot execute the SQL statement “SET CHAINED OFF”. "
(ZZZZZ/226) SET CHAINED command not allowed within multi-statement transaction.

I am not suppose to change transaction mode of this stored procedure to “any mode”. May I know if there is any other way to invoke this service via adapter service?

Thanks in advance :smiley:

2

Hi Ronnie,

can you try to set the unchained mode in the jdbc connection in the field otherProperties?

Using a separate AdapterService might not be helpful as it is not guaranteed that the two adapter services will hit the same connection from the pool.

Regards,
Holger

3

Good day Holder,

May I know where I can find JDBC connection’s otherProperties field?

Regards,
Ronnie She

4

I tried to set to unchained mode prior to executing my stored procedure. Here is how i do it:-

“if @@tranchained <> 0
BEGIN
SET CHAINED OFF
END
exec po_vas_search_member ?, ?, ?, ?, ?, ?, ?”

Sybase then returned following error in the first attempt:-

[ADA.1.316] Cannot execute the SQL statement “SET CHAINED OFF”. "
(ZZZZZ/226) SET CHAINED command not allowed within multi-statement transaction.

However, when I executed this fragment of code again, it worked fine and retuned expected result. Out of curiosity, I then executed this fragment of code again after 10 minutes. Guess what, Sybase returned the same error. Then I executed the service continuously after that, it worked fine. if I let it sat for another 10 minutes, the same error would pop up.

I am not very why it behaves like this. Any clue?

Regards,
Ronnie She

5

Hi Ronnie,

please provide your JDBC Adapter version with list of Fixes applied.

The Properties field is on the connection configuration screen in the JDBC Adapter UI reachable via IS Admin UI under Adapters → JDBC Adapter.

Regards,
Holger

6

Hi Holger,

I am using jCONNECT 6.05 type 4 for Sybase.

Finally I can find the “Other Properties” field. May I know what should I input here so that the transaction can become UNCHAINED?

Regards,
Ronnie She

7

Hi, appreciate if anyone can enlighten my issue :wink:

Regards,
Ronnie She

8

Hi all, I had just found a solution to this problem.

This is what I did :-

if @@tranchained <> 0
BEGIN
COMMIT
SET CHAINED OFF
END
exec po_get_agent_info ?

Hope it helps anyone who encounters the same issue.

Regards,
Ronnie She Chien Wei