Announcement Announcement Module
Collapse
No announcement yet.
not work SimpleJdbcInsert executeAndReturnKey on the MS SQL Server 2007 Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • not work SimpleJdbcInsert executeAndReturnKey on the MS SQL Server 2007

    I use Spring 2.5.6. I use Microsoft SQL Server JDBC Driver 2.0.
    I create in the MS SQL Server 2007 the next table:
    Code:
    CREATE TABLE employer (ID INTEGER NOT NULL PRIMARY KEY IDENTITY  , NAME VARCHAR(255) NOT NULL, EIN VARCHAR(20),
    						STATUS_ID INTEGER, CONTACT VARCHAR(100), 
    						ADDRESS VARCHAR(100), CITY VARCHAR(100), STATE VARCHAR(50), ZIP VARCHAR(8),
    						MAILING_ADDRESS VARCHAR(100), MAILING_CITY VARCHAR(100), MAILING_STATE VARCHAR(100),
    ...
    my dao:
    Code:
    public class JdbcEmployerDao extends SimpleJdbcDaoSupport implements
    		EmployerDao {
    ...
    	public void add(Employer employer) {
    		MapSqlParameterSource psMapEmployer = new MapSqlParameterSource();
    		psMapEmployer.addValue("NAME", employer.getName());
    		psMapEmployer.addValue("EIN", employer.getEin());
    ...
    
    		SimpleJdbcInsert insertProcedureDocument = new SimpleJdbcInsert(
    					getDataSource());
    		insertProcedureDocument.withTableName("employer")
    					.usingGeneratedKeyColumns("id");
    		Number newNumberId = insertProcedureDocument
    					.executeAndReturnKey(psMapEmployer); // throw exception
    			newEmployer_Id = newNumberId.intValue();
    stack trace:
    Code:
    Sep 20, 2009 4:53:39 PM com.mycompany.myproject.web.controllers.EmployersController add
    SEVERE: PreparedStatementCallback; bad SQL grammar []; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Operand type cla
    sh: nvarchar is incompatible with image
    org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is com.microsoft.sqlserver.
    jdbc.SQLServerException: Operand type clash: nvarchar is incompatible with image
            at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)
            at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    
            at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    
            at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    
            at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607)
            at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:824)
            at org.springframework.jdbc.core.simple.AbstractJdbcInsert.executeInsertAndReturnKeyHolderInternal(AbstractJdbcInsert.java:421)
            at org.springframework.jdbc.core.simple.AbstractJdbcInsert.executeInsertAndReturnKeyInternal(AbstractJdbcInsert.java:402)
            at org.springframework.jdbc.core.simple.AbstractJdbcInsert.doExecuteAndReturnKey(AbstractJdbcInsert.java:369)
            at org.springframework.jdbc.core.simple.SimpleJdbcInsert.executeAndReturnKey(SimpleJdbcInsert.java:110)
            at com.mycompany.myproject.repository.JdbcEmployerDao.add(JdbcEmployerDao.java:271)
            at com.mycompany.myproject.service.EmployerManagerImpl.add(EmployerManagerImpl.java:30)
            at com.mycompany.myproject.web.controllers.EmployersController.add(EmployersController.java:290)
            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.web.servlet.mvc.multiaction.MultiActionController.invokeNamedMethod(MultiActionController.java:473)
            at org.springframework.web.servlet.mvc.multiaction.MultiActionController.handleRequestInternal(MultiActionController.java:410)
            at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
            at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
            at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
            at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:807)
            at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)
            at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:511)
            at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
            at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
            at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
            at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
            at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
            at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
            at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
            at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
            at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
            at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
            at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
            at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
            at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
            at java.lang.Thread.run(Thread.java:619)
    Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Operand type clash: nvarchar is incompatible with image
            at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
            at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
            at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
            at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
            at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
            at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
            at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
            at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
            at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:306)
            at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
            at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:826)
            at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
            ... 34 more
    But if I run this code in the MySQL it's work fine !!!

    I also try by HashMap:
    Code:
    		Map<String, Object> parameters = new HashMap<String, Object>();
    		parameters.put("NAME", employer.getName());
    		parameters.put("EIN", employer.getEin());
    ...
    			SimpleJdbcInsert insertProcedureDocument = new SimpleJdbcInsert(
    					getDataSource());
    			insertProcedureDocument.withTableName("employer")
    					.usingGeneratedKeyColumns("id");
    			Number newNumberId = insertProcedureDocument
    					.executeAndReturnKey(parameters); // throw same Exception
    			newEmployer_Id = newNumberId.intValue();
    But it's NOT help!
    Last edited by a_subscriber; Sep 20th, 2009, 10:13 AM.

  • #2
    have you seen this in your exception?

    Code:
    jdbc.SQLServerException: Operand type clash: nvarchar is incompatible with image
    are you sure tables are well defined? or maybe you data is accessing to other types.

    Comment


    • #3
      are you sure tables are well defined? Looks like table data type is mismatch.

      /Murugesan.

      Comment


      • #4
        SQL Server 2007???

        Perhaps one of the problems is you're using SQL Server 2007!!! To the best of my knowledge, there's no SQL Server 2007; it must be either 2005 or 2008. Assuming it's 2005, perhaps you would do better if you define your column as varbinary(max) (this may allow up to 2GB in size).

        Another possibility is you have a defective MS JDBC driver (I have had this experience more than once). In this case I suggest you get the jTDS JDBC driver (search for the jTDS word in Google or simply go to thejtds.sourceforge.net website).

        This driver works fine with SQL Server.

        Comment

        Working...
        X