How To Use SQLite with ADO via ODBC

[Stefan-Schnell]

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.

[dehua_pan]

It's useful and understandable...!! :smile:

Thanks for Sharing your knowledge..

Regards

dehua pan

[Stefan-Schnell]

Hello Dehua Pan,

thank you very much, you are welcome.

Cheers

Stefan

[zdenekp]

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

 

 

[Stefan-Schnell]

zdenekp

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

[zdenekp]

I really appreciate your suggestions!

Prima - Vielen Dank!