Android Question sqlite: search blob item

Antonio D'Angeli

Member
Licensed User
i ave one question:
search row whit item"timestamp" = ? (where ? as blob)
B4X: 
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
 
Sort by date Sort by votes

KMatle

Expert
Licensed User
Longtime User
Please show the logs. However: Why do you use a blob column for a timestamp anyway?
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Antonio D'Angeli said:
search row whit item"timestamp" = ? (where ? as blob)
Click to expand...

If you insist on using Blob for timestamp, pleas try this code:
B4X: 
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
 
Upvote 0

Antonio D'Angeli

Member
Licensed User
KMatle said:
Please show the logs. However: Why do you use a blob column for a timestamp anyway?
Click to expand...
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
 
Last edited:
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Antonio D'Angeli said:
your code not work (argument 1 has type java.lang.String[], got java.lang.Object[])
Click to expand...
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/.
 
Last edited:
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Erel said:
Option 1 will most probably won't work. The underlying API doesn't seem to support it.
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…