The SQLite WIL Extender allows WinBatch scripts to create, modify, and query SQLite databases.
Version 44001 Nov 22, 2023 (Beta release.)
YASAM (Yet Another SQLite Access Method) for WinBatch. This extender's design goals are to implement WIL extender functions that follow the underlying SQLite library's exposed interfaces without excessive abstractions and to present SQLite database data values directly as WIL variable types. The benefits of this approach are a simpler extender interface and better performance.
The extender does require some knowledge of the SQL programming language as the extender documentation does not offer an SQL tutorial. It does, however, contain several WinBatch script sample files and an SQL script for building Microsoft Access' NorthWind database.
The goal is to maintain the current design strategy. This means that any feature request will be considered only in the context of that strategy.
The extender can be downloaded here:
https://files.winbatch.com/downloads/wb/ilcsl44i.zip
Or you can use the vcheck script.
This is beta software so you may find a few bugs. Reporting them is appreciated.
*The extender's help file is not yet integrated into Consolidated WIL Help, so you must locate the file in the WinBatch folder and click on the file to view it.
So good!!! Thanks Tony!!!
-Kirby
Excellent. Another section of my Extender I can look at deprecating.
One question, your CSV import example, should the data be readable once the import is complete? Looks like it is not handling the Unicode for the foreign languages or I am doing something wrong. It looks fine in the file but scrambled in the grid display unless the field is all plain text.
Jim
The answer depends on what you mean by "Unicode for the foreign languages". Assuming you are referring to the country code CSV files on the GitHub site, they are all UTF-8 and will work as long as the UTF-8 only contains characters that overlap bitwise with ANSI characters. Otherwise, you would need to convert the UTF-8 to UTF-16 LE before loading the file into the dialog control. This is the case because WIL dialogs are like the Windows OS and only directly support ANSI and UTF-16 LE. This is not a new topic and file conversions have been discussed several times before.
I am well aware of the joys of making Unicode stuff work and have been a part of those discussions. I simply downloaded the file you referenced and ran your sample code, as is. Didn't know if it should display properly or not. Seemed odd you would offer sample code that wouldn't work as expected so thought maybe there was a problem. You asked that we report bugs. Didn't know if this was one or not so thought I would ask.
Jim
Nothing odd about it. The US-English version of the county code file used by the examples works fine without any conversion for the WIL dialog. You do not need to perform any conversion of the UTF-8 CSV files for the SQLite extender. This is because SQLite's native character encoding is UTF-8 and the extender handles returning SQLite database data as UTF-16 LE in WIL variables. In fact, you could use the extender to convert a UTF-8 CSV file to a UTF-16 LE array for displaying the file's contents in a dialog.
<edit>The URI to the GitHub country code CSV file in the sample script and the URI in the WIL help file will be changed to point directly to the English file.
Have to stay on side lines as my version is still 2022C, but do have a question: Does the upgraded Extender no longer require SQLite binaries to be downloaded?
The new SQLite extender is self-contained so only WinBatch and the extender are required.
Thanks
Uploaded a new version of the extender zip file. No changes to the DLL but made a few tweaks to the documentation including updating the CSV example per the discussion above.
Thanks, Jim for pointing out the URI problem. It led to a modification to the example that makes it a better demonstration of the extender's usage.
I'm keen to try this new extender, however I find that sqlite cannot match the one thing Microsoft Access can do for me, and that is generate SQL statements with such ease using the GUI. It's the one thing that keeps me in the Microsoft ecosystem.
Does anyone have any suggestion for this obstacle of mine?
SQL has an almost natural language syntax and isn't that difficult to master. Like most other skills, proficiency comes from doing. You could start with a tutorial like the one offered by W3 School https://www.w3schools.com/sql/ (https://www.w3schools.com/sql/). And then pick a problem you need to solve and solve it. There are endless examples of SQL statements online, so if you get stumped, you can probably find one that helps.
There are multiple online SQL generators that use (what else?) AI to create SQL statements. They may be useful if you get completely stumped but using them may foster dependency rather than proficiency.
When I worked as a teaching assistant in the computer science department at the university I was attending, I found that the cheat sheets I prepared for my students were a great help to the students. As a bonus, preparing them helped me become more proficient at the same skill. You could create your own personalized cheat sheet for SQL.
Thanks Tony, I didn't say I didn't know SQL. Just that Microsoft Access can do it for me, which I don't believe you are arguing against. Just think, you've advocated using AI, where Access can generate them far more accurately and it's a stupid tool that's been around for nearly 35 years.
I was just offering a little sarcasm directed at the latest tech fad and was not advocating the use of AI. There are multiple generators out there both using and not using AI. I have never needed to use any of them (including MSFT's version which will likely soon be AI-based...) so I have no idea which is better.
If you think Access is the greatest tool out there then use it but it does bring into question the point of your post. Maybe just felt the need to troll? Or is it that you don't understand nor/or appreciate the reasons for SQLite's popularity?
While not a thread to go off on MsAccess SQL... it would be an interesting topic for another thread. That said: to SQLite's credit
- In-Memory databases
- Attach to other db's [something you can do with Access linked files, but kinda clunky]