Skip to Content
Technical Articles
Author's profile photo Stefan Schnell

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.

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo dehua pan
      dehua pan

      It's useful and understandable...!! 🙂

      Thanks for Sharing your knowledge..

      Regards

      dehua pan

      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Hello Dehua Pan,

      thank you very much, you are welcome.

      Cheers

      Stefan

      Author's profile photo Zdenek Pecenka
      Zdenek Pecenka

      Hello Stefan,

      The way you connect to SQLite is the same/very similar you would query an MS Access (.mdb) database (ADO via ODBC).

      Here is my problem: How can you do that in batch/background? Our SAP servers run Windows. Is it enough to have the SAP application server having access to the MDB database path? Do we need to have a MS Access application installed on each app server we need to run this on?

      Is there a way to utilize BinFile2ABAP ?

      Is there another better way to connect to the MDB?

      Sorry for sort of hijacking this thread. I would really appreciate a few pearls of your wisdom!

      Thank you,
      Zdenek

       

       

      Author's profile photo Stefan Schnell
      Stefan Schnell

      Zdenek Pecenka

      Hello Zedenek,

      thank you for your post.

      You can't use this way in background processes. The communication between ABAP and the ODBC driver is via OLE interface from the SAP GUI for Windows. Interfaces to do that are e.g. an RFC server or a web REST service which offers the functions you need.

      No, BinFile2ABAP offers no way to help you for this kind of requirement.

      In my opinion yes, but I have never tried that: Use MS SQL Server to store your data. You can link Access to this external data, you can find a good description here. It is also possible to use it via ABAP, but at first you must connect your database via TAC DBCO to your backend, you can find a good description here.

      Best regards
      Stefan

      Author's profile photo Zdenek Pecenka
      Zdenek Pecenka

      I really appreciate your suggestions!

      Prima - Vielen Dank!