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: neutral (herse.apache.org: local policy)
Message-ID: <45DDDCB6.8050707@amberpoint.com>
Date: Thu, 22 Feb 2007 10:11:02 -0800
From: Bryan Pendleton <bpendleton@amberpoint.com>
User-Agent: Thunderbird 1.5.0.9 (Windows/20061207)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: problem with ALTER COLUMN DEFAULT on VARCHAR column
References: <erkjdr$q0s$1@sea.gmane.org>
In-Reply-To: <erkjdr$q0s$1@sea.gmane.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


> alter table TABLE_NAME alter column COL_NAME DEFAULT 'new value'
> 
> and with some VARCHAR columns I get an error like this:
> 
> Invalid character string format for type long.

Well, I'm not sure what's causing this, but here's what I
think is going on, maybe it makes sense: when you alter the
default for a column which is automatically generated, the
code appears to want to compute the current maximum value for
that column, and internally it generates and executes the statement:

   SELECT MAX(COL_NAME) FROM TABLE_NAME

For some reason, this MAX query did not return a numeric value.

Perhaps the table is empty, and so the MAX query returned a NULL?

Does any of this make sense? Are you altering the default for
an automatically generated VARCHAR column? If so, can you try
running the SELECT MAX query by hand yourself prior to running
the ALTER TABLE statement and see what the SELECT MAX query returns?

thanks,

bryan