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 "
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
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
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
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
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")
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 "