In this example I've created 1 simple table.
Books table:
scott@connect--> /desc books
Describe: books
NAME TYPE NULLABLE
-------------------- -------------- --------
BOOK_ID NUMBER (3) N
ID NUMBER (3) N
SEGMENT VARCHAR2 (10) Y
[3 rows of 3 fields returned]
scott@connect--> select * from books/
BOOK_ID ID SEGMENT
------- --- -----------------------------------
1 1 Title segment: Using DBI
1 2 Author segment: TBD
1 3 Cost segment: Free
1 4 Pages segment: 327
1 5 Print Date segment: 12-DEC-2002
2 1 Title segment: Programming the Perl
3 1 Title segment: MySQL made easier
4 1 Title segment: MySQL made easy
[8 rows of 3 fields returned]
#/usr/local/bin/perl
require 5.6.0;
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect();
$dbh->{AutoCommit} = 0;
$dbh->{RaiseError} = 1;
$dbh->{PrintError} = 1;
# Select segments.
my $sel_seg_sql = q{
select
segment
from books
where book_id = ?
order by id ASC
};
# Book ID
my $id = 1;
# Using a utility method in DBI.
# $ary_ref = $dbh->selectcol_arrayref($statement, \%attr, @bind_values);
my $seg_ref = $dbh->selectcol_arrayref( $sel_seg_sql, undef, $id );
# Bring all the segments to together.
my $segment = join( " ", @$seg_ref );
print "Book id: $id\nSegment: $segment\n";
exit;
END {
$dbh->disconnect if $dbh;
}
Tom
On Fri, Aug 17, 2001 at 12:16:55PM -0400, Jeff Suszczynski wrote:
> Hello:
>
> Relevant background info: I program in an academic library, using
> Perl/DBI to connect to our Oracle8 database and query information
> about our books, etc.. Each book has a record, called a MARC record,
> which contains various information about Title, Size, Call Number,
> etc. This MARC record is contained in one or more record segments in
> the database. What I need to do for my latest project is string these
> record segments together to form the whole MARC record.
>
> So for every book that has ID=X, I need to concatenate the record
> segments into a unified whole, so that I can use the Perl module
> MARC.pm which parses through the various parts of a MARC record to
> give very granular results... The catch : MARC.pm can only recognize
> whole MARC records, not a record segment that may be lopped off in the
> middle of a field somewhere, which is why I need to concatenate and
> create only one "supersegment" for each ID number.
>
> Any ideas on how to do this within the return loop of a DBI query?
>
> A rough example of oversimplified code is below:
>
> use DBI;
> ### Grab the ID and Record Segments ###
> select
> id
> segment
> from
> table1
> where
> id > 52
> id < 99
>
> while (@row = $sth->fetchrow_array)
> {
>
> $id = $row[0];
> $seg = $row[1];
>
> then, I need something like (sorry for the mix of Perl and English) -
>
> if ($id = another $id during the loop)
> {
> pull together the $seg from each $id and call the new "supersegment" $full_record;
> }
> ### Basically, I am trying to make it so that if two or more record
> ### segments exist for a single ID, collapse those record segments by
> ### joining them together ###
>
> use Marc.pm;
> ### Now parse through the MARC record with my newly created "Supersegments" ###
> etc.
> etc.
>
> Thanks in advance for your help on this hard-to-describe problem...
--
Thomas A. Lowery
See DBI/FAQ http://tlowery.hypermart.net
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
