Author Topic: SQLite - Sidebar  (Read 908 times)

stanl

  • Pundit
  • *****
  • Posts: 1175
SQLite - Sidebar
« on: May 21, 2020, 09:00:26 am »
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

  • Pundit
  • *****
  • Posts: 1362
    • Data & Stuff Inc.
Re: SQLite - Sidebar
« Reply #1 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

JTaylor

  • Pundit
  • *****
  • Posts: 1362
    • Data & Stuff Inc.
Re: SQLite - Sidebar
« Reply #2 on: May 21, 2020, 01:22:43 pm »
I did use the new dbInsertBatch() option.

Jim

KeithW

  • Full Member
  • ***
  • Posts: 150
Re: SQLite - Sidebar
« Reply #3 on: May 21, 2020, 02:21:28 pm »
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

  • Pundit
  • *****
  • Posts: 1362
    • Data & Stuff Inc.
Re: SQLite - Sidebar
« Reply #4 on: May 21, 2020, 04:10:51 pm »
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

  • Pundit
  • *****
  • Posts: 1175
Re: SQLite - Sidebar
« Reply #5 on: May 21, 2020, 05:04:36 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.

JTaylor

  • Pundit
  • *****
  • Posts: 1362
    • Data & Stuff Inc.
Re: SQLite - Sidebar
« Reply #6 on: May 21, 2020, 06:00:00 pm »
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

;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

  • Pundit
  • *****
  • Posts: 1175
Re: SQLite - Sidebar
« Reply #7 on: May 22, 2020, 04:12:36 am »
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

  • Pundit
  • *****
  • Posts: 1175
Re: SQLite - Sidebar
« Reply #8 on: May 22, 2020, 06:19:57 am »
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

  • Pundit
  • *****
  • Posts: 1362
    • Data & Stuff Inc.
Re: SQLite - Sidebar
« Reply #9 on: May 22, 2020, 06:38:59 am »
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

  • Pundit
  • *****
  • Posts: 1175
Re: SQLite - Sidebar
« Reply #10 on: May 22, 2020, 09:39:21 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.
Code: Winbatch

;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

  • Pundit
  • *****
  • Posts: 1362
    • Data & Stuff Inc.
Re: SQLite - Sidebar
« Reply #11 on: May 22, 2020, 09:59:17 am »
The problem is the Array created by oTable.Select() has zero columns.   Still looking to see why.

Jim

KeithW

  • Full Member
  • ***
  • Posts: 150
Re: SQLite - Sidebar
« Reply #12 on: May 22, 2020, 10:25:36 am »
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

  • Pundit
  • *****
  • Posts: 1362
    • Data & Stuff Inc.
Re: SQLite - Sidebar
« Reply #13 on: May 22, 2020, 10:55:25 am »
I will load every 10,000 rows.  That seems to be a good breakpoint.

Jim

td

  • Tech Support
  • *****
  • Posts: 3460
    • WinBatch
Re: SQLite - Sidebar
« Reply #14 on: May 22, 2020, 10:58:07 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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

JTaylor

  • Pundit
  • *****
  • Posts: 1362
    • Data & Stuff Inc.
Re: SQLite - Sidebar
« Reply #15 on: May 22, 2020, 10:59:15 am »
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

  • Pundit
  • *****
  • Posts: 1362
    • Data & Stuff Inc.
Re: SQLite - Sidebar
« Reply #16 on: May 22, 2020, 11:05:13 am »
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

  • Tech Support
  • *****
  • Posts: 3460
    • WinBatch
Re: SQLite - Sidebar
« Reply #17 on: May 22, 2020, 11:59:26 am »
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...
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

JTaylor

  • Pundit
  • *****
  • Posts: 1362
    • Data & Stuff Inc.
Re: SQLite - Sidebar
« Reply #18 on: May 22, 2020, 12:21:23 pm »
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

  • Pundit
  • *****
  • Posts: 1175
Re: SQLite - Sidebar
« Reply #19 on: May 22, 2020, 12:21:42 pm »
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

  • Pundit
  • *****
  • Posts: 1175
Re: SQLite - Sidebar
« Reply #20 on: May 23, 2020, 04:21:08 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
Code: Winbatch

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


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


Code: Winbatch

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

  • Pundit
  • *****
  • Posts: 1362
    • Data & Stuff Inc.
Re: SQLite - Sidebar
« Reply #21 on: May 23, 2020, 06:08:14 am »
Were you able to get WinBatch to read the "Select" array?

Jim

stanl

  • Pundit
  • *****
  • Posts: 1175
Re: SQLite - Sidebar
« Reply #22 on: May 23, 2020, 07:14:15 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


Code: Winbatch

;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

  • Pundit
  • *****
  • Posts: 1362
    • Data & Stuff Inc.
Re: SQLite - Sidebar
« Reply #23 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.

Jim

td

  • Tech Support
  • *****
  • Posts: 3460
    • WinBatch
Re: SQLite - Sidebar
« Reply #24 on: May 23, 2020, 10:57:38 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."
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

JTaylor

  • Pundit
  • *****
  • Posts: 1362
    • Data & Stuff Inc.
Re: SQLite - Sidebar
« Reply #25 on: May 23, 2020, 04:50:26 pm »
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

  • Pundit
  • *****
  • Posts: 1175
Re: SQLite - Sidebar
« Reply #26 on: May 24, 2020, 05:16:03 am »
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

  • Pundit
  • *****
  • Posts: 1362
    • Data & Stuff Inc.
Re: SQLite - Sidebar
« Reply #27 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

stanl

  • Pundit
  • *****
  • Posts: 1175
Re: SQLite - Sidebar
« Reply #28 on: May 24, 2020, 10:19:14 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.

JTaylor

  • Pundit
  • *****
  • Posts: 1362
    • Data & Stuff Inc.
Re: SQLite - Sidebar
« Reply #29 on: May 24, 2020, 12:40:09 pm »
Okay.  Misunderstood.

Jim

stanl

  • Pundit
  • *****
  • Posts: 1175
Re: SQLite - Sidebar
« Reply #30 on: May 25, 2020, 04:01:44 am »
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

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

  • Full Member
  • ***
  • Posts: 150
Re: SQLite - Sidebar
« Reply #31 on: May 25, 2020, 07:40:23 am »
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

  • Tech Support
  • *****
  • Posts: 3460
    • WinBatch
Re: SQLite - Sidebar
« Reply #32 on: May 25, 2020, 08:59:19 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?
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

JTaylor

  • Pundit
  • *****
  • Posts: 1362
    • Data & Stuff Inc.
Re: SQLite - Sidebar
« Reply #33 on: May 25, 2020, 10:01:54 am »
Apparently my imagination.

Jim

stanl

  • Pundit
  • *****
  • Posts: 1175
Re: SQLite - Sidebar
« Reply #34 on: May 25, 2020, 12:45:48 pm »
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

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