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 23:12 |
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: 4145
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 #1024225 is a reply to message #1016230] |
Tue, 26 March 2013 01:37 |
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?
|
|
| |
Goto Forum:
Current Time: Wed Sep 25 11:49:41 GMT 2024
Powered by FUDForum. Page generated in 0.05421 seconds
|