How to Copy Data from SAP Table into SQLite DataBase

At the UiPath DevCon I had a very interesting discussion in the AMER room about the possibility to copy data from an SAP table directly into an SQLite database. The advantage of such an approach is that the data model of the SQLite database prevents inconsistent data. Furthermore, the standard query language SQL can be used. Based on my post about building your own SQLite package, here two Invoke Code Activity routines that can be used to transfer tables directly from SAP into an SQLite database.

Copy Data from SAP Table into SQLite as TEXT

The first C# invoke code routine copies the SAP table SFLIGHT into an SQLite database with the same name. To copy the data the routine uses the Remote Enabled Function Module (RFM) RFC_READ_TABLE. Data types are not considered, all field contents are transferred to the database as TEXT. The input parameters are at the begin and the output parameters at the end of the source code.

    string AppServHost = "YourHost";
    string Client = "099";
    string User = "YourName";
    string Password = "secret";
    string SQLiteDbName = "SFLIGHT";
    string SAPTableName = "SFLIGHT";
    string SAPFieldNames = string.Empty;
    string SAPWhereClause = string.Empty;
    string SAPDelimiter = "~";
    string ErrorReturn = string.Empty;

//-UiPath Code Begin----------------------------------------------------

RfcConfigParameters cfgParams = new RfcConfigParameters();
cfgParams.Add("NAME", "Test");
cfgParams.Add("ASHOST", AppServHost);
//cfgParams.Add("SYSNR", "");
//cfgParams.Add("SAPROUTER", "");
//cfgParams.Add("MESSAGESERVERHOST", "");
//cfgParams.Add("LOGONGROUP", "");
//cfgParams.Add("SYSTEMID", "");
cfgParams.Add("CLIENT", Client);
cfgParams.Add("USER", User);
cfgParams.Add("PASSWD", Password);
cfgParams.Add("LANGUAGE", "EN");
//cfgParams.Add("ABAPDEBUG", "1");

try {

  RfcDestination destination = RfcDestinationManager.GetDestination(cfgParams);
  IRfcFunction rfcFunction = destination.Repository.CreateFunction("RFC_READ_TABLE");

  //-Tables-------------------------------------------------------------
  IRfcTable tblDATA = rfcFunction.GetTable("DATA");
  IRfcTable tblFIELDS = rfcFunction.GetTable("FIELDS");
  IRfcTable tblOPTIONS = rfcFunction.GetTable("OPTIONS");

  //-Import-------------------------------------------------------------
  rfcFunction.SetValue("QUERY_TABLE", SAPTableName);
  rfcFunction.SetValue("DELIMITER", SAPDelimiter);
  //rfcFunction.SetValue("NO_DATA", "");
  //rfcFunction.SetValue("ROWCOUNT", 0);
  //rfcFunction.SetValue("ROWSKIPS", 0);

  //-FIELDS-------------------------------------------------------------
  if(!string.IsNullOrEmpty(SAPFieldNames)) {
    string[] arrSAPFieldNames = SAPFieldNames.Split(',');
    foreach(string SAPFieldName in arrSAPFieldNames) {
      tblFIELDS.Append();
      tblFIELDS.SetValue("FIELDNAME", SAPFieldName.Trim());
    }
  }

  //-OPTIONS------------------------------------------------------------
  if(!string.IsNullOrEmpty(SAPWhereClause)) {
    //tblOPTIONS.Append();
    //tblOPTIONS.SetValue("TEXT", "");
  }

  //-Invoke-------------------------------------------------------------
  rfcFunction.Invoke(destination);

  //-Create database----------------------------------------------------
  string SQLiteConnStr = @"Data Source=" + SQLiteDbName + ".sqlite;Version=3;";
  SQLiteConnection SQLiteConn = new SQLiteConnection(SQLiteConnStr);
  SQLiteConn.Open();

  SQLiteCommand SQLiteCmd = new SQLiteCommand(SQLiteConn);

  //-Get all field names to create the table----------------------------
  string Fields = string.Empty;
  foreach(IRfcStructure Record in tblFIELDS) {
    Fields += Record.GetString("FIELDNAME") + " TEXT, ";
  }
  Fields = Fields.Remove(Fields.Length - 2);

  //-Create table-------------------------------------------------------
  SQLiteCmd.CommandText = @"CREATE TABLE " + SAPTableName + "(" + Fields + ");";
  SQLiteCmd.ExecuteNonQuery();

  //-Get all field names to add the data--------------------------------
  Fields = string.Empty;
  foreach(IRfcStructure Record in tblFIELDS) {
    Fields += Record.GetString("FIELDNAME") + ", ";
  }
  Fields = Fields.Remove(Fields.Length - 2);

  //-Add data-----------------------------------------------------------
  foreach(IRfcStructure Record in tblDATA) {
    string strRecord = string.Empty;
    string[] arrWA = Record.GetString("WA").Split('~');
    foreach(string strWA in arrWA) {
      strRecord += "'" + strWA.Trim() + "', ";
    }
    strRecord = strRecord.Remove(strRecord.Length - 2);
    SQLiteCmd.CommandText = "INSERT INTO " + SAPTableName + "(" + Fields + 
      ") VALUES(" + strRecord + ")";
    SQLiteCmd.ExecuteNonQuery();
  }

  SQLiteConn.Close();

} catch(Exception ex) {
  ErrorReturn = ex.Message;
}

//-UiPath Code End------------------------------------------------------

    Console.WriteLine(ErrorReturn);

Here the result in the SQLite database.

Copy Data from SAP Table into SQLite with its Data Type

The second C# invoke code routine copies the SAP table SFLIGHT into an SQLite database on the same way as the first but data types are considered in this case.

    string AppServHost = "YourHost";
    string Client = "099";
    string User = "YourName";
    string Password = "secret";
    string SQLiteDbName = "SFLIGHT";
    bool SQLiteCreateTable = true;
    string SAPTableName = "SFLIGHT";
    //string SAPTableName = "SAIRPORT";
    //string SAPTableName = "SCARR";
    //string SAPTableName = "SBOOK";
    //string SAPTableName = "DD06L";
    string SAPFieldNames = string.Empty;
    string SAPWhereClause = string.Empty;
    string SAPDelimiter = "~";
    string ErrorReturn = string.Empty;

//-UiPath Code Begin----------------------------------------------------

RfcConfigParameters cfgParams = new RfcConfigParameters();
cfgParams.Add("NAME", "Test");
cfgParams.Add("ASHOST", AppServHost);
//cfgParams.Add("SYSNR", "");
//cfgParams.Add("SAPROUTER", "");
//cfgParams.Add("MESSAGESERVERHOST", "");
//cfgParams.Add("LOGONGROUP", "");
//cfgParams.Add("SYSTEMID", "");
cfgParams.Add("CLIENT", Client);
cfgParams.Add("USER", User);
cfgParams.Add("PASSWD", Password);
cfgParams.Add("LANGUAGE", "EN");
//cfgParams.Add("ABAPDEBUG", "1");

try {

  RfcDestination destination = RfcDestinationManager.GetDestination(cfgParams);
  IRfcFunction rfcFunction = destination.Repository.CreateFunction("RFC_READ_TABLE");

  //-Tables-------------------------------------------------------------
  IRfcTable tblDATA = rfcFunction.GetTable("DATA");
  IRfcTable tblFIELDS = rfcFunction.GetTable("FIELDS");
  IRfcTable tblOPTIONS = rfcFunction.GetTable("OPTIONS");

  //-Import-------------------------------------------------------------
  rfcFunction.SetValue("QUERY_TABLE", SAPTableName);
  rfcFunction.SetValue("DELIMITER", SAPDelimiter);
  //rfcFunction.SetValue("NO_DATA", "");
  //rfcFunction.SetValue("ROWCOUNT", 0);
  //rfcFunction.SetValue("ROWSKIPS", 0);

  //-FIELDS-------------------------------------------------------------
  if(!string.IsNullOrEmpty(SAPFieldNames)) {
    string[] arrSAPFieldNames = SAPFieldNames.Split(',');
    foreach(string SAPFieldName in arrSAPFieldNames) {
      tblFIELDS.Append();
      tblFIELDS.SetValue("FIELDNAME", SAPFieldName.Trim());
    }
  }

  //-OPTIONS (max. 72 characters per line)------------------------------
  if(!string.IsNullOrEmpty(SAPWhereClause)) {
    //tblOPTIONS.Append();
    //tblOPTIONS.SetValue("TEXT", "");
  }

  //-Invoke-------------------------------------------------------------
  rfcFunction.Invoke(destination);

  //-Create database----------------------------------------------------
  string SQLiteConnStr = @"Data Source=" + SQLiteDbName + ".sqlite;Version=3;";
  SQLiteConnection SQLiteConn = new SQLiteConnection(SQLiteConnStr);
  SQLiteConn.Open();

  SQLiteCommand SQLiteCmd = new SQLiteCommand(SQLiteConn);

  string Fields = string.Empty;

  if(SQLiteCreateTable == true) {

    //-Get all field names to create the table--------------------------
    foreach(IRfcStructure Record in tblFIELDS) {
      string SAPFieldType = Record.GetString("TYPE");
      switch(SAPFieldType) {
        case "C" : case "N" : case "D" : case "T" : case "g" :
          //C = Character String
          //N = Character String with Digits Only
          //D = Date (Date: YYYYMMDD)
          //T = Time (Time: HHMMSS)
          //g = Character string with variable length (ABAP type STRING)
          Fields += Record.GetString("FIELDNAME") + " TEXT, ";
          break;
        case "P" : case "F" : case "a" : case "e" :
          //P = Packed number
          //F = Floating point number to accuracy of 8 bytes
          //a = Decimal Floating Point Number, 16 Digits
          //e = Decimal Floating Point Number, 34 Digits
          Fields += Record.GetString("FIELDNAME") + " REAL, ";
          break;
        case "I" : case "b" : case "s" :
          //I = Integer number (4-byte integer with sign)
          //s = 2-byte integer, only for length field before LCHR or LRAW
          //b = 1-byte integer, integer number <= 254
          Fields += Record.GetString("FIELDNAME") + " INTEGER, ";
          break;
        default :
          //X = Byte Sequence (heXadecimal)
          //h = Table type
          //l = Reference to data object
          //r = Reference to class/interface
          //u = Structured type, flat
          //v = Structured type, deep
          //Hints:
          //Seems that RFC_READ_TABLE does not work with the data type y
          //y = Byte sequence with variable length (ABAP type XSTRING)
          //Seems meaningless:
          //V = Character string (old Dictionary type VARC)
          //j = Static Boxed Components
          //k = Generic Boxed Components
          Fields += Record.GetString("FIELDNAME") + " BLOB, ";
          break;
      }
    }
    Fields = Fields.Remove(Fields.Length - 2);

    //-Create table-----------------------------------------------------
    SQLiteCmd.CommandText = @"CREATE TABLE " + SAPTableName + "(" + 
      Fields + ");";
    SQLiteCmd.ExecuteNonQuery();

  }

  //-Get all field names to add the data--------------------------------
  Fields = string.Empty;
  foreach(IRfcStructure Record in tblFIELDS) {
    Fields += Record.GetString("FIELDNAME") + ", ";
  }
  Fields = Fields.Remove(Fields.Length - 2);

  //-Add data-----------------------------------------------------------
  foreach(IRfcStructure Record in tblDATA) {
    string strRecord = string.Empty;
    string[] arrWA = Record.GetString("WA").Split('~');
    foreach(string strWA in arrWA) {
      strRecord += "'" + strWA.Trim() + "', ";
    }
    strRecord = strRecord.Remove(strRecord.Length - 2);
    SQLiteCmd.CommandText = "INSERT INTO " + SAPTableName + "(" + 
      Fields + ") VALUES(" + strRecord + ")";
    SQLiteCmd.ExecuteNonQuery();
  }

  SQLiteConn.Close();

} catch(Exception ex) {
  ErrorReturn = ex.Message;
}

//-UiPath Code End------------------------------------------------------

    Console.WriteLine(ErrorReturn);

Here the result in the SQLite database. The difference here is now, that besides TEXT, also INTEGER and REAL are used as data types.

Conclusion

The direct transfer of SAP table data into a SQLite database with UiPath is easily possible. Of course, the limits of the RFC_READ_TABLE function module must be considered. This approach is also certainly unsuitable for large amounts of data, because the performance is rather poor. However, for smaller data sets, this may well be a valid approach. And all the advantages of a relational database system are offered.

3 Likes