"Dean Arnold" <[EMAIL PROTECTED]> writes:
> Does this need to be modified to provide the column type info ? I don't see
> anything in the DBI 1.38 POD indicating type info can be specified...tho
> I spose the bind_param() i/f (wo binding data) can be used as a cheat ?
Where non-default types are desired, dummy bind_param() calls are
used. This is actually not a cheat, but the correct supported way. The
DBI docs may no be very explicit about this, but it says under
bind_param():
97 }". The data type for a placeholder cannot be changed after the
first "bind_param" call. However, it can be left unspecified, in
which case it defaults to the previous value.
So you just say $sth=$dbh->prepare("INSERT INTO X VALUES(?)") and
immediately $sth->bind_param(1, undef, SQL_INTEGER); then any subsequent
array executes will use the specified type.
> Again, whence column type info ? Also, why less memory efficient
> (in the wholistic sense) ? If you bind N column arrays of M elements each
> column wise, vs. binding M tuple arrays of N columns for rowwise,
> isn't the total memory use nearly equal ?
Well, Perl does not have two-dimensional arrays like C does. So for
row-wise you need memory for a Perl array for _every_ tuple, as well as
memory for each element. For column-wise there are only three arrays.
Here is a quick test of the memory usage of a million rows in columns
and in tuples:
perl -e '$y = [[1..1e6], [1..1e6], [1..1e6]]; scalar(<STDIN>)'
7962 kn 23 0 132M 132M 912 S 0.0 13.1 0:01 perl
perl -e '$x = [map([$_,$_,$_], 1..1e6)]; scalar(<STDIN>)'
14174 kn 25 0 184M 184M 924 S 36.3 18.3 0:01 perl
So for row-wise there is an overhead of around 50 bytes/tuple in this
example compared to column-wise. Significant, but not very so.
> > 1. The execute_for_fetch() method. The implementation of this in the
> > driver is mandatory (in the sense that without it no native
> > execute_array() will be available; DBI will emulate it with an
> > execute() for each row).
>
> I assume you mean mandatory to support rowwise ?
No, actually I meant mandatory also for column-wise; execute_for_fetch()
should be the standard low-level driver interface, since it is the most
general. The other suggested methods are refinements that most drivers
need not bother about.
> > 2. A new $sth->execute_array_rowwise([EMAIL PROTECTED], [EMAIL PROTECTED])
> > for direct implementation of row-wise array execution. Optional; if
> > implemented it will be used by DBI::execute_array() when an array of
> > tuples is already available (ie. execute_array() method 4 above).
>
> - needs type attribute capability; maybe also a named PH order list to indicate
> which tuple elements map to which named PHs ?
Types are handled with dummy bind_param() calls as above. But I tend to
agree about the need to support named placeholders; relying on
placeholder order is fragile when there are many placeholders and code
is modified. The execute_for_fetch() also needs to take an extra
optional PH order list to support named placeholders.
--
Kristian Nielsen [EMAIL PROTECTED]
Development Manager, Sifira A/S
