No parameters in the prepared statement calling a SQL SERVER user defined function

Support
#1

Hi,

There’s a call to a Sql Server user defined function (dbo.TeFactProv) on the next JPQL query defined in the screen fragment controller:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<fragment xmlns="http://schemas.haulmont.com/cuba/screen/fragment.xsd">
    <data readOnly="true">
        <collection id="expEmbarcDc"
                    class="com.company.logistica.entity.ExpEmbarcament"
                    view="expEmbarcament-relacio">
            <loader id="expEmbarcDl">
                <query>
                    <![CDATA[select e from logis_ExpEmbarcament e where e.cteCd.conCod.conGenCom='S' and function('dbo.TeFactProv',e.cteID) = 0]]>
                </query>
            </loader>
        </collection>
    </data>
    <layout width="725px" height="540px" maxHeight="540" maxWidth="650" minHeight="540" minWidth="650">
        <table dataContainer="expEmbarcDc" width="800px" height="400">
            <columns>
                <column id="eemCd"/>
                <column id="eemDt"/>
                <column id="eemDtArrMA"/>
                <column id="eemCnte"/>
                <column id="cteCd.cteCd"/>
                <column id="cteCd.cteDt"/>
                <column id="cteID.cteLnRefP"/>
            </columns>
        </table>
    </layout>
</fragment>

But the prepared statement doesn’t contain the parameter, as you can see:

Caused by: org.eclipse.persistence.exceptions.DatabaseException: 
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: An insufficient number of arguments were supplied for the procedure or function dbo.TeFactProv.
Error Code: 313
Call: SELECT t1.ID, t1.EemCd, t1.EemCnte, t1.EemDt, t1.EemDtArrMA, t1.CteCd, t1.CteID, t0.ID, t0."CteCd", t0."CteDt", t0."ConCod", t2.ID, t2.ConGenCom, t3.ID, t3."CteLnRefP" FROM ContracteLin t3, CondicionsCte t2, ExpEmbarcament t1, Contracte t0 WHERE (((t2.ConGenCom = ?) AND (dbo.TeFactProv() = ?)) AND (((t0.ID = t1.CteCd) AND (t2.ID = t0."ConCod")) AND (t3.ID = t1.CteID)))
	bind => [S, 0]
Query: ReadAllQuery(referenceClass=ExpEmbarcament sql="SELECT t1.ID, t1.EemCd, t1.EemCnte, t1.EemDt, t1.EemDtArrMA, t1.CteCd, t1.CteID, t0.ID, t0."CteCd", t0."CteDt", t0."ConCod", t2.ID, t2.ConGenCom, t3.ID, t3."CteLnRefP" FROM ContracteLin t3, CondicionsCte t2, ExpEmbarcament t1, Contracte t0 WHERE (((t2.ConGenCom = ?) AND (dbo.TeFactProv() = ?)) AND (((t0.ID = t1.CteCd) AND (t2.ID = t0."ConCod")) AND (t3.ID = t1.CteID)))")
FetchGroup(){eemCd, eemDt, eemDtArrMA, cteID => {class java.lang.Object=FetchGroup(cteID){cteLnRefP, id}}, eemCnte, id, cteCd => {class java.lang.Object=FetchGroup(cteCd){conCod => {class java.lang.Object=FetchGroup(conCod){conGenCom, id}}, id, cteCd, cteDt}}}
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:342) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:722) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:564) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2093) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:603) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:263) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:275) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.executeCall(ExpressionQueryMechanism.java:2897) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:261) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:332) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:728) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2785) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2738) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:565) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1221) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:914) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1180) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:466) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1268) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:3020) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1892) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1874) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1839) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:262) ~[org.eclipse.persistence.jpa-2.7.3-6-cuba.jar:na]
	... 132 common frames omitted
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: An insufficient number of arguments were supplied for the procedure or function dbo.TeFactProv.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258) ~[mssql-jdbc-6.2.1.jre8.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535) ~[mssql-jdbc-6.2.1.jre8.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:467) ~[mssql-jdbc-6.2.1.jre8.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409) ~[mssql-jdbc-6.2.1.jre8.jar:na]
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151) ~[mssql-jdbc-6.2.1.jre8.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478) ~[mssql-jdbc-6.2.1.jre8.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219) ~[mssql-jdbc-6.2.1.jre8.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199) ~[mssql-jdbc-6.2.1.jre8.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:331) ~[mssql-jdbc-6.2.1.jre8.jar:na]
	at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:107) ~[tomcat-dbcp.jar:8.5.33]
	at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:107) ~[tomcat-dbcp.jar:8.5.33]
	at com.haulmont.cuba.core.sys.jdbc.ProxyPreparedStatement.lambda$executeQuery$0(ProxyPreparedStatement.java:34) ~[cuba-core-7.1.4.jar:7.1.4]
	at com.haulmont.cuba.core.sys.jdbc.ProxyStatement.executeSqlStatement(ProxyStatement.java:257) ~[cuba-core-7.1.4.jar:7.1.4]
	at com.haulmont.cuba.core.sys.jdbc.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:34) ~[cuba-core-7.1.4.jar:7.1.4]
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1059) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:679) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]

Is there any mistake on my part specifying the query or is there a problem when the framework prepare the JDBC statement ?

Regards

#5

Hi, Cuba Platform followers.

Is there anybody who can help me with this problem?. I’m using Cuba Studio v12.1, Cuba Platform v.7.1.4 and Sql Server 2008.

I will be very grateful for any help.

Regards

#9

Hi Xavier,
It seems like a problem with EclipseLink ORM. We’ll try to reproduce the problem and answer you later.

#13

Hi, @xlorentep!

I’ve tried to reproduce the problem using Cuba Platform v.7.1.4 and Sql Server 2008 but it didn’t occur. If possible, could you provide a sample project which reproduces the problem?

Regards,
Sergey

#15

Hi, Sergey

Sorry for the delay in my response because I migrated the project to Cuba Platform 7.2 with the hope that the problem would have been fixed, but it still remains.

Here you are a sample project, with a sql server test database, to reproduce the problem and try to solve it.

Regards,
Xavier

#16

Sorry, the file wasn’t attached due to the large size.
I’ll try to load the compressed file one by one.Steps to reproduce the error.pdf (78.7 KB)

#17

Another one fileCuba platform project.7z (2.1 MB)

#18

The database backup file…Database.7z (197.0 KB)

#19

Hi, Cuba Platform support.
Have you been able to reproduce the problem with the test project and the database backup sended few days ago?

Have you got any idea about the source of the problem ?

There’s an application in production environment that need this capability.

Thank you in advance

#21

Hi, @xlorentep!

After some tests It still seems like a problem with EclipseLink ORM, but I was unable to reproduce it. And I met some problems with your test project. There is mestrelegacy app component which I have no access to and it seems essential to project. Could you please provide another test project without it?

Regards,
Sergey

#22

Hi, Sergey
I’ll try to send you as soon as possible mestrelegacy project, because all the legacy entities are defined in this app component.
Regards,
Xavier

#23

Hi, Sergey
Here it is attached the local maven repository with the mestrelegacy app component for your testing purpose.

Regards,
Xavier

repository.7z (63.0 KB)

#24

Hi, @xlorentep,

Thanks for your help with reproduction of the problem. Seems like it is not a bug but a mistake with query and not so obvious reaction from EclipseLink ORM. In your query e.cteID is passed as a parameter. Function expects bigint parameter type but gets ContracteLin entity instead. I guess ID of that entity should be passed as parameter. So if we change query to

select e from logis_ExpEmbarcament e where e.cteCd.conCod.conGenCom='S' and function('dbo.test',e.cteID.id)=0

it will work fine.

Regards,
Sergey

#25

Hi, Sergey.
You’re completely right !!. Sorry for your time expended, the error message confused me and I had thought the problem was the generation of the SQL expression thrown to the database.

I’m very grateful for your help.

Regards,
Xavier