This is a follow up observation regarding my script upload in Jim's SQLite Extender Thread. I added a simple dialog to the script to look for a text files, confirm the delimiter and create a schema.ini before processing the file.
- the .txt file had over 142k rows, 8 columns
- Easily parsed by column type in Datatable and SQLite Create statement generated
- Unfortunately takes over an hour to INSERT rows into SQLite Table
Probably since SQL Server is more aligned with .NET - inserting rows into a table there took less than a minute. Not to diminish SQLite, it is great for holding and selecting data, when inserted a row at a time - but bulk inserting from a large text file... hurry up and wait.
And as for the TRANSACTION....COMMIT - that is great for a few operations, but for 142 of INSERTS doesn't make any difference as transaction is assumed for inserts.
Remember - ADO gives ability to create/save fabricated Recordsets, with typing from within WB, and save as binary files [.adt].
Challenge Accepted :)
I only had five columns but did 204,000 rows in 10 seconds in SQLite through my Extender.
Jim
I did use the new dbInsertBatch() option.
Jim
Jim,
I have to load 3.5MM, from a ASCII Fixed Record/Field into SQLite.
Do you think your Extender can do this in an hour or less?
So far 1 HR is the record using a product called FlowHeater (FlowHeater.NET) an amazing product for loading & transforming data, but if I could do everything I need with WB & your SQLite Extender that would be a winner in my book !!!
Keith
I do it via an Array so the first question would be will your records fit in an array or can they be easily split into small enough chunks.
Hard to say on load time. Indexes and such will affect things of course. If it can do 300,000 a minute which is 1/4 of my load time it should finish in about 12 minutes. Not sure if it can maintain or not as it grows though.
Jim
Quote from: JTaylor on May 21, 2020, 01:19:15 PM
Challenge Accepted :)
I only had five columns but did 204,000 rows in 10 seconds in SQLite through my Extender.
Jim
No offense, but that is kind of a moot point. I wrote code to insert 1/2 million rows with the SQLite ODBC Driver. But I didn't have a .NET datatable to work from back then. In my script there is a line that indicates where the SQLite .NET dll can be replaced by your Provider. And, I indicated that the oTable.Select() line places the datatable into an array. The sample file I provided had 66 rows, what I wrote about here had 142k rows.
I you want a challenge, modify my script to take over the SQLite portion with your Extender. I can then implement it against the 142k rows which are too large to upload here.
You may have a secret to batch import which significantly reduces my For...Next iteration of INSERTS. If so, you have a super tool and that is to your credit.
You aren't going to offend me but I did think the point was finding a faster way to load those records. Is this what you meant about the changes?
Jim
;Winbatch 2020A - Parsing csv for SQLite Structure
;Stan Littlefield, May 4, 2020
;Updated to Create SQLite db and table May 18
;///////////////////////////////////////////////////////////////////////////////////////////////////////////////
gosub udfs
AddExtender("wbsql44i.dll")
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):");"
oAdapter.Dispose()
oTable=0
oCmd=0
oConn=0
dbExecute(db, "Drop table If Exists Accounts; ":@CRLF: cCreate)
;**********
;Did this manually but could build it dynamically.
;**********
SQLText = "INSERT OR IGNORE INTO Accounts Values ('{{{0}}}','{{{1}}}','{{{2}}}','{{{3}}}','{{{4}}}','{{{5}}}','{{{6}}}','{{{7}}}','{{{8}}}');"
dbInsertBatch(db,SQLText,ArrayFileGetCsv("WakeCounty.csv", 0, @TAB),@FALSE)
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
* Schema.ini (0.04 kB - downloaded 0 times.)
* WakeCounty.csv (3.52 kB - downloaded 0 times.)
Nice. I did test with the array obtained by oTable.Select() from the datatable rather than ArrayFileGetCsv. It did fill all rows but with '{{{0}}}' etc...
Also, when I parsed this
Date,Agent,Contact,ServiceName,FullDispositionName,DispositionClass,FullWorkgroupName,AgentCalls
9/19/2013,"E064935 - Szymanski, Steven",0,CAR_OBS_CANCELS,CUSTOMER HANG UP 11/17/2008 15:33:38 Active,NON_CONTACT,CAR_OBS_ENICHOLS 01/25/2013 11:00:37 Active,1
9/19/2013,"E064935 - Szymanski, Steven",0,CAR_OBS_NOSA,ANS_MACH_HU 11/17/2008 15:22:30 Active,NON_CONTACT,CAR_OBS_ENICHOLS 01/25/2013 11:00:37 Active,1
9/19/2013,"E064935 - Szymanski, Steven",0,CAR_OBS_NOSA,Consultation Call 10/28/2008 19:47:02 Active,Gateway Dispositions,CAR_OBS_ENICHOLS 01/25/2013 11:00:37 Active,0
It put the values in as {0}....{07} but I think you were working on issues with quotes. But it was fast, 65k rows in around 4 seconds.
You must have the very first go at that...I added the additional braces because I could easily see data having something like {1} after first publishing it. Suggestions for the template? I really dislike all the braces.
Glad it worked though.
Jim
Quote from: JTaylor on May 22, 2020, 06:38:59 AM
Glad it worked though.
Jim
It does and it doesn't. Attached is your script only I'm trying to make it work with the Datatable array. I added lines to ensure it was an array and used ObjectType to check that is was a WB Array.
your line dbInsertBatch(db,SQLText,ArrayFileGetCsv("WakeCounty.csv", 0, @TAB),@FALSE) works
my try dbInsertBatch(db,SQLText,myRows,@FALSE) creates the data but is filed in with {{{0}}}... rather than column values.
At this point makes little sense, I would think an array is an array. I've seen other posts where they use ,?, or ,{?} or ,@ColumnName to populate data.
;Winbatch 2020A - Parsing csv for SQLite Structure
;Stan Littlefield, May 4, 2020
;Updated to Create SQLite db and table May 18
;///////////////////////////////////////////////////////////////////////////////////////////////////////////////
gosub udfs
AddExtender("wbsql44i.dll")
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):");"
oRows = oTable.Select()
;just check that data is an array
Message("",ObjectTypeGet(oRows))
myRows = ObjectType("ARRAY",oRows)
Message("",Arrinfo(myRows,1))
dbExecute(db, "Drop table If Exists Accounts; ":@CRLF: cCreate)
dbExecute(db, "VACUUM;")
;**********
;Did this manually but could build it dynamically.
;**********
SQLText = "INSERT OR IGNORE INTO Accounts Values ('{{{0}}}','{{{1}}}','{{{2}}}','{{{3}}}','{{{4}}}','{{{5}}}','{{{6}}}','{{{7}}}','{{{8}}}');"
;dbInsertBatch(db,SQLText,ArrayFileGetCsv("WakeCounty.csv", 0, @TAB),@FALSE) ;works
dbInsertBatch(db,SQLText,myRows,@FALSE) ;inserts, but no data
BoxShut()
oAdapter.Dispose()
oTable=0
oCmd=0
oConn=0
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
The problem is the Array created by oTable.Select() has zero columns. Still looking to see why.
Jim
Jim,
My concern is do you flush the array and reload till you hit EOF on input file OR are you expecting to load the entire
input file before flushing to SQLite?
It is almost a two-thread where one thread is reading and loading a buffer, say 1,000 lines and then passes off to a
2nd-thread writing to SQLite and keep flipping back and forth till you hit EOF on the Input File as in both on my file(s)
the 3.5MM and the 6,500 pg source that I gave you that example from will never fit in memory prior to writing the
SQLite.db equivalent.
??????
Keith
I will load every 10,000 rows. That seems to be a good breakpoint.
Jim
Quote from: stanl on May 22, 2020, 09:39:21 AM
At this point makes little sense, I would think an array is an array...
Well not exactly. Internally, the interpreter DLL does a lot of magic to make variant safearrays and WIL arrays look syntactically identical but they have completely different representations. One of those bits of magic is to convert a safearray to a WIL array when passing the array to a function if the parameter type is specified as an array. If the function parameter type is specified as a variant or "any type", the safearray is passed to the function as a safearray.
I believe the Extender is working as expected. The array has 66 rows so it loops through those and since it doesn't have any columns the Template is still in its original form so it loads the defined values, which are the place holders.
I can't see how to make the oTable.Select pull the needed data as it should. Tried a few things but with the same result.
Jim
Quote from: stanl on May 22, 2020, 09:39:21 AM
it does and it doesn't. Attached is your script only I'm trying to make it work with the Datatable array. I added lines to ensure it was an array and used ObjectType to check that is was a WB Array.
your line dbInsertBatch(db,SQLText,ArrayFileGetCsv("WakeCounty.csv", 0, @TAB),@FALSE) works
my try dbInsertBatch(db,SQLText,myRows,@FALSE) creates the data but is filed in with {{{0}}}... rather than column values.
At this point makes little sense, I would think an array is an array. I've seen other posts where they use ,?, or ,{?} or ,@ColumnName to populate data.
Ignoring the Extender, should WIL be able to read the array from oTables.Select?
Jim
Quote from: td on May 22, 2020, 10:58:07 AM
Quote from: stanl on May 22, 2020, 09:39:21 AM
At this point makes little sense, I would think an array is an array...
Well not exactly. Internally, the interpreter DLL does a lot of magic to make variant safearrays and WIL arrays look syntactically identical but they have completely different representations. One of those bits of magic is to convert a safearray to a WIL array when passing the array to a function if the parameter type is specified as an array. If the function parameter type is specified as a variant or "any type", the safearray is passed to the function as a safearray.
Yes, it can and it does. However, the array elements are .Net Foundation class DataRow objects. Not something that you want to try and deal with in your extender. The DataRow objects would need to be converted to some representation of column values before being passed to your extender.
https://docs.microsoft.com/en-us/dotnet/api/system.data.datarow?view=netframework-4.8 (https://docs.microsoft.com/en-us/dotnet/api/system.data.datarow?view=netframework-4.8)
Nothing quite like living on the edge...
Yeah...had read through that trying to see what might be wrong with the script since the Array wasn't right before the Extender came into play.
So something is wrong in what is being tried I guess as it ends up with zero dimensions for the the second dimension. Just wanted to make sure before we kept hammering away at it.
Thanks.
Jim
As it turned out, Trying to Place "BEGIN TRANSACTION.... COMMIT; as part of an SQLTtext made my row iteration of oTable.Select() turtle-ware. I changed to trans= oSQLITE.BeginTransaction ...... trans.commit() and it zipped along.
Jim's dbInsertBatch() rocks, however.
Quote from: td on May 22, 2020, 11:59:26 AM
The DataRow objects would need to be converted to some representation of column values before being passed to your extender.
Nothing quite like living on the edge...
If the DataTable is converted to an Arraylist
ObjectClrOption("useany","System.Collections")
oArrayList = ObjectClrNew('System.Collections.ArrayList')
Would that work with the Extender? Would just have to overcome this error
For i =0 To rowcount -1
For j = 0 To colcount -1
oArrayList.Add(oTable.Rows[i][j].ToString()) ; this doesn't work - error
Next
Next
Were you able to get WinBatch to read the "Select" array?
Jim
Quote from: JTaylor on May 23, 2020, 06:08:14 AM
Were you able to get WinBatch to read the "Select" array?
Jim
Yes, as individual column items which are assigned to the INSERT statements. Guess I could use the same logic when converting a datatable for use with Reportview - basically convert to a WB array. But that would probably not be a time saver.
And doesn't work
;Winbatch 2020A - Parsing csv for SQLite Structure
;Stan Littlefield, May 4, 2020
;Updated to Create SQLite db and table May 18
;///////////////////////////////////////////////////////////////////////////////////////////////////////////////
gosub udfs
AddExtender("wbsql44i.dll")
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):");"
arrData = ArrDimension(rowcount+1,colcount)
For _col = 0 to colcount-1
arrData[0,_col] = oTable.Columns.Item(_col).ColumnName
Next
For _row = 0 to rowcount-1
objRow = oTable.Rows.Item(_row)
For _col = 0 to colcount-1
objColumn = oTable.Columns.Item(_col)
arrData[_row+1,_col] = objRow.Item(objColumn)
Next
Next
dbExecute(db, "Drop table If Exists Accounts; ":@CRLF: cCreate)
dbExecute(db, "VACUUM;")
;**********
;Did this manually but could build it dynamically.
;**********
SQLText = "INSERT OR IGNORE INTO Accounts Values ('{{{0}}}','{{{1}}}','{{{2}}}','{{{3}}}','{{{4}}}','{{{5}}}','{{{6}}}','{{{7}}}','{{{8}}}');"
;dbInsertBatch(db,SQLText,ArrayFileGetCsv("WakeCounty.csv", 0, @TAB),@FALSE) ;works
dbInsertBatch(db,SQLText,arrData,@FALSE) ;inserts, but no data
BoxShut()
oAdapter.Dispose()
oTable=0
oCmd=0
oConn=0
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
I see. So it really isn't being read as an array of rows and columns but rather a single dimension array containing data objects/structures.
That explains the behavior I was seeing.
Jim
Quote from: JTaylor on May 23, 2020, 10:16:46 AM
I see. So it really isn't being read as an array of rows and columns but rather a single dimension array containing data objects/structures.
That explains the behavior I was seeing.
"the array elements are .Net Foundation class DataRow objects. Not something that you want to try and deal with in your extender. The DataRow objects would need to be converted to some representation of column values before being passed to your extender."
I understand. I thought from the earlier discussion that it was a full array in the 2 dimensional sense but just a special type.
Jim
Quote from: JTaylor on May 23, 2020, 04:50:26 PM
I understand.
Jim
Guess we can put this sidebar away. .NET datatables and your Extender just ain't gonna cut it. I played around with Getvalues() which appeared to be the oledb .net class equivalent to GetRows(). I was able to use both the ACE 12 Provider and TextFieldParser to move text files into a database ( SQLite, SQL Server, Access ). Even if often slower than your Extender if I develop at work scripts have to be in Powershell as WB exe's are not approved [yet].
Okay :(
Do you have a working script using GetValues? I'm not having any luck with that.
Jim
Quote from: JTaylor on May 24, 2020, 08:59:20 AM
Okay :(
Do you have a working script using GetValues? I'm not having any luck with that.
Jim
No, if I did I would have posted.
Okay. Misunderstood.
Jim
I guess where I got really confused was the last scriplet I posted on this thread which created arrData from oTable - you can add a line like Message("",arrinfo(arrData,0)) to see it is a 2 dimensional array, but....
SQLText = "INSERT OR IGNORE INTO Accounts Values SQLText = "INSERT OR IGNORE INTO Accounts Values ('{{{0}}}','{{{1}}}','{{{2}}}','{{{3}}}','{{{4}}}','{{{5}}}','{{{6}}}','{{{7}}}','{{{8}}}');"
dbInsertBatch(db,SQLText,arrData,@FALSE) ;inserts, but no data
Creates the table and columns but no rows are inserted. Curious how arrData differs from the results using ArrayFileGetCsv().
NOTE, if you are keeping current with the extender, the field indicators have changed
from {{{0}}} to [{0}] when representing them. Change occurred with v8 I believe.
Keith
Quote from: JTaylor on May 25, 2020, 06:19:00 AM
Yes. It shows as 2 dimensional but if you do ArrInfo(2) it will tell you that the second 2 dimension has zero elements. Which seems odd but guessing WinBatch is doing what it can with it. My Extender takes that and grabs the column count, in this case 0, and loops through them, or not in this case since there are none, replacing place holders with the column data. Since there are none but the Template is valid SQL it inserts it for each row. Sorry if repeating myself.
Jim
What is the "it" that shows as 2 dimensional?
Apparently my imagination.
Jim
Quote from: td on May 25, 2020, 08:59:19 AM
Quote from: JTaylor on May 25, 2020, 06:19:00 AM
Yes. It shows as 2 dimensional but if you do ArrInfo(2) it will tell you that the second 2 dimension has zero elements. Which seems odd but guessing WinBatch is doing what it can with it. My Extender takes that and grabs the column count, in this case 0, and loops through them, or not in this case since there are none, replacing place holders with the column data. Since there are none but the Template is valid SQL it inserts it for each row. Sorry if repeating myself.
Jim
What is the "it" that shows as 2 dimensional?
Understood. But arrData as created from oTable can be easily placed in Reportview, which I thought took an array. This uses SQLite, still creates a .Net Datatable and populates a Reportview as arrData - which I assumed was a 2-dimensional array.
db = DirScript():"zips.db"
If ! FileExist(db) Then Terminate(@TRUE,"Cannot Continue",db:" is missing")
ObjectClrOption("Appbase", DirScript())
;ObjectClrOption("Appbase", "c:\scripts\sqlite\")
ObjectClrOption('use','System.Data.SQLite')
ObjectClrOption("useany", "System")
ObjectClrOption("useany", "System.Data")
oTable = ObjectClrNew( 'System.Data.DataTable')
oAdapter = ObjectClrNew('System.Data.SQLite.SQLiteDataAdapter')
dbObj = ObjectClrNew("System.Data.SQLite.SQLiteConnection","Data Source=%db%;Version=3;Pooling=true;Legacy Format=True")
dbObj.Open
cmdObj = ObjectClrNew("System.Data.SQLite.SQLiteCommand",dbObj)
SQLText = "VACUUM;"
cmdObj.CommandText = SQLText
cmdObj.ExecuteNonQuery()
SQLText = "Select * from NC;"
cmdObj.CommandText = SQLText
rdrObj = ObjectClrNew("System.Data.SQLite.SQLiteDataReader")
rdr = cmdObj.ExecuteReader()
oTable.Load(rdr)
rowcount = oTable.Rows.Count
colcount = oTable.Columns.Count
arrData = ArrDimension(rowcount+1,colcount)
For _col = 0 to colcount-1
arrData[0,_col] = oTable.Columns.Item(_col).ColumnName
Next
For _row = 0 to rowcount-1
objRow = oTable.Rows.Item(_row)
For _col = 0 to colcount-1
objColumn = oTable.Columns.Item(_col)
arrData[_row+1,_col] = objRow.Item(objColumn)
Next
Next
Message("",arrinfo(arrData,0))
ObjectClose(cmdObj)
ObjectClose(dbObj)
ObjectClose(rdrObj)
ObjectClose(oTable)
MyDialogFormat=`WWWDLGED,6.2`
MyDialogCaption=`Sample SQLite Data`
MyDialogX=9999
MyDialogY=9999
MyDialogWidth=560
MyDialogHeight=102
MyDialogNumControls=002
MyDialogProcedure=``
MyDialogFont=`Microsoft Sans Serif|5632|70|34`
MyDialogTextColor=`DEFAULT`
MyDialogBackground=`DEFAULT,255|255|255`
MyDialogConfig=0
MyDialog001=`219,075,090,012,PUSHBUTTON,"PushButton_OK",DEFAULT,"OK",1,20,@csDefButton,DEFAULT,DEFAULT,"128|255|0"`
MyDialog002=`007,005,540,062,REPORTVIEW,"ReportView_1",arrData,DEFAULT,DEFAULT,10,@csAsort|@csFirstHeader|@csFullSel|@csGrid|@csSingleSel,"Microsoft Sans Serif|6144|70|34","255|255|0","0|0|0"`
ButtonPushed=Dialog("MyDialog")
Exit
You have a two-dimensional array because you created one not because any dotNet method returned one.
Quote from: td on May 26, 2020, 08:33:19 AM
You have a two-dimensional array because you created one not because any dotNet method returned one.
Exactly, that was the point. A few posts ago, I concluded that parsing text files into .NET then moving into the extender was not a wise choice. Still, I taken the extra time to create a WB array, the INSERT still didn't work with the Extender - ArrayFileGetCsv() did. Just asked why?
That your created 2-dimensional array didn't work in the extender is obviously what I missed. Pure speculation on my part but the reason it didn't work could be because your rank 2 array elements are variants and might require a little additional processing before they can be passed to SQLite. The additional processing is something that could be done in the extender but it would require a bit of coding to accomplish.
Quote from: td on May 27, 2020, 08:15:58 AM
That your created 2-dimensional array didn't work in the extender is obviously what I missed. Pure speculation on my part but the reason it didn't work could be because your rank 2 array elements are variants and might require a little additional processing before they can be passed to SQLite. The additional processing is something that could be done in the extender but it would require a bit of coding to accomplish.
Interesting. Reportview has no issue with the array. Might be worth testing if I assigned the elements as ObjectType("BSTR"),just to see if it achieves the same as dbInsertBatch(db,SQLText,ArrayFileGetCsv("WakeCounty.csv", 0, @TAB),@FALSE).
Guess I missed something too as I thought you were passing the oTables Array. I will go back and take a look as well.
Jim
Quote from: stanl on May 27, 2020, 04:34:35 PM
Quote from: td on May 27, 2020, 08:15:58 AM
That your created 2-dimensional array didn't work in the extender is obviously what I missed. Pure speculation on my part but the reason it didn't work could be because your rank 2 array elements are variants and might require a little additional processing before they can be passed to SQLite. The additional processing is something that could be done in the extender but it would require a bit of coding to accomplish.
Interesting. Reportview has no issue with the array. Might be worth testing if I assigned the elements as ObjectType("BSTR"),just to see if it achieves the same as dbInsertBatch(db,SQLText,ArrayFileGetCsv("WakeCounty.csv", 0, @TAB),@FALSE).
I figured out how to convert BSTR at least and assume I would have to check types and convert all of them. I think I would have to know you were sending Variants ahead of time but maybe there is a way around that??? Or create a different function.
Of course, this works too and is easier, on me anyway ;)
ArrayFilePutCSV("array.txt",arrData,@TAB)
arrData = ArrayFileGetCSV("array.txt",0,@TAB)
Quote from: JTaylor on May 27, 2020, 07:17:13 PM
I figured out how to convert BSTR at least and assume I would have to check types and convert all of them. I think I would have to know you were sending Variants ahead of time but maybe there is a way around that??? Or create a different function.
Of course, this works too and is easier, on me anyway ;)
ArrayFilePutCSV("array.txt",arrData,@TAB)
arrData = ArrayFileGetCSV("array.txt",0,@TAB)
Test wakecounty.csv had 66 rows, but when you get beyond 100,000 rows writing an array to file and back to an array is a bit much. I think we can settle that there are issues with .net DataTables. I was drawn to them having to deal with SEQUENCE objects in both Hana and SQL Server. They are BigInt and generally default to a number like -
9223372036854775808.
I know...I am just lazy :)
If it is something you would use, I will put it on my list and see if I can make it happen. It would be a good exercise for learning about all the variant sub-types.
Jim
Quote from: stanl on May 27, 2020, 04:34:35 PM
Interesting. Reportview has no issue with the array.
That is because the WIL interpreter does all kinds of magic that makes it possible for variants to be just another variable (or array element) from the perspective of the user.