selectcol_arrayref: secret weapon

Июнь 27th, 2012 | Posted by self in Pensieve - (Комментарии отключены)

Despite what the name suggests and what quick references say, selectcol_arrayref can be used with more than one column, and not only the first one, which makes it a pretty handy tool, as in some cases it’s much less cumbersome than usual selectall_arrayref and alike.

Normal use (to get values from the first column):

my $tables = $dbh->selectcol_arrayref("SHOW TABLES IN test");
foreach my $table (@$tables) { ... }

This is self-explanatory, we want to do something with every table in a schema.
Now, I also want to know if a table is a base table or a view, and do something different based on that.

my $tbl_ref = $dbh->selectcol_arrayref("SHOW FULL TABLES IN test", { Columns=>[1,2] } );
my %tables = @$tbl_ref;
foreach my $t (keys %tables) {
if ($tables{$t} eq 'VIEW') { ... }
else { ... }
}

Why is it easier than selectall_arrayref?
Just because I don’t need to bother about the second-level array reference, everything is already in the plain sight. Simple is good.

What can I say, reading documentation can be useful…
http://search.cpan.org/dist/DBI/DBI.pm#selectcol_arrayref

Get rid of the Windows problem solution dialog

Июнь 4th, 2012 | Posted by self in Pensieve | Testing - (Комментарии отключены)

foo.exe has stopped working Windows can check online for a solution to the problem Check online for a solution and close the program Close the program Debug the program

This screen (a problem notification dialog, debug screen, or whatever it is called), is all cool and nice when you are actually debugging something, but is real pain when you are running a batch job, auto tests, or anything else where you’d be happy with just a windows dump and error logs from your application. But no, the screen pops up, everything stops and waits for your reaction.

Here is the solution if your driver is in Perl, as mine often are:

require Win32::API;
my $errfunc = Win32::API->new('kernel32', 'SetErrorMode', 'I', 'I');
my $initial_mode = $errfunc->Call(2);
$errfunc->Call($initial_mode | 2);

It’s not my invention, I found it long time ago on ActiveState lists. Works like a charm.

Perl: fork, DBI and InactiveDestroy

Июнь 3rd, 2012 | Posted by self in Pensieve - (Комментарии отключены)

I spent quite some time trying to figure how to make peace between Perl forks and DBI. It was clear that InactiveDestroy was supposed to be the solution, but making it work turned out to be tricky. Finally found a useful, and, importantly, working recipe here: http://lists.mysql.com/perl/3619 (old stuff, but still good).

This is what {InactiveDestroy} is for:
When you open a database handle ($dbh) in a process, it contains a variety of database-specific state/connection information in it. When you fork a child process, you should never use the database handle ($dbh) which you opened in the parent process. This behavior is *not* defined to work by the DBI. It does work in some cases on *some* databases, but this is not supported behavior. Every process should have its own database handle.

The problem with this is that when you close the inherited database handle in the child process, it also shuts it down on the server side, thus rendering the handle unusable even in the parent process. So here’s the logic.

  • parent process opens a database handle
  • parent forks a child process
  • parent can continue using the open database handle safely as long as no child process uses it and no child process closes it “ungracefully”
  • child process immediately sets the {InactiveDestroy} attribute on the handle
  • child process deallocates the database handle (i.e. $dbh = undef;) but does *not* explicitly close the connection. This triggers the DESTROY method  on the database handle. Since the {InactiveDestroy} attribute is set, this causes the client-side state and connection information to be deallocated and shut down without telling the server to shut down the connection. this allows the parent process to continue to use the connection.
  • child opens its own connection to the database
  • child uses its own connection and closes it normally whenever it wishes

Here is the documentation from the DBI manual page.

http://search.cpan.org/~timb/DBI/DBI.pm