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
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
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
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
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 (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]
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
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.
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
Works for me.
Jim
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
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
Here are the columns...
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
Another interesting tidbit. To combat db bloating and fragmentation Access uses Compact, SQL Server uses Shrink and for some unknown reason SQLite uses Vacuum....
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.
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....