You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Derek Lewis <de...@lewisd.com> on 2014/02/13 20:55:37 UTC
ERROR 42846: Cannot convert types 'BLOB' to 'CHAR () FOR BIT DATA'.
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.
Cheers,
Derek
Re: ERROR 42846: Cannot convert types 'BLOB' to 'CHAR () FOR BIT DATA'.
Posted by Derek Lewis <de...@lewisd.com>.
Thanks very much Rick. I've added myself as a watcher on that issue.
On Fri, Feb 14, 2014 at 6:53 AM, Rick Hillegas <ri...@oracle.com>wrote:
> 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
>>
>
>
Re: ERROR 42846: Cannot convert types 'BLOB' to 'CHAR () FOR BIT
DATA'.
Posted by Rick Hillegas <ri...@oracle.com>.
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