Announcement Announcement Module
Collapse
No announcement yet.
SimpleJdbcInsert fails with JGeometry Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • SimpleJdbcInsert fails with JGeometry

    All,
    I have a strange error with Oracle/JGeometry where my inserts on tables with columns of type MDSYS.SDO_GEOMETRY are failing.
    I have tried using registerSqlType/registerTypeName with no luck. If I use a PreparedStatement on the same table it works? If I use a simpleJDBCinsert it fails. Below is the stack trace and the code. Debugging indicates
    that for some reason Spring is putting null in the field.... Any help is appreciated...

    The following parameters are used for call INSERT INTO TESTS (NAME, AGE, COVERAGE) VALUES(?, ?, ?) with: [org.springframework.jdbc.core.SqlParameterValue@7c 4768, org.springframework.jdbc.core.SqlParameterValue@13 42f5b, null]
    STACK://XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XX
    copying property [name], value=[Clause], sql type=[12], sql type name=[null]
    copying property [testId], value=[0], sql type=[4], sql type name=[null]
    Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected MDSYS.SDO_GEOMETRY got CHAR

    at org.springframework.jdbc.support.SQLExceptionSubcl assTranslator.doTranslate(SQLExceptionSubclassTran slator.java:93)
    at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.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.AbstractJdbcI nsert.executeInsertAndReturnKeyHolderInternal(Abst ractJdbcInsert.java:421)
    at org.springframework.jdbc.core.simple.AbstractJdbcI nsert.executeInsertAndReturnKeyInternal(AbstractJd bcInsert.java:402)
    at org.springframework.jdbc.core.simple.AbstractJdbcI nsert.doExecuteAndReturnKey(AbstractJdbcInsert.jav a:369)
    at org.springframework.jdbc.core.simple.SimpleJdbcIns ert.executeAndReturnKey(SimpleJdbcInsert.java:110)
    at com.saic.steve.AimesDAO.insert(AimesDAO.java:95)
    at com.saic.steve.InjectSimple.main(InjectSimple.java :54)
    Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected MDSYS.SDO_GEOMETRY got CHAR

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoe r.java:439)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoe r.java:395)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall. java:802)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.jav a:436)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java: 186)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:5 21)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4 CPreparedStatement.java:205)
    at oracle.jdbc.driver.T4CPreparedStatement.executeFor Rows(T4CPreparedStatement.java:1008)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTi meout(OracleStatement.java:1307)
    at oracle.jdbc.driver.OraclePreparedStatement.execute Internal(OraclePreparedStatement.java:3449)
    at oracle.jdbc.driver.OraclePreparedStatement.execute Update(OraclePreparedStatement.java:3530)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper. executeUpdate(OraclePreparedStatementWrapper.java: 1350)
    at org.apache.commons.dbcp.DelegatingPreparedStatemen t.executeUpdate(DelegatingPreparedStatement.java:1 02)
    at org.springframework.jdbc.core.JdbcTemplate$3.doInP reparedStatement(JdbcTemplate.java:826)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:591)
    ... 7 more
    Disconnected from the target VM, address: '127.0.0.1:3379', transport: 'socket'

    Process finished with exit code 1


    CODE://XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XX

    SimpleJdbcInsert tableInsert = new SimpleJdbcInsert( getDataSource() );

    tableInsert.withTableName("TESTS");
    tableInsert.usingGeneratedKeyColumns("TESTID");

    BeanPropertySqlParameterSource beanObjectMap = new BeanPropertySqlParameterSource(test);
    MapSqlParameterSource internalSqlParameterSource = new MapSqlParameterSource();


    Connection oracleConnection = null;
    STRUCT obj = null;
    if (getConnection() instanceof org.apache.commons.dbcp.DelegatingConnection) {
    // This returns a org.apache.commons.dbcp.PoolableConnection
    Connection pc = ((org.apache.commons.dbcp.DelegatingConnection)get Connection()).getDelegate();

    // The PoolableConnection is a DelegatingConnection itself - get the delegate (the Oracle connection)
    oracleConnection = (Connection)((org.apache.commons.dbcp.DelegatingCo nnection)pc).getDelegate();
    }

    for (String propName : beanObjectMap.getReadablePropertyNames()) {

    System.out.println("copying property [" + propName +
    "], value=[" + beanObjectMap.getValue(propName) +
    "], sql type=[" + beanObjectMap.getSqlType(propName) +
    "], sql type name=[" + beanObjectMap.getTypeName(propName) + ']');
    if(propName.equals("geom"))
    {
    try {
    obj = JGeometry.store((JGeometry)beanObjectMap.getValue( propName),oracleConnection);
    } catch (SQLException e) {
    e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
    }
    internalSqlParameterSource.addValue(propName,
    obj,
    beanObjectMap.getSqlType(propName),
    beanObjectMap.getTypeName(propName));
    } else {
    internalSqlParameterSource.addValue(propName,
    beanObjectMap.getValue(propName),
    beanObjectMap.getSqlType(propName),
    beanObjectMap.getTypeName(propName));
    }
    }
    Map params = internalSqlParameterSource.getValues();
    String typeName = internalSqlParameterSource.getTypeName("geom");
    int type = internalSqlParameterSource.getSqlType("geom");

    String[] keys = tableInsert.getGeneratedKeyNames();
    List<String> colNames = tableInsert.getColumnNames();
    long id = tableInsert.executeAndReturnKey(beanObjectMap).lon gValue();
    String insertst = tableInsert.getInsertString();
    }
Working...
X