Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

How To Export SAP Table Content to SQLite Data Containers

stefan_schnell
Active Contributor
‎03-11-2015 10:46 PM
SQLite is a platform independent database engine. So it is interesting to make SAP table data available for this engine. Here a way to export SAP table content via ABAP to SQL statements. After the export the content of the table is available as SQL statements in a text file. You can read this text file with sqlite.exe and create your SQLite database on this way. To use this way comfortable from ABAP I programmed a class with the public method GetTable2SQlCmdFile - to export the table indirect via an SQL command file, which can be imported to an SQLite database.

Here the class:
"-Begin-----------------------------------------------------------------

"-

"- Class to export SAP tables as SQL strings in text files, to import

"- the data in SQLite via read or other SQL databases

"-

"- Author: Stefan Schnell

"- Version: 1.08

"- Date: 2019-05-04

"-

"- Checked with SAP 7.50 SP 14 - Different tables, import SQLite 3.28

"- Checked with SAP 7.40 SP 12 - Table SFLIGHT

"- Checked with SAP 7.31 SP  4 - Different tables

"- Checked with SAP 7.02 SP  6 - Different tables

"-

"-----------------------------------------------------------------------



CLASS zsqlite DEFINITION PUBLIC CREATE PUBLIC .



  PUBLIC SECTION.



    CONSTANTS:

      true    TYPE i VALUE 1,

      false   TYPE i VALUE 0,

      crlf(2) TYPE c VALUE cl_abap_char_utilities=>cr_lf.



    "! Get a table in a text file as SQL commands

    "!

    "! @parameter iv_TableName        | Name of the table

    "! @parameter iv_WhereClause      | Where clause of select

    "! @parameter iv_FileName         | Name of the text file

    "! @parameter iv_SQLiteSQL        | Flag to use SQLite specific data types

    "! @parameter iv_OverWriteConfirm | Flag for request to overwrite an existing file

    METHODS gettable2sqlcmdfile

      IMPORTING

        iv_tablename        TYPE string

        iv_whereclause      TYPE string

        iv_filename         TYPE string

        iv_sqlitesql        TYPE i DEFAULT 1

        iv_overwriteconfirm TYPE c DEFAULT SPACE.



  PROTECTED SECTION.



  PRIVATE SECTION.



    "! Get the structure of a table as SQL string

    "!

    "! @parameter iv_TableName | Name of the table

    "! @parameter iv_SQLiteSQL | Flag to use SQLite specific data types

    "!

    "! @parameter cv_SQLCmd    | SQL command to create a table

    METHODS gettabstruc

      IMPORTING

        iv_tablename TYPE string

        iv_sqlitesql TYPE i

      CHANGING

        cv_sqlcmd    TYPE string.



    "! Get the data of a table as a table of SQL strings

    "!

    "! @parameter iv_TableName    | Name of the table

    "! @parameter iv_WhereClause  | Where clause of select

    "!

    "! @parameter SQLCmds         | SQL commands

    METHODS gettabdata

      IMPORTING

        iv_tablename   TYPE string

        iv_whereclause TYPE string

      CHANGING

        ct_sqlcmds     TYPE STANDARD TABLE.



    "! Delete leading zeros in a string

    "!

    "! @parameter cv_NumChar | Numeric string

    METHODS trimleadingzeros

      CHANGING

        cv_numchar TYPE string.



ENDCLASS.







CLASS ZSQLITE IMPLEMENTATION.







  METHOD gettabdata."---------------------------------------------------

    "-

    "- Get the data of a table as a table of SQL strings

    "-

    "- Arguments: iv_TableName   = Name of the table as string

    "-            iv_WhereClause = Where clause of select

    "- Changing:  ct_sqlcmds     = Table with SQL commands

    "-

    "-------------------------------------------------------------------



    TYPES: BEGIN OF tabstruc,

             tabname   TYPE dd03l-tabname,

             fieldname TYPE dd03l-fieldname,

             position  TYPE dd03l-position,

             inttype   TYPE dd03l-inttype,

             intlen    TYPE dd03l-intlen,

             datatype  TYPE dd03l-datatype,

             decimals  TYPE dd03l-decimals,

           END OF tabstruc.



    "-Maximum length of a record----------------------------------------

    DATA: BEGIN OF tablebuffer,

            buffer(65535),

          END OF tablebuffer.



    DATA:

      lt_tabstruc      TYPE STANDARD TABLE OF tabstruc,

      ls_tabstruc      TYPE tabstruc,

      lv_strtemp       TYPE string,

      lv_fieldcontent  TYPE string,

      lv_ifieldcontent TYPE i,

      lv_position      TYPE i,

      lv_i             TYPE i.



    FIELD-SYMBOLS:

      <tabstruc>      LIKE LINE OF lt_tabstruc,

      <table>         TYPE any,

      <fieldcontent>  TYPE any,

      <pfieldcontent> TYPE p,

      <cfieldcontent> TYPE c,

      <nfieldcontent> TYPE n,

      <xfieldcontent> TYPE x,

      <ifieldcontent> TYPE i.



    SELECT tabname fieldname position inttype intlen datatype

      decimals INTO CORRESPONDING FIELDS OF TABLE lt_tabstruc

      FROM dd03l WHERE tabname = iv_tablename ORDER BY position.



    ASSIGN tablebuffer TO <table> CASTING TYPE (iv_tablename).



    SELECT * FROM (iv_tablename) INTO <table> WHERE (iv_whereclause).



      lv_strtemp = `INSERT INTO ` && iv_tablename && ` VALUES (`.



      LOOP AT lt_tabstruc INTO ls_tabstruc.

        CASE ls_tabstruc-inttype.



          WHEN 'P'."-Packed numbers-------------------------------------

            ASSIGN COMPONENT ls_tabstruc-fieldname

              OF STRUCTURE <table> TO <pfieldcontent> CASTING.

            MOVE <pfieldcontent> TO lv_fieldcontent.

            lv_strtemp = lv_strtemp && ` ` && lv_fieldcontent && ` ,`.



          WHEN 'C'."-Characters-----------------------------------------

            ASSIGN COMPONENT ls_tabstruc-fieldname

              OF STRUCTURE <table> TO <cfieldcontent> CASTING.

            MOVE <cfieldcontent> TO lv_fieldcontent.

            REPLACE ALL OCCURRENCES OF '''' IN lv_fieldcontent WITH ''''''.

            lv_fieldcontent = `'` && lv_fieldcontent && `'`.

            lv_strtemp = lv_strtemp && ` ` && lv_fieldcontent && ` ,`.



          WHEN 'N'."-Numeric characters---------------------------------

            ASSIGN COMPONENT ls_tabstruc-fieldname

              OF STRUCTURE <table> TO <nfieldcontent> CASTING.

            MOVE <nfieldcontent> TO lv_fieldcontent.

            CALL METHOD me->trimleadingzeros

              CHANGING

                cv_numchar = lv_fieldcontent.

            lv_fieldcontent = `'` && lv_fieldcontent && `'`.

            lv_strtemp = lv_strtemp && ` ` && lv_fieldcontent && ` ,`.



          WHEN 'X'."-Byte sequence--------------------------------------

            CASE ls_tabstruc-datatype.

              WHEN 'INT1' OR 'INT2'.

                ASSIGN COMPONENT ls_tabstruc-fieldname

                  OF STRUCTURE <table> TO <xfieldcontent> CASTING.

                MOVE <xfieldcontent> TO lv_ifieldcontent.

                MOVE lv_ifieldcontent TO lv_fieldcontent.

              WHEN 'INT4'.

                ASSIGN COMPONENT ls_tabstruc-fieldname

                  OF STRUCTURE <table> TO <ifieldcontent> CASTING.

                MOVE <ifieldcontent> TO lv_ifieldcontent.

                MOVE lv_ifieldcontent TO lv_fieldcontent.

                CALL FUNCTION 'CLOI_PUT_SIGN_IN_FRONT'

                  CHANGING

                    value = lv_fieldcontent.

              WHEN OTHERS.

                ASSIGN COMPONENT ls_tabstruc-fieldname

                  OF STRUCTURE <table> TO <xfieldcontent> CASTING.

                MOVE <xfieldcontent> TO lv_fieldcontent.

                lv_fieldcontent = `'` && lv_fieldcontent && `'`.

            ENDCASE.

            lv_strtemp = lv_strtemp && ` ` && lv_fieldcontent && ` ,`.



          WHEN 'D'."-Date-----------------------------------------------

            ASSIGN COMPONENT ls_tabstruc-fieldname

              OF STRUCTURE <table> TO <fieldcontent>.

            MOVE <fieldcontent> TO lv_fieldcontent.

            "- To compensate empty or 00000000 date fields--------------

            IF lv_fieldcontent IS INITIAL OR lv_fieldcontent = '00000000'.

              lv_fieldcontent = '0'.

            ENDIF.

            lv_strtemp = lv_strtemp && ` ` && lv_fieldcontent && ` ,`.



          WHEN space."-Structure----------------------------------------

            "-

            "- To compensate structures

            "-

            "-----------------------------------------------------------



          WHEN OTHERS."-All other---------------------------------------

            ASSIGN COMPONENT ls_tabstruc-fieldname

              OF STRUCTURE <table> TO <fieldcontent>.

            MOVE <fieldcontent> TO lv_fieldcontent.

            lv_strtemp = lv_strtemp && ` ` && lv_fieldcontent && ` ,`.



        ENDCASE.

      ENDLOOP.



      lv_i = strlen( lv_strtemp ).

      lv_i = lv_i - 1.

      MOVE lv_strtemp+0(lv_i) TO lv_strtemp.

      lv_strtemp = lv_strtemp && ` );`.

      APPEND lv_strtemp TO ct_sqlcmds.



    ENDSELECT.



  ENDMETHOD.







  METHOD gettable2sqlcmdfile."------------------------------------------

    "-

    "- Get a table in a text file as SQL commands

    "-

    "- Arguments: lv_TableName        = Name of the table as string

    "-            lv_WhereClause      = Where clause of select

    "-            lv_FileName         = Name of the text file as string

    "-            lv_SQLiteSQL        = Flag to use SQLite specific data types

    "-                                  as integer (0 or 1)

    "-            lv_OverWriteConfirm = Flag for request to overwrite

    "-                                  an existing file as char

    "-                                  (Space or X)

    "-

    "-------------------------------------------------------------------



    DATA:

      lv_sqlcmd  TYPE string,

      lt_sqlcmds TYPE STANDARD TABLE OF string.



    "-Get the structure of the table------------------------------------

    CALL METHOD me->gettabstruc

      EXPORTING

        iv_tablename = iv_tablename

        iv_sqlitesql = iv_sqlitesql

      CHANGING

        cv_sqlcmd    = lv_sqlcmd.



    APPEND lv_sqlcmd TO lt_sqlcmds.



    "-Get the data of the table-----------------------------------------

    CALL METHOD me->gettabdata

      EXPORTING

        iv_tablename   = iv_tablename

        iv_whereclause = iv_whereclause

      CHANGING

        ct_sqlcmds     = lt_sqlcmds.



    "-Write the data to the output file---------------------------------

    CALL METHOD cl_gui_frontend_services=>gui_download

      EXPORTING

        filename          = iv_filename

        confirm_overwrite = iv_overwriteconfirm

      CHANGING

        data_tab          = lt_sqlcmds

      EXCEPTIONS

        OTHERS            = 1.



  ENDMETHOD.







  METHOD gettabstruc."--------------------------------------------------

    "-

    "- Get the structure of a table as SQL string

    "-

    "- Arguments: lv_TableName = Name of the table as string

    "-            lv_SQLiteSQL = Flag to use SQLite specific data types

    "-                           as integer (0 or 1)

    "- Return:    cv_SQLCmd    = Command to create a table

    "-

    "-------------------------------------------------------------------



    TYPES: BEGIN OF tabstruc,

             tabname   TYPE dd03l-tabname,

             fieldname TYPE dd03l-fieldname,

             position  TYPE dd03l-position,

             inttype   TYPE dd03l-inttype,

             datatype  TYPE dd03l-datatype,

             leng      TYPE dd03l-leng,

             decimals  TYPE dd03l-decimals,

           END OF tabstruc.



    DATA:

      lt_tabstruc TYPE STANDARD TABLE OF tabstruc,

      ls_tabstruc TYPE tabstruc,

      lv_i        TYPE i,

      lv_len      TYPE string,

      lv_dec      TYPE string.



    SELECT tabname fieldname position inttype datatype leng decimals

      INTO CORRESPONDING FIELDS OF TABLE lt_tabstruc FROM dd03l

      WHERE tabname = iv_tablename ORDER BY position.



    cv_sqlcmd = `CREATE TABLE ` && iv_tablename && ` (`.



    LOOP AT lt_tabstruc INTO ls_tabstruc.

      lv_i = lv_i + 1.

      lv_len = ls_tabstruc-leng.

      CALL METHOD me->trimleadingzeros CHANGING cv_numchar = lv_len.

      CASE ls_tabstruc-inttype.



        WHEN 'C' OR 'N'."-----------------------------------------------

          "-

          "- Convert C (Character string) or N (Character string with

          "- digits only) to SQL CHAR or SQLite TEXT

          "-

          "-------------------------------------------------------------

          cv_sqlcmd = cv_sqlcmd && ` ` && ls_tabstruc-fieldname.

          IF iv_sqlitesql = false.

            cv_sqlcmd = cv_sqlcmd && ` CHAR(`.

          ELSE.

            cv_sqlcmd = cv_sqlcmd && ` TEXT(`.

          ENDIF.

          cv_sqlcmd = cv_sqlcmd && lv_len && ')'.

          IF lv_i <> sy-dbcnt.

            cv_sqlcmd = cv_sqlcmd && ','.

          ENDIF.



        WHEN 'D' OR 'T'."-----------------------------------------------

          "-

          "- Convert D (Date) or T (Time) to SQL NUMERIC or SQLite

          "- NUMERIC

          "-

          "-------------------------------------------------------------

          cv_sqlcmd = cv_sqlcmd && ` ` && ls_tabstruc-fieldname.

          cv_sqlcmd = cv_sqlcmd && ` NUMERIC(` && lv_len && ')'.

          IF lv_i <> sy-dbcnt.

            cv_sqlcmd = cv_sqlcmd && ','.

          ENDIF.



        WHEN 'I'."------------------------------------------------------

          "-

          "- Convert I (Integer number) to SQL NUMERIC or

          "- SQLite INTEGER

          "-

          "-------------------------------------------------------------

          cv_sqlcmd = cv_sqlcmd && ` ` && ls_tabstruc-fieldname.

          IF iv_sqlitesql = false.

            cv_sqlcmd = cv_sqlcmd && ` NUMERIC(`.

          ELSE.

            cv_sqlcmd = cv_sqlcmd && ` INTEGER(`.

          ENDIF.

          cv_sqlcmd = cv_sqlcmd && lv_len && ')'.

          IF lv_i <> sy-dbcnt.

            cv_sqlcmd = cv_sqlcmd && ','.

          ENDIF.



        WHEN 'F' OR 'P'."-----------------------------------------------

          "-

          "- Convert F (Floating point number) or P (Packed number)

          "- to SQL NUMERIC or SQLite REAL

          "-

          "-------------------------------------------------------------

          cv_sqlcmd = cv_sqlcmd && ` ` && ls_tabstruc-fieldname.

          IF iv_sqlitesql = false.

            cv_sqlcmd = cv_sqlcmd && ` DECIMAL(`.

          ELSE.

            cv_sqlcmd = cv_sqlcmd && ` REAL(`.

          ENDIF.

          lv_dec = ls_tabstruc-decimals.

          CALL METHOD me->trimleadingzeros

            CHANGING

              cv_numchar = lv_dec.

          cv_sqlcmd = cv_sqlcmd && lv_len && ',' && lv_dec && ')'.

          IF lv_i <> sy-dbcnt.

            cv_sqlcmd = cv_sqlcmd && ','.

          ENDIF.



        WHEN 'X'."------------------------------------------------------

          "-

          "- Convert X (Byte sequence) to SQL NUMERIC or

          "- SQLite INTEGER or SQL CHAR or SQLite TEXT

          "-

          "-------------------------------------------------------------

          cv_sqlcmd = cv_sqlcmd && ` ` && ls_tabstruc-fieldname.

          IF iv_sqlitesql = false.

            CASE ls_tabstruc-datatype.

              WHEN 'INT1' OR 'INT2' OR 'INT4'.

                cv_sqlcmd = cv_sqlcmd && ` NUMERIC(`.

              WHEN OTHERS.

                cv_sqlcmd = cv_sqlcmd && ` CHAR(`.

            ENDCASE.

          ELSE.

            CASE ls_tabstruc-datatype.

              WHEN  'INT1' OR 'INT2' OR 'INT4'.

                cv_sqlcmd = cv_sqlcmd && ` INTEGER(`.

              WHEN OTHERS.

                cv_sqlcmd = cv_sqlcmd && ` TEXT(`.

            ENDCASE.

          ENDIF.

          cv_sqlcmd = cv_sqlcmd && lv_len && ')'.

          IF lv_i <> sy-dbcnt.

            cv_sqlcmd = cv_sqlcmd && ','.

          ENDIF.



      ENDCASE.



    ENDLOOP.



    cv_sqlcmd = cv_sqlcmd && ` );`.



  ENDMETHOD.







  METHOD trimleadingzeros."---------------------------------------------

    "-

    "- Delete leading zeros in a string

    "-

    "- Arguments: NumChar = Numeric string

    "- Return: Numeric string without leading zeros

    "-

    "-------------------------------------------------------------------



    DATA:

      lv_numcharlen TYPE i,

      lv_newlen     TYPE i,

      lv_pos        TYPE integer,

      lv_char(1)    TYPE c.



    lv_numcharlen = strlen( cv_numchar ).

    DO lv_numcharlen TIMES.

      lv_pos = sy-index - 1.

      MOVE cv_numchar+lv_pos(1) TO lv_char.

      IF lv_char <> '0'.

        EXIT.

      ENDIF.

    ENDDO.

    lv_newlen = lv_numcharlen - lv_pos.

    MOVE cv_numchar+lv_pos(lv_newlen) TO cv_numchar.



  ENDMETHOD.







ENDCLASS.

And here a report to test the class with different tables from SAPBC_DATAMODEL package:
REPORT zsqlite.



"-Begin-----------------------------------------------------------------

"-

"- An example how to use zSQLite class to export SAP tables to an

"- SQLite database as a collection of SQL statements in a text file.

"- This must be imported in SQLite via .read command.

"-

"- Author: Stefan Schnell

"- Date: 2019-05-04

"-

"-----------------------------------------------------------------------



DATA:

  lo_sqlite           TYPE REF TO zsqlite,

  lt_tablenames       TYPE STANDARD TABLE OF string,

  lv_tablename        TYPE string,

  lv_whereclause      TYPE string,

  lv_filename         TYPE string,

  lv_sqlitesql        TYPE i VALUE 1,

  lv_confirmoverwrite TYPE abap_bool VALUE abap_true.



lt_tablenames = VALUE #(

                         ( `SFLIGHT` )

                         ( `SFLIGHTS` )

                         ( `SAIRPORT` )

                         ( `SAPLANE` )

                         ( `SBOOK` )

                         ( `SBUSPART` )

                         ( `SCARPLAN` )

                         ( `SCARR` )

                         ( `SCURR` )

                         ( `SPFLI` )

                       ).



CREATE OBJECT lo_sqlite.



LOOP AT lt_tablenames INTO lv_tablename.



  lv_filename = 'C:\Dummy\' && lv_tablename && '.sql'.



  CALL METHOD lo_sqlite->gettable2sqlcmdfile

    EXPORTING

      iv_tablename        = lv_tablename

      iv_whereclause      = lv_whereclause

      iv_filename         = lv_filename

      iv_sqlitesql        = lv_sqlitesql

      iv_overwriteconfirm = lv_confirmoverwrite.



ENDLOOP.



"-End-------------------------------------------------------------------

You need the SQLite library to import the data from here.

To import all these tables into one SQLite database you can use this command set:
sqlite3.exe SFLIGHT.sqlite ".read SAIRPORT.sql"

sqlite3.exe SFLIGHT.sqlite ".read SAPLANE.sql"

sqlite3.exe SFLIGHT.sqlite ".read SBOOK.sql"

sqlite3.exe SFLIGHT.sqlite ".read SBUSPART.sql"

sqlite3.exe SFLIGHT.sqlite ".read SCARPLAN.sql"

sqlite3.exe SFLIGHT.sqlite ".read SCARR.sql"

sqlite3.exe SFLIGHT.sqlite ".read SCURR.sql"

sqlite3.exe SFLIGHT.sqlite ".read SFLIGHT.sql"

sqlite3.exe SFLIGHT.sqlite ".read SFLIGHTS.sql"

sqlite3.exe SFLIGHT.sqlite ".read SPFLI.sql"

And after that step you find all the tables with its content inside the SFLIGHT.sqlite database.

Enjoy the possibility to export SAP tables into SQLite databases. With this way you can use the content easily on different platforms and in different environments,
Labels in this area
Popular Blog Posts