Parameterizing the order column

Hi!

Is there a way to parameterize an order column in an SQL processor? See
the ORDER BY clause in a sample below to understand, what is needed.

/Ray

    <p:processor name="oxf:sql">
        <p:input name="data" href="#params"/>
        <p:input name="datasource" href="/config/myapp1-ds.xml"/>
        <p:input name="config">
            <sql:config>
                <friends>
                    <sql:connection>
                        <sql:execute>
                            <sql:query>
                                SELECT firstname, lastname, age
                                  FROM friends
                                 WHERE age = <sql:param type="xs:int"
select="/params/age" />
                              ORDER BY <sql:param type="xs:string"
select="/params/order-column" />
                            </sql:query>
                            <sql:result-set>
                                <sql:row-iterator>
                                    <friend>
                                        <firstname>
                                            <sql:get-column-value
type="xs:string" column="firstname"/>
                                        </firstname>
                                        <lastname>
                                            <sql:get-column-value
type="xs:string" column="lastname"/>
                                        </lastname>
                                        <age>
                                            <sql:get-column-value
type="xs:int" column="age"/>
                                        </age>
                                    </friend>
                                </sql:row-iterator>
                            </sql:result-set>
                        </sql:execute>
                    </sql:connection>
                </friends>
            </sql:config>
        </p:input>
        <p:output name="data" ref="data"/>
    </p:processor>


--
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

Raymond,

On Jan 23, 2008 4:08 PM, Raymond Jones <[hidden email]> wrote:
> Is there a way to parameterize an order column in an SQL processor? See
> the ORDER BY clause in a sample below to understand, what is needed.

The <sql:param> is for setting "values"; you can't use it for column
names or SQL keywords. For that you will need to use XSLT, i.e.
generate the <sql:config> with an XSLT step that runs before oxf:sql.
You still want to use <sql:param> for actual values instead of putting
the values in the query with XSLT, as <sql:param> will take care of
escaping for you, and also because this can potientialy be faster as a
prepared statement will be used.

Alex
--
Orbeon Forms - Web 2.0 Forms, open-source, for the Enterprise
http://www.orbeon.com/


--
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

On 24.01.2008 05:49 Alessandro Vernet wrote:
> Raymond,
>
> On Jan 23, 2008 4:08 PM, Raymond Jones <[hidden email]> wrote:
>  
>> Is there a way to parameterize an order column in an SQL processor? See the ORDER BY clause in a sample below to understand, what is needed.
>>    
>
> The <sql:param> is for setting "values"; you can't use it for column names or SQL keywords.

Yes, that's what I understood also after seeing the prepared statement
placeholders in a DBMS statement log.

> For that you will need to use XSLT, i.e generate the <sql:config> with an XSLT step that runs before oxf:sql.

Thank you for the idea... The query works now fine!

/Ray


--
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