How To Use SQLite with ADO via ODBC

One year ago I presented here the possibility how to export SAP table content as SQL descriptions to import it to SQLite containers. Here a complementation to use the full bandwith with ActiveX Data Objects (ADO) and Open Database Connectivity (ODBC).

To use SQLite via ODBC you must install a special driver. You can find a very good one here, it is free and released under BSD-type license. Now you can use SQLite databases with ABAP.

Here an example how to create and use an SQLite database:

"-Begin-----------------------------------------------------------------
Report ZODBC_SQLITE.

    Constants adUseClient Type i Value 3.

    Data:
      oCon Type OLE2_OBJECT,
      oRecSet Type OLE2_OBJECT,
      oFields Type OLE2_OBJECT,
      oField Type OLE2_OBJECT,
      cntRec Type i,
      cntFields Type i,
      sSQL Type String,
      i Type i,
      j Type i,
      nameField Type String,
      valueField Type String.

    Create Object oCon 'ADODB.Connection'.
    If sy-subrc <> 0 Or  oCon-Handle <= 0 Or oCon-Type <> 'OLE2'.
      Exit.
    EndIf.

    Set Property Of oCon 'CursorLocation' = adUseClient.

    Call Method Of oCon 'Open' Exporting
      #1 = 'DRIVER=SQLite3 ODBC Driver;Database=C:\Dummy\MyDb.db3;'.

    sSQL = 'DROP TABLE tblTest'.
    Call Method Of oCon 'Execute' Exporting #1 = sSQL.

    sSQL = 'CREATE TABLE tblTest(ID INTEGER PRIMARY KEY, NAME VARCHAR(40))'.
    Call Method Of oCon 'Execute' Exporting #1 = sSQL.

    i = 1.
    While i <= 16.
      sSQL = 'INSERT INTO tblTest VALUES(' && i && ', ''Name' && i && ''')'.
      Call Method Of oCon 'Execute' Exporting #1 = sSQL.
      i = i + 1.
    EndWhile.

    sSQL = 'SELECT * FROM tblTest'.
    Call Method Of oCon 'Execute' = oRecSet Exporting #1 = sSQL.

    Get Property Of oRecSet 'RecordCount' = cntRec.
    i = 1.
    While i <= cntRec.
      Get Property Of oRecSet 'Fields' = oFields.
      Get Property Of oFields 'Count' = cntFields.
      j = 0.
      While j <= cntFields - 1.
        Get Property Of oFields 'Item' = oField Exporting #1 = j.
        Get Property Of oField 'Name' = nameField.
        Get Property Of oField 'Value' = valueField.
        Write: / nameField, ` `, valueField.
        j = j + 1.
      EndWhile.
      Call Method Of oRecSet 'MoveNext'.
      i = i + 1.
    EndWhile.
    Call Method Of oCon 'Close'.
    Free Object oCon.

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

It is not necessary to build another library, with the existing possibilities like ADO and ODBC you can connect and use many different databases of different manufacturers with ABAP.