You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Alex O'Ree <al...@apache.org> on 2019/10/01 01:18:08 UTC
Re: [derby] searching within a blob
yup, definitely a blob. it looks like the like operator doesnt work for
blobs, or maybe i need a cast or some function to the conversion
On Mon, Sep 30, 2019 at 6:50 PM Rick Hillegas <ri...@gmail.com>
wrote:
> On 9/30/19 9:18 AM, Alex O'Ree wrote:
>
> I have a use case where i have string data stored in a blob and i want to
> perform a query similar to
>
> select * from table where column1 like '%hello world%'
>
> It doesn't look like this is possible with derby out of the box. Is there a
> way to create a function that calls a java function or something that can
> be used to make this work?
>
>
> Just to clarify, the column has type BLOB rather than CLOB? The following
> script works for me on CLOB types:
>
> connect 'jdbc:derby:memory:db;create=true';
> CREATE TABLE t (a CLOB);
> INSERT INTO t VALUES ('this will not match'), ('but shello worldly will');
> SELECT * FROM t WHERE a LIKE '%hello world%';
>
> Thanks,
>
> -Rick
>
Re: [derby] searching within a blob
Posted by Rick Hillegas <ri...@gmail.com>.
If the string really has been serialized as a byte array into a BLOB
column, then I'm not aware of an off-the-shelf solution. I would tackle
this problem by writing a function which does the following:
o Retrieves the java.sql.Blob via PreparedStatement.getBlob()
o Retrieves an InputStream from the BLOB via Blob.getBinaryStream()
o Decodes the InputStream's bytes into characters using a
java.nio.charset.CharsetDecoder obtained from a java.nio.charset.Charset
o Examines the decoded character stream for the pattern you want.
Hope this helps,
-Rick
On 9/30/19 6:18 PM, Alex O'Ree wrote:
> yup, definitely a blob. it looks like the like operator doesnt work for
> blobs, or maybe i need a cast or some function to the conversion
>
> On Mon, Sep 30, 2019 at 6:50 PM Rick Hillegas <ri...@gmail.com>
> wrote:
>
>> On 9/30/19 9:18 AM, Alex O'Ree wrote:
>>
>> I have a use case where i have string data stored in a blob and i want to
>> perform a query similar to
>>
>> select * from table where column1 like '%hello world%'
>>
>> It doesn't look like this is possible with derby out of the box. Is there a
>> way to create a function that calls a java function or something that can
>> be used to make this work?
>>
>>
>> Just to clarify, the column has type BLOB rather than CLOB? The following
>> script works for me on CLOB types:
>>
>> connect 'jdbc:derby:memory:db;create=true';
>> CREATE TABLE t (a CLOB);
>> INSERT INTO t VALUES ('this will not match'), ('but shello worldly will');
>> SELECT * FROM t WHERE a LIKE '%hello world%';
>>
>> Thanks,
>>
>> -Rick
>>