Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Querying a table with an array column fails
Querying a table with an array column fails [message #644818] Tue, 14 December 2010 10:36 Go to next message
Daniel  is currently offline Daniel
Messages: 2
Registered: December 2010
Junior Member
Hello,

I'm having trouble with querying a table which consists of a serial and a varchar-array column.

The (test) database layout is:
create database arraytest;
create schema arraytest;
create table array_test (id serial primary key, arr varchar(64)[]);
insert into arraytest.array_test (arr) values ('{"some", "value"}');

The entity code is:
@Entity
@Table(name = "array_test", catalog = "arraytest", schema = "arraytest")
@NamedQueries({
    /*Deleted automatically generated queries. */
})
public class ArrayTest implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
@Column(name = "id", nullable = false)
    private Integer id;
    @Column(name = "arr")
    @ElementCollection
    private List<String> arr = new ArrayList<String>();
/* Getter and Setter methods */
}

Unfortunately executing the standard .findAll query fails with the following exception:
Quote:
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: cross-database references are not implemented: "arraytest.arraytest.array_test"
Error Code: 0
Call: SELECT id FROM arraytest.arraytest.array_test
Query: ReadAllQuery(name="ArrayTest.findAll" referenceClass=ArrayTest sql="SELECT id FROM arraytest.arraytest.array_test")
at org.eclipse.persistence.exceptions.DatabaseException.sqlExce ption(DatabaseException.java:333)



Doing the same query in psql works fine.
System: Netbeans 6.9.1, Glassfish 3.0.1, eclipselink 2.0.2., postgresql 9.0

Any ideas?

Thanks in advance!
Daniel
Re: Querying a table with an array column fails [message #645103 is a reply to message #644818] Wed, 15 December 2010 15:25 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

JPA does not support object-relational data-types such as PostgreSQL's array type.

In JPA, and @ElementCollection mapping is used to store a basic collection in another table. You need to have this other table, not an array type.

If you need to use the PostgreSQL array type depending on how PostgreSQL supports this through JDBC, you should be able to use the ArrayMapping in EclipseLink (through a DescriptorCustomizer). We have only tested this with Oralce's object-relational data-type support, but it should work with any database that supports object-relational data-types, however you will probably need to customize your PostgreSQLPlatform.


James : Wiki : Book : Blog : Twitter
Previous Topic:JPA 2, problem with ORDER BY AVG
Next Topic:EclipseLink on SQLServer 2005 behaviour, every statement is recompiled leading to performance issues
Goto Forum:
  


Current Time: Wed Oct 01 22:27:32 GMT 2014

Powered by FUDForum. Page generated in 0.02142 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software