Re: DBI article in Korean
On 24/05/13 06:51, Gabor Szabo wrote: Hi, On Wed, May 22, 2013 at 10:08 PM, Martin J. Evans 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
Re: DBI article in Korean
Hi, On Wed, May 22, 2013 at 10:08 PM, Martin J. Evans 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? > "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 ? > "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. > "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 :) regards Gabor
Re: DBI article in Korean
On 22/05/2013 06:55, Gabor Szabo wrote: Hi, recently I published an introduction to DBI article on the Perl Maven site: http://perlmaven.com/simple-database-access-using-perl-dbi-and-sql the really interesting thing is that articles are constantly being translated to 13 languages. The Korean version has already arrived: http://ko.perlmaven.com/simple-database-access-using-perl-dbi-and-sql regards Gabor Gabor, Another good tutorial - please keep up the good work of promoting Perl. I have a few comments I hope you'll take constructively. Please ignore some of these if you think it is beyond the scope of your tutorial - it is difficult for me to tell the audience you are targeting. "Those drivers are compiled together with the C client libraries of the respective database engines." although this is mostly true there are pure perl DBDs. "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}". "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. "INSERT" You've presented an insert with an 4 columns and then inserted 3 - I know you know why but that might confuse people i.e., it is an auto incrementing column with a default value. SQLite is a bit different from other database in this respect as most would require you to define the id column as auto incrementing or having a default value. "UPDATE" Your example with the do method if fine but often people want to insert or update multiple rows and I think it is worth showing you can prepare a stmt and execute it many times with different parameters. As you do with the select example. "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/); Martin -- Martin J. Evans Wetherby, UK