[RESOLVED] RC6 CreateTableFromADORs creating TEXT columns

[beltrao73]

Hi,

Is there any way to force RC6 CreateTableFromADORs to create VARCHAR columns instead of TEXT, in fields with less than 255 characters of data?

[Schmidt]

SQLite-Table-TextFields (no matter what type-description you use) - will always allow 0 to 2GB of characters
(via a dynamically changing len-descriptor which allows to keep the string-storage small and efficient)

May I ask, why you need that?

Olaf

[beltrao73]

Hi Olaf,

I´m using CreateTableFromADORs as a speedy middle step to download from Oracle (in the cloud) to a legacy MS Access database.

So I when I link the sqlite tables in MS Access (using ODBC), the TEXT Fields are considered as MEMO, and some inner joins with already existing string fields of the local tables (not imported) don´t work anymore.

[Schmidt]

...when I link the sqlite tables in MS Access (using ODBC),
the TEXT Fields are considered as MEMO

Ah, Ok.

In that case, you can use "the last optional parameter" (arrDataTypes As Variant):

It needs to be of type Variant and 2-dimensionally redimed beforehand: Redim (0 to AdoRs.Fields.Count-1, 0 to 1)

- arrDataTypes(i, 0) <-- 0 is for TypeNames (as e.g. Varchar) and all index-slots need to be filled completely for all Fields(i) in the Rs
- arrDataTypes(i, 1) <-- 1 is optional ... but if you set it, you will have to place an SQLite ColumnType-EnumValue there

HTH

Olaf

[beltrao73]

Thanks again Olaf!

I created this function to dinamically map ADO Data Types to sqlite types and it worked just fine.

Function MapDataType ( ADODataType)
	
	Select Case ADODataType
	
		Case 2,3,16,20
			ret = "INTEGER"
		
		Case 4,5,14,131,139
			ret = "REAL"
		
		Case 129,200,201,202
			ret = "VARCHAR"	
		
		Case 7, 133, 134, 135
			ret = "DATETIME"	
		
		Case 11
			ret = "BOOLEAN"
			
		Case 204,205
			ret = "BLOB"		
	
	End Select
	
	MapDataType = ret
End Function