SQLite - Sidebar

[stanl]

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

[JTaylor]

Challenge Accepted  :)

I only had five columns but did 204,000 rows in 10 seconds in SQLite through my Extender.

Jim

[JTaylor]

I did use the new dbInsertBatch() option.

Jim

[KeithW]

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

[JTaylor]

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

[stanl]

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.

[JTaylor]

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

Code (winbatch) Select Expand



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



[stanl]

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

[stanl]

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.

[JTaylor]

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

[stanl]

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.

Code (WINBATCH) Select Expand



;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


[JTaylor]

The problem is the Array created by oTable.Select() has zero columns.   Still looking to see why.

Jim

[KeithW]

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

[JTaylor]

I will load every 10,000 rows.  That seems to be a good breakpoint.

Jim

[td]

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.

[JTaylor]

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

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.

[JTaylor]

Ignoring the Extender, should WIL be able to read the array from oTables.Select? 

Jim

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.

[td]

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

Nothing quite like living on the edge...

[JTaylor]

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

[stanl]

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.

[stanl]

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

Code (WINBATCH) Select Expand



ObjectClrOption("useany","System.Collections")
oArrayList =  ObjectClrNew('System.Collections.ArrayList')



Would that work with the Extender? Would just have to overcome this error

Code (WINBATCH) Select Expand



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


[JTaylor]

Were you able to get WinBatch to read the "Select" array?

Jim

[stanl]

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

Code (WINBATCH) Select Expand



;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


[JTaylor]

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

[td]

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

[JTaylor]

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

[stanl]

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

[JTaylor]

Okay  :(

Do you have a working script using GetValues?   I'm not having any luck with that.

Jim

[stanl]

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.

[JTaylor]

Okay.  Misunderstood.

Jim

[stanl]

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

Code (WINBATCH) Select Expand



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


i


Creates the table and columns but no rows are inserted. Curious how arrData differs from the results using ArrayFileGetCsv(). 

[KeithW]

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

[td]

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?

[JTaylor]

Apparently my imagination.

Jim

[stanl]

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.

Code (WINBATCH) Select Expand



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