Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1

TOPIC:

SQLite implementation 25 Jun 2021 19:09 #18915

  • OhioJoe
  • OhioJoe's Avatar
  • Topic Author


  • Posts: 102
  • Trying to compile in XIDE. Getting the following build error:

    error XS0009: Metadata file 'C:\XIDE\Assemblies\SQLite.Interop.dll' could not be opened -- PE image doesn't contain managed metadata.

    I'm acting upon the advice offered by Wolfgang in another post. In which he said I need to include the following:

    SQLite.Interop.dll
    System.Data.SQLite.dll

    As the error above indicates, I've copied them into the XIDE\Assemblies folder

    The ultimate goal here is to build an app that works with all SQL dialects. Buf first I'm trying to get it working in an environment that leaves the user free of having to install a separate database server. Hence the choice for SQLite.

    Any advice out there? Thank you.
    Joe Curran
    Ohio USA

    Please Log in or Create an account to join the conversation.

    SQLite implementation 25 Jun 2021 21:00 #18916

    • robert
    • robert's Avatar


  • Posts: 2371
  • Joe,
    You only need to include System.Data.SQLite as a reference. The other DLL must be copied to the EXE folder but is not a .Net DLL but a "normal" C++ DLL.

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    SQLite implementation 25 Jun 2021 21:13 #18917

    • OhioJoe
    • OhioJoe's Avatar
    • Topic Author


  • Posts: 102
  • I eliminated the reference to SQLite.Interop.dll. Now it compiles
    When I run the application, it says that I need an ODBC connection, which is what I'm trying to avoid. (To achieve the fewest possible user-setup requirements.)
    Here's my connection syntax, originally written in VO:
    oDatabase := cDBFile  // "joe.db"
    SQLConnectErrorMsg( TRUE )  
    oConnection := SQLConnection{"SQLite3", "", "" }	    
    oConnection:connect()
    
    // and then later on:
    cStatement := "ATTACH DATABASE '" + cDatabase+ "' AS joedata" 
    oStatement := SQLStatement{cStatement , oConnection}
    oStatement:Execute()
    oStatement:FreeStmt( SQL_DROP )
    

    What is the correct way to connect?
    Joe Curran
    Ohio USA

    Please Log in or Create an account to join the conversation.

    SQLite implementation 25 Jun 2021 21:46 #18918

    • robert
    • robert's Avatar


  • Posts: 2371
  • Joe,
    This code seems to use the VO compatible SqlConnection and SqlStatement.
    You will have to use the Ado.Net compatible classes:
    VAR db := SQLiteConnection{"Data Source="+cDatabase+";Version=3;"}
    db:Open()  
    USING VAR cmd := SQLiteCommand{"SELECT * from Table", oConn}
    USING VAR rdr := cmd:ExecuteReader()
    DO WHILE rdr:Read()
       ....
    ENDDO
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    SQLite implementation 25 Jun 2021 21:55 #18919

    • OhioJoe
    • OhioJoe's Avatar
    • Topic Author


  • Posts: 102
  • Robert, you also mentioned that SQLite.Interop.dll is to be placed in the EXE folder. Is there a line where this is to be referenced in the app, such as
    LoadLibrary("SQLite.Interop.dll")
    ?
    Thank you, Robert.
    Joe Curran
    Ohio USA

    Please Log in or Create an account to join the conversation.

    SQLite implementation 25 Jun 2021 22:53 #18921

    • Chris
    • Chris's Avatar


  • Posts: 2742
  • Joe, no need at all to load it in your code manually yourself. I assume this dll is used automatically from inside the code in the System.Data.SQLite.dll
    XSharp Development Team
    chris(at)xsharp.eu

    Please Log in or Create an account to join the conversation.

    SQLite implementation 26 Jun 2021 09:19 #18922

    • lumberjack
    • lumberjack's Avatar


  • Posts: 706
  • Hi Joe,

    OhioJoe wrote: The ultimate goal here is to build an app that works with all SQL dialects. Buf first I'm trying to get it working in an environment that leaves the user free of having to install a separate database server. Hence the choice for SQLite.
    Any advice out there? Thank you.

    If you want to save yourself a lot of effort you need to go the DbProviderFactories route. Here is some code that should get you on track:
        STATIC METHOD GetConnection(sDb AS STRING) AS DbConnection
            LOCAL cs AS ConnectionStringSettings
            LOCAL oConn AS DbConnection
            cs := ConfigurationManager.ConnectionStrings[sDb]
            oConn := DbProviderFactories.GetFactory(cs:ProviderName):CreateConnection()
            oConn:ConnectionString := cs:ConnectionString
        RETURN oConn
    
        STATIC METHOD GetData(sDb AS STRING, sComm AS STRING) AS STRING
            LOCAL obj AS OBJECT
            LOCAL oConn AS DbConnection
            LOCAL oComm AS DbCommand
            LOCAL oTran AS DbTransaction
            LOCAL oRead AS DbDataReader
            LOCAL ret, sDel AS STRING
            oConn := PsionUtils.GetConnection(sDb)
            oConn:Open()
            oComm := DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings[sDb]:ProviderName):CreateCommand()
            oComm:Connection := oConn
            oComm:CommandText := sComm
            oTran := oConn:BeginTransaction()
            oComm:Transaction := oTran
            oRead := oComm:ExecuteReader()
            ret := ""
    
            WHILE oRead:Read()
                IF ret:Length > 0
                    ret += ";"
                ENDIF
                sDel := ""
                FOR LOCAL col := 0 AS INT UPTO oRead:FieldCount -1
                    ret += sDel + oRead:GetValue(col):ToString()
                    sDel := ","
                NEXT
            ENDDO
            oRead:Close()
            oTran:Commit()
            oConn:Close()
        RETURN ret
    
        STATIC METHOD DataRead(sDB AS STRING, sComm AS STRING) AS DataTable
            LOCAL oConn AS DbConnection
            LOCAL oComm AS DbCommand
            LOCAL oTran AS DbTransaction
            LOCAL oRead AS DbDataReader
            LOCAL oDT AS DataTable
            oConn := PsionUtils.GetConnection(sDB)
            oConn:Open()
            oComm := DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings[sDB]:ProviderName):CreateCommand()
            oComm:Connection := oConn
            oComm:CommandText := sComm
            oTran := oConn:BeginTransaction()
            oComm:Transaction := oTran
            oRead := oComm:ExecuteReader()
            oDT := DataTable{}
            oDT:Load(oRead)
            oConn:Close()
            oConn:Dispose()
        RETURN oDT
    
        STATIC METHOD PutData(sDb AS STRING, sComm AS STRING) AS INT
            LOCAL oConn AS DbConnection
            LOCAL oComm AS DbCommand
            LOCAL oTran    AS DbTransaction
            LOCAL ret := 0 AS INT
            LOCAL stmt AS STRING[]
            oConn := PsionUtils.GetConnection(sDb)
            oComm := DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings[sDb]:ProviderName):CreateCommand()
            oComm:Connection := oConn
            oConn:Open()
            oTran := oConn:BeginTransaction()
            oComm:Transaction := oTran
            stmt := sComm:Split(";":ToCharArray(), StringSplitOptions.RemoveEmptyEntries)
            FOREACH s AS STRING IN stmt
                oComm:CommandText := s
                ret += oComm:ExecuteNonQuery()
            NEXT
            oTran:Commit()
            oTran:Dispose()
            oComm:Dispose()
            oConn:Close()
            oConn:Dispose()
        RETURN ret
    END CLASS
    
    **ApplicationName.exe.config
    
    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <system.data>
            <DbProviderFactories>
                <add name="Npgsql Data Provider" invariant="Npgsql" description=".Net Framework Data Provider for Postgresql" type="Npgsql.NpgsqlFactory, Npgsql" />
                <!--    /-->
            </DbProviderFactories>
        </system.data>
        <connectionStrings>
            <add name="logtrack" providerName="System.Data.OleDb" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\VIDE\Projects\visualSystem\Applications\psion\logtrackssc.mdb;"/>
            <add name="ddframework"  providerName="Npgsql" connectionString="Server=127.0.0.1;Port=5432;Database=visualframe;User Id=postgres;password=************" />
        </connectionStrings>
    </configuration>

    Let me know if there is something you don't understand.

    HTH
    ______________________
    Johan Nel
    George, South Africa

    Please Log in or Create an account to join the conversation.

    SQLite implementation 26 Jun 2021 14:46 #18923

    • OhioJoe
    • OhioJoe's Avatar
    • Topic Author


  • Posts: 102
  • Thank you. Yes, this fits!
    Just started with the reading about ADO.Net
    Included there is a section about DBProviderFactories
    Hopefully my experience here will help those who've been stuck in the DBF world, who want to create a version of their feature-rich application to an expanded user base, but who don't know where to start.
    Joe Curran
    Ohio USA

    Please Log in or Create an account to join the conversation.

    SQLite implementation 26 Jun 2021 14:51 #18924

    • lumberjack
    • lumberjack's Avatar


  • Posts: 706
  • Hi Joe,

    OhioJoe wrote: Thank you. Yes, this fits!
    Just started with the reading about ADO.Net
    Included there is a section about DBProviderFactories
    Hopefully my experience here will help those who've been stuck in the DBF world, who want to create a version of their feature-rich application to an expanded user base, but who don't know where to start.


    Feel free to contact me if you need any assistance.

    Regards
    ______________________
    Johan Nel
    George, South Africa

    Please Log in or Create an account to join the conversation.

    SQLite implementation 16 Sep 2021 22:42 #19617

    • OhioJoe
    • OhioJoe's Avatar
    • Topic Author


  • Posts: 102
  • Robert / Johan:
    (Just now getting back to this. Sorry for the delay.)
    What assemblies / references are required to implement your code?
    When I tried Robert's sample ...
    VAR db := SQLiteConnection{"Data Source=c:\fr\fptest.db;Version=3;"}
    db:Open()  
    USING VAR cmd := SQLiteCommand{"SELECT * from Table", db}
    USING VAR rdr := cmd:ExecuteReader()
    WHILE rdr:Read()
    ENDDO          
    ... I got the following error:
    error XS0246: The type or namespace name 'SQLiteConnection' could not be found (are you missing a using directive or an assembly reference?)
    Thank you for your help
    Joe Curran
    Ohio USA

    Please Log in or Create an account to join the conversation.

    SQLite implementation 17 Sep 2021 03:35 #19618

    • Jamal


  • Posts: 254
  • Joe,

    Add a reference to System.Data.SQLite

    then in your code add:
    USING System.Data.SQLite

    Jamal

    Please Log in or Create an account to join the conversation.

    SQLite implementation 17 Sep 2021 13:23 #19619

    • ic2


  • Posts: 1122
  • Hello Joe,

    In addition to what Jamal wrote: I think you need to install an SQLite first (note: with 1 'L'). I used it for a never finished Windows modern app and found SQLite.Net PCL . There's also a Microsoft.Data.Sqlite on NuGet.

    You can obtain them via Via Tools/NuGet Package Manager, Manage NuGet Packages for Solution; select Browse at the top, and search for SQLite.Net PCL or Microsoft.Data.Sqlite. Then add the reference as Jamal writes.

    You might want or need to read stackoverflow.com/questions/48439865/how...studio-2017/55083869 .

    Dick

    Please Log in or Create an account to join the conversation.

    SQLite implementation 17 Sep 2021 14:44 #19620

    • OhioJoe
    • OhioJoe's Avatar
    • Topic Author


  • Posts: 102
  • Thank you guys.
    (Dick, I've checked my SQLite installation and it seems OK)
    I took Robert's code and pasted it into a function. Added the USING statement. (Already had the reference to System.Data.SQLite)
    FUNCTION XTester( nChoice AS INT, cDB AS STRING ) AS VOID STRICT 
    	USING System.Data.SQLite 
    	VAR db := SQLiteConnection{"Data Source=" + cDB + ";Version=3;"} 
    	db:Open()  
    	USING VAR cmd := SQLiteCommand{"SELECT * from Master", db}
    	USING VAR rdr := cmd:ExecuteReader()
    	WHILE rdr:Read()
    	ENDDO          
    
    	RETURN
    Now I get this XIDE error:
    error XS9002: Parser: unexpected input 'VAR'	350,2	Start_Start.prg	XTester
    I think it has something to do with the placement of the USING statement.
    Anyone? Thank you.
    Joe Curran
    Ohio USA

    Please Log in or Create an account to join the conversation.

    SQLite implementation 17 Sep 2021 14:45 #19621

    • robert
    • robert's Avatar


  • Posts: 2371
  • Joe,
    Usings should not be inside a function/method body but at the start of the page.

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    SQLite implementation 17 Sep 2021 14:55 #19622

    • Jamal


  • Posts: 254
  • Joe,

    The USING statement should on top and outside of your methods:


    USING System.Data.SQLite 
    
    FUNCTION XTester( nChoice AS INT, cDB AS STRING ) AS VOID STRICT 
    	
    	VAR db := SQLiteConnection{"Data Source=" + cDB + ";Version=3;"} 
    	db:Open()  
    	USING VAR cmd := SQLiteCommand{"SELECT * from Master", db}
    	USING VAR rdr := cmd:ExecuteReader()
    	WHILE rdr:Read()
    	ENDDO          
    
    	RETURN

    FYI: www.xsharp.eu/help/using.html

    Also, I think that the USING statements inside the function that are used with variables should be written as:

    BEGIN USING .. END USING

    so that variables are disposed of properly.
    FUNCTION XTester( nChoice AS INT, cDB AS STRING ) AS VOID STRICT 
    	
    	BEGIN USING VAR db := SQLiteConnection{"Data Source=" + cDB + ";Version=3;"} 
    	    db:Open()  
    	    BEGIN USING VAR cmd := SQLiteCommand{"SELECT * from Master", db}
    	         BEGIN USING VAR rdr := cmd:ExecuteReader()
    	                WHILE rdr:Read()
    	                ENDDO          
                     END USING
                END USING
            END USING
    
    RETURN


    Jamal

    Please Log in or Create an account to join the conversation.

    Last edit: by Jamal.

    SQLite implementation 17 Sep 2021 15:10 #19623

    • wriedmann
    • wriedmann's Avatar


  • Posts: 2785
  • Hi Joe,
    if you need to use SQLite and to distribute it, you need the following in the binaries folder
    System.Data.SQLite.dll
    x64\SQLite.Interop.dll
    x86\SQLite.Interop.dll

    That is all. If your application is running in 32 bit mode, the interop is loaded from the x86 folder, otherwise from the x64 folder.

    You don't need any installation. Microsoft calls this "xcopy installation".

    Wolfgang
    P.S. since I use XIDE, I have to do some things manually
    Wolfgang Riedmann
    Meran, South Tyrol, Italy

    www.riedmann.it - docs.xsharp.it

    Please Log in or Create an account to join the conversation.

    SQLite implementation 17 Sep 2021 16:06 #19624

    • OhioJoe
    • OhioJoe's Avatar
    • Topic Author


  • Posts: 102
  • Thank you, Wolfgang. It turns out your comment could be very well-timed because the problem below might have something to do with 32 vs 64. (I think I'm in 32-bit mode but I can't find where to check.)

    It compiled. Then I got the following runtime error:
    System.DllNotFoundException: Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
       at System.Data.SQLite.UnsafeNativeMethods.sqlite3_config_none(SQLiteConfigOpsEnum op)
       at System.Data.SQLite.SQLite3.StaticIsInitialized()
       at System.Data.SQLite.SQLiteLog.Initialize(String className)
       at System.Data.SQLite.SQLiteConnection..ctor(String connectionString, Boolean parseViaFramework)
       at System.Data.SQLite.SQLiteConnection..ctor(String connectionString)
       at FRW.Exe.Functions.XTester(Int32 nChoice, String cDB) in C:\XIDE\Projects\FPSQLite\FRW\Start_Start.prg:line 350
       at XAPP.StartInitProg(__Usual[] Xs$Args) in C:\XIDE\Projects\FPSQLite\FRW\Start_Start.prg:line 127
       at XAPP.Start(__Usual[] Xs$Args) in C:\XIDE\Projects\FPSQLite\FRW\Start_Start.prg:line 40
       at FRW.Exe.Functions.Start() in C:\XIDE\Projects\FPSQLite\FRW\Start_Start.prg:line 7

    So I added a reference to C:\Program Files (x86)\XSharp\Extension\Project\SQLite.Interop.dll

    and then got the following compiler error:
    error XS0009: Metadata file 'C:\Program Files (x86)\XSharp\Extension\Project\SQLite.Interop.dll' could not be opened -- PE image doesn't contain managed metadata.

    Adding the reference left a copy of SQLite.interop.dll in my DEBUG folder. So I REMOVED the reference to SQLite.Interop.DLL ...

    ... and then it compiled and ran. With the copy of SQLite.interop.dll in the DEBUG folder but without the reference.

    ??

    My goal here is to follow up on the work I've done previously to convert a large VO app from DBF to SQLite and show everyone else that it's not so hard. (And it isn't. Once I can get these quirks worked out. Thank you for your help.)
    Joe Curran
    Ohio USA

    Please Log in or Create an account to join the conversation.

    SQLite implementation 17 Sep 2021 16:33 #19625

    • wriedmann
    • wriedmann's Avatar


  • Posts: 2785
  • Hi Joe,
    SQLite.Interop.DLL is not a .NET DLL, so you cannot add that to the references.
    You have to copy both folders (x64 and x86) to your binaries folder, and the System.Data.SQLite.DLL will load the correct when needed.
    This is not a thing that only SQLite does, CEFSharp does the same (it is a embedded Chrome browser I've played with), and also Microsoft's WebView2 control (an embedded Edge webbrowser control).
    Wolfgang
    Wolfgang Riedmann
    Meran, South Tyrol, Italy

    www.riedmann.it - docs.xsharp.it

    Please Log in or Create an account to join the conversation.

    SQLite implementation 18 Sep 2021 08:59 #19626

    • ic2


  • Posts: 1122
  • Adding to Wolfgang's comment, for future readers, who would want to work with WebVIew2 (as we do):

    You need to have a subdirectory installed in your program directory called runtimes\win-x86\native with WebView2Loader.dll in it + a couple of DLL's (Microsoft.Web.WebView2.Wpf.dll , Microsoft.Web.WebView2.Core.dll, Microsoft.Web.WebView2.Core.xml, and Microsoft.Web.WebView2.Wpf.xml) .And the WebView2 must be installed, in VO we have this line of code:

    ShellExecute(NULL, String2Psz("open"), String2Psz(" go.microsoft.com/fwlink/p/?LinkId=2124703 "), NULL_PSZ, NULL_PSZ, SW_SHOW) // Installation URL for Evergreen bootstapper from developer.microsoft.com/en-us/microsoft-edge/webview2/

    The WebView2 is used from our .Net library for VO, a mix of C# and X# code.

    I am not sure if the last step is necessary in WIndows 10, since March Microsoft auto installs WebvView2 with WIndows 10 since Office 365 also relies on it. Originally it would only be be auto installed if you run Office 365 2101 or later.

    Dick

    Please Log in or Create an account to join the conversation.

    • Page:
    • 1