Android Question Access to field type in SqlLite database

[Paolo Pini]

Hi,

I have records in a SqlLite table that I need to insert into a MsAccess table with the same structure.

Unfortunately I have to check each field in the export loop to check the data status:

if data is Null replace with '' if it is a text or 0 if is a number
if data is a date enter # character and reverse dd/mm

etc.

Initially I tried using:

 For b=0 To Cur.ColumnCount - 1
                fieldType=r.RunMethod2("getType", b, "java.lang.int")

but the fieldType doesn't seem to follow the sequence of the actual fields:

for example I have :
(SqlLite table structure)
SELF INTEGER "SELF" INTEGER
TODAY TEXT "TODAY" INTEGER
NAME TEXT "NAME" TEXT
......

and the result of fieldType is
0
3
0
.... and so on without a clear correspondance (for me)

Perhaps the fields are not sorted in the same way?

In any case the "Cur.ColumnCount" returns the correct number of fields.

Then I tried PRAGMA:

Cur = Starter.sql.ExecQuery("PRAGMA table_info('MyTable')")

but the query returns only 1 record with the header:

"cid name type notnull dflt_value pk"

and nothing else.

What am i doing wrong?

Many thanks in advance

Paolo

 

[coldtech]

Have you tried just writing a query to do this using IFNULL? I think it works similar to T-SQL ISNULL function. Not sure if that will work for you.

 

[Paolo Pini]

Have you tried just writing a query to do this using IFNULL? I think it works similar to T-SQL ISNULL function. Not sure if that will work for you.

Thanks for the answer.

I have more than 100 fields, so I use SELECT * to avoid to hardcode the fields names (I create the sqllite table directly from the MsAcces table, so the fields are always aligned) and to simplify editing.

However if there is no other option I will do it.

Regards

 

[cklester]

This might help: 5 Ways to Check a Column’s Data Type in SQLite

 

[walt61]

I have used this code which seems to work:

Public Sub SQLiteGetTableColumnTypes(theSQLdb As SQL, tblName As String) As Map

    Dim m As Map
    m.Initialize

    Try
        Dim rs As ResultSet = theSQLdb.ExecQuery("PRAGMA table_info(" & tblName & ")")
        Do While rs.NextRow
            Dim t As String = rs.GetString("type").ToLowerCase
            Select Case t
                Case "int", "numeric"
                    ' Keep the value
                Case Else
                    t = "text"
            End Select
            m.put(rs.GetString("name"), t)
        Loop
        rs.Close
        Return m
    Catch
        Log("SQLiteGetTableColumnTypes: " & LastException)
        Return m
    End Try

End Sub

It doesn't recognise dates as such. I presume (from what you wrote) that the dates are stored as text, so for text columns you may want to have a look at the values to decide whether or not they're valid dates, and take it from there (or just at the value in the first data row you fetch, to determine which columns contain dates - provided that the data are clean). Anyway, that's how I'd do it, perhaps there are better ways Perhaps start by using the above method and see what it returns for your db. Good luck!

 

[Paolo Pini]

I have used this code which seems to work:

Public Sub SQLiteGetTableColumnTypes(theSQLdb As SQL, tblName As String) As Map

    Dim m As Map
    m.Initialize

    Try
        Dim rs As ResultSet = theSQLdb.ExecQuery("PRAGMA table_info(" & tblName & ")")
        Do While rs.NextRow
            Dim t As String = rs.GetString("type").ToLowerCase
            Select Case t
                Case "int", "numeric"
                    ' Keep the value
                Case Else
                    t = "text"
            End Select
            m.put(rs.GetString("name"), t)
        Loop
        rs.Close
        Return m
    Catch
        Log("SQLiteGetTableColumnTypes: " & LastException)
        Return m
    End Try

End Sub

It doesn't recognise dates as such. I presume (from what you wrote) that the dates are stored as text, so for text columns you may want to have a look at the values to decide whether or not they're valid dates, and take it from there (or just at the value in the first data row you fetch, to determine which columns contain dates - provided that the data are clean). Anyway, that's how I'd do it, perhaps there are better ways Perhaps start by using the above method and see what it returns for your db. Good luck!

Thanks,
it works fine! ?

                Case "int", "numeric"

must be:

                Case "integer", "numeric"

In my table I found only 'integer' and 'text'.

For the date field there's no problem I know the field names.

Regards.

 

[walt61]

Excellent, and thanks for the correction/addition, that one must have slipped through the cracks!

 

[Mahares]

but the query returns only 1 record with the header:

"cid name type notnull dflt_value pk"

I am glad @walt61 helped you figure out your problem, but in your post #1, you had to iterate over the resultset to get all the column of the given table. You were on the right track, but you did not finish. You can put the info in a map or preferably a list.:

dim cur as resultset =  Starter.sql.ExecQuery("PRAGMA table_info('MyTable')")
Do While cur.nextrow
      Log("col name " & cur.GetString("name")) 
      Log("col type " & cur.GetString("type"))   
      Log("col pk " & cur.GetString("pk"))        
      Log("col dfit " & cur.GetString("dflt_value"))  
      Log("notnull " & cur.GetString("notnull"))    
      Log("cid col idx" & cur.GetString("cid"))    
loop      
cur.close

By the way, whatever the table structure shows, the type will display it the same way. The type can be TEXT, NUMERIC, INT, INTEGER, BLOB, REAL and others.

 

[Paolo Pini]

I am glad @walt61 helped you figure out your problem, but in your post #1, you had to iterate over the resultset to get all the column of the given table. You were on the right track, but you did not finish. You can put the info in a map or preferably a list.:

dim cur as resultset =  Starter.sql.ExecQuery("PRAGMA table_info('MyTable')")
Do While cur.nextrow
      Log("col name " & cur.GetString("name"))
      Log("col type " & cur.GetString("type"))  
      Log("col pk " & cur.GetString("pk"))       
      Log("col dfit " & cur.GetString("dflt_value")) 
      Log("notnull " & cur.GetString("notnull"))   
      Log("cid col idx" & cur.GetString("cid"))   
loop     
cur.close

By the way, whatever the table structure shows, the type will display it the same way. The type can be TEXT, NUMERIC, INT, INTEGER, BLOB, REAL and others.

Thanks Mahares for your clarification, I didn't quite understand how PRAGMA works, now I have more systems to deal with the problem.