Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
MIME-version: 1.0
Content-transfer-encoding: 7BIT
Content-type: text/plain; CHARSET=US-ASCII; format=flowed
Date: Thu, 04 Jun 2009 08:25:34 -0700
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: Inserting into BLOB field
In-reply-to: <4A27E626.1080706@sun.com>
Sender: Richard.Hillegas@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <4A27E76E.2080203@sun.com>
References: <23841004.post@talk.nabble.com> <4A25BB2D.3090700@Sun.COM>
 <23855280.post@talk.nabble.com> <4A27E626.1080706@sun.com>
User-Agent: Thunderbird 2.0.0.21 (Macintosh/20090302)

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