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 Sisilla Sookdeo <si...@paramounttransport.com> on 2006/11/10 19:29:03 UTC

comparisons are not supported for long varchar datatype

Hello All,

 

I am trying to create a table with a field of type 'LONG VARCHAR,' but I
keep getting the following error message:

 

An error has occurred during execution: Columns of type "LONG VARCHAR'
may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT,
EXCEPT or DISTINCT statements because comparisons are not supported for
that type.

 

Am I missing something here? I am using Derby 10.2.1.6 on Windows XP
Professional. I tried executing the following SQL code in NetBeans IDE:

 

CREATE TABLE Personnel

(

            PTTEmployeeID LONG VARCHAR NOT NULL,

            NationalID VARCHAR(55) NOT NULL,

            FirstName VARCHAR(55) NOT NULL,

            Surname VARCHAR(55) NOT NULL,

            PRIMARY KEY (PTTEmployeeID),

            UNIQUE (NationalID)

)

 

I appreciate any help.

 

Thanks,

Sisilla

Re: comparisons are not supported for long varchar datatype

Posted by Daniel John Debrunner <dj...@apache.org>.
Craig L Russell wrote:
> Hi Sisilla,
> 
> LONG VARCHAR is a type typically used for BLOBs: images, large 
> documents, graphics, and the like. So it's inappropriate for a primary 
> key or unique or indexed column.

LONG VARCHAR is a character type (not binary) and Derby treats it as a 
large object character string like CLOB. Section 4.2.3.3 of the SQL 
standard 2003 (foundation) basically states that such types are not 
comparable and cannot be used in <unique constraint definition> which 
includes primary keys.

> Are you sure that EmployeeID is a large object? Or perhaps it should be 
> an integer or other integral type.

I think that's the key question, why is LONG VARCHAR being used here?

Thanks,
Dan.

Re: comparisons are not supported for long varchar datatype

Posted by Craig L Russell <Cr...@Sun.COM>.
Hi Sisilla,

LONG VARCHAR is a type typically used for BLOBs: images, large  
documents, graphics, and the like. So it's inappropriate for a  
primary key or unique or indexed column.

Are you sure that EmployeeID is a large object? Or perhaps it should  
be an integer or other integral type.

Craig

On Nov 10, 2006, at 10:29 AM, Sisilla Sookdeo wrote:

> Hello All,
>
>
>
> I am trying to create a table with a field of type ‘LONG VARCHAR,’  
> but I keep getting the following error message:
>
>
>
> An error has occurred during execution: Columns of type “LONG  
> VARCHAR’ may not be used in CREATE INDEX, ORDER BY, GROUP BY,  
> UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons  
> are not supported for that type.
>
>
>
> Am I missing something here? I am using Derby 10.2.1.6 on Windows  
> XP Professional. I tried executing the following SQL code in  
> NetBeans IDE:
>
>
>
> CREATE TABLE Personnel
>
> (
>
>             PTTEmployeeID LONG VARCHAR NOT NULL,
>
>             NationalID VARCHAR(55) NOT NULL,
>
>             FirstName VARCHAR(55) NOT NULL,
>
>             Surname VARCHAR(55) NOT NULL,
>
>             PRIMARY KEY (PTTEmployeeID),
>
>             UNIQUE (NationalID)
>
> )
>
>
>
> I appreciate any help.
>
>
>
> Thanks,
>
> Sisilla
>
>
>
>

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!

Re: comparisons are not supported for long varchar datatype

Posted by Michael Segel <ms...@mycingular.blackberry.net>.
It sounds like a java error. Does the LONGVARCHAR overload the comparesTo() method?
Sent via BlackBerry.

-Mike Segel
Principal
MSCC
312 952 8175


-----Original Message-----
From: "Sisilla Sookdeo" <si...@paramounttransport.com>
Date: Fri, 10 Nov 2006 14:29:03 
To:<de...@db.apache.org>
Subject: comparisons are not supported for long varchar datatype

Hello All,
 
 
 
I am trying to create a table with a field of type ‘LONG VARCHAR,’ but I keep getting the following error message:
 
 
 
An error has occurred during execution: Columns of type “LONG VARCHAR’ may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
 
 
 
Am I missing something here? I am using Derby 10.2.1.6 on Windows XP Professional. I tried executing the following SQL code in NetBeans IDE:
 
 
 
CREATE TABLE Personnel
 
(
 
            PTTEmployeeID LONG VARCHAR NOT NULL,
 
            NationalID VARCHAR(55) NOT NULL,
 
            FirstName VARCHAR(55) NOT NULL,
 
            Surname VARCHAR(55) NOT NULL,
 
            PRIMARY KEY (PTTEmployeeID),
 
            UNIQUE (NationalID)
 
)
 
 
 
I appreciate any help.
 
 
 
Thanks,
 
Sisilla