home list archives users mailing list
 

Subject: Re: Whats wrong with this code? (SQLParameterisedQuery)

Date view Thread view Subject view Author view Attachment view

From: Ally Hume (a.hume@epcc.ed.ac.uk)
Date: Nov 28, 2007 10:26

Hi Mathias,

You are right. The output from SQLParameterisedQuery cannot be
directly into into TupleToWebRowSetCharArrays. I will explain why:

TupleToWebRowSetCharArrays expects list of tuples with the first
object in the list being the metadata object, e.g.
 LIST_BEGIN, MetadataWrapper(TupleMetadata), Tuple, Tuple, ... , LIST_END

The LIST_BEGIN and LIST_END markers are used to identify a the set of
tuples that came from a single query. If you passed two queries to
the SQLQuery activity for example then it would produce a stream of
output data that contains two sets of result tuples and the list
markers are used to seperate them, for example the data stream would
be:
 LIST_BEGIN, MetadataWrapper(TupleMetadata), Tuple, Tuple, ... ,
 LIST_END, LIST_BEGIN, MetadataWrapper(TupleMetadata), Tuple, Tuple, ... ,
 LIST_END.
This data stream can be handled by TupleToWebRowSetCharArrays, it will
produce to XML web row sets, again using the LIST_BEGIN and LIST_END
markers to seperate them.

So we now come back to SQLParameterisedQuery. The output of this is
slighly different from SQLQuery because we have a extra layer of
grouping in the output. The output from SQLParameterisedQuery is a
*list of lists of tuples* rather than a list of tuples. For example,
if the expression input of SQLParameterisedQuery has two expressions:
  "select * from tableA where id < ?"
  "select * from tableB where id < ?"
and there are two corresponding parameter lists:
  LIST_BEGIN MetadataWrapper(TupleMetadata) Tuple(1), Tuple(2) LIST_END
  LIST_BEGIN MetadataWrapper(TupleMetadata) Tuple(3), Tuple(4) LIST_END

This will lead to four SQL queries being executed:
  "select * from tableA where id < 1"
  "select * from tableA where id < 2"
  "select * from tableB where id < 3"
  "select * from tableB where id < 4"

To distinguish which result sets come from the first query and which
from the second we add an extra layer of lists. So the output stream
would be:
LIST_BEGIN,
   LIST_BEGIN MetadataWrapper(TupleMetadata), Tuple, Tuple, ... , LIST_END,
   LIST_BEGIN MetadataWrapper(TupleMetadata), Tuple, Tuple, ... , LIST_END,
LIST_END,
LIST_BEGIN,
   LIST_BEGIN MetadataWrapper(TupleMetadata), Tuple, Tuple, ... , LIST_END,
   LIST_BEGIN MetadataWrapper(TupleMetadata), Tuple, Tuple, ... , LIST_END,
LIST_END

The indentation is only included to make it more readable, this is
just a stream of java objects inside OGSA-DAI.

The TupleToWebRowSetCharArrays activity cannot code with this extra
layer of list markers. The simplest thing for you do in your example
is to simply throw them away! This can be done by adding a ListRemove
activity which will remove one layer of list markers. The following
code works for me:

    public void testSupportIssue() throws Exception
    {
        ResourceID relationalResourceID =
            mSystemTestConfig.getRelationalResourceID();
        // = connection.getResourceId();

        DataRequestExecutionResource drer = mDRER;
        // = connection.getResourceInfo().getDrer()

        CSVToTuple csvtuple = new CSVToTuple();
        csvtuple.addData(new StringReader("3\n"));

        SQLParameterisedQuery parQuery = new SQLParameterisedQuery();
        parQuery.addExpression("select * from littleblackbook where id < ?");
        parQuery.setResourceID(relationalResourceID);
        parQuery.connectParametersInput(csvtuple.getResultOutput());

        ListRemove listRemove = new ListRemove();
        listRemove.connectInput(parQuery.getDataOutput());

        TupleToWebRowSetCharArrays transform = new TupleToWebRowSetCharArrays();
        transform.connectDataInput(listRemove.getOutput());

        DeliverToRequestStatus delivery = new DeliverToRequestStatus();
        delivery.connectInput(transform.getResultOutput());

        PipelineWorkflow workflow = new PipelineWorkflow();
        workflow.add(csvtuple);
        workflow.add(parQuery);
        workflow.add(listRemove);
        workflow.add(transform);
        workflow.add(delivery);

        // You actually don't need this line because the transform is connected
        // directly to the DeliverToRequestStatusActivity, you only need this
        // if there is another activity or activities between them. If they
        // are connected directly the client toolkit can work it out.
        transform.getResultOutput().setDeliverToRequestStatusActivity(
            delivery);

        try {
            RequestResource rr =
                drer.execute(workflow,RequestExecutionType.SYNCHRONOUS);
            System.out.println(rr.getRequestStatus());

            ResultSet rs = transform.nextResultAsResultSet();

            System.out.println("Code to process the result set goes here");

        } catch (RequestExecutionException e) {
            RequestStatus requestStatus =
                e.getRequestResource().getRequestStatus();
            System.out.println("Error during execution. Request status is: ");
            System.out.println(requestStatus);
            e.printStackTrace();
        }

    }

I hope this helps you. Do you understand what is happening? It would
be good to know if my explaination is clear enough because I will add
some text explaining this to the online documentation of the
SQLParameterisedQuery activity. Is the above text good enough?

Regards,

Ally

On 27/11/2007, Mathias Brito <mathiasbrito@gmail.com> wrote:
> I'm having problems trying to execute the following code... i paste
> the messages that i intercepted with tcpmon! I cannot understand why i
> get this error, i guess that the output of SQLParameterisedQuery
> cannot be piped to a TupleToWebRowSetCharArrays!! i'm right!?
>
> CSVToTuple csvtuple = new CSVToTuple();
> for (int i = 0; i < parameters.size(); i++) {
> try {
> csvtuple.addData((CharArrayReader) parameters.get(i));
> } catch (IOException e) {
> throw new SQLException(
> "Error processing PreparedStatement, parameters.");
> }
> }
>
> SQLParameterisedQuery parQuery = new SQLParameterisedQuery();
> parQuery.addExpression(query);
> parQuery.setResourceID(connection.getResourceId());
> parQuery.connectParametersInput(csvtuple.getResultOutput());
>
> TupleToWebRowSetCharArrays transform = new TupleToWebRowSetCharArrays();
> transform.connectDataInput(parQuery.getDataOutput());
>
> DeliverToRequestStatus delivery = new DeliverToRequestStatus();
> delivery.connectInput(transform.getResultOutput());
>
> PipelineWorkflow workflow = new PipelineWorkflow();
> workflow.add(csvtuple);
> workflow.add(parQuery);
> workflow.add(transform);
> workflow.add(delivery);
>
> try {
> rr = connection.getResourceInfo().getDrer().execute(workflow,
> RequestExecutionType.SYNCHRONOUS);
>
> transform.getResultOutput().setDeliverToRequestStatusActivity(
> delivery);
>
> return transform.nextResultAsResultSet();
>
> } catch (Exception e) {
> e.printStackTrace();
> }
>
>
> >>>>>>>>>>>>>> REQUEST <<<<<<<<<<<<<<<<<<<<
> <?xml version="1.0" encoding="UTF-8"?>
> <soapenv:Envelope
> xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/03/addressing">
> <soapenv:Header>
> <wsa:MessageID
> soapenv:mustUnderstand="0">uuid:d0b23410-9d26-11dc-ab3d-ca00cb6e6dc9</wsa:MessageID>
> <wsa:To
> soapenv:mustUnderstand="0">http://localhost:8080/dai/services/DataRequestExecutionService>
> <wsa:Action
> soapenv:mustUnderstand="0">http://ogsadai.org.uk/namespaces/2007/04/service/execution/AxisDataRequestExecutionServicePortType/executeRequest>
> <wsa:From soapenv:mustUnderstand="0">
> <wsa:Address>http://schemas.xmlsoap.org/ws/2004/03/addressing/role/anonymous>
> </wsa:From>
> <ns1:ResourceID xmlns:ns1="http://ogsadai.org.uk"
> soapenv:mustUnderstand="0">DataRequestExecutionResource</ns1:ResourceID>
> </soapenv:Header>
> <soapenv:Body>
> <execute
> xmlns="http://ogsadai.org.uk/namespaces/2007/04/service/execution">
> <ns1:request
> xmlns:ns1="http://ogsadai.org.uk/namespaces/2007/04/types">
> <ns1:workflow>
> <ns1:pipeline>
> <ns1:activity
> instanceName="uk.org.ogsadai.CSVToTuple-ogsadai-11682c88973"
> name="uk.org.ogsadai.CSVToTuple">
> <ns1:inputs>
> <ns1:input name="data">
> <ns1:inputLiteral>
> <ns1:listBegin/>
> </ns1:inputLiteral>
> <ns1:inputLiteral>
> <ns1:charArray>
> <![CDATA[3]]></ns1:charArray>
> </ns1:inputLiteral>
> <ns1:inputLiteral>
> <ns1:listEnd/>
> </ns1:inputLiteral>
> </ns1:input>
> </ns1:inputs>
> <ns1:outputs>
> <ns1:outputStream name="result"
> pipe="ogsadai-11682c88974"/>
> </ns1:outputs>
> </ns1:activity>
> <ns1:activity
> instanceName="uk.org.ogsadai.SQLParameterisedQuery-ogsadai-11682c88975"
> name="uk.org.ogsadai.SQLParameterisedQuery" resource="LBB_WN00">
> <ns1:inputs>
> <ns1:input name="expression">
> <ns1:inputLiteral>
> <ns1:string>
> <![CDATA[SELECT * FROM
> littleblackbook WHERE id
> <?;]]></ns1:string>
> </ns1:inputLiteral>
> </ns1:input>
> <ns1:input name="parameters">
> <ns1:inputStream
> pipe="ogsadai-11682c88974"/>
> </ns1:input>
> </ns1:inputs>
> <ns1:outputs>
> <ns1:outputStream name="data"
> pipe="ogsadai-11682c88976"/>
> </ns1:outputs>
> </ns1:activity>
> <ns1:activity
> instanceName="uk.org.ogsadai.TupleToWebRowSetCharArrays-ogsadai-11682c88977"
> name="uk.org.ogsadai.TupleToWebRowSetCharArrays">
> <ns1:inputs>
> <ns1:input name="data">
> <ns1:inputStream
> pipe="ogsadai-11682c88976"/>
> </ns1:input>
> </ns1:inputs>
> <ns1:outputs>
> <ns1:outputStream name="result"
> pipe="ogsadai-11682c88978"/>
> </ns1:outputs>
> </ns1:activity>
> <ns1:activity
> instanceName="uk.org.ogsadai.DeliverToRequestStatus-ogsadai-11682c88979"
> name="uk.org.ogsadai.DeliverToRequestStatus">
> <ns1:inputs>
> <ns1:input name="input">
> <ns1:inputStream
> pipe="ogsadai-11682c88978"/>
> </ns1:input>
> </ns1:inputs>
> <ns1:outputs/>
> </ns1:activity>
> </ns1:pipeline>
> </ns1:workflow>
> </ns1:request>
> <session>
> <sessionID></sessionID>
> <createSession>false</createSession>
> </session>
> <isSynchronous>true</isSynchronous>
> </execute>
> </soapenv:Body>
> </soapenv:Envelope>
> <<<<<<<<<<<<<<<< END OF REQUEST >>>>>>>>>>>>>>>>>>>>>>>>>
>
> >>>>>>>>>>>>>>>>>>>> RESPONSE <<<<<<<<<<<<<<<<<<<<<<<<<<
> <?xml version="1.0" encoding="utf-8"?>
> <soapenv:Envelope
> xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/03/addressing">
> <soapenv:Header>
> <wsa:MessageID
> soapenv:mustUnderstand="0">uuid:d0cb1340-9d26-11dc-97f4-81ba1c1aecc6</wsa:MessageID>
> <wsa:To
> soapenv:mustUnderstand="0">http://schemas.xmlsoap.org/ws/2004/03/addressing/role/anonymous>
> <wsa:Action
> soapenv:mustUnderstand="0">http://ogsadai.org.uk/namespaces/2007/04/service/execution/AxisDataRequestExecutionServicePortType/executeRequestResponse>
> <wsa:From soapenv:mustUnderstand="0">
> <wsa:Address>http://localhost:8080/dai/services/DataRequestExecutionService>
> </wsa:From>
> <wsa:RelatesTo RelationshipType="wsa:Reply"
> soapenv:mustUnderstand="0">uuid:d0b23410-9d26-11dc-ab3d-ca00cb6e6dc9</wsa:RelatesTo>
> </soapenv:Header>
> <soapenv:Body>
> <executeResponse
> xmlns="http://ogsadai.org.uk/namespaces/2007/04/service/execution">
> <ns1:requestStatus
> xmlns:ns1="http://ogsadai.org.uk/namespaces/2007/04/types">
> <ns1:requestDetails id="ogsadai-11682c89115"
> status="COMPLETED_WITH_ERROR"/>
> <ns1:activity
> instanceName="uk.org.ogsadai.TupleToWebRowSetCharArrays-ogsadai-11682c88977"
> status="ERROR">
> <ns1:error>
> <ns1:errorCause>
>
> <ns1:id>uk.org.ogsadai.INVALID_INPUT_TYPE_EXCEPTION</ns1:id>
> <ns1:msg>An invalid type of input value has
> been encountered on the input named data. The input was expected to be
> of type uk.org.ogsadai.metadata.MetadataWrapper but was of type
> uk.org.ogsadai.activity.io.ControlBlock.</ns1:msg>
> <ns1:parameter>data</ns1:parameter>
>
> <ns1:parameter>uk.org.ogsadai.metadata.MetadataWrapper</ns1:parameter>
>
> <ns1:parameter>uk.org.ogsadai.activity.io.ControlBlock</ns1:parameter>
> </ns1:errorCause>
> </ns1:error>
> </ns1:activity>
> <ns1:activity
> instanceName="uk.org.ogsadai.CSVToTuple-ogsadai-11682c88973"
> status="COMPLETED"/>
> <ns1:activity
> instanceName="uk.org.ogsadai.SQLParameterisedQuery-ogsadai-11682c88975"
> status="COMPLETED"/>
> <ns1:activity
> instanceName="uk.org.ogsadai.DeliverToRequestStatus-ogsadai-11682c88979"
> status="ERROR">
> <ns1:error>
> <ns1:errorCause>
>
> <ns1:id>uk.org.ogsadai.PIPE_CLOSED_DUE_TO_PRODUCER_ERROR</ns1:id>
> <ns1:msg>The pipe has been closed due to an
> error that occurred at the data producer.</ns1:msg>
> </ns1:errorCause>
> </ns1:error>
> </ns1:activity>
> <ns1:result
> activityInstanceName="uk.org.ogsadai.DeliverToRequestStatus-ogsadai-11682c88979"
> resultName="result">
> <ns1:data>
> <ns1:listBegin/>
> </ns1:data>
> </ns1:result>
> </ns1:requestStatus>
> <requestID>ogsadai-11682c89115</requestID>
> </executeResponse>
> </soapenv:Body>
> </soapenv:Envelope>
> <<<<<<<<<<<<<<<<<<<< END OF RESPONSE >>>>>>>>>>>>>>>>>>>>>>>>>
> 

-- 
----------------------------------------------------------
Ally Hume
Software Architect
EPCC, The University of Edinburgh
Tel: +44 131 651 3397
Date view Thread view Subject view Author view Attachment view