topic Re: INSERT doesn't work with EXEC SQL in Application Development and Automation Discussions

INSERT doesn't work with EXEC SQL

Former Member — Sat, 07 Jan 2012 19:32:08 GMT

Hello Experts,

Please help me.

I have a program where I try insert data in a external database ( Oracle ), the program run normally without erros, but the data is not commited in oracle.

Is it possible de connection have a property to set some type of authorization to INSERT and UPDATE ??

see below the code:

EXEC SQL.

CONNECT TO :CONNECTION

ENDEXEC.

if sy-subrc = 0.

EXEC SQL.

SET CONNECTION :CONNECTION

ENDEXEC.

EXEC SQL.

INSERT INTO AGRICOLA.TESTE(DATA, TESTE) VALUES(SYSDATE, :sy-uname)

ENDEXEC.

EXEC SQL.

COMMIT

ENDEXEC.

EXEC SQL.

SET CONNECTION DEFAULT

ENDEXEC.

EXEC SQL.

DISCONNECT :CONNECTION

ENDEXEC.

endif.

Re: INSERT doesn't work with EXEC SQL

surajarafath — Sun, 08 Jan 2012 02:30:54 GMT

Hi ,

I think this problem might be due to the Data types,

Instead of sending system variable directly itself, you try to make it as char then try..

Re: INSERT doesn't work with EXEC SQL

Former Member — Mon, 09 Jan 2012 13:38:05 GMT

Thanks for your time my friend, but still doesn't work

I change the command for "UPDATE", but there isn't success

EXEC SQL.

UPDATE AGRICOLA.TESTE SET TESTE = 'W' WHERE TESTE = 'A'

ENDEXEC.

Re: INSERT doesn't work with EXEC SQL

RaymondGiuseppi — Mon, 09 Jan 2012 13:50:27 GMT

Try to add error management in your code like following sample :

DATA: lo_ref TYPE REF TO cx_sy_native_sql_error,
      lv_text TYPE string.
* ...
TRY.
    " ...
    EXEC SQL.
      INSERT INTO AGRICOLA.TESTE(DATA, TESTE) VALUES(SYSDATE, :sy-uname)
    ENDEXEC.
    " ...
  CATCH cx_sy_native_sql_error INTO lo_ref.
    lv_text = lo_ref->get_text( ).
    MESSAGE lv_text TYPE 'I'. " or 'E'.
ENDTRY.

Regards,

Raymond

Re: INSERT doesn't work with EXEC SQL

Former Member — Mon, 09 Jan 2012 14:36:52 GMT

check your return code after each call, not just the first one. And put out a different message for each.

Rob

Edited by: Rob Burbank on Jan 9, 2012 9:37 AM