SQLite Extender (Alpha? Beta?)

[JTaylor]

Don't anyone get too attached as this still may go down the toilet but the current outlook is promising :)

There are currently 2 Functions.   May not sound like much but I don't expect for there to be a lot when done.   I haven't done any massive testing.  At the moment, if anyone has a minute I would be interested in hearing if this works okay for you.   You know how it is when something works on the development machine but fails when it gets out in the world.

My next function will, hopefully, be one to return SELECT results as a collection/object.  Hoping to replicate the ADO functionality to some degree.

I still need to test Unicode data.   Guessing I need to do some tweaking but don't know yet.

Be sure and use Full Path for the database (db).

dbExecute(db,SQLText)
    Executes the supplied SQLText;
    My goal is to return the number of rows affected but isn't working at the moment.  Just returns zero if successful.

dbGetRows(db,SQLText,Col_Headers)

    Returns an Array of  SELECT results.
    3rd Parameter is @TRUE to return Column Header or @FALSE to not.


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

Thanks again to Tony for his assistance and for putting up with someone as annoying as me  :)

Jim

[JTaylor]

Also, if anyone has any ideas/suggestions of what should be in such an Extender let me know.   No promises but will do what I can.

Jim

[kdmoyers]

This is so totally awesome my head has exploded, and now I must locate my brain.  I think it rolled under the desk.

I mean, look at the code! there's zero fuss with inexplicable stuff.  You load the extender, identify the file and go.

The fourth line of the script has a new table created.  Talk about friction free!

I'm not super familiar with peculiarities of SQLite syntax, but SQL is my thing, so I relish looking into it.

Thanks Jim!   

-Kirby

[JTaylor]

Appreciate the feedback.  SQLite really is pretty slick.   Trying to figure out how to pass an object back to WinBatch now.  I'm told all this is blatantly obvious if I just look at the code but I must be looking in the wrong places ;)    I am persistent though so we will see.   Thanks again.


Jim

[td]

If by "object" you are referring to a COM Automation object, they are represented by VARIENT structures of type VT_DISPATCH in Windows programming. The only role that the WIL extender interface plays is passing COM Automation object to and from the WIL DLL. Passing a COM Automation object, a VT_DISPATCH variant, to an extender is demonstrated in the sample code for the old C++ extender.  The old C++ extender also has examples of passing variants to the DLL and since COM Automation objects are just variant structures of the type VT_DISPATCH, they are passed the same way any other variant is.

Now if you don't have an understanding the things like VT_DISPATCH or  Windows COM APIs and structures like VARIANTs in general, MSFT's website documentation would be a good place to start.   

[JTaylor]

Excellent.  Thank you.   Had headed down that path so the confirmation is VERY helpful. 

Also, if I might ask a very simple question.   What is the purpose of DllVarHandler?   I have an example from an old SDK (although it fails just like my attempt did so felt better about my failure) and have read everything in the Extender but what I think it does doesn't really make sense to me so I must be missing something.

Jim

[td]

As sort of documented in addons.h, the purpose of DllVarHandler is to set, query, or clear the value of a WIL variable.  I can't say that I have ever used it but I do know that several older production WIL extenders do use it successfully.

[JTaylor]

Okay.  Thanks.  That is what I understood but was having trouble seeing why/where it would be used.   If you haven't used it guessing I wouldn't either so will put it from my mind for now.  Thanks again.

Jim

[JTaylor]

Added a dbGetString() function which returns a delimited list of query results.   Might want to check Task Manager if you try this out and see if any WinBatch processes hang around.   Please let me know if you do.   I have seen that a couple of times but maybe I have just corrupted my memory from everything I have been doing and need to reboot???   Also, while I have been trying to make sure I clean up after myself, maybe I have missed a step somewhere.   Curious what others might find.

Now back to what I was really trying to do :)

Jim

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

[JTaylor]

I think I found the problem but hard to say since it was intermittent and I have rebooted but I did find some dirty laundry laying around.

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

Jim

[KeithW]

Jim,

I am very interested in a SQLite adapter for WinBatch.  If there is anything I can do to help you test I would be most interested and willing to assist.
I find myself do more and most with SQLite and having the ability to go at my db's via WinBatch would be super !!

Regards,
Keith

[JTaylor]

By all means...grab what I post and put it through its paces in ways you would normally use SQLite...as much as possible anyway.  Obviously may not have some functionality you need.   I am open to suggestions as well.  I am somewhat limited by my ignorance but working on that problem and will add what I can, that people will use. 


Jim

Jim,

I am very interested in a SQLite adapter for WinBatch.  If there is anything I can do to help you test I would be most interested and willing to assist.
I find myself do more and most with SQLite and having the ability to go at my db's via WinBatch would be super !!

Regards,
Keith

[JTaylor]

Added 

       dbTables(db) - Returns list of tables.
       dbSchema(db,table_name(optional)) - Returns Schema of Table(s) in string format.

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

Documentation in test script.


Jim

[KeithW]

Jim,

Unless you have this at the top of your script, things blow up....

SQLText = $"CREATE TABLE IF NOT EXISTS COMPANY(
      ID_NO INT UNIQUE PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL );$"

fixing COMPANY1 fields to all have a 1 appended to them.


Regards,
Keith

[KeithW]

Jim,


BTW ... what is codabarfly ?  You seem to freely share it but there is no explanation...


Regards,
Keith

[JTaylor]

Sorry about that...I just added the additional tables for testing the dbTables() function and still had "Company" in my database so didn't catch or think about that problem.   I fixed that in the test script.  Thanks.

Jim

[KeithW]

Are you writing your own library  or  attaching to WB an existing sqlite library?
Can we try any standard queries or only the ones you have in your test script?
select, insert into, update, delete
do you have the sqlitemaster abilities?

Regards,
Keith

[JTaylor]

Couldn't remember and had to go look :-)  .........

It generates barcode numbers for libraries.   Not sure why I put that together.   Has been 10 years since I touched it.   Probably should remove that.

Jim

Jim,


BTW ... what is codabarfly ?  You seem to freely share it but there is no explanation...


Regards,
Keith

[JTaylor]

I am using the source/library from sqlite.org and writing the Extender against that code.   Hopefully I can just drop in the new "c" and "h" files for their updates and be good to go in the future.

You should be able to run any SQL Statement that sqlite3_exec will handle.   I don't plan to go crazy trying to do everything.   Hoping dbExecute() will handle most everything except queries.

I hope to return some type of COM object or Collection for SELECT but still trying to figure that out.  When I get tired of beating my head against the wall on that front I add other things. 

If you have suggestions for behavior changes, now is the time.    Can obviously add stuff later but changing things like defaults and such become problematic.

Not sure what you mean by "sqlitemaster abilities".

Jim

Are you writing your own library  or  attaching to WB an existing sqlite library?
Can we try any standard queries or only the ones you have in your test script?
select, insert into, update, delete
do you have the sqlitemaster abilities?

Regards,
Keith

[JTaylor]

Not sure if I directly answered your question....

Yes.  You should be able to run pretty much any SQL Statement.    dbExecute() for everything except SELECT, that doesn't require a response to be useful.   If there is something that needs a response that isn't a SELECT let me know.    dbGetRows() and dbGetString() for SELECT, for now.

Jim

[KeithW]

Jim,

There are three (3) sets of Control Files in SQLite (actually a couple more I do believe)
1.  sqlite_master   that has db & index info  (worth a look, may need to generate queries against this table sometimes)
2. sqlite_sequence   which has next auto Increment numbers for tables      (if you want or need to reset auto-increment values)
3. sqlite_stat[134] which have index data for query planning & optimizing   (should not really be of any value to most people)

These were the table I was talking about.

Regards,
Keith

[JTaylor]

I think so then.   The dbSchema() and dbTables() pulls from sqlite_master.

Also, should mention that one MUST pay attention to the case sensitivity when creating or interacting with a database.   You probably know this but will probably trip some others up if they don't know.   I will leave everyone to read up on this issue.   Just know that by default databases are case sensitive so if you are not getting the results you think you should it might be something to check.

Jim

[KeithW]

Jim,

Sadly, I know well about the case issues with SQLite in particular... however yet, just this weekend I got totally abused by just that and after banding my head for hours, found the bloody lowercase "o" where it should have been "O" and once fixed everything worked fine. It felt so much better to stop banging my head against the wall !!!

Keith

[KeithW]

Jim,

Do you see a problem with this code?  The call to dbtables() returns a list of about 8 or 10 tables including 'specific_obligors' dead in the middle.
In the snipit below, I get nothing... no error message or return from dbGetString(). The screen flicks like a refresh when the operation is finished but no results provided.  If I launch another tool, I can see the table and it contents, a little over 50 rows of data....

Thoughts?

Keith

Code Select Expand


AddExtender("wbsql44i.dll")
;
;db  = DirScript():"wb_sql.sqlite"  ;INCLUDE FULL PATH FOR DATABASE
db  = DirScript():"DTC.db"         ;INCLUDE FULL PATH FOR DATABASE

; LIST OF TABLES
;
; dbTables(db)
;   Returns a TAB Delimited List of Tables
;
stmt = dbTables(db)
Message("dbTables()",stmt)

; LIST (SELECT) ROWS VIA DELIMITED LIST
;
; dbGetString()
;  Returns a Delimited List of the Results.   
;   3rd Parameter(Optional) tells it to Return a Column Header if set to @TRUE.  Defaults to @FALSE
;   4th Parameter(Optional) is Field Delimiter.  Defaults to @TAB.  Multi-character delimiters are allowed.
;   5th Parameter(Optional) is Row Delimiter.  Defaults to @CR.  Multi-character delimiters are allowed.
;      Probably obvious but since I sometimes wonder about optional parameter values...
;      If you change the last one, assign the defaults as the others.
;
SQLText = "SELECT * FROM specific_obligors"
stmt = dbGetString(db,SQLText,@TRUE,"|",@CR)

Message("dbGetString()",stmt)


[JTaylor]

No.  Looks good to me.  Must be something wrong on the Extender side???  I have seen that behavior before...the going away with no message.     Didn't seem to appear before I added the dbSchema() and dbTables() functions.   Maybe remove those from the equation and see if it levels out for you and please let me know.   Probably good to try it a few times if you would.   

Thanks for letting me know.

Jim

[KeithW]

Jim,


The following only dumps HEADERS plus 9 rows  (10 total items)
How can I get or control the amount, there are over 50 items in the table....

Code Select Expand


; dbGetRows()
;
;  Returns an Array of the Results.   
;   3rd Parameter tells it to Return a Column Header if set to @TRUE
;
SQLText = "SELECT * FROM specific_obligors"
stmt = dbGetRows(db,SQLText,@TRUE)

ArrayFilePutCsv ("array.txt", stmt)
Message("dbGetRows() ",FileGet("array.txt"))


Regards,
Keith

[KeithW]

Jim,

Maybe  ... for ...

stmt = dbGetRows(db,SQLText,@TRUE)

you consider a 4th parameter, quantity
if blank or 0     dump entire table
if >1 limit to that many rows

Regards,
Keith

[JTaylor]

That seems weird.   I will do some more testing with a bigger table.  Was on my list of to-do things anyway.

Had thought about adding a way to return a set number of records since I am trying to, somewhat, model this like DAO.

Jim

Jim,


The following only dumps HEADERS plus 9 rows  (10 total items)
How can I get or control the amount, there are over 50 items in the table....

Code Select Expand


; dbGetRows()
;
;  Returns an Array of the Results.   
;   3rd Parameter tells it to Return a Column Header if set to @TRUE
;
SQLText = "SELECT * FROM specific_obligors"
stmt = dbGetRows(db,SQLText,@TRUE)

ArrayFilePutCsv ("array.txt", stmt)
Message("dbGetRows() ",FileGet("array.txt"))


Regards,
Keith

[KeithW]

Jim,

Also not sure it is a good idea to just create a file, in this case, Array.txt
WITHOUT specifying that is going to happen or allow for an alternate name/location to be specified.

could cause some unforseen issues that might drive someone nuts since it was not in the script
BEFORE the call to warn you were using a file to pass info back ad forth.....

Keith

[KeithW]

Regarding the file, since it is not automatically cleaned up and I understand why...
it could be a Security  OR  Privacy issue leaving it on the filesystem.

Keith

[JTaylor]

Okay.   I just assumed this would only be for testing with this data.  I only do it to display the array data.  It has nothing to do with the use of the Extender.  I will clean it up.

I might have figured out the issue with the row stuff or at least it going away.   I don't think I am handling NULL's properly.  Working on that now.

Jim

Regarding the file, since it is not automatically cleaned up and I understand why...
it could be a Security  OR  Privacy issue leaving it on the filesystem.

Keith

[JTaylor]

Might be good to set this aside for now and let me work through some of these issues.   Don't want to waste your time.

Thanks.

Jim

[KeithW]

Jim,

I pared off the table into a TEST.db if it would help?  I would be happy to give it to you but would
rather not post on the forum itself.

Keith

[JTaylor]

Appreciate it but I think I am duplicating the problems well enough for now.   The 10 row issue was just oversight on my part.    One of the problems with queries in SQLite is you don't know how many rows are returned so you don't know how big to make the array.  I had set 10 for the row dimension when I was starting because I knew it would hold what I had but forgot to go back and change it.  Still have to work around the not knowing a size.

Now it is acting weird on many fronts so trying to solve that mystery.   I'll post again when I think I have these things solved.  As I said in my first post...it may end up in the toilet but hopefully not  :)

Jim

[stanl]

Jim;


Something I did in 2011. Not sure you can, or would want to, be part of your project - but still seems to work

Code (WINBATCH) Select Expand



#DefineFunction isSqLite(cDB)  ;cDB is file to test
valid="53514C69746520666F726D6174203300"
retval=0
fs=FileSize(cDB)
binbuf = BinaryAlloc(fs+100)
If binbuf == 0
   Return(retval)
Else
   BinaryRead(binbuf, cDB)
   ret=BinaryPeekHex( binbuf, 0, 16)
   If ret==valid Then retval=1
EndIf
BinaryFree(binbuf)
Return(retval)
#EndFunction