---
name: "DBIx::DBStag::SQLTemplate"
section: "3pm"
platform: "linux"
package: "libdbix-dbstag-perl"
version: "0.12-4"
release: "Debian 13 (Trixie)"
platforms: ["linux"]
url: "https://new.carta.tech/DBIx::DBStag::SQLTemplate"
---

# DBIx::DBStag::SQLTemplate(3pm)

## NAME

```
  DBIx::DBStag::SQLTemplate - A Template for an SQL query
```

## SYNOPSIS

```
  # find template by name
  $template = $dbh->find_template("mydb-personq");
  # execute this template, filling in the 'name' attribute
  $xml = $dbh->selectall_xml(-template=>$template,
                             -bind=>{name => "fred"});
```

## DESCRIPTION

A template represents a canned query that can be parameterized.

Templates are collected in directories (in future it will be possible to store them in files or in the db itself).

To tell DBStag where your templates are, you should set:

```
  setenv DBSTAG_TEMPLATE_DIRS "$HOME/mytemplates:/data/bioconf/templates"
```

Your templates should end with the suffix **.stg**, otherwise they will not be picked up

You can name templates any way you like, but the standard way is to use 2 or 3 fields

```
  SCHEMA-OBJECT
```

or

```
  SCHEMA-OBJECT-QUALIFIERS
```

(with underscores used within fields)

A template file should contain at minimum some SQL; for example:

- Example template 1

  ```
    SELECT
                 studio.*,
                 movie.*,
                 star.*
    FROM
                 studio NATURAL JOIN
                 movie NATURAL JOIN
                 movie_to_star NATURAL JOIN
                 star
    WHERE
                 [movie.genre = &genre&] [star.lastname = &lastname&]
    USE NESTING (set(studio(movie(star))))
  ```

  That's all! However, there are ways to make your template more useful

- Example template 2

  ```
    :SELECT
                 studio.*,
                 movie.*,
                 star.*
    :FROM
                 studio NATURAL JOIN
                 movie NATURAL JOIN
                 movie_to_star NATURAL JOIN
                 star
    :WHERE
                 [movie.genre = &genre&] [star.lastname = &lastname&]
    :USE NESTING (set(studio(movie(star))))
    //
    schema: movie
    desc: query for fetching movies
  ```

  By including **:** at the beginning it makes it easier for parsers to assemble SQL (this is not necessary for DBStag however)

  After the // you can add tag: value data.

  You should set **schema:** if you want the template to be available to users of a db that conforms to that schema

### GETTING A TEMPLATE

The DBIx::DBStag object gives various methods for fetching templates by name, by database or by schema

### VARIABLES

WHERE clause variables in the template look like this

```
  &foo&
```

variables are bound at query time

```
  my $set = $dbh->selectall_stag(-template=>$t,
                                 -bind=>["bar"]);
```

or

```
  my $set = $dbh->selectall_stag(-template=>$t,
                                 -bind=>{foo=>"bar"});
```

If the former is chosen, variables are bound from the bind list as they are found

### OPTIONAL BLOCKS

```
  WHERE [ foo = &foo& ]
```

If foo is not bound then the part between the square brackets is left out

Multiple option blocks are **AND**ed together

An option block need not contain a variable - if it contains no **&variable&** name it is automatically **AND**ed

### BINDING OPERATORS

The operator can be bound at query time too

```
  WHERE [ foo => &foo& ]
```

Will become either

```
  WHERE foo = ?
```

or

```
  WHERE foo LIKE ?
```

or

```
  WHERE foo IN (f0, f1, ..., fn)
```

Depending on whether foo contains the % character, or if foo is bound to an ARRAY

## METHODS

### name

```
  Usage   - $name = $template->name
  Returns - str
  Args    -
```

every template has a name that (should) uniquely identify it

### desc

```
  Usage   - $desc = $template->desc
  Returns - str
  Args    -
```

templates have optional descriptions

### get\_varnames

```
  Usage   - $varnames = $template->get_varnames
  Returns - listref of strs
  Args    -
```

Returns the names of all variable used in this template

## WEBSITE

\<http://stag.sourceforge.net\>

## AUTHOR

Chris Mungall \<*cjm@fruitfly.org*\>

## COPYRIGHT

Copyright (c) 2003 Chris Mungall

This module is free software. You may distribute this module under the same terms as perl itself