0

I have an insert statement in MyBatis using Oracle 11g R2 on the backend with the Oracle ojdbc6 driver.

I am repeatedly getting

java.sql.SQLSyntaxErrorException: ORA-01745: invalid host/bind variable name

However I don't see what is causing the issue, I'm not using any Oracle reserved Keywords.

<insert id="createRecord" parameterType="org.appliication.core.domain.TRRecord"
            statementType="PREPARED" useGeneratedKeys="true" keyColumn="ID" keyProperty="id">

        INSERT INTO T_TR_PUBLICATION p (
          p.TR_UID,
          p.TITLE,
          p.ITEM_TITLE,
          p.COVER_DATE,
          p.HAS_ABSTRACT,
          p.ISSUE,
          p.SORT_DATE,
          p.VOLUME,
          p.BEGIN_PAGE,
          p.END_PAGE,
          p.ACCESSION_NO,
          p.ISSN,
          p.DOI,
          p.FUNDING_TEXT
        )
        VALUES (
          #{trUid, jdbcType=NULL},
          #{title, jdbcType=NULL},
          #{titleItem, jdbcType=NULL},
          #{coverDate, jdbcType=NULL},
          #{hasAbstract, jdbcType=NULL},
          #{issue, jdbcType=NULL},
          #{sortDate, jdbcType=NULL}
          #{journalVolume, jdbcType=NULL},
          #{pageBegin, jdbcType=NULL},
          #{pageEnd, jdbcType=NULL},
          #{accessionNo, jdbcType=NULL},
          #{issn, jdbcType=NULL},
          #{doi, jdbcType=NULL},
          #{fundingText, jdbcType=NULL}
        )

    </insert>
greyfox
  • 6,426
  • 23
  • 68
  • 114
  • Don't know MyBatis, but try removing the `jdbcType=NULL`, since `NULL` is not a valid JDBC type for the purpose. You cannot bind a value to the `NULL` type. – Andreas Aug 18 '15 at 20:33
  • is this `org.appliication.core.domain.TRRecord` package/class path correct? – itwasntme Aug 18 '15 at 20:34
  • @Andreas, MyBatis will not work without the jdbcType=NULL because if an object like String is null, it maps to JdbcType.OTHER which Oracle JDBC driver does not understand thus it throws an Exception: UncategorizedSQLException: Error setting null for parameter #8 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: Invalid column type – greyfox Aug 18 '15 at 20:41
  • @Mastah, I've confirmed the path is correct, in fact my IDE autocompletes the path – greyfox Aug 18 '15 at 20:42
  • @greyfox Then specify the actual type you want, e.g. `VARCHAR`. This way, both a value ("abc") and a null will map to a type the JDBC driver can understand. However, I would have thought that MyBatis would have examined the types of the fields in `TRRecord` to automatically choose the correct type. – Andreas Aug 18 '15 at 20:48
  • @Andreas, you are right, you have to specify the JDBC type for anything that can potentially be NULL – greyfox Aug 18 '15 at 21:08
  • @greyfox Too bad MyBatis is too dumb to figure out the jdbcType from the (implicit) javaType. I have created an official answer so you can accept it. :-) – Andreas Aug 18 '15 at 21:12
  • accepted, from the docs it kind of suggest it is actually the Oracle JDBC driver which doesn't map JdbcType.OTHER, but either way an annoyance for sure – greyfox Aug 19 '15 at 01:32

1 Answers1

1

Specify the actual type you want, e.g. VARCHAR. This way, both a value ("abc") and a null will map to a type the JDBC driver can understand.

Andreas
  • 154,647
  • 11
  • 152
  • 247