SQLite pt. 2

[stanl]

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.

Code (WINBATCH) Select Expand



;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


[JTaylor]

Mostly unrelated but wanted to say thanks for your 2002 post on shaped recordsets...it may solve a problem for me.

Jim

[stanl]

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

Code Select Expand

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

[stanl]

This is ugly. Uses xml recordset previously attached

Code (WINBATCH) Select Expand



;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




[JTaylor]

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

[td]

Yeah. Talk about a stab from the past. I dug up that old script and discovered an issue. I was using a variable cOut

Code Select Expand

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.

[td]

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

[stanl]

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.

[stanl]

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 

[td]

Thanks. I had set BLOB as default in anticipation of types I might have missed.

Yup. I missed that.

[td]

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

[stanl]

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]