NAME

HTML::FormEngine::DBSQL - create html/xhtml forms for adding, updating and removing records to / in / from sql database tables

DEPENDENCIES

Perl Version

        5.004

Standard Modules

        Carp 1.01

Nonstandard Modules

        HTML::FormEngine 1.0
        Clone 0.13
        Hash::Merge 0.07
        Locale::gettext 1.01
        Digest::MD5 2.24
        DBI 1.42

REQUIREMENTS

  This class was only tested with PostgreSQL. Please tell me about
  your experiences with other DBMS. Thanks!

SYNOPSIS

Example Code

    #!/usr/bin/perl -w

    use strict;
    use HTML::FormEngine::DBSQL;
    use DBI;
    use CGI;
    #use POSIX; #for setlocale
    #setlocale(LC_MESSAGES, 'german'); #for german error messages

    my $q = new CGI;
    print $q->header;

    my $dbh = DBI->connect('dbi:Pg:dbname=test', 'test', 'test');
    my $Form = HTML::FormEngine::DBSQL->new(scalar $q->Vars, $dbh);
    $Form->dbsql_conf('user');
    $Form->make();
    print $q->start_html('FormEngine-dbsql example: User Administration');
    if($Form->ok) {
        if($_ = $Form->dbsql_insert()) {
            print "Sucessfully added $_ user(s)!<br>";
            $Form->clear;
        }
    }
    print $Form->get,
          $q->end_html;
    $dbh->disconnect;

Example Database Table

Execute the following (Postgre)SQL commands to create the tables I used when developing the examples:

    CREATE SEQUENCE user_uid_seq;

    CREATE TABLE "user" (
        uid integer DEFAULT nextval('user_uid_seq'::text) NOT NULL,
        name character varying(40) NOT NULL,
        forename character varying(40) NOT NULL,
        street character varying(40) NOT NULL,
        zip integer NOT NULL,
        town character varying(40) NOT NULL,
        email character varying(40) NOT NULL,
        phone character varying(15)[] DEFAULT '{"",""}'::character varying[],
        birthday date NOT NULL,
        newsletter boolean DEFAULT true
    );

    CREATE TABLE login (
        uid integer DEFAULT currval('user_uid_seq'::text) NOT NULL,
        username character varying(30) DEFAULT '-'::character varying NOT NULL,
        "password" character varying(30) DEFAULT '-'::character varying NOT NULL
    );


    ALTER TABLE ONLY "user"
        ADD CONSTRAINT user_pkey PRIMARY KEY (uid);

    ALTER TABLE ONLY login
        ADD CONSTRAINT login_pkey PRIMARY KEY (uid);

    ALTER TABLE ONLY login
        ADD CONSTRAINT "$1" FOREIGN KEY (uid) REFERENCES "user"(uid) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE;

    COMMENT ON COLUMN "user".zip IS 'ERROR=digitonly;';

    COMMENT ON COLUMN "user".email IS 'ERROR=rfc822;';

    COMMENT ON COLUMN "user".phone IS 'display_as={{,}};ERROR_IN={{{not_null,digitonly},{not_null,digitonly}}};SUBTITLE={{,/}};SIZE={{5,10}};';
    COMMENT ON COLUMN login.username IS 'ERROR={{regex,"must only contain A-Z, a-z and 0-9","^[A-Za-z0-9]+$"},unique,dbsql_unique};';

    COMMENT ON COLUMN login."password" IS 'TYPE=password;VALUE=;ERROR={{regex,"must have more than 4 chars",".{5,}"}};';

Of course you can use any other table(s) as well. The file user.sql in the examples directory contains the whole database dump.

Example Output

This output is produced by FormEngine::DBSQL when using the example code, the example table and no data was submitted:

    <form action="/cgi-bin/formengine-dbsql/createuser.cgi" method="post" name="FormEngine" accept="*" enctype="application/x-www-form-urlencoded" target="_self" id="FormEngine" >
    <table border=0 cellspacing=1 cellpadding=1 align="center" >
    <tr >
    <td colspan=3>
    <table border=0 summary="">
    <tr><input type="hidden" name="uid" value="" /><input type="hidden" name="uid" value="f29e202fda026b18561398f7879cdf37" /></tr>
    <tr>
       <td valign="top" align="left" ><label for="name" accesskey="">Name</label><span ></span></td>
       <td >

          <table border=0 cellspacing=0 cellpadding=0 >
            <tr >
              <td valign="top" >
                <table border=0 cellspacing=0 cellpadding=0 >
                  <tr >
                    <td align="" valign="" > &nbsp; </td>
                    <td >
                      <input type="text" value="" name="name" id="name" maxlength="40" size="20"  />

                    </td>
                    <td > &nbsp; </td>
                  </tr>
                  <tr ><td ></td><td style="color:#FF0000"></td></tr>
                </table>
              </td>
            </tr>
          </table>

    </td>
       <td align="left" valign="bottom" style="color:#FF0000"></td><input type="hidden" name="name" value="f29e202fda026b18561398f7879cdf37" />
    </tr>
    <tr>
       <td valign="top" align="left" ><label for="forename" accesskey="">Forename</label><span ></span></td>
       <td >
          <table border=0 cellspacing=0 cellpadding=0 >
            <tr >
              <td valign="top" >
                <table border=0 cellspacing=0 cellpadding=0 >

                  <tr >
                    <td align="" valign="" > &nbsp; </td>
                    <td >
                      <input type="text" value="" name="forename" id="forename" maxlength="40" size="20"  />
                    </td>
                    <td > &nbsp; </td>
                  </tr>

                  <tr ><td ></td><td style="color:#FF0000"></td></tr>
                </table>
              </td>
            </tr>
          </table>
    </td>
       <td align="left" valign="bottom" style="color:#FF0000"></td><input type="hidden" name="forename" value="f29e202fda026b18561398f7879cdf37" />
    </tr>
    <tr>
       <td valign="top" align="left" ><label for="street" accesskey="">Street</label><span ></span></td>

       <td >
          <table border=0 cellspacing=0 cellpadding=0 >
            <tr >
              <td valign="top" >
                <table border=0 cellspacing=0 cellpadding=0 >
                  <tr >
                    <td align="" valign="" > &nbsp; </td>
                    <td >

                      <input type="text" value="" name="street" id="street" maxlength="40" size="20"  />
                    </td>
                    <td > &nbsp; </td>
                  </tr>
                  <tr ><td ></td><td style="color:#FF0000"></td></tr>
                </table>
              </td>
            </tr>

          </table>
    </td>
       <td align="left" valign="bottom" style="color:#FF0000"></td><input type="hidden" name="street" value="f29e202fda026b18561398f7879cdf37" />
    </tr>
    <tr>
       <td valign="top" align="left" ><label for="zip" accesskey="">Zip</label><span ></span></td>
       <td >
          <table border=0 cellspacing=0 cellpadding=0 >
            <tr >
              <td valign="top" >

                <table border=0 cellspacing=0 cellpadding=0 >
                  <tr >
                    <td align="" valign="" > &nbsp; </td>
                    <td >
                      <input type="text" value="" name="zip" id="zip" maxlength="" size="20"  />
                    </td>
                    <td > &nbsp; </td>

                  </tr>
                  <tr ><td ></td><td style="color:#FF0000"></td></tr>
                </table>
              </td>
            </tr>
          </table>
    </td>
       <td align="left" valign="bottom" style="color:#FF0000"></td><input type="hidden" name="zip" value="f29e202fda026b18561398f7879cdf37" />
    </tr>
    <tr>

       <td valign="top" align="left" ><label for="town" accesskey="">Town</label><span ></span></td>
       <td >
          <table border=0 cellspacing=0 cellpadding=0 >
            <tr >
              <td valign="top" >
                <table border=0 cellspacing=0 cellpadding=0 >
                  <tr >
                    <td align="" valign="" > &nbsp; </td>

                    <td >
                      <input type="text" value="" name="town" id="town" maxlength="40" size="20"  />
                    </td>
                    <td > &nbsp; </td>
                  </tr>
                  <tr ><td ></td><td style="color:#FF0000"></td></tr>
                </table>
              </td>

            </tr>
          </table>
    </td>
       <td align="left" valign="bottom" style="color:#FF0000"></td><input type="hidden" name="town" value="f29e202fda026b18561398f7879cdf37" />
    </tr>
    <tr>
       <td valign="top" align="left" ><label for="email" accesskey="">Email</label><span ></span></td>
       <td >
          <table border=0 cellspacing=0 cellpadding=0 >
            <tr >

              <td valign="top" >
                <table border=0 cellspacing=0 cellpadding=0 >
                  <tr >
                    <td align="" valign="" > &nbsp; </td>
                    <td >
                      <input type="text" value="" name="email" id="email" maxlength="40" size="20"  />
                    </td>
                    <td > &nbsp; </td>

                  </tr>
                  <tr ><td ></td><td style="color:#FF0000"></td></tr>
                </table>
              </td>
            </tr>
          </table>
    </td>
       <td align="left" valign="bottom" style="color:#FF0000"></td><input type="hidden" name="email" value="f29e202fda026b18561398f7879cdf37" />
    </tr>
    <tr>

       <td valign="top" align="left" ><label for="phone" accesskey="">Phone</label><span ></span></td>
       <td >
          <table border=0 cellspacing=0 cellpadding=0 >
            <tr >
              <td valign="top" >
                <table border=0 cellspacing=0 cellpadding=0 >
                  <tr >
                    <td align="" valign="" > &nbsp; </td>

                    <td >
                      <input type="text" value="" name="phone" id="phone" maxlength="15" size="5"  />
                    </td>
                    <td > &nbsp; </td>
                  </tr>
                  <tr ><td ></td><td style="color:#FF0000"></td></tr>
                </table>
              </td>

              <td valign="top" >
                <table border=0 cellspacing=0 cellpadding=0 >
                  <tr >
                    <td align="" valign="" ><label for="phone" accesskey="">/</label><span ></span> &nbsp; </td>
                    <td >
                      <input type="text" value="" name="phone" id="phone" maxlength="15" size="10"  />
                    </td>

                    <td > &nbsp; </td>
                  </tr>
                  <tr ><td ></td><td style="color:#FF0000"></td></tr>
                </table>
              </td>
            </tr>
          </table>
    </td>

       <td align="left" valign="bottom" style="color:#FF0000"></td><input type="hidden" name="phone" value="f29e202fda026b18561398f7879cdf37" />
    </tr>
    <tr>
       <td valign="top" align="left" ><label for="birthday" accesskey="">Birthday</label><span ></span></td>
       <td >
          <table border=0 cellspacing=0 cellpadding=0 >
            <tr >
              <td valign="top" >
                <table border=0 cellspacing=0 cellpadding=0 >

                  <tr >
                    <td align="" valign="" > &nbsp; </td>
                    <td >
                      <input type="text" value="" name="birthday" id="birthday" maxlength="10" size="10"  />
                    </td>
                    <td > &nbsp; </td>
                  </tr>

                  <tr ><td ></td><td style="color:#FF0000"></td></tr>
                </table>
              </td>
            </tr>
          </table>
    </td>
       <td align="left" valign="bottom" style="color:#FF0000"></td><input type="hidden" name="birthday" value="f29e202fda026b18561398f7879cdf37" />
    </tr>
    <tr>
       <td valign="top" align="left" ><label for="newsletter" accesskey="">Newsletter</label><span ></span></td>

       <td >
          <table border=0 cellspacing=0 cellpadding=0 >
            <tr >
              <td valign="top" >
                <table border=0 cellspacing=0 cellpadding=0 >
                  <tr >
                    <td align="" valign="" > &nbsp; </td>
                    <td >


         <select size="" name="newsletter" id="newsletter"  >
            <option value="1" label="Yes"  >Yes</option> 
            <option value="0" label="No"  >No</option> 
         </select>
                    </td>
                    <td > &nbsp; </td>
                  </tr>
                  <tr ><td ></td><td style="color:#FF0000"></td></tr>

                </table>
              </td>
            </tr>
          </table>
    </td>
       <td align="left" valign="bottom" style="color:#FF0000"></td><input type="hidden" name="newsletter" value="f29e202fda026b18561398f7879cdf37" />
    </tr>
    <tr>
       <td colspan=3>&nbsp;</td>
    </tr>
    </table>

    </td>
    </tr>
    <tr >
       <td align="right" colspan=3 >
          <input type="submit" value="Ok" name="FormEngine" />
       </td>
    </tr>
    </table>

    </form>

DESCRIPTION

DBSQL.pm is an exentsion of HTML::FormEngine, that means it inherits all functionality from HTML::FormEngine and adds some new features.

In web development, form data is mostly used to update a database. For example most guestbooks or any similar webapplication store the entered data in a database. Often very large forms are needed, e.g. when the user should provide his personal data to subscribe to an certain service.

In most cases a SQL database is used. If you don't know anything about SQL databases or you're not using such things, this module will hardly help you. But if you do, you'll know that every record, that you want to store in a certain SQL database table, has to have certain fields and these fields must contain data of an certain type (datatype). So the tables structure already defines how a form, that wants to add data to this table, might look like (in case that you don't want to process the whole data before adding it to the table).

DBSQL.pm reads out the tables structure and creates a form definition for HTML::FormEngine.

Two examples:

A field of type boolean will only accept 0 or 1, this is represented in the form as 'Yes' or 'No'.

a field of type VARCHAR(30) will accept strings of maximal 30 characters, so it's represented as an one-line-text-input-field in which you can put maximal 30 characters.

Of course you can re-adjust the resulting form configuration, but in most cases you don't have to care about it!

DBSQL.pm also provides methods for adding and updating records. So you don't have to deal with sql commands.

HTML::FormEngine::DBSQL was only tested with PostgreSQL so far, but it should also work with other DBMS, like e.g. MySQL.

OVERVIEW

We expect that you know how to use HTML::FormEngine, if not, please first read its documentation. Using HTML::FormEngine:DBSQL isn't much diffrent: the conf method is replaced by dbsql_conf and you may pass a database handle as second argument to the new method, using dbsql_set_dbh is possible too. Before calling dbsql_conf, you may call dbsql_preconf for setting some variables by hand.

To dbsql_conf you pass the tables name and optionally a where condition (for updating records) and/or a reference to an array with fieldnames (for setting explicit which fields to show resp. not to show).

USING FormEngine::DBSQL

Configuring The Form Through The Database

datatype handlers

In DBSQL::DtHandler.pm you'll find all datatype handlers which come with this module. Which handler to use for which datatype is defined in DBSQL::SkinClassic, the default FormEngine skin for this module. If for a certain datatype no handler is defined, the default datatype handler will be called.

A handler creates the main part of the form field configuration.

You can easily add your own datatype handlers (see below).

array handling

Though the idea how to store arrays is taken from PostgreSQL, this should work with any other DBMS too!

In PostgreSQL every datatype can be arrayed. PostgreSQL arrays have the following structure: '{firstelem,secondelem}', a two dimensional array looks like this: '{{one,two},{three,four}}'. The problem is that PostgreSQL arrays don't have a fixed size, but FormEngine::DBSQL need such to represent the array in the form. Here we use a trick: the size which should be represented in the form is determined by the default value. So a field with '{,}' as default value will be represented as an one dimensional array (in case you specify display_as it'll be displayed according to that, see below). Of course you can put values between the commas, which will then be printed as defaults.

The following feature might sound a bit complicated, don't worry about it, you'll normaly not need it.

There are two special variables which make array handling more flexible. display_as can be used to specify how a database array shall be represented in the form, save_as works in the other direction, it defines in which format an array submitted by the form is written in the database. This is probably a bit hard to understand, so here is an example: you could save a telefon number in one database field which is of type integer[] (integer array). The first element is the code, the second the number. Of course in the database this is a one dimensional array. But when the telefon field is now represented by the form the one dimensional array will probably cause the two fields to be on two diffrent rows, so you want to turn the one dimensional array into an two dimensional array just by adding one more dimension. This is simply done by setting display_as={{,}}; in the database field comment (see "assigning FormEngine variables in the database"). Same with save_as. So if you specify e.g. {{,}{,}} for one of these variables it'll cause an array like [1,2,3,4] to be turned into [[1,2][3,4]]. The elements are simply read from left to right and putted into the template also from left to right.

NOT NULL fields

The form value of fields which have the NOT NULL property will be automatically passed to the not_null check method. This means that their ERROR variable will be set to not_null.

If the ERROR variable was already set through dbsql_preconf, nothing will be changed. If the variable was set through the fields comment (see "assigning FormEngine variables in the database"), the not_null check will be added in front.

If you called dbsql_set_write_null_fields the not_null check is probably not added since a field which will just be ignored if empty doesn't have to be checked whether it is empty. Read "dbsql_set_write_null_fields ( INTEGER )" for more information.

assigning FormEngine variables in the database

PostgreSQL and other DBMS offer to set comments on database objects. This feature can be used to explicitly set form field variables in the database.

You might e.g. want to store emailadresses in a certain field of a database table, it makes sense to validate an address before inserting it. First possibility is to use dbsql_preconf to set the ERROR variable to 'email' or 'rfc822', but perhaps you've more than one script which inserts or updates the table and so you're using several forms. In every script you now have to call the dbsql_preconf method and set the ERROR variable for the email field. This isn't nice, because the necessity to check this field is given by the table structure and so the check should also be set by the database. You might set a check constraint, but this will cause an ugly database error which the user might not understand. So beside defining an constraint (which is recommended), FormEngine::DBSQL should check the address before inserting it. Setting the database fields comment to 'ERROR=rfc822;' will force FormEngine::DBSQL to do so. You can still overwrite this setting with dbsql_preconf.

Below you see the whole command:

  COMMENT ON COLUMN "user".email IS 'ERROR=rfc822;'

Whenever you pass this tables name to the new method of FormEngine::DBSQL, it'll remember to call the rfc822 check method before inserting or updating a email field value.

You can even assign array structures to a variable:

  COMMENT ON COLUMN "user".phone IS 'ERROR_IN={{{not_null,digitonly},{not_null,digitonly}}};';

The phone field is a string array, with the above command we forbid NULL values and demand digits for the first two elements. More about arrays and their representation in the form is described above ("array handling").

It is possible to assign several variables:

  COMMENT ON COLUMN "user".zip IS 'ERROR=digitonly;TITLE=Postcode;';

Don't forget the ';' at the end of every assignment!

Of course you can still use the comment field to place normal comments there as well:

  COMMENT ON COLUMN "user".birthday IS 'We\'re really a bit curious!;ERROR=date;';

Note the ';' at the end of the trivial comment!

In quoted areas ("..") '{', '}' and ',' are not interpreted. You can prevent the parsing of '"' and ';' by putting an '\' (backslash) in front.

Methods

new ([ HASHREF, DBHANDLE ])

Works exactly like HTML::FormEngines new method but accepts a second parameter, the database handle. This is needed for communicating with the database. Alternatively it can be set through "dbsql_set_dbh ( DBHANDLE )".

dbsql_preconf ( HASHREF, PREPEND, APPEND )

In the referenced hash you can predefine some parts of the form configuration by hand. The hash keys must be named after the tables fields. Every element must be a hash reference, in the referenced hash you can set variables.

You can use the special keys prepend and append to add extra fields before or after the field.

An example:

    my %preconf = (
                   name => {
                          TITLE => 'Fore- and Surname',
                          ERROR => sub {$_ = shift; m/\w\W\w/ ? return 0 : return 'failed';}
                       },
                   email => {
                          TITLE => 'Your Emailadress',
                          ERROR => 'email'
                       }
                   );
    $Form->dbsql_preconf(\%preconf);

The field definitions passed for PREPEND or APPEND are added to the top resp. the bottom of the generated form. If you want to add more than one field, you have to reference an array which contains the definitions, else you can reference the hash directly. See the HTML::FormEngine for information about field definitions.

When using the special key format __add_VARNAME_last resp. __add_VARNAME_first the given values are added at the beginning resp. the end of the (probably) already existing value list. Of course you have to replace VARNAME with the name of the variable to which you want to add something. If the sofar specified value of the variable is a scalar its automatically turned into an array.

Note: If you pass more than one table name to dbsql_conf, you must reference the fields with tablename.fieldname!

dbsql_conf ( ... )

The three dots stand for: TABLENAME|ARRAYREF, [ COUNT|WHERECONDITION|HASHREF, FIELDNAMES|HASHREF ]

This method creates a FormEngine-form-definition and calls FormEngines conf method.

Normally you only want to manage records out of one table, then it is sufficient to give this tables name as first argument. But you can also pass several table names by using an array reference.

If you provide COUNT, the form fields will be displayed COUNT times, which means that you can insert COUNT records.

If you want to update records, you should provide WHERECONDITION instead. This must be a valid where-condition without the WHERE directive in front, or a hash reference. A hash reference you must provide if you passed several tablenames and want to define diffrent where conditions for theses tables. The keys must be the table names and the elements the complying conditions.

DBSQL then shows input fields for every found record and uses the current values as defaults. The primary keys are stored in hidden fields, so that they can't be changed. Later they're used for updating the records.

If you'd like to set only some of the tables fields, put their names in an array and pass a reference as third and last argument (FIELDNAMES). If the first array element is '!', all fields which aren't found in the array will be displayed. You must use a hash reference here if you passed more than one table name.

dbsql_update

This method can only be used if a where-condition was passed to "dbsql_conf ( ... )".

It updates the found table records to the submitted values. If an error occurs the update statement and the DBMSs error message and number is printed. If you want only some of this information to be displayed, see "dbsql_set_sqlerr ( INTEGER )".

Normally you must have defined a secret string if you want to use this method, else an error message will be printed. See "dbsql_set_secret ( SECRET )" for more information.

Before calling this method, you should prove that the form content is valid (see HTML::FormEngine, ok method).

dbsql_insert

This method inserts the transmitted data into the table. If an error occurs, the insert statement and the DBMSs error message and number are printed. If you don't want all or some of this information be displayed, see "dbsql_set_sqlerr ( INTEGER )". Before calling this method, you should prove that the form content is valid (see HTML::FormEngine, ok method).

dbsql_set_dbh ( DBHANDLE )

Use this function to set the internally used database handle. If you don't call this funtion, you must set it when creating the object with the "new ([ HASHREF, DBHANDLE ])" method.

dbsql_set_hide_pkey ( BOOLEAN )

By default the primary key fields are represented as hidden form fields. This makes sense because when updating records they mustn't be changed. Sometimes, especially when inserting new records, one might want to set them by hand. Then he should pass false (0) to this method.

Passing false to this method will also disable the primary key md5 checksum check when calling dbsql_update. This means that it'll be allowed to change the primary keys even when updating records. By default this is not allowed for security reasons. So be carefull with this method!. DATA CAN EASILY GET OVERWRITTEN!!!

You can as well set the pkey template by hand using "dbsql_preconf ( HASHREF, PREPEND, APPEND )".

dbsql_set_show_value ( BOOLEAN )

When you pass a valid where clause to the new method, the contents of the found records will be read in and displayed as defaults. In certain situations one might like to have the fields empty though. Passing false (0) to this method will do it.

dbsql_set_pkey ( SCALAR|ARRAYREF|HASHREF )

Normally the primary key of a database table is autodetected. Sometimes someone might like to define other fields as primary key though (the primary key is important when updating records). You can pass a fieldname or a reference to an array with fieldnames to this method. This method should be called before "dbsql_conf ( ... )" (for being sure, call this method as early as possible).

Note: If you pass several table names to dbsql_conf, you must pass as hash reference here, else the passed pkeys will only be used for the first table.

dbsql_set_show_default ( BOOLEAN )

If you pass true (1) to this method the field defaults defined in the database are used as defaults in the form. This is the default behavior in case you don't specify a where condition but a number (or nothing at all which defaults to 1) (see "dbsql_conf ( ... )"). In case that you do specify a where condition its just logical to not use the database defaults since the real values of the defined database records are used as default values for the form. So this standard behaviour should be just fine and you normally don't need this method. Passing false (0) will force this module to not use the field defaults defined by the database table structure.

dbsql_set_write_null_fields ( INTEGER )

With this method you can define whether the value of form fields for which the user didn't specify any value (he submitted them empty) should be interpreted as NULL and thus null will be written in the database or whether they should be ignored so that the default is used by the database (in case of an insert) resp. the value is not changed (in case of an update).

The default is to interpret empty fields as NULL fields.

0 forces the module to not pass empty fields to the database. This will cause problems when you perform an insert and a certain field is defined as not_null field and also doesn't have a default value. So its a bad idea to pass 0 in case you want to make an insert. Also when doing an update it doesn't make much sense normaly.

1 forces the module to only ignore the null value if it was specified for a not_null field (the table structure forbids the null value for the field). This will cause the same problems as described for 0 (see above). But this can be a good idea if your planning to make an update.

2 forces the module to only ignore an empty field in case it is defined as not_null by the database and a default value is defined. This makes e.g. sense when you want to make an insert and the database shall just set the default values for fields which were not fill out by the user. Perhaps you also want to use dbsql_set_show_default ( BOOLEAN ) to prevent the default values from being displayed.

3 this is the default behaviour. Empty field values are passed as NULL to the database.

dbsql_set_errmsg_templ ( TEMPLATENAME )

If you want to modifiy the output of the system error messages, create a new template (e.g. copy the default and fit it to your needs) and pass the new templates name to this method. By default the template called errmsg of the configured skin ist used (the default skin is HTML::FormEngine::DBSQL::SkinClassic).

dbsql_set_sqlerr ( INTEGER )

Perhaps you already read that whenever a database error occurs, the error message, error number and query command is printed out by default. Sometimes you might prove displaying the sql query a security lack. With the help of this method, you can define which information will be printed.

Listing of the bits and their influence:

1 error number

2 error message

4 sql command

So if you pass 3 to this method the error number and message will be printed, but not the sql command.

dbsql_set_sqlerr_templ ( TEMPLATENAME )

If you want to modifiy the output of the sql error messages, create a new template (e.g. copy the default and fit it to your needs) and pass the new templates name to this method. By default the template called sqlerror of the configured skin is used (the default skin is HTML::FormEngine::DBSQL::SkinClassic).

dbsql_set_row ( BOOLEAN )

If you provided a where condition and more than one record was found, or you provided a number instead and it was higher than 1, then by default it'll be used only one line per record, which means that fields belonging to the same record will be printed on the same line.

By passing 0 (false) to this method you can force the object to use one line per field, 1 (true) is the default.

dbsql_set_row_tmpl ( TEMPLATENAME )

By default the row template is used. If you want to use another template for placing the fields which belong to one record into one line, pass it to this method.

dbsql_set_empty_tmpl ( TEMPLATENAME )

By default the empty template is used for inserting space between the records, If you want to use another template pass its name to this method. The space is only inserted if every field takes one line.

dbsql_get_sqlerr

This method returns an array with the error number and error message from the last database error. The sql command which caused the error will be the third and last element.

dbsql_add_extra_sql(SQLCOMMAND, ARRAY)

This method can be used to define some more sql commands which then will be executed for each record when insert or <update> is called.

The sql command might contain '?' (question marks). These will be replaced with the values of the fields defined by the second argument. The first '?' is replaced with the value of the first element and so on.

A backslash before a question mark will prevent it from being parsed.

EXTENDING FORMENGINE::DBSQL

Write A Handler For Another Datatype

Have a look at DtHandler.pm and read HTML::FormEngine::DBSQL::DtHandler.

Suiting the Layout

For this task you should create a new skin. For general information about FormEngine skins, have a look at HTML::FormEngine and its submodules. You should also read HTML::FormEngine::DBSQL::SkinClassic and its source code, the templates which are defined there are necessary for DBSQL.pm and you should at least implement replacements for them in your new skin. Use set_skin_obj to load your skin.

MORE INFORMATION

Have a look at ...

BUGS

Please use http://rt.cpan.org/NoAuth/Bugs.html?Dist=HTML-FormEngine-DBSQL to inform you about reported bugs and to report bugs.

If it doesn't work feel free to email directly to moritz@freesources.org.

Thanks!

AUTHOR

(c) 2003-2004, Moritz Sinn. This module is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License (see http://www.gnu.org/licenses/gpl.txt) as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.

    This module is distributed in the hope that it will be useful, but
    WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
    General Public License for more details.

I am always interested in knowing how my work helps others, so if you put this module to use in any of your own code please send me the URL. If you make modifications to the module because it doesn't work the way you need, please send me a copy so that I can roll desirable changes into the main release.

Please use http://rt.cpan.org/NoAuth/Bugs.html?Dist=HTML-FormEngine-DBSQL for comments, suggestions and bug reports. If it doesn't work feel free to mail to moritz@freesources.org.

CREDITS

Special thanks to Mark Stosberg, he helped a lot by reporting bugs, contributing new ideas and sending patches.

SEE ALSO

HTML::FormEngine by Moritz Sinn

HTML::FormTemplate by Darren Duncan