NAME

Gtk2::Ex::DbLinker::DbiDataManager - a module that get data from a database using DBI and sql commands

VERSION

See Version in Gtk2::Ex::DbLinker::DbTools

SYNOPSIS

	use DBI;
	use Gtk2 -init;
	use Gtk2::GladeXML;
	use Gtk2::Ex:Linker::DbiDataManager; 

	my $dbh = DBI->connect (
                          "dbi:mysql:dbname=sales;host=screamer;port=3306",
                          "some_username",
                          "salespass", {
                                           PrintError => 0,
                                           RaiseError => 0,
                                           AutoCommit => 1,
                                       }
	);
	 my $builder = Gtk2::Builder->new();
	 $builder->add_from_file($path_to_glade_file);

To fetch the data from the a whole table

  my $rdbm = Gtk2::Ex::DbLinker::DbiDataManager->new(
	 	dbh => $dbh,
		sql =>{from => "mytable",
		select => "pk_id, field1, field2, field3",
		where => "1=1",
	},
 );

To link the data with a Gtk windows, have the Gtk entries ID, or combo ID in the xml glade file set to the name of the database fields: pk_id, field1, field2...

  $self->{form} = Gtk2::Ex::DbLinker::Form->new( 
	    data_manager => $rdbm,
	    builder =>  $builder,
	   ...
    );

To add a combo box in the form:

  my $dman = Gtk2::Ex::DbLinker::DbiDataManager->new(
		dbh => $dbh,
		sql => {
			select => "id, name",
			from => "table",
			order_by => "name ASC",
			where => "1=1",
			},
	);

The first field given in the select value will be used as the return value of the combo. noed is the Gtk2combo id in the glade file and the field's name in the table displayed in the form.

$self->{form}->add_combo(
	data_manager => $dman,
	id => 'noed',
  );

And when all combos or datasheets are added:

$self->{form}->update;

To change a set of rows in a subform, listen to the on_changed event of the primary key in the main form:

$self->{subform_a}->on_pk_changed($new_primary_key_value);

In the subform_a module:

sub on_pk_changed {
	 my ($self,$value) = @_;
	$self->{jrn_coll}->get_data_manager->query( where =>"pk_value_of_the_bound_table = ?", 
							bind_values => [ $value ],
						   );
	...
	}

DESCRIPTION

This module fetches data from a dabase using DBI and sql commands. A new instance is created using a database handle and sql string and this instance is passed to a Gtk2::Ex::DbLinker::Form object or to Gtk2::Ex::DbLinker::Datasheet objet constructors.

METHODS

constructor

The parameters to new are passed as a list of parameters name => values, or as a hash reference with the parameters name as keys.

Paramters are dbh, sql, primary_keys, ai_primary_key.

The value for primary_keys and ai_primary_key are arrayrefs holding the field names of the primary key and auto incremented primary keys. If the table use a autogenerated key, use ai_primary_key instead of primary_keys to set these. dbh, sql are mandatory. The value for sql is a hash reference with the following keys : select or select_distinct, from, where, order_by, bind_values, pass_through.

The constructor parameters are described below:

dbh: a DBI database handle. Mandatory.
aperture: default to 1. All the primary keys for a given where clause are retrieved but only aperture rows are fetch. Using a aperture of 50 speed up the code if the rows contain data that does not change.
before_query : a code ref to be run at the start of the query method.
primary_keys : an array ref of the primary key(s) name. With Mysql these are correctly detected.
ro_fields : an array fef of read only fields from joined tables that are not to be included in update and insert sql statement
ai_primary_key : an array ref of the auto incremeted primary key name. With Mysql this is corretly detected. You may pass ai_primary_key as undef to indicate that the primary key is not auto incrementing
defaults : a hash ref of fieldname => default value;

=item*

sql a hash ref with the following keys:
- select or select_distinct : a comma delimited string of the field names, default to * if left unspecified. Field aliasing is supported.
- from : a string of the join clause. Mandatory unless you use pass_through
- where : a string of the where clause. Use place holders if the bind_values keys is set. If left unspecified, no rows will be retrieved, but query with a hash ref describing a where clause can still be called on an data manager object.
- order_by : a string of the order by clause.
- bind_values : a array ref of the values corresponding to the place holders in the where clause.
- pass_through : an sql string that will be executed in the DB. Rows are read only, and can't be deleted or added. The query method can't be used to fetch a new set of rows.

Gtk2::Ex::DbLinker::DbiManager->new( dbh => $dbh,
				    sql => {
						select_distinct => "abo.ref as ref, ...",
						from   => "abo INNER JOIN jrnabt ON abo.noabt = jrnabt.noabt",
						where  => "nofm=?",
						order_by =>"abo.type ASC, abo.ref ASC",
						bind_values=> [ $nofm_value ],
					}
			);

`query( where =` "pk=?" , bind_values=>[ $value ] );

To display an other set of rows in a form, call the query method on the datamanager instance for this form. Return the number of rows retrieved.

my $dman = $self->{form_a}->get_data_manager();

$dman->query(where=>"nofm=?", bind_values=>[ $f->{nofm} ]);

$self->{form_a}->update;

query will not place the recordset cursor, but in the above example update on the Form (or a Datasheet) instance will. Be sure to call set_row_pos(0) on the datamanager object after query( ... ) in others situations.

The parameter of the query method is a list of parmaters name => values, or a hash reference with the keys as parameters name.

Parameters are:

where : a string of the where clause, with placeholder if the bind_values array is set.
bind_values : a array reference holding the value(s) corresponding to the placeholders of the where clause.

`save();`

Build the sql commands tu insert a new record or update an existing record. Fetch the value from auto_incremented primary key.

`save( $field_name =` $value );>

Pass a list or a hash reference as a parameter when a value has to be saved in the database without using $dman-set_field($ field, $value ) >. Use this when you want to change a field that is part of a multiple fields primary key.

`new_row();`

You may pass defaults in the constructor to pass default values as a hash ref {field name => value} that will be set in a new row. You have to use save on the datamanager to store the row in the database.

`delete();`

Delete the row at the current position.

`set_row_pos( $new_pos);`

Change the current row for the row at position $new_pos.

`get_row_pos( );`

Return the position (zero based) of the current row.

`set_field ( $field_id, $value);`

Sets $value in $field_id. undef as a value will set the field to null.

`get_field ( $field_id );`

return the value of the field $field_id or undef if null.

`get_field_type ( $field_id);`

Return one of varchar, char, integer, date, serial, boolean, text.

`row_count();`

Return the number of rows.

`get_field_names();`

Return an array of the field names.

`get_primarykeys()`;

Return an array of primary key(s) (auto incremented or not).

`get_autoinc_primarykeys();`

Return an array of auto incremented primary key(s). If the names are not supplied to the constructor, the array of primary keys is returned.

SUPPORT

Any Gk2::Ex::DbLinker::DbiDataManager questions or problems can be posted to me (rappazf) on my gmail account.

The current state of the source can be extract using Mercurial from http://sourceforge.net/projects/gtk2-ex-dblinker/.

AUTHOR

François Rappaz <rappazf@gmail.com>

COPYRIGHT AND LICENSE

CREDIT

Daniel Kasak, whose code have inspired this module.

See Gtk2::Ex::DBI

To install Gtk2::Ex::DbLinker::DbTools, copy and paste the appropriate command in to your terminal.

cpanm

cpanm Gtk2::Ex::DbLinker::DbTools

CPAN shell

perl -MCPAN -e shell
install Gtk2::Ex::DbLinker::DbTools

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

VERSION

SYNOPSIS

DESCRIPTION

METHODS

constructor

query( where = "pk=?" , bind_values=>[ $value ] );

save();

save( $field_name = $value );>

new_row();

delete();

set_row_pos( $new_pos);

get_row_pos( );

set_field ( $field_id, $value);

get_field ( $field_id );

get_field_type ( $field_id);

row_count();

get_field_names();

get_primarykeys();

get_autoinc_primarykeys();