select statement fails

From: activeperl-bounceslistserv.ActiveState.com
[mailto:activeperl-bounceslistserv.ActiveState.com]
On Behalf Of John
DePasquale
Sent: 31 August 2007 03:15
To: activeperllistserv.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 = $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.

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 = $dbh->prepare(qq{select * from table});
    $sth->execute;
    while (my $rowref = $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 

=========================================
Atos Euronext Market Solutions Disclaimer
=========================================

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 immediately 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 bond 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 75009 Paris France.

L'information contenue dans cet e-mail est confidentielle et
uniquement destinee a la (aux) personnes a laquelle
(auxquelle(s)) elle est adressee. Toute copie, publication
ou diffusion de cet email est interdite. Si cet e-mail vous
parvient par erreur, nous vous prions de bien vouloir
prevenir l'expediteur immediatement et d'effacer le e-mail
et annexes jointes de votre systeme. Le contenu de ce
message electronique ne represente pas necessairement la
position ou le point de vue d'Atos Euronext Market
Solutions.
Atos Euronext Market Solutions Limited Société de droit
anglais, enregistrée au Royaume Uni sous le numéro 3962327,
dont le siège social se situe 25 Bank Street E14 5NQ Londres
Royaume Uni.

Atos Euronext Market Solutions SAS, société par actions
simplifiée, enregistré au registre dui commerce et des
sociétés sous le numéro 425 100 294 RCS Paris et dont le
siège social se situe 6/8 Boulevard Haussmann 75009 Paris
France.
=========================================

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