'last_insert_id' is not a recognized function name

Question

i get this error:

last_insert_id is not a recognized function name.

when I try to send data to the server to be saved in a SqlServer 2008 database. It was working fine but suddenly I am having this issue.

Here's the full exception trace:

12:38:48,867  WARN JDBCExceptionReporter:77 - SQL Error: 195, SQLState: 42000
12:38:48,867 ERROR JDBCExceptionReporter:78 - 'last_insert_id' is not a recognized built-in function name.
12:38:48,867  WARN JDBCExceptionReporter:54 - SQL Warning: 195, SQLState: 42000
12:38:48,883  WARN JDBCExceptionReporter:55 - Preparing the statement failed: 'last_insert_id' is not a recognized built-in function name.
12:38:48,883 ERROR SaveOrderAction:91 - Services level, can't save order - Order: (id=0, visitorId=2, status=A, customerId=528/nOrderDetail:(id=0, orderId=0, productId=124, amount=2)/n)
com.kaizen.gate.service.ServiseException: Services level, can't save order - Order: (id=0, visitorId=2, status=A, customerId=528/nOrderDetail:(id=0, orderId=0, productId=124, amount=2)/n)
    at com.kaizen.gate.service.impl.OrderServiceImpl.save(OrderServiceImpl.java:27)
    at com.kaizen.gate.http.action.SaveOrderAction.doSpecificAction(SaveOrderAction.java:82)
    at com.kaizen.gate.http.action.Action.execute(Action.java:39)
    at com.kaizen.gate.http.ActionServlet.doPost(ActionServlet.java:29)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at com.kaizen.gate.http.LoginFilter.doFilter(LoginFilter.java:40)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at com.kaizen.gate.http.UrlFilter.doFilter(UrlFilter.java:37)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:579)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:307)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
    at java.lang.Thread.run(Thread.java:619)
Caused by: org.springframework.dao.InvalidDataAccessResourceUsageException: could not retrieve generated id after insert: [com.kaizen.gate.model.Order]; SQL [insert into MobileOrder (created, visitor_id, status, customer_id) values (?, ?, ?, ?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not retrieve generated id after insert: [com.kaizen.gate.model.Order]
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:629)
    at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)
    at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:339)
    at com.kaizen.gate.dao.impl.OrderDaoImpl.save(OrderDaoImpl.java:15)
    at com.kaizen.gate.service.impl.OrderServiceImpl.save(OrderServiceImpl.java:24)
    ... 27 more
Caused by: org.hibernate.exception.SQLGrammarException: could not retrieve generated id after insert: [com.kaizen.gate.model.Order]
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:69)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2163)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2643)
    at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:51)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
    at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:298)
    at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:181)
    at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:107)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:187)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:172)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:94)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
    at org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:507)
    at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:499)
    at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:495)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.orm.hibernate3.HibernateTemplate$CloseSuppressingInvocationHandler.invoke(HibernateTemplate.java:1282)
    at $Proxy4.saveOrUpdate(Unknown Source)
    at com.kaizen.gate.dao.impl.OrderDaoImpl$1.doInHibernate(OrderDaoImpl.java:19)
    at com.kaizen.gate.dao.impl.OrderDaoImpl$1.doInHibernate(OrderDaoImpl.java:15)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
    ... 30 more
Caused by: java.sql.SQLException: 'last_insert_id' is not a recognized built-in function name.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
    at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:778)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:55)
    ... 53 more

`last_insert_id()` is as far as I know not an MSSQL function, it's a MySQL function. — Joachim Isaksson, Sep 05 '12 at 04:46
@JoachimIsaksson can you tell me from the exception code where this last_insert_id is or i can find — ZAJ, Sep 05 '12 at 04:57
have u seen this [http://stackoverflow.com/questions/3837990/last-insert-id-mysql] — NoNaMe, Sep 05 '12 at 05:02

prayagupa · Answer 1 · 2012-09-05T05:05:36.777

do select @@identity to get the latest value in sql server. Here is the documentation

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.

@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

score 1 · Answer 2 · answered Sep 05 '12 at 06:21

If you want the last inserted record's ID, you can use in your DAOImplementation:

sessionFactory.getCurrentSession().persist(result); // result is object to save
sessionFactory.getCurrentSession().flush();
return result.getId(); //getter method from result object to get inserted records ID

JonathanApollo · Answer 3 · 2013-03-22T20:26:24.233

1

I received this error too; I'm running two environments with my app one using SQL Server and the other using MySQL, turned out that I forgot to switch the dialect when I switched environments and it was this that was causing my error. Looks like your database dialect has been incorrectly set.

edited Mar 22 '13 at 20:26

answered Mar 22 '13 at 19:28

JonathanApollo

11
3

score 0 · Answer 4 · answered May 22 '18 at 11:21

To get last inserted Id from the table you can use one of them from the given functions @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT in SQL Server.

To use bettor function check the given URL

http://www.freshcodehub.com/Article/51/difference-between-identity-scopeidentity-identcurrent-in-sql-server

'last_insert_id' is not a recognized function name

4 Answers4