Hello Team,
I have a problem when inserting data to a temporary table ( i am using sybase datase ).
The error is the following:
xxxx not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
Can you advise please.
Thank you
Best Regards
We need more information to help you. Which Hibernate and Sybase version are you using? How do you call the stored procedure?
Hello Beikov,
Thank you for your help.
I am using hibernate 6.1.5.
Currently i am facing the issue when trying to persist data into a temporary table, no stored procedure is used here.
I created an entity class that represent the temporary table and i am trying to persist data into it like this:
entitymanager.persist(object).
object is an instance of the entity class that represent the temporary table.
Thank you
Please show the mapping that you use and the full stack trace of the error.
Dear Beikov;
Please find below the error:
Kindly note that if i copy these same queries and run them inside sqlserver management studio, the table is well created and i can insert data into it and get data from it.
But from hibernate createNativeQuery i am not able to make any query related to this created temporary table ‘#SYS_PARAM_KEY_LABEL_TMP’
Waiting for your feedback please.
Dear Beikov,
Any advise on this?
I don’t know how you are trying to create the tables, but apparently in a way that produces an error, yet you are not showing that in the log. Maybe the user you use for the connection has no permission to create tables?
Dear Beikov,
I tried to execute the same queries in sql server and it works, i have been logged in with the same user.
Any way to create a table without calling the executeUpdate() on a native query.
Thank you
Please show me the code that executes the statements. Maybe you need to commit after creating the table.
public void createLABELING_FILE_TMP(TranslationCO translationCO) throws DAOException
{
StringBuilder query = new StringBuilder();
query.append("CREATE TABLE #SYS_PARAM_KEY_LABEL_TMP( ");
query.append(" APP_NAME VARCHAR(4) ");
query.append(" COLLATE DATABASE_DEFAULT ");
query.append(" NOT NULL, ");
query.append(" PROG_REF VARCHAR(15) ");
query.append(" COLLATE DATABASE_DEFAULT ");
query.append(" NOT NULL,");
query.append(" KEY_LABEL_CODE VARCHAR(250) ");
query.append("COLLATE DATABASE_DEFAULT");
query.append(" NOT NULL," + " KEY_GROUP_ID NUMERIC(4) NULL," + "KEY_LABEL_DESC TEXT ");
query.append("COLLATE DATABASE_DEFAULT");
query.append(" NULL)");
Query createSYS_PARAM_KEY_LABEL_TMP = hibernateCommonUtils.createNativeQuery(query.toString());
createSYS_PARAM_KEY_LABEL_TMP.executeUpdate();
}
hibernateCommonUtils is calling the entityManager.createNativeQuery(“sql string”)
Are you executing createLABELING_FILE_TMP and the query that does select * from #SYS_PARAM_KEY_LABEL_TMP on the same entity manager?
Dear Beikov,
yes it is the same entityManager, does the executeUpdate() can change the entity manager?
Dear Beikov,
After testing the same, the entity manager is the same after calling executeUpdate(); so all my queries are exectuted in one entity manager.
Please provide a reproducer for this issue so that we can look deeper into this: hibernate-test-case-templates/JPAUnitTestCase.java at main · hibernate/hibernate-test-case-templates · GitHub
Dear Beikov,
I Dont have access to push on this repository.
Kindly find below the file.java where the scenario is available.
Waiting for your advice.
Thank you
@Repository
public class LabelManagementDAOImpl implements LabelManagementDAO
{
@PersistenceContext
private EntityManager em;
/**
THIS METHOD CREATE A HASH TABLE THAN THE FIRST EXECUTE UPDATE WORK'S WELL BUT AFTER THE EXECUTE UPDATE IF I TRY
TO ACCESS THE SAME HASH TABLE AN ERROR IS THROWN ( INVALID OBJECT NAME )
FOR MY DATABASE CONNECTION I AM USING THE BELOW DRIVER:
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>8.4.1.jre11</version>
</dependency>
*/
@Transactional
@Override
public void standAloneTest()
{
String hql = "CREATE TABLE #DS_ZAHI_TMP (id int)";
String hql2 = " insert into #DS_ZAHI_TMP (id) select id from DS_ZAHI where city = 'zahi' ";
String hql3 = " delete from #DS_ZAHI_TMP where id IN (select id from DS_ZAHI) ";
String hql4 = hql.concat(hql2).concat(hql3);
Query temTest = em.unwrap(Session.class).createSQLQuery(hql4);
temTest.executeUpdate();
temTest = em.unwrap(Session.class).createSQLQuery(" insert into #DS_ZAHI_TMP (id) select id from DS_ZAHI where city = 'zahi' ");
temTest.executeUpdate();
}
}
I tried to execute the same queries in sql server and it works, i have been logged in with the same user.
You are missing semicolons between the statements… You can’t just concat multiple statements without the statement separator.
Thanks for your reply; however SQL server does not require semicolons.
In all cases we added the semicolons and still all the statements and the complete scenario is working on Oracle and Sybase DBs while when connected to SQL Server the temp table is disappearing after the first “executeUpdate” invocation.
The Console is showing the below error only on SQL server (same with semicolons added):
Dec 12, 2022 9:37:27 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 208, SQLState: S0002
Dec 12, 2022 9:37:27 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Invalid object name ‘#DS_ZAHI_TMP’.
[12/12/2022 09:37:27.674]com.az.kernel.api.RestExceptionHandler@handleGenericException(RestExceptionHandler.java:59)
[ERROR]: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708)
at com.az.aztranslationservice.dao.impl.LabelManagementDAOImpl$$EnhancerBySpringCGLIB$$ccc1aa76.standAloneTest()
at com.az.aztranslationservice.bo.impl.LabelManagementBOImpl.standAloneTest(LabelManagementBOImpl.java:417)
at com.az.aztranslationservice.bo.impl.LabelManagementBOImpl$$FastClassBySpringCGLIB$$156719ce.invoke()
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy.invokeMethod(CglibAopProxy.java:386)
at org.springframework.aop.framework.CglibAopProxy.access$000(CglibAopProxy.java:85)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:704)
at com.az.aztranslationservice.bo.impl.LabelManagementBOImpl$$EnhancerBySpringCGLIB$$a9fa4e74.standAloneTest()
at com.az.aztranslationservice.api.TranslationServiceController.test(TranslationServiceController.java:207)
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.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:655)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:890)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
at org.hibernate.engine.query.spi.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:107)
at org.hibernate.internal.SessionImpl.executeNativeUpdate(SessionImpl.java:1554)
at org.hibernate.query.internal.NativeQueryImpl.doExecuteUpdate(NativeQueryImpl.java:299)
at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1696)
at com.az.aztranslationservice.dao.impl.LabelManagementDAOImpl.standAloneTest(LabelManagementDAOImpl.java:579)
at com.az.aztranslationservice.dao.impl.LabelManagementDAOImpl$$FastClassBySpringCGLIB$$1fae3e4.invoke()
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
… 72 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name ‘#DS_ZAHI_TMP’.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7375)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3206)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:473)
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.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
at com.sun.proxy.$Proxy210.executeUpdate(Unknown Source)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
… 83 more
Can you advise which versions of hibernate, SQL_JDBC, and SQL Server you’re using?
I am currently using Hibernate 6.1.5, mssql-jdbc 11.2.1.jre11, and Microsoft SQL Server 2019 (RTM-CU11) (KB5003249) - 15.0.4138.2 (X64).