4.57. query

Passes SQL statements through to SQL databases, or allows SQL queries via Interchange's database abstraction into non-SQL databases and text files. The latter requires the Perl SQL Statement module (included with Bundle::Interchange from CPAN).

4.57.1. Summary

    [query sql]
    [query sql="SQL_query_text" other_named_attributes]
Parameters Description Default
sql The SQL statement.
  • Passed directly through to an SQL database.
  • For a non-SQL table, the tag interprets your SQL first. See the SQL Statement module for limitations and detail.
none
query Alias for sql none
Attributes Default
table products
base (alias for table) products
type (row_count, html, list, textref) none: uses arrayref="" if no type
arrayref arrayref="" if no type given
hashref none
more (type=list) No
xx form var. abbrev. (type=list) see form variable
  (type=list) sql
list_prefix (type=list) list
random (type=list) No
safe_data (type=list) No
label (type=list) current
form (type=list) none
wantarray No
interpolate No
reparse Yes
Other_Characteristics  
Invalidates cache No
Container tag Yes
Has subtags Yes
Nests No

Tag usage example:

This will list sku, description and price for ten products per page, followed by hyperlinks to the other pages of the list. Note that you may interpolate Interchange tags in the usual way if you double-quote the SQL statement.

    [query sql="select sku, description, price from products where price < [value mv_arg]"
         type=list
         more=1
           ml=10]

      [on_match]Matched<br>[/on_match]
      [no_match]Not Found<br>[/no_match]

      [list]
        [sql-code] [sql-param description] [sql-price]
      [/list]

      [more_list]
        Matches [matches] of [match-count] shown.<BR>
        [more]
      [/more_list]
    [/query]

ASP-like Perl call:

   my $sql = "select * from products order by price";
   my $result_array = $Tag->query( { sql => $sql,  },
                                   $body );
   my ($same_results, $col_name_hash, $col_name_array) =
                      $Tag->query( { sql => $sql,  },
                                   $body );

   my $result_hasharray = $Tag->query( { sql     => $sql,
                                         hashref => 'my_results',  },
                                       $body );

or similarly with positional parameters,

    $Tag->query( $sql, $attribute_hash_reference, $body);

4.57.2. Description

The query tag allows you to make SQL queries. If you are using an SQL database table, the tag will pass your SQL statement directly to the database and return the result.

If your table is not in an SQL database (for example, GDBM, text, LDAP, and in-memory tables), Interchange will internally convert it to an Interchange search specification with the Perl SQL Statement module (included with Bundle::Interchange from CPAN). This means that you can use simple SQL queries regardless of the underlying database implementation.

4.57.2.1. Subtags

For list queries (type=list), the following subtags are available:

Subtag Usage
on_match
 [on_match]
   do this if something matched
 [/on_match]
no_match
 [no_match]
   do this if nothing matched
 [/no_match]
list
 [list_prefix]
   do this for each matched item
 [/list_prefix]

The 'list' subtag defines a region where you can use any of the looping subtags that work in array-list context (see Looping tags and Sub-tags).

The default looping tag prefix will be 'sql'. Note however that you can override this by setting the prefix attribute in the enclosing query tag.

Similarly, the list_prefix attribute renames the [list] subtag itself to the value you set (see list_prefix below).

more_list
 [more_list]
   [more]
 [/more_list]

The 'more_list' and 'more' subtags are used when paginating the query results (see 'more' attribute). The [more] subtag will expand to a list of links to the other pages of the query results.

See also the example at the end of the Summary section above.

4.57.2.2. Perl and ASP usage

If you are calling $Tag->query within a perl tag (or whenever the code is secured by the Safe.pm module), you must be sure to set the tables attribute properly in the enclosing perl tag (see the perl tag documentation for detail).

The types that return text to a page (i.e., row_count, html, and textref) work as usual, returning an appropriate string. Note that you may also have access to the results as an array reference in $Vend::Interpolate::Tmp->{''} for the life of the page.

If you do not set a type, the tag will return a reference to an array of array references, since the default with no type is arrayref="".

If you call $Tag->query in scalar context and set arrayref or hashref, it will return your results as a reference to an array of either arrayrefs or hashrefs, respectively (i.e., the same data structures you would get from Perl's DBI.pm module with fetchall_arrayref).

In list context, the first returned element is the aforementioned reference to your results. The second element is a hash reference to your column names, and the third element is an an array reference to the list of column names.

The following examples should be illustrative:

  [perl tables=products]
    my $sql = "select sku, price, description from products
               where price < 10 order by price";

    my $results = $Tag->query( { sql => $sql, } );
    my ( $same_results, $col_name_hashref, $col_name_arrayref)
        = $Tag->query( { sql => $sql, } );

    my $hash_results = $Tag->query( {     sql => $sql,
                                      hashref => 'my_results' } );

    # $Vend::Interpolate::Tmp->{my_results} == $hash_results
    # $Vend::Interpolate::Tmp->{''} == $results == $same_results

    my $out = "The returned structure is\n";
        $out .= $Tag->uneval( $results );

    #loop through each row & display the fields
    foreach my $row (@$hash_results) {
       $out .= '<p>sku: ' . $row->{sku}
           $out .= '<br>price: ' . $row->{price};
           $out .= '<br>description: ' . $row->{description};
    }
    return $out;

  [/perl]


Technical Note: The $Tag->query() call works a bit differently in GlobalSubs and UserTags than within a perl tag. Specifically, in a GlobalSub or global UserTag, if you call query() in list context and want the three references (i.e., results, column hash and column array), then you need to set the 'wantarray=1' attribute in the query() call. See the wantarray attribute.


4.57.2.3. sql

This is the text of your SQL statement. The standard Interchange quoting rules apply. For example, use double quotes (") if you want to interpolate Interchange tags within your SQL statement, backticks (`) to calculate a value, etc.

    [query sql="select description, price from products
               where price < [value mv_arg]" ...]
        ...
    [/query]

4.57.2.4. table

The table attribute sets the database to use for the query. The default will typically be the database containing the 'products' table (unless you have changed the first entry in $Vend::Cfg->{ProductFiles}).

4.57.2.5. type

If you are not setting the 'arrayref' or 'hashref' attributes, then the type attribute defines the way the query will return its results. The type should be one of the following:

Type Returns
html The html type returns the results in an html table. You will need to supply the enclosing <TABLE ...> and </TABLE> html tags. The following is an example of typical usage:
 <TABLE>
   [query sql="select * from products
               where price > 12
               order by price"
         type=html]
   [/query]
 </TABLE>
list This allows you to use subtags to control the query output and pagination. See the Subtags section above for detail.
row_count This causes the tag to return the number of rows in the query result.
textref This causes the tag to return a the query results as a serialized array of arrays that Perl can evaluate with its eval() function. Here is an illustrative example:
  my $rows = eval( $Tag->query( { sql  => "select * from products"
                                  type => "textref" } )
                 );
 
  my $r3_c0 = $rows->[3]->[0];

If you do not specify a type, the tag will create an arrayref as if you had set 'arrayref=""'.

4.57.2.6. arrayref and hashref

If you set 'arrayref=keyname' or 'hashref=keyname', the query will not return results to the page. Instead, it will place the results of your query in the $Vend::Interpolate::Tmp hash. Using 'arrayref=my_query' sets $Vend::Interpolate::Tmp->{my_query} to refer to an array of array references, while 'hashref=my_query' creates an array of hash references.

Note that this is useful only if you intend to access the results within Perl code (for example, within a [perl] tag), since there is no direct output to the returned page.

The $Vend::Interpolate::Tmp hash persists only for the life of the template page being processed. If you need the query results array reference to outlive the page, you will have to save the reference somewhere more persistent such as the $Session hash:

   $Session->{my_query} = $Vend::Interpolate::Tmp->{my_query};

Beware the impact on performance if you do this with large result sets.

Technical note -- the string returned by the 'textref' type will eval() to the 'arrayref' data structure.

4.57.2.7. more

Requires 'type=list'.

You must set more=1 to properly paginate your results from list queries (see 'type=list' above. If you do not set more=1, then the links to later pages will merely redisplay the first page of your results.

4.57.2.8. form variable abbreviations

Requires 'type=list'.

See the Search and Form Variables appendix for a list of form variables. Note that you must use the two-letter abbreviation rather than the full form variable name.

A few deserve special mention:

Abbr Name Description
ml mv_matchlimit Sets number of rows to return. If paginating (more=1), sets rows returned per page.
fm mv_first_match Start displaying search at specified match
sp mv_search_page Sets the page for search display
st mv_searchtype Forces a specific search type (text, glimpse, db or sql), overriding the default determined from your database implementation.

4.57.2.9.

Requires 'type=list'.

Setting 'prefix=foo' overrides the default prefix of 'sql' for loop subtags within a list region (see Looping tags and Sub-tags).

See the list_prefix attribute below for an illustrative example.

4.57.2.10. list_prefix

Requires 'type=list'.

Setting 'list_prefix=bar' overrides the default region tagname of 'list'. The best way to show this is by example. Compare the following two examples of list queries, the first using the defaults and the second with explicitly set prefix and list_prefix.

    [query sql="select sku, description, price from products
               where price < 20"
         type=list
         more=1
           ml=10]

      [on_match]Matched<br>[/on_match]
      [no_match]Not Found<br>[/no_match]

      [list]
        [sql-code] [sql-param description] [sql-price]
      [/list]

      [more_list]
        [more]
      [/more_list]
    [/query]
---
    [query  sql="select sku, description, price from products
                where price < 20"
           type=list
         prefix=foo
    list_prefix=bar
           more=1
             ml=10]

      [on_match]Matched<br>[/on_match]
      [no_match]Not Found<br>[/no_match]

      [bar]
        [foo-code] [foo-param description] [foo-price]
      [/bar]

      [more_list]
        [more]
      [/more_list]
    [/query]

4.57.2.11. random

Requires 'type=list'.

You can use the 'random' attribute to randomly select a set of rows from the whole result set of your query. In other words, setting 'random=n', where n > 0, causes the [list] region to loop over n randomly chosen rows rather than the full query result set.

The example below would display three randomly chosen products priced under 20.

    [query sql="select * from products
               where price < 20"
         type=list
       random=3]

      [list]
        [sql-code] [sql-param description] [sql-price]
      [/list]

    [/query]

4.57.2.12. safe_data

Requires 'type=list'.

Note -- you should not set this unless you need it and know what you are doing.

Setting 'safe_data=1' allows the [sql-data] tag to return values containing the '[' character. See also Looping tags and Sub-tags.

Beware of reparsing issues.

4.57.2.13. label

Requires 'type=list'.

If you are setting up multiple simultaneously active search objects within a page, this allows you to distinguish them. The default label is 'current'. Most people will not need this.

4.57.2.14. form

Requires 'type=list'.

You can use this to pass one CGI form variable in the pagination links of a [more-list]. For example, 'form="foo=bar"' to include '&foo=bar' in the URL of each of the pagination links.

Note that the variable will not be available in the initial result set since the query returns the first page directly (i.e., you did not follow a pagination link).

4.57.2.15. wantarray

This is relevant only when calling $Tag->query( ... ) within global Perl code such as a globalsub or global usertag where $MVSAFE::Safe is not defined. In these cases, setting 'wantarray=1' allows the call to

  $Tag->query( { wantarray => 1, ... }, ... );

to return references as it would if called within an ordinary [perl] tag. Note that it does not force list context if you call $Tag->query in scalar context.

Here is another example of the use of the array references, from a UserTag:

    my $sku = 'os28044';
    my $sql = qq{select description, price from products where sku = '$sku'};
    my $table = 'products';

    my ($results, $col_name_hashref, $col_name_arrayref) = $Tag->query({
            wantarray => 1,
            sql => "$sql",
            table => "$table"});

    my $out;

    # this will get the first field (description)..
    $out = 'description: ' . $results->[0]->[0];

    # and this the second field (price)..
    $out .= '<br>price: ' . $results->[0]->[1];

    # this will tell us the position in the $results array of the price field..
    $out .= '<br>position of price field: ' . $col_name_hashref->{price};
    return $out;

If the [query] returns more than one row, the second row's description field would be:

    $results->[1]->[0]

Technical note -- the ordinary [query ...] ... [/query] usage forces scalar context on the query call and suppresses the return value for those types that would return references if $Tag->query were called within a [perl] tag. The wantarray option is needed because global subs and usertags are also affected by this unless you set wantarray.

Example of a nested query:

    [query
        ml=99
        type=list
        sp="@@MV_PAGE@@"
        sql=|
            SELECT  foo1, foo2, foo3
            FROM    bar
            WHERE   someothercol = 'bang'
        |]
        [list]

            Here is [sql-param foo1] from outer <br>


           [query
               prefix=usr
               list_prefix=detail
               ml=1
               type=list
               sp="@@MV_PAGE@@"
               sql=|
                SELECT  flip
                FROM    flap
                WHERE   flog = 'flan'
              |]

             [usr-on-match]
                something was found in the inner!
             [/usr-on-match]

             [detail]

                Here is [usr-param flip] from inner <br>

                Here is [sql-param foo2] from outer!! <br>

             [/detail]
           [/query]

            Here is [sql-param foo3] from outer!!! <br>

        [/list]
        [on-match]
            Something was found in the outer query<br>
        [/on-match]
        [no-match]
            Nothing was found in the outer query<br>
        [/no-match]
        [more-list]
                <br>[matches] in the outer query<br>
        [/more-list]
    [/query]

Notice the use of 'prefix' and 'list_prefix' on subsequent inner queries.