0

I am working with MyBatis and Informix DB. In my DB client the SQL works just fine. When I call the method in my program, there is an SQL syntax error.

<insert id="insertDataArchive">
        INSERT INTO dat_arch(id,code,dom,change_type,start_date,end_date)
        SELECT              (id,code,dom,#{changeType}, #{startDate}, #{endDate})
        FROM data_store
        WHERE id = #{id}
</insert>

The error messages I get are:

Error updating database.  Cause: java.sql.SQLSyntaxErrorException: A syntax error has occurred.
Cause: java.sql.SQLSyntaxErrorException: A syntax error has occurred.

There is no detail info, just these two messages.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
vviston
  • 183
  • 1
  • 12
  • 2
    You need to 1) remove the parentheses enclosing the column list of SELECT and 2) cast the parameters e.g. `cast(#{startDate} as date)`. This might be a duplicate of [this question](https://stackoverflow.com/q/14650288/1261766). – ave Sep 17 '20 at 16:11
  • I agree with 'remove the parentheses'. I'm not so sure about the CAST being necessary, but it depends on what the MyBatis code does with `#{startDate}`. If it treats it as a string in quotes (e.g. `'2020-09-17'` and your DB is set up to recognize ISO 8601 / ISO 9071 date notation, perhaps because you set `DBDATE=Y4MD-` in the environment), then the explicit cast is unnecessary, though it will do no harm. If it does something else, a cast may be necessary. – Jonathan Leffler Sep 17 '20 at 17:41
  • Tracking the exact SQL sent to the server (DBMS) will be tricky; you may end up needing to set `SQLIDEBUG=mybatis` in the connection string ([Debug your JDBC API program](https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.jdbc_pg.doc/ids_jdbc_282.htm)), or setting the environment variable `SQLIDEBUG=1:mybatis` or `SQLIDEBUG=2:mybatis`. The output file (which will start with the string `mybatis`) is text (if you set `1`) or binary (`2`). The binary file can be interpreted by `sqliprint`. – Jonathan Leffler Sep 17 '20 at 17:45
  • You can also enable logging on MyBatis I believe to print the underlying sql (see https://stackoverflow.com/questions/7267834/how-to-configure-log4j-for-mybatis-to-print-my-sql) If you can do that and show the SQL that is generated for Informix we will be able to see what the problem is. – Brian Hughes Sep 18 '20 at 13:15

1 Answers1

0

You can also use with insert query

<selectKey keyProperty="" resultType="" order="BEFORE"> select ..... </selectKey>

Prateek Chaudhary
  • 141
  • 10