On 24/05/13 06:51, Gabor Szabo wrote:
Hi,
On Wed, May 22, 2013 at 10:08 PM, Martin J. Evans <boh...@ntlworld.com> wrote:
Another good tutorial - please keep up the good work of promoting Perl.
Thanks. I hope others will also help promoting my articles and interviews.
"The DSN (Data Source Name) (in the $dsn variable) is very straight forward.
It contains the type of the database. That will be the clue to DBI which DBD
to load. In case of SQLite, the only thing we really need is the path to the
database file."
It may be a language thing but it is hardly a "clue". DBI clearly states the
connection string is dbi:DRIVER_NAME:something_else and DRIVER_NAMEs are
registered with DBI. Its also not always as "straight forward" as you
suggest as the something_else is usually a ';' separated string of
attributes and values e.g. "DRIVER={this driver}".
I know it can be more complex than my example, but I don't have to
frighten away the reader :)
I am confused with your comment about the word "clue". Isn't the name
of DBD::* derived from
the DRIVER_NAME ? Or does clue mean something else then guiding information?
Or do you mean that DRIVER_NAME is exact and a "clue" is only a direction?
I was only commenting on the use of the word "clue".
Clue is usually read a little like hint. A clue usually gives you some
information but is not everything you need to know whereas the DRIVER_NAME is
everything.
"The call to disconnect from the database is optional as it will
automatically be called when the variable $dbh goes out of scope, but it
having it might be a clear indication for the next programmer dealing with
this code, that you are done with the database. "
There are a number of gotchas with this. You may still have a select
statement active where you've not fetched all the rows yet from the cursor
in which case you'll get a warning. Also, you may be in the middle of a
transaction and in that case the transaction may be rolled back.
Are these gotchas different in case $dbh goes out of scope and when I
call $dbh->disconnect ?
I'm afraid so because in one case the DBD knows and in the other case it
doesn't. Then there is PrintWarn and Warn.
use 5.010;
use strict;
use warnings;
use DBI;
my $h = DBI->connect("dbi:ODBC:baugi", "xx", "yy",
{RaiseError => 1, PrintError => 0, PrintWarn => 0});
eval {
$h->do(q/drop table mje/);
};
$h->do(q/create table mje (a integer)/);
my $s = $h->prepare(q/insert into mje values(?)/);
$s->execute_array(undef, [1,2,3,4,5]);
$h->disconnect;
doit(Disconnect => 1, PrintWarn => 1, Warn => 1, Txn => 0);
doit(Disconnect => 0, PrintWarn => 1, Warn => 1, Txn => 0);
doit(Disconnect => 1, PrintWarn => 1, Warn => 0, Txn => 0);
doit(Disconnect => 1, PrintWarn => 1, Warn => 1, Txn => 1);
doit(Disconnect => 0, PrintWarn => 1, Warn => 1, Txn => 1);
doit(Disconnect => 1, PrintWarn => 1, Warn => 0, Txn => 1);
sub doit {
my %args = @_;
say "Disconnect=$args{Disconnect}, PrintWarn=$args{PrintWarn}, ",
"Warn=$args{Warn}, ",
($args{Txn} ? "Uncommitted txn" : "Unfinished select");
my $h = DBI->connect("dbi:ODBC:baugi", "xx", "yy",
{RaiseError => 1, PrintError => 0,
PrintWarn => $args{PrintWarn}});
$h->{Warn} = $args{Warn};
my $s;
if ($args{Txn}) {
$h->{AutoCommit} = 0;
$s = $h->prepare(q/delete from mje where a = ?/);
$s->execute(1);
} else {
$s = $h->prepare(q/select * from mje/);
$s->execute;
$s->fetch;
}
if ($args{Disconnect}) {
say " Disconnecting with Warn=$args{Warn}, PrintWarn=$args{PrintWarn}";
$h->disconnect;
} else {
say " connection handle going out of scope";
}
}
produces:
Disconnect=1, PrintWarn=1, Warn=1, Unfinished select
Disconnecting with Warn=1, PrintWarn=1
DBI::db=HASH(0x89ac660)->disconnect invalidates 1 active statement handle
(either destroy statement handles or call finish on them before disconnecting) at
/tmp/x.pl line 50.
Disconnect=0, PrintWarn=1, Warn=1, Unfinished select
connection handle going out of scope
Disconnect=1, PrintWarn=1, Warn=0, Unfinished select
Disconnecting with Warn=0, PrintWarn=1
Disconnect=1, PrintWarn=1, Warn=1, Uncommitted txn
Disconnecting with Warn=1, PrintWarn=1
DBD::ODBC::db disconnect warning: Disconnect with transaction in progress -
rolling back at /tmp/x.pl line 50.
Disconnect=0, PrintWarn=1, Warn=1, Uncommitted txn
connection handle going out of scope
Issuing rollback() due to DESTROY without explicit disconnect() of
DBD::ODBC::db handle baugi at /tmp/x.pl line 22.
Disconnect=1, PrintWarn=1, Warn=0, Uncommitted txn
Disconnecting with Warn=0, PrintWarn=1
DBD::ODBC::db disconnect warning: Disconnect with transaction in progress -
rolling back at /tmp/x.pl line 50.
Note how connection handle going out of scope when an unfinished select does
not generate anything but calling disconnect does and then specifically calling
disconnect but disabling Warn hides the warning.
Note for the txn example, in the first case when you call disconnect it gets to
the DBD which knows you have a txn in progress so it rolls it back (note the
DBD::ODBC in the message) but when the connection handle goes out of scope DBI
sees that and explicitly issues a rollback.
I'm not too sure the above is totally consistent now I've done this experiment.
"INSERT"
"UPDATE"
Clearly, I'll need more articles explaining database handling in more detail.
The thing is, I don't want to overwhelm the readers by giving them
all the examples and explanations at once.
It was the insert into a table with 4 columns but only 3 parameters that I
thought would confuse. Why not have 3 columns and 3 parameters.
"This is by far the most interesting part of the database access. As the
SELECT statement can return a lot of rows and a lot of values in each row we
cannot use a simple call to the do method. "
Some DBDs really dislike using the do method for select stmts and especially
multiple stmts in the same SQL e.g., do(q/something; select something from
something/);
I have not encountered that, but then I don't think I ever used do for
SELECT and
I think I use multiple SQL statements in one dbi call very rarely, if at all.
Surely an article about this could be interesting. I wonder who would
want to write it?
(hint hint :)
I already have written a number of articles:
Perl DBI/DBD::ODBC Tutorial Part 1 - Drivers, Data Sources and Connection:
http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_1.html
Perl DBI/DBD::ODBC Tutorial Part 2 - Introduction to retrieving data from your
database:
http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html
Perl DBI/DBD::ODBC Tutorial Part 3 - Connecting Perl on UNIX or Linux to
Microsoft SQL Server:
http://www.easysoft.com/developer/languages/perl/sql_server_unix_tutorial.html
Perl DBI - Put Your Data On The Web:
http://www.easysoft.com/developer/languages/perl/tutorial_data_web.html
Debugging Perl DBI:
http://www.easysoft.com/developer/languages/perl/dbi-debugging.html
Plus a few others on my personal web site.
Martin
