On 2/13/14 11:55 AM, Derek Lewis wrote:
Hello,
I'm struggling to write upgrade and rollback SQL for a CHAR(16) FOR
BIT DATA column. It's currently 16-bytes long, but I'd like to
migrate it to a BLOB since I'll need to put more data in it soon.
I'm using liquibase for the migration. Upgrading is fine, I add a new
column, and do "update <table> set newColumn = CAST(oldColumn AS BLOB)".
Rolling back is giving me problems though.
I'm trying to do "update <table> set oldColumn = CAST(newColumn AS
CHAR FOR BIT DATA)", but I keep getting the error in the subject.
The documentation I found at
http://db.apache.org/derby/docs/10.10/ref/rrefsqlj33562.html indicates
that casting from CHAR FOR BIT DATA to BLOB should be possible. I'm
using Derby 10.10.1.1, so I believe those are the correct docs.
Any idea what could be wrong? For what it's worth, this test is
taking place on empty tables.
Hi Derek,
I have logged https://issues.apache.org/jira/browse/DERBY-6479 to track
this issue. According to my reading of the SQL Standard, casts between
any two binary types should be allowed. I agree that the Reference Guide
section on the CAST function seems to be confused. A common workaround
for this problem is to write your own cast function. See the sample code
on DERBY-6479.
Hope this helps,
-Rick
Cheers,
Derek
