Android Question sqlite: search blob item

[Antonio D'Angeli]

i ave one question:
search row whit item"timestamp" = ? (where ? as blob)

Sub SeEsiste(timestamp() As Byte, NomeTab As String) As Boolean
    Dim Cursor1 As Cursor
        Cursor1=sqlpi.ExecQuery("Select count(*) as tot FROM " & NomeTab & " WHERE timestamp = '" & timestamp & "'" )

    Cursor1.Position=0
    If Cursor1.GetInt ("tot")>0 Then
            Cursor1.Close
            Return True
        Else
            Cursor1.Close
            Return False
    End If
End Sub

does not work ??
thank

 

[KMatle]

Please show the logs. However: Why do you use a blob column for a timestamp anyway?

 

[Mahares]

search row whit item"timestamp" = ? (where ? as blob)

If you insist on using Blob for timestamp, pleas try this code:

Sub SeEsiste(timestamp() As Byte, NomeTab As String) As Boolean
    Dim tot As Int
    tot=sqlpi.ExecQuerySingleResult2("Select count(*) FROM " & NomeTab & " WHERE timestamp = ?", _
    Array As Object(timestamp))
    If tot =0     Then
        Return False
    End If
    Return True
End Sub

 

[Antonio D'Angeli]

Please show the logs. However: Why do you use a blob column for a timestamp anyway?

because the sqlite table is synchronized with identical table on Mssql
your code not work (argument 1 has type java.lang.String[], got java.lang.Object[])
thanks

 

[OliverA]

your code not work (argument 1 has type java.lang.String[], got java.lang.Object[])

The sqlite interface in B4A is a little different from the sqlite interface from B4J. I'm assuming that the B4A interface (the standard SQL library) is based in Android's SQLiteDatabase class (https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html). The various query methods of that class use a string array for passing on parameters. There are two solutions to this problem:

1) Figure out (meaning, hopefully someone can tell us) how to access the underlying SQLiteDatabase class of the SQL object returned by B4A. Then use JavaObject and/or Reflection to access the compileStatement method. Then use the returned SQLiteStatement's (https://developer.android.com/reference/android/database/sqlite/SQLiteStatement.html) bindBlob method (https://developer.android.com/reference/android/database/sqlite/SQLiteProgram.html#bindBlob(int, byte[])) to bind the blob to the parameter (the ?). Finally, use the SQLiteStatement's simpleQueryForLong method to complete the query.

Note: #1 works because all that is needed is a single result. If the SELECT would return multiple results/multiple rows, this will not work (that is the limitation of the compliedStatement method/SQLiteStatement class).

2) Use jdbcSQL. Use the InitializeSQLite (https://www.b4x.com/b4j/help/jsql.html#sql_initializesqlite) method to open your SQLite database. @Mahares code should then work. You'll need to include the SQLite driver (.jar file) in you AdditionalJar. You can either use the .jar that comes with B4J (sqlite-jdbc-3.7.2) or download the latest from here https://bitbucket.org/xerial/sqlite-jdbc/downloads/.

 

[Erel]

Option 1 will most probably won't work. The underlying API doesn't seem to support it.
See the documentation of:
https://developer.android.com/refer...abase.html#compileStatement(java.lang.String)

I wouldn't go with option 2 as you need to add an external driver for this.

 

[OliverA]

Option 1 will most probably won't work. The underlying API doesn't seem to support it.

That's what I did look at (the docs) and in this one instance (returning a long for the COUNT(*)), a SELECT would (yes, I'm guessing here) work with compileStatement. You have to combine it with the bindBlob method to pass on the Blob's byte array. SQLiteStatement's simpleQueryForLong method (https://developer.android.com/refer...ite/SQLiteStatement.html#simpleQueryForLong()) actually mentions the SELECT COUNT(*) case.