topic Re: Update SQL table by ABAP CODE in Application Development and Automation Discussions

Update SQL table by ABAP CODE

Former Member — Tue, 13 Nov 2007 19:18:59 GMT

Hi,

We are ECC 6.0 and SAP is on Windows.

I want to update a SQl table from SAP.

I am trying this way..Can anybody help me with further steps?

I created Connection using DBCo tcode

and that created antries in DBCON table.

After this I am trying to code like this...

report x.

tables: marc.

data: begin of itab occurs 0,

matnr like marc-matnr,

werks like marc-werks,

end of itab.

select matnr werks from marc

into corresponding fields of table itab

where werks = '1000'.

EXEC SQL.

CONNECT TO 'TEST_SAP_MATERIAL' AS 'V'

ENDEXEC.

EXEC SQL.

SET CONNECTION 'V'

ENDEXEC.

loop at itab.

EXEC SQL.

WHAT SHOULD I CODE HERE???

MY SQL TABLE NAME IS TESTSQL and fields are MATNR & WERKS .

ENDEXEC.

endloop.

Regards

Praveen

null

Message was edited by:

PRAVEEN s

Re: Update SQL table by ABAP CODE

ferry_lianto — Tue, 13 Nov 2007 19:36:55 GMT

Hi,

Please try something like this.


LOOP AT ITAB.
  EXEC SQL.
    INSERT INTO TESTSQL
          (MATNR, WERKS)
           VALUES
          (:ITAB-MATNR, :ITAB-WERKS)
  ENDEXEC.
ENDLOOP.

Regards,

Ferry Lianto

Re: Update SQL table by ABAP CODE

Former Member — Tue, 13 Nov 2007 19:41:29 GMT

ferry,

I am getting a dump.

Runtime Errors DBIF_DSQL2_INTERNAL_ERROR

Except. CX_SY_NATIVE_SQL_ERROR

Date and Time 11/13/2007 13:38:15

by the way is there any other way we can test the connection to the server which we have established by DBco t-code?

Regards

Praveen

Re: Update SQL table by ABAP CODE

ferry_lianto — Tue, 13 Nov 2007 19:56:10 GMT

Hi,

I assumed your DBCON connection and authorization are setup properly.

Otherwise, please work with BASIS folks.

You can try to read material from table TESTSQL into ABAP internal table.


DATA: BEGIN OF IT_MATNR OCCURS 0,
        MATNR LIKE MARA-MATNR.
DATA: END OF IT_MATNR.

DATA: WA_MATNR LIKE MARA-MATNR,
      WA_WERKS LIKE MARC-WERKS. 

WA_WERKS = P_WERKS.

EXEC SQL PERFORMING GET_OUTPUT.
  SELECT MATNR
  INTO :WA_MATNR
  FROM TESTSQL
  WHERE WERKS = :WA_WERKS
ENDEXEC.

FORM GET_OUTPUT.
  MOVE WA_MATNR TO IT_MATNR.
  APPEND IT_MATNR.
ENDFORM.

Regards,

Ferry Lianto

Re: Update SQL table by ABAP CODE

Former Member — Tue, 13 Nov 2007 20:38:41 GMT

Ferry,

I guess i am having problem with DBCo itself.

I inseretd following way..TESTTABLE is my table where I want to populate the data.

When I tried to Read data from SQL I am getting following DUMp.

Short text

An SQL error occurred when executing Native SQL.

What happened?

The error 170 occurred in the current database connection "TEST".

What can you do?

Note down which actions and inputs caused the error.

To process the problem further, contact you SAP system

administrator.

Using Transaction ST22 for ABAP Dump Analysis, you can look

at and manage termination messages, and you can also

keep them for a long time.

How to correct the error

Database error text........: "Line 1: Incorrect syntax near '.'."

Database error code........: 170

Triggering SQL statement...: "SELECT MATNR FROM SapMaterial WHERE

Internal call code.........: "[DBDS/NEW DSQL]"

Please check the entries in the system log (Transaction SM21).

If the error occures in a non-modified SAP program, you may be abl

find an interim solution in an SAP Note.

If you have access to SAP Notes, carry out a search with the follo

keywords:

"DBIF_DSQL2_SQL_ERROR" "CX_SY_NATIVE_SQL_ERROR"

"ZTEST1" or "ZTEST1"

AND When I tried to INSERT

Short text

An SQL error occurred when executing Native SQL.

What happened?

The error 128 occurred in the current database connection "TEST".

What can you do?

Note down which actions and inputs caused the error.

To process the problem further, contact you SAP system

administrator.

Using Transaction ST22 for ABAP Dump Analysis, you can look

at and manage termination messages, and you can also

keep them for a long time.

How to correct the error

Database error text........: "The name 'ITAB' is not permitted in this context.

Only constants, expressions, or variables allowed here. Column names are not

permitted."

Database error code........: 128

Triggering SQL statement...: "INSERT INTO SapMaterial ( MATNR, WERKS ) VALUES (

ITAB-MATNR, ITAB-WERKS )"

Internal call code.........: "[DBDS/NEW DSQL]"

Please check the entries in the system log (Transaction SM21).

If the error occures in a non-modified SAP program, you may be able to

find an interim solution in an SAP Note.

If you have access to SAP Notes, carry out a search with the following

keywords:

Regards

Praveen

DB Connection : TESTSQL

DBMS: MSS

Username:TESTUSER

DB Pass word : PASSWORD/PASSWORD

Conn Info :MSSQL_SERVER=TESTPJD01 MSSQL_DBNAME=TESTDB OBJECT_SOURCE=TESTTABLE

Re: Update SQL table by ABAP CODE

ferry_lianto — Tue, 13 Nov 2007 20:44:02 GMT

Hi,

I would suggest to work with BASIS folk to resolve DB connection issue.

Once the connection issue resolved, you can try above sample codes for testing purposes.

Regards,

Ferry Lianto

Re: Update SQL table by ABAP CODE

Former Member — Tue, 13 Nov 2007 20:51:05 GMT

Ferry,

Connection worked.

With this code I am able insert records in the table but it only works with VALUES

report x.

data: begin of itab occurs 0,

matnr like marc-matnr,

werks like marc-werks,

end of itab.

select matnr werks from marc

into corresponding fields of table itab

where werks = '0105'.

EXEC SQL.

CONNECT TO 'TEST' AS 'V'

ENDEXEC.

EXEC SQL.

SET CONNECTION 'V'

ENDEXEC.

loop at itab.

EXEC SQL.

INSERT INTO SapMaterial

( MATNR , WERKS )

VALUES

('1111' , '0105' )

ENDEXEC.

endloop.

how Do I replace them with VARAIBLES??? ITAB-MATNR and ITAB-WERKS????

Rgds

Praveen

Re: Update SQL table by ABAP CODE

ferry_lianto — Tue, 13 Nov 2007 20:57:03 GMT

Hi,

Please try this.


...

EXEC SQL.
  CONNECT TO 'TEST' AS 'V'
ENDEXEC.

EXEC SQL.
  SET CONNECTION 'V'
ENDEXEC.

LOOP AT ITAB.
  EXEC SQL.
    INSERT INTO SapMaterial
           (MATNR, WERKS)
           VALUES
           (:ITAB-MATNR, :ITAB-WERKS)
  ENDEXEC.
ENDLOOP.

Regards,

Ferry Lianto

Re: Update SQL table by ABAP CODE

Former Member — Tue, 13 Nov 2007 21:20:12 GMT

Thank you ferry It worked!!!!!

I am awarding you full points.

Rgds

praveen