db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: ERROR 42846: Cannot convert types 'BLOB' to 'CHAR () FOR BIT DATA'.
Date Fri, 14 Feb 2014 14:53:34 GMT
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


Mime
View raw message