NAME

DBIx::Recordset - Perl extension for DBI recordsets

SYNOPSIS

  use DBIx::Recordset;

DESCRIPTION

DBIx::Recordset is a perl module, which should make it easier to access a set of records in a database. It should make standard database access (select/insert/update/delete) easier to handle (e.g. web application or scripts to enter/retrieve data to/from a database). Special attention is made for web applications to make it possible to handle the state-less access and to process the posted data of formfields. The programmer only has to supply the absolutely necessary information, the rest is done by DBIx::Recordset.

DBIx::Recordset use the DBI API to access the database, so it should work with every database for which a DBD driver is available (see also DBIx::Compat)

Most public functions take a hash reference as parameter, which makes it simple to supply various different arguments to the same function. The parameter hash can also be taken from a hash containing posted formfields like those available with CGI.pm, mod_perl, HTML::Embperl and others.

Before using a recordset it is necessary to setup an object. Of course the setup step can be made with same function call as the first database access, but can also be handled separately.

Most functions which setup an object returns a typglob. A typglob in perl is a object which holds pointers to all datatypes with the same name. Therefore a typglob must always have a name and can't be declared with my. You can only use it as global variable or declare it with local. The trick for using a typglob is that setup functions can return an reference to an object, an array and an hash at the same time.

The object is used to access the object methods, the array is used to access the records currently select in the recordset and the hash is used to access the current record.

If you don't like the idea of using typglobs you can also setup the object, array and hash separately, or just the ones you need.

ARGUMENTS

Since most methods take a hash reference as argument, here is first a description of the valid arguments.

Setup Parameters

All parameters starting with an '!' are only recognised at setup time. If you specify them in later function calls they will be ignored.

!DataSource

Driver/DB/Host. Same as the first parameter to the DBI connect function

!Table

Tablename, multiple tables are comma-separated

!Username

Username. Same as the second parameter to the DBI connect function.

!Password

Password. Same as the third parameter to the DBI connect function.

!DBIAttr

Reference to a hash which holds the attributes for the DBI connect function. See perldoc DBI for a detailed description.

!Fields

Fields which should be returned by a query. If you have specified multiple tables the fieldnames should be unique. If the names are not unique you must specify them among with the tablename (e.g. tab1.field).

NOTE 1: Fieldnames specified with !Fields can't be overridden. If you plan to use other fields with this object later, use $Fields instead.

NOTE 2: Because the query result is returned in a hash, there can only be one out of multiple fields with the same name fetched at once. If you specify multiple fields with the same name, only one is returned from a query. Which one this actually is, depends on the DBD driver.

NOTE 3: Some databases (e.g. mSQL) requires you to always qualify a fieldname with a tablename if more than one table is accessed in one query.

!TabRelation

Condition which describes the relation between the given tables. (e.g. tab1.id = tab2.id)

!PrimKey

Name of primary key. If specified, DBIx::Recordset assumes, that this is a unique key to the given table(s). DBIx::Recordset can not verify this; you are responsible for specifying the right key. If such a primary key exists in your table, you should specify it here, because it helps DBIx::Recordset the optimise the building of WHERE expressions.

!StoreAll

If present will cause DBIx::Recordset to store all rows which will be fetched between consecutive accesses, so it's possible to access data in a random order. (e.g. row 5, 2, 7, 1 etc.) If not specified rows will only be fetched into memory if requested, this means you have to access rows in an ascending order. (e.g. 1,2,3 if you try 3,2,4 you will get an undef for row 2 while 3 and 4 is ok) see also DATA ACCESS below

!HashAsRowKey

By default the hash with is returned by the setup functions is tied to the current record, i.e. you can use it to access the fields of the current record. If you set this parameter to true the hash will by tied to the whole database, this means that the key of the hash will be used as primary key in the table to select one row. (This parameter has only an effect on functions which return a typglob)

This parameter can be used to link multiple tables together. It takes a reference to a hash, which has as keys names for a special "linkfield" and as value a paramter hash. The paramter hash can contain all the Setup paramters. The setup paramters are taken to construct a new recordset object to access the linked table. If !DataSource is omitted (what it should normaly), the same DataSource (and database handle), as the main object is taken. There are special paramters which can only be occur in a link definition (see next paragraph). For a detail description how links are handled see "Accessing mulitple Tables" below.

!MainField

The !MailField parameter holds a fieldname which is used to retrive a key value for the search in the linked table from the main table. If obmitted it is set to the same value as !LinkedField.

!LinkedField

The fieldname which holds the key value in the linked table. If obmitted it is set to the same value as !MainField.

Where Parameters

The following parameters are used to build a SQL WHERE expression

{fieldname}

Value for field. The value will be automatically quoted if necessary.

'{fieldname}

Value for field. The value will always be quoted. This is only necessary if DBIx::Recordset cannot determine the correct type for a field.

#{fieldname}

Value for field. The value will never be quoted, but converted a to number. This is only necessary if DBIx::Recordset cannot determine the correct type for a field.

\{fieldname}

Value for field. The value will not be converted in any way i.e. you have to quote it before supplying it to DBIx::Recordset if necessary.

+{fieldname}|{fieldname}..

Values for multiple fields. The value must be in one/all fields depending on $compconj Example: '+name|text' => 'abc' will expand to name='abc' or text='abc'

$compconj

'or' or 'and' (default is 'or'). Specifies the conjunction between multiple fields. (see above)

$valuesplit

Regular expression for splitting a field value in multiple values (default is '\t') The conjunction for multiple values could be specified with $valueconj. Per default only one of the value must match the field.

 Example:
 'name' => "mouse\tcat" will expand to name='mouse' or name='cat'
$valueconj

'or' or 'and' (default is 'or'). Specifies the conjunction for multiple values.

$conj

'or' or 'and' (default is 'and') conjunction between fields

$operator

Default operator if not otherwise specified for a field. (default is '=')

*{fieldname}

Operator for the named field

 Example:
 'value' => 9, '*value' => '>' expand to value > 9

Search parameters

$start

First row to fetch. The row specified here will be appear as index 0 in the data array

$max

Maximum number of rows to fetch. Every attempt to fetch more rows the specified here will return undef, even if the select returns more rows.

$next

Add the number supplied with $max to $start. This intended to implement a next button.

$prev

Subtract the number supplied with $max from $start. This intended to implement a previous button.

$order

Fieldname(s) for ordering (comma separated, could also contain USING)

$fields

Fields which should be return by a query. If you have specified multiple tables the fieldnames should be unique. If the names are not unique you must specify them among with the tablename (e.g. tab1.field).

NOTE 1: If !fields is supplied at setup time, this can not be overridden by $fields.

NOTE: Because the query result is returned in a hash, there can only be one out of multiple fields with the same name fetched at once. If you specify multiple fields with same name, only one is returned from a query. Which one this actually is, depends on the DBD driver.

$primkey

Name of primary key. DBIx::Recordset assumes, that if specified this is a unique key to the given table(s). DBIx::Recordset can not verify this, you are responsible for specifying the right key. If such a primary exists in your table, you should specify it here, because it helps DBIx::Recordset the optimise the building of WHERE expressions.

See also !primkey

Execute parameters

The following parameter specify which action is to be executed

=search

search data

=update

update record(s)

=insert

insert record

=delete

delete record(s)

=empty

setup empty object

METHODS

*set = DBIx::Recordset -> Setup (\%params)

Setup a new object and connect to to a database and table(s). Collects information about the tables which are later needed. Returns a typglob which can be used to access the object ($set), an array (@set) and an hash (%set).

params: setup

$set = DBIx::Recordset -> SetupObject (\%params)

Same as above, but setup only the object, do not tie anything (no array, no hash)

params: setup

$set = tie @set, 'DBIx::Recordset', $set
$set = tie @set, 'DBIx::Recordset', \%params

Ties an array to an recordset object. The result of an query which is executed by the returned object can be accessed via the tied array. If array content is modified the database updated accordingly (see Data access below for more details) The first form ties the array to an already existing object, the second one setup a new object.

params: setup

$set = tie %set, 'DBIx::Recordset::Hash', $set
$set = tie %set, 'DBIx::Recordset::Hash', \%params

Ties a hash to an recordset object. The hash can be used to access/update/insert single rows of an table: the hash key is identical to the primary key value of the table. (see Data access below for more details)

The first form ties the hash to an already existing object, the second one sets up a new object.

params: setup

$set = tie %set, 'DBIx::Recordset::CurrRow', $set
$set = tie %set, 'DBIx::Recordset::CurrRow', \%params

Ties a hash to an recordset object. The hash can be used to access the fields of the current record of the recordset object. (see Data access below for more details)

The first form ties the hash to an already existing object, the second one sets up a new object.

params: setup

*set = DBIx::Recordset -> Select (\%params, $fields, $order)
$set -> Select (\%params, $fields, $order)
$set -> Select ($where, $fields, $order)

Selects records from the recordsets table(s)

The first syntax setups a new DBIx::Recordset object and does the select.

The second and third syntax selects into an existing DBIx::Recordset object.

params: setup (only syntax 1), where (without $order and $fields)

where: (only syntax 3) string for SQL WHERE expression

fields: comma separated list of fieldnames to select

order: comma separated list of fieldnames to sort on

*set = DBIx::Recordset -> Search (\%params)
set -> Search (\%params)

Does a search on the given tables and makes data ready to access them via @set or %set. First syntax also setup a new object.

params: setup (only syntax 1), where, search

*set = DBIx::Recordset -> Insert (\%params)
$set -> Insert (\%params)

Insert a new record in the recordset table(s). Params should contain one entry for every field you want to insert a value.

params: setup (only syntax 1), fields

*set = DBIx::Recordset -> Update (\%params, $where)
*set = DBIx::Recordset -> Update (\%params, $where)
set -> Update (\%params, $where)
set -> Update (\%params, $where)

Updates one or more record in the recordset table(s). Params should contain one entry for every field you want to update. The $where contains the SQL WHERE condition as string or as reference to a hash. If $where is omitted the where conditions are build from the params.

params: setup (only syntax 1+2), where (only if $where is omitted), fields

*set = DBIx::Recordset -> Delete (\%params)
$set -> Delete (\%params)

Deletes one or more records form the recordsets table(s)

params: setup (only syntax 1), where

*set = DBIx::Recordset -> Execute (\%params)
$set -> Execute (\%params)

Executes one of the above methods, depending on the given arguments. If multiple execute parameter are specified the priority is =search =update =insert =delete =empty

If none of the above parameters are specified, a search is performed.

params: setup (only syntax 1), execute, where, search, fields

$set -> do ($statement, $attribs, \%params)

Same as DBI do. Executes a single SQL statement on the open database.

$set -> First ()

Position the record pointer to the first row

$set -> Next ()

Position the record pointer to the next row

$set -> Prev ()

Position the record pointer to the previous row

$set -> AllNames ()

Returns an reference to an array of all fieldnames of all tables used by the object

$set -> Names ()

Returns an reference to an array of the fieldnames from the last query.

$set -> AllTypes ()

Returns an reference to an array of all fieldtypes of all tables used by the object

$set -> Types ()

Returns an reference to an array of the fieldtypes from the last query.

$set -> Add ()
$set -> Add (\%data)

Adds a new row to a recordset. The first one add an empty row, the second one will assign initial data to it. The Add method returns an index into the array where the new record is located.

  Example:

  # Add an empty record
  $i = $set -> Add () ;
  # Now assign some data
  $set[$i]{id} = 5 ;
  $set[$i]{name} = 'test' ;
  # and here it is written to the database
  # (without Flush it is written, when the record goes out of scope)
  $set -> Flush () ;

Add will also set the current record to the newly created empty record. So you can assign the data by simply using the current record.

  # Add an empty record
  $set -> Add () ;
  # Now assign some data to the new record
  $set{id} = 5 ;
  $set{name} = 'test' ;
$set -> MoreRecords ([$ignoremax])

Returns true if there are more records to fetch from the current recordset. If the $ignoremax parameter is specified and is true More ignores the $max parameter of the last Search.

To tell you if there are more records More actually fetch the next record from the database and stores it in memory, but it does not change the current record.

$set -> PrevNextForm ($prevtext, $nexttext, \%fdat)

Returns a HTML form which contains a previous and a next button and all data from %fdat, as hidden fields. When callikng the Search method, You must set the $max parameter to the number of rows you want to see at once. After the search and the retrieval of the rows, you can call PrevNextForm to generate the needed buttons for scrolling thru the recordset.

$set -> Flush

The Flush method flushes all data to the database and therefor makes sure that the db is uptodate. Normaly DBIx::Recordset holds the updates in memory until the row is destroyed, by either a new Select/Search or by the Recordsetobject itself is destroyed. With this method you can make sure that every update is really written to the db.

DBIx::Recordset::Undef ($name)

Undef takes the name of a typglob and will destroy the array, the hash and the object. All unwritten data is written to the db, all db connections are closed and all memory is freed.

  Example:
  # this destroys $set, @set and %set
  DBIx::Recordset::Undef ('set') ;
$set -> DBHdl ()

Returns the DBI database handle

$set -> StHdl ()

Returns the DBI statement handle of the last select

$set -> StartRecordNo ()

Returns the record no of the record which will be returned for index 0

$set -> LastSQLStatement ()

Returns the last executed SQL Statement

$set -> Disconnect ()

Close the connection to the database

DATA ACCESS

The data which is returned by a Select or a Search can be accessed in two ways:

1.) Through an array. Each item of the array corresponds to one of the selected records. Each array-item is a reference to a hash containing an entry for every field.

Example: $set[1]{id} access the field 'id' of the second record found $set[3]{name} access the field 'name' of the third record found

The record is fetched from the DBD driver when you access it the first time and is stored by DBIx::Recordset for later access. If you don't access the records one after each other, the skipped records are not stored and therefore can't be accessed anymore, unless you specify the !StoreAll parameter.

2.) DBIx::Recordset holds a currecnt record which can be accessed directly via a hash. The current record is the one you last access via the array. After a Select or Search it's reset to the first record. You can change the current record via the methods Next, Prev, First, Add.

Example: $set{id} access the field 'id' of the current record $set{name} access the field 'name' of the current record

Instead of doing a Select or Search you can directly access one row of a table when you have tied n hash to DBIx::Recordset::Hash or have specified the !HashAsRowKey Parameter. The hashkey will work as primary key to the table. You must specify the !PrimKey as setup parameter.

Example: $set{4}{name} access the field 'name' of the row with primary key = 4

MODIFYING DATA DIRECTLY

One way to update/insert data into the database is by using the Update, Insert or Execute method of the DBIx::Recordset object. A second way is to directly assign new values to the result of a previous Select/Search.

Example: # setup an new object and serach all records with name xyz *set = DBIx::Recordset -> Search ({'!DataSource' => 'dbi:db:tab', '!PrimKey => 'id', '!Table' => 'tabname', 'name' => 'xyz'}) ;

  #now you can update an existing record by assigning new values
  #Note: if possible specify a PrimKey for update to work faster
  $set[0]{'name'} = 'zyx' ;

  # or insert a new record by setting up an new array row
  $set[9]{'name'} = 'foo' ;
  $set[9]{'id'}   = 10 ;

  # if you don't know the index of a new row you can obtain
  # one by using Add
  my $i = $set -> Add () ;
  $set[$i]{'name'} = 'more foo' ;
  $set[$i]{'id'}   = 11 ;

  # or add an empty record via Add and assign the values to the current
  # reocord
  $set -> Add () ;
  $set{'name'} = 'more foo' ;
  $set{'id'}   = 11 ;

  # or insert the data directly via Add
  $set -> Add ({'name' => 'even more foo',
                'id'   => 12}) ;

  # NOTE: until here NO data is actualy written to the db!

  # we are done with that object,  Undef will flush all data to the db
  DBIx::Reocrdset::Undef ('set') ;

IMPORTANT: The data is not written to the database until you explicitly call flush, an new query is started or the object is destroyed. This will keep the actual writes to the database to a minimum.

WORKING WITH MULTIPLE TABLES

DBIx::Recordset has some nice features to make working with multiple tables and their relationen easier. First of all you can specify more then one table to the !Table parameter. If you do so you need to specifiy how both tables are related. You do this with !TabRelation parameter. This method will access all the specified tables simultanouly.

Example:

If you have the following two tables, where the field streetid is an pointer into the table street:

  table name
  name      char (30),
  streetid  integer

  table street
  id        integer,
  street    char (30)

You can perform the following search:

  *set = DBIx::Recordset -> Search ({'!DataSource' => 'dbi:drv:db',
                     '!Table'      => 'name, street',
                     '!TabRelation'=> 'name.streetid = street.id'}) ;

The result is that you get a set which contains the fields name, streetid, street and id, where id is always equal to streetid. If there are multiple streets for one name, you will get as much records for that name as streets present for it. For this reason this aproach works best when you have an 1:1 relation. If you have 1:n relations between two tables the following may be a better way to handle it:

  *set = DBIx::Recordset -> Search ({'!DataSource' => 'dbi:drv:db',
                     '!Table'      => 'name',
                     '!Links'      => {
                        streetlink => {
                            '!Table' => 'street',
                            '!LinkedField' => 'id',
                            '!MainField'   => 'streetid'
                            }
                        }
                    }) ;

After that query every record will contain the fields name and streetid. Additionaly there is a pseudofield named streetlink, which could be used to access another recordset object, which is the result of an query where streetid = id. Use

  $set{name} to access the name field
  $set{streetlink}{street} to access the first street (as long as the
                                    current record of the subobject isn't
                                    modified)

  $set{streetlink}[0]{street}   first street
  $set{streetlink}[1]{street}   second street
  $set{streetlink}[2]{street}   third street

  $set[2]{streetlink}[1]{street} to access the second street of the
                                    thrid name

You can have multiple linked tables in one recordset, also you can nest linked tables or can link a table to itself.

DEBUGING

DBIx::Recordset is able to write a logfile, so you can see what's happening inside. There are two public variables use for this purpose:

$DBIx::Recordset::Debug

Debuglevel 0 = off 1 = some infos (mainly the SQL Statements) 2 = much infos

DBIx::Recordset::LOG

Filehandle which is used for logging. Default is STDERR, if you running under HTML::Embperl the default is the Embperl logfile.

 Example:

    # open the log file
    open LOG, "test.log" or die "Cannot open test.log" ; 

    # assign filehandle
    *DBIx::Recordset::LOG = \*LOG ; 
    
    # set debugging level
    $DBIx::Recordset::Debug = 2 ; 

    # now you can create a new DBIx::Recordset object

SECURITY

Since on possible application of DBIx::Recordset is using in a web-server environment, some attention should paid to security issues.

The current version of DBIx::Recordset does not have an extended security management but some features could make your database access safer. (more security features will come in further releases).

First of all, use the security feature of your database. Assign the web server process as few rights as possible.

The greatest security risk is when you feed DBIx::Recordset a hash which contains the formfield data posted to the web server. Somebody who knows DBIx::Recordset can post other parameters than you expected. So the first issues is to override all parameters which should not be posted by your script.

Example: *set = DBIx::Recordset -> Search ({%fdat, ('!DataSource' => "dbi:$Driver:$DB", '!Table' => "$Table")}) ;

Assuming your posted form data is in %fdat. The above call will make sure nobody from outside can override the values supplied by $Driver, $DB and $Table.

The second approach is to presetup your objects supplying the parameters which should not change.

Somewhere in your script startup (or at server startup time) add a setup call:

 *set = DBIx::Recordset-> setup ({'!DataSource'  =>  "dbi:$Driver:$DB",
                                                '!Table'          =>  "$Table",
                                                '!Fields'         =>  "a, b, c"}) ;

Later when you process a request you can write:

 $set -> Search (\%fdat) ;

This will make sure that only the database specified by $Driver, $DB, the table specified by $Table and the Fields a, b, c can be accessed.

Compability with differnet DBD drivers

I have made a great effort to make DBIx::Recordset run with various DBD drivers. The problem is that not all necessary information is specified via the DBI interface (yet). So I have made the module DBIx::Compat which gives information about the difference of the various DBD drivers. Currently there are definitions for:

DBD::mSQL
DBD::mysql
DBD::Pg
DBD::Solid
DBD::ODBC
DBD::CSV

DBIx::Recordset has been tested with all those DBD drivers (on linux 2.0.32, execpt DBD::ODBC which has been tested on Windows '95 using Access 7).

If you want to use another DBD driver with DBIx::Recordset, it may neccesary to create an entry for that driver. See perldoc DBIx::Compat for more information.

EXAMPLES

The following show some examples of how to use DBIx::Recordset. The Examples are from the test.pl. The examples show the DBIx::Recordset call first, followed by the generated SQL command.

 *set = DBIx::Recordset-> setup ({'!DataSource'  =>  "dbi:$Driver:$DB",
                                            '!Table'      =>  "$Table"}) ;

Setup an DBIx::Recordset for driver $Driver, database $DB to access table $Table.

 $set -> Select () ;

 SELECT * form <table> ;


 $set -> Select ({'id'=>2}) ;
 is the same as
 $set1 -> Select ('id=2') ;

 SELECT * form <table> WHERE id = 2 ;


 $set -> Select ({name => "Second Name\tFirst Name"}) ;

 SELECT * from <table> WHERE name = 'Second Name' or name = 'First Name' ;


 $set1 -> Select ({value => "9991 9992\t9993",
                       '$valuesplit' => ' |\t'}) ;

 SELECT * from <table> WHERE value = 9991 or value = 9992 or value = 9993 ;


 $set -> Select ({'+name&value' => "9992"}) ;

 SELECT * from <table> WHERE name = '9992' or value = 9992 ;


 $set -> Select ({'+name&value' => "Second Name\t9991"}) ;

 SELECT * from <table> WHERE (name = 'Second Name' or name = '9991) or
                            (value = 0 or value = 9991) ;


 $set -> Search ({id => 1,name => 'First Name',addon => 'Is'}) ;

 SELECT * from <table> WHERE id = 1 and name = 'First Name' and addon = 'Is' ;


 $set1 -> Search ({'$start'=>0,'$max'=>2, '$order'=>'id'})  or die "not ok 
($DBI::errstr)" ;

 SELECT * form <table> ORDER BY id ;
 B<Note:> Because of the B<start> and B<max> only the record 0,1 will be returned


 $set1 -> Search ({'$start'=>0,'$max'=>2, '$next'=>1, '$order'=>'id'})  or die "not ok 
($DBI::errstr)" ;

 SELECT * form <table> ORDER BY id ;
 B<Note:> Because of the B<start>, B<max> and B<next> only the record 2,3 will be 
returned


 $set1 -> Search ({'$start'=>2,'$max'=>1, '$prev'=>1, '$order'=>'id'})  or die "not ok 
($DBI::errstr)" ;

 SELECT * form <table> ORDER BY id ;
 B<Note:> Because of the B<start>, B<max> and B<prev> only the record 0,1,2 will be 
returned


 $set1 -> Search ({'$start'=>5,'$max'=>5, '$next'=>1, '$order'=>'id'})  or die "not ok 
($DBI::errstr)" ;

 SELECT * form <table> ORDER BY id ;
 B<Note:> Because of the B<start>, B<max> and B<next> only the record 5-9 will be 
returned


 *set6 = DBIx::Recordset -> Search ({  '!DataSource'   =>  "dbi:$Driver:$DB",
                                                        '!Table'            =>  "t1, t2",
                                                        '!TabRelation'  =>
        "t1.value=t2.value",
                                        '!Fields'       =>  'id, name, text',
                                        'id'            =>  "2\t4" }) or die "not ok 
($DBI::errstr)" ;

 SELECT id, name, text FROM t1, t2 WHERE (id=2 or id=4) and t1.value=t2.value ;


 $set6 -> Search ({'name'            =>  "Fourth Name" }) or die "not ok 
($DBI::errstr)" ;
 SELECT id, name, text FROM t1, t2 WHERE (name = 'Fourth Name') and t1.value=t2.value 
;



 $set6 -> Search ({'id'            =>  3,
                  '$operator'     =>  '<' }) or die "not ok ($DBI::errstr)" ;

 SELECT id, name, text FROM t1, t2 WHERE (id < 3) and t1.value=t2.value ;


 $set6 -> Search ({'id'            =>  4,
                  'name'          =>  'Second Name',
                  '*id'           =>  '<',
                  '*name'         =>  '<>' }) or die "not ok ($DBI::errstr)" ;

 SELECT id, name, text FROM t1, t2 WHERE (id<4 and name <> 'Second Name') and 
t1.value=t2.value ;


 $set6 -> Search ({'id'            =>  2,
                  'name'          =>  'Fourth Name',
                  '*id'           =>  '<',
                  '*name'         =>  '=',
                  '$conj'         =>  'or' }) or die "not ok ($DBI::errstr)" ;

 SELECT id, name, text FROM t1, t2 WHERE (id<2 or name='Fourth Name') and 
t1.value=t2.value ;


 $set6 -> Search ({'+id|addon'     =>  "7\tit",
                  'name'          =>  'Fourth Name',
                  '*id'           =>  '<',
                  '*addon'        =>  '=',
                  '*name'         =>  '<>',
                  '$conj'         =>  'and' }) or die "not ok ($DBI::errstr)" ;

 SELECT id, name, text FROM t1, t2 WHERE (t1.value=t2.value) and (  ((name <> Fourth 
Name)) and (  (  id < 7  or  addon = 7)  or  (  id < 0  or  addon = 0)))


 $set6 -> Search ({'+id|addon'     =>  "6\tit",
                  'name'          =>  'Fourth Name',
                  '*id'           =>  '>',
                  '*addon'        =>  '<>',
                  '*name'         =>  '=',
                  '$compconj'     =>  'and',
                  '$conj'         =>  'or' }) or die "not ok ($DBI::errstr)" ;


 SELECT id, name, text FROM t1, t2 WHERE (t1.value=t2.value) and (  ((name = Fourth 
Name)) or (  (  id > 6 and addon <> 6)  or  (  id > 0 and addon <> 0))) ;


 *set7 = DBIx::Recordset -> Search ({  '!DataSource'   =>  "dbi:$Driver:$DB",
                                    '!Table'        =>  "t1, t2",
                                    '!TabRelation'  =>  "t1.id=t2.id",
                                    '!Fields'       =>  'name, typ'}) or die "not ok 
($DBI::errstr)" ;

 SELECT name, typ FROM t1, t2 WHERE t1.id=t2.id ;


 %h = ('id'    => 22,
      'name2' => 'sqlinsert id 22',
      'value2'=> 1022) ;


 *set9 = DBIx::Recordset -> Insert ({%h,
                                    ('!DataSource'   =>  "dbi:$Driver:$DB",
                                     '!Table'        =>  "$Table[1]")}) or die "not ok 
($DBI::errstr)" ;

 INSERT INTO <table> (id, name2, value2) VALUES (22, 'sqlinsert id 22', 1022) ;


 %h = ('id'    => 22,
      'name2' => 'sqlinsert id 22u',
      'value2'=> 2022) ;


 $set9 -> Update (\%h, 'id=22') or die "not ok ($DBI::errstr)" ;

 UPDATE <table> WHERE id=22 SET id=22, name2='sqlinsert id 22u', value2=2022 ;


 %h = ('id'    => 21,
      'name2' => 'sqlinsert id 21u',
      'value2'=> 2021) ;

 *set10 = DBIx::Recordset -> Update ({%h,
                                    ('!DataSource'   =>  "dbi:$Driver:$DB",
                                     '!Table'        =>  "$Table[1]",
                                     '!PrimKey'      =>  'id')}) or die "not ok 
($DBI::errstr)" ;

 UPDATE <table> WHERE id=21 SET name2='sqlinsert id 21u', value2=2021 ;


 %h = ('id'    => 21,
      'name2' => 'Ready for delete 21u',
      'value2'=> 202331) ;


 *set11 = DBIx::Recordset -> Delete ({%h,
                                    ('!DataSource'   =>  "dbi:$Driver:$DB",
                                     '!Table'        =>  "$Table[1]",
                                     '!PrimKey'      =>  'id')}) or die "not ok 
($DBI::errstr)" ;

 DELETE FROM <table> WHERE id = 21 ;



 *set12 = DBIx::Recordset -> Execute ({'id'  => 20,
                                   '*id' => '<',
                                   '!DataSource'   =>  "dbi:$Driver:$DB",
                                   '!Table'        =>  "$Table[1]",
                                   '!PrimKey'      =>  'id'}) or die "not ok 
($DBI::errstr)" ;

 SELECT * FROM <table> WHERE id<20 ;


 *set13 = DBIx::Recordset -> Execute ({'=search' => 'ok',
                    'name'  => 'Fourth Name',
                    '!DataSource'   =>  "dbi:$Driver:$DB",
                    '!Table'        =>  "$Table[0]",
                    '!PrimKey'      =>  'id'}) or die "not ok ($DBI::errstr)" ;

 SELECT * FROM <table>  WHERE   ((name = Fourth Name))


 $set12 -> Execute ({'=insert' => 'ok',
                    'id'     => 31,
                    'name2'  => 'insert by exec',
                    'value2'  => 3031,
 # Execute should ignore the following params, since it is already setup
                    '!DataSource'   =>  "dbi:$Driver:$DB",
                    '!Table'        =>  "quztr",
                    '!PrimKey'      =>  'id99'}) or die "not ok ($DBI::errstr)" ;

 SELECT * FROM <table> ;


 $set12 -> Execute ({'=update' => 'ok',
                    'id'     => 31,
                    'name2'  => 'update by exec'}) or die "not ok ($DBI::errstr)" ;

 UPDATE <table> SET name2=update by exec,id=31 WHERE id=31 ;


 $set12 -> Execute ({'=insert' => 'ok',
                    'id'     => 32,
                    'name2'  => 'insert/upd by exec',
                    'value2'  => 3032}) or die "not ok ($DBI::errstr)" ;


 INSERT INTO <table> (name2,id,value2) VALUES (insert/upd by exec,32,3032) ;


 $set12 -> Execute ({'=delete' => 'ok',
                    'id'     => 32,
                    'name2'  => 'ins/update by exec',
                    'value2'  => 3032}) or die "not ok ($DBI::errstr)" ;

 DELETE FROM <table> WHERE id=32 ;

SUPPORT

As far as possible for me support will be available via the DBI Users mailing list. (dbi-user@fugue.com)

AUTHOR

G.Richter (richter@dev.ecos.de)

SEE ALSO

perl(1) =item DBI(3) =item DBIx::Compat(3) =item HTML::Embperl(3) http://perl.apache.org/embperl/ =item Tie::DBI(3) http://stein.cshl.org/~lstein/Tie-DBI/

15 POD Errors

The following errors were encountered while parsing the POD:

Around line 2471:

'=item' outside of any '=over'

Around line 2554:

You forgot a '=back' before '=head2'

Around line 2556:

'=item' outside of any '=over'

Around line 2568:

You forgot a '=back' before '=head2'

Around line 2572:

'=item' outside of any '=over'

Around line 2632:

You forgot a '=back' before '=head2'

Around line 2634:

'=item' outside of any '=over'

Around line 2684:

You forgot a '=back' before '=head2'

Around line 2688:

'=item' outside of any '=over'

Around line 2709:

You forgot a '=back' before '=head1'

Around line 2712:

'=item' outside of any '=over'

Around line 2993:

You forgot a '=back' before '=head1'

Around line 3232:

'=item' outside of any '=over'

Around line 3256:

You forgot a '=back' before '=head1'

Around line 3532:

'=item' outside of any '=over'

=over without closing =back