Return-Path: 
 <derby-user-return-15464-apmail-db-derby-user-archive=db.apache.org@db.apache.org>
X-Original-To: apmail-db-derby-user-archive@www.apache.org
Delivered-To: apmail-db-derby-user-archive@www.apache.org
Received: from mail.apache.org (hermes.apache.org [140.211.11.3])
	by minotaur.apache.org (Postfix) with SMTP id 76186108DF
	for <apmail-db-derby-user-archive@www.apache.org>;
 Fri, 14 Feb 2014 14:54:41 +0000 (UTC)
Received: (qmail 43376 invoked by uid 500); 14 Feb 2014 14:54:26 -0000
Delivered-To: apmail-db-derby-user-archive@db.apache.org
Received: (qmail 43252 invoked by uid 500); 14 Feb 2014 14:54:20 -0000
Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
list-help: <mailto:derby-user-help@db.apache.org>
list-unsubscribe: <mailto:derby-user-unsubscribe@db.apache.org>
List-Post: <mailto:derby-user@db.apache.org>
List-Id: <derby-user.db.apache.org>
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Delivered-To: mailing list derby-user@db.apache.org
Received: (qmail 42932 invoked by uid 99); 14 Feb 2014 14:54:06 -0000
Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 14 Feb 2014 14:54:05 +0000
X-ASF-Spam-Status: No, hits=-2.3 required=5.0
	tests=RCVD_IN_DNSWL_MED,SPF_PASS,UNPARSEABLE_RELAY
X-Spam-Check-By: apache.org
Received-SPF: pass (nike.apache.org: domain of rick.hillegas@oracle.com
 designates 141.146.126.69 as permitted sender)
Received: from [141.146.126.69] (HELO aserp1040.oracle.com) (141.146.126.69)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 14 Feb 2014 14:53:56 +0000
Received: from ucsinet21.oracle.com (ucsinet21.oracle.com [156.151.31.93])
	by aserp1040.oracle.com (Sentrion-MTA-4.3.2/Sentrion-MTA-4.3.2) with ESMTP id
 s1EErYie019658
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK)
	for <derby-user@db.apache.org>; Fri, 14 Feb 2014 14:53:35 GMT
Received: from aserz7021.oracle.com (aserz7021.oracle.com [141.146.126.230])
	by ucsinet21.oracle.com (8.14.4+Sun/8.14.4) with ESMTP id s1EErXEt017183
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO)
	for <derby-user@db.apache.org>; Fri, 14 Feb 2014 14:53:34 GMT
Received: from abhmp0001.oracle.com (abhmp0001.oracle.com [141.146.116.7])
	by aserz7021.oracle.com (8.14.4+Sun/8.14.4) with ESMTP id s1EErXiZ029671
	for <derby-user@db.apache.org>; Fri, 14 Feb 2014 14:53:33 GMT
Received: from dhcp-whq-twvpn-3-vpnpool-10-159-228-13.vpn.oracle.com
 (/10.159.228.13)
	by default (Oracle Beehive Gateway v4.0)
	with ESMTP ; Fri, 14 Feb 2014 06:53:33 -0800
Message-ID: <52FE2DEE.8010908@oracle.com>
Date: Fri, 14 Feb 2014 06:53:34 -0800
From: Rick Hillegas <rick.hillegas@oracle.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.7; en-US;
 rv:1.9.2.18) Gecko/20110616 Thunderbird/3.1.11
MIME-Version: 1.0
To: derby-user@db.apache.org
Subject: Re: ERROR 42846: Cannot convert types 'BLOB' to 'CHAR () FOR BIT
 DATA'.
References: 
 <CAGz9in6K9z0zASWw77FOXc57WsNFESdcyK332MO3pNcLhLA_bA@mail.gmail.com>
In-Reply-To: 
 <CAGz9in6K9z0zASWw77FOXc57WsNFESdcyK332MO3pNcLhLA_bA@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Source-IP: ucsinet21.oracle.com [156.151.31.93]
X-Virus-Checked: Checked by ClamAV on apache.org

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