Re: [sqlite] querying with BLOB in WHERE clause, possible?

[Stephan Beal]

On Fri, Nov 14, 2014 at 3:57 PM, bjdodo  wrote:

> I've got it now. E.g. for delete you can do this:
>

A minor fix:

the 'try' needs to be moved to...


>
> int argidx = 0;
> SQLiteStatement statement = mDB.compileStatement(sql);
>

here. Because...


> throw new RuntimeException("unexpected argument type);
>

if that happens then you're not closing it.


> try {
> return statement.executeUpdateDelete();
> } finally {
> statement.close();
> }
>

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] querying with BLOB in WHERE clause, possible?

[bjdodo]

I've got it now. E.g. for delete you can do this:


String sql = "DELETE FROM " + table +
(!TextUtils.isEmpty(whereClause) ? " WHERE " + whereClause :
"");

int argidx = 0;
SQLiteStatement statement = mDB.compileStatement(sql);
if (whereArgs != null) {
for (WhereArg wa : whereArgs) {
Object o = wa.getParamValueRaw();
if (o == null) {
statement.bindNull(++argidx);
} else if (o instanceof String) {
statement.bindString(++argidx, (String) o);
} else if (o instanceof Long) {
statement.bindLong(++argidx, (Long) o);
} else if (o instanceof Double) {
statement.bindDouble(++argidx, (Double) o);
} else if (o instanceof byte[]) {
statement.bindBlob(++argidx, (byte[]) o);
} else {
throw new RuntimeException("unexpected argument type);
}
}
}

try {
return statement.executeUpdateDelete();
} finally {
statement.close();
}







--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/querying-with-BLOB-in-WHERE-clause-possible-tp44550p79220.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] querying with BLOB in WHERE clause, possible?

[Clemens Ladisch]

bjdodo wrote:
> Sorry for resurrecting an old thread. I got the where clause working for
> queries with byte array arguments based on this discussion. I cannot find
> the way to use byte arrays in where clauses for update and delete
> statements.

In the Android database API, execSQL() is the only function where the
parameters are not String[] but Object[]:

  byte[] blob = ...;
  db.execSQL("DELETE FROM t WHERE my_blob = ?", new Object[]{ blob });


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] querying with BLOB in WHERE clause, possible?

[Kees Nuyt]

On Fri, 14 Nov 2014 04:59:58 -0700 (MST), bjdodo 
wrote:

>Hi
>
>Sorry for resurrecting an old thread. I got the where clause working for
>queries with byte array arguments based on this discussion. I cannot find
>the way to use byte arrays in where clauses for update and delete
>statements. I know it is terrible to use byte arrays as query arguments, I
>need this because of some 3rd party library problems. I also know that I can
>query for the row and do the update using some other column in the where
>clause I just find that "hacky". So if there is a way to do this trick for
>deletes and updates please let me know.


$ sqlite3 test.sqlite
SQLite version 3.8.8 2014-11-13 14:30:56
Enter ".help" for usage hints.
sqlite> CREATE TABLE t (id INTEGER PRIMARY KEY NOT NULL, bl BLOB);
sqlite> INSERT INTO T VALUES (1,x'313131');
sqlite> INSERT INTO T VALUES (2,x'323232');
sqlite> UPDATE T SET bl=x'33' WHERE bl=x'323232';
sqlite> SELECT * FROM t;
1|111
2|333
sqlite> DELETE FROM t WHERE bl=x'313131';
sqlite> SELECT * FROM t;
2|333
sqlite>

>Thank you,
>Jozsef

Hope this helps.

-- 
Regards,
Kees Nuyt


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] querying with BLOB in WHERE clause, possible?

[bjdodo]

Hi

Sorry for resurrecting an old thread. I got the where clause working for
queries with byte array arguments based on this discussion. I cannot find
the way to use byte arrays in where clauses for update and delete
statements. I know it is terrible to use byte arrays as query arguments, I
need this because of some 3rd party library problems. I also know that I can
query for the row and do the update using some other column in the where
clause I just find that "hacky". So if there is a way to do this trick for
deletes and updates please let me know.

Thank you,
Jozsef





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/querying-with-BLOB-in-WHERE-clause-possible-tp44550p79209.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] querying with BLOB in WHERE clause, possible?

[Andreas Schildbach]

On 12/17/2011 09:02 PM, Igor Tandetnik wrote:

 Using the Android API, I'd like to do something like SELECT * from
 my_table WHERE my_blob=?

 My problem is, all of the query methods only take strings as a parameter
 for the '?' placeholder. How am I supposed to pass in a byte array?
>>>
>>> http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#compileStatement(java.lang.String)
>>> http://developer.android.com/reference/android/database/sqlite/SQLiteProgram.html#bindBlob(int,
>>>  byte[])
>>
>> Thanks for your answer. However, AFAIK compiled statements cannot be
>> used for queries (other than those returning only a single value).
> 
> Yes you are right. There's another class, SQLiteQuery, that is used 
> internally by Cursor as a wrapper around a SQLite handle, and that has 
> bindBlob method. But there doesn't seem to be any convenient way to get to it.
> 
> There may be one backdoor though. You should be able to write a class that 
> implements SQLiteDatabase.CursorFactory interface, and pass it to 
> SQLiteDatabase.queryWithFactory or rawQueryWithFactory. The method will turn 
> around and call newCursor on your factory, passing SQLiteQuery as a 
> parameter. You should be able to set the query's parameters, then just create 
> and return a new instance of SQLiteCursor (its constructor is public).

This works. Thanks for the hint!

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] querying with BLOB in WHERE clause, possible?

[Igor Tandetnik]

Andreas Schildbach  wrote:
> On 12/17/2011 04:10 PM, Igor Tandetnik wrote:
> 
>>> Using the Android API, I'd like to do something like SELECT * from
>>> my_table WHERE my_blob=?
>>> 
>>> My problem is, all of the query methods only take strings as a parameter
>>> for the '?' placeholder. How am I supposed to pass in a byte array?
>> 
>> http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#compileStatement(java.lang.String)
>> http://developer.android.com/reference/android/database/sqlite/SQLiteProgram.html#bindBlob(int,
>>  byte[])
> 
> Thanks for your answer. However, AFAIK compiled statements cannot be
> used for queries (other than those returning only a single value).

Yes you are right. There's another class, SQLiteQuery, that is used internally 
by Cursor as a wrapper around a SQLite handle, and that has bindBlob method. 
But there doesn't seem to be any convenient way to get to it.

There may be one backdoor though. You should be able to write a class that 
implements SQLiteDatabase.CursorFactory interface, and pass it to 
SQLiteDatabase.queryWithFactory or rawQueryWithFactory. The method will turn 
around and call newCursor on your factory, passing SQLiteQuery as a parameter. 
You should be able to set the query's parameters, then just create and return a 
new instance of SQLiteCursor (its constructor is public).
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] querying with BLOB in WHERE clause, possible?

[Andreas Schildbach]

On 12/17/2011 04:10 PM, Igor Tandetnik wrote:

>> Using the Android API, I'd like to do something like SELECT * from
>> my_table WHERE my_blob=?
>>
>> My problem is, all of the query methods only take strings as a parameter
>> for the '?' placeholder. How am I supposed to pass in a byte array?
> 
> http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#compileStatement(java.lang.String)
> http://developer.android.com/reference/android/database/sqlite/SQLiteProgram.html#bindBlob(int,
>  byte[])

Thanks for your answer. However, AFAIK compiled statements cannot be
used for queries (other than those returning only a single value).

There is no way to aquire a cursor, or to aquire different fields after
having executed the statement.

If you think it should work, could you give a code example?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] querying with BLOB in WHERE clause, possible?

[Igor Tandetnik]

Andreas Schildbach  wrote:
> Using the Android API, I'd like to do something like SELECT * from
> my_table WHERE my_blob=?
> 
> My problem is, all of the query methods only take strings as a parameter
> for the '?' placeholder. How am I supposed to pass in a byte array?

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#compileStatement(java.lang.String)
http://developer.android.com/reference/android/database/sqlite/SQLiteProgram.html#bindBlob(int,
 byte[])

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] querying with BLOB in WHERE clause, possible?

[Andreas Schildbach]

Using the Android API, I'd like to do something like SELECT * from
my_table WHERE my_blob=?

My problem is, all of the query methods only take strings as a parameter
for the '?' placeholder. How am I supposed to pass in a byte array?

I already checked for a convert/cast function so I could use WHERE
my_blob=CONVERT_STRING_TO_BLOB(?) but I did not find anything like this.

Yes, my BLOBs are longer than 8 bytes, so I can't use INTEGER.

And I'd like to stick with BLOB because TEXT (with hex encoding) takes
twice the amount of space.

Cheers,

Andreas

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users