Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » JOIN Syntax Worked in toplink essentials(JOIN syntax different from toplink)
JOIN Syntax Worked in toplink essentials [message #976771] Thu, 08 November 2012 21:03 Go to next message
Mike Phillips is currently offline Mike Phillips
Messages: 5
Registered: November 2012
Junior Member
I am trying to switch to eclipselink but an existing named query fails that
I used to use in toplink essentials and I can't figure out how to construct
the JOIN.

eclipselink outputs "T5"."CLIENT_ID"."T5"."CLIENT_ID" and it should be something like
"T5"."CLIENT_ID"

Is this a bug, or should I just be using different syntax?

If I remove the "JOIN a.clientIdCollection cc" and the "AND ac.clientId=cc.clientId"
the query works but does not produce the results I want.


@NamedQuery(name = "DmeAccount.hasFilePathAccess", query = "SELECT COUNT(a) FROM AssetCore ac, DmeAccount a JOIN a.clientIdCollection cc" +
" WHERE ac.assetDigitalStorage.fileName=:fileName AND a.userId=:userId AND ac.securityLevelId.id<=a.securityLevelId.id AND a.accountStatus <> 'DELETED' AND a.accountLocked=0 AND a.accountDisabled=0 and a.expirePwdOn=0 AND ac.clientId=cc.clientId"),

# Genenerated SQL from stack trace
Error Code: 904
Call: SELECT COUNT(t0.USER_ID) FROM DME_DB_SCHEMA.DMEACCOUNT_DMECLIENT t6, DME_DB_SCHEMA.DMECLIENT t5, DME_DB_SCHEMA.LU_SECURITY_LEVEL t4, DME_DB_SCHEMA.LU_SECURITY_LEVEL t3, DME_DB_SCHEMA.ASSET_CORE t2, DME_DB_SCHEMA.ASSET_DIGITAL_STORAGE t1, DME_DB_SCHEMA.DMEACCOUNT t0 WHERE (((((((((t1.FILE_NAME = ?) AND (t0.USER_ID = ?)) AND (t3.ID <= t4.ID)) AND (t0.ACCOUNT_STATUS <> ?)) AND (t0.IS_LOCKED = ?)) AND (t0.IS_DISABLED = ?)) AND (t0.IS_EXPIRE_PWD_ON = ?)) AND (t2.CLIENT_ID = t5.CLIENT_ID.t5.CLIENT_ID)) AND ((((t1.ASSET_ID = t2.ASSET_ID) AND (t3.ID = t2.SECURITY_LEVEL_ID)) AND (t4.ID = t0.SECURITY_LEVEL_ID)) AND ((t6.USER_ID = t0.USER_ID) AND (t5.CLIENT_ID = t6.CLIENT_ID))))

Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "T5"."CLIENT_ID"."T5"."CLIENT_ID": invalid identifier

[Updated on: Wed, 14 November 2012 00:53]

Report message to a moderator

Re: JOIN Syntax Worked in toplink essentials [message #982985 is a reply to message #976771] Tue, 13 November 2012 15:32 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

What version are you using? Can you try the latest 2.4 release.


James : Wiki : Book : Blog : Twitter
Re: JOIN Syntax Worked in toplink essentials [message #983256 is a reply to message #982985] Tue, 13 November 2012 20:10 Go to previous messageGo to next message
Mike Phillips is currently offline Mike Phillips
Messages: 5
Registered: November 2012
Junior Member
eclipselink 1.1.4
I ran against 2.4.0 but other EJB objects and annotations failed so, I could not successfully test against 2.4.0

[Updated on: Tue, 13 November 2012 20:10]

Report message to a moderator

Re: JOIN Syntax Worked in toplink essentials [message #985980 is a reply to message #976771] Fri, 16 November 2012 22:06 Go to previous messageGo to next message
Mike Phillips is currently offline Mike Phillips
Messages: 5
Registered: November 2012
Junior Member
I was able to test with eclipselink 2.4. It is still broken.

Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.0.v20120608-r11652): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00904: "T1"."CLIENT_ID"."T1"."CLIENT_ID": invalid identifier

Error Code: 904
Call: SELECT COUNT(t0.USER_ID) FROM DME_DB_SCHEMA.DMEACCOUNT_DMECLIENT t6, DME_DB_SCHEMA.LU_SECURITY_LEVEL t5, DME_DB_SCHEMA.LU_SECURITY_LEVEL t4, DME_DB_SCHEMA.ASSET_CORE t3, DME_DB_SCHEMA.ASSET_DIGITAL_STORAGE t2, DME_DB_SCHEMA.DMECLIENT t1, DME_DB_SCHEMA.DMEACCOUNT t0 WHERE (((((((((t2.FILE_NAME = ?) AND (t0.USER_ID = ?)) AND (t4.ID <= t5.ID)) AND (t0.ACCOUNT_STATUS <> ?)) AND (t0.IS_LOCKED = ?)) AND (t0.IS_DISABLED = ?)) AND (t0.IS_EXPIRE_PWD_ON = ?)) AND (t3.CLIENT_ID = t1.CLIENT_ID.t1.CLIENT_ID)) AND ((((t2.ASSET_ID = t3.ASSET_ID) AND (t4.ID = t3.SECURITY_LEVEL_ID)) AND (t5.ID = t0.SECURITY_LEVEL_ID)) AND ((t6.USER_ID = t0.USER_ID) AND (t1.CLIENT_ID = t6.CLIENT_ID))))
bind => [6 parameters bound]
Query: ReportQuery(name="DmeAccount.hasFilePathAccess" referenceClass=AssetCore sql="SELECT COUNT(t0.USER_ID) FROM DME_DB_SCHEMA.DMEACCOUNT_DMECLIENT t6, DME_DB_SCHEMA.LU_SECURITY_LEVEL t5, DME_DB_SCHEMA.LU_SECURITY_LEVEL t4, DME_DB_SCHEMA.ASSET_CORE t3, DME_DB_SCHEMA.ASSET_DIGITAL_STORAGE t2, DME_DB_SCHEMA.DMECLIENT t1, DME_DB_SCHEMA.DMEACCOUNT t0 WHERE (((((((((t2.FILE_NAME = ?) AND (t0.USER_ID = ?)) AND (t4.ID <= t5.ID)) AND (t0.ACCOUNT_STATUS <> ?)) AND (t0.IS_LOCKED = ?)) AND (t0.IS_DISABLED = ?)) AND (t0.IS_EXPIRE_PWD_ON = ?)) AND (t3.CLIENT_ID = t1.CLIENT_ID.t1.CLIENT_ID)) AND ((((t2.ASSET_ID = t3.ASSET_ID) AND (t4.ID = t3.SECURITY_LEVEL_ID)) AND (t5.ID = t0.SECURITY_LEVEL_ID)) AND ((t6.USER_ID = t0.USER_ID) AND (t1.CLIENT_ID = t6.CLIENT_ID))))")
Re: JOIN Syntax Worked in toplink essentials [message #986484 is a reply to message #985980] Tue, 20 November 2012 15:42 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

This could be an issue in your mappings. Please include your mappings for the DMECLIENT table, what field are you using for CLIENT_ID?

There have been some JPQL fixes since 2.4.0, so you may also try the latest build/patch.


James : Wiki : Book : Blog : Twitter
Re: JOIN Syntax Worked in toplink essentials [message #986520 is a reply to message #986484] Tue, 20 November 2012 17:49 Go to previous messageGo to next message
Mike Phillips is currently offline Mike Phillips
Messages: 5
Registered: November 2012
Junior Member
@Entity
@Table(name = "DMEACCOUNT",schema="DME_DB_SCHEMA")
@NamedQueries(
{
...
})
public class DmeAccount implements Serializable, Comparable, DmeEntityInterface<String>

...

@JoinTable(name = "DMEACCOUNT_DMECLIENT", schema="DME_DB_SCHEMA",joinColumns = {
@JoinColumn(name = "USER_ID", referencedColumnName = "USER_ID")
}, inverseJoinColumns = {
@JoinColumn(name = "CLIENT_ID", referencedColumnName = "CLIENT_ID")
})
@ManyToMany
private List<DmeClient> clientIdCollection;

###------------------------------------###

@Entity
@Table(name = "DMECLIENT",schema="DME_DB_SCHEMA")
@SequenceGenerator(name="client_id_sequence",
sequenceName="DME_DB_SCHEMA.CLIENT_ID_SEQ",
allocationSize=1)
@NamedQueries(
{
...
})
public class DmeClient implements Serializable, Comparable, DmeEntityInterface<Long>
{
...

@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE,generator="client_id_sequence")
@Column(name = "CLIENT_ID", nullable = false)
private Long clientId;
Re: JOIN Syntax Worked in toplink essentials [message #987689 is a reply to message #986520] Tue, 27 November 2012 15:50 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Sorry, I did not notice the SQL,

t1.CLIENT_ID.t1.CLIENT_ID

That is very odd, I have never seen that before. It seems like the column's table was somehow set to the full column name. The only thing I can think of is it might have something to do will the schema your setting. You could try removing this.

You did not include the mapping for the table, ASSET_CORE, which is causing the issue in its join. Please provide its table.

If you can create an isolated reproducible test case, please log a bug.


James : Wiki : Book : Blog : Twitter
Re: JOIN Syntax Worked in toplink essentials [message #1020797 is a reply to message #987689] Mon, 18 March 2013 23:20 Go to previous message
Mike Phillips is currently offline Mike Phillips
Messages: 5
Registered: November 2012
Junior Member
Just for closure, I finally found the syntax that I could change to correct the problem
I was using the join alias cc and adding .clientId when I did not have to.
join alias "JOIN a.clientIdCollection cc" offending portion of query "AND ac.clientId=cc.clientId". all I had to do is change to "AND ac.clientId=cc".
So the final fixed query again:
@NamedQuery(name = "DmeAccount.hasFilePathAccess", query = "SELECT COUNT(a) FROM AssetCore ac, DmeAccount a JOIN a.clientIdCollection cc" +
" WHERE ac.assetDigitalStorage.fileName=:fileName AND a.userId=:userId AND ac.securityLevelId.id<=a.securityLevelId.id AND a.accountStatus <> 'DELETED' AND a.accountLocked=0 AND a.accountDisabled=0 and a.expirePwdOn=0 AND ac.clientId=cc")

Thanks for taking a look.
Previous Topic:DoesExistQuery in nosql
Next Topic:IllegalArgument Exception using a Enum as Query Parameter
Goto Forum:
  


Current Time: Wed Sep 17 11:46:17 GMT 2014

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

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