SQLite - how to detect field type

Databases
1

Using SQLite, I’m not sure if this is normal behaviour. I’m cycling through the fields of a table and for most fields, the file type returned is ‘5’, which I believe is ‘Text’ or ‘VarChar’ field. I say most fields as when the table is created and a field is assigned as ‘REAL’ the file type returned is ‘7’. All ‘INT’ types are returned as ‘5’, again as ‘Text’ or ‘VarChar’ fields.

I’ve been reading Create Table - Sqlite and it seems that types are not strictly adhered to but, it is possible to at least specify types in the ‘Create Table’ sql.

Here is a short sample of an SQl Create Table snippet; the table is in fact created. The snippet is pulled from a larger SQL file whereby many tables are created in one hit.

CREATE TABLE tAccount_Types( Account_Type_ID INT PRIMARY KEY ASC NOT NULL, Account_Type_Name TEXT);

My question is, is there a way to examine a field and return the field type that was set via the initial ‘Create Table’ routine?

For completeness, here’s my code to cycle through the fields of a table “GetFields(pStrTableName as string)”

//Const MAXCOLUMN = 6
Dim rsf As RecordSet
Dim c As Integer            // columns
Dim intColumns As Integer    // Number of columns

'rsf = g.DB.FieldSchema(pStrTableName)
rsf = dbs.FieldSchema(pStrTableNames)

lstFields.DeleteAllRows

intColumns = rsf.FieldCount

// Add the DB columns as the heades for the ListBox
lstFields.ColumnCount = rsf.FieldCount
lstFields.Column(-1).WidthExpression = "100"
For i As Integer = 0 To rsf.FieldCount-1
  lstFields.Heading(i) = rsf.IdxField(i+1).Name
Next

If Not (rsf Is Nil) Then
  rsf.MoveFirst
  While Not rsf.EOF
    lstFields.AddRow ""
    
    For c = 0 To intColumns -1
      If c = 1 Then
        'lstFields.Cell(lstFields.LastIndex,c) = GetFieldType(rsf.IdxField(c+1).Value)
        lstFields.Cell(lstFields.LastIndex,c) = rsf.IdxField(c+1).Value
      Else
        lstFields.Cell(lstFields.LastIndex,c) = rsf.IdxField(c+1).Value
      End If
    Next c
    rsf.MoveNext
  Wend
Else
  lstFields.AddRow "PROBLEM..."
End If 

rsf.Close

Here’s a sample output:

https://www.dropbox.com/s/kzgy1sff4ll28an/2017-04-24_22-22-50.jpg?dl=0
https://www.dropbox.com/s/tl4njpt4rlojqdc/2017-04-24_22-15-00.jpg?dl=0

2

Use

dim rs as recordset = d.SQLSelect("pragma tableinfo(" + tableName + ");")

The recordset will return:

cid Integer name text type text <-- This is the one you need notnull integer dflt_value text pk integer
There will be an entry for each column within the table.

3

Simon’s trick will work only for sqlite.
you can use xojo fieldschema function, but it will not return all the possible types, but will work for other databases.
https://documentation.xojo.com/index.php/Database.FieldSchema

4

Or use the built in FieldSchema function

Both Simon and I have(are) producing an SQLite manager using Xojo… both our projects have different philosophy, and design, but there are a lot of things that will trip you up due to the way SQLite does (or does not) conform to strict datatypes (particularly when you get to BOOLEAN and DATE/TIME information)

5

If you want to get the originally used CREATE string, extract it from the sqlite_master Table.

6

[quote=327505:@Simon Berridge]Use

dim rs as recordset = d.SQLSelect("pragma tableinfo(" + tableName + ");")

…snip…
.[/quote]
Thanks Simon, your solution worked, though an underscore is required, ‘tableinfo’ should be ‘table_info’. More info here, not for you Simon but others that might read this thread.

[quote=327508:@Dave S]
…snip…
Both Simon and I have(are) producing an SQLite manager using Xojo… both our projects have different philosophy, and design, but there are a lot of things that will trip you up due to the way SQLite does (or does not) conform to strict datatypes (particularly when you get to BOOLEAN and DATE/TIME information)[/quote]

I can see that dealing with these two types might be problematic, no doubt in time I may seek assistance.