DBD::XBase, STDOUT, and IO issue

--=__Part3A1F1291.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

Hi Guys,
=20
I'm using a subclass of DBI called DBD::XBase for reading Oralce DBF =
tablespace datafiles, translating them into delimited text files, and then =
loading them into MySQL with DBI. The problem is the only way DBD:XBase =
can output the text file with field delimiters I need is it slurps the =
whole file to STDOUT. So I did this (don't laugh):
=20
my $table =3D new XBase;
open STDOUT, ">data.txt";
$table->dump_records( "fs" =3D> "|" );
close STDOUT;
=20
This does exactly what I need, except that now STDOUT is closed for =
further output (like for print statements), and if I do not close it, all =
STDOUT goes to the data file (I told you not to laugh!).
=20
I searched all night and cannot find an example of how to do this =
correctly. Capture the table dump's STDOUT with IO::Pipe somehow? The =
documentation of IO::Pipe is pretty sparse. Any suggestion is greatly =
appreciated.
=20
tia,
mark


--=__Part3A1F1291.0__=--

Re: DBD::XBase, STDOUT, and IO issue

On Wed, May 10, 2006 at 09:11:29AM -0400, Mark Galbreath wrote:
> Hi Guys,
>
> I'm using a subclass of DBI called DBD::XBase for reading Oralce DBF tablespace datafiles, translating them into delimited text files, and then loading them into MySQL with DBI. The problem is the only way DBD:XBase can output the text file with field delimiters I need is it slurps the whole file to STDOUT. So I did this (don't laugh):
>
> my $table = new XBase;
> open STDOUT, ">data.txt";
> $table->dump_records( "fs" => "|" );

Do not use dump_records for data transfer. Your data may get mangled in various ways. See the docs.

Tim.

Re: DBD::XBase, STDOUT, and IO issue

--=__Part47626D3D.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

Tim,
=20
I have preactically memorized the docs I have read them so many times. If =
you have a better suggestion, I am wide open for it!
=20
~mark

>>> Tim Bunce 11-May-06 08:37:59 AM >>>

On Wed, May 10, 2006 at 09:11:29AM -0400, Mark Galbreath wrote:
> Hi Guys,
> =20
> I'm using a subclass of DBI called DBD::XBase for reading Oralce DBF =
tablespace datafiles, translating them into delimited text files, and then =
loading them into MySQL with DBI. The problem is the only way DBD:XBase =
can output the text file with field delimiters I need is it slurps the =
whole file to STDOUT. So I did this (don't laugh):
> =20
> my $table =3D new XBase;
> open STDOUT, ">data.txt";
> $table->dump_records( "fs" =3D> "|" );

Do not use dump_records for data transfer. Your data may get mangled in =
various ways. See the docs.

Tim.



--=__Part47626D3D.0__=--

Re: DBD::XBase, STDOUT, and IO issue

On Thu, May 11, 2006 at 08:59:25AM -0400, Mark Galbreath wrote:
> Tim,
>
> I have preactically memorized the docs I have read them so many times. If you have a better suggestion, I am wide open for it!

Something simple would do:

$sth = $dbh->prepare(...);
$sth->execute;
while ( @row = $sth->fetchrow_array ) {
print join("|", @row), "\n";
}

(I'm assuming you know your data doesn't contain any | characters.)

Tim.

Re: DBD::XBase, STDOUT, and IO issue

Tim Bunce wrote:
> On Thu, May 11, 2006 at 08:59:25AM -0400, Mark Galbreath wrote:
>
>> Tim,
>>
>> I have preactically memorized the docs I have read them so many times. If you have a better suggestion, I am wide open for it!
>>
>
> Something simple would do:
>
> $sth = $dbh->prepare(...);
> $sth->execute;
> while ( @row = $sth->fetchrow_array ) {
> print join("|", @row), "\n";
> }
>
> (I'm assuming you know your data doesn't contain any | characters.)

And that the data doesn't contain any newlines. :-)

If you have embedded stuff, you might want:

my $csv_dbh = DBI->connect('dbi:CSV...);
my $mysql_dbh = DBI->connect('dbi:msqyl...);
my $mysql_sth = $mysql_dbh->prepare(... any MySQL query ...);
$csv_dbh->do(
"CREATE TABLE myCSV AS SELECT * FROM IMPORT(?)"
, {}
, $mysql_sth
);

That snippet will perform the MySQL query and create a properly escaped
CSV file of the results.

--
Jeff

Re: DBD::XBase, STDOUT, and IO issue

The solution was Perl's IO::Select()

Took 2 lines of code, as it should in perl. :-)


>>> Jeff Zucker 11-May-06 12:16:35 PM >>>
Tim Bunce wrote:
> On Thu, May 11, 2006 at 08:59:25AM -0400, Mark Galbreath wrote:
> =20
>> Tim,
>> =20
>> I have preactically memorized the docs I have read them so many times. =
If you have a better suggestion, I am wide open for it!
>> =20
>
> Something simple would do:
>
> $sth =3D $dbh->prepare(...);
> $sth->execute;
> while ( @row =3D $sth->fetchrow_array ) {
> print join("|", @row), "\n";
> }
>
> (I'm assuming you know your data doesn't contain any | characters.)

And that the data doesn't contain any newlines. :-)

If you have embedded stuff, you might want:

my $csv_dbh =3D DBI->connect('dbi:CSV...);
my $mysql_dbh =3D DBI->connect('dbi:msqyl...);
my $mysql_sth =3D $mysql_dbh->prepare(... any MySQL query ...);
$csv_dbh->do(
"CREATE TABLE myCSV AS SELECT * FROM IMPORT(?)"
, {}
, $mysql_sth
);

That snippet will perform the MySQL query and create a properly escaped=20
CSV file of the results.

--=20
Jeff