topic Re: INserting data into another databse in Application Development and Automation Discussions

INserting data into another databse

Former Member — Fri, 31 Mar 2006 08:15:34 GMT

Hi All,

I have a requirement like i have to insert some data into another database from SAP. That database is outside of SAP.

If any one of you worked on that please send us the sample code or the process that to be followed.

With regards,

Kiran I

Re: INserting data into another databse

Former Member — Fri, 31 Mar 2006 08:20:14 GMT

Hi kiran,

1. we have to involve basis team also in that.

2. they will make one entry

in DBCON table

(that entry will point to secondary database/external database)

(suppose the name of this secondary connection is HRCON)

3. Also they will make connection string

in application server.

4. After that,

we should have

the table definition in SAP dd dictionary

and also

the same defintion

in the external database.

5. now in abap code,

we have to use like this.

DATA: mydbcon(30) TYPE c.

mydbcon = 'HRCON'.

INSERT ydev_msg_cur CONNECTION (mydbcon)

FROM msg_cur .

regards,

amit m.

Re: INserting data into another databse

Former Member — Fri, 31 Mar 2006 08:32:18 GMT

Hi Amit,

Thanks for quick reply.

Step-4 should the table name and field names should be same.

DATA: mydbcon(30) TYPE c.

mydbcon = 'HRCON'.

INSERT ydev_msg_cur CONNECTION (mydbcon)

FROM msg_cur .

what is 'ydev_msg_cur' and 'msg_cur' in this statement.

IS this statement is version concern.Iam using 4.6c can i use this in this version.

With Regards,

Kiran I

Re: INserting data into another databse

Former Member — Fri, 31 Mar 2006 08:35:54 GMT

Hi Kiran,

Could you pls explain this "another database"? Do you want to populate the values into an Oracle database or somthing like that.

Waiting for further clarification on the question.

Regards,

Anjali

Re: INserting data into another databse

Former Member — Fri, 31 Mar 2006 08:39:08 GMT

Hi Anjali,

I want to populate into an ORACLE database.

Regards,

Kiran i

Re: INserting data into another databse

Former Member — Fri, 31 Mar 2006 08:39:30 GMT

Hi again,

1. Step-4 should the table name and field names should be same.

Absolutely.

Everything should be same,

the table name,

field name,

field type,

field length.

what is 'ydev_msg_cur' and 'msg_cur' in this statement

INSERT ydev_msg_cur CONNECTION (mydbcon)

FROM msg_cur .

ydev_msg_cur = your database table

msg_cur = your internal table (with header line eg)

eg.

loop at msg_cur.

INSERT ydev_msg_cur CONNECTION (mydbcon)

FROM msg_cur .

endloop.

regards,

amit m.

Re: INserting data into another databse

Former Member — Fri, 31 Mar 2006 08:45:07 GMT

Hi,

Chek these link:

To Transfer Oracle Database Tables to SAP:

Hope they help you.

Regards,

Anjali

Re: INserting data into another databse

Former Member — Fri, 31 Mar 2006 11:17:51 GMT

Hi Amit,

I saw some of the code posted by you in another thread.

abcxyz.HEXCHN.LOCAL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = bdcpss2)(PORT = 1221))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = casltab)

)

this is the code that is there in that post.

Can I use this code for my issue.If yes,kindly send me sample code.

can u give me ur personal mailid.

Regards,

Kiran i

Re: INserting data into another databse

Former Member — Fri, 31 Mar 2006 11:30:14 GMT

Hi again,

1. ofcourse we cannot use this same code.

2. the reason is :

inside this code there are the following things

which differ from server to server.

a. server name

b. port number

c. database id name

3. Please contact your basis team,

only they can do this thing !

regards,

amit m.

Re: INserting data into another databse

Former Member — Mon, 03 Apr 2006 05:17:38 GMT

Hi Amit,

Iam unable to use the statement

"INSERT ydev_msg_cur CONNECTION (mydbcon)

FROM msg_cur".Iam using 4.6c version.can i use this statement in this version.If not what is the alternative method of using this.

Regards,

Kiran I

Re: INserting data into another databse

Former Member — Mon, 03 Apr 2006 05:42:31 GMT

Hi again,

INSERT ydev_msg_cur CONNECTION (mydbcon)

FROM msg_cur

I suppose you have not used the same statement !

Please change the table names:

ydev_msg_cur = your physical z/y table

msg_cur = your internal table (work area)

2. What is the exact error you are geting ?

regards,

amit m.

Re: INserting data into another databse

ferry_lianto — Mon, 03 Apr 2006 05:45:35 GMT

Hi Kiran,

You can use Native SQL to update other database (outside SAP). In order to do this, you need to have DB connection setup.


*Create new hardware ID

EXEC SQL.
  INSERT INTO ORACLE_HARDWAREDATA
         (HWID, CODE, SRS, AM, UCID, STOPLEVEL)
  VALUES  (:LWA_HWID, :LWA_CODE, :LWA_SRS, :LWA_AM, '0', '0')
  ENDEXEC

Also please check this links.

DB Connection

http://help.sap.com/saphelp_47x200/helpdata/en/c3/86bc425748654a8ffefa5c1a7adf50/frameset.htm

SAP OSS Note 178949

Native SQL

http://help.sap.com/saphelp_47x200/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/content.htm

Hope this will help.

Regards,

Ferry Lianto

Re: INserting data into another databse

Former Member — Mon, 03 Apr 2006 07:37:48 GMT

Hi,

I used the same thing.but iamgetting the following error

Field "CONNECTION" is unknown. It is neither in one of the specified

tables nor defined by a "DATA" statement.

Regards,

Kiran i

Re: INserting data into another databse

Former Member — Mon, 03 Apr 2006 12:04:55 GMT

Hi Amit,

Iam still getting the same error what i gave in my last post.

Regards,

Kiran i

Re: INserting data into another databse

Former Member — Mon, 03 Apr 2006 12:09:54 GMT

Hi,

Assuming that the entry in dbcon table is created successfully thru trxn DBCA...

Here is a sample code:

EXEC SQL.

CONNECT TO 'RAJ' AS 'V'

ENDEXEC.

EXEC SQL.

SET CONNECTION 'V'

ENDEXEC.

*- Get the data from MS-SQL Server

EXEC SQL.

open C1 for

select

loc_id,

loc_name,

loc_state

from ho_loc_mast

ENDEXEC.

DO.

EXEC SQL.

FETCH NEXT C1 into :wa-c_locid, :wa-c_locname, :wa-c_locstate

ENDEXEC.

IF sy-subrc = 0.

PERFORM loop_output.

ELSE.

EXIT.

ENDIF.

ENDDO.

EXEC SQL.

CLOSE C1

ENDEXEC.

ENDFUNCTION.

&----

*& Form LOOP_OUTPUT

&----

Output

----

FORM loop_output .

WRITE: /5 wa-c_locid, 10 wa-c_locname, 65 wa-c_locstate.

CLEAR wa.

ENDFORM. " LOOP_OUTPUT

Regards,

Raj

Re: INserting data into another databse

Former Member — Tue, 04 Apr 2006 04:50:38 GMT

Hi Rajasekhar,

How to create a connection 'RAJ' what u mentioned in your solution.can u send steps for that.

Also please send me the sample code for inserting records (large amount).

Regards,

Kiran I

Re: INserting data into another databse

Former Member — Tue, 04 Apr 2006 09:16:10 GMT

Hi All,

Can anyone help me out of this problem

Regards,

Kiran I

Re: INserting data into another databse

Former Member — Tue, 17 Jul 2007 19:19:32 GMT

Hello, this is the solution

After the create the connection (trx DBCO)

REPORT zprueba_oracle.

PARAMETERS dbs TYPE dbcon-con_name.

TYPES:

BEGIN OF type_sip,

usuario(10) TYPE c,

cod_cliente(5) TYPE c,

cod_producto(6) TYPE c,

unidades(7) TYPE c,

neto_total(9) TYPE c,

END OF type_sip.

DATA carrid_wa TYPE scarr-carrid.

DATA ta_sip TYPE type_sip.

DATA dbtype TYPE dbcon_dbms.

SELECT SINGLE dbms

FROM dbcon

INTO dbtype

WHERE con_name = dbs.

IF dbtype = 'ORA'.

TRY.

EXEC SQL.

connect to :dbs

ENDEXEC.

IF sy-subrc <> 0.

RAISE EXCEPTION TYPE cx_sy_native_sql_error.

ENDIF.

IF sy-subrc = 0.

EXEC SQL.

set connection :dbs

ENDEXEC.

ENDIF.

EXEC SQL.

insert into prueb_te values ('pepe',1,2,3,4)

ENDEXEC.

exec sql.

commit

endexec.

EXEC SQL.

SELECT *

FROM prueb_te INTO :ta_sip

ENDEXEC.

WRITE / ta_sip.

EXEC SQL.

DISCONNECT :dbs

ENDEXEC.

CATCH cx_sy_native_sql_error.

MESSAGE `Error in Native SQL.` TYPE 'I'.

ENDTRY.

ENDIF.

Bye