SQLite Extender (Alpha? Beta?)

[JTaylor]

Good idea.   Thanks.   Now if I can only figure out why everything seems to be falling apart  :(

Jim

[td]

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.)

[KeithW]

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

[JTaylor]

...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

[td]

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. 

[KeithW]

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

[JTaylor]

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

[JTaylor]

I really need to start with deleting the database for my test script, don't I, so I catch these problems.

Jim

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

[KeithW]

And just like that I went backwards too...

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

Keith

[KeithW]

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.

Code Select Expand


;#############################################################
;#############################################################
;
;   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"))

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


[KeithW]

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.

Code Select Expand


;#############################################################
;#############################################################
;
;   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

[JTaylor]

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

[KeithW]

Jim,

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

Keith

[KeithW]

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

[KeithW]

Jim,

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

Keith

[JTaylor]

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

[td]

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. 

[JTaylor]

I think I have squashed the NULL issue.

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

Jim

[kdmoyers]

Jim, I'm getting 404 error on http://www.jtdata.com/anonymous/wbsql44i_v2.zip

[KeithW]

Jim,

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

Keith

[JTaylor]

How about now?

Jim

Jim,

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

Keith

[KeithW]

works, got it

thanx,
keith

[KeithW]

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

[JTaylor]

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

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.

[JTaylor]

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

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

[KeithW]

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

[JTaylor]

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

[KeithW]

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

[KeithW]

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

[JTaylor]

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

[JTaylor]

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

[JTaylor]

Use this with dbGetRows() to clean things up...

Code (winbatch) Select Expand


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

Jim

[KeithW]

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.

Code Select Expand


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

[KeithW]

Is @dbVersion suppose to be in the v3 library?

Keith

[JTaylor]

No.   The next one I post.  Just didn't want to forget to mention it.

Jim

Is @dbVersion suppose to be in the v3 library?

Keith