Inserting into BLOB field

[cmathrusse]

I know this has been asked before...

I need to perform an insert into a BLOB field. Previously my field was defined as a CLOB and there was no issue. But due to a new requirement I need to change this to a BLOB. The issue that I have now is that I cannot insert any seed data into this field with a SQL script.

Is there any way that I would be able to perform these inserts using a SQL script?

Thanks...

[Kristian Waagan-4]

cmathrusse wrote:
> I know this has been asked before...
>
> I need to perform an insert into a BLOB field. Previously my field was
> defined as a CLOB and there was no issue. But due to a new requirement I
> need to change this to a BLOB. The issue that I have now is that I cannot
> insert any seed data into this field with a SQL script.
>  

Hi,

Does the following thread help?
http://www.nabble.com/VALUES-expression%2C-BLOB%2C-and-using-ij-tp10778754p10778754.html


Regards,
--
Kristian
> Is there any way that I would be able to perform these inserts using a SQL
> script?
>
> Thanks...
>  

[cmathrusse]

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.

[Rick Hillegas-2]

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
statements.

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,
-Rick

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.
>  

[Rick Hillegas-2]

I forgot to mention that you this will only work on 10.5, which includes
the fix for DERBY-2201.

Regards,
-Rick

Rick Hillegas wrote:

> 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 statements.
>
> 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,
> -Rick
>
> 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.
>>  
>