[IBATIS-694] iBATIS wrongly blaims selectKey for null-values without jdbcType in insert-statements

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 3.0 Beta 5
• Fix Version/s: 3.0 Beta 6
• Component/s: Core
• Environment: N/A
• Assignee: Unassigned
• Reporter: Edvin Syse
• Created: 13/Nov/09 16:02
• Updated: 06/Dec/09 02:46
• Resolved: 06/Dec/09 02:46

Description

See attached email from [e-mail]. To reproduce:

Create an insert statement with an embedded selectKey statement. Call the insert statement with null value for one or more fields that doesn't declare a jdbcType. An org.apache.ibatis.type.TypeException is correctly thrown, indicating that jdbcType must be specified for all nullable parameters, but the SQL: reference blaims the selectKey instead of the insert statement:

1. 1. 1. SQL: SELECT nextval('messages_id_seq')

On Fri, Nov 13, 2009 at 8:29 AM, Edvin Syse <[e-mail]> wrote:
I just figured it out. The folder property of my domain object was null. It seems that the error message blaims the selectKey statement instead of the insert-statement. If it is possible to separate them so that the error message is more informative/correct, I think a lot of confusion can be avoided

– Edvin

Edvin Syse wrote:

Hi!

I have declared a sequence in Postgres and try to use it in a selectKey statement that runs before an insert:

<insert id="insert" parameterType="MessageSummary">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
SELECT nextval('messages_id_seq')
</selectKey>
INSERT INTO messages (id, folder, uid, subject, deliverydate, date, from, size, attachments, expunged, read)
VALUES (#

{id}, #{folder.id}, #{uid}, #{subject}, #{deliveryDate}, #{date}, #{from}, #{size}, #{attachments}, #{expunged}, #{read})
</insert>

This yields the following exception:

org.apache.ibatis.exceptions.IbatisException:
### Error updating database. Cause: org.apache.ibatis.type.TypeException: JDBC requires that the JdbcType must be specified for all nullable parameters.
### The error may exist in no/tornado/mail/mappers/MessageMapper.xml
### The error may involve no.tornado.mail.mappers.MessageMapper.insert-Inline
### The error occurred while setting parameters
### SQL: SELECT nextval('messages_id_seq')
### Cause: org.apache.ibatis.type.TypeException: JDBC requires that the JdbcType must be specified for all nullable parameters.
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:95)
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:82)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:49)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:18)

The query for nextval() returns an integer. None of the columns in the database are nullable, and the SQL it's complaining about doesn't contain any parameters at all.

I have another statement using the same syntax that actually works:

<insert id="insert" parameterType="MailFolder">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
SELECT nextval('folders_id_seq')
</selectKey>
INSERT INTO folders (id, userid, uidvalidity, parent, path) VALUES (#{id}

, #

{userId}

, #

{uidValidity}

, #

{parent.id,jdbcType=NUMERIC}

, #

{path}

)
</insert>

This table has one nullable column. Both sequences are created using the same syntax (CREATE sequence <sequence-name>

Am I doing something wrong, or could this be a bug?

Sincerely,
Edvin Syse