select statement fails

This is a multi-part message in MIME format.

--===============0561264520==
Content-class: urn:content-classes:message
Content-Type: multipart/alternative;
boundary="----_=_NextPart_001_01C7EB74.AD92FFF6"

This is a multi-part message in MIME format.

------_=_NextPart_001_01C7EB74.AD92FFF6
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

greetings all,

I think I have a case of too much data being returned to a perl script,
but I'm not certain.

using perl 5, DBI:ODBC, MS-SQL and IIS 6:

=20

executing the following code:

=20

$sth =3D $dbh->prepare("select field1 from table ") || &failed( "prep
failed" );

$exec =3D $sth->execute() || &failed( "exec failed" );

=20

correctly returns 170 rows of data. but if I replace the field name with
*, as in:

=20

$sth =3D $dbh->prepare("select * from table ") || &failed( "prep failed"
);

$exec =3D $sth->execute() || &failed( "exec failed" );

=20

it returns no data, but neither does either statement fail. the table
contains 160 fields.

=20

can the script be overwhelmed with too much data, and/or are there
limits of field counts or return-set size, and if so can they be
managed? I have a hard time believing perl can't handle this table.

any ideas greatly appreciated. thanks.

=20

John DePasquale

IT Manager

Law Offices of James Sokolove

1340 Centre Street - Suite 102

Newton, MA 02459

Direct line: 617-467-6932

Fax: 617-964-4336

This email is intended solely for the use of the individual to whom it
is addressed and may contain information that is privileged,
confidential or otherwise exempt from disclosure under applicable law.
If the reader of this email is not the intended recipient or the
employee or agent responsible for delivering the message to the intended
recipient, you are hereby notified that any dissemination, distribution,
or copying of this communication is strictly prohibited. If you have
received this communication in error, please immediately notify us by
telephone and return the original message to us at the listed email
address. Thank You.

=20


------_=_NextPart_001_01C7EB74.AD92FFF6
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:st1=3D"urn:schemas-microsoft-com:office:smarttags" =
xmlns=3D"http://www.w3.org/TR/REC-html40">


charset=3Dus-ascii">

namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"
name=3D"place"/>
namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"
name=3D"PostalCode"/>
namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"
name=3D"State"/>
namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"
name=3D"City"/>
namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"
name=3D"Street"/>
namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"
name=3D"address"/>
namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"
name=3D"PersonName"/>

Re: select statement fails

This is a multi-part message in MIME format.

--===============1753459050==
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_00A9_01C7EB4C.37742970"

This is a multi-part message in MIME format.

------=_NextPart_000_00A9_01C7EB4C.37742970
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

What are your previous/following lines on that code? I'de like to see it =
more complete in order tyo find possible errors. I've used perl to =
manage really large tables (much larger than yours) with no problem. =
Also, which database/driver are you using?

Paco

----- Original Message -----=20
From: John DePasquale=20
To: activeperl@listserv.ActiveState.com=20
Sent: Thursday, August 30, 2007 9:14 PM
Subject: select statement fails


greetings all,

I think I have a case of too much data being returned to a perl =
script, but I'm not certain.

using perl 5, DBI:ODBC, MS-SQL and IIS 6:

=20

executing the following code:

=20

$sth =3D $dbh->prepare("select field1 from table ") || &failed( "prep =
failed" );

$exec =3D $sth->execute() || &failed( "exec failed" );

=20

correctly returns 170 rows of data. but if I replace the field name =
with *, as in:

=20

$sth =3D $dbh->prepare("select * from table ") || &failed( "prep =
failed" );

$exec =3D $sth->execute() || &failed( "exec failed" );

=20

it returns no data, but neither does either statement fail. the table =
contains 160 fields.

=20

can the script be overwhelmed with too much data, and/or are there =
limits of field counts or return-set size, and if so can they be =
managed? I have a hard time believing perl can't handle this table.

any ideas greatly appreciated. thanks.

=20

John DePasquale

IT Manager

Law Offices of James Sokolove

1340 Centre Street - Suite 102

Newton, MA 02459

Direct line: 617-467-6932

Fax: 617-964-4336

This email is intended solely for the use of the individual to whom it =
is addressed and may contain information that is privileged, =
confidential or otherwise exempt from disclosure under applicable law. =
If the reader of this email is not the intended recipient or the =
employee or agent responsible for delivering the message to the intended =
recipient, you are hereby notified that any dissemination, distribution, =
or copying of this communication is strictly prohibited. If you have =
received this communication in error, please immediately notify us by =
telephone and return the original message to us at the listed email =
address. Thank You.

=20



------------------------------------------------------------ -------------=
-----


_______________________________________________
ActivePerl mailing list
ActivePerl@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
------=_NextPart_000_00A9_01C7EB4C.37742970
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word" xmlns:st1 =
"urn:schemas-microsoft-com:office:smarttags">
charset=3Diso-8859-1">
name=3DGENERATOR> name=3D"place"=20
namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"> ype> name=3D"PostalCode"=20
namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"> ype> name=3D"State"=20
namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"> ype> name=3D"City"=20
namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"> ype> name=3D"Street"=20
namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"> ype> name=3D"address"=20
namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"> ype> name=3D"PersonName"=20
namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"> ype>

RE: select statement fails

From: activeperl-bounces@listserv.ActiveState.com
[mailto:activeperl-bounces@listserv.ActiveState.com] On Behalf Of John
DePasquale
Sent: 31 August 2007 03:15
To: activeperl@listserv.ActiveState.com
Subject: select statement fails

> greetings all,
> I think I have a case of too much data being returned to a perl
script, but I'm not certain.
> using perl 5, DBI:ODBC, MS-SQL and IIS 6:

I am guessing you mean DBI and DBD::ODBC.

> =

> executing the following code:
> =

> $sth =3D $dbh->prepare("select field1 from table ") || &failed( "prep
failed" );
> $exec =3D $sth->execute() || &failed( "exec failed" );
> =

> correctly returns 170 rows of data. but if I replace the field name
with *, as in:
> =

> $sth =3D $dbh->prepare("select * from table ") || &failed( "prep failed"
);
> $exec =3D $sth->execute() || &failed( "exec failed" );
> =

> it returns no data, but neither does either statement fail. the table
contains 160 fields.
> =

> can the script be overwhelmed with too much data, and/or are there
limits of field counts or return-set size, =

> and if so can they be managed? I have a hard time believing perl can't
handle this table.
> any ideas greatly appreciated. thanks.

Firstly, colour me clueless with regard to ODBC, MS_SQL and IIS, but I
have some experience of DBI.

You don't really provide enough information, but the database driver
(DBD::ODBC in this case) should be capable of fetching enormous amounts
of data from the database if you use if intelligently, i.e. don't try to
fetch everything in one go. Certainly much more than 170 rows of 160
fields (if that is what you mean) so your problem is likely to be
elsewhere, probably in the code that you haven't shown us.

In the mean time, I have a couple of suggestions.

I prefer in general to use the RaiseError attribute on the database
connection which makes for cleaner code (IMO), and often less typing as
you don't need to explicitly check every DBI call, which I like. For
example my typical prepare/execute/fetch code looks something like this
(just typed from memory, not from real code):

eval {
my $sth =3D $dbh->prepare(qq{select * from table});
$sth->execute;
while (my $rowref =3D $sth->fetch) {
...
}
};
die $@ if S@;

Also, it is better not to prefix subroutine calls with '&', unless a)
you know what it does, and b) you actually need that functionality. Its
less typing as well, which I like, as I think I mentioned :-).

Can I also reiterate Jan Dubois' request to post in plain text, as it
makes it easier (certainly for me) to reply.

HTH

-- =

Brian Raven =


==================== =====3D=
================
Atos Euronext Market Solutions Disclaimer
==================== =====3D=
================

The information contained in this e-mail is confidential and solely for the=
intended addressee(s). Unauthorised reproduction, disclosure, modification=
, and/or distribution of this email may be unlawful.
If you have received this email in error, please notify the sender immediat=
ely and delete it from your system. The views expressed in this message do =
not necessarily reflect those of Atos Euronext Market Solutions.

Atos Euronext Market Solutions Limited - Registered in England & Wales with=
registration no. 3962327. Registered office address at 25 Bank Street Lon=
don E14 5NQ United Kingdom. =

Atos Euronext Market Solutions SAS - Registered in France with registration=
no. 425 100 294. Registered office address at 6/8 Boulevard Haussmann 750=
09 Paris France.

L'information contenue dans cet e-mail est confidentielle et uniquement des=
tinee a la (aux) personnes a laquelle (auxquelle(s)) elle est adressee. Tou=
te copie, publication ou diffusion de cet email est interdite. Si cet e-mai=
l vous parvient par erreur, nous vous prions de bien vouloir prevenir l'exp=
editeur immediatement et d'effacer le e-mail et annexes jointes de votre sy=
steme. Le contenu de ce message electronique ne represente pas necessaireme=
nt la position ou le point de vue d'Atos Euronext Market Solutions.
Atos Euronext Market Solutions Limited Soci=E9t=E9 de droit anglais, enregi=
str=E9e au Royaume Uni sous le num=E9ro 3962327, dont le si=E8ge social se =
situe 25 Bank Street E14 5NQ Londres Royaume Uni.

Atos Euronext Market Solutions SAS, soci=E9t=E9 par actions simplifi=E9e, e=
nregistr=E9 au registre dui commerce et des soci=E9t=E9s sous le num=E9ro 4=
25 100 294 RCS Paris et dont le si=E8ge social se situe 6/8 Boulevard Hauss=
mann 75009 Paris France.
==================== =====3D=
================

_______________________________________________
ActivePerl mailing list
ActivePerl@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs

RE: select statement fails

On Fri, 31 Aug 2007, Brian Raven wrote:
> [mailto:activeperl-bounces@listserv.ActiveState.com] On Behalf Of John
> DePasquale
> > greetings all,
> > I think I have a case of too much data being returned to a perl
> script, but I'm not certain.
> > using perl 5, DBI:ODBC, MS-SQL and IIS 6:
>
> I am guessing you mean DBI and DBD::ODBC.
>
> > executing the following code:
> >
> > $sth = $dbh->prepare("select field1 from table ") || &failed( "prep
> failed" );
> > $exec = $sth->execute() || &failed( "exec failed" );
> >
> > correctly returns 170 rows of data. but if I replace the field name
> with *, as in:
> >
> > $sth = $dbh->prepare("select * from table ") || &failed( "prep failed"
> );
> > $exec = $sth->execute() || &failed( "exec failed" );
> >
> > it returns no data, but neither does either statement fail. the table
> contains 160 fields.


> > can the script be overwhelmed with too much data, and/or are there
> limits of field counts or return-set size,
> > and if so can they be managed? I have a hard time believing perl can't
> handle this table.
> > any ideas greatly appreciated. thanks.

You can run into problems with blobs, large columns, and truncation of
fields.

This tutorial of DBD::ODBC has a good explanation.


If you retrieve a field with more than LongReadLen bytes (defaults to 80
bytes) and LongTruncOk is false (default), then you will get errors. My
recollection is that you would get these errors even if warnings are
turned off, but my memory may be faulty. RaiseError and PrintError (see
below) to see what is going on.

Since you are selecting *, you may be getting fields that contain > 80
bytes of data. If you don't mind truncation, then you can set

$dbh->{LongTruncOk} = 1;

This might not be your problem - best advice is what Brian already
recommended, which is to RaiseError. I pasted Tim Bunce's explanation of
why also using PrintError is a good idea (see below).

> Firstly, colour me clueless with regard to ODBC, MS_SQL and IIS, but I
> have some experience of DBI.
>
> You don't really provide enough information, but the database driver
> (DBD::ODBC in this case) should be capable of fetching enormous amounts
> of data from the database if you use if intelligently, i.e. don't try to
> fetch everything in one go. Certainly much more than 170 rows of 160
> fields (if that is what you mean) so your problem is likely to be
> elsewhere, probably in the code that you haven't shown us.
>
> In the mean time, I have a couple of suggestions.
>
> I prefer in general to use the RaiseError attribute on the database
> connection which makes for cleaner code (IMO), and often less typing as
> you don't need to explicitly check every DBI call, which I like. For
> example my typical prepare/execute/fetch code looks something like this
> (just typed from memory, not from real code):

Pasting an interesting exchange from dbi-users, where Tim Bunce (DBI
creator) explaiend why he recommends using both PrintError and RaiseError.
This is relevant, given Brian's example.

>Tim Bunce
>>Bart Lateur
>> I've always wondered, and now seems a good time to ask as any: why are
>> PrintError and RaiseError not mutually exclusive? It seems silly to
>> have
>> both on at the same time, essentially printing the error message twice.
>> (Yes I've tested it with a fairly recent version of DBI: 1.52).
>
>The exception may be caught by an eval { }.
>Using PrintError you'd still get a warning.

And back to Brian Raven:
> eval {
> my $sth = $dbh->prepare(qq{select * from table});
> $sth->execute;
> while (my $rowref = $sth->fetch) {
> ...
> }
> };
> die $@ if S@;
>
> Can I also reiterate Jan Dubois' request to post in plain text, as it
> makes it easier (certainly for me) to reply.

Agreed.

> HTH
>
> --
> Brian Raven

John Costello

_______________________________________________
ActivePerl mailing list
ActivePerl@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs