=pod =head1 Embperl and DBIx::Recordset This introduction gives an overview how to use I together with I. Since there are only a few I specific things herein, it should be also usefull for non I users. =head2 Overview It is often very difficult to layout and design the output of normal CGI scripts, because you are dealing with HTML-sourcecode which spans multiple prints, and it isn't possible to use some sort of HTML-editor. Embperl takes a different approach to this problem. With Embperl, you can build your HTML-pages with any tool you like, and you can embed fragments of code directly in the page. This makes it much easier for non-programmers to use, because they are able to use their usual tools and they see the fragments of code as normal text. This indroduction will deal with the Perl Modules I and I, with a focus on database access. =head2 Embperl In brief, the purpose of Embperl is to execute code that is embedded in HTML-pages as the page is requested from the server. There are two ways to do this with Embperl. The first way is to embed the code between [- and -] tags. This will cause Embperl to execute the code and remove it from the source before sending the page. The second way is to use [+ and +] as the delimiter, in which case the code will be executed and the result of the execution is send to the browser in place of the code. All database access is done via the module I, which simplifies a lot of common tasks when accessing a database via DBI. =head1 Basic Example The following example shows the basic functions of both modules. It shows the contents of a table whose name is passed as a parameter:

Contents of table "[+ $fdat{'!Table'} +]"

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

[+ $names -> [$col] +]
[+ $rec -> {$names->[$col]} +]

To show the contents of the table C

you may call it with: http://www.domain.com/path/to/example1.htm?!Table=address All query parameters are placed in the hash C<%fdat> by Embperl. In our example, C<$fdat{'!Table'}> would contain the value C

. Additionally, Embperl replaces the code between C<[+> and C<+]> 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. =head2 Search Next we call the method C of I, 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 I object and uses the passed parameters to query the database. It's also possible to divide these two steps and call C to first construct the object and then C with this object to execute the Search. In the example above, we do not pass any query parameters -- so C will return the contents of the whole table. (I converts the call internally to the SQL statement C command. I 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 I. 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: 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. =head1 Multiple tables Until now, we only have worked with one table. In real life, you often have to deal with mulitple tables. For this reason, I helps you to reduce the expense associated with dealing with multiple tables. The simplest way to do this is to use the parameters C and C to tell I to create an SQL-join between two or more tables. This will link the tables together and the result looks just like one great table. More interesting is the possibility to create B<"links">. As an example, we'll take the same table we used above and divide it into two tables: one table for the names and one table for the towns. As a link we add an id-field. If the fields are following some naming convention, I is able to find this link automatically. If fields are named in another way, you have to tell I manually how the tables belong together. Table name: firstname, name, town_id Table town: id, town Here, every name has exactly one town and every town has a number of names assigned. With a simple modification of our first example, we could get the same result as above (except that we are now dealing with two tables instead of one): [- use DBIx::Recordset ; $db = DBIx::Database -> new ('dbi:mysql:test') ; $db -> TableAttr ('town', '!NameField', 'town') ; $fdat{'!DataSource'} = $db ; $fdat{'!LinkName'} = 3 ; *set = DBIx::Recordset -> Search(\%fdat) ; $names = $set -> Names ; -] And the request would be: http://www.domain.com/path/to/example2.htm?!Table=name =head2 DBIx::Database The new thing here is the C object. It gathers meta-information about the database and stores it for later use. Because of the names of the fields the object can detect that the field C in the table C points to field C in the table C. Additionally, we tell the C object which column(s) contain the human-readable name of the table C. These initialisations only have to be executed once. If you use I, for example, you should be able to move these lines into a common startup file. Also new is the parameter C. It tells I to return the human-readable name (in our example, C) instead of the field which links the two tables together (C in our example). Internally, I generates an SQL-join, so there is only one C [$if $set -> Link4Field($name) $] [### Link to other table -> generate HTML link ###] [- $link = $set -> Link($set -> Link4Field($name)) -] Show record from table '[+ $link -> {'!Table'} +]' [$endif$] [### Buttons for the different actions, the "name" attribute determinates ###] [### which action should be taken ###] [$endif$] [$endif$] When you first request this page, it will show the contents of the preset table. Alternatively, you can supply a tablename with the parameter CC. 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 B button. This invokes the page itself and C will be instructed by the parameter C<=s>C (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 C<$max>, only C<$max> records are displayed. If this is the case, the C 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 I by the line C<$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 B (parameter C<=update>). A new, empty form could be shown with the button B (parameter C<=empty>) and if you have written data into this empty form, you can add it as a new record with the B button (parameter C<=insert>). Last but not least, there is a B button (parameter C<=delete>). In all of these cases, the content of the form is sent to the page itself, and the C method at the start of the page executes the desired action. More comments can be found inside the source ([# #] blocks).