Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of rick.hillegas@oracle.com
 designates 141.146.126.69 as permitted sender)
Message-ID: <524AFEAF.6060105@oracle.com>
Date: Tue, 01 Oct 2013 09:56:15 -0700
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: Serious issue with 10.10.1.1 and tomcat-jdbc
References: <348193E9-7912-4706-9925-31EF7706C6DD@HoudiniESQ.com>
 <38EE3654-A4BD-4A37-87D4-B739C973F916@HoudiniESQ.com>
In-Reply-To: <38EE3654-A4BD-4A37-87D4-B739C973F916@HoudiniESQ.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit

On 10/1/13 9:02 AM, Frank Rivera wrote:
> ALTER TABLE esq.licreq ADD COLUMN u_domain GENERATED ALWAYS AS 
> (UPPER(domain))
Hi Frank,

I have been able to reproduce this problem when the connected user (ROOT 
in this case) has not created any schema objects of his own. I have 
logged https://issues.apache.org/jira/browse/DERBY-6361 to track this 
problem.

Derby only implicitly creates a schema for the default APP user. Derby 
is supposed to implicitly create a schema for the connected user when 
the user performs certain operations. But this ALTER TABLE statement 
isn't one of those operations. The workaround would be to make sure that 
a schema has been created for the connected user. This can be done 
explicitly like this...

CREATE SCHEMA root;

Here's a script which shows the problem:

connect 'jdbc:derby:memory:db;create=true;user=esq';

create table licreq( domain varchar( 10 ) );

connect 'jdbc:derby:memory:db;user=root';

-- fails
ALTER TABLE esq.licreq ADD COLUMN u_domain GENERATED ALWAYS AS 
(UPPER(domain));

create schema root;

-- succeeds
ALTER TABLE esq.licreq ADD COLUMN u_domain GENERATED ALWAYS AS 
(UPPER(domain));

Hope this helps,
-Rick