Sorry if there is an answer already but i've searched for the solution half a day and hasn't found any.
I want to export the data from SAP HR system to MSSQL database using ADODB. So I have a form for opening the connection and for running sql statements like select and insert. Everything works great when I run this program in SAP GUI via SA38. It makes all the changes it should do like:
- running select statement and returning the results
- delete records from destination table
- insert records into destination table
The problem is when I try to run it in background which in fact is what I need since this application should provide the data to MSSQL on regular dayly basis.
It just does not make any changes on the destination database. Strange thing is that all of the statements like
CREATE OBJECT or CALL METHOD return with subrc = 0. But for instance the select statement which should return the results does not change the initial value of statement. So if you look at the code below, the statement
"select count(*) from addSAPHRIMPORT" normally returns the number and the pipe but when it runs in background the value doesn't change so after running this command I still have this select value in sql variable.
Therefore I cannot find where the problem is. The program runs without errors but has no effect on destination database.
Below is the code I use (many writes were added only for testing reasons so do not be scared 😞
*opening connection to MSSQL
FORM openconnection.
sql = 'Provider=SQLOLEDB.1;Persist Security Info=False;User ID=xxxxxx;Password=yyyyyy;Initial Catalog=schema_name;Data Source=db_server_dns_name'.
IF con-header IS INITIAL OR con-handle = -1.
CREATE OBJECT con 'ADODB.Connection'.
write : / 'The result: ', sy-subrc.
IF NOT sy-subrc = 0.
EXIT.
ENDIF.
CREATE OBJECT rec 'ADODB.Recordset'.
write : / 'The result : ', sy-subrc.
IF NOT sy-subrc = 0.
EXIT.
ENDIF.
ENDIF.
CALL METHOD OF con 'Open'
EXPORTING
#1 = sql.
if sy-subrc <> 0.
write: / 'Connection error', sy-subrc.
else.
write : / sql.
endif.
ENDFORM. "openconnection
FORM get_rec.
sql = 'select count(*) from addSAPHRIMPORT;'.
CALL METHOD OF rec 'OPEN'
EXPORTING
#1 = sql
#2 = con
#3 = '1'.
IF sy-subrc = 0.
WRITE: / 'OK'.
ELSE.
WRITE: / 'Error: ', sy-subrc.
EXIT.
ENDIF.
CALL METHOD OF rec 'getstring' = sql
EXPORTING
#1 = '2'
#2 = 1
#3 = '|'
#4 = '|'.
IF sy-subrc = 0.
REFRESH spl. CLEAR spl.
WRITE: / sql.
SPLIT sql AT '|' INTO noOfRecs xxx.
WRITE: / 'Number of records: ', noOfRecs.
ELSE.
WRITE: / 'Błąd: ', sy-subrc.
noOfRecs = 0.
EXIT.
ENDIF.
CALL METHOD of rec 'Close'.
sql = 'delete from [addSAPHRIMPORT]; '.
CALL METHOD OF rec 'Open'
EXPORTING
#1 = sql
#2 = con
#3 = '1'.
IF sy-subrc = 0.
WRITE : / 'Records deleted'.
ELSE.
WRITE : / 'ERROR'.
EXIT.
ENDIF.
CALL METHOD of rec 'Close'.
counter = 0.
refresh spl.
clear spl.
*
*
LOOP AT dbComm.
write : / dbComm-sqlStr.
CALL METHOD OF rec 'OPEN'
EXPORTING
#1 = dbComm-sqlStr
#2 = con
#3 = '1'.
IF sy-subrc = 0.
add 1 to counter.
ELSE.
write : 'ERROR: '.
ENDIF.
CALL METHOD of rec 'Close'.
ENDLOOP.
write : / 'Inserted ', counter, ' records'.
PERFORM close_connection.
ENDFORM. "Get_Rec
FORM close_connection.
FREE rec.
FREE con.
ENDFORM. "closeconnection
Edited by: Pawel Knapik on Jun 10, 2008 2:00 PM
Bluesky
