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

1.3.6 documentation
More infos
Add info about Embperl

    Stable 2.4.0
    Beta 2.5.0_3
Support the development of Embperl! More...
[ << Prev: WORKING WITH MULTIPLE TABLES ] [ Content ] [ Next: Casesensitive/insensitiv >> ]

The DBIx::Database object gathers information about a datasource. Its main purpose is to create, at startup, an object which retrieves all necessary information from the database. This object detects links between tables and stores this information for use by the DBIx::Recordset objects. There are additional methods which allow you to add kinds of information which cannot be retreived automatically.


  $db = DBIx::Database -> new ({'!DataSource'   =>  $DSN,
		                '!Username'     =>  $User,
				'!Password'     =>  $Password,
                                '!KeepOpen'     => 1}) ;

   *set = DBIx::Recordset -> Search ({'!DataSource'   =>  $db,
			              '!Table'        =>  'foo',
				     })  ;

new ($data_source, $username, $password, \%attr, $saveas, $keepopen)top



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




Username (optional)




Password (optional)




Attributes (optional) Same as the attribute parameter to the DBI connect function.




Name for this DBIx::Database object to save as. The name can be used in DBIx::Database::Get, or as !DataSource parameter in call to the DBIx::Recordset object.

This is intended as mechanism to retrieve the necessary metadata; for example, when your web server starts (e.g. in the startup.pl file of mod_perl). Here you can give the database object a name. Later in your mod_perl or Embperl scripts, you can use this metadata by specifying this name. This will speed up the setup of DBIx::Recordset object without the need to pass a reference to the DBIx::Database object.




Normaly the database connection will be closed after the metadata has been retrieved from the database. This makes sure you don't get trouble when using the new method in a mod_perl startup file. You can keep the connection open to use them in further setup calls to DBIx::Recordset objects. When the database is not kept open, you must specify the !Password parameter each time the recordset has to be reopend.




same as setup parameter !TableFilter




same as setup parameter !DoOnConnect




If set, forces DBIx::Database to undef any preexisting database handle and call connect in any case. This is usefull in together with Apache::DBI. While the database connection are still kept open by Apache::DBI, Apache::DBI preforms a test if the handle is still vaild (which DBIx::Database itself wouldn't).

You also can specify a hashref which can contain the following parameters:

!DataSource, !Username, !Password, !DBIAttr, !SaveAs, !KeepOpen, !TableFilter, !DoOnConnect, !Reconnect

$db = DBIx::Database -> DBHdltop

returns the database handle (only if you specify !KeepOpen when calling new).

$db = DBIx::Database -> Get ($name)top

$name = The name of the DBIx::Database object you wish to retrieve

Get a DBIx::Database object which has already been set up based on the name.

$db -> TableAttr ($table, $key, $value)top

get and/or set an attribute for an specfic table.




Name of table(s). You may use '*' instead of the table name to specify a default value which applies to all tables for which no other value is specified.




key to set/get




if present, set key to this value

$db -> TableLink ($table, $linkname, $value)top

Get and/or set a link description for an table. If no $linkname is given, returns all links for that table.




Name of table(s)




Name of link to set/get




if present, this must be a reference to a hash with the link decription. See !Links for more information.

$db -> MetaData ($table, $metadata, $clear)top

Get and/or set the meta data for the given table.




Name of table(s)




If present, this must be a reference to a hash with the new metadata. You should only use this if you really know what you are doing.




Clears the metadata for the given table, The next call to DBIx::Database -> new will recreate the metadata. Useful if your table has changed (e.g. by ALTER TABLE).

$db -> AllTablestop

This returns a reference to a hash of the keys to all the tables of the datasource.

$db -> AllNames ($table)top

Returns a reference to an array of all fieldnames for the given table.

$db -> AllTypes ($table)top

Returns a reference to an array of all fieldtypes for the given table.

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

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

$db -> CreateTables ($dbschema, $schemaname, $user, $setpriv, $alterconstraints)top

The CreateTables method is used to create an modify the schema of your database. The idea is to define the schema as a Perl data structure and give it to this function, it will compare the actual schema of the database with the one provided and creates new tables, new fields or drop fields as neccessary. It also sets the permission on the tables and is able to create indices for the tables. It will never drop a whole table! NOTE: Create tables cannot deteminate changes of the datatype of a fields, because DBI is not able to provide this information in a standart way.




Either the name of a file which contains the schema or a array ref. See below how this schema must look like.




schemaname (only used for Oracle)




User that should be granted access. See !Grant parameter.




If set to true, access privilegs are revoked and granted again for already existing tables. That is necessary when $user changes.




If set to true contrains are cleared/set for already existing fields. DBI doesn't provide a database independ way to check which contrains already exists.

Schema definitiontop

If give as a filename, the file must contain an hash %DBDefault and an array @DBSchema. The first gives default and the second is an array of hashs. Every of this hash defines one table.


  %DBDefault = 

    '!Grant' => 
  @DBSchema = (

    '!Table' => 'language',
    '!Fields' => 
        'id'            => 'char (2)',
        'directory'     => 'varchar(40)',
        'name'          => 'varchar(40)',
        'europe'        => 'bool', 
    '!PrimKey' => 'id',
    '!Default' =>
        'europe'    => 1,
    '!Init' =>
        {'id' => 'de', 'directory' => 'html_49', 'name' => 'deutsch'},
        {'id' => 'en', 'directory' => 'html_76', 'name' => 'english'},
        {'id' => 'fr', 'directory' => 'html_31', 'name' => 'french'},
   '!Index' =>
        'directory' => '',


The hash which defines a table can have the following keys:




Gives the table name




Array with field names and types. There a some types which a translated database specifc. You can define more database specific translation in Compat.pm.








If an autoincrementing integer. For databases (like Oracle) that doesn't have such a datatype a sequence is generated to provide the autoincrement value and the fields will be of type integer.




variables length text with up to 255 characters




variables length text




Name of the primary key




Can contain the same key as the table definintion, but is only executed for a specifc database.


    '!For' => { 
        'Oracle' => {
            '!Constraints' =>
                'web_id'           => ['foreign key' => 'REFERENCES web (id)'],

                'prim__menu_id'    => ['!Name'       => 'web_prim_menu_id',
                                       'foreign key' => 'REFERENCES menu (id)',
                                       'not null'    => ''],



Used to define contraints. See example under !For.


!Name => <name>


<constraint> => <second part>




Used to initialy populate the table.




Used to set a default value for a field, when the table is created. This doesn't have any affect for further INSERTs/UPDATEs.




Give the rights that should be grant to $user




Gives the names for the fields for which indices should be created. If the second parameter for an index is not empty, it gives the index name, otherwise a default name is used.

$db -> DropTables ($schemaname, $user)top

Drops all tables. Use with care!




schemaname (only used for Oracle)




User that should be revoked access. See !Grant parameter.

[ << Prev: WORKING WITH MULTIPLE TABLES ] [ Content ] [ Next: Casesensitive/insensitiv >> ]

© 1997-2012 Gerald Richter / ecos gmbh