ParamOrder attribute does not have same number of columns as needed in the given sql prepared statement.

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

ParamOrder attribute does not have same number of columns as needed in the given sql prepared statement.

jrautner
This post has NOT been accepted by the mailing list yet.
Hello,

i d like to have one question. Is there any difference between sql parameters in select and insert prepared statements? Insert statement with parameters work for me but when i try select some values i get this error.

2017-04-18T16:28:52.432+0200 WARNING [com.sun.jbi.engine.bpel.core.bpel.model.runtime.impl.BPELProcessInstanceImpl] (sun-bpel-engine-thread-4) BPCOR-6151: The process instance has been terminated because a fault was not handled; Fault Name is {http://www.sun.com/wsbpel/2.0/process/executable/SUNExtension/ErrorHandling}systemFault; Fault Data is <?xml version="1.0" encoding="UTF-8"?><jbi:message xmlns:sxeh="http://www.sun.com/wsbpel/2.0/process/executable/SUNExtension/ErrorHandling" type="sxeh:faultMessage" version="1.0" xmlns:jbi="http://java.sun.com/xml/ns/jbi/wsdl-11-wrapper"><jbi:part>Failed to denormalize message. 87016531203893-61562-137118185321990226 .</jbi:part></jbi:message>
com.sun.jbi.engine.bpel.core.bpel.exception.SystemException: BPCOR-6131: An Error status was received while doing an invoke (partnerLink=PartnerLink1, portType={http://j2ee.netbeans.org/wsdl/PlanApprovalProcess/wsdl/newWSDL}jdbcPortType, operation=selectApprovalUsers_Operation)
BPCOR-6129: Line Number is 64
BPCOR-6130: Activity Name is getPlanApprovalUsers
        at com.sun.jbi.engine.bpel.core.bpel.model.runtime.impl.InvokeUnitImpl.processStatus(InvokeUnitImpl.java:969)
        at com.sun.jbi.engine.bpel.core.bpel.model.runtime.impl.InvokeUnitImpl.process(InvokeUnitImpl.java:566)
        at com.sun.jbi.engine.bpel.core.bpel.model.runtime.impl.InvokeUnitImpl.doAction(InvokeUnitImpl.java:185)
        at com.sun.jbi.engine.bpel.core.bpel.engine.impl.BPELInterpreter.execute(BPELInterpreter.java:163)
        at com.sun.jbi.engine.bpel.core.bpel.engine.BusinessProcessInstanceThread.execute(BusinessProcessInstanceThread.java:98)
        at com.sun.jbi.engine.bpel.core.bpel.engine.impl.BPELProcessManagerImpl.process(BPELProcessManagerImpl.java:1088)
        at com.sun.jbi.engine.bpel.core.bpel.engine.impl.EngineImpl.process(EngineImpl.java:280)
        at com.sun.jbi.engine.bpel.core.bpel.engine.impl.EngineImpl.process(EngineImpl.java:1338)
        at com.sun.jbi.engine.bpel.BPELSEInOutThread.processStatus(BPELSEInOutThread.java:384)
        at com.sun.jbi.engine.bpel.BPELSEInOutThread.processMsgEx(BPELSEInOutThread.java:241)
        at com.sun.jbi.engine.bpel.BPELSEInOutThread.run(BPELSEInOutThread.java:193)
Caused by: java.lang.Exception: Failed to denormalize message. 87016531203893-61562-137118185321990226 .
        at org.glassfish.openesb.databasebc.OutboundMessageProcessor.processInOut(OutboundMessageProcessor.java:647)
        at org.glassfish.openesb.databasebc.OutboundMessageProcessor.execute(OutboundMessageProcessor.java:273)
        at org.glassfish.openesb.databasebc.OutboundMessageProcessor.run(OutboundMessageProcessor.java:153)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
Caused by: javax.jbi.messaging.MessagingException: Error occured while executing SQL.SELECT DISTINCT USER_ID FROM SOAPMS_PLANS.PLAN_PARTICIPANT_ROLE WHERE PLAN_ID=? AND PLAN_VERSION_ID=? AND PT_ROLE_SNAME IN ('Tvorca plánu', 'Schvaľovateľ', 'Zodpovedný za schválenie plánu', 'Spracovateľ', 'Hlavný spracovateľ', 'Pripomienkujúci') Reason: Unable to convert normalized message content to prepared statement, Error occured during populating PreparedStatement.Reason: Unable to convert normalized message to prepared sql statement.
 input ParamOrder attribute does not have same number of columns as needed in the given sql prepared statement.
        at org.glassfish.openesb.databasebc.OutboundMessageProcessor.executeOutboundSQLSelect(OutboundMessageProcessor.java:1824)
        at org.glassfish.openesb.databasebc.OutboundMessageProcessor.processInOut(OutboundMessageProcessor.java:616)
        ... 5 more
Caused by: javax.jbi.messaging.MessagingException: Unable to convert normalized message content to prepared statement, Error occured during populating PreparedStatement.Reason: Unable to convert normalized message to prepared sql statement.
 input ParamOrder attribute does not have same number of columns as needed in the given sql prepared statement.
        at org.glassfish.openesb.databasebc.JDBCDenormalizer.denormalizeOutbound(JDBCDenormalizer.java:135)
        at org.glassfish.openesb.databasebc.OutboundMessageProcessor.executeOutboundSQLSelect(OutboundMessageProcessor.java:1807)
        ... 6 more
Caused by: javax.jbi.messaging.MessagingException: Unable to convert normalized message to prepared sql statement.
 input ParamOrder attribute does not have same number of columns as needed in the given sql prepared statement.
        at org.glassfish.openesb.databasebc.JDBCDenormalizer.populatePreparedStatement(JDBCDenormalizer.java:617)
        at org.glassfish.openesb.databasebc.JDBCDenormalizer.denormalizeOutbound(JDBCDenormalizer.java:123)
        ... 7 more

Input paramOrder attribute does not have same number of columns as needed in the given sql.

I'm using oracle database. Same select with values without params work correct.

I will appreciate any help thank you so much. I can provide wsdl file of db definition if its needed.

Thank you
Have a nice day.

Greatings Jakub Rautner
Reply | Threaded
Open this post in threaded view
|

Re: ParamOrder attribute does not have same number of columns as needed in the given sql prepared statement.

Paul Perez
Administrator
Hello Jakub

"Input paramOrder attribute does not have same number of columns as needed in the given sql"
The parameter order defines the mapping between the elements from your input message and the parameters of The SQL Request. Let's take an example:
My input message is :
<person> 
   <name>perez</name>
   <firstName>paul</firstName> 
   <city>london</city>
   <company>pymma</company>
</person>

My Request is
select * from people where organisation=? and lastName=?;

You have to detail the way you map the elements from your input message and SQL Request since nowhere it is written that the fist parameter is the company and the second name.
To do it you add in the parameter order that information:
"company,name".
Then the database Component will associate organisation with company and name with lastName.
AFAIK, if you assign the right parameters, the error must disappear

Please let me informed

Paul
www.pymma.com The best services on OpenESB
Reply | Threaded
Open this post in threaded view
|

Re: ParamOrder attribute does not have same number of columns as needed in the given sql prepared statement.

jrautner
This post has NOT been accepted by the mailing list yet.
Thank you Paul,

here is xsd schema of the request:
<xsd:element name="select_no_param_Request">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element name="plan_id">
                    <xsd:complexType>
                        <xsd:simpleContent>
                            <xsd:extension base="xsd:decimal">
                                <xsd:attribute name="isNull" type="xsd:boolean"></xsd:attribute>
                                <xsd:attribute name="isDefaultColumn" type="xsd:boolean"></xsd:attribute>
                            </xsd:extension>
                        </xsd:simpleContent>
                    </xsd:complexType>
                </xsd:element>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>

here is jdbc input from concrete wsdl:
<jdbc:input xmlns:jdbc="http://schemas.sun.com/jbi/wsdl-extensions/jdbc/" MarkColumnName="" MarkColumnValue="" MoveRowToTableName="" PKName="" PollMilliSeconds="5000" PollingPostProcessing="MoveRow" TableName="select_no_param" Transaction="NOTransaction" numberOfRecords="" operationType="select_no_param_Operation" <b>paramOrder="plan_id" sql="select distinct user_id from soapms_plans.plan_participant_role where plan_id=?"/>

is there any other way to define parameters? I'm confused because i used same way with insert statement and it works fine.

Thank you for your time
Jakub
Reply | Threaded
Open this post in threaded view
|

Re: ParamOrder attribute does not have same number of columns as needed in the given sql prepared statement.

jrautner
This post has NOT been accepted by the mailing list yet.
In reply to this post by Paul Perez
Hallo Paul,

thank you for your answer. As your colleague David said. It looks like the problem was caused by ojdbc7 driver. When i tried it on ojdbc6 it works fine.

Thank you for your time
Have a nice day.

Jakub
Reply | Threaded
Open this post in threaded view
|

Re: ParamOrder attribute does not have same number of columns as needed in the given sql prepared statement.

Paul Perez
Administrator
Hi Jakub,

This is a very good news. To be complete on that issue, the bug does not come from OpenESB component but from the Oracle driver, When we run the same request on a pure Java program, we got the same error. Few months ago,
a fix has been asked to Oracle but we don't get a feedback from the Oracle customer that made the request yet
www.pymma.com The best services on OpenESB