Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Batch fetch for type IN results in sql exception (Batch fetch for type IN on relationships with composite foreign keys results in sql exception)
Batch fetch for type IN results in sql exception [message #1015507] Thu, 28 February 2013 18:12 Go to next message
Prasad Mysore is currently offline Prasad Mysore
Messages: 3
Registered: February 2013
Junior Member
DB: Microsoft SQL Server
Eclipselink version : 2.3.2
Springframework version: 3.0.6


I am working on optimizing some JPA object graph queries, and I am trying to evaluate all the three Batch fetch types. Join and Exists seem to work fine. When I tried IN batch fetch type, I found that the IN queries that are generated have sql syntax problems. This is because of our entity join mappings having composite foreign keys.

For eg. the sql that is generated looks like this.

SELECT schemaId, overlayGroup, archiveFromForm, archiveRelated, archiveToFile, archiveToForm, archiveType, associations, enable, hourmask, minute, monthday, numLevels, tagsLong, tagsShort, weekday, queryLong, queryShort FROM schema_archive WHERE ((schemaId, overlayGroup) IN ((?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?)))
bind => [50 parameters bound]


Our JoinMapping for the specific one-one mapping looks like this

@OneToOne(mappedBy = "form", fetch = FetchType.EAGER, targetEntity = ArchiveEntity.class, cascade = CascadeType.ALL,orphanRemoval=true)
@JoinColumns({ @JoinColumn(name = "schemaId", referencedColumnName = "schemaId"),
@JoinColumn(name = "overlayGroup", referencedColumnName = "overlayGroup") })
protected ArchiveEntity archive;


The sql error looks like this

Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: An expression of non-boolean type specified in a context where a condition is expected, near ','.
Error Code: 4
145

Is this the known defect? Is there a workaround that I can use to make it work or has this issue been fixed in the latest 2.4.1 of eclipselink?

Any help is appreciated.

Thanks
P
Re: Batch fetch for type IN results in sql exception [message #1016230 is a reply to message #1015507] Tue, 05 March 2013 10:00 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

IN batch fetching only works for singleton ids, unless the database supports nested array querying (your database does not seem to).


James : Wiki : Book : Blog : Twitter
Re: Batch fetch for type IN results in sql exception [message #1024225 is a reply to message #1016230] Mon, 25 March 2013 21:37 Go to previous messageGo to next message
Prasad Mysore is currently offline Prasad Mysore
Messages: 3
Registered: February 2013
Junior Member
Thanks James. I got pulled into some other task, and had to set this aside for a while.

DB: Microsoft SQL Server
Eclipselink version : 2.3.2
Springframework version: 3.0.6

Yes, it does sound like MS Sql doesnt support nested array querying.

I am wondering if you or anybody else has seen this before, and can help me with this. I am now trying to evaluate between BatchFetchType of Exists and Join. They seem to work in terms of syntax and functionality. However, I noticed that the sql logs were slightly bigger, and there was no improvement in performance. I am using query hints instead of annotations. When i started looking at the logs, I saw something weird. Eclipselink seems to be firing the EXISTS(when i use batch fetch type exists) queries for fetching the relationships in one shot. But shortly after that, it also fires the default queries, that is fetching relationships for one top level object at a time. Its as if the query fired, but it didn't use the results, but instead fired all the individual queries again. It is the same exact behavior with batchfetchtype join.

My assumption is that there are no changes needed to the ORM Entity definition themselves when using query hints. I will see if the eclipselink logs will give me anymore information.

Has anybody seen this behavior before. Am I missing something here?
Re: Batch fetch for type IN results in sql exception [message #1024253 is a reply to message #1024225] Mon, 25 March 2013 22:38 Go to previous message
Prasad Mysore is currently offline Prasad Mysore
Messages: 3
Registered: February 2013
Junior Member
Actually, I found this. http://www.eclipse.org/forums/index.php/m/670987/?srch=batch+fetch#msg_670987

That seems to help. I knew there was some setting that was causing this. Thanks for your help.
Previous Topic:Scrollable ResultSet Exception "End of Stream"
Next Topic:Validate xml against multiple schema
Goto Forum:
  


Current Time: Thu Apr 17 23:32:21 EDT 2014

Powered by FUDForum. Page generated in 0.01696 seconds