SAP对接外部数据库

SAP对接外部数据库

---

前言

SAP与外部数据库连接.相关TCODE:DBCO

---

一、准备工作

1.SAP需要安装相关的驱动，需要联系basis去安装，并且配置相关DBCO的参数。具体配置请百度

二、参考代码

&---------------------------------------------------------------------
*& Report ZHRPT005
&---------------------------------------------------------------------
*&
&---------------------------------------------------------------------
REPORT ZHRPT005.
TABLES:EKKO,DBCON,PA0003.
DATA:BEGIN OF GS_DISP,
ID TYPE INT8,
USERID TYPE INT8,
UPDATEDATE TYPE CHAR10,
USERNUMBER TYPE CHAR30,
CHECKTIME TYPE CHAR30,
END OF GS_DISP.
DATA:GT_DISP LIKE TABLE OF GS_DISP.
FIELD-SYMBOLS: <GT_HEAD> TYPE STANDARD TABLE.
DATA: LS_ORA_DATA,
EXC_REF TYPE REF TO CX_SY_NATIVE_SQL_ERROR,
ERROR_TEXT TYPE STRING,
LV_ERROR_MSG TYPE STRING.
DATA:LO_EXCEPTION TYPE REF TO CX_SY_NATIVE_SQL_ERROR.
DATA:GS_NUM TYPE VTBBEWE-ATAGE.
DATA:BEGIN OF GS_PA0003,
PERNR TYPE PA0003-PERNR,
END OF GS_PA0003.
DATA:GT_PA0003 LIKE TABLE OF GS_PA0003.
RANGES R_P0003 FOR RANGE_C8-LOW.
*DATA:s_p03 like LINE OF r_p0003.
CONSTANTS P_CONNR TYPE DBCON-CON_NAME VALUE ‘SQLSERVER’.
-----------------------------------------------------------------------
*选择屏幕定义
-----------------------------------------------------------------------
SELECTION-SCREEN:BEGIN OF BLOCK B1 WITH FRAME TITLE A1.
SELECT-OPTIONS:
S_AEDAT FOR EKKO-AEDAT,
S_PERNR FOR PA0003-PERNR.
SELECTION-SCREEN END OF BLOCK B1.

&---------------------------------------------------------------------
*屏幕初始化
&---------------------------------------------------------------------
INITIALIZATION.

A1 = ‘获取考勤机和手机云之家打卡数据’.

---

• 屏幕检查

---

AT SELECTION-SCREEN.

READ TABLE S_AEDAT INDEX 1.
IF SY-SUBRC = 0.
IF S_AEDAT-HIGH IS NOT INITIAL.
CALL FUNCTION ‘FIMA_DAYS_AND_MONTHS_AND_YEARS’
EXPORTING
I_DATE_FROM = S_AEDAT-LOW
" I_KEY_DAY_FROM =
I_DATE_TO = S_AEDAT-HIGH
" I_KEY_DAY_TO =
" I_FLG_SEPARATE = ’ ’
" I_FLG_ROUND_UP = ‘X’
IMPORTING
E_DAYS = GS_NUM
" E_MONTHS =
" E_YEARS =
.
IF SY-SUBRC <> 0.

• Implement suitable error handling here
  ENDIF.
  IF GS_NUM > ‘31’.
  MESSAGE E398(00) WITH ‘日期间隔不能超过31天’.
  ENDIF.
  ENDIF.
  ENDIF.

AT SELECTION-SCREEN OUTPUT.
S_AEDAT-LOW = SY-DATUM - 1.
S_AEDAT-HIGH = SY-DATUM - 1.
APPEND S_AEDAT.

&---------------------------------------------------------------------
*取数
&---------------------------------------------------------------------
START-OF-SELECTION.

SELECT
PERNR
INTO TABLE GT_PA0003
FROM PA0003
WHERE PERNR IN S_PERNR
AND BEGDA <= SY-DATUM
AND ENDDA >= SY-DATUM.

LOOP AT GT_PA0003 INTO GS_PA0003.
R_P0003-SIGN = ‘I’.
R_P0003-OPTION = ‘EQ’.
R_P0003-LOW = GS_PA0003-PERNR+2.
APPEND R_P0003.
ENDLOOP.

PERFORM FRM_GET_SQLSEVER.

IF GT_DISP IS NOT INITIAL.
PERFORM FRM_MOVE_DISP.
ENDIF.

END-OF-SELECTION.
&---------------------------------------------------------------------
*& Form FRM_GET_SQLSEVER
&---------------------------------------------------------------------
*& text
&---------------------------------------------------------------------
*& --> p1 text
*& <-- p2 text
&---------------------------------------------------------------------
FORM FRM_GET_SQLSEVER .
DATA: B_DATE TYPE CHAR8,
D_DATE TYPE CHAR8.
READ TABLE S_AEDAT INDEX 1.
IF SY-SUBRC <> 0.
S_AEDAT-LOW = SY-DATUM - 1.
S_AEDAT-HIGH = SY-DATUM - 1.
ENDIF.

IF S_AEDAT-LOW IS INITIAL.
S_AEDAT-LOW = SY-DATUM - 1.
ENDIF.

IF S_AEDAT-HIGH IS INITIAL.
S_AEDAT-HIGH = S_AEDAT-LOW.
ENDIF.

IF S_AEDAT-LOW = S_AEDAT-HIGH.
S_AEDAT-HIGH = S_AEDAT-HIGH + 1.
ENDIF.

B_DATE = S_AEDAT-LOW.
D_DATE = S_AEDAT-HIGH.
TRY.
EXEC SQL.
connect to :p_connr
ENDEXEC.
CATCH CX_SY_NATIVE_SQL_ERROR INTO LO_EXCEPTION.
CALL METHOD LO_EXCEPTION->GET_TEXT
RECEIVING
RESULT = LV_ERROR_MSG.
ENDTRY.

IF LV_ERROR_MSG IS NOT INITIAL.
MESSAGE E398(00) WITH ‘连接数据库失败’.
ENDIF.

TRY.
EXEC SQL PERFORMING FRM_GET_LINES.
SELECT
ID,
USERID,
updatedate,
usernumber,
CHECKTIME
FROM dbo.checkinout
INTO :GS_DISP
where updatedate >= :S_AEDAT-LOW
AND updatedate < :S_AEDAT-HIGH
ENDEXEC.
CATCH CX_SY_NATIVE_SQL_ERROR INTO EXC_REF.
ERROR_TEXT = EXC_REF->GET_TEXT( ).
"关闭连接
EXEC SQL.
DISCONNECT :p_connr
ENDEXEC.

ENDTRY.

ENDFORM.
&---------------------------------------------------------------------
*& Form FRM_GET_LINES
&---------------------------------------------------------------------
*& text
&---------------------------------------------------------------------
*& --> p1 text
*& <-- p2 text
&---------------------------------------------------------------------
FORM FRM_GET_LINES .
IF GS_DISP IS NOT INITIAL AND GS_DISP-USERNUMBER IS NOT INITIAL.
APPEND GS_DISP TO GT_DISP.
ENDIF.
CLEAR:GS_DISP.
ENDFORM.
&---------------------------------------------------------------------
*& Form FRM_MOVE_DISP
&---------------------------------------------------------------------
*& text
&---------------------------------------------------------------------
*& --> p1 text
& <-- p2 text
&---------------------------------------------------------------------
FORM FRM_MOVE_DISP .
DATA:LT_TAB TYPE TABLE OF ZTHRPT001,
LS_TAB TYPE ZTHRPT001.
DATA: L_SEQNO TYPE CHAR20.
DELETE GT_DISP WHERE USERNUMBER IS INITIAL.
DELETE GT_DISP WHERE USERNUMBER NOT IN R_P0003.
LOOP AT GT_DISP INTO GS_DISP.
PERFORM FRM_GET_SEQNO CHANGING L_SEQNO.
LS_TAB-ZGUID = L_SEQNO.
LS_TAB-PERNR = GS_DISP-USERNUMBER.
LS_TAB-SIDAT = GS_DISP-CHECKTIME(4) && GS_DISP-CHECKTIME+5(2) && GS_DISP-CHECKTIME+8(2).
LS_TAB-SITIM = GS_DISP-CHECKTIME+11(2) && GS_DISP-CHECKTIME+14(2) && GS_DISP-CHECKTIME+17(2).
LS_TAB-PLATF = ‘10’.
LS_TAB-ERNAM = SY-UNAME.
LS_TAB-ERDAT = SY-DATUM.
LS_TAB-ERZET = SY-UZEIT.
SELECT COUNT() FROM ZTHRPT001 WHERE PERNR = LS_TAB-PERNR AND SIDAT = LS_TAB-SIDAT AND SITIM = LS_TAB-SITIM AND PLATF = ‘10’.
IF SY-SUBRC = 0.
CONTINUE.
ELSE.
APPEND LS_TAB TO LT_TAB.
ENDIF.
ENDLOOP.

IF LT_TAB IS NOT INITIAL.
MODIFY ZTHRPT001 FROM TABLE LT_TAB.
IF SY-SUBRC = 0.
COMMIT WORK AND WAIT.
MESSAGE S398(00) WITH ‘保存成功’.
ELSE.
ROLLBACK WORK.
MESSAGE E398(00) WITH ‘保存失败’.
ENDIF.
ENDIF.
ENDFORM.
&---------------------------------------------------------------------
*& Form FRM_GET_SEQNO
&---------------------------------------------------------------------
*& text
&---------------------------------------------------------------------
*& <-- L_SEQNO
&---------------------------------------------------------------------
FORM FRM_GET_SEQNO CHANGING P_SEQNO TYPE CHAR20.

CALL FUNCTION ‘NUMBER_RANGE_ENQUEUE’
EXPORTING
OBJECT = ‘ZGUID’
EXCEPTIONS
FOREIGN_LOCK = 1
OBJECT_NOT_FOUND = 2
SYSTEM_FAILURE = 3
OTHERS = 4.
IF SY-SUBRC EQ 0.
CALL FUNCTION ‘NUMBER_GET_NEXT’
EXPORTING
NR_RANGE_NR = ‘01’
OBJECT = ‘ZGUID’
IMPORTING
NUMBER = P_SEQNO
EXCEPTIONS
INTERVAL_NOT_FOUND = 1
NUMBER_RANGE_NOT_INTERN = 2
OBJECT_NOT_FOUND = 3
QUANTITY_IS_0 = 4
QUANTITY_IS_NOT_1 = 5
INTERVAL_OVERFLOW = 6
BUFFER_OVERFLOW = 7
OTHERS = 8.

CALL FUNCTION ‘NUMBER_RANGE_DEQUEUE’
EXPORTING
OBJECT = ‘ZGUID’
EXCEPTIONS
OBJECT_NOT_FOUND = 1
OTHERS = 2.
ELSE.
RAISE NUM_RANGE_ERROR .
ENDIF.

ENDFORM.
``

三、总结

注意点在于abap的变量前置要用 : ，取数时可以用 子例程去循环append（TRY. EXEC SQL PERFORMING FRM_GET_LINES. ）