Android Question Pragma table_info SQLite error

Enthousiastic

Member
Licensed User
Longtime User
Can you please tell me where the error is, because I spent many nights trying to make combinations in quotes and parentheses. It gives me the following error :
android.database.sqlite.SQLiteException: near "WHERE": syntax error (code 1 SQLITE_ERROR): , while compiling: PRAGMA table_info(MyRts) WHERE cid = FieldNumber

Many thanks
in advanced


sql query:
Dim TBQuery As String  = "PRAGMA table_info(" & TheTableName & ") WHERE cid = FieldNumber "
 

Serge Bertet

Active Member
Licensed User
Longtime User
Try this syntax :

B4X:
Dim TBQuery As String = $"PRAGMA table_info(${TheTableName}) WHERE cid = FieldNumber"$

For your information, here is a working function :

B4X:
Private Sub ColumnExists(TableName As String, ColumnName As String) As Boolean
    Dim csr As Cursor = myDB.ExecQuery($"PRAGMA table_info(${TableName})"$)
    For i = 0 To csr.RowCount - 1
        csr.Position = i
        If csr.GetString("name") = ColumnName Then Return True
    Next
    Return False
End Sub
 
Upvote 0

Enthousiastic

Member
Licensed User
Longtime User
Thank you or your answer . As you see in the code , i want to return the column name of 2nd , first or last field and so on ... that's why i include where . So where is the mistake ?


Return the name of fieldname:
Sub FieldNameOrder(TheTableName As String , FieldNumber As Int ) As String
      
    Dim tbrs As ResultSet
    
    tbrs.IsInitialized
    
    Dim s As String
    
'    Dim TBQuery As String  = "PRAGMA table_info(" & TheTableName & ")"
    
    Dim TBQuery As String  = "PRAGMA table_info(" & TheTableName & ") WHERE cid = FieldNumber "

    tbrs = SQL1.ExecQuery(TBQuery)
    
    
    
'    cur = SQL1.ExecQuery("PRAGMA table_info(DBTableName);")


        
            If FieldNumber<0 Then FieldNumber = 0
        
    If FieldNumber> tbrs.ColumnCount Then FieldNumber = tbrs.ColumnCount
        
            '    Log(" Field Names No 2 are :    " & s)
        
    
    s = tbrs.GetString("name")
    
    
    Log(" Final now is     :    " & s)
    
    
    
    tbrs.Close
    
    Return s
    
End Sub
 
Upvote 0

Serge Bertet

Active Member
Licensed User
Longtime User
Yes you'r rigth: no WHERE in PRAGMA table_info.
This should work:
B4X:
    Dim csr As Cursor = DB.ExecQuery($"PRAGMA table_info(${TableName})"$)
    For i = 0 To csr.RowCount - 1
        csr.position = i  ' i is the FieldNumber 
        Log($"Column #${i} name: ${csr.GetString("name")}"$)
    Next
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
I suggest to use Resultset instead of Cursor, which will also works on B4J or B4i.

B4X:
Sub ColumnName (Table As String, Index As Int) As String
    Dim s As String = "Not found"
    Dim query As String  = $"PRAGMA table_info(${Table})"$
    Dim rs As ResultSet = SQL1.ExecQuery(query)
    Do While rs.NextRow
        If rs.GetInt("cid") = Index Then
            s = rs.GetString("name")
            Exit
        End If
    Loop
    rs.Close
    Return s
End Sub
 
Upvote 0

Isaacc

Member
The problem is that PRAGMA table_info() doesn’t support a WHERE clause. That's why the syntax error occurs. To filter rows by cid, query all columns and then check in code:
B4X:
Dim csr As Cursor = SQL1.ExecQuery($"PRAGMA table_info(${TheTableName})"$)
csr.Position = FieldNumber
s = csr.GetString("name")
 
Upvote 0
Top