Running the script with attached xml recordset will pop up a create statement based on .NET datatypes. I'm looking to use the 'portable' System.Data.SQLite dll and create SQLite tables from other sources. I know a lot of the conversions [ i.e. System.String ==> TEXT ] are obvious but wondering how to handle the more specific types, like System.Byte. I have read there is an SQLite GetType() function but maybe a simple lookup would suffice.
;Winbatch 2020A - prepare .Net DataTble for SQLite
;Stan Littlefield, May 1, 2020
;///////////////////////////////////////////////////////////////////////////////////////////////////////////////
gosub udfs
IntControl(73,1,0,0,0)
cXML=dirscript():"Struct.xml"
If ! FileExist(cXML) Then Terminate(@TRUE,"Cannot Continue",cXML:" is missing")
cConn='Provider=MSPersist'
ObjectClrOption("useany","System.Data")
oConn = ObjectClrNew( 'System.Data.OleDb.OleDbConnection',cConn)
oConn.Open()
oCmd = ObjectClrNew( 'System.Data.OleDb.OleDbCommand')
cSQL = cXML
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 Test (":@LF
ForEach col in oCols
cCreate = cCreate:col.ColumnName:" ":col.DataType.ToString():",":@LF
Next
cCreate = cCreate:");"
oAdapter.Dispose()
oTable=0
oCmd=0
oConn=0
Message("Create Statement",cCreate)
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
Mostly unrelated but wanted to say thanks for your 2002 post on shaped recordsets...it may solve a problem for me.
Jim
Quote from: JTaylor on May 01, 2020, 03:05:25 PM
Mostly unrelated
Yeah. Talk about a stab from the past. I dug up that old script and discovered an issue. I was using a variable cOut
cOut=FileOpen(cTXT, "WRITE") which is now a reserved word for output to a WB console window.
.... still appreciate thoughts on converting .NET types to SQLite
This is ugly. Uses xml recordset previously attached
;Winbatch 2020A - prepare .Net DataTble for SQLite
;Stan Littlefield, May 1, 2020
;///////////////////////////////////////////////////////////////////////////////////////////////////////////////
gosub udfs
IntControl(73,1,0,0,0)
cXML=dirscript():"Struct.xml"
If ! FileExist(cXML) Then Terminate(@TRUE,"Cannot Continue",cXML:" is missing")
cConn='Provider=MSPersist'
ObjectClrOption("useany","System.Data")
oConn = ObjectClrNew( 'System.Data.OleDb.OleDbConnection',cConn)
oConn.Open()
oCmd = ObjectClrNew( 'System.Data.OleDb.OleDbCommand')
cSQL = cXML
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 Test (":@LF
ForEach col in oCols
colType = col.DataType.ToString()
cCreate = cCreate:col.ColumnName:" ":cvt(colType):",":@LF
Next
cCreate = cCreate:");"
oAdapter.Dispose()
oTable=0
oCmd=0
oConn=0
Message("Create Statement",cCreate)
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="REAL"
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"
Return(retval)
#EndSubRoutine
Return
They have such a narrow choice I am guessing where you went is on the right track. Could use GetType() or typeof() but that doesn't really help the one part of the problem. Might be a good use for a "Map" though. Then you could do something like the following. If certain you have covered all the bases then wouldn't need the "If" statement.
If MapKeyExist(coltype) Then
DTypes[col_type]
Else
DTypes["System.Object"]
EndIf
Not sure if this will help or not.
https://www.sqlite.org/datatype3.html
3.1. Determination Of Column Affinity
The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown:
If the declared type contains the string "INT" then it is assigned INTEGER affinity.
If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.
If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB.
If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity.
Otherwise, the affinity is NUMERIC.
Note that the order of the rules for determining column affinity is important. A column whose declared type is "CHARINT" will match both rules 1 and 2 but the first rule takes precedence and so the column affinity will be INTEGER.
3.1.1. Affinity Name Examples
The following table shows how many common datatype names from more traditional SQL implementations are converted into affinities by the five rules of the previous section. This table shows only a small subset of the datatype names that SQLite will accept. Note that numeric arguments in parentheses that following the type name (ex: "VARCHAR(255)") are ignored by SQLite - SQLite does not impose any length restrictions (other than the large global SQLITE_MAX_LENGTH limit) on the length of strings, BLOBs or numeric values.
Example Typenames From The CREATE TABLE Statement or CAST Expression Resulting Affinity Rule Used To Determine Affinity
INT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8 INTEGER 1
CHARACTER(20)
VARCHAR(255)
VARYING CHARACTER(255)
NCHAR(55)
NATIVE CHARACTER(70)
NVARCHAR(100)
TEXT
CLOB TEXT 2
BLOB
no datatype specified BLOB 3
REAL
DOUBLE
DOUBLE PRECISION
FLOAT REAL 4
NUMERIC
DECIMAL(10,5)
BOOLEAN
DATE
DATETIME NUMERIC 5
Quote from: stanl on May 02, 2020, 02:58:15 AM
Yeah. Talk about a stab from the past. I dug up that old script and discovered an issue. I was using a variable cOut cOut=FileOpen(cTXT, "WRITE") which is now a reserved word for output to a WB console window.
Thanks for pointing that out. Normally, WIL function names are not an issue but in this case, something about
cIn and
cOut needs to be added to the Readme.txt file under the "Converting scripts to the latest version" section because those command names are simple enough to be a potential backward compatibility issue.
Quote from: stanl on May 02, 2020, 04:17:10 AM
This is ugly. Uses xml recordset previously attached
You might need to add the "System.Array" type to your type mapping UDS. The Framework likes to store binary data as an array of bytes which SQLite should be able to treat as type "BLOB".
Quote from: td on May 02, 2020, 10:24:49 PM
You might need to add the "System.Array" type to your type mapping UDS. The Framework likes to store binary data as an array of bytes which SQLite should be able to treat as type "BLOB".
Thanks. I had set BLOB as default in anticipation of types I might have missed. Toyed with using a WB map as a lookup which would allow adding relations for types w/out having to modify script.
Quote from: JTaylor on May 02, 2020, 05:50:49 AM
They have such a narrow choice I am guessing where you went is on the right track.
I used the SQLite datatype url you pointed out and implemented Tony's suggestion for System.Array, and in that reply mentioned maps. While putting together the mspersist file I tried to use the Access 2019 'Large Number' data type which would equate to a BigInt datatype that is prevalent in SAP Hana db's. Hana uses a construct called SEQUENCE which is roughly consistent with the AUTONUMBER. Problems with 'Large Number' - not backward compatible, can't be accessed with ACE Provider
Quote from: stanl on May 03, 2020, 03:42:35 AM
Thanks. I had set BLOB as default in anticipation of types I might have missed.
Yup. I missed that.
A few other "non-blob" types that you will likely never encounter but worth mentioning anyway:
System.SByte
System.UInt16
System.UInt32
System.UInt64
System.IntPtr
Quote from: td on May 03, 2020, 01:25:23 PM
A few other "non-blob" types that you will likely never encounter but worth mentioning anyway:
System.SByte
System.UInt16
System.UInt32
System.UInt64
System.IntPtr
Thanks again. I got the LargeNumber figured out. Had issues with the Microsoft Click_to_Run service [as have many others]. My next task is to completely port a large Excel table or .csv file to SQLite [using the CLR and System.Data.SQLite]