Hint: No function matches the given name and argument types. You might need to add explicit type casts.

11,548 views

Skip to first unread message

embree...@gmail.com

unread,

May 3, 2021, 7:07:23 PM5/3/21

to jOOQ User Group

Hi I have tried multiple times to get the results of a postgres function returned and been unsuccessful. I have tried .... select() and selectFrom(). Changed the function to return query/table, return an explicit table, return just a single UUID.

Right now I have this function ....

CREATE OR REPLACE FUNCTION khoj.get_transaction(
   v_user_name character varying,
   v_password character varying,
   v_name_space character varying,
   v_reason_for_edit character varying)
    RETURNS TABLE (uuid UUID, date_of_creation timestamp, date_of_expiration timestamp, user_name character varying(80), break_point bigint)
   --RETURNS khoj.SYS_TRANSACTION
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE SECURITY DEFINER
AS $BODY$
DECLARE
V_UUID UUID;
V_SUCCESSFUL boolean;
V_ERROR_MESSAGE character varying;
I INTEGER;
V_REASON_REQUIRED BOOLEAN;
R_NAME_SPACE khoj.SYS_NAME_SPACE%ROWTYPE;
R_USER_ACCOUNT khoj.A_USER_ACCOUNT%ROWTYPE;
R_SYSTEM_VARIABLE khoj.SYS_SYSTEM_VARIABLE%ROWTYPE;
R_SYS_TRANSACTION khoj.SYS_TRANSACTION%ROWTYPE;

BEGIN

...

RETURN QUERY SELECT * FROM khoj.SYS_TRANSACTION WHERE UUID = V_UUID;
END;
$BODY$;

JOOQ query is ...

GetTransactionRecordRecord GetTransactionRecord = create.selectFrom(getTransactionRecord(inline(session.user().userName()), inline(password), inline(session.branch().name()), inline(reason_for_edit))).fetchOne();

I get the following Exception Stack ....

17:53:41,162 ERROR [org.dyslexicdesign.limsserver.ejb.util.LimsTransactionManager] (ServerService Thread Pool -- 63) Failed to create LIMS Transaction: org.jooq.exception.DataAccessException: SQL [select "get_transaction_record"."uuid", "get_transaction_record"."date_of_creation", "get_transaction_record"."date_of_expiration", "get_transaction_record"."user_name", "get_transaction_record"."break_point" from "khoj"."get_transaction_record"('SYSTEM', 'XXX', 'Trunk', '')]; ERROR: function khoj.get_transaction_record(unknown, unknown, unknown, unknown) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 215
   at org.jooq_3.14.8.POSTGRES.debug(Unknown Source)
   at org.jooq.impl.Tools.translate(Tools.java:2903)
   at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:757)
   at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:389)
   at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:333)
   at org.jooq.impl.AbstractResultQuery.fetchInto(AbstractResultQuery.java:1550)
   at org.jooq.impl.SelectImpl.fetchInto(SelectImpl.java:3936)
   at org.dyslexicdesign.limsserver.ejb.util.LimsTransactionManager.getTransaction(LimsTransactionManager.java:59)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.base/java.lang.reflect.Method.invoke(Method.java:566)
   at org.jboss.as.ee.component.ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptor.java:52)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:437)
   at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.doMethodInterception(Jsr299BindingsInterceptor.java:82)
   at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.processInvocation(Jsr299BindingsInterceptor.java:93)
   at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:63)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.invocationmetrics.ExecutionTimeInterceptor.processInvocation(ExecutionTimeInterceptor.java:43)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:47)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:437)
   at org.jboss.weld.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke(AbstractEJBRequestScopeActivationInterceptor.java:64)
   at org.jboss.as.weld.ejb.EjbRequestScopeActivationInterceptor.processInvocation(EjbRequestScopeActivationInterceptor.java:83)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ee.concurrent.ConcurrentContextInterceptor.processInvocation(ConcurrentContextInterceptor.java:45)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:21)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)
   at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:52)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.pool.PooledInstanceInterceptor.processInvocation(PooledInstanceInterceptor.java:51)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInCallerTx(CMTTxInterceptor.java:254)
   at org.jboss.as.ejb3.tx.CMTTxInterceptor.required(CMTTxInterceptor.java:329)
   at org.jboss.as.ejb3.tx.CMTTxInterceptor.processInvocation(CMTTxInterceptor.java:239)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.invocationmetrics.WaitTimeInterceptor.processInvocation(WaitTimeInterceptor.java:47)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.security.SecurityContextInterceptor.processInvocation(SecurityContextInterceptor.java:100)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.deployment.processors.StartupAwaitInterceptor.processInvocation(StartupAwaitInterceptor.java:22)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.interceptors.ShutDownInterceptorFactory$1.processInvocation(ShutDownInterceptorFactory.java:64)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.interceptors.LoggingInterceptor.processInvocation(LoggingInterceptor.java:67)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ee.component.NamespaceContextInterceptor.processInvocation(NamespaceContextInterceptor.java:50)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.interceptors.AdditionalSetupInterceptor.processInvocation(AdditionalSetupInterceptor.java:54)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.ContextClassLoaderInterceptor.processInvocation(ContextClassLoaderInterceptor.java:64)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.InterceptorContext.run(InterceptorContext.java:356)
   at org.wildfly.security.manager.WildFlySecurityManager.doChecked(WildFlySecurityManager.java:636)
   at org.jboss.invocation.AccessCheckingInterceptor.processInvocation(AccessCheckingInterceptor.java:61)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.InterceptorContext.run(InterceptorContext.java:356)
   at org.jboss.invocation.PrivilegedWithCombinerInterceptor.processInvocation(PrivilegedWithCombinerInterceptor.java:80)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)
   at org.jboss.as.ee.component.ViewService$View.invoke(ViewService.java:198)
   at org.jboss.as.ee.component.ViewDescription$1.processInvocation(ViewDescription.java:185)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)
   at org.jboss.as.ee.component.ProxyInvocationHandler.invoke(ProxyInvocationHandler.java:73)
   at org.dyslexicdesign.limsserver.ejb.util.LimsTransactionManager$$$view10.getTransaction(Unknown Source)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.base/java.lang.reflect.Method.invoke(Method.java:566)
   at org.jboss.weld.util.reflection.Reflections.invokeAndUnwrap(Reflections.java:433)
   at org.jboss.weld.bean.proxy.EnterpriseBeanProxyMethodHandler.invoke(EnterpriseBeanProxyMethodHandler.java:128)
   at org.jboss.weld.bean.proxy.EnterpriseTargetBeanInstance.invoke(EnterpriseTargetBeanInstance.java:56)
   at org.jboss.weld.bean.proxy.InjectionPointPropagatingEnterpriseTargetBeanInstance.invoke(InjectionPointPropagatingEnterpriseTargetBeanInstance.java:67)
   at org.jboss.weld.bean.proxy.ProxyMethodHandler.invoke(ProxyMethodHandler.java:100)
   at org.dyslexicdesign.limsserver.ejb.util.LimsTransactionManager$Proxy$_$$_Weld$EnterpriseProxy$.getTransaction(Unknown Source)
   at org.dyslexicdesign.limsserver.ejb.SessionManager.pushTransaction(SessionManager.java:227)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.base/java.lang.reflect.Method.invoke(Method.java:566)
   at org.jboss.as.ee.component.ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptor.java:52)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:437)
   at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.doMethodInterception(Jsr299BindingsInterceptor.java:82)
   at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.processInvocation(Jsr299BindingsInterceptor.java:93)
   at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:63)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.invocationmetrics.ExecutionTimeInterceptor.processInvocation(ExecutionTimeInterceptor.java:43)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:47)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.jpa.interceptor.SFSBInvocationInterceptor.processInvocation(SFSBInvocationInterceptor.java:57)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.stateful.StatefulSessionSynchronizationInterceptor.processInvocation(StatefulSessionSynchronizationInterceptor.java:135)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:437)
   at org.jboss.weld.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke(AbstractEJBRequestScopeActivationInterceptor.java:64)
   at org.jboss.as.weld.ejb.EjbRequestScopeActivationInterceptor.processInvocation(EjbRequestScopeActivationInterceptor.java:83)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ee.concurrent.ConcurrentContextInterceptor.processInvocation(ConcurrentContextInterceptor.java:45)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:21)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)
   at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:52)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.stateful.StatefulComponentInstanceInterceptor.processInvocation(StatefulComponentInstanceInterceptor.java:59)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInCallerTx(CMTTxInterceptor.java:254)
   at org.jboss.as.ejb3.tx.CMTTxInterceptor.required(CMTTxInterceptor.java:329)
   at org.jboss.as.ejb3.tx.CMTTxInterceptor.processInvocation(CMTTxInterceptor.java:239)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.invocationmetrics.WaitTimeInterceptor.processInvocation(WaitTimeInterceptor.java:47)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.security.SecurityContextInterceptor.processInvocation(SecurityContextInterceptor.java:100)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.deployment.processors.StartupAwaitInterceptor.processInvocation(StartupAwaitInterceptor.java:22)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.interceptors.ShutDownInterceptorFactory$1.processInvocation(ShutDownInterceptorFactory.java:64)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.interceptors.LoggingInterceptor.processInvocation(LoggingInterceptor.java:67)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ee.component.NamespaceContextInterceptor.processInvocation(NamespaceContextInterceptor.java:50)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.interceptors.AdditionalSetupInterceptor.processInvocation(AdditionalSetupInterceptor.java:54)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.ContextClassLoaderInterceptor.processInvocation(ContextClassLoaderInterceptor.java:64)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.InterceptorContext.run(InterceptorContext.java:356)
   at org.wildfly.security.manager.WildFlySecurityManager.doChecked(WildFlySecurityManager.java:636)
   at org.jboss.invocation.AccessCheckingInterceptor.processInvocation(AccessCheckingInterceptor.java:61)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.InterceptorContext.run(InterceptorContext.java:356)
   at org.jboss.invocation.PrivilegedWithCombinerInterceptor.processInvocation(PrivilegedWithCombinerInterceptor.java:80)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)
   at org.jboss.as.ee.component.ViewService$View.invoke(ViewService.java:198)
   at org.jboss.as.ee.component.ViewDescription$1.processInvocation(ViewDescription.java:185)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)
   at org.jboss.as.ee.component.ProxyInvocationHandler.invoke(ProxyInvocationHandler.java:73)
   at org.dyslexicdesign.limsserver.ejb.SessionManager$$$view6.pushTransaction(Unknown Source)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.base/java.lang.reflect.Method.invoke(Method.java:566)
   at org.jboss.weld.util.reflection.Reflections.invokeAndUnwrap(Reflections.java:433)
   at org.jboss.weld.bean.proxy.EnterpriseBeanProxyMethodHandler.invoke(EnterpriseBeanProxyMethodHandler.java:128)
   at org.jboss.weld.bean.proxy.EnterpriseTargetBeanInstance.invoke(EnterpriseTargetBeanInstance.java:56)
   at org.jboss.weld.bean.proxy.ProxyMethodHandler.invoke(ProxyMethodHandler.java:100)
   at org.dyslexicdesign.limsserver.ejb.SessionManager$Proxy$_$$_Weld$EnterpriseProxy$.pushTransaction(Unknown Source)
   at org.dyslexicdesign.limsserver.ejb.SessionManager$Proxy$_$$_WeldClientProxy.pushTransaction(Unknown Source)
   at org.dyslexicdesign.limsserver.annotations.LimsTransactionRequiredInterceptor.auditMethod(LimsTransactionRequiredInterceptor.java:54)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.base/java.lang.reflect.Method.invoke(Method.java:566)
   at org.jboss.weld.interceptor.reader.SimpleInterceptorInvocation$SimpleMethodInvocation.invoke(SimpleInterceptorInvocation.java:74)
   at org.jboss.weld.interceptor.proxy.WeldInvocationContext.invokeNext(WeldInvocationContext.java:83)
   at org.jboss.weld.interceptor.proxy.WeldInvocationContext.proceed(WeldInvocationContext.java:115)
   at org.jboss.weld.bean.InterceptorImpl.intercept(InterceptorImpl.java:108)
   at org.jboss.as.weld.ejb.DelegatingInterceptorInvocationContext.proceed(DelegatingInterceptorInvocationContext.java:77)
   at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.delegateInterception(Jsr299BindingsInterceptor.java:68)
   at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.doMethodInterception(Jsr299BindingsInterceptor.java:80)
   at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.processInvocation(Jsr299BindingsInterceptor.java:93)
   at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:63)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.invocationmetrics.ExecutionTimeInterceptor.processInvocation(ExecutionTimeInterceptor.java:43)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:47)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.jpa.interceptor.SFSBInvocationInterceptor.processInvocation(SFSBInvocationInterceptor.java:57)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.stateful.StatefulSessionSynchronizationInterceptor.processInvocation(StatefulSessionSynchronizationInterceptor.java:135)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:437)
   at org.jboss.weld.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke(AbstractEJBRequestScopeActivationInterceptor.java:64)
   at org.jboss.as.weld.ejb.EjbRequestScopeActivationInterceptor.processInvocation(EjbRequestScopeActivationInterceptor.java:83)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ee.concurrent.ConcurrentContextInterceptor.processInvocation(ConcurrentContextInterceptor.java:45)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:21)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)
   at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:52)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.stateful.StatefulComponentInstanceInterceptor.processInvocation(StatefulComponentInstanceInterceptor.java:59)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInCallerTx(CMTTxInterceptor.java:254)
   at org.jboss.as.ejb3.tx.CMTTxInterceptor.required(CMTTxInterceptor.java:329)
   at org.jboss.as.ejb3.tx.CMTTxInterceptor.processInvocation(CMTTxInterceptor.java:239)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.invocationmetrics.WaitTimeInterceptor.processInvocation(WaitTimeInterceptor.java:47)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.security.SecurityContextInterceptor.processInvocation(SecurityContextInterceptor.java:100)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.deployment.processors.StartupAwaitInterceptor.processInvocation(StartupAwaitInterceptor.java:22)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.interceptors.ShutDownInterceptorFactory$1.processInvocation(ShutDownInterceptorFactory.java:64)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.interceptors.LoggingInterceptor.processInvocation(LoggingInterceptor.java:67)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ee.component.NamespaceContextInterceptor.processInvocation(NamespaceContextInterceptor.java:50)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.interceptors.AdditionalSetupInterceptor.processInvocation(AdditionalSetupInterceptor.java:54)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.ContextClassLoaderInterceptor.processInvocation(ContextClassLoaderInterceptor.java:64)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.InterceptorContext.run(InterceptorContext.java:356)
   at org.wildfly.security.manager.WildFlySecurityManager.doChecked(WildFlySecurityManager.java:636)
   at org.jboss.invocation.AccessCheckingInterceptor.processInvocation(AccessCheckingInterceptor.java:61)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.InterceptorContext.run(InterceptorContext.java:356)
   at org.jboss.invocation.PrivilegedWithCombinerInterceptor.processInvocation(PrivilegedWithCombinerInterceptor.java:80)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)
   at org.jboss.as.ee.component.ViewService$View.invoke(ViewService.java:198)
   at org.jboss.as.ee.component.ViewDescription$1.processInvocation(ViewDescription.java:185)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)
   at org.jboss.as.ee.component.ProxyInvocationHandler.invoke(ProxyInvocationHandler.java:73)
   at org.dyslexicdesign.limsserver.ejb.util.EventTypeManager$$$view3.createEventType(Unknown Source)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.base/java.lang.reflect.Method.invoke(Method.java:566)
   at org.jboss.weld.util.reflection.Reflections.invokeAndUnwrap(Reflections.java:433)
   at org.jboss.weld.bean.proxy.EnterpriseBeanProxyMethodHandler.invoke(EnterpriseBeanProxyMethodHandler.java:128)
   at org.jboss.weld.bean.proxy.EnterpriseTargetBeanInstance.invoke(EnterpriseTargetBeanInstance.java:56)
   at org.jboss.weld.bean.proxy.ProxyMethodHandler.invoke(ProxyMethodHandler.java:100)
   at org.dyslexicdesign.limsserver.ejb.util.EventTypeManager$Proxy$_$$_Weld$EnterpriseProxy$.createEventType(Unknown Source)
   at org.dyslexicdesign.limsserver.ejb.util.EventTypeManager$Proxy$_$$_WeldClientProxy.createEventType(Unknown Source)
   at org.dyslexicdesign.limsserver.startup.StartUp.configureDefaultEventTypes(StartUp.java:86)
   at org.dyslexicdesign.limsserver.startup.StartUp.StartUp(StartUp.java:60)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.base/java.lang.reflect.Method.invoke(Method.java:566)
   at org.jboss.as.ee.component.ManagedReferenceLifecycleMethodInterceptor.processInvocation(ManagedReferenceLifecycleMethodInterceptor.java:96)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.doLifecycleInterception(Jsr299BindingsInterceptor.java:114)
   at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.processInvocation(Jsr299BindingsInterceptor.java:103)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:437)
   at org.jboss.weld.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke(AbstractEJBRequestScopeActivationInterceptor.java:73)
   at org.jboss.as.weld.ejb.EjbRequestScopeActivationInterceptor.processInvocation(EjbRequestScopeActivationInterceptor.java:83)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.weld.injection.WeldInjectionInterceptor.processInvocation(WeldInjectionInterceptor.java:53)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ee.component.ManagedReferenceFieldInjectionInterceptorFactory$ManagedReferenceFieldInjectionInterceptor.processInvocation(ManagedReferenceFieldInjectionInterceptorFactory.java:107)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ee.component.AroundConstructInterceptorFactory$1.processInvocation(AroundConstructInterceptorFactory.java:28)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.weld.injection.WeldInterceptorInjectionInterceptor.processInvocation(WeldInterceptorInjectionInterceptor.java:56)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.weld.ejb.Jsr299BindingsCreateInterceptor.processInvocation(Jsr299BindingsCreateInterceptor.java:100)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ee.component.NamespaceContextInterceptor.processInvocation(NamespaceContextInterceptor.java:50)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:275)
   at org.jboss.as.ejb3.tx.CMTTxInterceptor.requiresNew(CMTTxInterceptor.java:349)
   at org.jboss.as.ejb3.tx.LifecycleCMTTxInterceptor.processInvocation(LifecycleCMTTxInterceptor.java:68)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.weld.injection.WeldInjectionContextInterceptor.processInvocation(WeldInjectionContextInterceptor.java:43)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ee.concurrent.ConcurrentContextInterceptor.processInvocation(ConcurrentContextInterceptor.java:45)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.ContextClassLoaderInterceptor.processInvocation(ContextClassLoaderInterceptor.java:64)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.as.ejb3.component.singleton.StartupCountDownInterceptor.processInvocation(StartupCountDownInterceptor.java:25)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.InterceptorContext.run(InterceptorContext.java:356)
   at org.jboss.invocation.PrivilegedWithCombinerInterceptor.processInvocation(PrivilegedWithCombinerInterceptor.java:80)
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
   at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)
   at org.jboss.as.ee.component.BasicComponent.constructComponentInstance(BasicComponent.java:161)
   at org.jboss.as.ee.component.BasicComponent.constructComponentInstance(BasicComponent.java:134)
   at org.jboss.as.ee.component.BasicComponent.createInstance(BasicComponent.java:88)
   at org.jboss.as.ejb3.component.singleton.SingletonComponent.getComponentInstance(SingletonComponent.java:124)
   at org.jboss.as.ejb3.component.singleton.SingletonComponent.start(SingletonComponent.java:138)
   at org.jboss.as.ee.component.ComponentStartService$1.run(ComponentStartService.java:54)
   at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
   at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
   at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
   at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
   at java.base/java.lang.Thread.run(Thread.java:829)
   at org.jboss.threads.JBossThread.run(JBossThread.java:320)
Caused by: org.postgresql.util.PSQLException: ERROR: function khoj.get_transaction_record(unknown, unknown, unknown, unknown) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 215
   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
   at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
   at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
   at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
   at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:132)
   at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:214)
   at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:4201)
   at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:279)
   at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:375)
   ... 296 more

If I run the SQL ...

select "get_transaction_record"."uuid", "get_transaction_record"."date_of_creation", "get_transaction_record"."date_of_expiration", "get_transaction_record"."user_name", "get_transaction_record"."break_point" from "khoj"."get_transaction_record"('SYSTEM', 'XXX', 'Trunk', '')

in a sql client it returns a record with no problems.

I don't know what else to try or check. Any thoughts would be much appreciated.

Thanks

Kevin

Rob Sargent

unread,

May 3, 2021, 7:13:14 PM5/3/21

to jooq...@googlegroups.com

I suspect your search_path does not include khoj. If you can connect using psql use the “\df” meta-command to list functions (\df *get_tran* might be informative).

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/8739011a-6b05-4e8b-8f75-9d207b29895cn%40googlegroups.com.

embree...@gmail.com

unread,

May 3, 2021, 10:32:19 PM5/3/21

to jOOQ User Group

Seems fine to me ....

\c khoj khoj

\df get_transaction_record

khoj=> \df get_transaction_record

                                                                               List of functions
Schema |          Name          |                          Result data type                                   |           Argument data types      | Type
--------+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+--------
khoj   | get_transaction_record | TABLE(uuid uuid, date_of_creation timestamp without time zone, date_of_expiration timestamp without time zone, user_name character varying, break_point bigint)|

Double checked the grants and khoj was granted usage of the function.

Only oddness I see at all is SQL type of 'timestamp without time zone' here is mapped to SQLDataType.LOCALDATETIME(0)

Where as use in other tables maps to SQLDataType.LOCALDATETIME(6).

Don't know that would be significant.

Thanks for the idea,

-Kevin

Rob Sargent

unread,

May 3, 2021, 10:51:54 PM5/3/21

to jooq...@googlegroups.com

Far as I can tell you only show three of the columns for the function def (schema, name, return type).

khoj.get_transaction_record(unknown, unknown, unknown, unknown) does not exist

But the parser doesn’t know the data type of each arg you’re passing in. Have you tried wrapping those in “cast”, especially the date.

This seems a very cumbersome way of useing jOOQ. Have you not generated classes, including for the functions?

Lukas Eder

unread,

May 4, 2021, 2:20:05 AM5/4/21

to jOOQ User Group

Kevin, the missing precision on the timestamp precision in generated code might be due to https://github.com/jOOQ/jOOQ/issues/11191, but wouldn't affect the query in the way you're seeing.

You could indeed attempt to explicitly cast your arguments (e.g. inline("").cast(VARCHAR)), but I don't see why you should have to. When you're inlining the arguments, then PostgreSQL should be able to infer the types (I can't reproduce your problem on my machine, with PostgreSQL 13 - what version are you using of PostgreSQL and of the jdbc driver?).

I wonder if the jOOQ code you've shown is really the code you've executed. Your stack trace shows fetchInto(), but you're showing a call to fetchOne() - might not be relevant.

Can you help reproduce this? We have a template to create MCVE's (minimal, complete, verifiable examples) here: https://github.com/jOOQ/jOOQ-mcve but any other way would be useful too. I'd be looking out for anything specific to your setup, including your Settings, JDBC connection parameters, software versions, etc.

Thanks,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/9F223085-88D8-45CA-871B-6112FD127C2B%40gmail.com.

embree...@gmail.com

unread,

May 4, 2021, 7:34:57 AM5/4/21

to jOOQ User Group

Thank you for the response,

Postgers Version 10.16

org.postgresql.Driver (version 42.2)

Must of cut and pasted in between tries. I tried

.fetchOne()

.fetchOneInto(GetTransactionRecordRecord.class)

.fetch()

.fetchInto(GetTransactionRecordRecord.class)

All with the same result.

I will try the explicit casting and see if that makes any difference and also see about setting up a MVCE as well. Hopefully that will bring to light the problem.

Thanks,

Kevin

Lukas Eder

unread,

May 11, 2021, 10:11:59 AM5/11/21

to jOOQ User Group

Hi Kevin,

Did casting yield any results? Could you track down the problem to something specific?

Thanks,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/ef6555a5-1b6e-4b9a-bbe8-6e3091f7893en%40googlegroups.com.

Reply all

Reply to author

Forward

0 new messages