develooper Front page | perl.beginners | Postings from September 2009

Re: Limit on number of columns pulled using DBI::ODBC

Thread Previous | Thread Next
From:
Tony Esposito
Date:
September 30, 2009 19:54
Subject:
Re: Limit on number of columns pulled using DBI::ODBC
Message ID:
690199.24867.qm@web24613.mail.ird.yahoo.com
After doing some research, it looks like I am having problems either retrieving CLOBs or just that the source database (Oracle 10gr2) is set to UTF-8 character set.

I am trying to retrieve all columns and some (not all) rows from an Oracle table which contain - among other things - 2 CLOB columns and print this to a flat file (ASCII file).  

As I "tune" $dbh->{LongReadLen} = 20000 I can varying results from 'Out of Memory!" errors to not all data retrieved to a Perl Interpreter Error Window, which asks me if I want to send the error contents to Microsoft.  

I am running on WindowsXP SP2 and using the DBI::ODBC driver with the Oracle Client ODBC driver.

The SQL statement is just SELECT * FROM table, nothing fancy here.  I just believe that the data is 'too big/wide' for the variables I am using to hold them and that is why I am leaning to this being an character set encoding issue.  But I was hoping that adjusting  $dbh->{LongReadLen} = 20000 would work.

Alsot tried  $dbh->{LongTruncOk} = 1; just to truncate the any data that overflows memory but still get same errors.

HTH

--- On Wed, 30/9/09, Steve Bertrand <steve@ibctech.ca> wrote:

From: Steve Bertrand <steve@ibctech.ca>
Subject: Re: Limit on number of columns pulled using DBI::ODBC
To: "Tony Esposito" <tony1234567893@yahoo.co.uk>
Cc: "Beginners Perl" <beginners@perl.org>
Date: Wednesday, 30 September, 2009, 8:39 PM

Tony Esposito wrote:
> Is there a limit on the number of columns pulled from a table using DBI::ODBC?

Although I just received your own reply to this message, I'll respond to
this one as I have some questions.

> I am getting an 'out of memory' error if I try to retrieve 40 columns or more when using the following ...

Are you referring really to "columns", or do you mean 'rows'?

> my $dbh = DBI->connect( dbi:ODBC:orcl, "login", "password",
>                          { RaiseError => 1 }
>                       ) || die "Database connection not made: $DBI::errstr";
> ....

It would be prudent for you to explain what you have in '...' here. ie.
what does your select statement say?

> while (my $tableRef = $sth->fetchrow_arrayref) {
> ...
> }

...again, I can't tell if you are really configuring a statement to
acquire just the table column headings (because you haven't shown us
your SQL statement), or if you are using the terminology 'columns' when
you really mean 'rows'.

Without knowing either Oracle or your statement, I'd have to assume that
it may help if you throw a 'limit N' at the end of your statement.

Grab what you can from the db using 'limit', write a function that
checks the next id in the table and then grabs the next N number of rows.

fwiw, $dbh->{LongReadLen} = 20000; is something I've never seen before...

What is it you are trying to do exactly?

Steve



      
Thread Previous | Thread Next


nntp.perl.org: Perl Programming lists via nntp and http.
Comments to Ask Bjørn Hansen at ask@perl.org | Group listing | About