Technical Articles
How To Export SAP Table Content to SQLite Data Containers
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,
Be the first to leave a comment
You must be Logged on to comment or reply to a post.