db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Inserting into BLOB field
Date Thu, 04 Jun 2009 15:20:06 GMT
Often these coercion problems can be finessed by writing your own cast 
function. You might try the following:

1) Write a public static method which takes a String argument and 
returns a java.sql.Blob value.

2) Register that method as a Derby function which takes a VARCHAR 
argument and returns a BLOB.

3) Then use the function to coerce string values to Blobs in your insert 

Here's some code which shows how to do this. First the static method:

import java.sql.Blob;
import org.apache.derbyTesting.functionTests.tests.lang.StringColumnVTI;

public class z
    public static Blob makeBlob( String seed )
        return new StringColumnVTI.SimpleBlob( seed.getBytes() );

Then a little ij script which exercises the function:

connect 'jdbc:derby:memory:dummy;create=true';

create function makeBlob( seed varchar( 32672 ) )
returns blob
language java
parameter style java
no sql
external name 'z.makeBlob';

create table t( a blob );

insert into t( a ) values ( makeBlob( 'xyz' ) );

select * from t;

Hope this helps,

cmathrusse wrote:
> I had read that post prior and it would require me converting the text that I
> am attempting to insert, an xslt file, into a hex representation. Only then
> could I attempt to do this. I did actually attempt this. (not very friendly)
> I did convert the contents of my file into a hex representation and
> attempted the insert by performing a CAST( X'text content' AS BLOB), but it
> complained when I executed the SQL. While this would be a viable approach, I
> think you would agree that this is far from desirable. 
> What would be optimal would be the ability to have Derby allow inserts of
> TEXT into BLOB fields. I know that this is how Sybase ASE operates, (Yes, I
> know Derby is not ASE) and I don't know if this is ANSI standard, but it
> sure makes life less complicated.

View raw message