SQL processor can't get parameter

I wrote a dynamic query to get data from different table.

I can't get parameter from data input.

I add debug attribute at data input, get something like

<parameter xmlns:ev="http://www.w3.org/2001/xml-events" xmlns:xforms="http://www.w3.org/2002/xforms" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xxforms="http://orbeon.org/oxf/xml/xforms">
    <database-name>TABLENAME</database-name>
</parameter>

But the SQL query select TOP 10 * from <sql:param type="xs:string" select="/parameter/table-name"/> become

select TOP 10 * from  ?

not

select TOP 10 * from  TABLENAME

below is source

view.xhtml

<xforms:instance id="pick-instance-parameters">
  <parameter xmlns="">
    <table-name>TABLENAME</table-name>
  </parameter>
</xforms:instance>
<xforms:submission id="pick-service" ref="instance('pick-instance-parameters')" replace="instance" instance="pick-instance" method="post" action="/purchase-order/pick"/>

page flow

<config xmlns="http://www.orbeon.com/oxf/controller">
  <page path-info="/purchase-order/" view="view.xhtml" />
  <page path-info="/purchase-order/pick" view="pick-service.xpl" />
  <page path-info="/purchase-order/country" view="country-service.xpl" />
  <page path-info="/purchase-order/category" view="category-service.xpl" />
  <epilogue url="oxf:/config/epilogue.xpl"/>
</config>

XPL

<p:config xmlns:oxf="http://www.orbeon.com/oxf/processors"
          xmlns:p="http://www.orbeon.com/oxf/pipeline"
          xmlns:sql="http://orbeon.org/oxf/xml/sql"
          xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <p:param type="input" name="instance" />
  <p:param type="output" name="data" />

  <p:processor name="oxf:sql">
    <p:input name="data" href="#instance" debug="true" />
    <p:input name="datasource" href="datasource.xml" debug="true" />
    <p:input name="config">
      <sql:config>
        <items>
          <sql:connection>
            <sql:execute>
              <sql:query debug="true">select TOP 10 * from <sql:param type="xs:string" select="/parameter/table-name" /></sql:query>
              <sql:result-set>
                <sql:row-iterator>
                  <item>
                    <sql:get-columns />
                  </item>
                </sql:row-iterator>
              </sql:result-set>
              <sql:no-results>
                <item>
                  none
                </item>
              </sql:no-results>
            </sql:execute>
          </sql:connection>
        </items>
      </sql:config>
    </p:input>
    <p:output name="data" ref="data" debug="true" />
  </p:processor>

</p:config>

Thanks in advance,

Jack

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
OW2 mailing lists service home page: http://www.ow2.org/wws

Jack,

> I wrote a dynamic query to get data from different table.
> I can't get parameter from data input.

you could add some more debugging attributes - in your case, i would
start with the p:param type="input" to check how the xml data looks like
that's passed to the XPL:

p:param type="input" debug="xpl-pick-in" name="instance"

If the input looks good, the next point of interest could be the xpath
in the select attribute of the sql:param element. Does //table-name work?
(...)

HTH
florian


--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
OW2 mailing lists service home page: http://www.ow2.org/wws

Thanks everyone who reply my question.

I understand what reason caused get nothing.

Actually I have got the parameter from data input because I wrote

<sql:query debug="true">select TOP 10 * from TABLENAME WHERE <sql:param type="xs:string" select="/parameter/condition" /></sql:query>

it works

The sql:param might only use under WHERE clause.

How can I achieve my purpose which query data from different table?

Or only stored procedure can achieve.

Jack




--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
OW2 mailing lists service home page: http://www.ow2.org/wws

Jack,

> How can I achieve my purpose which query data from different table?
> Or only stored procedure can achieve.

you could use an XSLT processor to build the SQL query according to your
needs. This has the drawback that there's another processor in the XPL,
but you have complete flexibility regarding the output. Practically, you
could build the complete XML code thats needed as config input for the
SQL processor using XSLT. This way, the SQL processor just needs to
reference the different inputs:

<!-- Build query -->
<p:processor name="oxf:xslt">
    <p:input name="data"
            href="#instance"
            debug="xsltDataIn" />
    <p:input name="config">
        <xsl:stylesheet version="1.0">
            <!-- XSLT code to produce SQL config input -->
        </xsl:stylesheet>
    </p:input>
    <p:output name="sqlquery"
            id="sqlquery"
            debug="xsltDataOut" />
</p:processor>

<!-- Execute query -->
<p:processor name="oxf:sql">
    <p:input name="datasource"
            href="datasource.xml"
            debug="sqlDatasourceIn" />
    <p:input name="config"
            href="sqlquery"
            debug="sqlQueryIn" />
    <p:output name="data"
            ref="data"
            debug="sqlQueryOut" />
</p:processor>

There's no need for a data input, because the SQL processor doesn't
build the query any more, it just executes the prepared query.

HTH
florian


--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
OW2 mailing lists service home page: http://www.ow2.org/wws

Thank you very much!

You help me to solve problem again!

Jack

-----Original Message-----
From: Florian Schmitt <[hidden email]>
Reply-to: [hidden email]
To: [hidden email]
Subject: [ops-users] Re: Re: SQL processor can't get parameter
Date: Fri, 09 Jan 2009 08:50:03 +0100

Jack,

> How can I achieve my purpose which query data from different table?
> Or only stored procedure can achieve.

you could use an XSLT processor to build the SQL query according to your
needs. This has the drawback that there's another processor in the XPL,
but you have complete flexibility regarding the output. Practically, you
could build the complete XML code thats needed as config input for the
SQL processor using XSLT. This way, the SQL processor just needs to
reference the different inputs:

<!-- Build query -->
<p:processor name="oxf:xslt">
    <p:input name="data"
            href="#instance"
            debug="xsltDataIn" />
    <p:input name="config">
        <xsl:stylesheet version="1.0">
            <!-- XSLT code to produce SQL config input -->
        </xsl:stylesheet>
    </p:input>
    <p:output name="sqlquery"
            id="sqlquery"
            debug="xsltDataOut" />
</p:processor>

<!-- Execute query -->
<p:processor name="oxf:sql">
    <p:input name="datasource"
            href="datasource.xml"
            debug="sqlDatasourceIn" />
    <p:input name="config"
            href="sqlquery"
            debug="sqlQueryIn" />
    <p:output name="data"
            ref="data"
            debug="sqlQueryOut" />
</p:processor>

There's no need for a data input, because the SQL processor doesn't
build the query any more, it just executes the prepared query.

HTH
florian
plain text document attachment (message-footer.txt)
--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
OW2 mailing lists service home page: http://www.ow2.org/wws




--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
OW2 mailing lists service home page: http://www.ow2.org/wws