Home : Introduction : DBIx::Recordset
Google Web perl.apache.org

 
Home
 
Features
 
Introduction
 
Embperl
 
Embperl::Object
 
Embperl 2 Advanced
 
DBIx::Recordset
 
Documentation
 
Installation
 
Download
 
Support
 
Changes
 
Wiki
 
More infos
 
Add info about Embperl
 
Login

    Stable 2.4.0
    Beta 2.5.0_3
Support the development of Embperl! More...
Basic Example
[ << Prev: Embperl and DBIx::Recordset ] [ Content ] [ Next: Multiple tables >> ]

The following example shows the basic functions of both modules. It shows the contents of a table whose name is passed as a parameter:

<body> <h1>Contents of table "[+ $fdat{'!Table'} +]"</h1>

  [-
  use DBIx::Recordset ;
 
  $fdat{'!DataSource'} = 'dbi:mysql:test' ;
  *set = DBIx::Recordset -> Search(\%fdat) ;
  $names = $set -> Names ;
  -]

  <table>
    <tr>
      <th>[+ $names -> [$col] +]</th>
    </tr>
    <tr>
      [- $rec = $set[$row] -]
      <td>[+ $rec -> {$names->[$col]} +]</td>
    </tr>
  </table>
</body>

To show the contents of the table address you may call it with:

  http://www.domain.com/path/to/example1.htm?!Table=address

All query parameters are placed in the hash %fdat by Embperl. In our example, $fdat{'!Table'} would contain the value address. Additionally, Embperl replaces the code between [+ and +] with the result, so the headline of the page would be 'Contents of table "address"'.

The following [- -] block will be executed by Embperl. No trace of it will show up in the page that is sent to the browser. The first line sets the database which should be accessed. The syntax is the same as for the DBI connect call. If you omit the line, you must additionally send the databasename as a query parameter - but for security reasons, that isn't a very good idea.



Searchtop

Next we call the method Search of DBIx::Recordset, where we have the choice between the object and the class-method. This applies to a lot of other methods as well. When we call it as a class method, as we do in our example, it constructs a new DBIx::Recordset object and uses the passed parameters to query the database. It's also possible to divide these two steps and call Setup to first construct the object and then Search with this object to execute the Search. In the example above, we do not pass any query parameters -- so Search will return the contents of the whole table. (DBIx::Recordset converts the call internally to the SQL statement SELECT * FROM address).

The last line of the [- -] block retrieves the fieldnames of the table. Here we can see a special feature of DBIx::Recordset, which we will discuss in detail later on. The constructor returns a typeglob (*set), but the call to Names uses a scalar ($set). By returning a typeglob, DBIx::Recordset is able to return a scalar, an array and a hash at the same time. (If you don't like the idea of using typeglobs, you can also construct all three with different methods).



Display the tabletop

At first glance, the following might appear to be a simple HTML-table. But Embperl expands it, so that the full contents of the database table is shown. Let us first look at the header, which should show the fieldnames of the database-table: $names contains a reference to an array which contains the fieldnames. Embperl gives us the magical variable $col. $col will be automatically incremented as long as the result of the expression which contains $col doesn't return undefined. At the same time, Embperl repeats the surrounding <th> or <td> tags. If we have a table with the three columns name, firstname and town, the output would look like this:

  <th>name</th><th>firstname</th><th>town</th>

Now the header is ready and we can start to output the contents. Here we use the array part of the typeglob that is returned by Search. Access to the results of the SQL-query is done via the array @set, and every row of the array "contains" one row of the database-table. It does not really contain the row, but DBIx::Recordset will fetch the row from the databases for you if you access the corresponding array row. The rows are stored as a hash, where the fieldnames are the hashkeys. This is the same mechanism that helped us to expand the columns of the header, but it's at work here in a two-dimensional manner. $row contains the row-count and $col contains the column-count.



Supplying query parameterstop

But our small example can do even more: If we supply more query parameters in our request, we can decide which parts of the table should be selected (and therefor, shown). If we request the page with

  http://wwww.domain.com/path/to/example1.htm?!Table=address&town=Berlin

Embperl will not only place !C<T>able in the hash %fdat, but also town. Since town corresponds to a fieldname in our table, DBIx::Recordset interprets it as a parameter for the WHERE part of the SELECT command. DBIx::Recordset will generate the following SQL-query:

  SELECT * FROM address WHERE town='Berlin' ;

The programmer doesn't have to pay attention to datatypes or quoting, this is done automatically by DBIx::Recordset.

Also, complex queries are easy to implement: if, for example, the user wants to be able to search for a name or for a town, it would be possible to use the following form:

  <form action="/path/to/example1.htm" method=GET >
    <input type=text name="+name|town">
    <input type=hidden name="!Table" value="address">
    <input type=submit>
  </form>

If the user enters "Richter" to the input field and presses the submit button, the following SQL-query will be generated:

  SELECT * FROM address WHERE name='Richter' OR town='Richter' ;

Just by varying the parameters, it is possible to create simple or complex queries. In this way, you can use the same page with different parameters to create different sorts of queries.


[ << Prev: Embperl and DBIx::Recordset ] [ Content ] [ Next: Multiple tables >> ]

© 1997-2012 Gerald Richter / ecos gmbh