WinBatch® Technical Support Forum

All Things WinBatch => WinBatch => Topic started by: JTaylor on May 14, 2020, 08:03:50 PM

Title: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 14, 2020, 08:03:50 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 14, 2020, 08:31:44 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: kdmoyers on May 15, 2020, 11:03:32 AM
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

Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 15, 2020, 12:17:13 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: td on May 15, 2020, 01:23:26 PM
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.   
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 15, 2020, 01:56:36 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: td on May 15, 2020, 02:10:11 PM
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.
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 15, 2020, 02:19:26 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 16, 2020, 07:14:03 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 17, 2020, 08:59:46 AM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 17, 2020, 06:07:12 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 17, 2020, 06:30:48 PM
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

Quote from: KeithW on May 17, 2020, 06:07:12 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 17, 2020, 06:33:17 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 17, 2020, 08:29:36 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 17, 2020, 08:31:06 PM
Jim,


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


Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 17, 2020, 08:34:14 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 17, 2020, 08:36:20 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 17, 2020, 08:39:50 PM
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

Quote from: KeithW on May 17, 2020, 08:31:06 PM
Jim,


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


Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 17, 2020, 08:48:10 PM
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

Quote from: KeithW on May 17, 2020, 08:36:20 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 17, 2020, 08:56:45 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 17, 2020, 09:56:27 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 18, 2020, 06:54:39 AM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 18, 2020, 08:44:48 AM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 18, 2020, 01:05:41 PM
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



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)

Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 18, 2020, 02:13:06 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 18, 2020, 02:19:21 PM
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....


; 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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 18, 2020, 02:29:44 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 18, 2020, 02:36:58 PM
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


Quote from: KeithW on May 18, 2020, 02:19:21 PM
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....


; 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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 18, 2020, 02:41:36 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 18, 2020, 02:43:14 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 18, 2020, 02:57:27 PM
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

Quote from: KeithW on May 18, 2020, 02:43:14 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 18, 2020, 03:20:44 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 18, 2020, 03:42:07 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 18, 2020, 03:55:23 PM
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
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: stanl on May 19, 2020, 02:46:02 AM
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


#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

Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 19, 2020, 08:37:19 AM
Good idea.   Thanks.   Now if I can only figure out why everything seems to be falling apart  :(

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: td on May 19, 2020, 09:53:24 AM
The idea of an SQLite extender has been kicked around for a long time.  The thinking is that having a built-in WinBatch database is a useful feature but adding it directly to the interpreter DLL bloats the DLL more than is acceptable.  The main design focus of such an extender would be in-memory databases but file system based databases could come along for the ride.

It is something that we would consider bumping up the todo list with the expectation that a deliverable might appear sometime in the next 12 to 18 months, barring the unforeseen of course. (Development plans have been blown up twice in the last five months by the unforeseen.)
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 19, 2020, 11:48:00 AM
Having been in Corp IT for years before I escaped, I understand the 12-18 month Development Cycle, sad to say things have not changed that much over the years in order to get projects out, etc.   I would love to see a fully implemented SQLite feature within the WB product whether by WB personnel or external resources.  I now have a way to access SQLite database thru the redirection of the Command Line and while I would *much* rather have an Extender or Built-In interface I can make do with the Command Line methods on my current project.

I have not tinkered with C programming for probably close to 20 years, never got into C++ and no longer even have a compiler on my systems.  I would be willing to test, help document, etc for anyone working to get a SQLite extension in place but understand if the mountain is too high to cross at the moment, hopefully things will not remain that way for too long.

If I can help in any way please let me know.  I could really go to town with a fully integrated SQLite facility within WB.

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 19, 2020, 01:02:21 PM
...and like someone waved a wand it seems to be working again.   

I did turn off dbGetString().   Have a weird situation where it dies quietly when I hit row 37 of the results.   Trying to decide if it is even worth having since WB can translate this easily enough from an Array.  I could see some benefits though.  Something similar would be to return a MapCreate() compatible string so cracking the one would make the other possible as well. 

Not sure what you all will think of my solution for the Array size issue.   Open to suggestions.

Since this is, in part, a learning project for me I will probably continue plugging away.   Learning a LOT about what doesn't work and occasionally things that do work.   Love to a see a native one provided either way.

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

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: td on May 19, 2020, 01:36:44 PM
No done deals.  I posted the idea mostly just to get feedback and measure interest.  The underlying idea would be to provide WinBatch with a tuple like data structure to go along with lists, arrays, and maps but provided by an extender instead of built-in. 
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 19, 2020, 03:00:48 PM
Jim,


Test Scripts tries to INSERT INTO Company1 with incorrect field names...
dbGetRows() has a the forth parameter but does not want to accept.
?
could you consider putting a  dbGetVer() and either put a compile number of date so we can be sure what version with are running/testing
date/time on my dll 5/19/2020 @ 2:52 PM

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 19, 2020, 03:01:25 PM
You know me...I am always interested in Data & Dialog related enhancements and one can never have too many Tuples.

May want to post the idea under it's own Posting though as many have probably lost interest here and won't see it.

jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 19, 2020, 03:02:59 PM
I really need to start with deleting the database for my test script, don't I, so I catch these problems.

Jim

Quote from: KeithW on May 19, 2020, 03:00:48 PM
Jim,


Test Scripts tries to INSERT INTO Company1 with incorrect field names...
dbGetRows() has a the forth parameter but does not want to accept.
?
could you consider putting a  dbGetVer() and either put a compile number of date so we can be sure what version with are running/testing
date/time on my dll 5/19/2020 @ 2:52 PM

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 19, 2020, 03:10:19 PM
And just like that I went backwards too...

dbTables()   works
dbSchema() works
dbGetRows() returns nothing, it did yesterday on the same table....

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 19, 2020, 03:53:06 PM
Jim,

Two things,

first on the copy of the Extender I got this afternoon, this error occurs.....
see Screenshot

and

Below is a cleaned up copy of the Demo Script, hope you do not mind my stylizing.

;#############################################################
;#############################################################
;
;   wb_sql.wbt - Demo Script for SQLite databse operations
;;
;#############################################################
;#############################################################

AddExtender("wbsql44i.dll")             ; REQUIRED EXTENDER !!

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

;#############################################################
; Building 5 Tables for testing  COMPANY & COMPANY1 - COMPANY4
;#############################################################
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 );$"
resp = dbExecute(db,SQLText)

SQLText = $"CREATE TABLE IF NOT EXISTS COMPANY1(
ID_NO INT UNIQUE PRIMARY KEY     NOT NULL,
NAME1          TEXT    NOT NULL,
AGE1           INT     NOT NULL,
ADDRESS1       CHAR(50),
SALARY1        REAL );$"
resp = dbExecute(db,SQLText)

SQLText = $"CREATE TABLE IF NOT EXISTS COMPANY2(
ID_NO INT UNIQUE PRIMARY KEY     NOT NULL,
NAME2          TEXT    NOT NULL,
AGE2           INT     NOT NULL,
ADDRESS2       CHAR(50),
SALARY2        REAL );$"
resp = dbExecute(db,SQLText)

SQLText = $"CREATE TABLE IF NOT EXISTS COMPANY3(
ID_NO INT UNIQUE PRIMARY KEY     NOT NULL,
NAME3          TEXT    NOT NULL,
AGE3           INT     NOT NULL,
ADDRESS3       CHAR(50),
SALARY3        REAL );$"
resp = dbExecute(db,SQLText)

SQLText = $"CREATE TABLE IF NOT EXISTS COMPANY4(
ID_NO INT UNIQUE PRIMARY KEY     NOT NULL,
NAME4          TEXT    NOT NULL,
AGE4           INT     NOT NULL,
ADDRESS4       CHAR(50),
SALARY4        REAL );$"
resp = dbExecute(db,SQLText)

Message("Create",resp)

;#############################################################
; Inserting 4 rows of data in COMPANY
;#############################################################
SQLText = $"INSERT OR IGNORE INTO COMPANY (ID_NO,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 ); 
            INSERT OR IGNORE INTO COMPANY (ID_NO,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 );     
            INSERT OR IGNORE INTO COMPANY (ID_NO,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
            INSERT OR IGNORE INTO COMPANY (ID_NO,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );$"
resp = dbExecute(db,SQLText)
Message("dbExecute() - Insert",resp)

;#############################################################
; Listing TABLES in db
;#############################################################
;dbTables(db)
; Returns a TAB Delimited List of Tables

stmt = dbTables(db)
Message("dbTables",stmt)

;#############################################################
; Listing fields within COMPANY
;#############################################################
;dbSchema(db,Table_Name)
; Returns Database Table Schema in a String Format
;  2nd Argument(optional) is Table Name. Leave off if you want all tables.

stmt = dbSchema(db,"COMPANY")
Message("dbSchema",stmt)

;############################################################
; Showing DDL for COMPANY
;#############################################################
;dbGetRows()
; Returns an Array of the Results.   
;  3rd Parameter tells it to Return a Column Header if set to @TRUE
;  4th Parameter - Number of Rows to be Returned or 0 (zero) if using modified query
;
;***IMPORTANT****
;   One problem with SQLite is there is no way to know how many rows are being returne and therefore
;   no way to know how big to make an array.   There are ways around it but not very efficient ones
;   as far as I can tell.  This seems to be the most elegant.
;   I am providing two(3?) options.   This modified query and parameters to tell it how many rows you
;   want returned as well as putting "*EOF*" in the last row of the array if the array size is greater
;   than the number of rows.  I couldn't see a way to trap an uninitialized element without using ERROR
;   Handling so went this route.
;   
;   If you are using the modified query then set row_cnt parameter = 0
;
;   For the modified query it needs to look like the following.   The WHERE statement must be repeated.
;
;   WITH cnt(total) as (SELECT COUNT(*) from TABLE_NAME where id_no > 400) select * from
;   {NOTE COUNT VARIABLE HERE. REMOVE THIS NOTE}  cnt   ,TABLE_NAME where id_no > 400"
;
;#############################################################

SQLText = "WITH cnt(total) AS (SELECT COUNT(*) FROM company WHERE id_no > 600) SELECT * FROM cnt,company WHERE id_no > 600"
SQLText = "SELECT * FROM company WHERE id_no > 0"
stmt = dbGetRows(db,SQLText,@TRUE,600)
;stmt = dbGetRows(db,SQLText,@TRUE)

;Message("JUGS", stmt)
Message("dbGetRows() Rows",ArrInfo(stmt,1))

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

;#############################################################
;#############################################################
;
;    THIS IS DEPRECATED FOR NOW*******************************
;
; It is easy enough to convert in WinBatch
;#############################################################
;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 COMPANY; "
; stmt = dbGetString(db,SQLText,@TRUE,"|",@CR)
;
; Message("dbGetString()",stmt)

;#############################################################
; Deleting the rows of data from COMPANY
;#############################################################
;dbDelete

SQLText = "DELETE FROM COMPANY1;"
resp = dbExecute(db,SQLText)
Message("dbExecute() - Delete",resp)

;#############################################################
; Verifying COMPANY has been emptied
;#############################################################
;dbGetRows()
; Returns an Array of the Results.   
;  3rd Parameter tells it to Return a Column Header if set to @TRUE

SQLText = "Select * from COMPANY"
stmt = dbGetRows(db,SQLText,@TRUE,600)

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

;#############################################################
;#############################################################
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 19, 2020, 06:47:27 PM
Jim,

I promise I won't touch it anymore tonight, lol

I found a couple of typos I wanted to correct  AND  fixed it so it ran all the way thru
On both call to dbGetRows()  i put a line removing the 4th parameter so this script
runs with the most current DLL that you posted.


;#############################################################
;#############################################################
;
;   wb_sql.wbt - Demo Script For:  SQLite Database Operations
; As of: 05/19/2020
;#############################################################
;#############################################################

AddExtender("wbsql44i.dll")             ; REQUIRED EXTENDER !!

db = DirScript():"wb_sql.sqlite"  ; USE FULL PATH FOR DATABASE

;#############################################################
; Building 5 Tables for testing  COMPANY & COMPANY1 - COMPANY4
;#############################################################
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 );$"
resp = dbExecute(db,SQLText)

SQLText = $"CREATE TABLE IF NOT EXISTS COMPANY1(
ID_NO INT UNIQUE PRIMARY KEY     NOT NULL,
NAME1          TEXT    NOT NULL,
AGE1           INT     NOT NULL,
ADDRESS1       CHAR(50),
SALARY1        REAL );$"
resp = dbExecute(db,SQLText)

SQLText = $"CREATE TABLE IF NOT EXISTS COMPANY2(
ID_NO INT UNIQUE PRIMARY KEY     NOT NULL,
NAME2          TEXT    NOT NULL,
AGE2           INT     NOT NULL,
ADDRESS2       CHAR(50),
SALARY2        REAL );$"
resp = dbExecute(db,SQLText)

SQLText = $"CREATE TABLE IF NOT EXISTS COMPANY3(
ID_NO INT UNIQUE PRIMARY KEY     NOT NULL,
NAME3          TEXT    NOT NULL,
AGE3           INT     NOT NULL,
ADDRESS3       CHAR(50),
SALARY3        REAL );$"
resp = dbExecute(db,SQLText)

SQLText = $"CREATE TABLE IF NOT EXISTS COMPANY4(
ID_NO INT UNIQUE PRIMARY KEY     NOT NULL,
NAME4          TEXT    NOT NULL,
AGE4           INT     NOT NULL,
ADDRESS4       CHAR(50),
SALARY4        REAL );$"
resp = dbExecute(db,SQLText)

Message("Create",resp)

;#############################################################
; INSERT 4 rows of data into COMPANY
;#############################################################
SQLText = $"INSERT OR IGNORE INTO COMPANY (ID_NO,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 ); 
            INSERT OR IGNORE INTO COMPANY (ID_NO,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 );     
            INSERT OR IGNORE INTO COMPANY (ID_NO,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
            INSERT OR IGNORE INTO COMPANY (ID_NO,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );$"
resp = dbExecute(db,SQLText)
Message("dbExecute() - Insert",resp)

;#############################################################
; Listing TABLES in db
;#############################################################
;dbTables(db)
; Returns a TAB Delimited List of Tables

stmt = dbTables(db)
Message("dbTables",stmt)

;#############################################################
; Listing fields within COMPANY
;#############################################################
;dbSchema(db,Table_Name)
; Returns Database Table Schema in a String Format
;  2nd Argument(optional) is Table Name.
;   Leave off if you want all tables.

stmt = dbSchema(db,"COMPANY")
Message("dbSchema",stmt)

;############################################################
; Showing DDL for COMPANY
;#############################################################
;dbGetRows()
; Returns an Array of the Results.   
;  3rd Parameter tells it to Return a Column Header if set to @TRUE
;  4th Parameter - Number of Rows to be Returned or 0 (zero) if using modified query
;
;***IMPORTANT****
;   One problem with SQLite is there is no way to know how many rows are being returne and therefore
;   no way to know how big to make an array.   There are ways around it but not very efficient ones
;   as far as I can tell.  This seems to be the most elegant.
;   I am providing two(3?) options.   This modified query and parameters to tell it how many rows you
;   want returned as well as putting "*EOF*" in the last row of the array if the array size is greater
;   than the number of rows.  I couldn't see a way to trap an uninitialized element without using ERROR
;   Handling so went this route.
;   
;   If you are using the modified query then set row_cnt parameter = 0
;
;   For the modified query it needs to look like the following.   The WHERE statement must be repeated.
;
;   WITH cnt(total) as (SELECT COUNT(*) from TABLE_NAME where id_no > 400) select * from
;   {NOTE COUNT VARIABLE HERE. REMOVE THIS NOTE}  cnt   ,TABLE_NAME where id_no > 400"
;
;#############################################################

SQLText = "WITH cnt(total) AS (SELECT COUNT(*) FROM company WHERE id_no > 600) SELECT * FROM cnt,company WHERE id_no > 600"
SQLText = "SELECT * FROM company WHERE id_no > 0"
;stmt = dbGetRows(db,SQLText,@TRUE,600)
stmt = dbGetRows(db,SQLText,@TRUE)

;Message("JUGS", stmt)
Message("dbGetRows() Rows",ArrInfo(stmt,1))

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

;#############################################################
;#############################################################
;
; *** *** ***   THIS IS DEPRECATED FOR NOW   *** *** ***
;
;        It is easy enough to convert in WinBatch
;#############################################################
;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 to the others.
;#############################################################
; SQLText = "Select * from COMPANY;"
; stmt = dbGetString(db,SQLText,@TRUE,"|",@CR)

; Message("dbGetString()",stmt)

;#############################################################
; Deleting the rows of data from COMPANY
;#############################################################
;dbDelete

SQLText = "DELETE FROM COMPANY;"
resp = dbExecute(db,SQLText)
Message("dbExecute() - Delete",resp)

;#############################################################
; Verifying COMPANY has been emptied
;#############################################################
;dbGetRows()
; Returns an Array of the Results.   
;  3rd Parameter tells it to Return a Column Header if set to @TRUE

SQLText = "Select * from COMPANY"
;stmt = dbGetRows(db,SQLText,@TRUE,600)
stmt = dbGetRows(db,SQLText,@TRUE)

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

;#############################################################
;   ***   ***   ***   End Of Demo Script   ***  ***   ***
;#############################################################


Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 19, 2020, 07:33:36 PM
Hmmmmm...not sure why that 4th parameter wasn't working.  That is very odd because I run the test script I post every time and it worked.  I now delete the database with every run so it should eliminate those unnecessary errors you have been encountering as well.

In any event, let's see if this does any better.   Still trying to figure out why it starts dying after it works fine.   This one seems more stable though so hopefully you will agree.

I added a dbMapData() function, the results of which one can pass immediately into the WinBatch MapCreate() function.  Why?   Just because  :)

Really appreciate the time you have put into this.   If I can get this stable at this point I will probably stop posting until I have a object type SELECT working.  Unless I have forgotten something one can now do pretty much everything in a general SQL sense.   Even if I don't get the object approach working, iterating through the array would do the job and for most things people would want an array anyway, for grid population.   Have to figure out how to create a COM object to make the object thing work, I think.  Been working through the MS docs on that subject.

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



Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 19, 2020, 07:52:00 PM
Jim,

Have you tried building an index?  Common task
Just asking ...

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 19, 2020, 09:23:45 PM
Jim,

OK Progress !!
Modified my app script with syntax changes provided by your Demo Script

It still dies on NULL fields but everything worked with my table,
so best of luck getting the NULL resolved and we may just have a winner on your hands.

I understand the Sub-queries to get array dimensions and  that's cool.  Is the a physical (WB)
memory limit or a compiled in buffer limit (you) for the 1MB on dbGetString() & dbMapData() functions? 
If it is a buffer limitation, could a function be written to dynamically create a larger buffer with a call
placed up front in the WB script to allocate larger before running the rest of the script?  A User
configuration/override should the 1MB be exceeded?

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 19, 2020, 10:59:26 PM
Jim,

I successfully Created & Dropped Indexes with dbExecute() function...
worked like a champ !!!

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 20, 2020, 06:49:15 AM
Thanks for the reminder....forgot about the NULL issue after recreating the database as that zapped the test I set up.

I may be able to overcome the 1Mb limit.   Mostly ignorance on my part.   Has to do with not being able to return (string)s and having to convert to char*s.   The char*s seemed to choke fairly quickly and strings seemed to be the consensus when it comes to large amounts of this type of data.   Mostly went that route as I was spinning my wheels with no solution in sight, in addition to the fact that one can easily convert to delimited format from an Array within WinBatch.   Also wasn't sure if people really needed to return large result sets in a delimited form that they would keep in memory.  Agreed that even if that is so, 1 million isn't that much.   Maybe a knowledgeable person will read this and tell me how to make strings of any size work because nothing I have tried works ;D   

Yeah...any non-SELECT SQL statement should run successfully with dbExecute().  Actually a SELECT should too, just won't do you any good  :)   Especially general stuff and no reason everything shouldn't work.   Haven't tested everything, of course, but can't think of why they wouldn't but could be wrong.

Also, I have done nothing to test BLOBs and I need to switch things to handle UNICODE.   

Glad to hear this one seems stable.

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: td on May 20, 2020, 08:16:29 AM
Quote from: JTaylor on May 19, 2020, 03:01:25 PM
You know me...I am always interested in Data & Dialog related enhancements and one can never have too many Tuples.

May want to post the idea under it's own Posting though as many have probably lost interest here and won't see it.

jim


I was mostly interested in your response as well as those posting to this topic.

BTW, "in mathematics, a tuple is a finite ordered list (sequence) of elements." In computer science, it is often thought of as a data type.  Some languages have tuples as a native data type. The Python language is one of the better-known languages with a tuple data type. 
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 20, 2020, 08:57:52 AM
I think I have squashed the NULL issue.

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

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: kdmoyers on May 20, 2020, 09:31:37 AM
Jim, I'm getting 404 error on http://www.jtdata.com/anonymous/wbsql44i_v2.zip
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 20, 2020, 11:09:28 AM
Jim,

I too get the 404 Error on v2 zip...

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 20, 2020, 11:24:59 AM
How about now?

Jim

Quote from: KeithW on May 20, 2020, 11:09:28 AM
Jim,

I too get the 404 Error on v2 zip...

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 20, 2020, 11:26:11 AM
works, got it

thanx,
keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 20, 2020, 11:34:40 AM
JIM,


YEAH !!!   It does not trip over NULL values, good job my friend !!!!!
I think you have the start of a winner, my test script works perfectly with this version !!!!!

Thank-You !!

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 20, 2020, 11:37:42 AM
I vaguely remember Tuples but hadn't taken time yet to refresh my memory. 

I do have some projects where I have need and make use of a small and easily distributed database so would definitely make use of such a thing as it would eliminate sqlite and the required files, easy as it is.

When you say in-memory, would we be loading data from a file every time?   Delimited? Proprietary? Something else?   Would it be SQL based?   Just curious how it would work.

If I do this right it might save you the hassle and you are welcome to it.   Upside is the data source can be used by any other SQLite client and management tool.

In any event, I think a native database option, whatever the foundation, would be used and appreciated by a number of people.   I am sure prioritizing development is difficult.    Happy to take some of the load on this front, especially if you throw me a bone here and there to help me figure out problems  :)

Jim

Quote from: td on May 20, 2020, 08:16:29 AM

I was mostly interested in your response as well as those posting to this topic.

BTW, "in mathematics, a tuple is a finite ordered list (sequence) of elements." In computer science, it is often thought of as a data type.  Some languages have tuples as a native data type. The Python language is one of the better-known languages with a tuple data type.
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 20, 2020, 11:43:19 AM
If you haven't, please try a dbGetRows() query that returns a few thousand results and see what happens.   I am getting a WinBatch error about not having the memory to save results.   That uses the Extender functions to build the data set rather than me cobbling something together so not sure where it is going wrong.  Obviously my fault, just not sure why.   Assuming it isn't something else on my machine.

Jim

Quote from: KeithW on May 20, 2020, 11:34:40 AM
JIM,


YEAH !!!   It does not trip over NULL values, good job my friend !!!!!
I think you have the start of a winner, my test script works perfectly with this version !!!!!

Thank-You !!

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 20, 2020, 11:51:20 AM
Jim,

I will try to get at that shortly here, I have tables all the way upto 3.5MM rows.... see where it falls down.

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 20, 2020, 12:08:04 PM
I figured out the problem...just needed a little atoi() action when retrieving the value from the query results.  It was turning it into a huge number casting directly to INT for the row count and running out of memory.

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

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 20, 2020, 01:19:15 PM
JIM,

SQLText = "SELECT * FROM summ_by_Customer LIMIT 100;"
stmta = dbGetRows(db,SQLText,@TRUE,103)

given the above the minimum value for the 4th Param in dbGetRows() is data return length plus 3 !!
Header, EOF and whatever the third thing is????   102 will NOT work so there should probably be some notes about this???

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 20, 2020, 01:25:32 PM
Jim,

You return a value of 104 rows in my case above,  )-based counting issue?

Did you consider putting a version function in Library?

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 20, 2020, 01:47:23 PM
What is happening is that you are telling it there are 103 rows and you want Headers so it defines the Array as 103 + 1 for headers.   The data is only returning 100 rows + header so there are 103 rows with the *EOF*.   The array will tell you there are 104 rows, which is correct because that is what you requested and what was dimensioned.   When it is written to a file you will only see the 103 because, by default, it doesn't write out undefined elements.  If you change the ArrayFilePutCSV() parameters you would see all 104 rows.  You could also try addressing the 104th row (103 subscript) and it will error out telling you it is uninitialized but not that it is out of bounds.

This is one of those things I will not be able to win at, unless SQLite decides to provide a result count up front, but I will clarify further in the documentation.  Thanks for the feedback.

I will get to a version number soon.   Was waiting to make sure this was actually going somewhere :)    That is why I starting numbering the zip files so you would know the latest.

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 20, 2020, 01:52:24 PM
So I don't forget to mention it...after the next post...version can be obtained via the @dbVersion CONSTANT.

Now to remember to update it.

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 20, 2020, 02:10:33 PM
Use this with dbGetRows() to clean things up...

Code (winbatch) Select

dim = ArraySearch (stmt, "*EOF*", 0, 0)    ;Read up on Search Types
If dim > -1 Then ArrayRedim(stmt, dim, -1)

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 20, 2020, 02:36:29 PM
Jim,

Here are some stats on dbGetRows() on my system, a i7 notebook with 16GB Ram, 1 TB Intel SSD (Split C/D and both partitions are really full, my bad, and the system is anything but dry... many programs and a boat load of background stuff = busy system, but that is how I run so I wanted to test i a realistic mode for me)

The Table I was running again is just over 3.5MM rows, 20 columns, average row width is just shy of 400 characters...  I was using a LIMIT on the SELECT to see what happened.... see code...

ROWS  TIME/SECs
100         0
500         0
1000       0
1500       0
2500       0
5000       0
10000     1
25000     0
50000     2
100000   3
250000   9
500000   ?  the process stopped at 415,832 lines (line incomplete) array.txt file size 59,848,153 bytes
415000   80  process complete initially 415,002 lines (completed) array.txt file size 59,728,611 bytes.

Then I got the following error trying to display the ARRAY
3096 Memory Allocation Failure, Out Of memory for strings.

Using: WinBatch 32 2020A
WIL Version 6.20ata


so I am guessing that is the max my system can chew at a time.


i = 500000
SQLText = "SELECT * FROM summ_by_Customer LIMIT %i%;"

ScriptStart = TimeYmdHms()
stmta = dbGetRows(db,SQLText,@TRUE,i + 3)
ScriptEnd = TimeYmdHms()

ArrayFilePutCsv("array.txt", stmta)  ; stmta not an Array?  Out-of-range error
Elapsed = TimeDiffSecs(ScriptEnd,ScriptStart)
Message("dbGetRows()","Processing Complete !!" : @CRLF : "Executed in: %Elapsed% Seconds" )


Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 20, 2020, 02:52:36 PM
Is @dbVersion suppose to be in the v3 library?

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 20, 2020, 03:03:14 PM
No.   The next one I post.  Just didn't want to forget to mention it.

Jim

Quote from: KeithW on May 20, 2020, 02:52:36 PM
Is @dbVersion suppose to be in the v3 library?

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 20, 2020, 03:08:44 PM
Wow.  I am impressed it performed that well.

Yes.   Depending on many factors the cell limit for an Array in WinBatch is around 10mil but you have a lot of wide columns so 8 mil is not unexpected.

Glad to hear my Extender wasn't a limiting factor.  I am very pleased.  Performance was pretty reasonable as well I thought, at least up to 250,000.  80 seconds seemed a bit long but probably nothing I can do about that. 

Thanks for the info.

Jim


Quote from: KeithW on May 20, 2020, 02:36:29 PM
Jim,

Here are some stats on dbGetRows() on my system, a i7 notebook with 16GB Ram, 1 TB Intel SSD (Split C/D and both partitions are really full, my bad, and the system is anything but dry... many programs and a boat load of background stuff = busy system, but that is how I run so I wanted to test i a realistic mode for me)

The Table I was running again is just over 3.5MM rows, 20 columns, average row width is just shy of 400 characters...  I was using a LIMIT on the SELECT to see what happened.... see code...

ROWS  TIME/SECs
100         0
500         0
1000       0
1500       0
2500       0
5000       0
10000     1
25000     0
50000     2
100000   3
250000   9
500000   ?  the process stopped at 415,832 lines (line incomplete) array.txt file size 59,848,153 bytes
415000   80  process complete initially 415,002 lines (completed) array.txt file size 59,728,611 bytes.

Then I got the following error trying to display the ARRAY
3096 Memory Allocation Failure, Out Of memory for strings.

Using: WinBatch 32 2020A
WIL Version 6.20ata


so I am guessing that is the max my system can chew at a time.


i = 500000
SQLText = "SELECT * FROM summ_by_Customer LIMIT %i%;"

ScriptStart = TimeYmdHms()
stmta = dbGetRows(db,SQLText,@TRUE,i + 3)
ScriptEnd = TimeYmdHms()

ArrayFilePutCsv("array.txt", stmta)  ; stmta not an Array?  Out-of-range error
Elapsed = TimeDiffSecs(ScriptEnd,ScriptStart)
Message("dbGetRows()","Processing Complete !!" : @CRLF : "Executed in: %Elapsed% Seconds" )


Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 20, 2020, 04:03:11 PM
Jim,

Keep in mind that the file I/O of creating Array.txt is in that time, I do believe and my full filesystem will be impacting that time as well.
I watched  array.txt swell during those 80 seconds.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 20, 2020, 04:23:10 PM
Jim,

I would guess that v4 may well become a workable piece of software.  I am in the process of automating my project.  It was in a different (DBased RAD Tool and I wanted to migrate it to SQL based tool) and I have all of the support scripts saved as separate .sql files for the entire process.  The goal was to move the project to a SQL based environment and then automate all but two or three steps that could not and make it easier to manage on a monthly basis.  I think dbSQLite is almost ready to take on the challenge and I certainly appreciate your attention to getting this put together as I think it will make my life a bit easier going forward.

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: stanl on May 20, 2020, 05:56:13 PM
Jim;


I modified my original script to create an SQLite db and Table populated from a tab-delimited CSV file. The attached wakecounty.csv is tab=delimited and I included a 1-liner schema.ini (I'm working on a interface to autocreate the .ini) so Ace Provider wouldn't error. The process of inserting rows from the datatable into SQLite is a little slow and clumsy to look at. Since the database/sqlite table are identical in structure maybe there is a way to INSERT INTO....FROM - or some kind of bulk copy.  Anyway, be a nice project to replace the System.Data.SQLite with your extender. There was one bug. The original csv had a column Time Zone - which although could be created in the SQLite table, put out an error during the INSERT command. I changed it to TimeZone figuring there may be a need in SQLite to enclose multi-word columns in brackets

[/size]
Code (WINBATCH) Select


;Winbatch 2020A - Parsing csv for SQLite Structure
;Stan Littlefield, May 4, 2020
;Updated to Create SQLite db and table May 18
;///////////////////////////////////////////////////////////////////////////////////////////////////////////////
gosub udfs
IntControl(73,1,0,0,0)
folder = dirscript()
;file is tab-delimited and simple schema.ini included
;future plan is to create schema.ini on the fly
file=folder:"WakeCounty.csv"
If ! FileExist(file) Then Terminate(@TRUE,"Cannot Continue",file:" is missing")


BoxOpen("Please Wait","Transferring Text File To SQLite Table")


;cConn='Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%folder%':';Extended Properties="TEXT;HDR=YES;FMT=Delimited"'
cConn='Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%folder%':';Extended Properties="TEXT;HDR=YES"'
;Message("Connection",cConn)


db = DirScript():"WakeCounty.sqlite"
ObjectClrOption("Appbase", DirScript())
ObjectClrOption('use','System.Data.SQLite')


ObjectClrOption("useany","System.Data")
oConn = ObjectClrNew( 'System.Data.OleDb.OleDbConnection',cConn)
oConn.Open()
oCmd = ObjectClrNew('System.Data.OleDb.OleDbCommand')
cSQL = "SELECT * FROM ":file


oCmd.Connection = oConn
oCmd.CommandText = cSQL


Boxtext("Creating .NET DataTable")


oAdapter = ObjectClrNew( 'System.Data.OleDb.OleDbDataAdapter')
oTable = ObjectClrNew( 'System.Data.DataTable')   
oTable.TableName = 'TEST'             
oAdapter.SelectCommand = oCmd
rowcount = oAdapter.Fill(oTable)
colcount = oTable.Columns.Count
BoxText("Columns ":colcount:@LF:"Rows ":rowcount)
oCols = oTable.Columns
cCreate = "Create Table Accounts (":@LF
ForEach col in oCols
   name = col.ColumnName
   colType = col.DataType.ToString()
   objRow = oTable.Rows.Item(0)
   val = objRow.Item(col)
   ;cCreate = cCreate:name:" ":cvt(colType):",":"[VALUE: %val% .net type %colType%]":@LF
   cCreate = cCreate:name:" ":cvt(colType):",":@LF
Next
cCreate = StrSub(cCreate,1,Strlen(cCreate)-2):");"


;WB SQLite Extender in place of System.Data.SQLite
oSQLite = ObjectClrNew("System.Data.SQLite.SQLiteConnection","Data Source=%db%;Version=3;Pooling=true;Legacy Format=True")
oSQLite.Open
oCmd1 = ObjectClrNew("System.Data.SQLite.SQLiteCommand",oSQLite)
oCmd1.CommandText = "DROP TABLE IF EXISTS Accounts";
oCmd1.ExecuteNonQuery();


SQLText = cCreate
oCmd1.CommandText = SQLText
oCmd1.ExecuteNonQuery();


oRows = oTable.Select()
For i =0 To rowcount -1
  oRow=oRows[i]
  SQLText = "INSERT INTO Accounts ("
  ForEach col in oCols
     SQLText = SQLText:col.ColumnName:","
  Next
  SQLText = StrSub(SQLText,1,Strlen(SQLText)-1):") VALUES ("
  For col = 0 To ColCount-1
     oCol = oTable.Columns.Item(col)
     SQLText = SQLText:"'":oRow.Item(oCol):"'":","
  Next
  SQLText = StrSub(SQLText,1,Strlen(SQLText)-1):");"
  oCmd1.CommandText = SQLText
  oCmd1.ExecuteNonQuery();
Next


oAdapter.Dispose()
oTable=0
oCmd=0
oConn=0
ObjectClose(oCmd1)
ObjectClose(oSQLite)
BoxShut()
Message("Complete","Check for SQLite db ":db:@LF:"and Table Accounts")


Exit


:WBERRORHANDLER
geterror()
Message("Error Encountered",errmsg)
Exit


;/////////////////////////////////////////////////////////////////////////////////////


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine


#DefineSubRoutine cvt(colType)
retval = "BLOB"
If colType == "System.Boolean" Then  retval="INTEGER"
If colType == "System.Byte" Then  retval="INTEGER"
If colType == "System.Char" Then  retval="TEXT"
If colType == "System.DateTime" Then retval="TEXT"
If colType == "System.Decimal" Then retval="NUMERIC"
If colType == "System.Int32" Then retval="INTEGER"
If colType == "System.Int64" Then retval="REAL"
If colType == "System.Int16" Then retval="INTEGER"
If colType == "System.Object" Then retval="BLOB"
If colType == "System.Single" Then retval="REAL"
If colType == "System.String" Then retval="TEXT"
If colType == "System.Double" Then retval="REAL"
If colType == "System.Array" Then retval="BLOB"
If colType == "System.UInt16" Then retval="INTEGER"
If colType == "System.UInt32" Then retval="REAL"
If colType == "System.UInt64" Then retval="REAL"
If colType == "System.IntPtr" Then retval="TEXT"


Return(retval)
#EndSubRoutine


Return
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 20, 2020, 07:58:16 PM
Nice work, as always.   Agreed on the clunky insert (speaking of SQLite insert, not your script).   They don't have a bulk option in SQLite, at least I don't think so, and one needs to use a transaction to make batches of INSERTs faster.   I am sure you and most others know this but for thoroughness...

BEGIN TRANSACTION;
INSERT....;
INSERT....;
COMMIT;

I could create a batch insert but not sure how long it would take me to sort that one out :)    Will add it to my list.   Had thought about some "file" options, for OUT especially, since I am struggling getting the dbGetString() to allow for larger results.  Guess I could take the easy way out and have the batch be submitted as an Array.  Would avoid me having to parse delimited data.  Let WinBatch handle that load.  One extra step for the user and a whole lot of hours saved by me :)   The Batch Insert option would be fairly easy at that point (famous last words).

The next thing I want to get in place is UNICODE support.   Once that is done and everything is stable I will consider it ready for initial release and then continue adding new stuff.   While limited on some fronts, at that point it will allow one to do most anything they want with the database.    Need to test BLOBs I guess.   Haven't even checked on that yet.  Hopefully they work, as is.

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 20, 2020, 08:04:13 PM
I wondered since that was a big jump.   I am pleasantly surprised and pleased with the performance.  Thanks again.

Jim

Quote from: KeithW on May 20, 2020, 04:03:11 PM
Jim,

Keep in mind that the file I/O of creating Array.txt is in that time, I do believe and my full filesystem will be impacting that time as well.
I watched  array.txt swell during those 80 seconds.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 20, 2020, 08:17:26 PM
I think I misunderstood what you were saying at first.    If I went with my idea of submitting an array for insert I think that would work well with what you are doing.   Do your query in the one and I think you can easily turn that into an array.  I assume RS.GetRows() works and submit that to my Extender.   Am I getting what you want?

Jim

Quote from: stanl on May 20, 2020, 05:56:13 PM
Jim;


I modified my original script to create an SQLite db and Table populated from a tab-delimited CSV file. The attached wakecounty.csv is tab=delimited and I included a 1-liner schema.ini (I'm working on a interface to autocreate the .ini) so Ace Provider wouldn't error. The process of inserting rows from the datatable into SQLite is a little slow and clumsy to look at. Since the database/sqlite table are identical in structure maybe there is a way to INSERT INTO....FROM - or some kind of bulk copy.  Anyway, be a nice project to replace the System.Data.SQLite with your extender. There was one bug. The original csv had a column Time Zone - which although could be created in the SQLite table, put out an error during the INSERT command. I changed it to TimeZone figuring there may be a need in SQLite to enclose multi-word columns in brackets
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: stanl on May 21, 2020, 02:40:50 AM
Quote from: JTaylor on May 20, 2020, 08:17:26 PM
Do your query in the one and I think you can easily turn that into an array.


Actually oTable.Select() returns an array. Could have come up with a full schema.ini with column types pre-defined then there would be no need to work with a .NET Datatable for column types. Basically use ACE Provider to query file as Recordset where GetRows() would work. I'm more interested in parsing delimited text on the fly and only stamping a schema.ini with a delimiter. I probably should have posted the script as a separate thread so as not to hi-jack the intent here. I was just pleased to get the syntax of the Create and Insert statements correct with minimal frustration. ;)
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 21, 2020, 04:38:29 AM
No problem.  Glad you got it sorted.   I could use some "minimal frustration" myself.   Plus such things generate ideas for improving what is being done here.

Not sure how much work it would be to parse delimited data in the Extender.   Mainly thinking about embedded quotes and such.  Now, if one had a file and then did dbInsertBatch(db,table,ArrayFileGetCSV(....)), if such a function existed, that would give me a cleanly separated source with which to work or, as noted, one can usually get an array of the results from the various recordset tools.   Really can't think of a situation where it would be a hassle to provide an array.

In any event, good work on what you posted and I am off to get this UNICODE thing sorted.

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 21, 2020, 04:15:17 PM
Here is the latest.   UNICODE is still whipping my butt so didn't want to hold up the dbInsertBatch() option and @dbVersion constant.

See notes in script on new function.  Nothing will run by default.  Also, if your source is UNICODE it will load correctly.   Now if only the output did the same.

Let me know how those 3.55 mil records do...hope they don't catch on fire from the blazing speed  ;)

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

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 21, 2020, 04:59:12 PM
Jim,

While I have not yet looked at v4 did you add a function that will take a flat file (no delimiters)?

If not, what about a function that will take column settings like 1-10, 11-10, etc however if you can do
that do not assume there isn't white space that is not needed like so many program do.  I would rather
specify every column start position and column length whether fields butt up to each other of not.

If that functionality isn't there my 3.5MM is not of value yet with your SQLite Extender.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 21, 2020, 05:32:53 PM
Added a Help file to the v4 file just now.   Don't forget you will probably have to "Unblock" it under File Properties.

Also, if you have a suggestion for the SQLTemplate field indicators, let me know.  Don't really like all the braces but was having trouble thinking of something to use.

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 21, 2020, 06:07:48 PM
Jim

What about a Control string for SQLite   dbFlatFile (file_to_load_from,tablename,"FieldName!start-length,Field2!Start-length") with any undefined SQLite Fields set to NULL?  So you only specify what you are loading.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 21, 2020, 06:11:32 PM
Jim,

Between (file-to-load, ADD, tablename, ...)

add =lines to skip in file-to-load to account for headers, etc)

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 21, 2020, 07:36:55 PM
Probably good to change the Column Header option to a start row, as you suggest.   Give it a little more flexibiltiy.   Thinking of dbInsertBatch here.

Help me understand how the control string would be better than what I did?  Not disagreeing, just wanting to understand.  The current option allows you to specify which columns to load and there is a solid parser already in place or is this to allow for handling larger files which might not fit in an array?

What is "start-length"?

Jim

Quote from: KeithW on May 21, 2020, 06:07:48 PM
Jim

What about a Control string for SQLite   dbFlatFile (file_to_load_from,tablename,"FieldName!start-length,Field2!Start-length") with any undefined SQLite Fields set to NULL?  So you only specify what you are loading.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 21, 2020, 08:06:50 PM
Jim,

IN  file_to_load  "start" is starting column and for "length" is to grab for the field.
The fieldname in the file_to_load does not need to be named as you are giving physical positions
FieldName regards to field in SQLite table to receive data

Internally you would need to dbSchema the table
Set what FieldName references you are given
Set unamed columns (in SQLIte) to NULL
and process file_to_load based on the above settings

Possible ???

Keith

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 21, 2020, 08:16:41 PM
It has been a long day and I will probably get it tomorrow but if you get a chance please mock up a function call for me using the Company table and load ID_NO, NAME and AGE.

Wouldn't you want to let SQLite handle the unaddressed fields with the Default values?   I can't imagine setting all to NULL as a default would be the desired behavior by all.   Will keep that in mind though.

I am off to bed.  Thanks again for all the help here.

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 21, 2020, 09:31:40 PM
Jim,

Hopefully the attach file, data, example, etc will make things clearer for you.
If not, do not hesitate to ask for more clarification.  If you will add this it will
be a huge benny for me, as I come across this several times a year in the
work I do.

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 22, 2020, 06:35:34 AM
Okay.   Wondered if that was what you meant but was so tired my brain couldn't get any traction to follow through on the thought.

I have a plan.   We'll see where it goes.     Also going to tackle delimited files but only accept "clean" ones.   My brain almost explodes thinking about trying to parse something like:

1,""HELLO WORLD": A Programming Guide for C,C#,C++,etc.","Mr. Programmer,"aka Mr. P" Joseph"

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 22, 2020, 11:53:58 AM
Here is the latest.   The size limit on dbGetStrings()  and dbMapData() should be gone.


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

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 22, 2020, 11:57:59 AM
Jim,

I downloaded and will look at in a bit, on a client project for the next several hours.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 22, 2020, 02:31:49 PM
Added dbInsertFile().    This is a delimited text file option.  Not the Flat file.   This was warmup for the other :)

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

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 22, 2020, 02:38:42 PM
Jim,

Cool, I finished up on my project for now and now back to playing with this Lib...

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 22, 2020, 06:10:55 PM
Added dbInsertFlat().  I think you can do the "-" sign and NULL stuff via SQL without any problem.  See the examples in the test script and Help fil.

The configuration might seem a little hokey at first glance but I think it will work well and be easier to maintain.   Let me know if you disagree.

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

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 22, 2020, 07:45:22 PM
Jim,

Will explore when I get a chance, my client came right back and added to my workload.

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 23, 2020, 02:09:01 AM
Jim,

Not sure what I am doing wrong here, I dropped the header line since it did not appear that the function would parse it correctly.
I had originally set to start with Line 2, but you were trying to parse line 1 anyway.  Deleting the Header was the simple fix for now.

The file (sample included) has 42 fields per row.  The first many rows have no values in he 1st two fields, but they start later in the file.
It is returning a "Syntax Error" on what I am guessing is the 10th field in the first row BUT since there are multiple values
(in this case a date in yyyymmdd format) I cannot be sure which field it really choked on.... nothing gets written to the SQL3
database.  Maybe you can either spot something I have wrong in the setup or get the Lib to give you a bit more information
about what it does not seem to like.

After a few hours of sleep, I will look at the flat, fixed record file... basically doing almost identical to this example...just different data.

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 23, 2020, 06:37:00 AM
Not sure.  It is returning useful errors for me but not sure what it doesn't like yet.   It is failing on the 10th column no matter which row I start on.  I thought at first it was because you have 43 table columns but only 42 data columns.  Also thought it might be choking on the ID_NO because it is set to unique but most of the first column were the same.  I fixed those issues but get the exact same error.   Have to leave for a family thing so will be late today before I can get it sorted.  Sorry for the delay.

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 23, 2020, 10:20:05 AM
Jim,

Sorry about the 43 vs 42, was almost asleep and meant to chance the ID_NO to: ID_NO          INTEGER   PRIMARY KEY   AUTOINCREMENT
so that would be 43 fields  (ID plus my 42)  forgot to go back up and make that change.

I too have to run some errands today but have some other things to try when I get back.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 23, 2020, 10:28:35 AM
You are missing a quote on every holder with the number 8 in it.  8, 18, 28, 38.

That was masking the other issues.

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 23, 2020, 04:40:56 PM
Dang, another trip to the eye doctor and another pair glasses...

Thanx, Jim

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 23, 2020, 04:51:56 PM
Jim,

With the AUTOINCREMENT up front this SQLtext definition successfully loaded the 140 lines....


SQLText = "INSERT INTO BFS_Data Values "
SQLText = SQLText : "(NULL,'{{{0}}}','{{{1}}}','{{{2}}}','{{{3}}}','{{{4}}}','{{{5}}}','{{{6}}}','{{{7}}}','{{{8}}}','{{{9}}}',"
SQLText = SQLText :   "'{{{10}}}','{{{11}}}','{{{12}}}','{{{13}}}','{{{14}}}','{{{15}}}','{{{16}}}','{{{17}}}','{{{18}}}','{{{19}}}',"
SQLText = SQLText :   "'{{{20}}}','{{{21}}}','{{{22}}}','{{{23}}}','{{{24}}}','{{{25}}}','{{{26}}}','{{{27}}}','{{{28}}}','{{{29}}}',"
SQLText = SQLText :   "'{{{30}}}','{{{31}}}','{{{32}}}','{{{33}}}','{{{34}}}','{{{35}}}','{{{36}}}','{{{37}}}','{{{38}}}','{{{39}}}',"
SQLText = SQLText :   "'{{{40}}}','{{{41}}}');"

Message("SQL Template",SQLText)   ; DEBUG CODE

stmt = dbInsertFile(db, SQLText, DirScript() : "AR_ATB_JUNE_BFS1_K140.csv", 1, ",")
Message("dbInsertFile()",stmt)



Is that what you would have expected for 42 loaded files with an AUTO_ID up front?

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 23, 2020, 05:18:22 PM
Yes.   The other option is to provide the column list before "Values" and leave out that column name.  But I am sure you know that.     Just to be clear there is nothing special I do here with the templates.  It is straight SQL.  You could do things like

       INSERT INTO BFS_Data Values (NULL, '[{0}]', '[{1}]', '[{2}]', Substr('[{3}]',1,6), '[{4}]', 'Peaches', '[{6}]'/'[{7}]', '[{8}]', CASE Substr('[{9}]',length('[{9}]'),1) WHEN '-' THEN '[{9}]'*-1 END;

Also, I have posted a new version which changes the place holders to

       [{n}]

I am going to stick with that unless someone tells me SOON that it isn't a good choice.  Seems a lot easier to read.

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



Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 23, 2020, 05:50:32 PM
Jim

This is still v7 (I downloaded v8 but have not adjusted the {{{n}}} to [{n}] as of yet....
Thought you would like to have some real stats.....

CSV Load File is: 304,696KB containing 1,096,988 lines with the single header line.
the skip (starting line) DID NOT WORK, I ended up with the Header Line in my Database  !!
also  the dbSchema() function choKes on this file, process silently dies on BFS_Data table

Now here is the good news, I am using the following extender:
AddExtender("wilx44i.dll",0,"wilx64i.dll")               ; xGetElapsed

the xGetElapsed() & GetExactTime() timng functions

it loaded all (1,096,988 lns) on my full filesystem yielding a 197,460KB SQL3 DB in
1 min, 10secs & 54 hundereths

NOT BAD !!


this is the WB used for the testing....

AddExtender("wbsql44i.dll") ; SQLite3 Functions()
AddExtender("wilx44i.dll",0,"wilx64i.dll") ; xGetElapsed


db  = DirScript():"BFS_CSV_Test.sqlite"      ;INCLUDE FULL PATH FOR DATABASE
If FileExist(db) Then FileDelete(db) ;start over fresh every running

Message("Version",@dbVersion) ;show Library version

; UNIQUE   NOT NULL
;#############################################################
SQLText = $"CREATE TABLE IF NOT EXISTS BFS_Data (
ID_NO INTEGER PRIMARY KEY AUTOINCREMENT,
   RSTXTLG Text(20),
x0PMNTTRMS Text(4),
x0COMP_CODE Text(4),
x0CRED_ACCNT Text(10),
x0CRED_GROUP Text(4),
x0REPR_GROUP Text(3),
x0DEBITOR Text(10),
x0AC_DOC_TYP Text(2),
x0ITEM_NUM Text(3),
x0NETDUEDATE Text(25),
x0DOC_DATE Text(25),
x0AC_DOC_NO Int(10),
x0PSTNG_DATE Text(25),
x0DOC_CURRCY Text(3),
x0LOC_CURRCY Text(3),
ZARSNAPD Text(10),
ZARCDUE REAL(13,2),
ZARDCDUE REAL(13,2),
ZARDFDUE REAL(13,2),
ZARDPDU10 REAL(13,2),
ZARDPDUE1 REAL(13,2),
ZARDPDUE2 REAL(13,2),
ZARDPDUE3 REAL(13,2),
ZARDPDUE4 REAL(13,2),
ZARDPDUE5 REAL(13,2),
ZARDPDUE6 REAL(13,2),
ZARDPDUE7 REAL(13,2),
ZARDPDUE8 REAL(13,2),
ZARDPDUE9 REAL(13,2),
ZARDTDUE REAL(13,2),
ZARFDUE REAL(13,2),
ZARPDUE1 REAL(13,2),
ZARPDUE10 REAL(13,2),
ZARPDUE2 REAL(13,2),
ZARPDUE3 REAL(13,2),
ZARPDUE4 REAL(13,2),
ZARPDUE5 REAL(13,2),
ZARPDUE6 REAL(13,2),
ZARPDUE7 REAL(13,2),
ZARPDUE8 REAL(13,2),
ZARPDUE9 REAL(13,2),
ZARTDUE REAL(13,2) );$"

resp = dbExecute(db,SQLText)
Message("Create",resp)
;#############################################################


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



;#############################################################
;dbSchema(db,Table_Name)
;Returns Database Table Schema in a String Format
;2nd Argument(optional) is Table Name. Leave off if you want all tables.
;#############################################################
stmt = dbSchema(db,"BFS_Data")
;Message("dbSchema",stmt) ; This chokes on the configuration !!!!
;#############################################################


;#############################################################
;dbInsertFile()
; Batch Insert from a Delimited File (CLEANLY delimited, no embedded delimiters)
;  Arg 1 - STRING - Full Path for Database.
;  Arg 2   STRING - SQL Template
;  Arg 3 - STRING - Full File Path
;  Arg 4 - Int(optional)  -  Row to Start - Default 1
;  Arg 5 - Field Delimiter(optional) - Default @TAB
;
; Quote (") enclosed fields will have the quotes removed. Ex.  ("Hope") will turn into (Hope)
;
;#############################################################


SQLText = "INSERT INTO BFS_Data Values "
SQLText = SQLText : "(NULL,'{{{0}}}','{{{1}}}','{{{2}}}','{{{3}}}','{{{4}}}','{{{5}}}','{{{6}}}','{{{7}}}','{{{8}}}','{{{9}}}',"
SQLText = SQLText :   "'{{{10}}}','{{{11}}}','{{{12}}}','{{{13}}}','{{{14}}}','{{{15}}}','{{{16}}}','{{{17}}}','{{{18}}}','{{{19}}}',"
SQLText = SQLText :   "'{{{20}}}','{{{21}}}','{{{22}}}','{{{23}}}','{{{24}}}','{{{25}}}','{{{26}}}','{{{27}}}','{{{28}}}','{{{29}}}',"
SQLText = SQLText :   "'{{{30}}}','{{{31}}}','{{{32}}}','{{{33}}}','{{{34}}}','{{{35}}}','{{{36}}}','{{{37}}}','{{{38}}}','{{{39}}}',"
SQLText = SQLText :   "'{{{40}}}','{{{41}}}');"
Message("SQL Template",SQLText)
Timedelay(1)
time1 = GetExactTime()
;stmt = dbInsertFile(db, SQLText, DirScript() : "AR_ATB_JUNE_BFS1_K140.csv", 1, ",") ; <---Initial Script Testing, no headers
stmt = dbInsertFile(db, SQLText, DirScript() : "AR_ATB_JUNE_BFS1.csv", 2, ",") ; <---REAL McCOY   !!! With Headers !!
time2 = GetExactTime()
rc = xGetElapsed(time2, time1)
Message("dbInsertFile()",stmt)
;Message("Processing Complete","Lines Read:     %icount%%@CRLF%Lines Written: %ocount%%@CRLF%Elapsed: %rc%%@CRLF%----------  H: M: S. hh")
Message("Processing Complete","Elapsed: %rc%%@CRLF%----------  H: M: S. hh")


Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 23, 2020, 06:06:49 PM
Jim,

dbTables() is starting to return garbage which is causing the process to silently die....
See screenshot.... DO NOT KNOW WHAT SUDDENLY CHANGED, been running the process over & over
to see what the variation in the load times I gave you.   Thy are within a second of each other until
this corruption. now the script will not even run thru. start when I tried the dbSchema() but that is
commented out and the database get deleted for each running....any ideas?

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 23, 2020, 06:30:22 PM
Please download v8 again and give that a try.   I thought I had removed all the HEAP stuff but those two were still using it.   Guessing it is related.   I was deleting it but maybe not correctly.

jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 23, 2020, 06:59:00 PM
Jim,

The first time thru with v8 (without dbSchema() ) everything worked fine but runtime increased to 2m 37s 95 100ths
from original v7 runtimes of 1m 10s 54 100ths, not sure why the change there?

With v8, if run dbSchema() dbTables gets screwed up again.  Even after commenting out the call to dbSchema(), the corruption
for dbTable seems to continue expanding.  However the process has run twice now even with the corrupted dbTable()
return.

Run Time seems to now be holding in the 2m 42s window?????

ALSO, still not honoring the Lines to SKip, getting hear in the SQLite3 db.


Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 23, 2020, 07:14:00 PM
Jim,

I do not want to drag you away from fixing the CSV cause I think you are SOOO close.

I would like to work on the Flat File as that is really what I need at the moment but I need more
clarification on the ARRAY setup for the Flat File Processing.

Your code sample

;#############################################################
;dbInsertFlat()
;Batch Insert from a Flat, Fixed File
; Arg 1 - STRING - Full Path for Database.
; Arg 2   STRING - SQL Template
; Arg 3 - STRING - Full File Path
; Arg 4 - ARRAY  - Load Configuration Array
; Arg 5 - Int(optional)  -  Row to Start - Default 1


; Load Configuration Array - Each Column to be loaded, needs a Column in the Array
;  Row 0 -  FldName - Will not be used here for the benefit of the user.
;  Row 1 -  Begin   - Column at which the field begins
;  Row 2 -  Length  - Length of Field.
;
;Example of post-processing SQL
;   SQLText = "UPDATE ATB_DETAIL SET PDovr60 = CASE Substr(PDovr60,length(pdovr60),1) WHEN '-' THEN PDovr60*-1 END;"
;   dbExecute(db,SQLText)
;
;#############################################################


SQLText = "INSERT OR IGNORE INTO atb_detail (Trans_No, Tran_Date, Due_Date, Tr_Cass, Tr_Amt, Amt_App, Current, PD01_30, PD31_60, PDovr60) Values ('{{{0}}}','{{{1}}}','{{{2}}}','{{{3}}}','{{{4}}}',NULL,'{{{5}}}','{{{6}}}','{{{7}}}','{{{8}}}');"

;Arr = ArrayFileGetCSV("load_config.txt",0,@TAB)
;stmt = dbInsertFlat(db,SQLText,"C:\gbat\extenders\Sqlite\WBSql\atb.txt", Arr, 6)


Message("dbInsertFlat()",stmt)


Uses an array to specifiy the columns,  while you said you did not use Row(0)
You said the Row(1) was start, row(2) was length, etc...

Are you using a single dimension rows 1 & 2 first field, 3 & 4 second field ...  OR

row(1)-field1 -  dim(1) for start, dim(2) for length
row(2)-field2 -  dim(1) for start, dim(2) for length

wanting to be sure I understand how to address the parameters.

Thanx,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 23, 2020, 07:34:00 PM
Open this in excel - TAB Delimited.

Let me know if that helps.

ArrayFileGetCSV() is a great way to get what you need to submit.   Partly why I went this route.  Viewing the setup is clear, at least to me.  Open to other suggestions. 

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 23, 2020, 07:40:11 PM
Got It

Thanx,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 23, 2020, 07:42:24 PM
Please post your dbInsertFile() call.   This works fine for me so not sure what might be happening.

Jim

Quote from: KeithW on May 23, 2020, 06:59:00 PM
Jim,



ALSO, still not honoring the Lines to SKip, getting hear in the SQLite3 db.


Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 23, 2020, 07:43:05 PM
Not sure about the time increase.   I didn't change anything of which I am aware.

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 23, 2020, 07:44:15 PM
I can live with 2.5 mins for a million records
Thats still hummin along.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 23, 2020, 08:01:35 PM
Not sure if this will fix it but it works for me.  Ran it over and over again.   Didn't really do anything other than switch to UNICODE.   I looked at everything closely and, as far as I can tell, I am cleaning up after myself at every point.

I think I have UNICODE working in all the functions now.   May need a bit of tweaking on that front.   It shows properly from the array but when I write japanese to a file it gets corrupted.   French works fine though.  Guessing that isn't a real issue for what you are doing though. 

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

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 23, 2020, 09:53:36 PM
Jim,

Was playing with the FlatFile rountine... everything worked up the actual call to dbFlatFile().
dbTable(), dbSchedma() are both clean and it builds the db, I verify thru and 3rd DBTool.

On the dbFlatFile()  I get    near 'INSERT': syntax error

What I am not sure is how to go about the ID_NO (AUTOINCREMENT) specification
how do you code that in the DB_Config.txt file?

Anyway, included WB Script, Dep_Configtxt (in my case) and a Sample of ~100 transactions to load.

If you see the error of my ways, please enlighten....

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: stanl on May 24, 2020, 05:00:04 AM
Quote from: KeithW on May 23, 2020, 09:53:36 PM
Jim,

Was playing with the FlatFile rountine...
Regards,
Keith


Off-Topic: Noticed your flat file had Raleigh and Wilmington references. By any chance you reside in NC? I'm in Raleigh.
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 07:49:35 AM
Guess it may not be obvious so to clarify, the place holder number in the template corresponds to the array column.   Maybe my wording about table columns in the help might be misleading.  The placeholders must have a corresponding column in the config array.  You can also duplicate them.   That is you can use '[{0}]' in more than one place in the template.  If you aren't using a placeholder for a column such as the auto increment field you do not need to worry about anything for it in the config file.

Off to look at why your INSERT is failing.   Just wanted to get this clarification out here.

Hopefully I didn't make this too complicated.  Another reason I went this route was that I had more ambitious plans for the config file than what ended up here but realized it was overkill.   I still think it is a good way to do it.  I do appreciate the questions as it helps me with the HELP file.

Jim

Quote from: KeithW on May 23, 2020, 09:53:36 PM
Jim,

Was playing with the FlatFile rountine... everything worked up the actual call to dbFlatFile().
dbTable(), dbSchedma() are both clean and it builds the db, I verify thru and 3rd DBTool.

On the dbFlatFile()  I get    near 'INSERT': syntax error

What I am not sure is how to go about the ID_NO (AUTOINCREMENT) specification
how do you code that in the DB_Config.txt file?

Anyway, included WB Script, Dep_Configtxt (in my case) and a Sample of ~100 transactions to load.

If you see the error of my ways, please enlighten....

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 07:52:08 AM
I think the cause is that you need a semi-colon on the end of the INSERT statement.  It is failing when it tries to insert the second statement because it doesn't see it as a new statement.

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 09:37:30 AM
Jim,

You were right about the ";" as it was late at nite and I just wasn't seeing, sorry I should have caught that one myself !!

I do have a problem with the way your Config.txt file works... when transcribing  column position they SHOULD NOT BE 0 based !!!
You want to process that way internally, you should do the (-1) in your processing when looking at stuff on paper, it is "1" based !!!
Unless changed, you are gonna cause much frustration...

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 10:11:53 AM
Jim,

For the dbInsertFlat() do you have any input data limiting in place?
See attached Screenshot.

202003_1MM1918.txt is 1 Million rows
202003_2MM1918.txt is 2 Million rows
202003_550K1918.txt is 500,000 rows
202003_fmc1918.txt is 3.5 Million rows (Master File)
202003_SAM1918.txt is 100 rows (Sample for initial testing)

reason I am asking is the 202003_SAM1918.txt loads in a couple hundredths of a second and all of the others die silently !!!!


The only line in the script being changed is below....  EVERYTHING else remains the same


stmt = dbInsertFlat(db, SQLText, DirScript() : "202003_500K1918.txt", Arr, 1)



Any ideas?

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 10:13:34 AM
Jim,

I should have also included these situations are all with the v9 Extender....

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 12:37:35 PM

No intentional limits.   Works pretty much like dbInsertFile().   I will take another  look.

Jim


Quote from: KeithW on May 24, 2020, 10:11:53 AM
Jim,

For the dbInsertFlat() do you have any input data limiting in place?
See attached Screenshot.

202003_1MM1918.txt is 1 Million rows
202003_2MM1918.txt is 2 Million rows
202003_550K1918.txt is 500,000 rows
202003_fmc1918.txt is 3.5 Million rows (Master File)
202003_SAM1918.txt is 100 rows (Sample for initial testing)

reason I am asking is the 202003_SAM1918.txt loads in a couple hundredths of a second and all of the others die silently !!!!


The only line in the script being changed is below....  EVERYTHING else remains the same


stmt = dbInsertFlat(db, SQLText, DirScript() : "202003_500K1918.txt", Arr, 1)



Any ideas?

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 12:39:31 PM
I can see that...will change it to 1-based system.

Jim

Quote from: KeithW on May 24, 2020, 09:37:30 AM
Jim,

You were right about the ";" as it was late at nite and I just wasn't seeing, sorry I should have caught that one myself !!

I do have a problem with the way your Config.txt file works... when transcribing  column position they SHOULD NOT BE 0 based !!!
You want to process that way internally, you should do the (-1) in your processing when looking at stuff on paper, it is "1" based !!!
Unless changed, you are gonna cause much frustration...

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 01:13:15 PM
Quote from: JTaylor on May 24, 2020, 12:37:35 PM

No intentional limits.   Works pretty much like dbInsertFile().   I will take another  look.

Jim



Jim,

100 lines works, 1,000 lines works,  10,000 has a silent death.
While I did not try and find a threshold it lies between  1,000 - 10,000

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 01:22:18 PM
That was my bad...I found the problem and your post now makes it fairly certain I did.

1-based change is part of this as well.   I *think* UNICODE is working properly in all functions.


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

Basically, once you finish testing and make sure everything works you are trying and if no one else jumps in with suggestions or bugs I will declare this "RELEASED"!!!   :)

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 01:40:47 PM
Jim,

I meant to ask you if you could include the following....

regards @dbVersio() you display your Extender Version   100#, etc...

Could you also add the SQLite Version Info such as...

SQLite 3.31.1  or whatever the version of the Library you are using?

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 02:00:25 PM
Jim,

Whoa, we went backwards brother....

Zip file contains:
202003_SAM1918.txt        -  Sample I/P File
DEP_ATB_Mar20.sqlite      - Generated SQL3 db
Dep_Config.txt                  - Column Definitions
wb_sql_FIXED_Test.wbt   - wb script

If you view the SQL3 db you will see  [{0}]  where they don't belong and the columns are shifted.
The only thing I did to the Dep_Config.txt in ROW(1) added "1" to each starting column to get away from 0 based column reference

As I understood it, nothing else needed to be adjusted ???

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 02:23:31 PM
Just added to the "_va" zip.

   dbSQLiteVersion()


Jim


Quote from: KeithW on May 24, 2020, 01:40:47 PM
Jim,

I meant to ask you if you could include the following....

regards @dbVersio() you display your Extender Version   100#, etc...

Could you also add the SQLite Version Info such as...

SQLite 3.31.1  or whatever the version of the Library you are using?

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 02:26:22 PM
Your script has a 0 placeholder in the Template.

SQLtext = SQLtext : "VALUES ('[{0}]','[{1}]','[{2}]','[{3}]','[{4}]','[{5}]','[{6}]','[{7}]','[{8}]','[{9}]','[{10}]',"


Jim

Quote from: KeithW on May 24, 2020, 02:00:25 PM
Jim,

Whoa, we went backwards brother....

Zip file contains:
202003_SAM1918.txt        -  Sample I/P File
DEP_ATB_Mar20.sqlite      - Generated SQL3 db
Dep_Config.txt                  - Column Definitions
wb_sql_FIXED_Test.wbt   - wb script

If you view the SQL3 db you will see  [{0}]  where they don't belong and the columns are shifted.
The only thing I did to the Dep_Config.txt in ROW(1) added "1" to each starting column to get away from 0 based column reference

As I understood it, nothing else needed to be adjusted ???

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 02:27:56 PM
Now I get it....I think I misunderstood what you wanted.  I can change that too.

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 02:34:53 PM
How about this?   Sorry for the misunderstanding.   I will confess, I didn't test this one.

You will need to change templates....just don't be silly like me and change all the numbers instead of adding one on the end and deleting the first one :)


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


Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 02:35:43 PM
Jim,

Given the Choice, I would prefer BOTH of the changes....

Might I suggest you use [{0}] that as a NULL placeholder....  AUTOINCREMENT  OR FIELDS NOT TO LOAD
OR Do you really think that will be confusing???   ) meaning-nothing here....

I was originally talking about the Config.txt ROW starting column values....

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 02:47:26 PM
I think I want to stress the use of SQL for such things since it really is an SQL issue.   That is the MAIN reason for the Templates.  It allows you to do a GREAT deal during import that you might not realize is possible.  I think I posted an example earlier related to handling the Negative numbers in your flat file.   I could have provided some fixed method for column assignment but this opens a world of possibilities and avoids requests to handle custom situations.

Anything else you think would make it work smoother or better?   As you know, once I release it, behavior changes become problematic.  Obviously won't promise I will make the change   :)

Jim

Quote from: KeithW on May 24, 2020, 02:35:43 PM
Jim,

Might I suggest you use [{0}] that as a NULL placeholder....  AUTOINCREMENT  OR FIELDS NOT TO LOAD
OR Do you really think that will be confusing???   ) meaning-nothing here....

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 02:54:24 PM
Jim,

I think there will be one other small addition,
I need to run a couple tests to verify.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 03:15:01 PM
Jim,

It handled 1MM rows in51s 38hh
It handled 2MM rows in 1m 42s 62hh

When I fed it the entire file of 3.5MM rows
I got the error in the Screenshot, anything you can think of to get past this?

Regards,
Keith

PS Still think dbVersion should include SQLite Library version, too.....

Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 03:28:46 PM
I made it so you can request the SQLite version.  Had to use a function as the CONSTANTs only allow Int, as far as I could tell.  dbSQLiteVersion()

Started to say to try it in two batches with Row_To_Start but that would only work if we had a Row_To_End.

Not sure what else to try.   If it is running 2 mil rows okay I would think my code is fine but not sure why else it would choke unless maybe the file size is over 2gig???   Not sure if that would matter but maybe FileStream has a limitation???  What is the size of the file?  I will check on that as well.

If file size doesn't seem to be an issue let me add a Row_To_End option and see if it will handle it that way.   Will be a bit later though.  Need to get some supper.

I must say I am pleased with the performance.

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 03:32:13 PM
Jim,

filesize of 3.5+MM row file is: 996,661 KB

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 03:34:23 PM
Jim,

Reason we need: dbSQLiteVersion()
The Docs refer to functionality changes by versions where things are or are no longer valid/available...
We need to be able to verify what we are using when things are not as expected.

KEith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 03:45:18 PM
Jim,

A bit off topic within this thread,
I was wondering, what are you using to create your  WBSql.chm  file?

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 04:23:42 PM
Jim,

WENT BACK TO CSV TESTING

First I would like to recommend a function name change
dbInsertFile  to  dbInsertCSV

so that

dbInsertFlat makes sense, and maybe this should be  dnInsertFixed rather than flat???

would help keep things more organized thought wise as to what you are working with.


AND

We still have the header issue with current dbInsertFile() not honoring the ignore headers line(s).
Zip contians WB Script, DataFile & generate SQL3 db.

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 04:57:19 PM
Help & Manual.

Quote from: KeithW on May 24, 2020, 03:45:18 PM
Jim,

A bit off topic within this thread,
I was wondering, what are you using to create your  WBSql.chm  file?

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 05:08:28 PM
I had considered a different name for InsertFile but got distracted with everything else.  I have changed it to what you have suggested.   I think I will leave Flat alone.   I think either would work though, as you have noted.

The start row worked fine for me.  Had meant to ask if that was still an issue for you.   Will take another look.

Jim

Quote from: KeithW on May 24, 2020, 04:23:42 PM
Jim,

WENT BACK TO CSV TESTING

First I would like to recommend a function name change
dbInsertFile  to  dbInsertCSV

so that

dbInsertFlat makes sense, and maybe this should be  dnInsertFixed rather than flat???

would help keep things more organized thought wise as to what you are working with.


AND

We still have the header issue with current dbInsertFile() not honoring the ignore headers line(s).
Zip contians WB Script, DataFile & generate SQL3 db.

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 06:02:54 PM
Start Row should be resolved.  Guess I tested the wrong function before.

I added a Row_To_End parameter to dbInsertFlat() and dbInsertCSV().   It is inclusive and ignores start row.  That is, if you say start at 5 it does NOT shift 5 rows.   I could see an argument for either but opted for this choice.   The change in dbInsertCSV() is a breaking change as it moves the delimiter over.

Let me know if you can break up the 3.5 file in that fashion, successfully. 

Can't remember what else but I have addressed all the issues I know about.

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

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 06:18:28 PM
Jim,

As I am starting to work on this"cut"

I noticed you went from release B to D, never sending out a "C", correct?

Examples in the HELP file for these two InsertFile functions  (CSV & Flat) have a the incorrect
call parameters in the code examples compared to the descriptions above.

I am going to play around with the now now....

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 06:27:23 PM
Maybe not on c.   I just changed to the next from what was in my folder.  I think I started to post it and then went back and added something else.

Thanks on the Help.  I should have caught that.

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 06:42:57 PM
Jim,

Beat up the InsertCSV() and it seems stable

My test code
AddExtender("wbsql44i.dll") ; SQLite3 Functions()
AddExtender("wilx44i.dll",0,"wilx64i.dll") ; xGetElapsed

;   THIS REQUIRES vD & ABOVE OF THE SQLite3 Function Library !!!!!!!
;   ----------------------------------------------------------------

db  = DirScript():"BFS_CSV_Test.sqlite"      ;INCLUDE FULL PATH FOR DATABASE
If FileExist(db) Then FileDelete(db) ;start over fresh every running

Message("Version","Extender: " : @dbVersion : @CRLF : "    SQLite: " : dbSQLiteVersion() ) ;shows Extender/SQLite version

; ID_NO INTEGER PRIMARY KEY AUTOINCREMENT,
;#############################################################
SQLText = $"CREATE TABLE IF NOT EXISTS BFS_Data (
ID_NO INTEGER PRIMARY KEY AUTOINCREMENT,
   RSTXTLG Text(20),
x0PMNTTRMS Text(4),
x0COMP_CODE Text(4),
x0CRED_ACCNT Text(10),
x0CRED_GROUP Text(4),
x0REPR_GROUP Text(3),
x0DEBITOR Text(10),
x0AC_DOC_TYP Text(2),
x0ITEM_NUM Text(3),
x0NETDUEDATE Text(25),
x0DOC_DATE Text(25),
x0AC_DOC_NO Int(10),
x0PSTNG_DATE Text(25),
x0DOC_CURRCY Text(3),
x0LOC_CURRCY Text(3),
ZARSNAPD Text(10),
ZARCDUE REAL(13,2),
ZARDCDUE REAL(13,2),
ZARDFDUE REAL(13,2),
ZARDPDU10 REAL(13,2),
ZARDPDUE1 REAL(13,2),
ZARDPDUE2 REAL(13,2),
ZARDPDUE3 REAL(13,2),
ZARDPDUE4 REAL(13,2),
ZARDPDUE5 REAL(13,2),
ZARDPDUE6 REAL(13,2),
ZARDPDUE7 REAL(13,2),
ZARDPDUE8 REAL(13,2),
ZARDPDUE9 REAL(13,2),
ZARDTDUE REAL(13,2),
ZARFDUE REAL(13,2),
ZARPDUE1 REAL(13,2),
ZARPDUE10 REAL(13,2),
ZARPDUE2 REAL(13,2),
ZARPDUE3 REAL(13,2),
ZARPDUE4 REAL(13,2),
ZARPDUE5 REAL(13,2),
ZARPDUE6 REAL(13,2),
ZARPDUE7 REAL(13,2),
ZARPDUE8 REAL(13,2),
ZARPDUE9 REAL(13,2),
ZARTDUE REAL(13,2) );$"

resp = dbExecute(db,SQLText)
Message("Create",resp)
;#############################################################


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



;#############################################################
;dbSchema(db,Table_Name)
;Returns Database Table Schema in a String Format
;2nd Argument(optional) is Table Name. Leave off if you want all tables.
;#############################################################
stmt = dbSchema(db,"BFS_Data")
Message("dbSchema",stmt) ; This chokes on the configuration !!!!
;#############################################################


;#############################################################
;dbInsertFile()
; Batch Insert from a Delimited File (CLEANLY delimited, no embedded delimiters)
;  Arg 1 - STRING - Full Path for Database.
;  Arg 2   STRING - SQL Template.  Use '[{n}]' for placeholders. n is 1-based.
;  Arg 3 - STRING - Full File Path
;  Arg 4 - Int(optional)  -  Row to Start - Default 1
;  Arg 5 - Int(optional)  -  Row to End   - Set to -1 for All Rows.  Ignores Start Row.
;  Arg 6 - Field Delimiter(optional) - Default @TAB 
;
; Quote (") enclosed fields will have the quotes removed. Ex.  ("Hope") will turn into (Hope)
;
;#############################################################


SQLText = "INSERT INTO BFS_Data Values "
SQLText = SQLText : "(NULL,'[{1}]','[{2}]','[{3}]','[{4}]','[{5}]','[{6}]','[{7}]','[{8}]','[{9}]',"
SQLText = SQLText :   "'[{10}]','[{11}]','[{12}]','[{13}]','[{14}]','[{15}]','[{16}]','[{17}]','[{18}]','[{19}]',"
SQLText = SQLText :   "'[{20}]','[{21}]','[{22}]','[{23}]','[{24}]','[{25}]','[{26}]','[{27}]','[{28}]','[{29}]',"
SQLText = SQLText :   "'[{30}]','[{31}]','[{32}]','[{33}]','[{34}]','[{35}]','[{36}]','[{37}]','[{38}]','[{39}]',"
SQLText = SQLText :   "'[{40}]','[{41}]','[{42}]');"
;Message("SQL Template",SQLText)

Timedelay(1)
time1 = GetExactTime()
;stmt = dbInsertCSV(db, SQLText, DirScript() : "AR_ATB_JUNE_BFS1_K140.csv", 2, -1, ",") ; <---Initial Script Testing, With headers
stmt = dbInsertCSV(db, SQLText, DirScript() : "AR_ATB_JUNE_BFS1.csv", 2, 500000, ",") ; <---REAL McCOY !!! With Headers !!  Pt1
stmt = dbInsertCSV(db, SQLText, DirScript() : "AR_ATB_JUNE_BFS1.csv", 500001, -1, ",") ; <---REAL McCOY !!! With Headers !!  Pt2
time2 = GetExactTime()
rc = xGetElapsed(time2, time1)
Message("dbInsertFile()",stmt)
Message("Processing Complete","Elapsed: %rc%%@CRLF%----------  H: M: S. hh")


Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 07:15:12 PM
Jim,

dbInsertFlat() issue:

stmt = dbInsertFlat(db, SQLText, DirScript() : "202003_fmc1918.txt", Arr, 1,2000000) ; 3.5MM Live File, No Header
stmt = dbInsertFlat(db, SQLText, DirScript() : "202003_fmc1918.txt", Arr, 2000001,-1) ; 3.5MM Live File, No Header


I tried running it on the HUGE file in one operation   1,-1 (last two params and it caused a C/C++ Library Error like I gave you before.
When I ran a count of what inserted in SQL3 db, it was: 2,909,999 rows on the all in one.  When As ran it as a two part operation, I
died with exactly the same number of rows in the SQL3 db, verified by (select acct_id, count(1) from fmc_detail; ).

There has to be a buffer or variable limit, some threshold that is being hit, this file has been loaded/processed by two other packages
so I DO NOT believe there to be a hole or problem in this input file. The number of rows is: 3,580,982 !!

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 07:35:06 PM
Jim,

I had not run your demo script in the last several releases, just simply cut what I needed to continue with my test script.
I just ran it from vD and I think the screenshot I sent is not exactly what you desired to produce, correct ?

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 07:41:02 PM
Probably not.

Jim

Quote from: KeithW on May 24, 2020, 07:35:06 PM
Jim,

I had not run your demo script in the last several releases, just simply cut what I needed to continue with my test script.
I just ran it from vD and I think the screenshot I sent is not exactly what you desired to produce, correct ?

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 07:43:58 PM
What if you try dropping a few of the columns from the Template and config?   Do you get to the same row or further.  That might answer part of the question.   If a memory issue then I would think it should go further.

Jim

Quote from: KeithW on May 24, 2020, 07:15:12 PM
Jim,

dbInsertFlat() issue:

stmt = dbInsertFlat(db, SQLText, DirScript() : "202003_fmc1918.txt", Arr, 1,2000000) ; 3.5MM Live File, No Header
stmt = dbInsertFlat(db, SQLText, DirScript() : "202003_fmc1918.txt", Arr, 2000001,-1) ; 3.5MM Live File, No Header


I tried running it on the HUGE file in one operation   1,-1 (last two params and it caused a C/C++ Library Error like I gave you before.
When I ran a count of what inserted in SQL3 db, it was: 2,909,999 rows on the all in one.  When As ran it as a two part operation, I
died with exactly the same number of rows in the SQL3 db, verified by (select acct_id, count(1) from fmc_detail; ).

There has to be a buffer or variable limit, some threshold that is being hit, this file has been loaded/processed by two other packages
so I DO NOT believe there to be a hole or problem in this input file. The number of rows is: 3,580,982 !!

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 07:59:34 PM
What is weird is if I run this against the script that I use for testing as I develop it returns the data correctly.

Jim

Quote from: KeithW on May 24, 2020, 07:35:06 PM
Jim,

I had not run your demo script in the last several releases, just simply cut what I needed to continue with my test script.
I just ran it from vD and I think the screenshot I sent is not exactly what you desired to produce, correct ?

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 08:19:24 PM
Jim,

I dropped the last 2 fields, both REALs and if it made a difference memory wise over 2.9MM rows we should have picked up
at least a few more rows in the SQL3 db, but we did not, still have 2,909,999 WHICH looks like some sort of limit to me,
but what do I know, lol?

Test script attached

AddExtender("wbsql44i.dll") ; SQLite3 Functions()
AddExtender("wilx44i.dll",0,"wilx64i.dll") ; xGetElapsed(), GetExtactTime()

; THIS REQUIRES vD & later for the SQLite Function Library !!
; -----------------------------------------------------------

db  = DirScript():"DEP_ATB_MAR20.sqlite"     ;INCLUDE FULL PATH FOR DATABASE
If FileExist(db) Then FileDelete(db) ;start over fresh every running

Message("Version","Extender: " : @dbVersion : @CRLF : "    SQLite: " : dbSQLiteVersion() ) ;shows Extender/SQLite version

; ID_NO INTEGER PRIMARY KEY AUTOINCREMENT,
;#############################################################
SQLText = $"CREATE TABLE IF NOT EXISTS fmc_detail (
ID_NO INTEGER PRIMARY KEY AUTOINCREMENT,
   Acct_Id Text(10),
Title Text(4),
F_Name Text(10),
M_Name Text(10),
L_Name Text(30),
Suffix Text(4),
Cust_Id Text(9),
Addr Text(40),
City Text(20),
State Text(2),
Zip Text(9),
Mail_Rte Text(5),
   Status Text(1),
Class Text(2),
Balance REAL(13,2),
Current REAL(13,2),
C_Per_1 REAL(13,2),
C_Per_2 REAL(13,2),
C_Per_3 REAL(13,2),
C_Per_4 REAL(13,2) );$"
; Dep_Total REAL(13,2),
; Dep_Int REAL(13,2) );$"

resp = dbExecute(db,SQLText)
Message("Create",resp)
;#############################################################


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


;#############################################################
;dbSchema(db,Table_Name)
;Returns Database Table Schema in a String Format
;2nd Argument(optional) is Table Name. Leave off if you want all tables.
;#############################################################
stmt = dbSchema(db,"fmc_detail")
Message("dbSchema",stmt)
;#############################################################


;#############################################################
;dbInsertFlat()
;Batch Insert from a Flat, Fixed File
; Arg 1 - STRING - Full Path for Database.
; Arg 2   STRING - SQL Template  Use '[{n}]' for placeholders. n is 1-based.
; Arg 3 - STRING - Full File Path
; Arg 4 - ARRAY  - Load Configuration Array
; Arg 5 - Int(optional)  -  Row to Start - Default 1
; Arg 6 - Int(optional)  -  Row to End - This includes this row.  This also ignores the start row.


; Load Configuration Array - Each Column to be loaded, needs a Column in the Array
;  Row 0 -  FieldName - Will not be used here for the benefit of the user.
;  Row 1 -  Start     - Column at which the field begins
;  Row 2 -  Length    - Length of Field.

;Example of post-processing SQL
;   SQLText = "UPDATE ATB_DETAIL SET PDovr60 = CASE Substr(PDovr60,length(pdovr60),1) WHEN '-' THEN PDovr60*-1 END;"
;   dbExecute(db,SQLText)

;#############################################################

SQLText = "INSERT INTO fmc_detail (ID_NO,Acct_Id,Title,F_Name,M_Name,L_Name,Suffix,Cust_Id,Addr,City,State,Zip,"
;SQLtext = SQLText : "Mail_Rte,Status,Class,Balance,Current,C_Per_1,C_Per_2,C_Per_3,C_Per_4,Dep_Total, Dep_Int) " ; ALL !!
SQLtext = SQLText : "Mail_Rte,Status,Class,Balance,Current,C_Per_1,C_Per_2,C_Per_3,C_Per_4) " ; -2 fields
SQLtext = SQLtext : "VALUES (NULL,'[{1}]','[{2}]','[{3}]','[{4}]','[{5}]','[{6}]','[{7}]','[{8}]','[{9}]','[{10}]',"
;SQLtext = SQLtext : "'[{11}]','[{12}]','[{13}]','[{14}]','[{15}]','[{16}]','[{17}]','[{18}]','[{19}]','[{20}]','[{21}]','[{22}]');" ; ALL !!
SQLtext = SQLtext : "'[{11}]','[{12}]','[{13}]','[{14}]','[{15}]','[{16}]','[{17}]','[{18}]','[{19}]','[{20}]');" ; -2 fields
Message("SQLtext",SQLtext)

;Arr = ArrayFileGetCSV("Dep_Config_Full.txt",0,@TAB) ; No Dropped Fields - ALL !!
Arr = ArrayFileGetCSV("Dep_Config_Full-2.txt",0,@TAB) ; -2 fields (Dep_Total & Dep_Int)
Timedelay(1)
time1 = GetExactTime()
;stmt = dbInsertFlat(db, SQLText, DirScript() : "202003_10K1918.txt", Arr, 2,-1) ; 10K rows TEST with Header Row
stmt = dbInsertFlat(db, SQLText, DirScript() : "202003_fmc1918.txt", Arr, 1,2000000) ; 3.5MM Live File, No Header
stmt = dbInsertFlat(db, SQLText, DirScript() : "202003_fmc1918.txt", Arr, 2000001,-1) ; 3.5MM Live File, No Header

time2 = GetExactTime()
rc = xGetElapsed(time2, time1)

Message("dbInsertFlat()",stmt)
Message("Processing Complete","Elapsed: %rc%%@CRLF%----------  H: M: S. hh")


Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 08:34:18 PM
At a loss as to what the limiter might be.   It isn't an SQLite limit.   I am not limiting.  If it was a memory overflow one would think the row# would have changed with the fewer pieces of data.   The row count is int so it should handle 3.5 mil.   Will have to think on that and mock up a file with a few million records.   Probably won't be tonight though.

Not sure on dbGetString().   My development test database works fine.   Testing some other options now.  Do you see that behavior on your databases?

Jim

Quote from: KeithW on May 24, 2020, 08:19:24 PM
Jim,

I dropped the last 2 fields, both REALs and if it made a difference memory wise over 2.9MM rows we should have picked up
at least a few more rows in the SQL3 db, but we did not, still have 2,909,999 WHICH looks like some sort of limit to me,
but what do I know, lol?


Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 08:37:54 PM
Jim

In my database, no
Only in the one spot in your Demo Script???

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 08:50:48 PM
That is my experience as well.   May just get rid of the test script :)

Jim

Quote from: KeithW on May 24, 2020, 08:37:54 PM
Jim

In my database, no
Only in the one spot in your Demo Script???

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 24, 2020, 08:52:22 PM
I just loaded 4.2 million records via InsertCSV().  Only had 5 columns though.   Will try an InsertFlat file.

Jim

Quote from: KeithW on May 24, 2020, 08:19:24 PM
Jim,

I dropped the last 2 fields, both REALs and if it made a difference memory wise over 2.9MM rows we should have picked up
at least a few more rows in the SQL3 db, but we did not, still have 2,909,999 WHICH looks like some sort of limit to me,
but what do I know, lol?



Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 24, 2020, 10:32:42 PM
Jim,

I just wrote a script to reformat the 3.5MM row file to a true CSV
Will then try to import the CSV

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 12:05:15 AM
Jim,


***   ***   ***   ***   STOP THE PRESSES !!!!!

It works !!!  Turns out there was a buried (0x1A) at 2,914,421 lines in, first character of the M_Name field.
Both the other products I use must be doing some sort of buffered/block I/O and they just picked it up and
moved it right on down the line. Never bothered to let out a peep... obviously the way you are reading and
WB in general, since my FLAT->CSV conversion died at that line which is how I knew there was an issue.

Fired up UltraEdit(UltraStudio) jumped to that line, went to HEX mode and there it was, did a HEX Edit of x1A to x20
and ran the process again, ALL Fields, and the entire 3,580,982 rows  TXT (996,661 KB) converted to SQL3
419,740 KB in 3m 16s 39hh  which is really nice cause it took about an hour to build before using the other tools.

This isn't the first time I have run into this, but usually one of the other processes craps out and I know I have
to fix the file.... this time neither had an issue and I have already done that monthly process two (2) times
without any issues so I had no reason to believe the I/P file was compromised.

I am gonna need to come up with a utility to can catch those x1A's, tell me where they are in the file (line/byte wise)
and give me the option to plug them to spaces (x20) so this does not happen again.  Obviously the utility cannot be
confused by them.

Sorry for putting you thru a wringer, certainly was not my intention, again I apologize !!!!!

Other than that weird GetString() issue... the only other thing I would like to see is some cleanup to the Doc File
code samples that are complete and scripts that are nicely formatted,  sorry I managed a group of Tech Writers
years ago for some Document Management Software and can get pretty picky about documentation.

Regards
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 06:24:30 AM
Could you post a small sample of the file with that character included?

What do you think most people would want to do with such a character?   Make it a question mark so it is somewhat obvious?   Make it a blank space?  Remove it?   Return an Error?

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 07:37:02 AM
Jim,

Attached is an extract of the file containing the embedded x1A ...  in the example it is on line 22
which in reality is line 2,914,422 of the real file, out of 3,580,982 lines in total.
The file is generated on a Mainframe with custom written software, beyond that I do not know
the specifics of generation.

This happens to me a few times a year from a couple different clients and most of the time I
simply throw out the x1A (EOF) with a x20 (space) however I DO look at it in context to make
sure I am not making the problem worse.  In this case it is a FIXED Length Flat file so just removing
the character would screw up the file from that point back.  Not always the case though.

As an autofix I would like to see a utility replace the embedded character with a SPACE personally
and give me the   Line / Character Position where the substitution was made.  That would allow me
to verify the integrity of the change so yes it would have to keep track of the file rather than just
doing a global search & replace.  Others may have other issues, concerns or requirements.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 07:56:06 AM
Here is the latest.  I backed off my attempt to make dbGetString() handle Unicode which fixes the one issue.   The annoying thing is I shouldn't have to do what I was doing to get to show Unicode, as far as I could tell from documentation.   Can't see what is different from what I do in other places where it handles Unicode.

I cleaned up the documentation a bit.   It probably won't get much better :)

dbInsertBatch() is now dbInsertArray()

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

If dbInsertArray doesn't work download again.


Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 07:59:17 AM
Isn't x1A (SUB)stitue   That is, it doesn't know what to do with the character it found so it puts in this character instead?

Jim


Quote from: KeithW on May 25, 2020, 07:37:02 AM
Jim,

Attached is an extract of the file containing the embedded x1A ...  in the example it is on line 22
which in reality is line 2,914,422 of the real file, out of 3,580,982 lines in total.
The file is generated on a Mainframe with custom written software, beyond that I do not know
the specifics of generation.

This happens to me a few times a year from a couple different clients and most of the time I
simply throw out the x1A (EOF) with a x20 (space) however I DO look at it in context to make
sure I am not making the problem worse.  In this case it is a FIXED Length Flat file so just removing
the character would screw up the file from that point back.  Not always the case though.

As an autofix I would like to see a utility replace the embedded character with a SPACE personally
and give me the   Line / Character Position where the substitution was made.  That would allow me
to verify the integrity of the change so yes it would have to keep track of the file rather than just
doing a global search & replace.  Others may have other issues, concerns or requirements.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 08:11:15 AM
Without looking it up....  Ctrl-Z  EOF I believe.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 08:41:21 AM
Okay.  I have trapped it and am returning info as the return value.  I need to change that to an error but have to answer a question first.  This is only stop gap.

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

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 10:06:45 AM
Okay.  This contains error trapping for the "empty" characters in a file, with location information as well as, hopefully, useful error responses for SQL Errors.

I do not try to do anything with the data if it is something that will crash the Extender or WinBatch, I just return an error with the location information.   Anything else seemed like a no-win situation and, probably, inappropriate.   Binary functions might be a useful tool for checking/fixing any problems immediately prior to loading.

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

Any other issues?

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 10:34:03 AM
Jim,

Releases F & G DO NOT work on my system
Your test script dies silently with the Creation of the DB, none of the rest of your tst script even runs.

None of my scripts run past that point either.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 10:37:17 AM
Jiim,

Take that back.....

In Ver F your test script run fine, the GetStrings() issues sees to be resolved too.

My scripts DO RUN,
but NOTHING works in G !!

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 10:56:15 AM
Jim,

Release F  DID TRAP the error,  I guess the message of Data is Empty, while true might just as well be
Unexpected Data  (in this case attempted to read past EOF)...

I am sure there are other conditions that I have seen over the past 20+ years of doing this BUT this exact
condition occurs by far the most.  Occasionally, I have seen some binary zeros (x00 - NULL) dumped into the
file by accident. Cannot think of what others I have encountered.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 10:59:57 AM
Jim,

Release F & G show the same Extender Version (1009)

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 11:25:57 AM
Documentation Notes as of Release G

Things are looking much better....

In the Contents (left pane) stills shows  dbInsertBatch  for right pane item dbInsertArray
For dbInsertCSV  & dbInsertFlat the description is identical to dbInsertArray ???

One thing that many do not do but I really like seeing, is putting the version number in the Docs
on the Intro page in your case such as the following.  YES, I know it is another place to touch/update.

This Help File is for Extender Version 1009 and respresents what is capable of doing.
It also supports SQLite Version 3.31.1 (See sqlite.org for SQL implementation documentation)

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 11:41:35 AM
Jim,

Regarding returning additional info from query's, etc...

Can that be done with @Tags?

@SQLrows - how may rows effected by last query, if it had to be a function SQLrows(value) where 0 is last and -1, -2 are previous queries

@SQLerror - sql error number/description ... maybe including non-fatal warnings ???

could some things be handled this way and leave the "resp" as it is today?

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 12:45:43 PM
That was weird.  I know what caused it but not why.   See if this gets you back in business.

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

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 12:46:55 PM
In any event, I really don't think it would be appropriate for me to start tweaking people's data.   Leave it to them to sort it out.   I did change the message.

Jim

Quote from: KeithW on May 25, 2020, 10:56:15 AM
Jim,

Release F  DID TRAP the error,  I guess the message of Data is Empty, while true might just as well be
Unexpected Data  (in this case attempted to read past EOF)...

I am sure there are other conditions that I have seen over the past 20+ years of doing this BUT this exact
condition occurs by far the most.  Occasionally, I have seen some binary zeros (x00 - NULL) dumped into the
file by accident. Cannot think of what others I have encountered.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 01:06:57 PM
If I could get it to tell me how many rows were affected I could do something like that.   I figured out how to set the value of a WIL variable from within the Extender.   The problem is, I have yet to beat that part of SQLite into submission.  It is on my todo list.    I don't know of any "warnings" that sqlite gives out.

Jim

Quote from: KeithW on May 25, 2020, 11:41:35 AM
Jim,

Regarding returning additional info from query's, etc...

Can that be done with @Tags?

@SQLrows - how may rows effected by last query, if it had to be a function SQLrows(value) where 0 is last and -1, -2 are previous queries

@SQLerror - sql error number/description ... maybe including non-fatal warnings ???

could some things be handled this way and leave the "resp" as it is today?

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 01:23:22 PM
Jim,

I ran with the bad character in the input file and it stopped with a useable Error Msg.
Then I ran with a cleaned file and while it built the file completely, the script died silently and never returned the RESP = 0 message,
running it again to see it that was a fluke?  Did it a second time, did not return, but file was released just the same?


Would you consider crafting a text file analyzer that could be run on large text files (CSV or Flat) that would warn of any potential bad
characters  ( <x20  or  >x7F with the exception of @CR or @LF.    Giving the Line and Col like you were previously in a list of any such
occurrences thru-out the file in one pass, rather than have to possibly run multiple times to proclaim a clean file?

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 01:40:40 PM
Jim,

Possible typo in Hep File?

Isn't the SQL Version 3.31.1  or   are you really back at 3.11.1?

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 01:44:44 PM
Hmmmmmmm...must be some other character that it doesn't like but which isn't seen as an "empty" string.

Did it load any rows?   If so, can you send me the rest of the file?

Jim

Quote from: KeithW on May 25, 2020, 01:23:22 PM
Jim,

I ran with the bad character in the input file and it stopped with a useable Error Msg.
Then I ran with a cleaned file and while it built the file completely, the script died silently and never returned the RESP = 0 message,
running it again to see it that was a fluke?  Did it a second time, did not return, but file was released just the same?


Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 01:55:00 PM
My Bad.   I sat there looking at that for a while thinking there was something wrong but couldn't figure out what so moved on.

Jim

Quote from: KeithW on May 25, 2020, 01:40:40 PM
Jim,

Possible typo in Hep File?

Isn't the SQL Version 3.31.1  or   are you really back at 3.11.1?

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 02:00:01 PM
I really feel like I would be depriving you of a learning experience if I were to do this...

This is from BinaryReplace() in the Help file.
Code (winbatch) Select

str="hello"
rep="goodbye"
dafile="C:\Temp\myfile.txt"
fs = FileSize( dafile )
binbuf = BinaryAlloc( fs+100 )
ret = BinaryRead( binbuf, dafile )
num = BinaryReplace( binbuf, str, rep ,0)
Message( "Number of '%str%' strings replaced", num )
BinaryWrite( binbuf, dafile )
BinaryFree( binbuf)
Exit


If your files are too large for one Buffer,  you can search for "Split Large Files" in the Tech Database and go to the "WinBatch Samples from Users" section you will find some code that might be useful.  If you want to get really fancy I can post some code that reads into a buffer a chunk at a time but it is a little more involved.

Jim

Quote from: KeithW on May 25, 2020, 01:23:22 PM
Jim,

Would you consider crafting a text file analyzer that could be run on large text files (CSV or Flat) that would warn of any potential bad
characters  ( <x20  or  >x7F with the exception of @CR or @LF.    Giving the Line and Col like you were previously in a list of any such
occurrences thru-out the file in one pass, rather than have to possibly run multiple times to proclaim a clean file?

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 02:15:03 PM
Jim,

Tried a couple more times BUT it still dies a silent death.

It DOES COMPLETELY LOAD THE SQLITE3 db !!
Just that the call from dbInsertFlat() never returns to the WB script.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 02:32:06 PM
Jim,

I grabbed the BinaryReplace example you posted
Also the Split Large Text File from the Tech Support DB

If you will be as kind as to post the other code you mentioned I will sit down and try to get a working program
put together...

Thanx,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 02:48:34 PM
This came out of a program I wrote that would take LARGE XML files (40gb+) and split them up.   (Detlev Dalitz helped with some of the heavy lifting)  So you will need to do a little translation of the code.  When you see variables related to "node" it is something that would be defined such as "<Books>", "<Items>", etc.  so it could chop up the XML but make each a complete, valid, XML file.  Hope this make sense.


chunk_size is in mb. 
split_file is file.
Node stuff will need changed to look for what you are using for split criteria and what you want to place at end of split files.



You will need --  AddExtender ("WWHUG44i.DLL")

Code (winbatch) Select


#DefineFunction UDFCvtFloatToHuge(f)

  fupper=strupper(f + 0.0)
  f1=ItemExtract(1,fupper,"E")
  f2=ItemExtract(2,fupper,"E")
  If f2 == "" then Return(f1)
   
  neg=@false
  If f1<0
    neg=@TRUE
    f1 = -f1
  EndIf

  f1=StrReplace(f1,".","") ; remove .

  If f2<0
    f1=strcat("0.",strfixleft(f1,0,-(f2)+strlen(f1)-1))
  Else
    f1=strfix(f1,0,max(strlen(f1) , f2+1))
  EndIf

  If neg==@FALSE then
    Return(f1)
  Else
    Return (strcat("-",f1))
  EndIf

#EndFunction

#DefineSubRoutine BinSplit()

   split_file_size = FileSize(split_file)
   If ItemCount(split_file_size,"e") > 1 Then
     split_file_size = UDFCvtFloatToHuge(split_file_size)
   EndIf

   chunk_size_byte = huge_Multiply(1 << 20, chunk_size) 
   chunk_size_byte = huge_Add(chunk_size_byte,StrLen(root_node))
   chunk_size_byte = huge_Add(chunk_size_byte,StrLen(root_node_end))

   If StrSub(huge_Subtract(split_file_size,chunk_size_byte),1,1) == "-" Then
     Message("Note","Chunk Size is bigger than the file.  Please select a smaller chunk size and try again.")
     Return
   EndIf

   big_binary_buffer = BinaryAlloc(chunk_size_byte) ; Physical memory is the limit.
   chunk_size_byte = huge_Subtract(chunk_size_byte,StrLen(root_node))

   root_node_offset = 0
   file_count = 0
   Sample_File()
   split_file_offset = 0

   While @TRUE
     file_count = file_count + 1
     If IsKeyDown(@SHIFT & @CTRL) Then Break
     bytes_read = "" : BinaryReadEx(big_binary_buffer, root_node_offset, split_file, split_file_offset, chunk_size_byte)

     If file_count == 1 Then
       bytes_read_save = bytes_read
       root_node_offset = StrLen(root_node_end)-1
     Else
       BinaryPokeStr(big_binary_buffer,0,root_node)
     EndIf
 
     inode_offset = BinaryIndexEx(big_binary_buffer, chunk_size_byte-1, item_node_end, @BACKSCAN, @TRUE)
     If inode_offset == -1 Then
       Message("Note","Node not found")
       Break
     EndIf
     inode_offset = inode_offset + StrLen(item_node_end)
     inode_diff = huge_Subtract(chunk_size_byte,inode_offset)

     BinaryEODSet(big_binary_buffer,inode_offset)+StrLen(root_node_end)
     BinaryPokeStr(big_binary_buffer, inode_offset, root_node_end)
     output_file = output_dir:output_base2:"_":StrFixLeft(file_count,"0",output_counter):".":output_ext2

     If bytes_read < bytes_read_save Then BinaryEODSet(big_binary_buffer,bytes_read+root_node_offset)

     If IsKeyDown(@SHIFT & @CTRL) Then Break
     BinaryWrite(big_binary_buffer,output_file)
 
     DialogProcOptions(SPL_Handle, 1003,"XML Splitter - Chunk#: ":file_count)
     DialogControlSet(SPL_Handle,"ib_SPL_output_files",dc_itemboxadd,ItemExtract(-1,output_file,"\"))
     DialogControlSet(SPL_Handle,"ib_SPL_output_files",dc_itemscrollpos,-1)

     split_file_offset_save = split_file_offset
     If file_count == 1 Then
       split_file_offset = huge_Add(split_file_offset, chunk_size_byte)
     Else
       split_file_offset = huge_Add(split_file_offset, huge_Subtract(chunk_size_byte,StrLen(root_node)))
      EndIf
     split_file_offset = huge_Subtract(split_file_offset, inode_diff)

     If IsKeyDown(@SHIFT & @CTRL) Then Break

     If StrSub(huge_Subtract(split_file_offset, split_file_size),1,1) == "-" || huge_Subtract(split_file_offset, split_file_size) == 0 Then Continue

     Break

   EndWhile
     
   big_binary_buffer = BinaryFree (big_binary_buffer)
   
   WinHide("")

#EndSubRoutine




Quote from: KeithW on May 25, 2020, 02:32:06 PM
Jim,

I grabbed the BinaryReplace example you posted
Also the Split Large Text File from the Tech Support DB

If you will be as kind as to post the other code you mentioned I will sit down and try to get a working program
put together...

Thanx,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 03:02:41 PM
Jim,

OK, thanx, now that my eyes have glazed over I will see where I get with it.
The first version may be slow and awkward BUT given time I will prevail.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 03:24:20 PM
Not sure what to do on this one.  I just loaded 4.2million rows and it worked as expected.   Any possibility of downloading the file you are using?   If there is a link and you want to PM me I could try it here.

Jim

Quote from: KeithW on May 25, 2020, 02:15:03 PM
Jim,

Tried a couple more times BUT it still dies a silent death.

It DOES COMPLETELY LOAD THE SQLITE3 db !!
Just that the call from dbInsertFlat() never returns to the WB script.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 04:12:29 PM
Jim,

Here is the current load script to go with the file I sent you.
The Config file is attached, too.

The BIG Data File is the original client file with the x1A in line 2,914,421 at column 11

I have run it WITH and WITHOUT fixing it.


Keith



AddExtender("wbsql44i.dll") ; SQLite3 Functions()
AddExtender("wilx44i.dll",0,"wilx64i.dll") ; xGetElapsed(), GetExtactTime()

; THIS REQUIRES vD & later for the SQLite Function Library !!
; -----------------------------------------------------------

db  = DirScript():"DEP_ATB_MAR20.sqlite"     ;INCLUDE FULL PATH FOR DATABASE
If FileExist(db) Then FileDelete(db) ;start over fresh every running

Message("Version","Extender: " : @dbVersion : @CRLF : "    SQLite: " : dbSQLiteVersion() ) ;shows Extender/SQLite version

; ID_NO INTEGER PRIMARY KEY AUTOINCREMENT,
;#############################################################
SQLText = $"CREATE TABLE IF NOT EXISTS fmc_detail (
ID_NO INTEGER PRIMARY KEY AUTOINCREMENT,
   Acct_Id Text(10),
Title Text(4),
F_Name Text(10),
M_Name Text(10),
L_Name Text(30),
Suffix Text(4),
Cust_Id Text(9),
Addr Text(40),
City Text(20),
State Text(2),
Zip Text(9),
Mail_Rte Text(5),
   Status Text(1),
Class Text(2),
Balance REAL(13,2),
Current REAL(13,2),
C_Per_1 REAL(13,2),
C_Per_2 REAL(13,2),
C_Per_3 REAL(13,2),
C_Per_4 REAL(13,2),
Dep_Total REAL(13,2),
Dep_Int REAL(13,2) );$"

resp = dbExecute(db,SQLText)
Message("Create",resp)
;#############################################################


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


;#############################################################
;dbSchema(db,Table_Name)
;Returns Database Table Schema in a String Format
;2nd Argument(optional) is Table Name. Leave off if you want all tables.
;#############################################################
stmt = dbSchema(db,"fmc_detail")
Message("dbSchema",stmt)
;#############################################################


;#############################################################
;dbInsertFlat()
;Batch Insert from a Flat, Fixed File
; Arg 1 - STRING - Full Path for Database.
; Arg 2   STRING - SQL Template  Use '[{n}]' for placeholders. n is 1-based.
; Arg 3 - STRING - Full File Path
; Arg 4 - ARRAY  - Load Configuration Array
; Arg 5 - Int(optional)  -  Row to Start - Default 1
; Arg 6 - Int(optional)  -  Row to End - This includes this row.  This also ignores the start row.


; Load Configuration Array - Each Column to be loaded, needs a Column in the Array
;  Row 0 -  FieldName - Will not be used here for the benefit of the user.
;  Row 1 -  Start     - Column at which the field begins
;  Row 2 -  Length    - Length of Field.

;Example of post-processing SQL
;   SQLText = "UPDATE ATB_DETAIL SET PDovr60 = CASE Substr(PDovr60,length(pdovr60),1) WHEN '-' THEN PDovr60*-1 END;"
;   dbExecute(db,SQLText)

;#############################################################

SQLText = "INSERT INTO fmc_detail (ID_NO,Acct_Id,Title,F_Name,M_Name,L_Name,Suffix,Cust_Id,Addr,City,State,Zip,"
SQLtext = SQLText : "Mail_Rte,Status,Class,Balance,Current,C_Per_1,C_Per_2,C_Per_3,C_Per_4,Dep_Total, Dep_Int)
SQLtext = SQLtext : "VALUES (NULL,'[{1}]','[{2}]','[{3}]','[{4}]','[{5}]','[{6}]','[{7}]','[{8}]','[{9}]','[{10}]',"
SQLtext = SQLtext : "'[{11}]','[{12}]','[{13}]','[{14}]','[{15}]','[{16}]','[{17}]','[{18}]','[{19}]','[{20}]','[{21}]','[{22}]');"
Message("SQLtext",SQLtext)

Arr = ArrayFileGetCSV("Dep_Config_Full.txt",0,@TAB) ; No Dropped Fields - ALL !!
Timedelay(1)
time1 = GetExactTime()
;stmt = dbInsertFlat(db, SQLText, DirScript() : "202003_10K1918.txt", Arr, 2,-1) ; 10K rows TEST with Header Row
stmt = dbInsertFlat(db, SQLText, DirScript() : "202003_fmc1918.txt", Arr, 1,-1) ; 3.5MM Live File, No Header

time2 = GetExactTime()
rc = xGetElapsed(time2, time1)

Message("dbInsertFlat()",stmt)
Message("Processing Complete","Elapsed: %rc%%@CRLF%----------  H: M: S. hh")
EXIT


Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 06:02:43 PM
If I fix the the "SUB" character it runs through as it should for me so not sure how to fix something I can't reproduce.

I tried seeing what happens if I replaced that character on the fly.  Oddly enough the next column crashes WinBatch and nothing I tried made it act any different.  Tried several different approaches.  Very weird.

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 06:40:42 PM

You are running it from the WinBatch Studio, correct?

Just tried it again and it again died silently BUT built the SQL3 db in its entirety.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 07:02:24 PM
No.   I almost never use Studio.

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 07:06:19 PM
Jim,

OK on No Studio...

I tried compiling and it complained about Syntax Error on Line 87  missing the final space & quote ....
fixed that, tried Studio again, it had not complained at all, still died silently.  So I recompiled and
ran the compiled file.... it also created the SQL3 db in it entirety, but still no  RESP from the dbInsertFlat().

Strange...

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 07:47:34 PM
Can't say this will help but did make an educated change to see.   I didn't change the help file.

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

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 08:12:56 PM
Jim,

Made a good Educated Change...

Both WbStudio & Compiled completed properly, did not die silently like before.

Still need to update the SQLite Version to 3.31.1 in help
Otherwise, up to you want you want to do next.

Thanx for the long hours of torture !!!
Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 08:18:59 PM
Wall Run Times are almost a minute longer BUT that might be do to the additional gigs I added to my filesystem with today's debugging issues.
Still I will take the 6 mins over almost an hour prior to this exercise.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 08:20:32 PM
Excellent. 

I did make that change.  Thought it would have made it that way by now but it has been changed so will show up eventually.

Probably take another crack at making the Unicode thing consistent with dbGetString() but will, most likely, post a "Release" version sometime soon either way.


Thanks again for all the testing and feedback and to all the others as well who have given it a go.    If there are any more suggestions regarding current functionality, please make them soon.

Jim

Quote from: KeithW on May 25, 2020, 08:12:56 PM
Jim,

Made a good Educated Change...

Both WbStudio & Compiled completed properly, did not die silently like before.

Still need to update the SQLite Version to 3.31.1 in help
Otherwise, up to you want you want to do next.

Thanx for the long hours of torture !!!
Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 08:26:38 PM
Jim,

If you are looking for work, lol...

It would be nice to add:  dbInsertExcel  (xls, xlsx - multi tab support)
Also an OutPut Version of this  dbPutExcel ... just stack on the feature request list for the future.

Thanx,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 08:34:31 PM
You will want to update again....sorry...noticed something when following through on the previous change.

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


Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 08:36:00 PM
I wouldn't hold my breath on that one :)   You can easily output a delimited format and do the Excel part through Winbatch.  No need to recreate the wheel.

Jim


Quote from: KeithW on May 25, 2020, 08:26:38 PM
Jim,

If you are looking for work, lol...

It would be nice to add:  dbInsertExcel  (xls, xlsx - multi tab support)
Also an OutPut Version of this  dbPutExcel ... just stack on the feature request list for the future.

Thanx,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 08:46:58 PM
FYI   Help File & Extender versions out of sync.

Verified  WBStudio operation - runtime back to where it was originally, under 4 mins
Verified  Compiled  operation - runtime in the same range as above

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 25, 2020, 08:58:15 PM

TO EVERYONE INTERESTED IN NATIVE SQLITE3 ACCESS FROM A WINBATCH SCRIPT

Jim has put in an enormous effort in the past 8 days to make this happen.  I feel grateful to have been a part of this effort
to get here and I know I will benefit hugely from having this available.  If you even think you might remotely have a need
for this, now if the time to get onboard and be a part of getting this Extender whipped up to be of use to as many as could
use it.  For NOW until a format release is made be sure to use the vJ release OR LATER to benefit from our learning experience
to date and drop Jim a note of thanx !!!!!!!!!!!!!!!!!!!!!!!

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 25, 2020, 09:38:58 PM
Thanks, even though I am sure it won't be the last time  :)

Jim

Quote from: KeithW on May 25, 2020, 08:46:58 PM
FYI   Help File & Extender versions out of sync.

Verified  WBStudio operation - runtime back to where it was originally, under 4 mins
Verified  Compiled  operation - runtime in the same range as above

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 27, 2020, 09:45:54 AM
Jim,

Is there *ANY* chance you will fix  dbInsertCSV  to allow for embedded delimiters  (quote-mode)  ,"ABC Co, Inc",
where the function understand this example properly?

I did a search on my BIG file and  I have @TAB (for field) and "|" for quoting and maybe one other
special character out of all the ones I searched for... this is brutal.  If someone uses a "|" in the data
I think I am screwed.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 27, 2020, 11:33:36 AM
Just thinking about embedded delimiters makes my brain ache with all the possibilities.   Perhaps at some point but there is another thing higher on my list.    Nag me again later on.   If you run into trouble before that point let me know.   With some creative use of StrReplace(), ArrayFileGetCSV(), ArrayFilePutCSV() and SQL we can overcome most anything.


Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 27, 2020, 12:40:46 PM
Jim,

I got the Binary functions to scout the I/P file and let me know if there are any issues... at least for what it tests for at this time.

Before all is said and done... are you going to compile/provide a 64b version of WBSql?

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 27, 2020, 01:10:09 PM
Excellent.

I wondered how long it would be before I got the 64-bit question.   I would happily do that if I knew what was needed.   Someone wanted my CommControl Extender in 64-bit but I didn't know how so they hired one or two people to do it but that went nowhere.   I don't think it is really that complicated but I have no idea what the difference between the two might be.   I remember a couple of things being mentioned in the past but don't recall much now.   Int sizes and such, I think.     Some references in SDK but not enough for me to know where to even start.   I have been reading through a good article on the differences though.

Basically I wouldn't hold your breath.   Hopefully I can figure it out at some point but no idea when.   The other guy has been waiting 7 years, give or take  :)     I will put it on my list.   I understand a lot more now than then so there is hope.

Jim




Quote from: KeithW on May 27, 2020, 12:40:46 PM
Jim,

I got the Binary functions to scout the I/P file and let me know if there are any issues... at least for what it tests for at this time.

Before all is said and done... are you going to compile/provide a 64b version of WBSql?

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 27, 2020, 01:39:05 PM
Jim,

HOUSTON WE HAVE AN ISSUE.... LOL

We need to add a Param7 on the dbInsertCSV() called quoting character
that will allow the specification of any *possible* quoting character rather than forcing only (")
cause if it isn't a " it gets load into the db....

PLEASE, I have almost 10,000 "s in the file and I would have to research each one before I could make
a change to replace them... be MUCH EASIER to specify a different quoting char.  This still does not resolve
handling embedded quoting chars, just side steps that issue.

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: td on May 27, 2020, 02:36:53 PM
Quote from: JTaylor on May 27, 2020, 01:10:09 PM
Excellent.

I wondered how long it would be before I got the 64-bit question.   I would happily do that if I knew what was needed.   Someone wanted my CommControl Extender in 64-bit but I didn't know how so they hired one or two people to do it but that went nowhere.   I don't think it is really that complicated but I have no idea what the difference between the two might be.   I remember a couple of things being mentioned in the past but don't recall much now.   Int sizes and such, I think.     Some references in SDK but not enough for me to know where to even start.   I have been reading through a good article on the differences though.

Basically I wouldn't hold your breath.   Hopefully I can figure it out at some point but no idea when.   The other guy has been waiting 7 years, give or take  :)     I will put it on my list.   I understand a lot more now than then so there is hope.

Jim

Some extenders ported to 64-bit with only a few very minor changes.  Others took a lot of recoding.  Generally, the newer extenders ported with almost no changes partially because they are coded in C++ and follow good code practices.  It can be said that the WIL SDK C++ Extender template ports without issue. 
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 27, 2020, 05:30:35 PM
Okay.   Switched to x64 and tried compiling but it wanted altbase.h and after copying that in it wanted a bunch of others.   Also, didn't like a lot of the casting but this gives me a place to start.  Sounds like there is nothing Extender related that needs to be changed, just the parts I added.   That is helpful to know.

Thanks.

Jim

Quote from: td on May 27, 2020, 02:36:53 PM

Some extenders ported to 64-bit with only a few very minor changes.  Others took a lot of recoding.  Generally, the newer extenders ported with almost no changes partially because they are coded in C++ and follow good code practices.  It can be said that the WIL SDK C++ Extender template ports without issue.
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 27, 2020, 05:47:50 PM
Jim,

I got the following on a bad spec for Creating A Table, but it needs a little help
not just a popup like this ???

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 27, 2020, 06:44:12 PM
Sorry about that.  Not sure how that got left in there.   I didn't take time to change any version info.   In the middle of something else but didn't want you to have to wait.

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

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 27, 2020, 07:19:25 PM
Here are things with updated version info.

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

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 27, 2020, 07:20:55 PM
Help me understand what data you are receiving that provides CSV quotes that are not double-quotes.   Want to make sure I am allowing for the right thing.

Jim



Quote from: KeithW on May 27, 2020, 01:39:05 PM
Jim,

HOUSTON WE HAVE AN ISSUE.... LOL

We need to add a Param7 on the dbInsertCSV() called quoting character
that will allow the specification of any *possible* quoting character rather than forcing only (")
cause if it isn't a " it gets load into the db....

PLEASE, I have almost 10,000 "s in the file and I would have to research each one before I could make
a change to replace them... be MUCH EASIER to specify a different quoting char.  This still does not resolve
handling embedded quoting chars, just side steps that issue.

Regards,
Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 27, 2020, 07:27:43 PM
Jim,

The data has "," and embedded " in the file....
I wanted to change the field delimiter to TAB  and the quoting character to "|" in order to get away for the existing issues.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 27, 2020, 07:47:59 PM
If you have a clean delimiter the quotes shouldn't be an issue, unless I am misunderstanding?    It will remove the quotes from around the data but it only does this if the first and last characters are quotes.  If it is working as I intended, any quotes in your data should be valid.   Of course, I may have screwed something up.    Does this solve your problem?

I am going to sign off soon.

Jim

Quote from: KeithW on May 27, 2020, 07:27:43 PM
Jim,

The data has "," and embedded " in the file....
I wanted to change the field delimiter to TAB  and the quoting character to "|" in order to get away for the existing issues.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 27, 2020, 07:50:44 PM
Well when I used "|" as the quoting character, they did not get stripped and ended up in the database.
Why can't that character be redefined?

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 27, 2020, 08:08:26 PM
Not saying I can't but just want to make sure I understand the need and that it is needed.    It sounds like you are having to change the data to get the | as the "quote" character.  If your data item is enclosed in double-quotes, even if there is an embedded quote, it should remove them correctly and leave the embedded quotes in place.   I just want to make sure it is clear how it is working and that there is a real need for the request because in the handling of CSV files, as I understand them, having quotes that are not "quotes" would be very non-standard and I don't think I have ever seen such an option provided.   Of course, requiring clean field separators might be non-standard as well.   Not saying no, just clarifying whether it is needed.

I am off for the night.

Jim

Quote from: KeithW on May 27, 2020, 07:50:44 PM
Well when I used "|" as the quoting character, they did not get stripped and ended up in the database.
Why can't that character be redefined?

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 28, 2020, 10:20:30 AM
This allows one to specify a different "quote" character for the CSV import.

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

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: KeithW on May 28, 2020, 10:31:22 AM
Jim,

thanx !!  will test in a little bit, working on some client projects for now.

Keith
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: kdmoyers on May 29, 2020, 10:37:40 AM
I've been tied up rejiggering my business for adding back workers, so I have not had time to participate much here.  But I just want to jump in for a second to say THANKS to Jim for his awesome work here.  Really very much appreciated.

I'm sure I'm going to get excellent use out of this thing.  There are so many cases where I don't necessarily have access to the SQL server (my preference for serious db work) and this will be perfect, and very easy to use.  It's so freakin easy to use, I may start using it for stuff I used to use big fat ini files for.

Question: what should I bear in mind with regard to multiuser access?  I'm assuming I should strictly avoid it. Correct?

-Kirby

Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 29, 2020, 11:22:31 AM
Thanks.

I would read about this issue on SQLite's site or other experts.  I don't think I have done anything which would make things better or worse on that front so whatever they say should apply.  If you discover different please let me know and I will put notes in the Help file.

Jim
Title: Re: SQLite Extender (Alpha? Beta?)
Post by: JTaylor on May 30, 2020, 07:21:29 AM
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.

Wish I could do this with real objects but after much study of documentation and a few tutorials and feeling like I have a slightly less than surface understanding of COM, I still don't know what pieces are required to make it work within an Extender.   Maybe one day...

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

Jim