Re: SQLite Extender (Beta?)

[JTaylor]

Since the other Thread has grown so long I thought I would start a new one, especially since I feel like this has gelled to the point where the structure will remain as it is and there will, hopefully, be no more breaking changes.

This version has some (read-only) Recordset emulation capabilities.   Keep in mind this is still Beta(?), with only me testing, so be careful of use and let me know of any problems or suggestions.   This is fairly similar to the previous release other than the addition of some Tagging capabilities (unless that was in the previous one???).    Most of the changes were internal due to Tony and Chuck flipping on a light switch for me which made what I did SO MUCH easier and I was able to eliminate a few hundred lines of code.

One function related change was the removal of the dbBind() function.  That is now a part of dbRS_Open().   

Also, one can now open 10 Recordsets at the same time, if you have the resources for it and I removed the record set constants so you no longer need those. 

Assuming it wasn't in the last release, you can now Tag records with a value and then retrieve those into an Array.   If it was, it is much better now.  Although I just thought of a tweak I should apply to one of the related functions.

    http://www.jtdata.com/anonymous/wbsql44i_vo.zip

Jim

[KeithW]

Jim,

Sorry, been out of the loop all week. Went to TN for my daughters wedding, car broke down on way back so rather than being back on Wed I just got home tonight.  All safe & sound in any case.  I have a day or two of immediate catch up since I had not planned to be out all week and as soon as I get some fires put to rest I will resume checking the latest release.

Regards,
Keith

[JTaylor]

Thought this would take longer based on an early error response but that turned out to be something else so here is the very latest.

Added dbOpen() and dbClose() functions.  Mostly as a way to support in-memory databases.   They are not, usually (ever?), needed if you are not running in-memory.

Also tweaked the IsTagged() function I mentioned earlier but guessing no one has tried it since I posted earlier, but could be wrong.

http://www.jtdata.com/anonymous/wbsql44i_vp.zip

Jim

[JTaylor]

Welcome back.

Jim

Jim,

Sorry, been out of the loop all week. Went to TN for my daughters wedding, car broke down on way back so rather than being back on Wed I just got home tonight.  All safe & sound in any case.  I have a day or two of immediate catch up since I had not planned to be out all week and as soon as I get some fires put to rest I will resume checking the latest release.

Regards,
Keith

[JTaylor]

Finally figured out that intermittent error with dbGetRows().

   http://www.jtdata.com/anonymous/wbsql44i_vq.zip

Jim

[JTaylor]

Hopefully I, and everyone else, won't regret it but I added Recordset Update and Delete options.   Test VERY, VERY CAREFULLY before using and have a backup.  I think I fixed all the weird, inconsistent, problems that kept cropping up as well.

   http://www.jtdata.com/anonymous/wbsql44i_vr.zip

Need to stop adding stuff and get UNICODE sorted out and then I think I will be done until I think of something else I want to add.

Jim

[JTaylor]

Here is the latest.   I have Unicode sorted for the dbGetString() & dbGetRows() functions, I think.   Fixed a delimiter issue with dbGetString();    Updated SQLite to 3.33.0.  Still need to finish Unicode updates for rest of functions.

I added the ability to query JSON via SQL.   Need to do more here but thought I would throw this out for a taste.   Should work with the two above-mentioned functions.   No guarantees on anything else.  I included a sample script.  It uses an in-memory database so you won't see a database.

Here is some more info on what is possible if I get it all implemented.    https://www.sqlite.org/json1.html

http://www.jtdata.com/anonymous/wbsql44i_vs.zip

Hopefully I haven't missed anything and someone finds this useful.   Maybe it will just be a big yawn for everyone else but I thought it pretty slick.

Jim

[KeithW]

Cool...

[JTaylor]

Some more examples:

SELECT fullkey, value FROM json_tab, json_tree(json_tab.jtxt, '$.books[0]') WHERE json_tree.type NOT IN ('object','array')

SELECT json_group_array(value) FROM json_tab, json_tree(json_tab.jtxt, '$.books[0]') WHERE json_tree.type NOT IN ('object','array')

SELECT json_group_object(key, value) FROM json_tab, json_tree(json_tab.jtxt, '$.books[0]') WHERE json_tree.type NOT IN ('object','array')


Jim

[JTaylor]

Had to fix a minor thing for RecordSets due to Sqlite not returning a datatype for JSON field and it didn't like NULL in the answer.  Threw in an example for Recordsets.   Most if not all of the functions on the sqlite.org page listed previously will work as is with varying degrees of usefulness.   Looking now into how I might wrap some of those so the submission is simpler and you won't have to submit SQL statements every time.

http://www.jtdata.com/anonymous/wbsql44i_vt.zip

Jim

[JTaylor]

Sorry for all the posts.   Never know when I am going to get pulled off onto another project so like to post when I have something solid.   I added a dbQueryValue() function.   It simply returns all the data with no type of delimiting.   It was originally intended for single value queries but with a little creativity you can do more.   I included an example of both.

http://www.jtdata.com/anonymous/wbsql44i_vu.zip

Jim

[JTaylor]

One final post for tonight...

If you don't need or want to bother with creating a table for the JSON you can do something like:

Code (winbatch) Select Expand

    SQLText = "SELECT key, value FROM json_tree('":FileGet("sqlite.json"):"', '$.books[0]') WHERE json_tree.type NOT IN ('object','array')"


Also, I reposted the latest version.   Have sorted out the Unicode question in practically all functions.     dbInsertFlat() and dbExecute() are the only ones left I think.

Jim

[stanl]

Jim;


Hopefully I can find time this weekend to absorb some of this. I just responded to a thread we have going about getting data from yahoo finance and referenced a url that returns Json for multiple stocks.


I'm not clear, and I apologize: but is the intent of you Json additions to the Extender to

   
• parse vaid Json into an SQlite Table
• export SQLite columns as Json
• both

[JTaylor]

Should be able to do both.   Wouldn't even, necessarily, have to write the JSON or data to a Table.  If you use the in-Memory option you don't even need a database.   It just allows you to manipulate JSON via SQL.   My last examples shows how you can just feed it in from a file and parse it without ever reading/writing anything to a table.   Haven't tried every single function but seems to be everything needed for most jobs.

As mentioned previously, I am trying to decide if I need to do anything more.  I keep thinking I do but then think of a way to do what I am wanting with things as they are.   Would appreciate some feedback on that front though.   I tried to provide enough SELECT examples to give you a good idea of how flexible the functions are that they provide.

For easy reference:   https://www.sqlite.org/json1.html

I am off to help the wife organize a shed today so may not respond to any other posts until later.


Jim