Announcement Announcement Module
Collapse
No announcement yet.
int-jdbc:stored-proc-outbound-gateway and communication between two stored procedures Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • int-jdbc:stored-proc-outbound-gateway and communication between two stored procedures

    How to make spring integration piece of code where communication will be between two stored procedures , where source stored procedure will be having two out parameters , one is list of objects and another one is having error code. If error code empty then target stored procedure will be invoked by the list of objects from source stored procedure, otherwise if anything there in error code of source stored procedure flow should be terminated at that point.

  • #2
    I think you should district stored procedure call knowlages from messaging.
    Actually, for messaging it doesn't matter where message comes from.
    So,
    1. the first stored procedure returns some data.
    2. you convert that to message and send further
    3. here by message payload you determine what to do
    4. the filter might decide to discard it, because the "error" fied of payload contains some flag
    5. if not, your message might be sended to the next channel, where the hadler is with the second stored procedure

    Can you explain where is your issue? Everything looks good and valid case for Messaging.
    And what is interest both procedures might be invoked within the same transaction.

    Comment


    • #3
      <int-jdbc:stored-proc-outbound-gateway data-source="dataSource"
      id="stored.proc.id"
      request-channel="request.channel"
      stored-procedure-name="APP.TEST_PACK.GET_PARTY_DATA"
      ignore-column-meta-data="true"
      skip-undeclared-results="true">
      <int-jdbcarameter name="party_details_tbl"/>

      <int-jdbc:returning-resultset name="party_details_tbl" row-mapper="com.test.mapper.EntityMapper"/>

      </int-jdbc:stored-proc-outbound-gateway>

      Am using the above statement to invoke the stored procedure where only one out parameter is available in stored procedure this out parameter is providing List of objects and I want to take it to EntityMapper, But getting the exception as :

      CallableStatementCallback; bad SQL grammar [{call APP.TEST_PACK.GET_PARTY_DATA(?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
      PLS-00306: wrong number or types of arguments in call to 'GET_PARTY_DATA'

      But I am able to invoke the storedprocedure using oracle commands and getting successful results,
      Need help on how to register the out parameters from stored procedure in spring integration tags.

      Thanks

      Comment


      • #4
        There are no stops to register several OUT parameters. How does your procedure signature look?

        Comment


        • #5
          PROCEDURE GET_PARTY_DATA ( party_details_tbl OUT PARTY_DETAILS_OBJ_TBL, x_error_code OUT VARCHAR2, x_error_msg OUT VARCHAR2);
          Above is the actual stored procedure to be invoked from integration piece of code, where as I edited to PROCEDURE GET_PARTY_DATA ( party_details_tbl OUT PARTY_DETAILS_OBJ_TBL) for doing poc.

          Please help me providing sample codes to check and understand the spring integration code for the same type.

          Comment


          • #6
            Well, you just should add all parameters to procedure declaration:
            HTML Code:
            <int-jdbc:stored-proc-outbound-gateway data-source="dataSource"
                       id="stored.proc.id"
                       request-channel="request.channel"
                       stored-procedure-name="APP.TEST_PACK.GET_PARTY_DATA"
                       ignore-column-meta-data="true"
                       skip-undeclared-results="true">
            
                   <int-jdbc:sql-parameter-definition name="party_details_tbl" type="#{T(oracle.jdbc.OracleTypes).CURSOR}" direction="OUT"/>
                   <int-jdbc:sql-parameter-definition name="x_error_code" direction="OUT"/>
                   <int-jdbc:sql-parameter-definition name="x_error_msg" direction="OUT"/>
            
                   <int-jdbc:returning-resultset name="party_details_tbl" row-mapper="com.test.mapper.EntityMapper"/>
            
            </int-jdbc:stored-proc-outbound-gateway>

            Comment


            • #7
              Hi Artem Bilan


              I am struggling with spring integration issue. Please help me in this.

              This is my processor xml ~<?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"
              xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schem...ring-beans.xsd
              http://www.springframework.org/schema/integration http://www.springframework.org/schema/integration/spring-integration-4.0.xsd">
              <int:channel id="input"/>
              <int:channel id="output"/>
              <int:service-activator input-channel="input" ref="lineSplitterService" method="onMessage"/>
              <bean id="lineSplitterService" class="com.bigdata.LineSplitterService">
              <constructor-arg index="0" ref="output"/>
              </bean>
              </beans>


              This is my sink xml mapSink.xml
              ~<?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:context="http://www.springframework.org/schema/context"
              xmlns:int="http://www.springframework.org/schema/integration"
              xmlns:int-jdbc="http://www.springframework.org/schema/integration/jdbc"
              xmlns:task="http://www.springframework.org/schema/task"
              xsi:schemaLocation="http://www.springframework.org/schema/integration http://www.springframework.org/schem...ntegration.xsd
              http://www.springframework.org/schema/task http://www.springframework.org/schem...pring-task.xsd
              http://www.springframework.org/schema/integration/jdbc http://www.springframework.org/schem...ation-jdbc.xsd
              http://www.springframework.org/schema/beans http://www.springframework.org/schem...ring-beans.xsd
              http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
              <int:channel id="input"></int:channel>
              <int:channel id="mapChannel"></int:channel>
              <int:transformer ref="csv2mapTransformer" input-channel="input" output-channel="mapChannel" />
              <bean id="csv2mapTransformer" class="com.bigdata.map.CSV2MapTransformer">
              <property name="columns" value="eventId,trainId,readingTime,routeId,latitud e,longitude,eventType,axel,component,side,value" />
              </bean>
              <int-jdbc:outbound-channel-adapter
              data-source="dataSource"
              channel="mapChannel"
              query="put into test1(Railroad,RoadNumber,EventId,TrainId,ReadingT ime,RouteId,Latitude,Longitude,EventType,Axel,Comp onent,Side,Value) values(ayload[Railroad], ayload[RoadNumber], ayload[EventId],ayload[TrainId], ayload[ReadingTime],ayload[RouteId], ayload[Latitude], ayload[Longitude], ayload[EventType], ayload[Axel], ayload[Component], ayload[Side], ayload[Value])"
              ></int-jdbc:outbound-channel-adapter>
              <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
              destroy-method="close">
              <property name="driverClassName" value="com.vmware.sqlfire.jdbc.ClientDriver" />
              <property name="url" value="jdbc:sqlfire://192.168.206.145:1527" />
              <property name="username" value="APP" />
              <property name="password" value="APP" />
              </bean>
              </beans>


              ~~This is my Stream
              stream create --name stream1 --definition "file --dir=/tmp/xd/input/RMD/ --ref=true| lineSplitter | mapSink"

              This is my processor file
              import java.io.File;
              import java.io.FileNotFoundException;
              import java.util.Scanner;
              import org.slf4j.Logger;
              import org.slf4j.LoggerFactory;
              import org.springframework.integration.annotation.Service Activator;
              import org.springframework.integration.support.MessageBui lder;
              import org.springframework.messaging.Message;
              import org.springframework.messaging.MessageChannel;

              public class LineSplitterService {
              private MessageChannel output;

              public LineSplitterService(MessageChannel output){
              this.output = output;
              }
              private Logger logger = LoggerFactory.getLogger(LineSplitterService.class) ;
              public void onMessage(Message<?> message){

              File file = getFile(message);
              Scanner scanner = null;
              try {
              scanner = new Scanner(file);
              scanner.useDelimiter(\\n);
              int count = 0;
              while(scanner.hasNext()){
              Message<?> outmsg = MessageBuilder.withPayload(scanner.next()).setCorr elationId(count).build();
              output.send(outmsg);
              count++;
              }
              } catch (FileNotFoundException e) {
              e.printStackTrace();
              } finally{
              if(scanner != null)
              scanner.close();
              }
              }


              private File getFile(Message<?> message){
              File f = null;
              System.out.println(String.format("####### Message payload: %s %s",message.getPayload().getClass(),message.getPay load()));
              if(File.class.isAssignableFrom(message.getPayload( ).getClass())){
              f = (File)message.getPayload();
              System.out.println("inside the File.class..."+f);
              }
              else if(String.class.isAssignableFrom(message.getPayloa d().getClass())){
              f = new File((String)message.getPayload());
              System.out.println("inside the String.class..."+f);
              }
              return f;
              }
              }
              The above program using below jar files. I export as jar from eclipse and put in the /xd/modules/processors/scanSplitter/lib/below 2 jars and in config/xml file.
              spring-messaging-4.0.3.RELEASE, spring-integration-core-4.0.0.RELEASE jar files. It is working good. It is using Message Builder from Spring Integration core 4.0.0. jar. It is sending messages to the next level.
              If the sink is a file, it is clearly able to deliver the messages. If it is database(ex:GemFire XD). It is not able to deliver the messages.
              Here is my sink configuration
              import java.util.HashMap;
              import java.util.Map;
              import org.springframework.integration.support.MessageBui lder;
              import org.springframework.integration.transformer.Abstra ctTransformer;
              import org.springframework.messaging.Message;
              import org.springframework.stereotype.Component;
              @Component
              public class CSV2MapTransformer extends AbstractTransformer{
              private String columns;
              private String delimiter = "\\s+";

              @Override
              protected Object doTransform(Message<?> message) throws Exception {

              String[] payload = message.getPayload().toString().split(delimiter);

              String[] columnMap = null;
              if(this.columns != null && !this.columns.isEmpty()){
              columnMap = this.columns.split(",");
              }
              else{
              Object columnHeaders = message.getHeaders().get("columnNames");
              if(columnHeaders != null && columnHeaders.getClass().isAssignableFrom(String.c lass)){
              columnMap = String.valueOf(columnHeaders).split(",");
              }else{
              columnMap = new String[payload.length];
              for(int i=0;i<payload.length;i++){
              columnMap[i] = "column_"+i;
              }
              }
              }

              Map<String,String> map = new HashMap<String, String>();
              for(int i=0;i<payload.length;i++){
              map.put(columnMap[i], payload[i]);
              }
              return MessageBuilder.withPayload(map).copyHeaders(messag e.getHeaders()).build();
              }
              public void setColumns(String columns){
              this.columns = columns;
              }
              public void setDelimiter(String delimiter){
              this.delimiter = delimiter;
              }
              }
              If I dont use /xd/modules/sink//mapSink/lib/spring-integration-jdbc-2.2.3.release jar file, it is talking abu the
              Line 27 in XML document from URL [file:/home/gpadmin/spring-xd-1.0.0.M5/xd/modules/sink/mapSink/config/mapSink.xml] is invalid; nested exception is org.xml.sax.SAXParseException: cvc-complex-type.2.4.c: The matching wildcard is strict, but no declaration can be found for element 'int-jdbc:outbound-channel-adapter'.
              If I use spring-integration-jdbc-2.2.3.release jar , it is talking about
              13:37:33,230 ERROR http-nio-8090-exec-1 web.BasicErrorController:108 - java.lang.NoClassDefFoundError: org/springframework/integration/Message. It is expecting messages from the Spring Integration.

              Please do the needful. I am really in urgent delivery.

              Thanks,
              -Suyodha










              Comment


              • Artem Bilan
                Artem Bilan commented
                Editing a comment
                Please, move your question to the http://stackoverflow.com under "spring-xd" and I'll answer there.
                Don't use existing topics to ask new question.

                Best regards
            Working...
            X