Re: fetchall_arrayref and Can't set DBI::st=HASH(0x850eeb0)-{NAME}: unrecognised attribute or invalid value
On Thu, Aug 05, 2004 at 10:50:07PM -0400, Rudy Lippan wrote: On Thu, 5 Aug 2004, Tim Bunce wrote: I don't see any metion of it in the docs. but I recall that DBD::mysql only makes statement handle attributes like NAME available while the statement handle is still Active. Should DBD::mysql make the attribs available when the handle is not active? That would be nice but I don't think it's essential. When the issue came up originally (a year or more ago I think) I tweaked the DBI spec to say that sth metadata attributes may not be available after the sth goes in-Active. Note that any attributes used already will be cached and thus still available after finish(). It's probably best not to change the code until next_results() and related support for batched statements gets added. Tim.
Re: fetchall_arrayref and Can't set DBI::st=HASH(0x850eeb0)-{NAME}: unrecognised attribute or invalid value
I don't see any metion of it in the docs. but I recall that DBD::mysql
only makes statement handle attributes like NAME available while
the statement handle is still Active.
fetchall_arrayref() will fetch all the data and so leave the statement
handle in-Active.
Just get the NAME array ref before calling fetchall_arrayref().
Tim.
On Wed, Aug 04, 2004 at 09:29:37PM -0700, [EMAIL PROTECTED] wrote:
SENARIO:
I have downloaded a CSV file for the past 8 months with about 50k lines of
data. The data does change a little each day, just some fields. I have
uploaded all the files to a MySQL database. I have been able to compare one
row of data at a time. Easy!. But I want to compare a whole bunch like 1k
lines. When I use fetchall_arrayref I cannot get the name of the field that
contains the data. The DBI gives me the error message that it is used for
the subject of this email. Any suggestions I will enclosed of the code that
I use and my system software and libraries.
Thanks
-Max
CODE:
$q2 = $dbh-prepare(SELECT * FROM $table WHERE LicNumber
BETWEEN \$gL1\ AND \$gL2\);
$q2-execute or die ERROR: $q2-errstr \n;
$rowRef2 = $q2-fetchall_arrayref;
# print ROW2: $rowRef2-[0]-[0]\n;
$q3 = $dbh-prepare(SELECT @columns from $table2 WHERE
LicNumber BETWEEN \$gL1\ AND \$gL2\);
$q3-execute or die ERROR: $q3-errstr \n;
$rowRef3 = $q3-fetchall_arrayref;
## some more looping
for ($i = 1; $i = $q2-{NUM_OF_FIELDS}; $i++) {
if ($rowRef2-[$j]-[$i-1] ne $rowRef3-[$j]-[$i-1]) {
print $rowRef2-[$j]-[$i-1] \t -
$rowRef3-[$j]-[$i-1]\n;
push(@fields,$q3-{NAME}-[$i-1],);
push(@records,\$rowRef2-[$j]-[$i-1]\,);
push(@NewRecords,$q3-{NAME}-[$i-1]=\$rowRef2-[$j]-[$i-1]\,);
}
}
}
SYSTEM:
SDL_perl-1.20.0-350
apache2-mod_perl-1.99_12_20040302-33
perl-5.8.3-32
perl-Archive-Zip-1.09-27
perl-Compress-Zlib-1.33-30
perl-Config-Crontab-1.03-46
perl-DBD-mysql-2.9003-22
perl-DBI-1.41-28
perl-Data-ShowTable-3.3-569
perl-Digest-SHA1-2.07-30
perl-HTML-Mason-1.05-251
perl-HTML-Parser-3.35-31
perl-HTML-Tagset-3.03-550
perl-HTML-Template-2.6-135
perl-MLDBM-2.01-236
perl-PDA-Pilot-0.11.8-120
perl-Params-Validate-0.14-276
perl-Template-Toolkit-2.13-25
perl-TermReadKey-2.21-292
perl-Tie-IxHash-1.21-584
perl-URI-1.30-29
perl-XML-Parser-2.34-28
perl-gettext-1.01-576
perl-libwww-perl-5.76-30
yast2-perl-bindings-2.9.25-8
thinkpad:/mnt/net1/data/RE/individuals # rpm -qa | sort | grep mysql
apache2-mod_auth_mysql-20030510-204
mysql-shared-4.0.18-32
perl-DBD-mysql-2.9003-22
Re: fetchall_arrayref and Can't set DBI::st=HASH(0x850eeb0)-{NAME}: unrecognised attribute or invalid value
On Thu, 5 Aug 2004, Tim Bunce wrote: I don't see any metion of it in the docs. but I recall that DBD::mysql only makes statement handle attributes like NAME available while the statement handle is still Active. Should DBD::mysql make the attribs available when the handle is not active? Rudy
RE: fetchall_arrayref(): large amounts of data
How about using,
$dbh-{RowCacheSize} = (check documentation to see options).
# This is like Oracle's pre-fetch.
$sth = $dbh-prepare($sql);
$sth-execute;
$sth-bind_columns(\($var1, $var2, ...));
while ($sth-fetch) {
# put in Mysql
}
**Note : make sure your DBD::Oracle is current(1.12), otherwise you will
have to put a '$sth-{NAME};' before the execute, a bug fix for
caching. It was fixed prior to 1.12, but I forget which version
so best to have the current version.
On 14-Oct-2002 Philip Daggett wrote:
I'm downloading several million records from an Oracle database to a MySql
database and would like to use fetchall_arrayref() to do it. However, there
are so many records that my computer memory fills up and then crashes.
Is there a way of chunking the data coming down or do I need to use the
fetch_arrayref() and do it one record at a time (several million times)?
Thanks,
Phil
--
E-Mail: Scott T. Hildreth [EMAIL PROTECTED]
Date: 14-Oct-2002
Time: 20:27:57
--
Re: fetchall_arrayref(): large amounts of data
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 15 October 2002 01:23, Philip Daggett wrote: I'm downloading several million records from an Oracle database to a MySql database and would like to use fetchall_arrayref() to do it. However, there are so many records that my computer memory fills up and then crashes. Is there a way of chunking the data coming down or do I need to use the fetch_arrayref() and do it one record at a time (several million times)? What's the problem with that? If you're importing data to MySQL, the speed of the process should not matter much. You'll get high enough speed doing fetchrow_arrayref() for each row. Inserting the data to a database is much slower than fetching it. -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE9q9n3nksV4Ys/z5gRAgvpAJwLVKQ+vNtzbmhDZRmJRio8c7tNBACeL+69 YgjNiQ9On5wN0RskFg65ByQ= =QOS7 -END PGP SIGNATURE-
Re: fetchall_arrayref(): large amounts of data
Upgrade and read the docs. Tim. On Mon, Oct 14, 2002 at 03:23:42PM -0700, Philip Daggett wrote: I'm downloading several million records from an Oracle database to a MySql database and would like to use fetchall_arrayref() to do it. However, there are so many records that my computer memory fills up and then crashes. Is there a way of chunking the data coming down or do I need to use the fetch_arrayref() and do it one record at a time (several million times)? Thanks, Phil
Re: fetchall_arrayref() and multiple result sets
On Tue, 2002-06-18 at 13:34, Umaa Rebbapragada wrote: I'm noticing that the fetchall_arrayref method in DBI doesn't handle multple result sets. For example, if I prepare a statement using this sql (for example): select * from A select * from B , execute this in DBI under a single statement handle, then call Data::Dumper on fetchall_arrayref, it only returns the result of the first query. Why not the second query too? I'd rather not set up multiple statement handles, so if there's a way to accomplish this with one, I'd be extremely interested. Which DBD driver are you using? Michael -- Michael Peppler / [EMAIL PROTECTED] / http://www.mbay.net/~mpeppler [EMAIL PROTECTED] / ZetaTools, Inc / http://www.zetatools.com ZetaTools: Call perl functions as Sybase stored procedures! signature.asc Description: This is a digitally signed message part
RE: fetchall_arrayref() and multiple result sets
DBD::Sybase -Original Message- From: Michael Peppler [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 18, 2002 1:51 PM To: '[EMAIL PROTECTED]' Subject: Re: fetchall_arrayref() and multiple result sets On Tue, 2002-06-18 at 13:34, Umaa Rebbapragada wrote: I'm noticing that the fetchall_arrayref method in DBI doesn't handle multple result sets. For example, if I prepare a statement using this sql (for example): select * from A select * from B , execute this in DBI under a single statement handle, then call Data::Dumper on fetchall_arrayref, it only returns the result of the first query. Why not the second query too? I'd rather not set up multiple statement handles, so if there's a way to accomplish this with one, I'd be extremely interested. Which DBD driver are you using? Michael -- Michael Peppler / [EMAIL PROTECTED] / http://www.mbay.net/~mpeppler [EMAIL PROTECTED] / ZetaTools, Inc / http://www.zetatools.com ZetaTools: Call perl functions as Sybase stored procedures!
RE: fetchall_arrayref() and multiple result sets
On Tue, 2002-06-18 at 13:57, Umaa Rebbapragada wrote: DBD::Sybase That's what I figured :-) Well - DBI doesn't support multiple result sets internally. Maybe I'll have to add an ad-hoc version of the fetchall_*() routines, or include some documentation regarding the limitations of using them with multiple result sets. For now I think you'll either have to code your own subroutine that fetches all the rows, or split the request in two parts. Michael -Original Message- From: Michael Peppler [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 18, 2002 1:51 PM To: '[EMAIL PROTECTED]' Subject: Re: fetchall_arrayref() and multiple result sets On Tue, 2002-06-18 at 13:34, Umaa Rebbapragada wrote: I'm noticing that the fetchall_arrayref method in DBI doesn't handle multple result sets. For example, if I prepare a statement using this sql (for example): select * from A select * from B , execute this in DBI under a single statement handle, then call Data::Dumper on fetchall_arrayref, it only returns the result of the first query. Why not the second query too? I'd rather not set up multiple statement handles, so if there's a way to accomplish this with one, I'd be extremely interested. Which DBD driver are you using? Michael -- Michael Peppler / [EMAIL PROTECTED] / http://www.mbay.net/~mpeppler [EMAIL PROTECTED] / ZetaTools, Inc / http://www.zetatools.com ZetaTools: Call perl functions as Sybase stored procedures! -- Michael Peppler / [EMAIL PROTECTED] / http://www.mbay.net/~mpeppler [EMAIL PROTECTED] / ZetaTools, Inc / http://www.zetatools.com ZetaTools: Call perl functions as Sybase stored procedures! signature.asc Description: This is a digitally signed message part
RE: fetchall_arrayref() and multiple result sets
On Tue, 2002-06-18 at 13:57, Umaa Rebbapragada wrote: DBD::Sybase That's what I figured :-) Well - DBI doesn't support multiple result sets internally. Maybe I'll have to add an ad-hoc version of the fetchall_*() routines, or include some documentation regarding the limitations of using them with multiple result sets. For now I think you'll either have to code your own subroutine that fetches all the rows, or split the request in two parts. Rats ;) All right, thanks for the help. ps. Regarding the use of the UNION in my sql statement, that actually won't work in my case. For what I'm doing, I'm executing a stored proc, doing some inserts, and then a select statement. But since my stored proc has a return value, my result set contains only that value, and not the result from my select, which is what I really need. But, thanks for the suggestion though. -Original Message- From: Michael Peppler [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 18, 2002 1:51 PM To: '[EMAIL PROTECTED]' Subject: Re: fetchall_arrayref() and multiple result sets On Tue, 2002-06-18 at 13:34, Umaa Rebbapragada wrote: I'm noticing that the fetchall_arrayref method in DBI doesn't handle multple result sets. For example, if I prepare a statement using this sql (for example): select * from A select * from B , execute this in DBI under a single statement handle, then call Data::Dumper on fetchall_arrayref, it only returns the result of the first query. Why not the second query too? I'd rather not set up multiple statement handles, so if there's a way to accomplish this with one, I'd be extremely interested. Which DBD driver are you using? Michael -- Michael Peppler / [EMAIL PROTECTED] / http://www.mbay.net/~mpeppler [EMAIL PROTECTED] / ZetaTools, Inc / http://www.zetatools.com ZetaTools: Call perl functions as Sybase stored procedures! -- Michael Peppler / [EMAIL PROTECTED] / http://www.mbay.net/~mpeppler [EMAIL PROTECTED] / ZetaTools, Inc / http://www.zetatools.com ZetaTools: Call perl functions as Sybase stored procedures!
Re: fetchall_arrayref contents
I would strongly recommend
1) perldoc DBI # to read the excellent DBI documentation
included with the DBI module
2) Programming the Perl DBI book by Tim Bunce
perldoc DBI has a whole section on fetchall_arrayref - it
explains that fetchall_arrayref the way you've coded it I think
returns a reference to an array - each element of that array
is a reference to an array for one row returned from your query.
---
my $all_rows_arrayref=$sth-fetchall_arrayref();
foreach $one_row_arrayref (@{$all_rows_arrayref}) {
($filename, $size, $score) = @{$one_row_arrayref};
### Now do whatever you want with $filename, $size, $score
}
Be careful - this code is completely untested - it's been
a while since I've done DBI code, but hopefully it's close.
ALSO, be sure to read about placeholders in
perldoc DBI
placeholders will save you a LOT of time and aggrevation with
proper quoting issues - and they can(will) also be a big help with
performance.
HTH.
--
Hardy Merrill
Senior Software Engineer
Red Hat, Inc.
[EMAIL PROTECTED]
Brad [[EMAIL PROTECTED]] wrote:
I've been struggling with this and getting nothing but gibberish.
my $array_ref=$sth-fetchall_arrayref()
I can't get anything out of $array_ref though, I can't work out how to
manipulate it.
The query was select filename, size, score from files where
codeID=\.$codeID.\ which should return about 3-100 rows of data
depending on the codeID, I then want to put into @filearray, @sizearray,
@scorearray. So far I've only managed to pull what looks like hex.
Any help GREATLY appreciated. I'm using mysql, on RH 6.2
Re: fetchall_arrayref contents
How about the following:
foreach my $row_ref (@{$array_ref}) {
print @{$row_ref}, \n;
}
The above loop will print all the rows, each row has its own line, and there
is no space between two values in a row though.
Bao
Brad wrote:
I've been struggling with this and getting nothing but gibberish.
my $array_ref=$sth-fetchall_arrayref()
I can't get anything out of $array_ref though, I can't work out how to
manipulate it.
The query was select filename, size, score from files where
codeID=\.$codeID.\ which should return about 3-100 rows of data
depending on the codeID, I then want to put into @filearray, @sizearray,
@scorearray. So far I've only managed to pull what looks like hex.
Any help GREATLY appreciated. I'm using mysql, on RH 6.2
--
BAO RuiXian, PROGRAMMER, Technologies Team, Project Services Group
AtBusiness Communications Corp., Kaapeliaukio 1, FIN-00180 Helsinki
tel. +358-9-2311 6674, mob. +358-50-329 6275, fax +358-9-2311 6601
http://www.atbusiness.com, email: [EMAIL PROTECTED]
Re: fetchall_arrayref problem
Guru Prasad wrote: Dear All, I am using DBI version 1.19. I am using fetchall_arrayref function to get all the records in a single strech. The table is having about 15000 records. Now the memory occupied by the process increses by 20 MB. Once i have finished processing the records, i am doing a proper 'undef' to the array to release the memory. But it's not happening. Can it be sorted out in program itself or at DBI level or is it a problem in perl itself. Is it really absolutely necessary to get thw whole database at once? The best way to avoid this problem would be to handle one record after the other and put as much action as possible to the server via SQL. -- Helmut A. Bender Verlag Hans Schöner GmbH, Königsbach [EMAIL PROTECTED]
Re: fetchall_arrayref problem
On Thu, Nov 29, 2001 at 02:07:29PM +0530, Guru Prasad wrote: Dear All, I am using DBI version 1.19. I am using fetchall_arrayref function to get all the records in a single strech. The table is having about 15000 records. Now the memory occupied by the process increses by 20 MB. Once i have finished processing the records, i am doing a proper 'undef' to the array to release the memory. But it's not happening. `undef'ing a variable causes the storage associated with that variable to be made available for reuse. Most of the time `reuse' means within the perl script, i.e., it is not released back to the operating system. This is most likely what you are seeing, you `undef' the variable but your program size remains large. Can it be sorted out in program itself or at DBI level or is it a problem in perl itself. It is most likely not a DBI issue, nor a ``problem'' with perl. It is just the perl way to handle memory. In general, not releasing the memory back to the operating system increases speed of execution (unless you gobble up so much memory your system starts swapping). I know of no way to force perl to purge its unused memory, but here are a couple things you could try: - put the array (as a my variable), fetch, and array processing in an eval block; or eval $code; - fork a child process and do the array processing in there - write a separate script to do the fetch and processing and backtick or system call it dd -- David Dooling Informatics
Re: fetchall_arrayref problem
On Thu, Nov 29, 2001 at 07:17:15 -0600, David Dooling wrote:
It is most likely not a DBI issue, nor a ``problem'' with perl. It is
just the perl way to handle memory. In general, not releasing the
memory back to the operating system increases speed of execution
(unless you gobble up so much memory your system starts swapping).
And in this case the unused ('undef'-ined) memory should be
the first that's being paged out, if it's inactive. So even
if you'd gobble up tons of memory that's noncritical, if your
complete working set (actively used memory of all processes
on the machine) fits OK in the installed RAM. You'll have
much swap used, but not much 'swapping', i.e. paging activity,
so the machine will not trash.
Then there's How can I make my Perl program take less
memory? in the perlfaq -- use perldoc -q memory to see it.
- fork a child process and do the array processing in there
This reportedly works, but is expensive!
-Wolfgang
RE: fetchall_arrayref
If I use something like:
my $data= $sth-fetchall_arrayref;
foreach (@$data){
print qq(@$_BR);
}
I get all the data, but how the hell do I access the specific
elements in
this array of arrays? Is there a better fetch method to use?
my $data = $sth-fetchall_arrayref;
# Print Each element of each row per line separated by a space.
foreach my $row (@{$data}) {
foreach $element (@{$row}) {
print $element ;
}
print \n;
}
or
print $data-[2][3]\n;# print the 4th element of the third row.
or whatever slicing needs you want.
Have a look at perldoc perldsc for the data structure cookbook examples
which may lead you to look at other dbi methods to get the data structure
and processing you want.
--Neil
Thanks,
Eric
__
Please Note :
Only the intended recipient is authorised to access or use this e-mail.
If you are not the intended recipient, please delete this e-mail and notify
the sender immediately. The contents of this e-mail are the writer's
opinion and are not necessarily endorsed by the Gunz Companies
unless expressly stated.
We use virus scanning software but exclude all liability for viruses or
similar in any attachment.
RE: fetchall_arrayref
fetchall_hashref can be used if you need to work on hashes, though you can
get away with any method, as long as you are only comparing values.
Ilya
-Original Message-
From: Brose, Eric
To: '[EMAIL PROTECTED]'
Sent: 08/02/2001 12:52 PM
Subject: fetchall_arrayref
Hello,
I'm trying to come up with a way to compare some query results, but I'm
not
sure how I should handle this.
I am querying a dB and getting back two columns...only about 10 rows.
(see
query below).
#run query to get players picks for the week.
my $sqlstatement = qq{SELECT pick, gameNum from Picks WHERE week = ?
and
playerName = ?};
my $sth = $dbh-prepare($sqlstatement);
$sth-execute($week,$man) ||
die Could not execute SQL statement ... maybe invalid?;
I have a very similar query that acutally gets the same info from
another
table.
What I want to do, is compare the return values of the pick variable
from
one query against the pick variable from the other query, using the the
gameNum variable to pair them together.
Could someone give me some tips as to what fetch method I should use in
such
a situation.
It would make sense if I could just stick the results in 2 differnet
hashes
and use the keys from the hashes(the gameNum) to access the values(the
picks).
If I use something like:
my $data= $sth-fetchall_arrayref;
foreach (@$data){
print qq(@$_BR);
}
I get all the data, but how the hell do I access the specific elements
in
this array of arrays? Is there a better fetch method to use?
Thanks,
Eric
