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...
Modify the Database
[ << Prev: Multiple tables ] [ Content ]

Up to this point, we have only discussed the retrieval and display of data. But of course it's also possible to modify data. The simplest way to do this is to assign new values to the result of a Search call. For example, you may write $set{town} = 'Frankfurt' to change the name of the town. DBIx::Recordset converts this into a vaild SQL-Update-command.

While this is very useful in normal Perl scripts, you probably won't use it very often in a cgi script. The methods Insert/Update/Delete will probably prove more useful. Just like Search, these directly accept query parameters posted to the page. The method Execute combines all four of these together, making it possible to control the type of action via the CGI-parameters.

Here is an example:

<html> <head> <title>Database Access with HTML::Embperl and DBIx::Recordset</title> </head> <body>

  [-
  ### Database-parameter ###
  use DBIx::Recordset ;
  $fdat{'!DataSource'} = 'dbi:mysql:test' ;
  $fdat{'!Table'} ||= 'town' ;
  $fdat{'!PrimKey'} = 'id' ;
  $fdat{'$max'}     = 10 ;

  ### Execute action according to the query parameters ###
  *set = DBIx::Recordset -> Execute (\%fdat) ;
  -]

  [$if $DBI::errstr $]
	<h1>Database Error [+ $DBI::errstr +]</h1>
  [$else$]
    [-$names = $set -> AllNames ; -]
    [$if $set[0] && $set -> MoreRecords $]
      [### We found more then one record ###]
      [### -> display as a table         ###]
      <table>
	<tr>  [### Display header ###]
	  <th>[+ ucfirst ($names -> [$col]) +]</th>
	</tr>
	<tr>  [### Display record -> Table will be expanded by Embperl ###]
	  [- $rec = $set[$row] -]
	  <td>
	    [- $name = $names -> [$col] -]
	    [$if $name eq $fdat{'!PrimKey'} $]
	      [### Generate HTML link to edit this record ###]
	      <a href="example4.htm?!Table=[+ $fdat{'!Table'} +]&[+ $fdat{'!PrimKey'} +]=[+ $rec ->{$fdat{'!PrimKey'}} +]">[+ $rec -> {$name} +]</a>
	    [$elsif $set -> Link4Field($name) $]
	      [### Link to other table -> generate HTML link ###]
	      [- $link = $set -> Link($set -> Link4Field($name)) -]
	      <a href="example4.htm?!Table=[+ $link -> {'!Table'} +]&[+ $link -> {'!LinkedField'} +]=[+ $rec -> {$link -> {'!MainField'}} +]">[+$rec -> {$name}+]</a>
	    [$else$] 
	      [### Display contents of field ###]
	      [+ $rec -> {$names->[$col]} +]
	    [$endif$]
	    </td>
	</tr>
      </table>
      [+ $set -> PrevNextForm ('\<\<Prev', 'Next\>\>', \%fdat) +]
      <hr>
      <a href="example4.htm?!Table=[+ $fdat{'!Table'} +]&%3dempty=1">Search</a> record in table '[+ $fdat{'!Table'} +]' 
    [$else$]
      [### We found no/one record(s) ###]
      [### -> Display form          ###]
      <form>
	<table>
	<tr>
	  [- $name = $names -> [$row] -]
	  <td> [### Display fieldname ###]
	    [+ ucfirst ($name) +]
	  </td>
	  <td> [### Display content of field ###]
	    <input type=text name="[+ $name +]" value="[+ $set{$name} +]">
	    [$if $set -> Link4Field($name) $]
	      [### Link to other table -> generate HTML link ###]
	      [- $link = $set -> Link($set -> Link4Field($name)) -]
	      <a href="example4.htm?!Table=[+ $link -> {'!Table'} +]&[+ $link -> {'!LinkedField'} +]=[+ $set{$link -> {'!MainField'}} +]">Show record from table '[+ $link -> {'!Table'} +]'</a>
	    [$endif$]
	  </td>
	</tr>
	</table>
	[### Buttons for the different actions, the "name" attribute determinates ###]
	[###  which action should be taken                                        ###]
	<input type=submit name="=search" value="Search">
	<input type=submit name="=empty"  value="New">
	<input type=submit name="=insert" value="Add">
	<input type=submit name="=update" value="Update">
	<input type=submit name="=delete" value="Delete">
	<input type=hidden name="!Table"  value="[+ $fdat{'!Table'} +]">
      </form>
    [$endif$]
  [$endif$]
</body>
</html>

When you first request this page, it will show the contents of the preset table. Alternatively, you can supply a tablename with the parameter !TC<able>. The link, which is shown at the bottom of the page, leads you to an imput form. There, you can fill in one or more fields and press the Search button. This invokes the page itself and Execute will be instructed by the parameter =sC<earch> (Name of the button "Search") to retrieve all records which match the entered values.

If the query finds more then one record, a table with all records found will be shown. If there are more records than specified by the parameter $max, only $max records are displayed. If this is the case, the PrevNextForm method adds a "Previous" and a "Next" button to the page, allowing you to browse through the whole table. In the example above, we assume that every table has a primary key, which is passed to DBIx::Recordset by the line $fdat{'!PrimKey'} = 'id' ;. The column which contains this primary key will be displayed as an HTML link containing the parameters to execute a search for just this record. As you can see in example4.htm, this can be used to display a form which includes some of the data from the found record (see below). Columns which are links to other tables will also be shown with an HTML-link. A click on that link will open the linked table or record.

If the search only selects one record, the same form is shown, but with the data from the record filled in. Now it's possible to change the content. The changes are written to the database when you press the button Update (parameter =update). A new, empty form could be shown with the button New (parameter =empty) and if you have written data into this empty form, you can add it as a new record with the Add button (parameter =insert). Last but not least, there is a Delete button (parameter =delete). In all of these cases, the content of the form is sent to the page itself, and the Execute method at the start of the page executes the desired action.

More comments can be found inside the source ([# #] blocks).


[ << Prev: Multiple tables ] [ Content ]

© 1997-2012 Gerald Richter / ecos gmbh