SQLite Extender (Alpha? Beta?)

[JTaylor]

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

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

[JTaylor]

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

[KeithW]

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

[JTaylor]

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

[KeithW]

Jim,

Beat up the InsertCSV() and it seems stable

My test code

Code Select Expand

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

[KeithW]

Jim,

dbInsertFlat() issue:

Code Select Expand


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

[KeithW]

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

[JTaylor]

Probably not.

Jim

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

[JTaylor]

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

Jim,

dbInsertFlat() issue:

Code Select Expand


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

[JTaylor]

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

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

[KeithW]

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

Code Select Expand


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

[JTaylor]

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

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?

[KeithW]

Jim

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

Keith

[JTaylor]

That is my experience as well.   May just get rid of the test script :)

Jim

Jim

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

Keith

[JTaylor]

I just loaded 4.2 million records via InsertCSV().  Only had 5 columns though.   Will try an InsertFlat file.

Jim

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

[KeithW]

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

[KeithW]

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

[JTaylor]

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

[KeithW]

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

[JTaylor]

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

[JTaylor]

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

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

[KeithW]

Without looking it up....  Ctrl-Z  EOF I believe.

Keith

[JTaylor]

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

[JTaylor]

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

[KeithW]

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

[KeithW]

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

[KeithW]

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

[KeithW]

Jim,

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

Keith

[KeithW]

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

[KeithW]

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

[JTaylor]

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

[JTaylor]

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

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

[JTaylor]

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

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

[KeithW]

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

[KeithW]

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