topic Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement in Application Development and Automation Discussions

Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

Former Member — Tue, 05 May 2015 02:28:42 GMT

In production system, sometimes it dumps with this error:


Runtime Errors         DBIF_RSQL_INVALID_RSQL 
Exception              CX_SY_OPEN_SQL_DB

And here is the code cause this problems:


CALL FUNCTION 'ENQUEUE_E_TABLE'
  EXPORTING 
  mode_rstable = 'E'
  tabname     = 'ZVKN_TRPHIST'.


MODIFY zvkn_trsphist FROM TABLE lt_trsphist. <<<<<< Error


CALL FUNCTION 'DEQUEUE_E_TABLE'
  EXPORTING
  mode_rstable = 'E'
  tabname = 'ZVKN_TRPHIST'.


IF sy-subrc EQ 0.
  return-type = 'S'.
ELSE.
  ROLLBACK WORK.
  return-type = 'E'.
ENDIF.

I have tried to use ENQUEUE statement with WAIT but no succeed. How to manage with no dump?

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

nishantbansal91 — Tue, 05 May 2015 05:43:46 GMT

Dear I Namie,

This type of dump occurs if your column contain unexpected value than data element.

Suppose your COL-A type Int and it contain Character. That type this dump will occur.

Please check your code i think in some cases might be wrong data pass to your program.

Thanks

Nishant

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

Former Member — Tue, 05 May 2015 05:55:42 GMT

Hi,

please try with COMMIT WORK AND WAIT when SY-SUBRC = 0.

Otherwise there might be an issue, if you are processing those code lines a second time.

Regards,

Klaus

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

Former Member — Tue, 05 May 2015 08:44:54 GMT

Hi, thanks for your reply.

My table has 80 fields and I accessed through Remote Desktop so that I have not checked all the field if there is a type conflict during processing.

I have viewed the data element of this table and notice there are Char, Numc, and Quan Element.

Can these data type occur dump?

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

Former Member — Tue, 05 May 2015 08:49:29 GMT

Hi, thanks for your reply.

Did you mean that adding COMMIT WORK AND WAIT when ENQUEUE is success and before modify statement?

I not get it clearly. I use the code below. Is this ok to transport to Production?

CALL FUNCTION 'ENQUEUE_E_TABLE'

EXPORTING

MODE_RSTABLE = 'E'

TABNAME = 'ZVKN_TRPHIST'

* VARKEY =

* X_TABNAME = ' '

* X_VARKEY = ' '

* _SCOPE = '2'

_WAIT = 'X'

* _COLLECT = ' '

EXCEPTIONS

FOREIGN_LOCK = 1

SYSTEM_FAILURE = 2

OTHERS = 3

IF SY-SUBRC <> 0.

return-type = 'E'.

return-number = sy-msgno.

return-id = sy-msgid.

CALL FUNCTION 'MESSAGE_TEXT_BUILD'

EXPORTING

msgid = sy-msgid

msgnr = sy-msgno

msgv1 = sy-msgv1

msgv2 = sy-msgv2

msgv3 = sy-msgv3

msgv4 = sy-msgv4

IMPORTING

message_text_output = return-message.

ROLLBACK work.

RETURN.

ELSE.

COMMIT WORK AND WAIT.

MODIFY zvkn_trsphist FROM TABLE lt_trsphist.

CALL FUNCTION 'DEQUEUE_E_TABLE'

EXPORTING

mode_rstable = 'E'

tabname = 'ZVKN_TRPHIST'.

RETURN-type = 'S'.

ENDIF.

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

nishantbansal91 — Tue, 05 May 2015 09:01:23 GMT

Dear I Namie,

I think before transporting request to PRD you have to first analysis the issue what is the root cause for that issue. Pass the wrong value in DEV system in Debugging mode than you will come to know.

Thanks

Nishant

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

Former Member — Tue, 05 May 2015 09:02:56 GMT

Hi,

I would prefer the COMMIT WORK AND WAIT after the MODIFY. So the system lock table entries will be released, too.

Regards,

Klaus

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

Former Member — Tue, 05 May 2015 09:11:13 GMT

Hi Nishant,

I can not procedure to demostrate the error in Dev System. That's why I have to try and transport to PRD to see the result

BR,

Nam

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

Former Member — Tue, 05 May 2015 09:16:20 GMT

This is the error log in ST22.

it error in line MODIFY, I dont know whether COMMIT WORK after MODIFY is execute or not?

And I also use DEQUEUE_E_TABLE. What is difference between COMMIT WORK and DEQUEUE_E_TABLE here? I think DEQUEUE_E_TABLE also release the lock tables.

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

Former Member — Tue, 05 May 2015 10:00:18 GMT

Hi,

the COMMIT is commented out and without a WAIT.

Please use COMMIT WORK AND WAIT immediately after the MODIFY command. Maybe the DEQUEUE is no longer possible or needed then.

Regards,

Klaus

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

former_member210963 — Wed, 06 May 2015 14:22:41 GMT

Hi Narnie,

When your using modify statement use the transporting key word to pass exact values to modify.

Thanks,

Sivanadh

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

Former Member — Tue, 30 Jun 2015 07:49:54 GMT

Hi Sivanadh,

Thanks for your reply. In my situation, this table has many fields and I can not manage which field will be transported or not at this point. This is difficult.

I suppose problem is not caused by data type conflict.

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

Former Member — Tue, 30 Jun 2015 08:04:31 GMT

Hi,

Sorry for the lately reply. More than a month with this problem, I think this problem is gone and I can mark the answer here. But yesterday I got this log in SM21. I can not reprocedure this problem again

And still around the code above.

The first thing I want to clarify here is

What is the problem "Database error with SEL access to table"?

When and Why does it happen? (I didn't find any document relate to this keywords)

And How can I determine the root cause in this situation?

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

Former Member — Fri, 03 Jul 2015 02:15:40 GMT

I use this code but have no luck

MODIFY zvkn_trsphist FROM TABLE lt_trsphist.

IF sy-subrc EQ 0.

commit work and wait.

return-type = 'S'.

ELSE.

ROLLBACK work.

return-type = 'E'.

ENDIF.

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

Former Member — Fri, 03 Jul 2015 04:39:42 GMT

Hi,

please check your internal table lt_trsphist. Check that all key fields are filled in all lines and there are no lines with duplicate key fields. If you have many entries you can sort it before searching duplicate keys in debugger.

Regards,

Klaus

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

RaymondGiuseppi — Fri, 03 Jul 2015 05:25:44 GMT

First could you wrap the MODIFY statement in a TRY/CATCH /ENDTRY.


TRY.
  MODIFY zvkn_trsphist FROM TABLE lt_trsphist. <<<<<< error
      catch cx_sy_open_sql_db into oref.
  text300 = oref->get_text( ).
  MESSAGE text300 TYPE 'S' DISPLAY LIKE 'E'.
  sy-subrc = 12.
ENDTRY.

So you could get immediately a clear error message.

Then on which database are you working, on Oracle, this is error ORA-00060 some deadlock during update, insure there are no duplicates in your internal table comparing primary keys only, if this is not the case also insure there are no other reports updating this table without taking care to lock table.

Hints:

If duplicates a better coding in previous code or a SORT/DELETE ADJACENT DUPLICATES COMPARING statements could solve it
If this is an actual lock problem, either have the other developper adapts his program, or try to wrap your update now monitored in a DO 10 TIMES/ENDDO exiting the loop in case of succes and raising error at end if sy-subrc NE 0.

Regards,

Raymond

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

Former Member — Fri, 03 Jul 2015 06:01:08 GMT

Hi, I have tried to implement this approach in old version. This error disappeared in ST22.

Recently, in SM21, there is a deadlock error related to this table. When I change this program not to catch exception like this, I notice that in SM21 a shortdump appear with deadlock error. I think when using Try Catch Exception, we just hide this error from user or shortdump to not be stored or logged in ST22. By the way, this error is still there and logged by SM21 (Database error with SEL access error). (this system use MaxDB)

Can you please explain for me, where this error begin, before or after MODIFY? I think of this situation:

1> when I locked this object using ENQUEUE but this FM was timeout, then the object was locked by others. That's why MODIFY statement caused error.

2> This ENQUEUE statement worked well, but after that the lock was not released. And after sometimes, after some job, MODIFY statement caught an lock object and caused error (Because this error appears unpredictable, discrete time)

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

Former Member — Fri, 03 Jul 2015 06:03:33 GMT

Hi,

I will try to do this. But I want to clarify somethings. As I understand, if the lt_trsphist has duplicated key fields item, I will get error everytime I run this FM. But this FM caused error in discrete time, I don't find the logic of its appearance.

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

Former Member — Fri, 03 Jul 2015 06:43:08 GMT

Hi,

lt_trsphist is named like a local table in a FORM.

If it is globally defined, there might be data remaining from the last call of the fm. But also if you cann another fm to create lrows of lt_trsphist, there might a data refresh issue in this called fm, so you will get data twice.

Or you have a database select without a needed DISTINCT option. There are many reasons possible, It depends on your creation logic of the lt_trsphist rows.

Regards,

Klaus

Re: Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

RaymondGiuseppi — Fri, 03 Jul 2015 09:34:41 GMT

Error is raised in the database server not the application server during execution of the generated SQL INSERT/UPDATE statements.

Catching the error, just allows to retry later, the database fails and records the error, but your program is not interrupted and is given a new chance to success later.

Hint: Enqueue at table level is only suitable for some Customizing table (e.g. SM30 managed) but is not suitable for master data or application data, for those, a lock by keys is required. Build a lock object with SE11 on this table/primary keys and lock only required records IN EVERY PROGRAM with the generated Enqueue FM.

Lock are released by DEQUEUE FM and COMMIT/ROLLBACK WORK.

Regards,

Raymond