Mapping .NET to SQLite - for Jim

Started by stanl, January 18, 2021, 05:18:03 AM

Previous topic - Next topic

stanl

Jim;


Below is an updated script that uses your Extender. Not wanting to extend earlier comments and sidetrack Kirby's post, the script illustrates mapping data types between a DataTable and an SQLite db - using WB maps rather than a UDF. The attached .csv supports the script, It should create the db and output a supporting .ini entry for the .csv.


Having no yet upgraded to the latest WB version the script uses MapKeyExists(). If the key does not exists then a default data type is used. In Kirby's post, Tony recognized a potential issue with MapKeyFind() in terms of a return value which may not exist. I simply asked, given the situation in the script if MapKeyFind() could accept a default value, thus eliminating the If Else Endif logic.
Code (WINBATCH) Select


;Winbatch 2020A - Parsing csv for SQLite Structure
;Stan Littlefield, January 18, 2021
;Updated to from original May 2020 script
;///////////////////////////////////////////////////////////////////////////////////////////////////////////////
gosub udfs
AddExtender("wbsql44i.dll")
IntControl(73,1,0,0,0)


;WB Map to mapping data types between .NET DataTable and SQLite
types = $"System.Boolean,INTEGER
System.Byte,INTEGER
System.Char,TEXT
System.DateTime,TEXT
System.Decimal,NUMERIC
System.Int32,INTEGER
System.Int64,REAL
System.Int16,INTEGER
System.Object,BLOB
System.Single,REAL
System.String,TEXT
System.Double,REAL
System.Array,BLOB
System.UInt16,INTEGER
System.UInt32,REAL
System.UInt64,REAL
System.IntPtr,TEXT$"
dTypes= MapCreate(types,'',@lf)


folder = dirscript()


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"'
;set up INI entry for Tab Delimted Format
cIni = "[WakeCounty.csv]":@CRLF
cIni = cIni:"Format=TabDelimited":@CRLF
cIni = cIni:"ColNameHeader=True":@CRLF
cIni = cIni:"MaxScanRows=10":@CRLF
cIni = cIni:"CharacterSet=ANSI":@CRLF
FilePut(Dirscript():"schema.ini",cIni)




db = DirScript():"NetTest.sqlite"
dbOpen(db,@FALSE) ;will create SQLite db if it doesn't exist
tbl="Accounts"


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 %tbl% (":@LF
ForEach col in oCols
   name = col.ColumnName
   colType = col.DataType.ToString()
   objRow = oTable.Rows.Item(0)
   val = objRow.Item(col)
   If MapKeyExist(dTypes,colType)
      cCreate = cCreate:name:" ":dTypes[ColType]:",":@LF
   Else
      cCreate = cCreate:name:"  BLOB":",":@LF
   Endif
   ;original UDF before Map
   ;cCreate = cCreate:name:" ":cvt(colType):",":@LF


Next
cCreate = StrSub(cCreate,1,Strlen(cCreate)-2):");"
Message("Schema",cCreate)


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] = ObjectType("BSTR",objRow.Item(objColumn)) 
   Next
Next


dbExecute(db, "Drop table If Exists Accounts; ":@CRLF: cCreate)
dbExecute(db, "VACUUM;")


;pre-known column count, but could be built dynamically
SQLText = "INSERT OR IGNORE INTO Accounts Values ('[{1}]','[{2}]','[{3}]','[{4}]','[{5}]','[{6}]','[{7}]','[{8}]','[{9}]');"
stmt = dbInsertCSV(db,SQLText,"WakeCounty.csv", 1, -1, @TAB)


BoxShut()
dbClose()
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




;original udf to lookup and assign conversion
;replaced by WB Map
#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

stanl

Jim;


related [EDIT].  The script does create arrData and those lines are not really needed. But, I did test


stmt = dbInsertArray(db,SQLText,arrData,1)  ;which stopped script execution, no error.


The arrData is valid and I've used it in other SQLite scripts w/out your Extender. Perhaps I was not using dbInsertArray correctly.

JTaylor

I understood on the other post.  I was just suggesting that maybe all the IF logic and data table could be replaced with an SQL Statement, if that made sense in your situation but after looking at the changes you made I don't think anything would be gained.

Jim

JTaylor

I will take a look and fix it, if needed.  Thanks.

Jim

Quote from: stanl on January 18, 2021, 06:26:34 AM
Jim;


related [EDIT].  The script does create arrData and those lines are not really needed. But, I did test


stmt = dbInsertArray(db,SQLText,arrData,1)  ;which stopped script execution, no error.


The arrData is valid and I've used it in other SQLite scripts w/out your Extender. Perhaps I was not using dbInsertArray correctly.

stanl

Quote from: JTaylor on January 18, 2021, 08:02:34 AM
I understood on the other post.  I was just suggesting that maybe all the IF logic and data table could be replaced with an SQL Statement, if that made sense in your situation but after looking at the changes you made I don't think anything would be gained.

Jim


Not so much a situation. You responses about using SQL seemed to indicate getting types from SQLite which can be easily done through Pragma..   I was looking in the other direction :-[ .  I had some frustration with Typeof() in WB CLR, so creating the .NET DataTable seemed workable, and easily built a CREATE statement. Oh, and have to remember... this is more related to my dumb question about MapFindKey() not your Extender.


I have an old app... circa 2009 where I place Excel template files into Access Binary fields via ADODB Streams. They are then extracted via Streams and used per other reporting scripts I wrote. Worked and continues to work well. The ask is if the Access tables can be converted to SQLite and the templates still added/extracted. I know an Access Binary field will map to a BLOB, just have to understand updating or adding new templates to SQLite w/out ADODB Stream support.


Of course that takes this topic off-course as is lately my tendency.

stanl

Oh, and here is a slight revision that keeps the arrdata, displays it in a Reportview, then fails with stmt = dbInsertArray(db,SQLText,arrData,1)


Code (WINBATCH) Select


;Winbatch 2020A - Parsing csv for SQLite Structure
;Stan Littlefield, January 18, 2021
;Updated to from original May 2020 script
;///////////////////////////////////////////////////////////////////////////////////////////////////////////////
gosub udfs
AddExtender("wbsql44i.dll")
IntControl(73,1,0,0,0)
arrData = ""
;WB Map to mapping data types between .NET DataTable and SQLite
types = $"System.Boolean,INTEGER
System.Byte,INTEGER
System.Char,TEXT
System.DateTime,TEXT
System.Decimal,NUMERIC
System.Int32,INTEGER
System.Int64,REAL
System.Int16,INTEGER
System.Object,BLOB
System.Single,REAL
System.String,TEXT
System.Double,REAL
System.Array,BLOB
System.UInt16,INTEGER
System.UInt32,REAL
System.UInt64,REAL
System.IntPtr,TEXT$"
dTypes= MapCreate(types,'',@lf)


folder = dirscript()


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"'
;set up INI entry for Tab Delimted Format
cIni = "[WakeCounty.csv]":@CRLF
cIni = cIni:"Format=TabDelimited":@CRLF
cIni = cIni:"ColNameHeader=True":@CRLF
cIni = cIni:"MaxScanRows=10":@CRLF
cIni = cIni:"CharacterSet=ANSI":@CRLF
FilePut(Dirscript():"schema.ini",cIni)




db = DirScript():"NetTest.sqlite"
dbOpen(db,@FALSE) ;will create SQLite db if it doesn't exist
tbl="Accounts"


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 %tbl% (":@LF
ForEach col in oCols
   name = col.ColumnName
   colType = col.DataType.ToString()
   objRow = oTable.Rows.Item(0)
   val = objRow.Item(col)
   If MapKeyExist(dTypes,colType)
      cCreate = cCreate:name:" ":dTypes[ColType]:",":@LF
   Else
      cCreate = cCreate:name:"  BLOB":",":@LF
   Endif
   ;original UDF before Map
   ;cCreate = cCreate:name:" ":cvt(colType):",":@LF


Next
cCreate = StrSub(cCreate,1,Strlen(cCreate)-2):");"
Message("Schema",cCreate)


;optional uncomment to test
arrdlg() ;display .NET data in WB Reportview




dbExecute(db, "Drop table If Exists Accounts; ":@CRLF: cCreate)
dbExecute(db, "VACUUM;")


;pre-known column count, but could be built dynamically
SQLText = "INSERT OR IGNORE INTO Accounts Values ('[{1}]','[{2}]','[{3}]','[{4}]','[{5}]','[{6}]','[{7}]','[{8}]','[{9}]');"
;stmt = dbInsertCSV(db,SQLText,"WakeCounty.csv", 1, -1, @TAB)


;if arrdlg is run, you can uncomment previous stmt= and try line below, which failed for me.
stmt = dbInsertArray(db,SQLText,arrData,1)




BoxShut()
dbClose()
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




;original udf to lookup and assign conversion
;replaced by WB Map
#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




#DefineSubRoutine arrdlg()
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] = ObjectType("BSTR",objRow.Item(objColumn)) 
   Next
Next


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


Return(1)
#EndSubRoutine


Return

JTaylor

I have been looking at ArrayInsert.   Still not sure why it fails.   Wasted an hour or two with an idiotic oversight on my part but still puzzled.   

If I do an ArrayFilePutCSV() of your array and then an ArrayFileGetCSV() it works.

Thought I had found it due to NULLs being present in the array but then realized I hadn't removed the above tweak.

So I don't forget to mention it...you have a header in the data so would need to start on row 2 for this one.

I will let you know once I get it figured out.   Now that I have fixed my initial oversight I can better track down where it is failing.

Jim

JTaylor

Okay.   I still don't understand why it is happening but there is something with the data you are submitting.  Maybe Tony will chime in with a thought about why.    Whenever I try to read  lv.lpstr in the Extender it just goes away without an error.

                        m_lpViperReturn->inittype = VARTYPE_ARRAY;
         vv.inittype = VARTYPE_STRING;
         int J = DllArrHandler(&Arr, dwSub1, dwSub2, dwSub3, dwSub4, dwSub5, (LPVIPERVAR)&vv, 1);   ----  I tried options 4 and 5 as well.

         txt = vv.lpstr;   ---- IT EXITS SILENTLY HERE.  txt is a string but have tried LPSTR and char*.


If I Trim() your data, it works. 

Code (winbatch) Select
   
     arrData[_row+1,_col] = StrTrim(ObjectType("BSTR",objRow.Item(objColumn)))   



Jim

JTaylor


JTaylor

Should mention that I don't think there is anything special about Trim().  The size reported correctly before Trim() so there aren't any non-printable characters involved.   I am guessing that if I did anything to make it evaluate the data in such a fashion it would probably work.

Jim

stanl

Quote from: JTaylor on January 18, 2021, 11:13:13 AM
Should mention that I don't think there is anything special about Trim().  The size reported correctly before Trim() so there aren't any non-printable characters involved.   I am guessing that if I did anything to make it evaluate the data in such a fashion it would probably work.

Jim


Just .02 but what if you try
      arrData[_row+1,_col] = objRow.Item(objColumn) 

      instead of

     ;arrData[_row+1,_col] = ObjectType("BSTR",objRow.Item(objColumn))
   
I think I added the BSTR based on another post where an array gave an issue. But still think it gives blank rows.. I'll try your update.

stanl

Just tried your new Extender... still gives blank rows

JTaylor

I had tried removing the ObjectType stuff.  Didn't help.

Odd.   It worked fine for me.   

Did you change your start row?   


Here is the entire script I used.  Just to make sure we are on the same page.


Code (winbatch) Select

;Winbatch 2020A - Parsing csv for SQLite Structure
;Stan Littlefield, January 18, 2021
;Updated to from original May 2020 script
;///////////////////////////////////////////////////////////////////////////////////////////////////////////////
gosub udfs
AddExtender(DirScript():"wbsql44i.dll")
IntControl(73,1,0,0,0)


;WB Map to mapping data types between .NET DataTable and SQLite
types = $"System.Boolean,INTEGER
System.Byte,INTEGER
System.Char,TEXT
System.DateTime,TEXT
System.Decimal,NUMERIC
System.Int32,INTEGER
System.Int64,REAL
System.Int16,INTEGER
System.Object,BLOB
System.Single,REAL
System.String,TEXT
System.Double,REAL
System.Array,BLOB
System.UInt16,INTEGER
System.UInt32,REAL
System.UInt64,REAL
System.IntPtr,TEXT$"
dTypes= MapCreate(types,'',@lf)


folder = dirscript()


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"'
;set up INI entry for Tab Delimted Format
cIni = "[WakeCounty.csv]":@CRLF
cIni = cIni:"Format=TabDelimited":@CRLF
cIni = cIni:"ColNameHeader=True":@CRLF
cIni = cIni:"MaxScanRows=10":@CRLF
cIni = cIni:"CharacterSet=ANSI":@CRLF
FilePut(Dirscript():"schema.ini",cIni)




db = DirScript():"NetTest.sqlite"
dbOpen(db,@FALSE) ;will create SQLite db if it doesn't exist
tbl="Accounts"


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 %tbl% (":@LF
ForEach col in oCols
   name = col.ColumnName
   colType = col.DataType.ToString()
   objRow = oTable.Rows.Item(0)
   val = objRow.Item(col)
   If MapKeyExist(dTypes,colType)
      cCreate = cCreate:name:" ":dTypes[ColType]:",":@LF
   Else
      cCreate = cCreate:name:"  BLOB":",":@LF
   Endif
   ;original UDF before Map
   ;cCreate = cCreate:name:" ":cvt(colType):",":@LF


Next
cCreate = StrSub(cCreate,1,Strlen(cCreate)-2):");"
Message("Schema",cCreate)


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] = StrTrim(ObjectType("BSTR",objRow.Item(objColumn)))
   Next
Next

dbExecute(db, "Drop table If Exists Accounts; ":@CRLF: cCreate)
dbExecute(db, "VACUUM;")


;arrData = ArrayFileGetCSV("stan.txt",0,@TAB)
;pre-known column count, but could be built dynamically
SQLText = "INSERT OR IGNORE INTO Accounts Values ('[{1}]','[{2}]','[{3}]','[{4}]','[{5}]','[{6}]','[{7}]','[{8}]','[{9}]');"
stmt = dbInsertArray(db,SQLText,arrData,2)


BoxShut()
dbClose()
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




;original udf to lookup and assign conversion
;replaced by WB Map
#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


Jim

stanl

I did change the start row and still shot blanks. Your script worked. I think my subroutine where I put arrData into a reportview messed things up. Stranger things have happened to me. Sorry if this sent you on a wild goose chase.

JTaylor

I thought the only thing I changed was the one line but maybe not.   In any event, glad what I posted worked.

No problem.  I found a bug as a result so that was a good thing.

Jim