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

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 database

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

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->{linker} = Gtk2::Ex::DbLinker::Form->new({ 
                    data_manager => $rdbm,
                    builder =>  $builder,
                    rec_spinner => $self->{dnav}->get_object('RecordSpinner'),
                    status_label=>  $self->{dnav}->get_object('lbl_RecordStatus'),
                    rec_count_label => $self->{dnav}->get_object("lbl_recordCount"),
            });

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"
                                },
                });

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->{linker}->add_combo({
        data_manager => $dman,
        id => 'noed',
      });

And when all combos or datasheets are added:

      $self->{linker}->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 in a hash reference with the keys dbh, sql, primary_keys, ai_primary_keys. The value for primary_keys and ai_primary_keys 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_keys 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.

The value are

• select or select_distinct : a comma delimited string of the field names.

• from : a string of the join clause.

• where : a string of the where clause. Use place holders if the bind_values keys is set.

• 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.

• before_query : a code ref to be run at the start of the query method.

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

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.

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

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

The parameter of the query method is a hash reference with the folowing key / value pairs:

• 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 href to save 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();

delete();

set_row_pos( $new_pos);

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

get_row_pos( );

Return the position of the current row, first one is 0.

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.

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). Can be supplied to the constructor, or are guessed by the code.

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 questions or problems can be posted to the the mailing list. To subscribe to the list or view the archives, go here: http://groups.google.com/group/gtk2-ex-dblinker. You may also send emails to gtk2-ex-dblinker@googlegroups.com.

The current state of the source can be extract using Mercurial from http://code.google.com/p/gtk2-ex-dblinker/.

AUTHOR

François Rappaz <rappazf@gmail.com>

COPYRIGHT AND LICENSE

Copyright (c) 2014 by F. Rappaz. All rights reserved. This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

SEE ALSO

Gtk2::Ex::DbLinker::Forms

Gtk2::Ex::DbLinker::Datasheet

Gtk2::Ex::DBI

CREDIT

Daniel Kasak, whose code have been heavily borrowed from, to write this module.

cpanm Gtk2::Ex::DbLinker

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