Android Question Access to field type in SqlLite database

Paolo Pini · May 9, 2023

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:

B4X:

 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:

B4X:

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 · May 9, 2023

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 · May 9, 2023

coldtech said:
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 · May 9, 2023

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

walt61 · May 9, 2023

I have used this code which seems to work:

B4X:

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 · May 9, 2023

walt61 said:
I have used this code which seems to work:

B4X:

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! ?

B4X:

                Case "int", "numeric"

must be:

B4X:

                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 · May 9, 2023

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

Mahares · May 10, 2023

Paolo Fiaschi said:
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.:

B4X:

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 · May 10, 2023

Mahares said:
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.:

B4X:

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.

Android Question Access to field type in SqlLite database

Paolo Pini

Member

coldtech

Member

Paolo Pini

Member

cklester

Well-Known Member

walt61

Active Member

Paolo Pini

Member

walt61

Active Member

Mahares

Expert

Paolo Pini

Member

Similar Threads

Android Question Access to field type in SqlLite database

Member

Member

Member

Well-Known Member

Active Member

Member

Active Member

Expert

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency