I am trying to insert values into DB using jdbc:outbound-gateway, getting below error

Question

    <?xml version="1.0" encoding="UTF-8"?>
        <beans xmlns="http://www.springframework.org/schema/beans"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xmlns:int="http://www.springframework.org/schema/integration"
            xmlns:jpa="http://www.springframework.org/schema/data/jpa"
            xmlns:int-http="http://www.springframework.org/schema/integration/http"
            xmlns:task="http://www.springframework.org/schema/task"
            xmlns:int-jdbc="http://www.springframework.org/schema/integration/jdbc"
            xmlns:jdbc="http://www.springframework.org/schema/jdbc"
            xmlns:tx="http://www.springframework.org/schema/tx"
            xmlns:int-jms="http://www.springframework.org/schema/integration/jms"
            xsi:schemaLocation="http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
                                http://www.springframework.org/schema/integration https://www.springframework.org/schema/integration/spring-integration.xsd
                                http://www.springframework.org/schema/data/jpa https://www.springframework.org/schema/data/jpa/spring-jpa.xsd
                                http://www.springframework.org/schema/integration/http https://www.springframework.org/schema/integration/http/spring-integration-http.xsd
                                http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task.xsd
                                http://www.springframework.org/schema/jdbc https://www.springframework.org/schema/jdbc/spring-jdbc.xsd
                                http://www.springframework.org/schema/integration/jdbc https://www.springframework.org/schema/integration/jdbc/spring-integration-jdbc.xsd
                                http://www.springframework.org/schema/tx https://www.springframework.org/schema/tx/spring-tx.xsd
                                http://www.springframework.org/schema/integration/jms http://www.springframework.org/schema/integration/jms/spring-integration-jms.xsd">
        
            <import
                resource="classpath:integration/spring-integration-database-context.xml" />
        
            <!-- Channels -->
            <int:gateway id="requestGatewayIntegration"
                service-interface="ca.bell.bmf.customer.gateway.CustomerProfileGateway"
                default-request-channel="objectToJsonChannel"
                error-channel="errorChannel11" />
        
            <int:channel id="objectToJsonChannel">
                <int:interceptors>
                    <bean class="ca.bell.bmf.customer.service.MyChannelInterceptor" />
                </int:interceptors>
        
            </int:channel>
            
            
            <int-jdbc:outbound-gateway
            update="Insert into INT_CHANNEL_MESSAGE (MESSAGE_ID, GROUP_KEY,CREATED_DATE,MESSAGE_PRIORITY,MESSAGE_SEQUENCE,REGION) 
            values(:headers[id],:headers[id],'1211','2','3','Test')"
            request-channel="objectToJsonChannelTMF639" reply-channel="responseobjectToJsonChannelTMF639" data-source="dataSource" />   
        
        </beans>

if I gave hardcoded values(below) it is working as expected inserting data

<int-jdbc:outbound-gateway update="Insert into INT_CHANNEL_MESSAGE (MESSAGE_ID, GROUP_KEY,CREATED_DATE,MESSAGE_PRIORITY,MESSAGE_SEQUENCE,REGION) values('242452','','1211','2','3','Test')" request-channel="objectToJsonChannelTMF639" reply-channel="responseobjectToJsonChannelTMF639" data-source="dataSource" />

when I gave :headers[id] and :headers.id it's not working and giving below error.

from source: ''int-jdbc:outbound-gateway''' while handling 'GenericMessage [payload=CPMPayload ': error occurred in message handler [bean 'org.springframework.integration.jdbc.JdbcOutboundGateway#0'; defined in: 'URL [file:/C:/Users/19.3/Git/profile/process/target/classes/integration/Profile-context.xml]'; from source: ''int-jdbc:outbound-gateway'']; nested exception is org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [Insert into INT_CHANNEL_MESSAGE (MESSAGE_ID, GROUP_KEY,CREATED_DATE,MESSAGE_PRIORITY,MESSAGE_SEQUENCE,REGION)      values(?,?,'1211','2','3','Test')]; SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type. Failing over to the next subscriber.

please help me to access header values in jdbc:outbound-gateway and also help on how can we insert blob data(payload) into DB though jdbc:outbound-gateway.

Answer 1

It would be great if you share more stack trace for that error. It is also not clear why would one use a framework table outside of the framework components? See JdbcChannelMessageStore and its supporting ChannelMessageStorePreparedStatementSetter to understand what is wrong with just your :headers[id] and we do handle BLOBs.

The JdbcMessageHandler, used internally in the JdbcOutboundGateway for inserts, does not support BLOBs for now.