SQLite

[JTaylor]

In case it interests anyone.   Here is an example using SQLite .NET without having to load anything on your system regarding SQLite packages.   I am using the "System.Data.SQLite.dll"  found in the "sqlite-netFx46-static-binary-bundle-Win32-2015-1.0.112.0.zip" at

         https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

It seems to be the only file needed from the zip file.

Documentation can be found at:

    https://docs.microsoft.com/en-us/dotnet/api/microsoft.data.sqlite.sqlitedatareader.getordinal?view=msdata-sqlite-3.1.0

Not as nice as some approaches such as the ODBC driver but simple, only one file needed and no registrations.

Jim

[stanl]

Nice. Looks like a potential project to move persisted SQLite data into ReportView using a reader/dataset rather than an ODBC recordset.


EDIT: Unfortunately, download the package, copied the .dll to a folder where I could run our script and it failed. See attached

[JTaylor]

Yeah...one of the thoughts I had as well.

Interesting how you and I get different results from these things.  I even tried this on my wife's computer before posting to make sure nothing I had on mine would be helping it along.   What is the date and size on the DLL?

Jim

[stanl]

My bad. Ran it in 32-bit Studio. Tried 64-bit but same error. Put it in GAC and useany gives same error.


Oh:  Size of .dll is1,601 kb dated 10/27/2019

[stanl]

Methinks the SQLite dll is versioned with .NET. Instead of the dll extracted from
[size=0px]         https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki


I found it was included with my VS Community 2019. So I replaced the dll and can create the object but it wants SQLite.Interop.dll as a dependency.  This was a 32-bit version. [/size]

[JTaylor]

Mine is 1,423kb but same date.

I tested on my wife's computer because I didn't think she had anything related but turns out she has 4 different applications installed which use it.   Not sure if they do any registration of it or not.

Up to you but don't waste your time on my account.  Still hoping to get an Extender put together.

jim

[stanl]

Finally got it straightened out. Did Notice there was an System.Data.SQLite.SQLiteDataAdapter class and when combined with a system.dataset can be formed for ReportView (just haven't tried it, but Deana had posted code in Tech DB as a start). I attached a sample of zipcodes as SQLite db if anyone cares to test the attached.

Code (WINBATCH) Select Expand



db = DirScript():"zips.db"
If ! FileExist(db) Then Terminate(@TRUE,"Cannot Continue",db:" is missing")


ObjectClrOption("Appbase", DirScript())
ObjectClrOption('use','System.Data.SQLite')
dbObj = ObjectClrNew("System.Data.SQLite.SQLiteConnection","Data Source=%db%;Version=3;Pooling=true;Legacy Format=True")
dbObj.Open
cmdObj = ObjectClrNew("System.Data.SQLite.SQLiteCommand",dbObj)
rdrObj = ObjectClrNew("System.Data.SQLite.SQLiteDataReader")


SQLText = "Select DISTINCT City from NC;"
cmdObj.CommandText = SQLText
rdr = cmdObj.ExecuteReader();
n=0
While (rdr.Read()) ; Read() returns true if there is still a result line to read


    ccity         = rdr.GetValue(rdr.GetOrdinal("City"));
    Message("City",ccity)
    n=n+1
    If n>10 Then Break
EndWhile


ObjectClose(cmdObj)
ObjectClose(dbObj)


Exit


[JTaylor]

Works for me.

Jim

[stanl]

I threw this quickie together. Had issues with the SQLite adapter object, but found you could load a data table directly from reader. The resultant ReportView doesn't have headers, but just more a proof of concept. Uses same data I uploaded in previous post

Code (WINBATCH) Select Expand



db = DirScript():"zips.db"
If ! FileExist(db) Then Terminate(@TRUE,"Cannot Continue",db:" is missing")


ObjectClrOption("Appbase", DirScript())
ObjectClrOption('use','System.Data.SQLite')
ObjectClrOption("useany", "System")
ObjectClrOption("useany", "System.Data")


oTable = ObjectClrNew( 'System.Data.DataTable')
oAdapter = ObjectClrNew('System.Data.SQLite.SQLiteDataAdapter')
dbObj = ObjectClrNew("System.Data.SQLite.SQLiteConnection","Data Source=%db%;Version=3;Pooling=true;Legacy Format=True")
dbObj.Open
SQLText = "Select * from NC;"
cmdObj = ObjectClrNew("System.Data.SQLite.SQLiteCommand",dbObj)
cmdObj.CommandText = SQLText
rdrObj = ObjectClrNew("System.Data.SQLite.SQLiteDataReader")
rdr = cmdObj.ExecuteReader()


oTable.Load(rdr)
rowcount = oTable.Rows.Count
colcount = oTable.Columns.Count


arrData = ArrDimension(rowcount,colcount)
For _row = 0 to rowcount-1
   objRow = oTable.Rows.Item(_row)
   For _col = 0 to colcount-1
     objColumn = oTable.Columns.Item(_col)
     arrData[_row,_col] = objRow.Item(objColumn) 
   Next
Next


ObjectClose(cmdObj)
ObjectClose(dbObj)
ObjectClose(rdrObj)
ObjectClose(oTable)


MyDialogFormat=`WWWDLGED,6.2`


MyDialogCaption=`Sample SQLite Data`
MyDialogX=9999
MyDialogY=9999
MyDialogWidth=560
MyDialogHeight=102
MyDialogNumControls=002
MyDialogProcedure=``
MyDialogFont=`Microsoft Sans Serif|5632|70|34`
MyDialogTextColor=`DEFAULT`
MyDialogBackground=`DEFAULT,255|255|255`
MyDialogConfig=0


MyDialog001=`219,075,090,012,PUSHBUTTON,"PushButton_OK",DEFAULT,"OK",1,20,@csDefButton,DEFAULT,DEFAULT,"128|255|0"`
MyDialog002=`007,005,540,062,REPORTVIEW,"ReportView_1",arrData,DEFAULT,DEFAULT,10,@csAsort|@csFirstHeader|@csFullSel|@csGrid|@csSingleSel,"Microsoft Sans Serif|6144|70|34","255|255|0","0|0|0"`


ButtonPushed=Dialog("MyDialog")


Exit


[JTaylor]

Here are the columns...

Code (winbatch) Select Expand




db = DirScript():"zips.db"
If ! FileExist(db) Then Terminate(@TRUE,"Cannot Continue",db:" is missing")


ObjectClrOption("Appbase", DirScript())
ObjectClrOption('use','System.Data.SQLite')
ObjectClrOption("useany", "System")
ObjectClrOption("useany", "System.Data")


oTable = ObjectClrNew( 'System.Data.DataTable')
oAdapter = ObjectClrNew('System.Data.SQLite.SQLiteDataAdapter')
dbObj = ObjectClrNew("System.Data.SQLite.SQLiteConnection","Data Source=%db%;Version=3;Pooling=true;Legacy Format=True")
dbObj.Open
SQLText = "Select * from NC;"
cmdObj = ObjectClrNew("System.Data.SQLite.SQLiteCommand",dbObj)
cmdObj.CommandText = SQLText
rdrObj = ObjectClrNew("System.Data.SQLite.SQLiteDataReader")
rdr = cmdObj.ExecuteReader()


oTable.Load(rdr)
rowcount = oTable.Rows.Count
colcount = oTable.Columns.Count


arrData = ArrDimension(rowcount+1,colcount)
For _col = 0 to colcount-1
  arrData[0,_col] = oTable.Columns.Item(_col).ColumnName
Next

For _row = 0 to rowcount-1
   objRow = oTable.Rows.Item(_row)
   For _col = 0 to colcount-1
     objColumn = oTable.Columns.Item(_col)
     arrData[_row+1,_col] = objRow.Item(objColumn) 
   Next
Next


ObjectClose(cmdObj)
ObjectClose(dbObj)
ObjectClose(rdrObj)
ObjectClose(oTable)


MyDialogFormat=`WWWDLGED,6.2`


MyDialogCaption=`Sample SQLite Data`
MyDialogX=9999
MyDialogY=9999
MyDialogWidth=560
MyDialogHeight=102
MyDialogNumControls=002
MyDialogProcedure=``
MyDialogFont=`Microsoft Sans Serif|5632|70|34`
MyDialogTextColor=`DEFAULT`
MyDialogBackground=`DEFAULT,255|255|255`
MyDialogConfig=0


MyDialog001=`219,075,090,012,PUSHBUTTON,"PushButton_OK",DEFAULT,"OK",1,20,@csDefButton,DEFAULT,DEFAULT,"128|255|0"`
MyDialog002=`007,005,540,062,REPORTVIEW,"ReportView_1",arrData,DEFAULT,DEFAULT,10,@csAsort|@csFirstHeader|@csFullSel|@csGrid|@csSingleSel,"Microsoft Sans Serif|6144|70|34","255|255|0","0|0|0"`


ButtonPushed=Dialog("MyDialog")


Exit


[stanl]

Another interesting tidbit. To combat db bloating and fragmentation Access uses Compact, SQL Server uses Shrink and for some unknown reason SQLite uses Vacuum....

Code (WINBATCH) Select Expand



cmdObj = ObjectClrNew("System.Data.SQLite.SQLiteCommand",dbObj)
SQLText = "VACUUM;"
cmdObj.CommandText = SQLText
cmdObj.ExecuteNonQuery()



which is useful since DELETE queries merely reserve the space for additional use so the db size is unchanged.

[stanl]

Other tidbits:

   
• Ran my script with dll from 3.5 binary [364kb] and Jim's reference [1,432kb] - no issues
• Initial chagrin - Powershell script failed. Had to go to dll properties and check unblock. Something users here following this thread might want to consider depending on how dll is extracted.

Special shout Out to Jim: thanks for recent posts on this as well as HTMLAgility... kept my pooh-brain active during these stay at home times....