Sql params Xml section.

Properties

Data type	Attribute
Collection	-

Used in

Macro	Description
SqlQuery	Executes sql queries/statements and shows the results.

Remarks

SQL	Copy Code
SELECT Nr, Title FROM vwContent WHERE parent=?

SQL	Copy Code
SELECT Nr, Title FROM vwContent WHERE ContentType=?:CT AND ModDate > ?:LastChanged

Examples

SqlQuery Parameters Example

This example shows you how perform a select using query parameters. As you can see, query parameters can be named parameters and for each parameter in sql query, there is an equivalent in the params parameter. Some parameters also have their type specified, which is usually not necessary but required by some databases.

Smartsite SXML	Copy Code
<se:sqlquery> <se:parameters> <se:parameter name="sql"> select nr, title, modDate from vwActive where author = ?:author and moddate >= ?:thisyear and contenttype = ?:ct </se:parameter> <se:parameter name="params"> <se:collection> <se:member name="author">John</se:member> <se:member name="thisyear" type="datetime">1/1/{datetime.getyear(datetime.now())}</se:member> <se:member name="ct" type="integer">{contenttype.getnumber('PWP')}</se:member> </se:collection> </se:parameter> </se:parameters> </se:sqlquery>

Smartsite SXML

Copy Code

<se:sqlquery>
    <se:parameters>
        <se:parameter name="sql">
            select nr, title, modDate from vwActive where
                author = ?:author and
                moddate >= ?:thisyear and
                contenttype = ?:ct
        </se:parameter>
        <se:parameter name="params">
            <se:collection>
                <se:member name="author">John</se:member>
                <se:member name="thisyear" type="datetime">1/1/{datetime.getyear(datetime.now())}</se:member>
                <se:member name="ct" type="integer">{contenttype.getnumber('PWP')}</se:member>
            </se:collection>
        </se:parameter>
    </se:parameters>
</se:sqlquery>

Smartsite SXML	Copy Code
{buffer.set(a, datatable.createempty())} {datatable.columns.add($a, 'Name', string)} {datatable.columns.add($a, 'Type', string)} {datatable.columns.add($a, 'Value', string)} {datatable.rows.add($a, 'nr', 'integer', 27436)} {datatable.rows.add($a, 'title', 'string', 'Screwdriver 300GT')} <se:sqlquery sql="select title from contents where nr=?:nr or title=?:title order by title"> <se:parameters> <se:parameter name="params"> <se:collection inputdata="a"/> </se:parameter> <se:parameter name="format"> <se:rowformat>{this.field(title)}</se:rowformat> <se:rowdelimiter>,</se:rowdelimiter> </se:parameter> </se:parameters> </se:sqlquery>

Smartsite SXML

Copy Code

{buffer.set(a, datatable.createempty())}

{datatable.columns.add($a, 'Name', string)}
{datatable.columns.add($a, 'Type', string)}
{datatable.columns.add($a, 'Value', string)}

{datatable.rows.add($a, 'nr', 'integer', 27436)}
{datatable.rows.add($a, 'title', 'string', 'Screwdriver 300GT')}

<se:sqlquery sql="select title from contents where nr=?:nr or title=?:title order by title">
    <se:parameters>
        <se:parameter name="params">
            <se:collection inputdata="a"/>
        </se:parameter>
        <se:parameter name="format">
            <se:rowformat>{this.field(title)}</se:rowformat>
            <se:rowdelimiter>,</se:rowdelimiter>
        </se:parameter>
    </se:parameters>
</se:sqlquery>

More examples

SXML