Jeff,
We finally were able to get someone to change the 'Output Format' on the
UniVerse side and we still have the problem. We found some more information
that may be helpful: when using Brio or MS Access to extract the UniVerse
data, the Output Format is ignored and no columns are truncated; when using
Perl/DBI/DBD::ODBC and MS Query columns are truncated to the size of the
Output Format. When the Output Format was changed, MS Query was able to pull
columns with no truncation but we continue to the have the problem with
Perl.
We are using DBI v 1.30 and DBD-ODBC v 0.45.18
As a reminder, the code won't go past a field that gets truncated unless we
include $dbh->{LongTruncOK] = 1;
Here's the data base part of the code:
my $dbh = DBI->connect("dbi:ODBC:calpac", "user_name", "password",
{PrintError => 1, RaiseError => 1});
DBI->trace(9, 'dbitrace.log');
$dbh->{LongTruncOk} = 1;
$sth = $dbh->prepare("select ADDR1, ADDR2, MAIL_ADDR1, MAIL_ADDR2, MAIL_CSZ
from SG_MEMBER");
$sth->execute();
$i = 0;
while (@row = ($sth->fetchrow_array)) {
$i++;
$out_row = join("|", @row);
print "$i : $out_row\n";
}
The trace follows, but as you can see, the trace shows different data than
with the old version of DBI/DBD::ODBC. The trace coers from the connection
to the first few rows pulled, at the end is a row that has been cutoff, the
field should contain '1999 Harrison Street, 22nd Floor' but gets truncated
after 25 characters to '1999 Harrison Street, 22n':
DBI 1.30-ithread dispatch trace level set to 9
....
>> STORE DISPATCH (DBI::db=HASH(0x1d87138) rc2/1 @3 g0 ima410
pid#1784) at D:\bin\calti.pl line 9
-> STORE for DBD::ODBC::db (DBI::db=HASH(0x1d87138)~INNER 'LongReadLen'
256) thr#01ABF3C0
STORE DBI::db=HASH(0x1d87138) 'LongReadLen' => 256
<- STORE= 1 at D:\bin\calti.pl line 9
>> STORE DISPATCH (DBI::db=HASH(0x1d87138) rc2/1 @3 g0 ima410
pid#1784) at D:\bin\calti.pl line 10
-> STORE for DBD::ODBC::db (DBI::db=HASH(0x1d87138)~INNER 'LongTruncOk'
1) thr#01ABF3C0
STORE DBI::db=HASH(0x1d87138) 'LongTruncOk' => 1
<- STORE= 1 at D:\bin\calti.pl line 10
>> prepare DISPATCH (DBI::db=HASH(0x1d853d0) rc1/1 @2 g0 ima1
pid#1784) at D:\bin\calti.pl line 15
-> prepare for DBD::ODBC::db (DBI::db=HASH(0x1d853d0)~0x1d87138 'select
ADDR1, ADDR2, MAIL_ADDR1, MAIL_ADDR2, MAIL_CSZ
from SG_MEMBER') thr#01ABF3C0
New DBI::st (for DBD::ODBC::st, parent=DBI::db=HASH(0x1d87138), id=)
dbih_setup_handle(DBI::st=HASH(0x1d853e8)=>DBI::st=HASH(0x1e17898),
DBD::ODBC::st, 1d853f4, Null!)
dbih_make_com(DBI::db=HASH(0x1d87138), DBD::ODBC::st, 204) thr#01ABF3C0
dbih_setup_attrib(DBI::st=HASH(0x1e17898), Err, DBI::db=HASH(0x1d87138))
SCALAR(0x1d992c8) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x1e17898), State,
DBI::db=HASH(0x1d87138)) SCALAR(0x1d99310) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x1e17898), Errstr,
DBI::db=HASH(0x1d87138)) SCALAR(0x1d992ec) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x1e17898), Debug,
DBI::db=HASH(0x1d87138)) 0 (already defined)
dbih_setup_attrib(DBI::st=HASH(0x1e17898), FetchHashKeyName,
DBI::db=HASH(0x1d87138)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0x1e17898), HandleError,
DBI::db=HASH(0x1d87138)) undef (not defined)
<- prepare= DBI::st=HASH(0x1d853e8) at D:\bin\calti.pl line 15
>> execute DISPATCH (DBI::st=HASH(0x1d853e8) rc1/1 @1 g0 ima41
pid#1784) at D:\bin\calti.pl line 18
-> execute for DBD::ODBC::st (DBI::st=HASH(0x1d853e8)~0x1e17898)
thr#01ABF3C0
<- execute= -1 at D:\bin\calti.pl line 18
>> fetchrow_array DISPATCH (DBI::st=HASH(0x1d853e8) rc1/1 @1 g1 ima0
pid#1784) at D:\bin\calti.pl line 30
-> fetchrow_array for DBD::ODBC::st (DBI::st=HASH(0x1d853e8)~0x1e17898)
thr#01ABF3C0
dbih_setup_fbav for 5 fields => 0x1ab561c
<- fetchrow_array= ( ' ' undef ' ' undef undef ) [5 items] row1 at
D:\bin\calti.pl line 30
>> fetchrow_array DISPATCH (DBI::st=HASH(0x1d853e8) rc1/1 @1 g1 ima0
pid#1784) at D:\bin\calti.pl line 30
-> fetchrow_array for DBD::ODBC::st (DBI::st=HASH(0x1d853e8)~0x1e17898)
thr#01ABF3C0
<- fetchrow_array= ( undef '1551 Stanton Way' undef '1551 Stanton Way'
'Stockton, CA 95207' ) [5 items] row2 at D:\bin\calti.pl line 30
....
-> fetchrow_array for DBD::ODBC::st (DBI::st=HASH(0x1d853e8)~0x1e17898)
thr#01ABF3C0
<- fetchrow_array= ( undef '1999 Harrison Street, 22n.' undef '1999
Harrison Street, 22n.' 'Oakland, CA 94612' ) [5 items] row61 at
D:\bin\calti.pl line 30
Let me know if you need more information.
Regards,
Roger
----- Original Message -----
From: "Jeff Urlwin" <[EMAIL PROTECTED]>
To: "Roger Magoulas" <[EMAIL PROTECTED]>; "Jeff Urlwin"
<[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, October 28, 2002 5:11 PM
Subject: RE: Problem Accessing UniVerse data w/ DBI
> Roger,
>
> Tracelevel 9 should get you there. I normally set it right after the
> connect.
> Let me know if that doesn't solve your problem, as I have a potential
patch
> waiting, but would rather not change it without more research. It works
for
> me here, so I might keep it in and release it anyway, but I try not to
> change things if they are not broken, so...I'll wait for you.
>
> Regards,
>
> Jeff
> >
> >
> > Jeff,
> >
> > I have installed the versions of the DBI and DBD::ODBC available
> > via the web
> > site you reference below. Using DBI v. 1.30 and DBD-ODBC v 0.45.18 I
> > continue the same results, varchar fields are cut-off at 25
> > characters. What
> > is different is the trace results no longer show the dbd_describe
> > statements
> > that included the length and maxlen for the columns included in the
select
> > statment. The trace is set to 9 [DBI->trace(9, 'dbitrace.log');]. The
> > following is the trace results with the new DBI and DBD::ODBC from the
> > prepare() to the first row pulled:
> >
> > -> prepare for DBD::ODBC::db (DBI::db=HASH(0x1d853d0)~0x1d87138
'select
> > ADDR1, MAIL_ADDR1, MAIL_CSZ
> > from SG_MEMBER') thr#01ABF3C0
> > New DBI::st (for DBD::ODBC::st, parent=DBI::db=HASH(0x1d87138), id=)
> > dbih_setup_handle(DBI::st=HASH(0x1d853e8)=>DBI::st=HASH(0x1e17898),
> > DBD::ODBC::st, 1d853f4, Null!)
> > dbih_make_com(DBI::db=HASH(0x1d87138), DBD::ODBC::st, 204)
> > thr#01ABF3C0
> > dbih_setup_attrib(DBI::st=HASH(0x1e17898), Err,
> > DBI::db=HASH(0x1d87138))
> > SCALAR(0x1d992c8) (already defined)
> > dbih_setup_attrib(DBI::st=HASH(0x1e17898), State,
> > DBI::db=HASH(0x1d87138)) SCALAR(0x1d99310) (already defined)
> > dbih_setup_attrib(DBI::st=HASH(0x1e17898), Errstr,
> > DBI::db=HASH(0x1d87138)) SCALAR(0x1d992ec) (already defined)
> > dbih_setup_attrib(DBI::st=HASH(0x1e17898), Debug,
> > DBI::db=HASH(0x1d87138)) 0 (already defined)
> > dbih_setup_attrib(DBI::st=HASH(0x1e17898), FetchHashKeyName,
> > DBI::db=HASH(0x1d87138)) 'NAME' (already defined)
> > dbih_setup_attrib(DBI::st=HASH(0x1e17898), HandleError,
> > DBI::db=HASH(0x1d87138)) undef (not defined)
> > <- prepare= DBI::st=HASH(0x1d853e8) at D:\bin\calti.pl line 15
> > >> execute DISPATCH (DBI::st=HASH(0x1d853e8) rc1/1 @1 g0 ima41
> > pid#2132) at D:\bin\calti.pl line 18
> > -> execute for DBD::ODBC::st (DBI::st=HASH(0x1d853e8)~0x1e17898)
> > thr#01ABF3C0
> > <- execute= -1 at D:\bin\calti.pl line 18
> > >> fetchrow_array DISPATCH (DBI::st=HASH(0x1d853e8) rc1/1 @1 g1 ima0
> > pid#2132) at D:\bin\calti.pl line 21
> > -> fetchrow_array for DBD::ODBC::st
> > (DBI::st=HASH(0x1d853e8)~0x1e17898)
> > thr#01ABF3C0
> > dbih_setup_fbav for 3 fields => 0x1ab561c
> > <- fetchrow_array= ( ' ' ' ' undef ) [3 items] row1 at
D:\bin\calti.pl
> > line 21
> > >> fetchrow_array DISPATCH (DBI::st=HASH(0x1d853e8) rc1/1 @1 g1 ima0
> > pid#2132) at D:\bin\calti.pl line 21
> > -> fetchrow_array for DBD::ODBC::st
> > (DBI::st=HASH(0x1d853e8)~0x1e17898)
> > thr#01ABF3C0
> >
> > On this thread Rob Leadbeater suggested changing a parameter on
> > the UniVerse
> > database and we haven't had an opportunity to try that yet. Let me know
if
> > you need more information or if there is something I need to do get
column
> > information in the trace.
> >
> > Regards,
> > Roger
> >
> > ----- Original Message -----
> > From: "Jeff Urlwin" <[EMAIL PROTECTED]>
> > To: "Roger Magoulas" <[EMAIL PROTECTED]>; "Jeff Urlwin"
> > <[EMAIL PROTECTED]>
> > Cc: <[EMAIL PROTECTED]>
> > Sent: Sunday, October 27, 2002 5:58 AM
> > Subject: RE: Problem Accessing UniVerse data w/ DBI
> >
> >
> > > Roger,
> > >
> > > First -- please *always* keep the dbi-users mailing list copied on
> > e-mails.
> > > Sending messages just to me won't help others and, in fact, can
> > delay the
> > > response you get as others are helpful...
> > >
> > > Well, 0.28 is *not* the latest, but that's ActiveState's
> > problem and still
> > > not resolved. It's been a LONG time since I released updates to 0.28.
> > See
> > > the DBI FAQ at www.xmlproj.com/cgi/fom.cgi for installing "private"
> > updates
> > > to the ActiveState binaries. I have a later version of DBI and
> > DBD::ODBC.
> > >
> > > Also, in my latest development version (not yet released), I have
tested
> > > some code which may fix your problem, if I read the trace
> > correctly. I'd
> > > prefer, though, that you try the latest first and if that doesn't
solve
> > it,
> > > I'll try the patch. My patch simply sets the column display size to
the
> > > greater of the column length and the display size. I'm not
> > sure this is a
> > > good one overall, but I don't see how it's harmful (yet).
> > >
> > > Please send me a trace with the new version, just to make sure.
> > >
> > > Regards,
> > >
> > > Jeff
> > >
> > >
> > >
> > > > -----Original Message-----
> > > > From: Roger Magoulas [mailto:roger@;i-loft.com]
> > > > Sent: Friday, October 25, 2002 7:10 PM
> > > > To: Jeff Urlwin
> > > > Subject: Re: Problem Accessing UniVerse data w/ DBI
> > > >
> > > >
> > > > Jeff,
> > > >
> > > > Since we are running on NT/W2K we are running ActiveState
> > Perl (v. 5.6.1
> > > > build 631). I used the PPM program to load DBI v. 1.201 and
> > DBD::ODBC v.
> > > > 0.28. I used PPM to see if there are more recent versions using the
> > search
> > > > command and found a newer DBI (v. 1.27) but not a newer DBD::ODBC. I
> > > > upgraded to the newer DBI but received the same results.
> > > >
> > > > Let me know if you need more information. We have been knocking our
> > heads
> > > > against the wall trying to figure this one out and appreciate
> > the help.
> > > >
> > > > Regards,
> > > > Roger
> > > >
> > > > ----- Original Message -----
> > > > From: "Jeff Urlwin" <[EMAIL PROTECTED]>
> > > > To: "Roger Magoulas" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> > > > Sent: Friday, October 25, 2002 3:21 PM
> > > > Subject: RE: Problem Accessing UniVerse data w/ DBI
> > > >
> > > >
> > > > > Roger,
> > > > >
> > > > > Which version of DBD::ODBC are you using?
> > > > >
> > > > > Regards,
> > > > >
> > > > > Jeff
> > > > >
> > > > >
> > > > > > We are trying to use Perl/DBI running on W2K to access UniVerse
> > > > > > data stored on an AIX machine. We are using the UV ODBC drivers
> > > > > > and have created a DSN to access the UniVerse data. We have no
> > > > > > problem connecting to the database or running queries via
> > > > > > perl/dbi, except that the fetchrow_array() stops when it reaches
> > > > > > a row with a column with more than 26 characters. Once we set
> > > > > > $dbh->{LongTruncOk} = 1; the fetchrow_array() processes all
rows,
> > > > > > but all columns are truncated to 26 chars. While we didn't think
> > > > > > it relevant (the columns appear as varchar(255), not blobs or
> > > > > > text), we set the $dbh->{LongReadLen} = 256 and found no change
in
> > > > effect.
> > > > > >
> > > > > > With the dbi trace on we find that once the select is processed
> > > > > > there are 2 sets of definition for the columns, the columns are
> > > > > > correctly defined as varchars with a length of 254, but they
have
> > > > > > a second definition with a maxlen = 26 (dbi trace below):
> > > > > >
> > > > > > dbd_describe sql 31661464: num_fields=3
> > > > > > col 1: VARCHAR len=254 disp= 26, prec=254 scale=0
> > > > > > col 2: VARCHAR len=254 disp= 26, prec=254 scale=0
> > > > > > col 3: VARCHAR len=254 disp= 26, prec=254 scale=0
> > > > > > col 1: 'ADDR1' sqltype=VARCHAR, ctype=SQL_C_CHAR, maxlen=26
> > > > > > col 2: 'MAIL_ADDR1' sqltype=VARCHAR, ctype=SQL_C_CHAR,
maxlen=26
> > > > > > col 3: 'MAIL_CSZ' sqltype=VARCHAR, ctype=SQL_C_CHAR, maxlen=26
> > > > > >
> > > > > > Using the same ODBC DSN and running queries using the BrioQuery
> > > > > > tool, we get no problems. We turned ODBC tracing and found that
> > > > > > the connection and the select processing look different in the
> > > > > > ODBC logs, via Brio there is no 2nd column definition with the
> > > > > > maxlen=26 restriction.
> > > > > >
> > > > > > We assume Perl/DBI is getting the maxlen from the UniVerse
> > > > > > database but don't know how to fix the problem.
> > > > > >
> > > > > > Thanks in advance for any help.
> > > > > >
> > > > > > Regards,
> > > > > > Roger
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
