[derby] searching within a blob

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

[derby] searching within a blob

Alex O'Ree
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?
Reply | Threaded
Open this post in threaded view
|

Re: [derby] searching within a blob

Peter Ondruška-4

On Mon, 30 Sep 2019 at 18:18, Alex O'Ree <[hidden email]> 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?
Reply | Threaded
Open this post in threaded view
|

Re: [derby] searching within a blob

Rick Hillegas-3
In reply to this post by Alex O'Ree
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

Reply | Threaded
Open this post in threaded view
|

Re: [derby] searching within a blob

Alex O'Ree
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 <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: [derby] searching within a blob

Rick Hillegas-3
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 <[hidden email]>
> 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
>>