hana数据库连接mysql_如何连接HANA数据库读取表数据
1, 取保SAP系统与HANA 数据库服务器网络连接
在SE38中运行report RSBDCOS0,ping一下HANA database,确保没有丢包现象发生。
也可以通过tcode:OS01来ping hana database服务器。
2, 在SAP ABAP系统中创建HANA数据库链接
使用TCode: DBCO创建ABAP系统到hana数据库的链接,这步有点类似SM59。
在connection info字段中,输入HANA服务器的hostname或者是IP地址,格式如下,
Hostname:315
其中,315 是端口, 是 HANA的instance号。
DBCO创建完毕后,通过报表ADBC_TEST_CONNECTION来检查是否创建成功,
输入DB connection名字HDB(上图创建的),运行,连接成功会提示,successfully opened。
除了用报表ADBC_TEST_CONNECTION还可以用tcode:DBACOCKPIT来检查HANA数据库连接。
3, ABAP程序实现
创建完SAP abap系统和HANA数据库的连接后,就可以通过abap程序来读取hana数据库中的数据了,
这主要是通过类CL_SQL_CONNECTION,CL_SQL_STATEMENT,CL_SQL_RESULT_SET来实现的。
完整代码:
ABAP
REPORT ztest_hana_connection.
TYPE-POOLS:adbc.
DATA lv_sql TYPE string.
DATA lv_from TYPE string.
DATA lv_where TYPE string.
DATA lv_where2 TYPE string.
DATA lv_where3 TYPE string.
DATA : v_rows TYPE i.
DATA : lt_meta TYPE adbc_rs_metadata_descr_tab,
lt_column TYPE adbc_column_tab,
ls_meta LIKE LINE OF lt_meta,
ls_column LIKE LINE OF lt_column.
*Define variables for the connection
DATA: lr_dbconn TYPE REF TO cl_sql_connection,
lv_con TYPE dbcon_name VALUE 'HDB',
lr_sql_env TYPE REF TO cl_sql_statement,
lr_sql_result TYPE REF TO cl_sql_result_set,
lr_sql_exc TYPE REF TO cx_sql_exception, "#EC NEEDED
lr_par_exc TYPE REF TO cx_parameter_invalid, "#EC NEEDED
dref TYPE REF TO data,
tab_ref TYPE REF TO data,
gt_components TYPE cl_abap_structdescr=>component_table,
gs_components LIKE LINE OF gt_components.
FIELD-SYMBOLS :
TYPE any.
*Get connection
lr_dbconn = cl_sql_connection=>get_connection( con_name = lv_con ).
CREATE OBJECT lr_sql_env
EXPORTING
con_ref = lr_dbconn.
* SQL statment
lv_sql = 'SELECT * FROM ZLYTEST01'.
* excute SQL
lr_sql_result = lr_sql_env->execute_query( lv_sql ).
* get SQL resule set
lt_meta = lr_sql_result->get_metadata( ).
LOOP AT lt_meta INTO ls_meta.
ls_column = ls_meta-column_name.
APPEND ls_column TO lt_column.
gs_components-name = ls_meta-column_name.
gs_components-type ?= cl_abap_datadescr=>describe_by_data( p_data = ls_meta-data_type ).
APPEND gs_components TO gt_components.
ENDLOOP.
DATA lr_tabledescr TYPE REF TO cl_abap_tabledescr.
lr_tabledescr = cl_abap_tabledescr=>create(
p_line_type = cl_abap_structdescr=>create( p_components = gt_components ) ).
CREATE DATA tab_ref TYPE HANDLE lr_tabledescr.
* get REFERENCE OF LT_DATA INTO tab_ref.
lr_sql_result->set_param_table(
EXPORTING
itab_ref = tab_ref " Reference to Output Variable
corresponding_fields = lt_column ).
**Execute the query
lr_sql_result->next_package( ).
v_rows = lr_sql_result->next( ).
ASSIGN tab_ref->* TO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
REPORTztest_hana_connection.
TYPE-POOLS:adbc.
DATAlv_sqlTYPEstring.
DATAlv_fromTYPEstring.
DATAlv_whereTYPEstring.
DATAlv_where2TYPEstring.
DATAlv_where3TYPEstring.
DATA:v_rowsTYPEi.
DATA:lt_metaTYPEadbc_rs_metadata_descr_tab,
lt_columnTYPEadbc_column_tab,
ls_metaLIKELINEOFlt_meta,
ls_columnLIKELINEOFlt_column.
*Define variables for the connection
DATA:lr_dbconnTYPEREF TOcl_sql_connection,
lv_conTYPEdbcon_nameVALUE'HDB',
lr_sql_envTYPEREF TOcl_sql_statement,
lr_sql_resultTYPEREF TOcl_sql_result_set,
lr_sql_excTYPEREF TOcx_sql_exception,"#EC NEEDED
lr_par_excTYPEREF TOcx_parameter_invalid,"#EC NEEDED
drefTYPEREF TO data,
tab_refTYPEREF TO data,
gt_componentsTYPEcl_abap_structdescr=>component_table,
gs_componentsLIKELINEOFgt_components.
FIELD-SYMBOLS:
TYPEany.
*Get connection
lr_dbconn=cl_sql_connection=>get_connection(con_name=lv_con).
CREATE OBJECTlr_sql_env
EXPORTING
con_ref=lr_dbconn.
* SQL statment
lv_sql='SELECT * FROM ZLYTEST01'.
* excute SQL
lr_sql_result=lr_sql_env->execute_query(lv_sql).
* get SQL resule set
lt_meta=lr_sql_result->get_metadata().
LOOP ATlt_metaINTOls_meta.
ls_column=ls_meta-column_name.
APPENDls_columnTOlt_column.
gs_components-name=ls_meta-column_name.
gs_components-type?=cl_abap_datadescr=>describe_by_data(p_data=ls_meta-data_type).
APPENDgs_componentsTOgt_components.
ENDLOOP.
DATAlr_tabledescrTYPEREF TOcl_abap_tabledescr.
lr_tabledescr=cl_abap_tabledescr=>create(
p_line_type=cl_abap_structdescr=>create(p_components=gt_components)).
CREATE DATAtab_refTYPEHANDLElr_tabledescr.
* get REFERENCE OF LT_DATA INTO tab_ref.
lr_sql_result->set_param_table(
EXPORTING
itab_ref=tab_ref" Reference to Output Variable
corresponding_fields=lt_column).
**Execute the query
lr_sql_result->next_package().
v_rows=lr_sql_result->next().
ASSIGNtab_ref->*TO
以上。
文章来自于网络,如果侵犯了您的权益,请联系站长删除!