PSQLException: ERROR: syntax error at or near ")"

Support
#1

We are facing the issue while accessing the pillar item editor screen through the cuba application.

Please find below a screenshot. In the below screen shot, we were able to open the Process pillar

and see the data, but when trying to double-click on it, it was not able to open following an error…

Capture
Capture813×311 19.2 KB

Detailed Error Log:

com.haulmont.cuba.core.global.RemoteException:

javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.3.18-cuba): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: syntax error at or near “)”
Position: 314
Error Code: 0
Call: SELECT ID, ASSOCIATED_WITH, AUTHOR, CREATE_TS, CREATED_BY, DELETE_TS, DELETED_BY, DOC_DATE, DOC_NO, DOCUMENT_ID, IS_RECORD, NAME, DOC_TYPE, UPDATE_TS, UPDATED_BY, VERSION, ASSET_ID, ASSOCIATED_ENTITY_ID, ENTITY_ID, LOCATION_ID, PERSON_ID, POSITION_ID, TASK_INSTANCE_ID, FILE_ID FROM ALIGN_DOCUMENT WHERE ((ID IN ()) AND (0=0))
Query: ReadAllQuery(name=“associatedDoc” referenceClass=Document sql=“SELECT ID, ASSOCIATED_WITH, AUTHOR, CREATE_TS, CREATED_BY, DELETE_TS, DELETED_BY, DOC_DATE, DOC_NO, DOCUMENT_ID, IS_RECORD, NAME, DOC_TYPE, UPDATE_TS, UPDATED_BY, VERSION, ASSET_ID, ASSOCIATED_ENTITY_ID, ENTITY_ID, LOCATION_ID, PERSON_ID, POSITION_ID, TASK_INSTANCE_ID, FILE_ID FROM ALIGN_DOCUMENT WHERE ((ID IN ?) AND (0=0))”)

org.eclipse.persistence.exceptions.DatabaseException:
Internal Exception: org.postgresql.util.PSQLException: ERROR: syntax error at or near “)”
Position: 314
Error Code: 0
Call: SELECT ID, ASSOCIATED_WITH, AUTHOR, CREATE_TS, CREATED_BY, DELETE_TS, DELETED_BY, DOC_DATE, DOC_NO, DOCUMENT_ID, IS_RECORD, NAME, DOC_TYPE, UPDATE_TS, UPDATED_BY, VERSION, ASSET_ID, ASSOCIATED_ENTITY_ID, ENTITY_ID, LOCATION_ID, PERSON_ID, POSITION_ID, TASK_INSTANCE_ID, FILE_ID FROM ALIGN_DOCUMENT WHERE ((ID IN ()) AND (0=0))
Query: ReadAllQuery(name=“associatedDoc” referenceClass=Document sql=“SELECT ID, ASSOCIATED_WITH, AUTHOR, CREATE_TS, CREATED_BY, DELETE_TS, DELETED_BY, DOC_DATE, DOC_NO, DOCUMENT_ID, IS_RECORD, NAME, DOC_TYPE, UPDATE_TS, UPDATED_BY, VERSION, ASSET_ID, ASSOCIATED_ENTITY_ID, ENTITY_ID, LOCATION_ID, PERSON_ID, POSITION_ID, TASK_INSTANCE_ID, FILE_ID FROM ALIGN_DOCUMENT WHERE ((ID IN ?) AND (0=0))”)

It throws the error message exactly as the question title. When I take the text of the base Query and the String SQL and enter them in my PostgreSQL app exactly (replacing the question marks with actual values), it retrieves the table perfectly. Where is the syntax error here?

We tried to upgrade the driver version as well, but still facing the issue

#2

The error is probably in the parameters list - it’s empty, see ... WHERE ((ID IN ()) AND (0=0)) in the first query.

We need more information to give you any advice. Platform version, source code of the entities, what operation is performed, full exception stacktrace and previous log output.

Regards,
Konstantin

#3

Hi @krivopustov , Thanks for the reply ,

Cuba Application Version: 7.2.16
PostgreSQL driver version:42.7.3 (tried with 42.2.9 as well)

we are not using that exact query anywhere in our application,
we have Process entity and Document is the child entity for that, process screen working fine ,but if we open process-editor screen we are facing the error.

we have process-edit.xml in that we are using following type


please find attached log files as well
postgresql.log.2024-05-02-14.txt (6.6 KB)
Cuba-UI ErrorLog.txt (9.8 KB)
process-edit.xml.txt (17.0 KB)

#4

Try to set fetch mode to UNDEFINED for the documents property in the process-all-attributes view. Something like this:

<view class="...Process"
      name="process-all-attributes">
    ...
    <property name="documents" view="..." fetch="UNDEFINED"/>
</view>

If this view is used in multiple places, better copy it to ensure the changes will affect only this screen.

With fetch="UNDEFINED", EclispeLink will not try to optimize the loading of the graph and will just load the collection with a separate select by Process id.

#5

We tried to implement the same as in view fetch as UNDEFINED but still getting error