How to do LOAD and UNLOAD in DBD::Informix
==========================================
Contrary to popular misconceptions, the LOAD and UNLOAD statements are
not a part of ESQL/C. The programs that provide the commands (ISQL,
DB-Access, I4GL, etc) first have to identify that the statement is a
LOAD or UNLOAD statement, and then arrange to call a function of their
own choosing or devising that implements the requested operation.
Consequently, there is no built-in LOAD or UNLOAD command in
DBD::Informix. However, Perl and DBD::Informix provide all the
facilities necessary to implement these functions. It would be possible
to put this code in a module such as DBD::Informix:Load and
DBD::Informix::Unload. However, there are striking differences in the
performance of different options, so it may be better to allow people to
choose between safety (completeness) and performance.
------------------------------------------------------------------------
UNLOAD:
As always, the hardest part is dealing with nulls and blobs! The
optional code segments below deal with varying amounts of complexity.
If your data is sufficiently simple, option 3 is a decent working
UNLOAD. Option 4 is good if you don't have BYTE blobs; option 5 is
necessary if you do have BYTE blobs.
# Common code prefix for all options.
use DBI;
$dbdelim = $ENV{DBDELIMITER} ? $ENV{DBDELIMITER} : "|";
$dbh = DBI->connect('dbi:Informix:dbase@server', $username, $password, {RaiseError=>1});
$dbh->{ChopBlanks} = 1;
$sth = $dbh->prepare("SELECT * FROM Customers");
$sth->execute;
# Option 1: more or less Informix UNLOAD format but:
# -- all fields enclosed in single quotes
# -- no escapes of $dbdelim in data
# -- no escapes of embedded newlines
# -- byte blobs are not hex-encoded
$sth->dump_results(32767,"\n",$dbdelim); # See 'perldoc DBI' for 4 optional arguments
# Option 2: works with no nulls in data; noisy if -w flag set
# -- no escapes of $dbdelim in data
# -- no escapes of embedded newlines
# -- byte blobs are not hex-encoded
$, = $dbdelim;
while ($ref = $sth->fetchrow_arrayref)
{
print @$ref, "\n";
}
# Option 3: maps nulls to empty strings; quiet if -w flag set
# -- no escapes of $dbdelim in data
# -- no escapes of embedded newlines
# -- byte blobs are not hex-encoded
$, = $dbdelim;
while ($ref = $sth->fetchrow_arrayref)
{
my(@row) = map { defined $_ ? $_ : "" } @$ref;
print @row, "\n";
}
# Option 4: UNLOAD format except for BYTE blobs
# -- escapes backslashes, newlines and $dbdelim in data
# -- byte blobs are not hex-encoded
$, = $dbdelim;
while ($ref = $sth->fetchrow_arrayref)
{
my(@row) = map { defined $_ ? $_ : "" } @$ref;
s/[\\$dbdelim\n]/\\$&/gm for @row;
print @row, "\n";
}
If there are BYTE blobs in the data, then you need to use the 'unpack'
command with the H format for the BYTE blobs (only - TEXT blobs are
treated the same as large CHAR fields). Using unpack is both messy and
counter-intuitive. The code for that should be:
# Option 5: UNLOAD format with BYTE blobs hex-encoded
# -- escapes backslashes, newlines and $dbdelim in data
$, = $dbdelim;
# Generate list of column numbers of BYTE columns.
my(@byte);
my($nbyte) = 0;
my(@types) = (@{$sth->{ix_NativeTypeName}});
for (my($i) = 0; $i <= $#types; $i++)
{
$byte[$nbyte++] = $i if ($types[$i] eq "BYTE");
}
while ($ref = $sth->fetchrow_arrayref)
{
my(@row) = map { defined $_ ? $_ : "" } @$ref;
s/[\\$dbdelim\n]/\\$&/gm for @row;
for (my($i) = 0; $i < $nbyte; $i++)
{
$row[$byte[$i]] = unpack('H*', $row[$byte[$i]]);
}
print @row, "\n";
}
I'd hope that the longhand loops could be shortened, but haven't spent
the time working out how to do it. Note the counter-intuitive use of
unpack for the UNLOAD operation; we'll need to use pack to convert from
the hex-string to the binary data.
Note that any of these fragments could be wrapped into a suitable
function that takes an opened statement handle (on which $sth->execute
has been executed), a file handle, and the field delimiter. A more
general version could include the record delimiter (newline) and escape
character (backslash) as arguments too. It would be possible to decide
semi-automatically which of the unload fragments should be used based on
the types of the fields.
------------------------------------------------------------------------
LOAD:
The equivalent LOAD examples still need to be written.
------------------------------------------------------------------------
Material prepared by the following in July 2001:
Andrew Hamm <AHamm@sanderson.net.au>
Jack Parker <jack.parker4@verizon.net>
Jonathan Leffler <Jonathan.Leffler@informix.com>
David Williams <djw@smooth1.demon.co.uk>
Copyright 2001 Andrew Hamm
Copyright 2001 Jonathan Leffler
Copyright 2001 Jack Parker
Copyright 2001 David Williams
Copyright 2002 IBM
@(#)$Id: load.unload,v 100.3 2002/02/13 22:21:58 jleffler Exp $