Per affinity as Astra

[spl]

This goes back to recent SQLite threads referring to SQLite Affinity. One of the scripts I posted used a WB map to convert ADODB datatypes [numerics] to SQLite data types. Not sure if really needed, so tested further with ADO .NET with datatypes

Code Select Expand

System.Boolean
System.Byte
System.Char
System.DateTime
System.Decimal
System.Int32
System.Int64
System.Int16
System.Object
System.Single
System.String
System.Double
System.Array
System.UInt16
System.UInt32
System.UInt64
System.IntPtr

The script below [using an Excel dates calendar example, which can be created from previous posts I made] uses CLR to open the Excel file and make a Create Statement for SQLite, without a map. Simply remove "System." for the datatype string variable and the table is created - so in this case Affinity works w/out a lookup map.

Understand, a lot of posts and examples for SQLite assume you already know the column names and types when making a Create Statement... this is for those who might have to translate from a source object [Excel, CSV, TXT] => SQLite

Code Select Expand

;Winbatch 2022C - prepare .Net DataTble from Excel and inset into SQLite
;Stan Littlefield, June 15, 2024
;
;NOTE: tested as 64bit WB and used 64bit SQLite .NET dll                       
;///////////////////////////////////////////////////////////////////////////////////////////////////////////////
gosub udfs
IntControl(73,1,0,0,0)
cXLS = dirscript():"dates.xlsx"
sqlite =  dirscript():"createdates.db"
If ! FileExist(cXLS) Then Terminate(@TRUE,"Cannot Continue",cXLS:" is missing")
cConn='Provider=Microsoft.ACE.OLEDB.16.0;Data Source=%cXLS%;Extended Properties="Excel 12.0 Xml;HDR=YES"'

If FileExist(sqlite) Then FileDelete(sqlite)

base = dirscript():"\64bit"  ;folder where  System.Data.SQLite.dll located
ObjectClrOption("AppBase", base)
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 [dates$];"
oCmd.Connection = oConn
oCmd.CommandText = cSQL

oAdapter = ObjectClrNew( 'System.Data.OleDb.OleDbDataAdapter')
oTable = ObjectClrNew( 'System.Data.DataTable')                 
oAdapter.SelectCommand = oCmd
rowcount = oAdapter.Fill(oTable)
colcount = oTable.Columns.Count
oCols = oTable.Columns
cCreate = "Create Table Dates (":@LF
ForEach col in oCols
   name = "`":col.ColumnName:"`"
   colType = col.DataType.ToString()
   colType = strreplace(colType,"System.","")
   objRow = oTable.Rows.Item(0)
   val = objRow.Item(col)
   cCreate = cCreate:name:" ":colType:",":@LF
   ;cCreate = cCreate:name:" ":cvt(colType):",":"[VALUE: %val% .net type %colType%]":@LF
Next
cCreate = Strsub(cCreate,1,strlen(cCreate)-2):@LF:");"
oAdapter.Dispose()
oTable=0
oCmd=0
oConn=0
Message("Create Statement",cCreate)

;try to create sqlite table
Display(2,"Creating ":sqlite,"Begin data load")
cn= ObjectClrNew("System.Data.SQLite.SQLiteConnection")
cn.ConnectionString = "Data Source=":sqlite
cm = objectClrNew("System.Data.SQLite.SQLiteCommand")
cm.Connection  = cn
cn.Open()
cm.CommandText = cCreate
cm.ExecuteNonQuery()
cm=0
cn.Close()

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

Return