Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Update SQL table by ABAP CODE

Former Member
0 Kudos

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

1 ACCEPTED SOLUTION

ferry_lianto
Active Contributor

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

8 REPLIES 8

ferry_lianto
Active Contributor
0 Kudos

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

0 Kudos

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

ferry_lianto
Active Contributor
0 Kudos

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

0 Kudos

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

ferry_lianto
Active Contributor
0 Kudos

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

0 Kudos

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

ferry_lianto
Active Contributor

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

0 Kudos

Thank you ferry It worked!!!!!

I am awarding you full points.

Rgds

praveen