SQLite implementation

[Jamal]

Joe,

Add a reference to System.Data.SQLite

then in your code add:

Code: Select all

USING System.Data.SQLite

Jamal

[ic2]

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 https://stackoverflow.com/questions/484 ... 7/55083869.

Dick

[OhioJoe]

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)

Code: Select all

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:

Code: Select all

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.

[robert]

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

Robert

[Jamal]

Joe,

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

Code: Select all

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: https://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.

Code: Select all

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

[wriedmann]

Hi Joe,
if you need to use SQLite and to distribute it, you need the following in the binaries folder
System.Data.SQLite.dll
x64SQLite.Interop.dll
x86SQLite.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

[OhioJoe]

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:

Code: Select all

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:XIDEProjectsFPSQLiteFRWStart_Start.prg:line 350
   at XAPP.StartInitProg(__Usual[] Xs$Args) in C:XIDEProjectsFPSQLiteFRWStart_Start.prg:line 127
   at XAPP.Start(__Usual[] Xs$Args) in C:XIDEProjectsFPSQLiteFRWStart_Start.prg:line 40
   at FRW.Exe.Functions.Start() in C:XIDEProjectsFPSQLiteFRWStart_Start.prg:line 7

So I added a reference to C:Program Files (x86)XSharpExtensionProjectSQLite.Interop.dll

and then got the following compiler error:

Code: Select all

error XS0009: Metadata file 'C:Program Files (x86)XSharpExtensionProjectSQLite.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.)

[wriedmann]

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

[ic2]

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 runtimeswin-x86native 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("https://go.microsoft.com/fwlink/p/?LinkId=2124703"), NULL_PSZ, NULL_PSZ, SW_SHOW) // Installation URL for Evergreen bootstapper from https://developer.microsoft.com/en-us/m ... /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