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: Copy varchars from table A to Blobs in Table b?
Date Mon, 14 Jan 2008 17:56:17 GMT
Hi Jim,

Derby, complying with the ANSI SQL standard, does not allow you to cast 
non-BLOB to BLOB. The ANSI rules are documented in part 2 of the 2003 
SQL standard, section 6.12. The Derby behavior is documented in the 
Derby Reference Guide section titled "Data type assignments and 
comparison, sorting, and ordering": 

As Kristian points out, you can cast string types to CLOB. If you really 
need the target column to be typed as BLOB, then you can write your own 
conversion function. For instance, you would write a Java method with 
this signature:

public static java.sql.Blob convertToBlob( String arg ) { ... }

and you would register this function using the CREATE FUNCTION 
statement. Then you would issue a statement something like this:

insert into varcharTable( varcharCol )
  select convertToBlob( blobCol ) from blobTable

Hope this helps,

Jim Murphy wrote:
> I can't seem to convert/cast from plain old varchars to blobs.  
> I have a table of biggish varshars that in a new DB schema are now modeled
> as blobs.  In writing the conversion script I can't figure a way to select
> varchars from one tabel and insert them into the other.
> Any ideas?
> Thanks
> Jim

View raw message