This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: <dbi-users@perl.org>
From: "Steve Howard" <showard@pccompsoft.net>
Subject: Reusable code for binding columns.
Date: Wed, 7 Mar 2001 21:16:10 -0600
Does anyone have any ideas on how to make this work:
I write a lot of scripts to do migrations. I try to make as
much of my code reusable as possible. Straight table copies
are no problem after I query the system tables to get the
table, and column names, and build something like:
INSERT INTO $ini->{targetdb}..$table ($columnlist) SELECT $columnlist FROM
$ini->{sourcedb}..$table
My problem comes with finding a way to build reusable code
for a subroutine to deal with tables that will not go
straight across. The obstacle is in this statement:
$row = $select->bind_columns(undef, \$column1, \$column2......
Again, I can get the column names by querying the system
catalogs. I don't necessarily have to bind them by anything
resembling their column name, I only need a way to reference
them. So once I get the number of columns into the script,
how can I then assign variable, or hash key names so that I
can build a bind_columns statement that can work?
I may be just too close to this to see something obvious -
whatever the case, I would greatly appreciate any ideas that
will help with this.
===
To: <dbi-users@perl.org>
From: "Steve Howard" <showard@pccompsoft.net>
Subject: Reusable code for binding columns.
Date: Wed, 7 Mar 2001 22:49:22 -0600
Sorry, let me clarify, the INSERT...SELECT statement in this
e-mail works just fine. The $columnlist is built properly,
and that is not a problem - Maybe I confused the issue when
I included it. I was just trying to expound on what I was
doing and maybe show to what extent I am trying to make the
scripts reusable.
The reason I need to bind columns is when I need to
scrub/convert/converge/split and/or print to file as
intermediate steps. The code I'm trying to make reusable is
the part that selects this, and the obstacle I run into is
in building the bind_columns list without knowing at the
time I write the script how many columns are going to be
returned by the select statement. If I can just get the
bind_columns built so that it works, and this is reusable,
then my scripting time is greatly reduced.
Sorry I was nor more clear on the first e-mail.
===
To: Steve Howard <showard@pccompsoft.net>
From: Stephen Clouse <stephenc@theiqgroup.com>
Subject: Re: Reusable code for binding columns.
Date: Wed, 7 Mar 2001 23:45:54 -0600
On Wed, Mar 07, 2001 at 09:16:10PM -0600, Steve Howard wrote:
> My problem comes with finding a way to build reusable code
> for a subroutine to deal with tables that will not go
> straight across. The obstacle is in this statement:
> $row = $select->bind_columns(undef, \$column1, \$column2......
> Again, I can get the column names by querying the system
> catalogs. I don't necessarily have to bind them by
> anything resembling their column name, I only need a way
> to reference them. So once I get the number of columns
> into the script, how can I then assign variable, or hash
> key names so that I can build a bind_columns statement
> that can work?
You could eschew the bind_columns and column lookup
chicanery and just retrieve the records with
fetchrow_hashref.
===
To: "Steve Howard" <showard@pccompsoft.net>
From: merlyn@stonehenge.com (Randal L. Schwartz)
Subject: Re: Reusable code for binding columns.
Date: 07 Mar 2001 23:46:07 -0800
>>>>> "Steve" == Steve Howard <showard@pccompsoft.net> writes:
Steve> $row = $select->bind_columns(undef, \$column1, \$column2......
This is weird, but it works:
$rc = $sth->bind_columns(\@column{qw(one two three four five)});
Now $column{one} is the first column, and $column{two} is the second
column. Very cool, and much faster than the fetchrow_hashref, since
we aren't rebuilding the hash each time... instead the fetch goes
right into the existing scalars which were bound.
===
To: "Randal L. Schwartz" <merlyn@stonehenge.com>
From: David Wheeler <david@wheeler.net>
Subject: Re: Reusable code for binding columns.
Date: Thu, 8 Mar 2001 00:00:50 -0800 (PST)
On 7 Mar 2001, Randal L. Schwartz wrote:
> This is weird, but it works:
>
> $rc = $sth->bind_columns(\@column{qw(one two three four five)});
I actually do this all the time with arrays:
> $rc = $sth->bind_columns(\@column[0..$#cols]);
Very sweet.
===
To: "Steve Howard" <showard@pccompsoft.net>,
<dbi-users@perl.org>
From: "Michael A. Chase" <mchase@ix.netcom.com>
Subject: Re: Reusable code for binding columns.
Date: Thu, 8 Mar 2001 04:33:09 -0800
When I generate SQL automatically from the datadictionary, I
normally use an array to hold the column values:
# $dbh -> {RaiseError} = 1;
$sth -> execute;
my @sTitle = @{$sth -> {'NAME'}};
my @sCol = ();
$sth -> bind_columns( \( @sCol[0 .. $#sTitle] ) );
while ( $sth -> fetch ) {
. . .
I use \(), because sometimes I mix multiple scalars and
array slices in the bind_columns() call.
===
To: dbi-users@perl.org, merlyn@stonehenge.com
From: "Thomas A. Lowery" <stlowery@yahoo.com>
Subject: Re: Reusable code for binding columns.
Date: Fri, 9 Mar 2001 22:56:11 -0500
On Wed, Mar 07, 2001 at 11:46:07PM -0800, Randal L. Schwartz wrote:
>
> This is weird, but it works:
> Now $column{one} is the first column, and $column{two} is
> the second column. Very cool, and much faster than the
> fetchrow_hashref, since we aren't rebuilding the hash each
> time... instead the fetch goes right into the existing
> scalars which were bound.
OK Randal, how does this work? I put it in code and see it
WORKS, but my brains hurts trying to understand it.
> $rc = $sth->bind_columns(\@column{qw(one two three four five)});
===
To: dbi-users@perl.org
From: Abhijit Menon-Sen <ams@wiw.org>
Subject: Re: Reusable code for binding columns.
Date: Sat, 10 Mar 2001 09:56:52 +0530
On 2001-03-09 22:56:11, stlowery@yahoo.com wrote:
>
> OK Randal, how does this work? I put it in code and see it WORKS, but
> my brains hurts trying to understand it.
>
> > $rc = $sth->bind_columns(\@column{qw(one two three four five)});
@x{qw(a b) is ($x{a}, $x{b}). \($a, $b) is (\$a, \$b).
Thus, \@x{qw(a b)} is (\$x{a}, \$x{b}), which is what bind_columns
wants (the hash elements are autovivified when you take references to
them).
===
To: dbi-users@perl.org
From: "Matthew O. Persico" <persicom@acedsl.com>
Subject: Re: Reusable code for binding columns.
Date: Fri, 09 Mar 2001 23:55:57 -0500
"Thomas A. Lowery" wrote:
>
> OK Randal, how does this work? I put it in code and see it WORKS, but my
> brains hurts trying to understand it.
>
> > $rc = $sth->bind_columns(\@column{qw(one two three four five)});
Step back a bit.
An array element is $array[0].
An array slice is @array[1,2,3,4], for example.
A hash element is $hash{'hi_there'}. I know you don't NEED the quotes,
but they make sense for the purposes of the demo.
A hash slice is @hash{'hi_there','bye_there','over_there'} for example.
<aside>
As I understand it the reason that a HASH slice is defined
with an ARRAY indicator (@) is that in this context, @ is
NOT an array indicator - it's a LIST indicator. The type of
list is defined by the brackets; [] is an array, a list of
elements, {} is a hash, a list of paired elements.
</aside>
Therefore, @column{'one', 'two', 'three', 'four', 'five'} is a hash
slice, which is simply a list of the values at these keys of the hash
%column.
Typing all the quotes is a PITA, so we use the "quote word" operator to
simplify things to
@column{qw(one two three four five)}
Now, according to the docs (http://www.perldoc.com/cpan/DBI.html)
bind_columns
$rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
Calls /bind_col for each column of the SELECT statement. The
bind_columns method will die
if the number of references does not match the number of fields.
So, in order to get a list_of_refs, you put \ before the
list. This does NOT create a reference to the whole list, as
you'd might expect. Rather the "ref"-ness gets distributed
to each element of the list, creating a list of REFs.
Personally, I think this is a perfect example of the
"beauty" of Perl. Visually, \@column{qw(one two three four
five)} LOOKS like a ref of a list. Bit, I don't think there
IS such a thing.
\@foobar IS a ref of an ARRAY.
\%baba is a ref of a HASH.
\(1,2,3,4) is a ref of a LIST. But what's the point of that? If you want
an ARRAY ref, you can do [1,2,3,4]. If you want a hash ref, do
\{1,2,3,4} (or, more clearly, \{1=>2,3=>4}). A ref of a LIST is
ambiguous, as used in this context. Therefore, it can't be used that
way. So, I guess, it was decided that defining this construct as
converting a list to a list of refs would be useful and therefore, it
was done.
<shameless flame-bait>
Now, IMHO, the majority of compsci language police would end
up in straight jackets if they tried to create a BNF for
this scenario. Tough. Perl gets the job done w/o slavish
devotion to an academic concept. Let Gossling, and Guido
chew on that! harumph! Perl "just makes sense". It's as
close to a DWIM language as we have today.
</shameless flame-bait>
HTH
Oh, and by the way, just because I think I can explain it,
don''t for one momment belive that I'd EVER dream that one
up on my own. :-)
But you shouldn't be surprised that Randal did. Remember,
he's the author of the Schwarzian transform.
===
To: "Matthew O. Persico" <persicom@acedsl.com>,
dbi-users@perl.org
From: "Sterin, Ilya" <Isterin@ciber.com>
Subject: RE: Reusable code for binding columns.
Date: Sat, 10 Mar 2001 00:10:39 -0500
That's a good explanation, since I myself understood
everything except the @ usage for this expression. Now I
can see, but why doesn't this work with my perl5.6
use strict;
my @hash{'hi_there','bye_there','over_there'};
$hash{'bye_there'} = "testing";
print $hash{'bye_there'};
Any ideas, it comes with...
syntax error at test.pl line 2, near "@hash{"
Execution of test.pl aborted due to compilation errors.
===
To: "Sterin, Ilya" <Isterin@ciber.com>,
From: James Maes <jmaes@sportingnews.com>
Subject: Re: Reusable code for binding columns.
Date: Fri, 9 Mar 2001 23:27:44 -0600
On Friday 09 March 2001 23:10, Sterin, Ilya wrote:
> That's a good explanation, since I myself understood everything except the
> @ usage for this expression. Now I can see, but why doesn't this work with
> my perl5.6
>
> use strict;
> my @hash{'hi_there','bye_there','over_there'};
> $hash{'bye_there'} = "testing";
> print $hash{'bye_there'};
>
> Any ideas, it comes with...
> syntax error at test.pl line 2, near "@hash{"
> Execution of test.pl aborted due to compilation errors.
>
> -----Original Message-----
> From: Matthew O. Persico [mailto:persicom@acedsl.com]
> Sent: Friday, March 09, 2001 11:56 PM
> To: dbi-users@perl.org
> Subject: Re: Reusable code for binding columns.
>
> "Thomas A. Lowery" wrote:
> > OK Randal, how does this work? I put it in code and see it WORKS, but my
> > brains hurts trying to understand it.
> >
> > > $rc = $sth->bind_columns(\@column{qw(one two three four five)});
>
> Step back a bit.
>
> An array element is $array[0].
>
> An array slice is @array[1,2,3,4], for example.
>
> A hash element is $hash{'hi_there'}. I know you don't NEED the quotes,
> but they make sense for the purposes of the demo.
>
> A hash slice is @hash{'hi_there','bye_there','over_there'} for example.
>
> <aside>
> As I understand it the reason that a HASH slice is defined with an ARRAY
> indicator (@) is that in this context, @ is NOT an array indicator -
> it's a LIST indicator. The type of list is defined by the brackets; []
> is an array, a list of elements, {} is a hash, a list of paired
> elements.
> </aside>
>
> Therefore, @column{'one', 'two', 'three', 'four', 'five'} is a hash
> slice, which is simply a list of the values at these keys of the hash
> %column.
>
> Typing all the quotes is a PITA, so we use the "quote word" operator to
> simplify things to
>
> @column{qw(one two three four five)}
>
> Now, according to the docs (http://www.perldoc.com/cpan/DBI.html)
>
> bind_columns
>
> $rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
>
> Calls /bind_col for each column of the SELECT statement. The
> bind_columns method will die
> if the number of references does not match the number of fields.
>
> So, in order to get a list_of_refs, you put \ before the list. This does
> NOT create a reference to the whole list, as you'd might expect. Rather
> the "ref"-ness gets distributed to each element of the list, creating a
> list of REFs.
>
> Personally, I think this is a perfect example of the "beauty" of Perl.
> Visually, \@column{qw(one two three four five)} LOOKS like a ref of a
> list. Bit, I don't think there IS such a thing.
>
> \@foobar IS a ref of an ARRAY.
> \%baba is a ref of a HASH.
> \(1,2,3,4) is a ref of a LIST. But what's the point of that? If you want
> an ARRAY ref, you can do [1,2,3,4]. If you want a hash ref, do
> \{1,2,3,4} (or, more clearly, \{1=>2,3=>4}). A ref of a LIST is
> ambiguous, as used in this context. Therefore, it can't be used that
> way. So, I guess, it was decided that defining this construct as
> converting a list to a list of refs would be useful and therefore, it
> was done.
>
> <shameless flame-bait>
> Now, IMHO, the majority of compsci language police would end up in
> straight jackets if they tried to create a BNF for this scenario. Tough.
> Perl gets the job done w/o slavish devotion to an academic concept. Let
> Gossling, and Guido chew on that! harumph! Perl "just makes sense". It's
> as close to a DWIM language as we have today.
> </shameless flame-bait>
still need an ='s sign for anonymouse arrays
@hash = {'hi_there','bye_there','over_there'};
$hash{'bye_there'} = "testing";
print $hash{'bye_there'};
===
To: dbi-users@perl.org
From: Abhijit Menon-Sen <ams@wiw.org>
Subject: Re: Reusable code for binding columns.
Date: Sat, 10 Mar 2001 10:56:02 +0530
On 2001-03-10 00:10:39, Isterin@ciber.com wrote:
>
> why doesn't this work with my perl5.6
>
> use strict;
> my @hash{'hi_there','bye_there','over_there'};
> $hash{'bye_there'} = "testing";
> print $hash{'bye_there'};
What are you expecting it to do? It isn't meaningful to "my" a hash
slice, or even particular keys in a hash.
$ perl -wle 'my $x{a}'
Can't declare hash elem in my at -e line 1, at EOF
Execution of -e aborted due to compilation errors.
===
To: dbi-users@perl.org
From: "Matthew O. Persico" <persicom@acedsl.com>
Subject: Re: Reusable code for binding columns.
Date: Sat, 10 Mar 2001 00:33:06 -0500
"Sterin, Ilya" wrote:
>
> That's a good explanation, since I myself understood everything except the @
> usage for this expression. Now I can see, but why doesn't this work with my
> perl5.6
>
> use strict;
> my @hash{'hi_there','bye_there','over_there'};
> $hash{'bye_there'} = "testing";
> print $hash{'bye_there'};
>
> Any ideas, it comes with...
> syntax error at test.pl line 2, near "@hash{"
> Execution of test.pl aborted due to compilation errors.
In order to declare the hash, it has to a hash, not a slice of a hash;
you don't yet have a hash to slice :-). Try this:
my %hash = ('hi_there' => undef,
'bye_there' => 'testing',
'over_there' => undef);
===
To: jmaes@sportingnews.com, "Matthew O. Persico"
From: "Sterin, Ilya" <Isterin@ciber.com>
Subject: RE: Reusable code for binding columns.
Date: Sat, 10 Mar 2001 00:33:29 -0500
Oops, now I got the concept. Hash is created in a regular way and can't be
created like this
@hash = {'hi_there','bye_there','over_there'};
but this whole thing refered to the access of the hash and not really
building of it.
Thanks for clearing it up.
===
To: <jmaes@sportingnews.com>, "Sterin, Ilya"
<Isterin@ciber.com>,
From: "Steve Howard" <showard@pccompsoft.net>
Subject: RE: Reusable code for binding columns.
Date: Sat, 10 Mar 2001 01:46:58 -0600
Thanks to all for the further discussion on this. After this
tonight, going back to my original question about reusable
code for binding columns, this makes for a slicker solution
than I even though would come from asking the question (and
I know some of you are slick) :-)
That would mean in my subroutine, I can do something like
(shorthand version, but still critique it if it needs it):
my $select = qq{SELECT $columnlist FROM $ini->{sourcedb}..$table};
my $columns = $columnlist;
# Allow for the possibility of non-standard column names
$columns =~ s/ //;
$columns =~ s/,/ /;
my $selprep = $source->prepare($select) || print logfile "Can't prepare:
$select \n";
$selprep->execute() || print logfile "Can't Execute $select \n";
# and the solution to my original question:
$rc = $sth->bind_columns(\@column{qw($columns)});
And be able to refer to any number of columns by names still
recognizable as the original column name (minus any spaces
that might have been in the source), and still even have the
original column list in tact to work with later.
Cool! That's even better than I exected as a solution.
Thanks a lot to all of you who responded, and contributed.
===
To: dbi-users@perl.org
From: Abhijit Menon-Sen <ams@wiw.org>
Subject: Re: Reusable code for binding columns.
Date: Sat, 10 Mar 2001 13:32:56 +0530
On 2001-03-10 01:46:58, showard@pccompsoft.net wrote:
>
> $rc = $sth->bind_columns(\@column{qw($columns)});
qw doesn't work like you seem to expect (see perlop(1)). You need
something like:
my @columns = split / /, $columnlist; # or whatever
# ...
$rc = $sth->bind_columns(\@column{@columns});
===
To: "Steve Howard" <showard@pccompsoft.net>
From: merlyn@stonehenge.com (Randal L. Schwartz)
Subject: Re: Reusable code for binding columns.
Date: 10 Mar 2001 07:44:41 -0800
>>>>> "Steve" == Steve Howard <showard@pccompsoft.net> writes:
Steve> Thanks to all for the further discussion on this. After this
Steve> tonight, going back to my original question about reusable code
Steve> for binding columns, this makes for a slicker solution than I
Steve> even though would come from asking the question (and I know
Steve> some of you are slick) :-)
Well, when I first stumbled across bind_columns, I ignored it.
Then I saw someone use it, and I was hooked.
Then I played with
$sth->bind_columns(\my $a, \my $b);
and thought that was really cool, for about five minutes until I came
up with:
$sth->bind_columns(\my($a, $b, $c));
and that stayed in my Very Cool pile until someone asked about a varying
number of columns... so I remembered that \@foo[0..9] means
(\$foo[0], \$foo[1], through \$foo[9]), and came up with:
$sth->bind_columns(\@foo[0..$#col_list]); # @col_list has names
But then you came along with your request, and that got me to think
about a hash slice instead.
So thank you for the question, because now I'm gonna go rewrite some
of my other code!
===
To: "Matthew O. Persico" <persicom@acedsl.com>,
<dbi-users@perl.org>
From: "Michael A. Chase" <mchase@ix.netcom.com>
Subject: Re: Reusable code for binding columns.
Date: Sat, 10 Mar 2001 08:46:49 -0800
See below.
"Matthew O. Persico" <persicom@acedsl.com> wrote:
>. . .
> Personally, I think this is a perfect example of the "beauty" of Perl.
> Visually, \@column{qw(one two three four five)} LOOKS like a ref of a
> list. Bit, I don't think there IS such a thing.
>
> \@foobar IS a ref of an ARRAY.
> \%baba is a ref of a HASH.
> \(1,2,3,4) is a ref of a LIST. But what's the point of that? If you want
> an ARRAY ref, you can do [1,2,3,4]. If you want a hash ref, do
> \{1,2,3,4} (or, more clearly, \{1=>2,3=>4}). A ref of a LIST is
> ambiguous, as used in this context. Therefore, it can't be used that
> way. So, I guess, it was decided that defining this construct as
> converting a list to a list of refs would be useful and therefore, it
> was done.
Actually, \( 1, 2, 3, 4 ) distributes to ( \1, \2, \3, \4 ) which makes it
possible to send a bunch of different slices and scalars as references:
\( @hash{qw(one two three)}, @array[0 .. 4], $scal1, $scal2 ).
The most interesting way I've used that was when I was comparing two
identically structured tables in different instances:
$dbh -> {RaiseErrors} = 1;
my $cols = join ", ", (map{ "a.$_" } @keycols, @datacols),
map { "b.$_" } @datacols;
my $join = join " AND ", map { "a.$_ = b.$_ (+)" } @keycols;
my $compare = join " OR ", "b.$keycols[0] IS NULL",
map { "a.$_ != b.$_" } @datacols;
my $sth = $dbh -> prepare( join "\n",
"SELECT $cols",
" FROM tab1 a, tab2 b",
" WHERE $join AND ($compare)" );
$sth -> execute;
my ( @keyval, @dataval0, @dataval1 );
$sth -> bind_columns(
\( @keyval[0 .. $#keycols],
@dataval0[0 .. $#datacols], @dataval1[0 .. $#datacols] ) );
while ( $sth -> fetch ) {
# report rows with same keys that are missing or have different data
}
Multiply this by about 60 tables in 6 instances.
===