Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much

accessing data in DBI object

by arcnon (Monk)
on Apr 14, 2005 at 14:51 UTC ( #447820=perlquestion: print w/replies, xml ) Need Help??

arcnon has asked for the wisdom of the Perl Monks concerning the following question:

I cant seen to figure out how to access the the data in the DBI object and get it back in.
$Query_Statement=" SELECT fname,lname,address,city,state,zip,phone,email FROM main ORDER by lname,fname;"; my $DataOut = $DBHandle->prepare($Query_Statement); $DataOut->execute(); foreach my $row_ref (@$DataOut->fetchrow_array){ my $fname = @$row_ary[0]; my $lname = @$row_ary[1]; my $total += length($fname); my $total += length($lname); if ((length($fname) + length($lname)) > 18){ my $Fintial = substr($fname,0,1); if ((length($Fintial) + length($lname)) > 18){ $lname = substr($lname,0,16); } $row_ary[0] = $Fintial; $row_ary[1] = $lname; } else{ next; } }

Replies are listed 'Best First'.
Re: accessing data in DBI object
by polettix (Vicar) on Apr 14, 2005 at 14:53 UTC
    If you want to get an array ref, use fetchrow_arrayref instead of fetchrow_array. Then... use it instead of using $row_ary!

    Update Of course, using array refs would lead to using $row_ref->[0] where you use $row_ary[0] - see perldoc perldata anyway.

    Flavio (perl -e "print(scalar(reverse('ti.xittelop@oivalf')))")

    Don't fool yourself.
Re: accessing data in DBI object
by RazorbladeBidet (Friar) on Apr 14, 2005 at 14:58 UTC
    Agreeing with frodo72 with an additional statement that using strict (i.e. adding "use strict;" at the top of your program) would have shown you that row_ary doesn't exist anywhere. Good to have (as well as "use warnings;")

    Obviously, this is just a snippet. But I imagine you don't have it or it would have complained.
    "But what of all those sweet words you spoke in private?"
    "Oh that's just what we call pillow talk, baby, that's all."
Re: accessing data in DBI object
by arturo (Vicar) on Apr 14, 2005 at 15:16 UTC

    It helps us help you if you can say what's not working, e.g. what error message is perl giving you when you try to execute the code. That said, there are a couple of things that jumped out at me:

    • foreach my $row_ref ... but inside the loop, you're referencing a thing you think is an array reference with the name row_ary; if you're using strict, this should be caught at compile time.
    • you're getting an error message that looks like Not an ARRAY reference at your foreach line, because @$object->method treats $object as an array reference

    (Also, what you probably meant to write ( @{$DataOut->fetchrow_array} ) won't work either, because fetchrow_array returns the row's data as an array.

    So the following loop rewrite should help you get over the two problems I've mentioned:

    while ( my @row = $DataOut->fetchrow_array ) { my $fname = $row[0]; # ... }

    An even better solution, IMO, would be to use fetchrow_hashref so the columns have readable names and you don't have to go back to the SQL to figure out which column maps onto which variable.


    If not P, what? Q maybe?
    "Sidney Morgenbesser"

Re: accessing data in DBI object
by rnahi (Curate) on Apr 14, 2005 at 15:40 UTC
      I own programming the perl dbi which pretty much the same thing.

      I learn well from example but I could never find any info on accessing the object.

Re: accessing data in DBI object
by JediWizard (Deacon) on Apr 14, 2005 at 15:16 UTC

    In addition to the comment made by the other monks, I'd like to describe a basic locigical flaw with this code. By using foreach my $row_ref (@$dataOut->fetchrow_array) you are actually iterating over columns, not rows. Changing this to use fetchrow_arrayref only changes it to be a foreach over a list with only one value (doesn't make any sense to do that). What you really meant to use there was fetchall_arrayref. This will return an array of arrays with all the data retrieved by your SQL query.

    Update: After reading arturo's response, I'd like to add the following: using while(my @row = $dbh->fetchrow_array) is more memory efficient than using foreach my $row_ref ($dbh->fetchall_arrayref) but fetchall_arrayref may be faster (I'm not sure, it would depend on which DBD module was being used, you'd have to benchmark it). To speak to arturo's recomendation of using fetchrow_hash, it does make the code more maintainable, but less memory efficient, so it really depends on which is more important to you. You can achive the same effect as using fetchrow_hash using fetchall_arrayref by passing an empty hash ref in as an argument to the method.
    HTH, Cheers

    A truely compassionate attitude towards other does not change, even if they behave negatively or hurt you

    —His Holiness, The Dalai Lama

      I was just trying to get at 2 fields and change the data without having to create a new object. Which I did anyway...

      I alway use strict. Yes it throws errors which just told me I wasn't accessing the structure right. But I already knew that. :)

      References eat my lunch anyway. I use objects to dodge them to avoid @{}->[]->{} crap. So when I have to work with them(refs) I am always lost. my solution was as follows

      $Query_Statement="select fname,lname,address,city,state,zip,phone,emai +l FROM main order by lname,fname;"; my $sth = $dbh->prepare($Query_Statement); $sth->execute(); my @array; my $dataObjRef = \@array; while( my $array_ref = $sth->fetchrow_arrayref){ #print ">>@$array_ref\n"; my $fname = $array_ref->[0]; my $lname = $array_ref->[1]; my $total += length($fname); my $total += length($lname); if ((length($fname) + length($lname)) > 18){ my $Fintial = substr($fname,0,1); if ((length($Fintial) + length($lname)) > 18){ $lname = substr($lname,0,16); } $array_ref->[0] = $Fintial; $array_ref->[1] = $lname; #print "$Fintial $lname\n"; } else{ next; } my @newArray = @$array_ref; push(@$dataObjRef, \@newArray); } $sth->finish(); $dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n"; return $dataObjRef; }

        The only objects you have in this snippet are $dbh (a database handle object) and $sth (a statement handle object). (You called them $DBHandle and $DataOut in the original snippet.) The rest of the variables are just your ordinary garden variety variables: scalars, arrays and hashes. Objects are references (a scalar holding the address of another variable) that have been blessed into a given class. See perldata, perlref, perldsc, perllol, perlobj, perlmod, perltoot and perlboot for the complete details about Perl data types, references, Perl data structures, modules, and objects.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://447820]
Approved by polettix
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (2)
As of 2023-06-01 01:09 GMT
Find Nodes?
    Voting Booth?

    No recent polls found