在 Excel 内使用 ODBC 消费 SAP ABAP CDS view

Consuming CDS View Entities Using ODBC-Based Client Tools

本文介绍通过基于 ODBC(Open Database Connectivity) 的 SQL 语句访问 ABAP 系统 CDS view 数据的方法。

ODBC 是一套标准的访问数据库的 API.

Why an ODBC Driver for ABAP?

在某些情况下，您希望对 ABAP 系统拥有的 CDS 对象进行外部 SQL 读取访问。对 ABAP 系统底层 SAP HANA 数据库的直接 SQL 读取访问不是一个好的选择。 SAP Note 2511210 中列出了一些问题。ABAP 数据库中的名称和内部结构可能不稳定，因为生命周期由 ABAP 系统管理。类型转换可能不会按预期执行，例如，NUMC 数据类型可能未正确填充，或者货币数据可能未正确移动。只有从 ABAP 系统访问视图时，数据库会话变量才能正确设置。如果直接用底层 SQL 语句查询，这样绕过了 ABAP 级别的安全概念。

当您通过使用 ODBC 直接访问 ABAP 系统将 ABAP 系统本身视为数据库时，所有这些问题都会消失。在这种情况下，身份验证和授权是使用 ABAP 用户完成的。应用完整的 ABAP SQL 语义，甚至可以使用应用程序服务器级别的缓冲以及 ABAP 级别的访问控制和读取访问日志记录。

与 ODATA 接口相比，ODBC 接口的优势在于它允许对所有公开的 ABAP CDS 视图实体进行无限制的 SQL 访问。来自不同实体的数据可以临时方式连接起来，并且可以聚合数据以进行分析查询。

在其当前版本中，“用于 ABAP 的 ODBC 驱动程序”仅支持在 ABAP 系统中使用具有特权访问权限（无 DCL）的技术用户。只允许对公开的 ABAP CDS 对象进行读取访问。

Overview of Steps and Prerequisites

要通过 ODBC 访问 ABAP 系统中的 CDS 视图实体，这些实体首先需要在后端系统中正确被暴露，然后才能通过 ODBC 应用程序中的“用于 ABAP 的 ODBC 驱动程序”访问它们。

ODBC 应用程序可以是任何能够加载 ODBC 驱动程序的客户端工具，也可以是用能够使用 ODBC 驱动程序的程序语言（例如 C、C++、python、node.js、PHP）编写的程序。

本文将展示 Windows 上的 Microsoft Excel 用例。

“适用于 ABAP 的 ODBC 驱动程序”本身在 Windows 和 Linux 上可用。

(1) Create and fill some test tables

首先创建两个测试数据库表，用于存储订单的抬头和行项目：

@EndUserText.label : 'ORDER ITEMS'@AbapCatalog.enhancement.category : #[e-mail] : #[e-mail] : #[e-mail] : #RESTRICTEDdefine table zorderitems {
  key orderid : abap.numc(10) not null;
  key pos     : abap.int4 not null;
  item        : abap.char(100) not null;
  amount      : abap.int4 not null;}@EndUserText.label : 'Jerry ORDERS'@AbapCatalog.enhancement.category : #[e-mail] : #[e-mail] : #[e-mail] : #RESTRICTEDdefine table zorders {
  key id       : abap.numc(10) not null;
  creationdate : abap.datn;}

创建一个 ABAP class，用于填充数据：

class zcl_fill_orders definition

  public
  final
  create public.

  public section.
    interfaces if_oo_adt_classrun.
  protected section.
  private section.endclass.class zcl_fill_orders implementation.

  method if_oo_adt_classrun~main.

    data: lt_orders type table of zorders.
    delete from zorders.
    lt_orders = value #(
      ( id = '1' creationdate = '20210801' )
      ( id = '2' creationdate = '20210802'  )
      ( id = '3' creationdate = '20210803' )
    ).
    insert zorders from table @lt_orders.
    out->write( sy-dbcnt ).

    data: lt_orderitems type table of zorderitems.
    delete from zorderitems.
    lt_orderitems = value #(
      ( orderid = '1' pos = '1' item = 'Apple' amount = '5' )
      ( orderid = '1' pos = '2' item = 'Banana' amount = '5' )
      ( orderid = '1' pos = '3' item = 'Orange Juice' amount = '2' )

      ( orderid = '2' pos = '1' item = 'Orange' amount = '10' )
      ( orderid = '2' pos = '2' item = 'Apple' amount = '5' )

      ( orderid = '3' pos = '1' item = 'Bottle Water' amount = '5' )
    ).
    insert zorderitems from table @lt_orderitems.
    out->write( sy-dbcnt ).

  endmethod.endclass.

插入的测试数据如下图所示：

下面给这些数据库表创建 CDS view，因为只有 CDS view 才能暴露给 ODBC 消费端。

@AccessControl.authorizationCheck: #[e-mail]: 'ORDERS'define view entity ZORDERSVIEW as select from zorders {
  key id as Id,
  creationdate as CreationDate}@AccessControl.authorizationCheck: #[e-mail]: 'ORDER ITEMS'define view entity ZORDERITEMSVIEW as select from zorderitems {
  key orderid as OrderId,
  key pos as Pos,
  item as Item,
  amount as Amount}

现在我们需要一个 Service definition 和一个对应的 SQL 类型的 Service binding 来定义新的 CDS 视图实体, 并通过 SQL 服务公开。

要定义新的服务定义，请右键单击项目资源管理器中的 ZORDERSVIEW 或 ZORDERITEMSVIEW 视图之一，然后选择“New Service Definition”并为服务定义命名，例如 Z_SERVICE_DEF_SQL。

@EndUserText.label: 'test SQL service'define service Z_SERVICE_DEF_SQL {
  expose ZORDERSVIEW as Orders;
  expose ZORDERITEMSVIEW as OrderItems;}

这个新的 service 定义可以用在一个 service binding 里了。选中 service definition，右键菜单里创建 service binding：

binding 类型一定要选择成：SQL1 - Web API

这个 Service Binding 的名称 ZORDERS,稍后将成为外部 ODBC 消费端使用的 schema 名称。

由于我们要使用ABAP系统中的技术用户来访问服务绑定，我们现在需要在SAP BTP ABAP Environment系统中创建一个通信场景 communication scenario：Z_COMM_SCENARIO_SQL 和一个通信安排 communication arrangement.

在生成的 communication scenario 里，点击 inbound 标签页。Inbound Service ID，选择 S_PRIVILEGED_SQL1:

S_PRIVILEGED_SQL1 是预配置的服务，用于对 CDS 视图实体的特权访问。DCL 代表 Data Control Language，它提供了一种访问控制机制，根据条件对从数据库中 CDS 视图返回的结果进行过滤。

Authentication method 选择 Basic：

切换到 Authorization 标签页，添加新的 Authorization 对象：S_SQL_VIEW

给这个 Authorization object 的三个字段分别维护上对应的值。

SQL_SCHEMA 必须包含我们要授予访问权限的服务绑定的名称。 在这种情况下，大写符号是可以的。

SQL_VIEW 的值“*”意味着我们允许访问附加到服务绑定 ZORDERS 的服务定义中的所有视图。

由于当前只允许只读访问，因此 SQL_VIEWOP=SELECT 是必需的。

实际上，我们将模式 ZORDERS 中所有视图的 SELECT 权限授予通信场景的用户，就像我们在数据库中发出 GRANT 语句一样。

点击 publish locally：

右键单击 ABAP Development Tool 里的 ABAP 项目，选择属性，找到该 ABAP 环境的 Fiori url：

创建一个新的 communication system：

勾上 Inbound Only：

创建一个新的 communication user，维护长度为 20 的密码，比如 S!重复三遍。

最后一步，创建 Communication arrangement，将 ADT 里创建的 Communication scenario 同刚刚创建的 Communication system 连接起来。

输入 Communication system，可以自动把 user 也带出来：

把 Service url 抄下来：

https://\.http://abap.eu10.hana.ondemand.com/sap/bc/sql/sql1/sap/S_PRIVILEGED

Install the ODBC driver on Windows

访问 SAP support portal 的 software download 区域：

https://launchpad.support.sap.com/#/softwarecenter

搜索关键字：ODBC DRIVER FOR ABAP

根据操作系统的不同，下载对应的驱动：

同理，下载 SAPCRYPTOLIB：

扩展名为 .SAR, 需要用专门的解压工具 SAPCAR 来解压：

解压之后的文件列表如下图所示：

启动 ODBC data source 应用，这是 Windows10 自带的应用：

点击 Add 按钮：

驱动类型选择 ODBC driver for ABAP：

新建一条 User Data Source Name：

最后就可以在 Excel 等客户程序里通过 ODBC 消费 CDS view 数据了：

选择刚刚创建的 User DataSource Name:

输入之前创建的 technical user credential：

点击 Connect 按钮之后，我们能在 excel 里看到 service binding ZORDERS 暴露的全部数据：

我们也可以在 Excel From ODBC 的 Advanced Options 里，指定我们想要执行的 SQL 语句：

可以直接在 Excel 里看到执行结果：