NAME
Gtk2::Ex::DBI
SYNOPSIS
use DBI; use Gtk2 -init; use Gtk2::GladeXML; use Gtk2::Ex::DBI;
my $dbh = DBI->connect ( "dbi:mysql:dbname=sales;host=screamer;port=3306", "some_username", "salespass", { PrintError => 0, RaiseError => 0, AutoCommit => 1, } );
my $prospects_form = Gtk2::GladeXML->new("/path/to/glade/file/my_form.glade", 'Prospects');
my $data_handler = Gtk2::Ex::DBI->new( { dbh => $dbh, table => "Prospects", primarykey => "LeadNo", sql_select => "select *", sql_where => "where Actve=1", form => $prospects, formname => "Prospects", on_current => \&Prospects_current, calc_fields => { calc_total => 'eval { $self->{form}->get_widget("value_1")->get_text + $self->{form}->get_widget("value_2")->get_text }' }, default_values => { ContractYears => 5, Fee => 2000 } } );
sub Prospects_current { # I get called when moving from one record to another ( see on_current key, above ) }
DESCRIPTION
This module automates the process of tying data from a DBI datasource to widgets on a Glade-generated form. All that is required is that you name your widgets the same as the fields in your data source. You have to set up combo boxes ( ie create your Gtk2::ListStore and attach it to your combo box ) *before* creating your Gtk2::Ex::DBI object.
Steps for use:
* Open a DBI connection
* Create a Gtk2::GladeXML object ( form )
* Create a Gtk2::Ex::DBI object and link it to your form
You would then typically create some buttons and connect them to the methods below to handle common actions such as inserting, moving, deleting, etc.
METHODS
new
Object constructor. Expects a hash of key / value pairs. Bare minimum are:
dbh - a DBI database handle table - the name of the table you are querying primary_key - the primary key of the table you are querying ( required for updating / deleting ) sql_select - the 'select' clause of the query form - the Gtk2::GladeXML object that created your form formname - the name of the form ( from the Glade file )
The 'new' method will call the 'query' method, which will in turn move to the 1st record and paint your form.
Other keys:
sql_where - the 'where' clause of the query ( try 'where 0=1' for economy when you are simply inserting records )
on_current - a reference to some Perl code to run when moving to a new record
on_apply - a reference to some Perl code to tun *after* applying the current record
calc_fields - a hash of fieldnames / Perl expressions to provide calculated fields
sql_order_by - the 'order by' clause of the query
apeture - the size of the recordset slice ( in records ) to fetch into memory ONLY change this BEFORE querying
manual_spinner - disable automatic move() operations when the RecordSpinner is clicked read_only - whether we allow updates to the recordset ( default = 0 ; updates allowed ) defaults - a HOH of default values to use when a new record is inserted quiet - a flag to silence warnings such as missing widgets
fieldlist
Returns a fieldlist as an array, based on the current query. Mainly for internal Gtk2::Ex::DBI use
query ( [ new_where_clause ] ) Requeries the DB server, either with the current where clause, or with a new one ( if passed ).
insert Inserts a new record in the *in-memory* recordset and sets up default values, either from the database schema, or optionally overridden with values from the default_values hash.
count Returns the number of records in the current recordset.
paint Paints the form with current data. Mainly for internal Gtk2::Ex::DBI use.
move ( offset, [ absolute_position ] ) Moves to a specified position in the recordset - either an offset, or an absolute position. If an absolute position is given, the offset is ignored. If there are changes to the current record, these are applied to the DB server first. Returns 1 if successful, 0 if unsuccessful.
apply Apply changes to the current record back to the DB server. Returns 1 if successful, 0 if unsuccessful.
changed Sets the 'changed' flag, which is used internally when deciding if an 'apply' is required.
revert Reverts the current record back to its original state. Deletes the in-memory recordset if we were inserting a new record.
delete Deletes the current record. Asks for confirmation first.
position Returns the current position in the keyset ( starting at zero ).
BUGS
SQL Server support is *UNSTABLE* Previously I had claimed that this module had been tested under SQL Server. Now, unfortunately, I have to report that there are some bugs *somewhere* in the chain from DBD::ODBC to FreeTDS. In particular, 'money' column types in SQL Server will not work at all - SQL Server throws a type conversion error. Also I have had very strange results with 'decimal' column types - the 1st couple of fields are accepted, and everything after that ends up NULL. When I encountered this, I added the 'debug' flag to this module to dump details of the values being pulled from widgets and placed into our @bind_variables array. Rest assured that everything *here* is working fine. The problem is certainly somewhere further up the chain. So be warned - while some forms work quite well with SQL Server, others will *NOT*. Test first. Better still, don't use SQL Server.
Other cool things you should know about:
This module is part of an umbrella project, 'Axis Not Evil', which aims to make Rapid Application Development of database apps using open-source tools a reality. The project includes:
Gtk2::Ex::DBI - forms
Gtk2::Ex::Datasheet::DBI - datasheets
PDF::ReportWriter - reports
All the above modules are available via cpan, or for more information, screenshots, etc, see: http://entropy.homelinux.org/axis_not_evil