Hello community,

I create a way to export SAP table content via ABAP to SQLite databases. You export the content of the table 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.07
"- Date: 2017-01-14
"-
"- Checked with SAP 7.40 SP 12 and table SFLIGHT
"- Checked with SAP 7.31 SP 4 and different tables
"- Checked with SAP 7.02 SP 6 and different tables
"-
"-----------------------------------------------------------------------

  Class zSQLite Definition Public Create Public .

    Public Section.

        Constants True Type i Value 1.
        Constants False Type i Value 0.

        Constants CrLf(2) Type c Value CL_ABAP_CHAR_UTILITIES=>CR_LF.

        "! Get a table in a text file as SQL commands
        "!
        "! @parameter TableName        | Name of the table
        "! @parameter WhereClause      | Where clause of select
        "! @parameter FileName         | Name of the text file
        "! @parameter SQLiteSQL        | Flag to use SQLite specific data types
        "! @parameter OverWriteConfirm | Flag for request to overwrite an existing file
        Methods GetTable2SQLCmdFile
          Importing
            TableName Type String
            WhereClause Type String
            FileName Type String
            SQLiteSQL Type i
            OverWriteConfirm Type c.

    Protected Section.

    Private Section.

        "! Get the structure of a table as SQL string
        "!
        "! @parameter TableName | Name of the table
        "! @parameter SQLiteSQL | Flag to use SQLite specific data types
        "!
        "! @parameter SQLCmd    | SQL command to create a table
        Methods GetTabStruc
          Importing
            TableName Type String
            SQLiteSQL Type i
          Changing
            SQLCmd Type String..

        "! Get the data of a table as a table of SQL strings
        "!
        "! @parameter TableName    | Name of the table
        "! @parameter Where Clause | Where clause of select
        "!
        "! @parameter SQLCmds      | SQL commands
        Methods GetTabData
          Importing
            TableName Type String
            WhereClause Type String
          Changing
            SQLCmds Type Standard Table.

        "! Delete leading zeros in a string
        "!
        "! @parameter NumChar | Numeric string
        Methods TrimLeadingZeros
          Changing
            NumChar Type String.

EndClass.



Class ZSQLITE Implementation.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSQLITE->GETTABDATA
* +-------------------------------------------------------------------------------------------------+
* | [--->] TABLENAME                      TYPE        STRING
* | [--->] WHERECLAUSE                    TYPE        STRING
* | [<-->] SQLCMDS                        TYPE        STANDARD TABLE
* +--------------------------------------------------------------------------------------</SIGNATURE>
    Method GetTabData.

      "-Function GetTabData---------------------------------------------
      "-
      "- Get the data of a table as a table of SQL strings
      "-
      "- Arguments: TableName = Name of the table as string
      "-            WhereClause = Where clause of select
      "-            SQLCmds = SQL commands in a table of strings
      "- Return: Table with SQL commands
      "-
      "-----------------------------------------------------------------

      "-Structures------------------------------------------------------
        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.

      "-Variables-------------------------------------------------------
        Data TabStruc Type Standard Table Of TabStruc.
        Data waTabStruc Type TabStruc.
        Data strTemp Type String Value ''.
        Data FieldContent Type String Value ''.
        Data iFieldContent Type i Value 0.
        Data Position Type i Value 0.
        Data i Type i Value 0.
        Field-Symbols <TabStruc> Like Line Of TabStruc.
        Field-Symbols <Table> Type Any.
        Field-Symbols <FieldContent> Type Any.
        Field-Symbols <pFieldContent> Type p.
        Field-Symbols <cFieldContent> Type c.
        Field-Symbols <nFieldContent> Type n.
        Field-Symbols <xFieldContent> Type x.
        Field-Symbols <iFieldContent> Type i.

      Select TABNAME FIELDNAME POSITION INTTYPE INTLEN DATATYPE
        DECIMALS Into Corresponding Fields Of Table TabStruc
        From DD03L Where TABNAME = TableName Order By POSITION.

      Assign TableBuffer To <Table> Casting Type (TableName).

      Select * From (TableName) Into <Table> Where (WhereClause).

        strTemp = `INSERT INTO ` && TableName && ` VALUES (`.

        Loop At TabStruc Into waTabStruc.
          Case waTabStruc-INTTYPE.

            "-Packed numbers--------------------------------------------
              When 'P'.
                Assign Component waTabStruc-FIELDNAME
                Of Structure <Table> To <pFieldContent> Casting.
                Move <pFieldContent> To FieldContent.
                strTemp = strTemp && ` ` && FieldContent && ` ,`.

            "-Characters------------------------------------------------
              When 'C'.
                Assign Component waTabStruc-FIELDNAME
                  Of Structure <Table> To <cFieldContent> Casting.
                Move <cFieldContent> To FieldContent.
                FieldContent = `'` && FieldContent && `'`.
                strTemp = strTemp && ` ` && FieldContent && ` ,`.

            "-Numeric characters----------------------------------------
              When 'N'.
                Assign Component waTabStruc-FIELDNAME
                  Of Structure <Table> To <nFieldContent> Casting.
                Move <nFieldContent> To FieldContent.
                Call Method Me->TrimLeadingZeros
                  Changing
                    NumChar = FieldContent.
                FieldContent = `'` && FieldContent && `'`.
                strTemp = strTemp && ` ` && FieldContent && ` ,`.

            "-Byte sequence---------------------------------------------
              When 'X'.
                Case waTabStruc-DATATYPE.
                  When 'INT1' Or 'INT2'.
                    Assign Component waTabStruc-FIELDNAME
                      Of Structure <Table> To <xFieldContent> Casting.
                    Move <xFieldContent> To iFieldContent.
                    Move iFieldContent To FieldContent.
                  When 'INT4'.
                    Assign Component waTabStruc-FIELDNAME
                      Of Structure <Table> To <iFieldContent> Casting.
                    Move <iFieldContent> To iFieldContent.
                    Move iFieldContent To FieldContent.
                    Call Function 'CLOI_PUT_SIGN_IN_FRONT'
                      Changing value = FieldContent.
                  When Others.
                    Assign Component waTabStruc-FIELDNAME
                      Of Structure <Table> To <xFieldContent> Casting.
                    Move <xFieldContent> To FieldContent.
                    FieldContent = `'` && FieldContent && `'`.
                EndCase.
                strTemp = strTemp && ` ` && FieldContent && ` ,`.

            "-Date------------------------------------------------------
              When 'D'.
                Assign Component waTabStruc-FIELDNAME
                  Of Structure <Table> To <FieldContent>.
                Move <FieldContent> To FieldContent.
                "- To compensate empty or 00000000 date fields----------
                  If FieldContent Is Initial Or FieldContent = '00000000'.
                    FieldContent = '0'.
                  EndIf.
                strTemp = strTemp && ` ` && FieldContent && ` ,`.

            "-Structure-------------------------------------------------
            "-
            "- To compensate structures
            "-
            "-----------------------------------------------------------
              When Space.

            "-All other-------------------------------------------------
              When Others.
                Assign Component waTabStruc-FIELDNAME
                  Of Structure <Table> To <FieldContent>.
                Move <FieldContent> To FieldContent.
                strTemp = strTemp && ` ` && FieldContent && ` ,`.

          EndCase.
        EndLoop.

        i = StrLen( strTemp ).
        i = i - 1.
        Move strTemp+0(i) To strTemp.
        strTemp = strTemp && ` );`.
        Append strTemp To SQLCmds.

      EndSelect.

    EndMethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZSQLITE->GETTABLE2SQLCMDFILE
* +-------------------------------------------------------------------------------------------------+
* | [--->] TABLENAME                      TYPE        STRING
* | [--->] WHERECLAUSE                    TYPE        STRING
* | [--->] FILENAME                       TYPE        STRING
* | [--->] SQLITESQL                      TYPE        I
* | [--->] OVERWRITECONFIRM               TYPE        C
* +--------------------------------------------------------------------------------------</SIGNATURE>
    Method GetTable2SQLCmdFile.

      "-Sub GetTable2SQLCmdFile-----------------------------------------
      "-
      "- Get a table in a text file as SQL commands
      "-
      "- Arguments: TableName = Name of the table as string
      "-            WhereClause = Where clause of select
      "-            FileName = Name of the text file as string
      "-            SQLiteSQL = Flag to use SQLite specific data types
      "-                        as integer (0 or 1)
      "-            OverWriteConfirm = Flag for request to overwrite
      "-                               an existing file as char
      "-                               (Space or X)
      "-
      "-----------------------------------------------------------------

      "-Variables-------------------------------------------------------
        Data SQLCmd Type String Value ''.
        Data SQLCmds Type Standard Table Of String.
        Data FileLength Type i Value 0.

      "-Get the structure of the table----------------------------------
        Call Method Me->GetTabStruc
          Exporting
            TableName = TableName
            SQLiteSQL = SQLiteSQL
          Changing
            SQLCmd = SQLCmd.

        Append SQLCmd To SQLCmds.

      "-Get the data of the table---------------------------------------
        Call Method Me->GetTabData
          Exporting
            TableName = TableName
            WhereClause = WhereClause
          Changing
            SQLCmds = SQLCmds.

      "-Write the data to the output file-------------------------------
        Call Function 'GUI_DOWNLOAD'
          Exporting
            FILENAME = FileName
            CONFIRM_OVERWRITE = OverwriteConfirm
          Importing
            FILELENGTH = FileLength
          Tables
            DATA_TAB = SQLCmds
          Exceptions
            Others = 1.

    EndMethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSQLITE->GETTABSTRUC
* +-------------------------------------------------------------------------------------------------+
* | [--->] TABLENAME                      TYPE        STRING
* | [--->] SQLITESQL                      TYPE        I
* | [<-->] SQLCMD                         TYPE        STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
    Method GetTabStruc.

      "-Function GetTabStruc--------------------------------------------
      "-
      "- Get the structure of a table as SQL string
      "-
      "- Arguments: TableName = Name of the table as string
      "-            SQLiteSQL = Flag to use SQLite specific data types
      "-                        as integer (0 or 1)
      "-            SQLCmd = SQL command to create a table as string
      "- Return: Command to create a table
      "-
      "-----------------------------------------------------------------

      "-Structures------------------------------------------------------
        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.

      "-Variables-------------------------------------------------------
        Data TabStruc Type Standard Table Of TabStruc.
        Data waTabStruc Type TabStruc.
        Data i Type i Value 0.
        Data Len Type String Value ''.
        Data Dec Type String Value ''.

      Select TABNAME FIELDNAME POSITION INTTYPE DATATYPE LENG DECIMALS
        Into Corresponding Fields Of Table TabStruc From DD03L
        Where TABNAME = TableName Order By POSITION.

      SQLCmd = `CREATE TABLE ` && TableName && ` (`.

      Loop At TabStruc Into waTabStruc.
        i = i + 1.
        Len = waTabStruc-LENG.
        Call Method Me->TrimLeadingZeros Changing NumChar = Len.
        Case waTabStruc-INTTYPE.

          "-------------------------------------------------------------
          "-
          "- Convert C (Character string) or N (Character string with
          "- digits only) to SQL CHAR or SQLite TEXT
          "-
          "-------------------------------------------------------------
            When 'C' Or 'N'.
              SQLCmd = SQLCmd && ` ` && waTabStruc-FIELDNAME.
              If SQLiteSQL = False.
                SQLCmd = SQLCmd && ` CHAR(`.
              Else.
                SQLCmd = SQLCmd && ` TEXT(`.
              EndIf.
              SQLCmd = SQLCmd && Len && ')'.
              If i <> sy-dbcnt.
                SQLCmd = SQLCmd && ','.
              EndIf.

          "-------------------------------------------------------------
          "-
          "- Convert D (Date) or T (Time) to SQL NUMERIC or SQLite
          "- NUMERIC
          "-
          "-------------------------------------------------------------
            When 'D' Or 'T'.
              SQLCmd = SQLCmd && ` ` && waTabStruc-FIELDNAME.
              SQLCmd = SQLCmd && ` NUMERIC(` && Len && ')'.
              If i <> sy-dbcnt.
                SQLCmd = SQLCmd && ','.
              EndIf.

          "-------------------------------------------------------------
          "-
          "- Convert I (Integer number) to SQL NUMERIC or
          "- SQLite INTEGER
          "-
          "-------------------------------------------------------------
            When 'I'.
              SQLCmd = SQLCmd && ` ` && waTabStruc-FIELDNAME.
              If SQLiteSQL = False.
                SQLCmd = SQLCmd && ` NUMERIC(`.
              Else.
                SQLCmd = SQLCmd && ` INTEGER(`.
              EndIf.
              SQLCmd = SQLCmd && Len && ')'.
              If i <> sy-dbcnt.
                SQLCmd = SQLCmd && ','.
              EndIf.

          "-------------------------------------------------------------
          "-
          "- Convert F (Floating point number) or P (Packed number)
          "- to SQL NUMERIC or SQLite REAL
          "-
          "-------------------------------------------------------------
            When 'F' Or 'P'.
              SQLCmd = SQLCmd && ` ` && waTabStruc-FIELDNAME.
              If SQLiteSQL = False.
                SQLCmd = SQLCmd && ` DECIMAL(`.
              Else.
                SQLCmd = SQLCmd && ` REAL(`.
              EndIf.
              Dec = waTabStruc-DECIMALS.
              Call Method Me->TrimLeadingZeros
                Changing
                  NumChar = Dec.
              SQLCmd = SQLCmd && Len && ',' && Dec && ')'.
              If i <> sy-dbcnt.
                SQLCmd = SQLCmd && ','.
              EndIf.

          "-------------------------------------------------------------
          "-
          "- Convert X (Byte sequence) to SQL NUMERIC or
          "- SQLite INTEGER or SQL CHAR or SQLite TEXT
          "-
          "-------------------------------------------------------------
            When 'X'.
              SQLCmd = SQLCmd && ` ` && waTabStruc-FIELDNAME.
              If SQLiteSQL = False.
                Case waTabStruc-DATATYPE.
                  When 'INT1' Or 'INT2' Or 'INT4'.
                    SQLCmd = SQLCmd && ` NUMERIC(`.
                  When Others.
                    SQLCmd = SQLCmd && ` CHAR(`.
                EndCase.
              Else.
                Case waTabStruc-DATATYPE.
                  When  'INT1' Or 'INT2' Or 'INT4'.
                    SQLCmd = SQLCmd && ` INTEGER(`.
                  When Others.
                    SQLCmd = SQLCmd && ` TEXT(`.
                EndCase.
              EndIf.
              SQLCmd = SQLCmd && Len && ')'.
              If i <> sy-dbcnt.
                SQLCmd = SQLCmd && ','.
              EndIf.

          EndCase.

      EndLoop.

      SQLCmd = SQLCmd && ` );`.

    EndMethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSQLITE->TRIMLEADINGZEROS
* +-------------------------------------------------------------------------------------------------+
* | [<-->] NUMCHAR                        TYPE        STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
    Method TrimLeadingZeros.

      "-Function TrimLeadingZeros---------------------------------------
      "-
      "- Delete leading zeros in a string
      "-
      "- Arguments: NumChar = Numeric string
      "- Return: Numeric string without leading zeros
      "-
      "-----------------------------------------------------------------

      "-Variables-------------------------------------------------------
        Data NumCharLen Type i Value 0.
        Data NewLen Type i Value 0.
        Data Pos Type Integer Value 0.
        Data Char(1) Type c Value ''.

      NumCharLen = StrLen( NumChar ).
      Do NumCharLen Times.
        Pos = sy-index - 1.
        Move NumChar+Pos(1) To Char.
        If Char <> '0'.
          Exit.
        EndIf.
      EndDo.
      NewLen = NumCharLen - Pos.
      Move NumChar+Pos(NewLen) To NumChar.

    EndMethod.

EndClass.

You must name the table, an optional where clause, the name of the file which stores the SQL statements and two flags to control the processing.

Here an example report:

REPORT ZSQLITE.

"-Begin-----------------------------------------------------------------
"-
"- An example how to use zSQLite class to export an SAP table to an
"- SQLite database as a collection of SQL statements in a text file
"-
"- Author: Stefan Schnell
"- Date: 2017-01-14
"-
"-----------------------------------------------------------------------

  "-Variables-----------------------------------------------------------
    Data:
      SQLite Type Ref To zSQLite,
      TableName Type String Value 'SFLIGHT',
      WhereClause Type String,
      FileName Type String Value 'C:\Dummy\SFLIGHT.sql',
      SQLiteSQL Type i Value 1,
      ConfirmOverWrite Type abap_bool Value abap_true
      .

  "-Main----------------------------------------------------------------
  Create Object SQLite.

  Call Method SQLite->GetTable2SQLCmdFile
    Exporting
      TableName = TableName
      WhereClause = WhereClause
      FileName = FileName
      SQLiteSQL = SQLiteSQL
      OverWriteConfirm = ConfirmOverWrite.

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

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

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,

Cheers

Stefan

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Janu Vidya Midavelil

    Hi Stefan,

    I am not able to register the SQLite3COM.dll which I downloaded from your link. It throws the message – ‘There was a problem starting the dll. The specified module could not be found’. I am very new to this and am probably missing some step. Would be great if you can provide some pointers?

    Thanks,

    Vidya

    (0) 

Leave a Reply