Invalid column type: 1111

[MSana]

When I am doing in the insert into the table, I am getting the
following error. I am running it as stand alone application using
spring and MyBatis. Can anyone tell me how to print sql in the console
(eclipse) and how to find which column it is complaining for invalid
type. There are around 50 columns and it is hard to find out without
proper error message.

Thanks in advance

Caused by: org.apache.ibatis.type.TypeException: Error setting null
parameter. Most JDBC drivers require that the JdbcType must be
specified for all nullable parameters. Cause: java.sql.SQLException:
Invalid column type: 1111
at
org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:
17)
at
org.apache.ibatis.executor.parameter.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:
73)
at
org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:
61)
at
org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:
43)
at
org.apache.ibatis.executor.ReuseExecutor.prepareStatement(ReuseExecutor.java:
62)
at
org.apache.ibatis.executor.ReuseExecutor.doUpdate(ReuseExecutor.java:
31)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:
73)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:
102)

[Larry Meadors]

One of the values you are inserting is null, and you didn't tell
mybatis the jdbc type, so it uses the default jdbc type which is
Types.UNKNOWN (1111).

The reason you don't get the column name that is causing the issue is
that the driver doesn't tell us - it's not a mybatis thing, it's just
how the driver works.

Sucks, but true.

I'd look at the data you're inserting to see which fields are null -
it's one of them. Change your mapped statement to provide jdbc types
for them, and you should be good to go.

Larry

[MSana]

Thanks Larry for response. I had to go through 50 columns to find for
which column it is complaining. I was able to fix it now.

Can you please tell me what changes do I need to make so that it
split the sql in the log? I am running as stand alone application

Thanks

[Larry Meadors]

If you're using log4j, add this:

log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.org.apache=DEBUG
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

Larry

[srinivasulu atte]

Hi,

  I am getting the same problem.. the problem is for below mapping..
my data field is Integer and i have mapped like below. while updating.

    private Integer adrPhone1;

ADR_PHONE1 = #{adrPhone1,jdbcType=NUMERIC},

Could you please help me, where can i get right jdbctypes for mybatis.

Thanks,
Srini

[Eduardo Macarron]

Probably you are inserting a null value somewhere (maybe not in the property you pointed) and mybatis is setting the type to OTHER (1111).

2013/11/13 srinivasulu atte <atte.s...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[srinivasulu atte]

Hi Msana,

     I am also getting the same problem, could you please check my mapper and let me know where i am doing mistake..attached the model and mapper.

Thanks,
Srini

On Sunday, October 17, 2010 12:00:27 AM UTC-4, MSana wrote:

[Rick R]

If some of your insert/update columns could be null then you should declare the jdbc type:

I do not know your code so I'm not sure which ones are nullable or not.. probably onces like adrStreet2 etc. are so for those

you'll need declarations like

#{adrStreet2,jdbcType=VARCHAR}

#{idnRequestor},

#{nmeRequestorLast},

#{nmeRequestorFirst},

#{nmeRequestorMinit},

#{nmeSuffix},

#{adrStreet1},

#{adrStreet2},

#{adrCity},

#{adrState},

#{adrZip5},

#{adrZip4},

#{adrCountry},

#{adrPhone1},

#{adrPhone2},

#{adrPhone3},

#{nmeRequestorFirm},

#{emailAddress},

#{phoneExt},

#{amtBalance},

#{cdeStatusRequestor},

#{createdOn},

#{lastUpdatedBy}

--

You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
Rick R

[srinivasulu atte]

Rick,

  Thanks you for the response.. the problem is as below.

  In update method
<update id="updateRequesterDetails" parameterType="requesterBean" >
        UPDATE CW_REQUESTORS
        SET
            NME_REQUESTOR_LAST = #{nmeRequestorLast,jdbcType=VARCHAR},           
            NME_REQUESTOR_FIRST = #{nmeRequestorFirst,jdbcType=VARCHAR},
            ADR_STREET1 = #{adrStreet1,jdbcType=VARCHAR},
            ADR_CITY = #{adrCity,jdbcType=VARCHAR},
            ADR_STATE = #{adrState,jdbcType=VARCHAR},
            ADR_COUNTRY = #{adrCountry,jdbcType=VARCHAR},
            ADR_ZIP5 = #{adrZip5,jdbcType=VARCHAR},   
            ADR_ZIP4 = #{adrZip4,jdbcType=VARCHAR},   
           
            <if test=" nmeRequestorFirm != ''">
                NME_REQUESTOR_FIRM = #{nmeRequestorFirm,jdbcType=VARCHAR},
            </if>
            <if test=" nmeRequestorMinit != ''">
                NME_REQUESTOR_MINIT = #{nmeRequestorMinit,jdbcType=VARCHAR},
            </if>
            <if test=" nmeSuffix != ''">
                NME_SUFFIX = #{nmeSuffix,jdbcType=VARCHAR},
            </if>
            <if test=" adrStreet2 != ''">
                ADR_STREET2 = #{adrStreet2,jdbcType=VARCHAR},
            </if>
            <if test=" emailAddress != ''">
                EMAIL = #{emailAddress,jdbcType=VARCHAR},
            </if>
            <if test="adrPhone1 != null">
                ADR_PHONE1 = #{adrPhone1,jdbcType=INTEGER},
            </if>
            <if test="adrPhone2 != null">
                ADR_PHONE2 = #{adrPhone2,jdbcType=INTEGER},
            </if>
            <if test="adrPhone3 != null">
                ADR_PHONE3 = #{adrPhone3,jdbcType=INTEGER},
            </if>           
                PHONE_EXT = #{phoneExt, javaType=Integer,jdbcType=BIGINT},
           
            UPDATE_BY = #{lastUpdatedBy,jdbcType=VARCHAR}
        WHERE IDN_REQUESTOR = #{idnRequestor}   
      </update>

for PHONE_EXT if i can give some no its working fine when i gave empty  its throwing the  exception (jdbcType i have tried with NUMBERIC AND BIGINT both are getting the problem.  Same problem for Phone 1, 2  3

root cause

java.sql.SQLException: Invalid column type: 1111

Thanks,
Srini

[Rick R]

On Mon, Nov 18, 2013 at 3:49 PM, srinivasulu atte <atte.s...@gmail.com> wrote:

 
for PHONE_EXT if i can give some no its working fine when i gave empty  its throwing the  exception (jdbcType i have tried with NUMBERIC AND BIGINT both are getting the problem.  Same problem for Phone 1, 2  3

I assume this was a typo "NUMBERIC" ? 

If not, be sure you used "NUMERIC"

What DB? And what is the actual DB datatype?

--
Rick R

[satte0528]

Hi,

private Integer adrPhone1;

ADR_PHONE1 = #{adrPhone1,jdbcType=NUMERIC},

In the above when i gave a number in adrPhone1 its working fine when i will
send null (empty) its throwing the below exception.
org.springframework.jdbc.UncategorizedSQLException: Error setting null

parameter. Most JDBC drivers require that the JdbcType must be specified
for all nullable parameters

root cause

java.sql.SQLException: Invalid column type: 1111

Thanks,
Srini



--
View this message in context: http://mybatis-user.963551.n3.nabble.com/Invalid-column-type-1111-tp1717787p4027480.html
Sent from the mybatis-user mailing list archive at Nabble.com.

[satte0528]

Rick,

its typo when i was typing in this but i gave right one still its getting
the same problem.

thanks,
Srini



--
View this message in context: http://mybatis-user.963551.n3.nabble.com/Invalid-column-type-1111-tp1717787p4027482.html

[satte0528]

Please find my update query i have set jdbctype for every field

ADR_PHONE1 = #{adrPhone1,jdbcType=NUMERIC},

</if>
<if test="adrPhone2 != null">

ADR_PHONE2 = #{adrPhone2,jdbcType=NUMERIC},

</if>
<if test="adrPhone3 != null">

ADR_PHONE3 = #{adrPhone3,jdbcType=NUMERIC},
</if>
PHONE_EXT = #{phoneExt, jdbcType=NUMERIC},

UPDATE_BY = #{lastUpdatedBy,jdbcType=VARCHAR}
WHERE IDN_REQUESTOR = #{idnRequestor}
</update>

Thanks,
Srini



--
View this message in context: http://mybatis-user.963551.n3.nabble.com/Invalid-column-type-1111-tp1717787p4027481.html

[satte0528]

Hi,

I am updating the table partially (some columns) , even i have
specified jdbc type still i am getting the java.sql.SQLException: Invalid
column type: 1111 Please help me to fix this issue.. where i have created
the new record its working fine bz i have given all the columns. here below
is the prepared log statement.

UPDATE CW_REQUESTORS SET
NME_REQUESTOR_LAST = ?,
NME_REQUESTOR_FIRST = ?,
ADR_STREET1 = ?,
ADR_CITY = ?,
ADR_STATE = ?,
ADR_COUNTRY = ?,
ADR_ZIP5 = ?,
ADR_ZIP4 = ?,
NME_REQUESTOR_FIRM = ?,
ADR_PHONE1 = ?,
ADR_PHONE2 = ?,
ADR_PHONE3 = ?,
PHONE_EXT = ?,
UPDATE_BY = ?
WHERE IDN_REQUESTOR = ?
John(String),
atte(String),
8978 street(String),
plainsboro(String),
1(String),
US(String),
(String),
(String),
main(String),
(String),
(String),
null,
(String),
ATTE(String),
4335(Integer)

Mybatis query

ADR_PHONE1 = #{adrPhone1,jdbcType=INTEGER},

</if>
<if test="adrPhone2 != null">

ADR_PHONE2 = #{adrPhone2,jdbcType=INTEGER},
</if>

ADR_PHONE3 = #{adrPhone3,jdbcType=INTEGER},

PHONE_EXT = #{phoneExt, jdbcType=VARCHAR},

UPDATE_BY = #{lastUpdatedBy,jdbcType=VARCHAR}
WHERE IDN_REQUESTOR = #{idnRequestor, jdbcType=INTEGER}



--
View this message in context: http://mybatis-user.963551.n3.nabble.com/Invalid-column-type-1111-tp1717787p4027484.html