NAME

Gtk2::Ex::DBI - Bind a Gtk2::GladeXML - generated window to a DBI data source

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, schema => "sales", sql => { select => "*", from => "Prospects", where => "Actve=? and Employees>?", where_values => \[ 1, 200 ], order_by => "ClientName", }, 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

form

the Gtk2::GladeXML object that created your form

formname

the name of the form ( from the Glade file )

sql

The sql object describes the query to be executed to fetch your records. Note that in contrast to version 1.x, all the keywords ( select, from, where, order by, etc ) are *OMMITTED* ... see above example. This is for consistency and ease of manipulating things. Trust me.

Minimum requirements for the sql object are the 'select' and 'from' keys. All others are optional.

Details:

select

The SELECT clause

from

The FROM clause

where

The WHERE clause ( try '0=1' for inserting records )

bind_values

An array of values to bind to placeholders ... you ARE using placeholders, right?

order_by

The ORDER BY clause

Other ( non-essential ) keys:

primary_key

The PRIMARY KEY of the table you are querying.

As of version 2.0, the primary key is automatically selected for you if you use MySQL. Note, however, that this will only work if the FROM clause contains a single table. If you have a multi-table query, you must specify the primary_key, otherwise the last primary_key encountered will use. I recommend against using multi-table queries anyway.

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

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 = FALSE ; 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

schema

The schema to query to get field details ( defaults, column types ) ... not required for MySQL

disable_full_table_find

Don't allow the user to replace the where clause; only append to the existing one

disable_find

Disable the 'find' item in the right-click menu of GtkText widgets ( ie disable user-initiated searches )

The 'new' method will call the 'query' method, which will in turn move to the 1st record and paint your form.

fieldlist

Returns a fieldlist as an array, based on the current query. Mainly for internal Gtk2::Ex::DBI use

query ( [ where_object ] )

Requeries the DB server, either with the current where clause, or with a new one ( if passed ).

Version 2.x expects a where_object hash, containing the following keys:

where

The where key should contain the where clause, with placeholders ( ? ) for each value. Using placeholders is particularly important if you're assembling a query based on values taken from a form, as users can initiate an SQL injection attack if you insert values directly into your where clause.

bind_values

bind_values should be an array of values, one for each placeholder in your where clause.

Version 1.x expected to be passed an optional string as a new where clause. This behaviour is still supported for backwards compatibility. If a version 1.x call is detected ( ie if where_object isn't a hash ), any existing bind_values will be deleted

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 TRUE if successful, FALSE if unsuccessful.

apply

Apply changes to the current record back to the DB server. Returns TRUE if successful, FALSE 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. If you are selecting from multiple tables, this method will not work as expected, if at all, as we don't know which table you want to delete from. The best case scenario is an error - this is what MySQL does. Other database may delete from both / all tables. I haven't tried this, but I wouldn't be surprised ...

find_dialog ( [ field ] )

Opens a find 'dialog' ( a window in GTK speak ) that allows the user to query the active table ( whatever's in the sql->{from} clause ). This will allow them to *alter* the where clause. If you only want them to be able to *append* to the existing where clause, then set the disable_full_table_find key to TRUE ( see 'new' method ).

If an optional field is passed, this will be inserted into the dialog as the first field in the criteria list.

Note that the user can currently activate the find_dialog by right-clicking in a text field. To disable this behaviour, set the disable_find key to TRUE ( see 'new' method ).

position

Returns the current position in the keyset ( starting at zero ).

ISSUES

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.

AUTHORS

Daniel Kasak - dan@entropy.homelinux.org

CREDITS

Muppet

 - tirelessly offered help and suggestions in response to my endless list of questions

Gtk2-Perl Authors

 - obviously without them, I wouldn't have gotten very far ...

Gtk2-Perl list

 - yet more help, suggestions, and general words of encouragement

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

Crank ON!

cpanm Gtk2::Ex::DBI

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